Oracle SQLPlus Note
By admin on Aug 10, 2008 in Oracle, side 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;
Sorry, comments for this entry are closed at this time.