Moving Control Files in Oracle

1. SQL> alter system set control_files='new_file1_location','new_file2_location','new_file3_location' scope=spfile;
2. SQL> shutdown immediate;
3. move/copy control files to new location
4. SQL> startup

Moving Oracle Datafiles...non system and system

To move non-system oracle datafiles

1. alter tablespace offline;
2. host out and move the files
3. alter database rename file 'fully_qualified_old_file_name' to 'fully_qualified_new_file_name';
4. alter tablespace online;
5. Backup your controlfile.

To move system oracle datafiles

1. SQL> shutdown immediate;
2. SQL> startup mount;
3. SQL> !ls -l ;
4. SQL> !mv ;
5. SQL> alter database rename file '' to '';
SQL> alter database open;

To gather a SQLTXPLAIN for a single SQL Statement.



Read Oracle Metalink Note: 215187.1 "SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement



Given one SQL statement within a file or from memory, it generates a comprehensive report that includes the explain plan for that SQL statement, related CBO stats, and a full set of diagnostic information on all related objects.
This tool is designed to help during the diagnostic phase of SQL performance issues (SQL tuning). Besides providing a comprehensive report, it also facilitates the creation of test cases on separate environments. During its execution, it creates a metadata script, that combined with a simple procedure to migrate the related CBO stats, it simplifies the replication of the SQL analysis environment.
The SQL statement to diagnose can be provided as a text file, or directly from memory passing as a parameter value its ID (hash_value or SQL_ID).