Oracle Migration

https://console.aws.amazon.com/cloudformation/home?region=us-east-1#/stacks?filteringText=&filteringStatus=active&viewNested=true&hideStacks=false

Remote Desktop Host: <EC2HostName>  

Username: developer
Passowrd: <EC2DeveloperPassword>

Servername: localhost
port: 1521
SID: XE
Username: dms_user
Password: dms_user
DriverPath: C:\DMS Workshop\JDBC\ojdbc8.jar

Servername: <RedshiftClusterEndpoint>
Port: <RedshiftClusterPort>
Database: <RedshiftClusterDatabase>
Username: <RedshiftClusterUser>
Password: <RedshiftClusterPassword>
RedshiftDriverPath: C:\DMS Workshop\JDBC\RedshiftJDBC42-1.2.43.1067.jar

C:\DMS Workshop\JDBC\RedshiftJDBC42-1.2.43.1067.jar

Driver: Amazon Redshift
URL: jdbc:redshift://<RedshiftClusterEndpoint>:<RedshiftClusterPort>/<RedshiftClusterDatabase>
Username: <RedshiftClusterUser>
Password: <RedshiftClusterPassword>
Autocommit: tick mark

select t.table_schema, t.table_name,i.tbl_rows
from svv_tables t left join svv_table_info i
on t.table_schema = i.schema and t.table_name = i.table
where t.table_type = 'BASE TABLE' and t.table_schema='dms_sample'
order by 1,2

Name: Oracle
Username: dms_user
Password: dms_user
hostname: localhost
port: 1521
sid: xe

select owner, table_name, nvl(num_rows,-1)
from all_tables where owner='DMS_SAMPLE'
order by 1,2

select t.table_schema, t.table_name,i.tbl_rows
from svv_tables t left join svv_table_info i
on t.table_schema = i.schema and t.table_name = i.table
where t.table_type = 'BASE TABLE' and t.table_schema='dms_sample'
order by 1,2

select * from dms_sample.sport_type order by 1;

select * from dms_sample.sport_type order by 1;

INSERT ALL
INTO dms_sample.sport_type (name,description) VALUES ('hockey', 'A sport in which two teams play against each other by trying to more a puck into the opponents goal using a hockey stick')
INTO dms_sample.sport_type (name,description) VALUES ('basketball', 'A sport in which two teams of five players each that oppose one another shoot a basketball through the defenders hoop')
INTO dms_sample.sport_type (name,description) VALUES ('soccer','A sport played with a spherical ball between two teams of eleven players')
INTO dms_sample.sport_type (name,description) VALUES ('volleyball','two teams of six players are separated by a net and each team tries to score by grounding a ball on the others court')
INTO dms_sample.sport_type (name,description) VALUES ('cricket','A bat-and-ball game between two teams of eleven players on a field with a wicket at each end')
SELECT * FROM dual;

COMMIT;

select * from dms_sample.sport_type order by 1;

select * from dms_sample.sport_type order by 1;

drop table if exists dms_sample.person cascade;
drop table if exists dms_sample.ticket_purchase_hist cascade;
drop table if exists dms_sample.sporting_event_ticket cascade;
drop table if exists dms_sample.sporting_event cascade;
drop table if exists dms_sample.seat cascade;
drop table if exists dms_sample.sport_type cascade;
drop table if exists dms_sample.sport_location cascade;
drop table if exists dms_sample.sport_team cascade;
drop table if exists dms_sample.sport_league cascade;
drop table if exists dms_sample.sport_division cascade;
drop table if exists dms_sample.seat_type cascade;
drop table if exists dms_sample.player cascade;
drop table if exists dms_sample.name_data cascade;
drop table if exists dms_sample.nfl_stadium_data cascade;
drop table if exists dms_sample.nfl_data cascade;
drop table if exists dms_sample.mlb_data cascade;

delete from dms_sample.sport_type where name in ('hockey', 'basketball', 'soccer', 'volleyball', 'cricket');