SQL explain
-- install plan table
@C:\Oracle\ora81\RDBMS\ADMIN\UTLXPLS
-- create plan in plan table
EXPLAIN PLAN FOR
SELECT * FROM all_tables ;
-- create plan in plan table with own id
EXPLAIN PLAN set STATEMENT_ID = 'MyPlan1' FOR
SELECT * FROM all_tables ;
-- List plans in plan table
select ID
, STATEMENT_ID
, to_char(timestamp,'DD-MM-YYYY HH24 MI')
from PLAN_TABLE where id = 0;
-- Get plan from plan table
SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'MyPlan1'
ORDER BY id;
## Get an explain plan on the prompth 10g>
set autotrace traceonly explain
select * from user_tables;
set autotrace off
## listener.ora
SQLNET.ORA ======================================================================= PARAMETERS | Values | Example (CLIENT, UNIX) ======================================================================= * CLIENT side ======================================================================= TRACE_LEVEL_CLIENT | OFF/USER/ADMIN | TRACE_LEVEL_CLIENT=USER TRACE_FILE_CLIENT | string | TRACE_FILE_CLIET=client.trc TRACE_DIRECTORY_CLIENT | valid directory | TRACE_DIRECTORY_CLIENT=/oracle/log TRACE_UNIQUE_CLIENT | OFF/ON | TRACE_UNIQUE_CLIENT=ON LOG_FILE_CLIENT | string | LOG_FILE_CLIENT=client.log LOG_DIRECTORY_CLIENT | valid directory | LOG_DIRECTORY_CLIENT=/oracle/log ======================================================================= * SERVER side ======================================================================= TRACE_LEVEL_SERVER | OFF/USER/ADMIN | TRACE_LEVEL_SERVER=USER TRACE_FILE_SERVER | string | TRACE_FILE_SERVER=server.trc TRACE_DIRECTORY_SERVER | valid directory | TRACE_DIRECTORY_SERVER = | | c:\temp\trace LOG_FILE_SERVER | string | LOG_FILE_SERVER=server.log LOG_DIRECTORY_SERVER | valid directory | LOG_DIRECTORY_SERVER=c:\temp | | \log ======================================================================= * TNSPING ======================================================================= TNSPING.TRACE_LEVEL | OFF/USER/ADMIN | TNSPING.TRACE_LEVEL=USER TNSPING.TRACE_DIRECTORY| valid directory | TNSPING.TRACE_DIRECTORY=/oracle/log =======================================================================
LISTENER.ORA ======================================================================= PARAMETERS | VALUES | EXAMPLE (CLIENT,UNIX) ======================================================================= TRACE_LEVEL_LISTENER | OFF/USER/ADMIN | TRACE_LEVEL_LISTENER=OFF TRACE_FILE_LISTENER | string | TRACE_FILE_LISTENER=listener.trc TRACE_DIRECTORY_LISTENER | valid directory | TRACE_DIRECTORY_LISTENER=/oracle/log LOG_FILE_LISTENER | string | LOG_FILE_LISTENER =listener.log LOG_DIRECTORY_LISTENER | valid directory | LOG_DIRECTORY_LISTENER =/oracle/log =======================================================================
Examples : sqlnet.ora tnsnames.ore
## Select with escape caracter set.
Search for object with the name '%NAME' :
select * from all_objects where object name like '%/%NAME%' escape '/'
|