Category: Oracle

http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1008815

syntax sqlplus -S <userid>/<password>@<database> @<sql-file> > <output-file> 2> /dev/null example sqlplus -S <meuser>/<mepass>@<medatabase> @<mequery.sql> > <meoutput.dat> 2> /dev/null   The below lines need to be at the top of the sql-file to ensure the output-file is formatted correctly. -S set …

sqlplus – produce clean extract file Read more »

syntax sqlldr <schema_name>/<schema_password>@<database_name> control=<control_file> log=<log_file> data=<data_file> example sqlldr <meschema>/<mepassword>@<medatabase> control=<mecontrol_file> log=<melog_file> data=<medata_file>   <control_file>.ctl control script LOAD DATA APPEND INTO TABLE <TABLE_NAME> FIELDS TERMINATED BY ‘<FIELD_SEPARATOR_VALUE>’ OPTIONALLY ENCLOSED BY ‘<QUOTE_CHARACTER>’ TRAILING NULLCOLS (<COL1>, <COL2>, <COL3>)

syntax RENAME <old-table-name> TO <new-table-name> example RENAME cutomers; TO new_customers

syntax SELECT <query-fields> FROM <table-name> sample(<%age of records to be returned>) [WHERE <condition>] example SELECT <customer-name> FROM <customer> sample(10)

syntax COPY FROM <schema>/<password>@<database> TO <schema>/<password>@<database> CREATE | REPLACE | INSERT | APPEND <tablename> USING <Select clause that returns a results set> example COPY FROM one-schema/one-schemas-password@one-database TO another-schema/another-schemas-password@another-database> CREATE replica-table USING SELECT * FROM original-table   Good idea to create …

Copy Table – more powerful Read more »

syntax CREATE TABLE AS SELECT FROM example CREATE TABLE new_cusomer AS SELECT * FROM customer