RSS Feed for This PostCurrent Article

Oracle SQLPlus Note

@ and @@

The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts. A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH). @@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script).

& and &&

“&” is used to create a temporary substitution variable that will prompt you for a value every time it is referenced. “&&” is used to create a permanent substitution variable. Once you have entered a value (defined the variable) its value will used every time the variable is referenced.

DEF variablename=value

Define variable variablename, if not already defined, and set its value to value

UNDEF variablename

Undefine variablename

Gather Statistics

sqlplus /nolog @/gather_stats.sql sdp logfile connection_string

connect &&3;
define schema_name=&&1;
set echo on timing on time on termout on;
spool &&2;
exec DBMS_STATS.UNLOCK_SCHEMA_STATS (‘&&schema_name’);
exec dbms_stats.gather_schema_stats(‘&&schema_name’,options=>’GATHER’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>’FOR ALL COLUMNS SIZE AUTO’,cascade=>TRUE);
spool off;
exit;


Trackback URL


Sorry, comments for this entry are closed at this time.