Archive

Archive for the ‘SQL*Plus’ Category

Using SQL*Plus copy command in Oracle 11gR2

March 20, 2014 Leave a comment

There’s a unusual command in SQL*Plus that may be useful under certain circumstances: the COPY command (http://docs.oracle.com/cd/E11882_01/server.112/e16604/apb.htm).

Although this command will be obsoleted in future releases of SQL*Plus, Oracle has been saying the same since (at least) Oracle 9, and it’s still available in Oracle 11gR2.

I need to copy a table from one database to another database, and I don’t want to do an export/import of the table.

A simple copy of the table is enough for me:


miquel@db_2> copy from otheruser@db_1 create my_table using select * from other_table;
Enter FROM password:

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit when done. (copycommit is 0)
Maximum long size is 2000000000. (long is 2000000000)
Table MY_TABLE created.

55481 rows selected from otheruser@db_1.
 55481 rows inserted into MY_TABLE.
 55481 rows committed into MY TABLE at DEFAULT HOST connection.

It’s that simple!

Feel free to comment below.

Categories: 11gR2, oracle, SQL*Plus Tags:

Rename a tablespace/datafile in Oracle 9i/10g

October 25, 2010 2 comments

To rename a tablespace in Oracle 10g is very easy:

SQL> alter tablespace test1 rename to test2;

The rename only works in Oracle 10, and updates the dictionary entries for user quotas (user_ts_quotas). There is no simple way to rename a tablespace in Oracle 9, you have to use exp/imp, so, you cannot issue this online.

If you want to delete a tablespace, it’s better to first unassign all quotas on that tablespace, because if you later create a tablespace with the same name, the old quotas are still active:

SQL> alter user <username> quota 0 on <tablespace_name>;
SQL> drop tablespace <tablespace_name> including contents and datafiles;

If you want to rename also its datafile(s) you have to do it via SQL*Plus and linux console:

SQL> ALTER TABLESPACE test2 OFFLINE;
linux> mv 'oldfile.dbf' 'newfile.dbf'
SQL>  ALTER TABLESPACE test2 RENAME datafile '/path_to_old_datafile/oldfile.dbf' TO '/path_to_new_datafile/newfile.dbf'

SQL>  ALTER TABLESPACE test2 ONLINE;
Categories: oracle, SQL*Plus Tags: , , ,

How to trace a SQL session

October 21, 2010 Leave a comment

To trace an SQL session in SQL*Plus you need to set autotrace on:

SQL> set autotrace on

Setting autotrace allows to display some statistics and/or a query execution plan for DML statements.

set autotrace on Shows the execution plan as well as statistics of the statement.
set autotrace on explain Displays the execution plan only.
set autotrace on statistics Displays the statistics only.
set autotrace traceonly Displays the execution plan and the statistics (as set autotrace on does), but doesn’t print a query’s result.
set autotrace off Disables all autotrace
Categories: oracle, SQL*Plus Tags: , ,