Oracle shortcuts

oracle shortcuts

-- Show all tables
> SELECT TABLE_NAME FROM TABS

-- List fields from a table 
> SELECT *
 FROM user_tab_cols
 WHERE table_name = 'table_name'

-- List some fields fields from a table 
> SELECT column_name, data_type,  FROM user_tab_cols WHERE table_name = 'table_name'

-- Alter table
ALTER TABLE table MODIFY ( field type )

select R_CONSTRAINT_NAME from user_constraints where
                     Constraint_name = 'W1244_T0238_FK';
 
 
select table_name, COLUMN_NAME 
           from user_cons_columns where 
                  CONSTRAINT_NAME =  'Obtained from above'
 
select table_name, COLUMN_NAME 
           from user_cons_columns where 
                  CONSTRAINT_NAME ='W1244_T0238_FK';

-- Create another user and import a .dmp file
create user foo identified by foo;
GRANT CONNECT, RESOURCE, DBA TO foo;
imp foo/foo file=/backup.dmp log=/tmp/backup.log fromuser=bar touser=foo full ignore=y buffer=10000000

-- load a csv file
acme@petunia /tmp $ sqlldr userid=foo/foo control=/tmp/control.ctl

acme@petunia /tmp $ cat /tmp/control.ctl
LOAD DATA
INFILE /tmp/control.csv
APPEND
INTO TABLE bar
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
field1,
field2
)