Oracle function Case
Oracle case
SET SERVEROUT ON
DECLARE
p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;
BEGIN
BEGIN
CASE
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 5;
END;
DBMS_OUTPUT.PUT_LINE('q = ' || q);
END;
/
case for null
SET SERVEROUT ON
DECLARE
p BINARY_INTEGER:= null;
-- p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;
BEGIN
BEGIN
CASE
WHEN p is null then q:=10000;
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 4;
END;
DBMS_OUTPUT.PUT_LINE('q = ' || q);
END;
/
Saturday, 15 October 2011
ORA-01476: divisor is equal to zero
to avoid ORA-01476: divisor is equal to zero
declare
n1 number := 155;
n2 number := 0;
begin
n1 := n1/n2; -- divide by zero
dbms_output.put_line(n1);
exception
when ZERO_DIVIDE
then dbms_output.put_line('You Divided by Zero');
end;
/
declare
n1 number := 155;
n2 number := 0;
begin
n1 := n1/n2; -- divide by zero
dbms_output.put_line(n1);
exception
when ZERO_DIVIDE
then dbms_output.put_line('You Divided by Zero');
end;
/
DBMS_STATS.GATHER_SCHEMA_STATS
Schema stats
DBMS_STATS state gather
DBMS_STATS
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'
There are several values for the OPTIONS parameter that we need to know about:
GATHER - Reanalyzes the whole schema
GATHER EMPTY - only analyzes tables that have no existing statistics
GATHER STALE - only reanalyzes tables with more than 10 percent modifications (inserts, updates, deletes)
GATHER AUTO - will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.
Example :
exec DBMS_STATS.gather_schema_stats(
ownname => 'ANUJ',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true )
SQL> show user
USER is "SCOTT"
SQL> /
Objects Analyzed: 0
PL/SQL procedure successfully completed.
SQL> r
1 declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14* end;
Objects Analyzed: 0
PL/SQL procedure successfully completed.
ORA-20001: Illegal option GATHER REANALYZES: must be GATHER | GATHER AUTO | GATHER STALE | GATHER EMPTY | LIST AUTO | LIST STALE | LIST EMPTY
'GATHER REANALYZES' reanalyzes
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> show user
USER is "SCOTT"
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15
16
17 /
Objects Analyzed: 15
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: SALGRADE
Object Name: MYEMP_IDX
Object Name: PK_DEPT
Object Name: SYS_C0022541
Object Name: SYS_C0022542
Object Name: SYS_C0022543
PL/SQL procedure successfully completed.
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15 /
Objects Analyzed: 7
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: SALGRADE
PL/SQL procedure successfully completed.
SQL> declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats (OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line ( '-------------------------' );
15 end loop;
16 end;
17 /
SH
INDEX
SYS_IOT_TOP_74375
-------------------------
SH
INDEX
SYS_IOT_TOP_74380
-------------------------
PL/SQL procedure successfully completed.
DBMS_STATS state gather
DBMS_STATS
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'
There are several values for the OPTIONS parameter that we need to know about:
GATHER - Reanalyzes the whole schema
GATHER EMPTY - only analyzes tables that have no existing statistics
GATHER STALE - only reanalyzes tables with more than 10 percent modifications (inserts, updates, deletes)
GATHER AUTO - will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.
Example :
exec DBMS_STATS.gather_schema_stats(
ownname => 'ANUJ',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true )
SQL> show user
USER is "SCOTT"
SQL> /
Objects Analyzed: 0
PL/SQL procedure successfully completed.
SQL> r
1 declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14* end;
Objects Analyzed: 0
PL/SQL procedure successfully completed.
ORA-20001: Illegal option GATHER REANALYZES: must be GATHER | GATHER AUTO | GATHER STALE | GATHER EMPTY | LIST AUTO | LIST STALE | LIST EMPTY
'GATHER REANALYZES' reanalyzes
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> show user
USER is "SCOTT"
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15
16
17 /
Objects Analyzed: 15
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: SALGRADE
Object Name: MYEMP_IDX
Object Name: PK_DEPT
Object Name: SYS_C0022541
Object Name: SYS_C0022542
Object Name: SYS_C0022543
PL/SQL procedure successfully completed.
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15 /
Objects Analyzed: 7
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: SALGRADE
PL/SQL procedure successfully completed.
SQL> declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats (OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line ( '-------------------------' );
15 end loop;
16 end;
17 /
SH
INDEX
SYS_IOT_TOP_74375
-------------------------
SH
INDEX
SYS_IOT_TOP_74380
-------------------------
PL/SQL procedure successfully completed.
===============
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/12.2/db_home
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export DATE=$(date +%y-%m-%d_%H%M%S)
# Gather ANUJ schema stats
sqlplus / as sysdba << EOF > /tmp/ANUJ_stats_gather_$DATE.log
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;
EXEC DBMS_STATS.gather_schema_stats('ANUJ');
EOF
echo "Stats gathered succeeded"
********
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;
/home/oracle/bin/run_gather_database_stats.sh orcl &> /dev/null
cat /home/oracle/bin/run_gather_database_stats.sh
#! /bin/bash
#
source /home/oracle/.bash_profile
sid=$1
export OUTFILE=/var/log/oracle/gather_database_stats_$sid.log
export ORACLE_SID=${sid}
$echo $ORACLE_SID
sqlplus /nolog << EOF
connect / as sysdba
set line 999
set pagesize 999
set trimspool on
set echo on
spool $OUTFILE
@/home/oracle/bin/gather_databse_stats.sql
spool off
exit
cat /home/oracle/bin/gather_databse_stats.sql
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER EMPTY');
select sysdate from dual;
exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER STALE');
select sysdate from dual;
[oracle@lon1-rac01 ~]$
=============
on root and pdbs !!!!
===================
check this
https://anuj-singh.blogspot.com/search?q=catcon.pl
set verify off time on timing on
alter session set nls_date_format='dd-mon-yyyy';
select sysdate from dual;
prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()
prompt ========================================
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS')
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
prompt ========================================
prompt DBMS_STATS.GATHER_DICTIONARY_STATS
prompt ========================================
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();
====================
or
set echo on time on timing on;
prompt Stats on Container;
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
DBMS_STATS.GATHER_DICTIONARY_STATS ();
END;
alter session set container=XXXXX;
prompt Stats on Pluggable;
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
DBMS_STATS.GATHER_DICTIONARY_STATS ();
END;
/
====
SET SERVEROUTPUT ON
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_SCHEMA_stats(OWnNAME=>'&schema_name',objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
=====================
nohup sqlplus '/ as sysdba' @run_gather_stats.sql > run_gather_stats1.log 2>&1 &
change pdb name
set time on timing on
timing start
alter session set nls_date_format='dd-mon-yyyy hh24:mi';
select sysdate from dual;
prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
prompt ========================================
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree);
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree);
prompt 'alter session set container=XXXXX' ------<<<<<<
alter session set container=XXXXXXX ;
show pdbs
alter session set nls_date_format='dd-mon-yyyy hh24:mi';
prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
prompt ========================================
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree);
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree);
select sysdate from dual;
/
timing stop
*****************
to check
show pdbs
alter session set nls_date_format='DD-MM-YYYY hh24:mi';
col last_analyzed for a13
set termout off
set trimspool off
set feedback off
spool dictionary_statistics.txt append
prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) sys_dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1;
prompt 'Statistics for SYSTEM Objects'
SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) system_dictionary_tables
FROM dba_tables
WHERE owner = 'SYSTEM'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1;
prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY');
/
=====================================
Automatic Statistics Gathering does not Complete - Diagnostics Interpretation Guidelines (Doc ID 1902112.1)
exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pagesize 9999
spool dba_autotask_client.html
set markup html on
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE;
select * from DBA_SCHEDULER_JOB_LOG;
SELECT program_name, program_action, enabled FROM dba_scheduler_programs;
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pagesize 9999
spool dba_autotask_client.html
set markup html on
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE;
select * from DBA_SCHEDULER_JOB_LOG;
SELECT program_name, program_action, enabled FROM dba_scheduler_programs;
spool off
Following command can be used to get the report:-
SET LINES 200 PAGES 0 LONG 100000
COLUMN REPORT FORMAT A200
VARIABLE my_report CLOB;
BEGIN
:my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (
since => SYSDATE-1
, until => SYSDATE
, detail_level => 'TYPICAL'
, format => 'HTML'
, auto_only => TRUE
);
END;
/
spool stats_output.html
print my_report;
spool off;
to generate the status of all objects touched by AUTO statistics job:
SET LINES 200 PAGES 0
SET LONG 100000
COLUMN REPORT FORMAT A200
VARIABLE my_rep CLOB;
BEGIN
:my_rep :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (OPID =>444, FORMAT => 'HTML');
END;
/
PL/SQL procedure successfully completed.
SQL> spool stats_task_output.html
SQL> print my_rep;
SQL> spool off;
#
for trace
SQL> conn / as sysdba
SQL> alter session set tracefile_identifier = '10046_auto_stats';
SQL> alter session SET MAX_DUMP_FILE_SIZE = UNLIMITED;
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 2+4+8+16+64+512+1024+2048 /* 3678 */);
SQL>exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS; -- Run auto stats job manually.
-- Once Job completes disable Trace
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('trace',0);
In Oracle Null=null ? ---- False
In Oracle Null is not equal to null :) !!!!!!!!!!
select case when null = null
then 1 ----true
else 2 ----flase
end
from dual ;
CASEWHENNULL=NULLTHEN1----TRUEELSE2----FLASEEND
-----------------------------------------------
2 ---- so false
so you can not equate the null with null ( null=null) --<<--- false
select decode(null,null, 1, 2) from dual; --<<<<--- in decode
DECODE(NULL,NULL,1,2)
---------------------
1 <<<---- true
select case when null = null
then 1
else 2
end "Case",
decode(null,null, 1, 2) "Decode" from dual
SQL> /
Case Decode
---------- ----------
2 1
SELECT * FROM dual WHERE NULL = NULL;
No rows selected.
SELECT * FROM dual WHERE NULL <> NULL;
No rows selected.
select case when null = null
then 1 ----true
else 2 ----flase
end
from dual ;
CASEWHENNULL=NULLTHEN1----TRUEELSE2----FLASEEND
-----------------------------------------------
2 ---- so false
so you can not equate the null with null ( null=null) --<<--- false
select decode(null,null, 1, 2) from dual; --<<<<--- in decode
DECODE(NULL,NULL,1,2)
---------------------
1 <<<---- true
select case when null = null
then 1
else 2
end "Case",
decode(null,null, 1, 2) "Decode" from dual
SQL> /
Case Decode
---------- ----------
2 1
SELECT * FROM dual WHERE NULL = NULL;
No rows selected.
SELECT * FROM dual WHERE NULL <> NULL;
No rows selected.
Subscribe to:
Comments (Atom)
