Daily used PL/SQL commands

1) Dump all console screen to a file

SQL>spool c:\log.txt
SQL>execute sql ...
SQL>spool off

2) Modify the line size, page size to let the report more neat.

SQL>set line 150
SQL>set pagesize 999

3) Format the columns to let the report more neat

SQL>col varchar_colname format awidthheading''new col name'
SQL>col number_colname format 999,999

4) Show execution plan

SQL>explain plan for SQL-Statement.
or
SQL>set autotrace traceonly explain
SQL>SQL-Statement
SQL>set autotrace off

5) Show the text file

SQL>get pln.sql

6) Create a variable and set its value
SQL>variable regid number
SQL>exec :regid := 1

7) Show statistics.

SQL>set autotrace traceonly statistics

8) Recalculate the statistics.

SQL> exec dbms_stats.gather_table_stats(user, 'MY_OBJECTS', estimate_percent => 100, cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
SQL> exec dbms_stats.gather_schema_stats('user');