scripts

Scripts: Monitor Tablespace space growth

January 10, 2013 maintain, oracle No comments

这个脚本可以监控tablespace 空间增长趋势,并且邮件通知到相关domain– 根据TOAD SPACE MANAGEMENT 修改

创建schema,tablespace,table:

create tablespace toad '+ARCH/data/tbs_tst01_00.dbf' size 50G autoextend off;

create user toad identified by toad account unlock;

create user TOAD
  default tablespace toad
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on toad;
-- Grant/Revoke object privileges 
grant select on sys.DBA_DATA_FILES to TOAD;
grant select on sys.DBA_FREE_SPACE to TOAD;
grant select on sys.DBA_TABLESPACES to TOAD;
grant select on sys.V_$FILESTAT to TOAD;
-- Grant/Revoke role privileges 
grant connect to TOAD;
grant resource to TOAD;
-- Grant/Revoke system privileges 
grant create any synonym to TOAD;
grant create public synonym to TOAD;
grant create session to TOAD;
grant select any dictionary to TOAD;
grant select any table to TOAD;
grant unlimited tablespace to TOAD;




-- Create table
create table TOAD_TABLESPACES
(
  tablespace_name VARCHAR2(30) not null,
  mon_date        DATE not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_TABLESPACES
  add constraint TOAD_TABLESPACES_PK primary key (TABLESPACE_NAME, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_TABLESPACES to PUBLIC;


-- Create table
create table TOAD_DATA_FILES
(
  file_id         NUMBER not null,
  mon_date        DATE not null,
  tablespace_name VARCHAR2(30) not null,
  file_name       VARCHAR2(257) not null,
  bytes           NUMBER
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_DATA_FILES
  add constraint TOAD_DATA_FILES_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_DATA_FILES
  add constraint TOAD_DATA_FILES_FK foreign key (TABLESPACE_NAME, MON_DATE)
  references TOAD_TABLESPACES (TABLESPACE_NAME, MON_DATE) on delete cascade;
-- Create/Recreate indexes 
create unique index TOAD_DATA_FILES_UK on TOAD_DATA_FILES (TABLESPACE_NAME, FILE_NAME, MON_DATE)
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_DATA_FILES to PUBLIC;


-- Create table
create table TOAD_FILESTAT
(
  file_id   NUMBER not null,
  mon_date  DATE not null,
  phyrds    CHAR(18) not null,
  phywrts   CHAR(18) not null,
  phyblkrd  CHAR(18) not null,
  phyblkwrt CHAR(18) not null,
  readtim   CHAR(18) not null,
  writetim  CHAR(18) not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_FILESTAT
  add constraint TOAD_FILESTAT_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_FILESTAT
  add constraint TOAD_FILESTAT_FK foreign key (FILE_ID, MON_DATE)
  references TOAD_DATA_FILES (FILE_ID, MON_DATE) on delete cascade;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_FILESTAT to PUBLIC;

-- Create table
create table TOAD_FREE_SPACE
(
  file_id  NUMBER not null,
  mon_date DATE not null,
  bytes    NUMBER not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_FREE_SPACE
  add constraint TOAD_FREE_SPACE_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_FREE_SPACE
  add constraint TOAD_FREE_SPACE_FK foreign key (FILE_ID, MON_DATE)
  references TOAD_DATA_FILES (FILE_ID, MON_DATE) on delete cascade;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_FREE_SPACE to PUBLIC;


-- Create table
create table TOAD_REF
(
  name  VARCHAR2(30),
  value NUMBER
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_REF to PUBLIC;

创建PACKAGE BODY DBMS_JOB:

CREATE OR REPLACE PACKAGE TOAD_SPACEMAN IS
  PROCEDURE CAPTURE; 
  PROCEDURE RESET; 
END; 



CREATE OR REPLACE PACKAGE BODY TOAD_SPACEMAN IS
PROCEDURE RESET AS 
BEGIN 
  /* DELETE DATA FROM ALL TABLES */ 
  DELETE FROM TOAD.TOAD_TABLESPACES; 
  COMMIT; 

  /* LOAD REAL DATA FOR TODAY */ 
  TOAD.TOAD_SPACEMAN.CAPTURE; 

  /* LOAD DUMMY DATA FOR 60 DAYS */ 
  FOR I IN 1..60 LOOP 
    INSERT INTO TOAD.TOAD_TABLESPACES 
    SELECT TABLESPACE_NAME, TRUNC(MON_DATE-I) 
    FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_DATA_FILES 
    SELECT FILE_ID, TRUNC(MON_DATE-I), TABLESPACE_NAME, FILE_NAME, ROUND(BYTES*(1-I/100)) 
    FROM TOAD.TOAD_DATA_FILES WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_FREE_SPACE 
    SELECT FILE_ID, TRUNC(MON_DATE-I), ROUND(BYTES*(1+I/100)) 
    FROM TOAD.TOAD_FREE_SPACE WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_FILESTAT 
    SELECT FILE_ID, TRUNC(MON_DATE-I), PHYRDS, PHYWRTS, 
    PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM 
    FROM TOAD.TOAD_FILESTAT WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

  END LOOP; 

END RESET; 

PROCEDURE CAPTURE AS 

  V_MON_DATE    DATE    := NULL; 
  V_ROW_SIZE    INTEGER := 100000; 
  NumDaysToRemove NUMBER; 
  CURSOR History_Cur IS 
    SELECT Value 
      FROM TOAD.TOAD_REF 
     WHERE Name = 'Space Manager History Limit'; 

BEGIN 
  /* SAVE TODAYS DATE */ 
  V_MON_DATE := TRUNC(SYSDATE); 
  /* RETRIEVE THE NUMBER OF DAYS TO REMOVE */ 
  OPEN History_Cur; 
  FETCH History_Cur INTO NumDaysToRemove; 

  /* DELETE DATA OLDER THAN USER SPECIFIED DAYS */ 
  DELETE FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE < V_MON_DATE - NumDaysToRemove; 
  COMMIT; 

  /* DELETE PRE-EXISTING DATA FOR CURRENT DAY */ 
  DELETE FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE = V_MON_DATE; 
  COMMIT; 
  /* DELETE DATA FOR NONEXISTENT TABLESPACES */ 
  DELETE FROM TOAD.TOAD_TABLESPACES X 
  WHERE NOT EXISTS (SELECT 1 FROM DBA_TABLESPACES D 
  WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME); 
  COMMIT; 

  --/* DELETE DATA FOR NONEXISTENT DATA FILES */ 
  --DELETE FROM TOAD_DATA_FILES X 
  -- WHERE NOT EXISTS (SELECT 1 FROM DBA_DATA_FILES D 
  --                    WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME 
  --                      AND X.FILE_NAME       = D.FILE_NAME); 
  --COMMIT; 

  /* INSERT NEW TABLESPACES FOUND */ 
  INSERT INTO TOAD.TOAD_TABLESPACES 
  SELECT TABLESPACE_NAME, V_MON_DATE 
    FROM DBA_TABLESPACES; 
  COMMIT; 

  /* INSERT NEW DATA FILES FOUND */ 
  INSERT INTO TOAD.TOAD_DATA_FILES 
  SELECT FILE_ID, V_MON_DATE, TABLESPACE_NAME, FILE_NAME, BYTES 
    FROM DBA_DATA_FILES; 
  COMMIT; 

  /* COLLECT CURRENT FREE SPACE DATA */ 
  INSERT INTO TOAD.TOAD_FREE_SPACE 
  SELECT FILE_ID, V_MON_DATE, SUM(BYTES) 
    FROM DBA_FREE_SPACE 
   GROUP BY FILE_ID, TRUNC(SYSDATE); 
  COMMIT; 

  /* COLLECT CURRENT IO RATE DATA */ 
  INSERT INTO TOAD.TOAD_FILESTAT 
  SELECT FILE#, V_MON_DATE, PHYRDS, PHYWRTS, 
         PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM 
    FROM V$FILESTAT; 
  COMMIT; 

END CAPTURE; 
END TOAD_SPACEMAN; 


begin
  sys.dbms_job.submit(job => :job,
                      what => 'BEGIN 
  TOAD.TOAD_SPACEMAN.CAPTURE; 
END;',
                      next_date => to_date('11-01-2013', 'dd-mm-yyyy'),
                      interval => 'TRUNC(SYSDATE+1)');
  commit;
end;
/

修改监控脚本,监控tablespace趋势并MAIL

0 17 * * * sh /home/oracle/tools/tablespace.sh >> /home/oracle/tools/tablespace.log 2>&1


-bash-3.2$ more  /home/oracle/tools/tablespace.sh
#!/bin/bash
. /home/oracle/.profile

sqlplus / as sysdba <<eof
set head off;
set echo off;
set term off;
set pages 0;
set feed off;
SET SQLPROMPT --SQL
#set echo off pagesize 0 feedback off line 125
spool tablespace.csv
@/home/oracle/tools/tablespace.sql

spool off
exit

eof


(cat tablespace.csv| grep ,- |awk -F "," '$NF < 0 ' && uuencode tablespace.csv tablespace.csv )|mailx -s 'USER_space_10.0.1.4' lihang@xxx.com
(cat tablespace.csv| grep ,- |awk -F "," '$NF < 0 ' && uuencode tablespace.csv tablespace.csv )|mailx -s 'USER_space_10.0.1.4' dba@xxx.com

-bash-3.2$ 
-bash-3.2$ 
-bash-3.2$ more /home/oracle/tools/tablespace.sql
set head off;
set echo off;
set term off;
set pages 0;
set feed off;
SET SQLPROMPT --SQL
#set echo off pagesize 0 feedback off line 125

select 'TABLESPACE_NAME,MAX,MEGS_ALLOC,MEGS_USED,MEGS_FREE,MAX_MEGS_FREE,PCT_USED,PCT_FREE,LAST7DAYS_USED_MEGS,7DAYSLATER_REMAIN_MEGS' from dual union all
select  a.tablespace_name
        ||','|| round(maxbytes/1048576) --Max
        ||','|| round(a.bytes_alloc / 1024 / 1024) --megs_alloc
        ||','|| round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) --megs_used
        ||','|| round(nvl(b.bytes_free, 0) / 1024 / 1024) --megs_free
        ||','|| (round(maxbytes/1048576)-(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024))) --Max_megs_free
        ||','|| (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100)) --Pct_used
        ||','|| round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) --Pct_Free
        ||','|| c.last7days_used_megs
        ||','|| (round(maxbytes/1048576)-(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024))-c.last7days_used_megs) --"7DAYSLATER_REMAIN_MEGS"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b,
        (select x.tablespace_name,(x.Megs-y.Megs) last7days_used_megs
from
(
SELECT   tablespace_name, (SUM (a.bytes)-sum(b.bytes))/1024/1024  Megs
    FROM   toad_data_files a,toad_free_space b
   WHERE   a.mon_date = TRUNC (SYSDATE - 1)
     and a.file_id=b.file_id(+)
     and a.mon_date=b.mon_date(+)
GROUP BY   tablespace_name
)x,
(
SELECT   tablespace_name, (SUM (a.bytes)-sum(b.bytes))/1024/1024  Megs
    FROM   toad_data_files a,toad_free_space b
   WHERE   a.mon_date = TRUNC (SYSDATE - 8)
     and a.file_id=b.file_id(+)
     and a.mon_date=b.mon_date(+)
GROUP BY   tablespace_name
) y
where x.tablespace_name=y.tablespace_name
) c
where a.tablespace_name = b.tablespace_name (+)
  and a.tablespace_name = c.tablespace_name (+)
;

同理 可以使用 dba_hist_tbspc_space_usage 实现以上功能 不过貌似dba_hist_tbspc_space_usage要license.

Scripts: dbms_backup_restore modify dbid and db_name

December 5, 2012 Internals, oracle 1 comment

Scripts:dbms_backup_restore_dbid

eg:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @dbid
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2.0176E+10 bytes
Fixed Size		    2237048 bytes
Variable Size		 2483031432 bytes
Database Buffers	 1.7650E+10 bytes
Redo Buffers		   41488384 bytes
Database mounted.
Database opened.

PL/SQL procedure successfully completed.


OLD_NAME
--------------------------------------------------------------------------------
LOUIS

Enter the new Database Name:louisliu
Enter the new Database ID:100


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Convert LOUIS(1153888553) to louisliu(100)

PL/SQL procedure successfully completed.

ControlFile:
=> Change Name:1
=> Change DBID:1
DataFile: /data/oracle/oradata/louis/system01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /data/oracle/oradata/louis/sysaux01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /data/oracle/oradata/louis/undotbs01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /data/oracle/oradata/louis/users01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /data/oracle/oradata/louis/temp01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1

PL/SQL procedure successfully completed.


[oracle@db-2-16 dbs]$ cat initlouis.ora | sed "s/db_name='louis'/db_name='louisliu'/" > initlouisliu.ora
[oracle@db-2-16 dbs]$ export ORACLE_SID=louisliu
[oracle@db-2-16 dbs]$ ora si 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 00:17:20 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2.0176E+10 bytes
Fixed Size		    2237048 bytes
Variable Size		 2483031432 bytes
Database Buffers	 1.7650E+10 bytes
Redo Buffers		   41488384 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database openresetlogs;
alter database openresetlogs
                           *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> alter database open resetlogs;

Database altered.


SQL> select dbid ,name from v$database;

      DBID NAME
---------- ---------------------------
       100 louisliu

SQL> alter system switch logfile;

System altered.

Scripts: 查找每年特定星期x的日期

August 10, 2012 maintain, oracle No comments

11g之前还不支持分区自动interval day 功能,对于需要指定日期的分区需求例如每周2一个分区,可以采用下面脚本实现

select  'alter table END_USER_LOGIN_LOG_1 add partition P'|| TO_CHAR(a,'YYMMDD') ||  

' values less than (TO_DATE(''' || TO_CHAR(a,'YYYY-MM-DD HH24:MI:SS') 

|| ''', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace UDATA01	;'

from (select next_day(to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+(level-1)*7,3) a
from dual
connect by next_day(to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+(level-1)*7,3) <= to_date('2013-12-31','yyyy-mm-dd'))

-------------------------------------

	"alter table END_USER_LOGIN_LOG_1 add partition P120103 values less than (TO_DATE('2012-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120110 values less than (TO_DATE('2012-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120117 values less than (TO_DATE('2012-01-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120124 values less than (TO_DATE('2012-01-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120131 values less than (TO_DATE('2012-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120207 values less than (TO_DATE('2012-02-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120214 values less than (TO_DATE('2012-02-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120221 values less than (TO_DATE('2012-02-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
  
  
  .....

Scripts: Monitor smon rollback progress

July 7, 2012 maintain, oracle No comments

this scripts is used to monitor transaction rolling back progress


——————————————————————————-

— Script: rolling_back.sql
— Purpose: to predict when transactions will finish rolling back
— For: 9.0+

— Copyright: (c) Ixora Pty Ltd
— Author: Steve Adams

——————————————————————————-
spool save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back …
prompt

declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v$transaction t,
sys.v$session s
where
x.inst_id = userenv(‘Instance’) and
x.ktuxesta = ‘ACTIVE’ and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usn number;
xid_slot number;
xid_sqn number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv(‘Instance’) and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = ‘ACTIVE’;
if used_ublk2 < used_ublk1 then sys.dbms_output.put_line( user_name || '''s transaction ' || xid_usn || '.' || xid_slot || '.' || xid_sqn || ' will finish rolling back at approximately ' || to_char( sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24, 'HH24:MI:SS DD-MON-YYYY' ) ); end if; end loop; if user_name is null then sys.dbms_output.put_line('No transactions appear to be rolling back.'); end if; end; / prompt exit;

eg:


SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 14:54:02 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rollback

Looking for transactions that are rolling back …

SYS’s transaction 10.3.25785 will finish rolling back at approximately 14:55:52
07-JUL-2012


SQL> begin
2 for i in 1..1000000 loop
3 insert into t1 values (i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> rollback
2 ;

Rollback complete.

SQL> !date
Sat Jul 7 14:55:59 CST 2012

非常准确的时间估算 cool scripts 🙂

Scripts: check user all granted privileges

June 14, 2012 maintain, oracle No comments

this scripts is used to check user all privileges

set serveroutput on;
DECLARE
v_user VARCHAR2(30) := ‘XXX’;
v_ddl VARCHAR2(2000);
v_status VARCHAR2(32);
BEGIN
— Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,’SQLTERMINATOR’,true);

select dbms_metadata.get_ddl(‘USER’,v_user) INTO v_ddl from dual;
dbms_output.put_line(v_ddl);

— Get the user’s granted quotas
DECLARE
v_quota VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’,v_user)
INTO v_quota from dual;
dbms_output.put_line(v_quota);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No quotas granted’);
END;

–Get the user’s granted roles
DECLARE
v_role VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’,v_user) INTO
v_role from dual;
dbms_output.put_line(v_role);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No roles granted’);
END;

— Get the user’s system grants
DECLARE
v_system VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’,v_user) INTO
v_system from dual;
dbms_output.put_line(v_system);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO system grants’);
END;

— Get the user’s object grants
DECLARE
v_object VARCHAR2(30000);
BEGIN
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’,v_user) INTO
v_object from dual;
dbms_output.put_line(v_object);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO object grants’);
END;

SELECT account_status INTO v_status FROM dba_users WHERE username =
v_user;

IF(v_status = ‘OPEN’) THEN
dbms_output.put_line(‘ALTER USER ‘||v_user||’ ACCOUNT UNLOCK’);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– User not found’);
END;
/

eg:

SQL> set serveroutput on;
SQL> DECLARE
v_user VARCHAR2(30) := ‘LIU’;
v_ddl VARCHAR2(2000);
v_status VARCHAR2(32);
BEGIN
— Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,’SQLTERMINATOR’,true);

select dbms_metadata.get_ddl(‘USER’,v_user) INTO v_ddl from dual;
dbms_output.put_line(v_ddl);

— Get the user’s granted quotas
DECLARE
v_quota VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’,v_user)
INTO v_quota from dual;
dbms_output.put_line(v_quota);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No quotas granted’);
END;

–Get the user’s granted roles
DECLARE
v_role VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’,v_user) INTO
v_role from dual;
dbms_output.put_line(v_role);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No roles granted’);
END;

— Get the user’s system grants
DECLARE
v_system VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’,v_user) INTO
v_system from dual;
dbms_output.put_line(v_system);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO system grants’);
END;

— Get the user’s object grants
DECLARE
v_object VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’,v_user) INTO
v_object from dual;
dbms_output.put_line(v_object);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO object grants’);
END;

SELECT account_status INTO v_status FROM dba_users WHERE username =
v_user;

IF(v_status = ‘OPEN’) THEN
dbms_output.put_line(‘ALTER USER ‘||v_user||’ ACCOUNT UNLOCK’);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– User not found’);
END;
/

CREATE USER “LIU” IDENTIFIED BY VALUES
‘S:416DF25746736369B087A8388B7178AF33DA895C7F7DC8D997455C1BF0E9;9E1A31EC43A0799C

DEFAULT TABLESPACE “UDATA”
TEMPORARY TABLESPACE “TEMP”;
— No quotas granted

GRANT “DBA” TO “LIU”;

GRANT UNLIMITED TABLESPACE TO “LIU”;

GRANT EXECUTE ON “SYS”.”DBMS_SYSTEM” TO “LIU”;
GRANT DELETE ON
“TEST2″.”COUPON” TO “LIU”;
GRANT SELECT ON “TEST2″.”COUPON” TO “LIU”;
GRANT
SELECT ON “SYS”.”T” TO “LIU”;
ALTER USER LIU ACCOUNT UNLOCK

PL/SQL procedure successfully completed.

“hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g

April 20, 2012 Internals, oracle No comments

Execution Environment:

Access Privileges:
Requires to be run connected as SYS schema

Usage:
$ sqlplus
SQL*Plus: Release 9.2.0.2.0 – Production on Mon Nov 11 12:00:06 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 – Production
SQL> set serveroutput on size unlimited
SQL> spool outputfile
SQL> execute hcheck.full


Instructions:

1. Connect as SYS schema in sqlplus
2. Create package hOut as described in Note:101468.1
3. Create package hcheck in SYS schema (Refer the attachments under SCRIPT to Create package hcheck, this is hcheck2.sql and/or hcheck3.sql
4. spool outputfile
5. execute hcheck.full

6. Output will go to the spool file and the session trace file.
The script will report various dictionary related issues
which may or may not be a problem – Any problems reported
should be reviewed by an experienced support analyst as some
reported “problems” may be normal and expected.

PROOFREAD THE SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

Description
To provide a single package which looks for common data dictionary
problems.
The script can be used with Oracle versions 9i onwards (release 2.00 includes 8.1)
It checks consistency of certain dictionary relationships and looks for certain
known issues – certain reported “problems” will be normal and expected.

This script is for use mainly under the guidance of Oracle Support.

eg:

SQL> exec hcheck.full
H.Check Version 9i+/hc3.45
—————————————
Catalog Version 11.2.0.3.0 (1102000300)
—————————————

Catalog Fixed
Procedure Name Version Vs Release Run
—————————— … ———- — ———- —
.- SynLastDDLTim … 1102000300 > 1001000200 : n/a
.- LobNotInObj … 1102000300 > 1000000200 : n/a
.- MissingOIDOnObjCol … 1102000300 <= *All Rel* : Ok .- SourceNotInObj ... 1102000300 > 1002000100 : n/a
.- IndIndparMismatch … 1102000300 > 1102000100 : n/a
.- InvCorrAudit … 1102000300 > 1102000100 : n/a
.- OversizedFiles … 1102000300 <= *All Rel* : Ok .- TinyFiles ... 1102000300 > 900010000 : n/a
.- PoorDefaultStorage … 1102000300 <= *All Rel* : Ok .- PoorStorage ... 1102000300 <= *All Rel* : Ok .- MissTabSubPart ... 1102000300 > 900010000 : n/a
.- PartSubPartMismatch … 1102000300 > 1102000100 : n/a
.- TabPartCountMismatch … 1102000300 <= *All Rel* : Ok .- OrphanedTabComPart ... 1102000300 > 900010000 : n/a
.- ZeroTabSubPart … 1102000300 > 902000100 : n/a
.- MissingSum$ … 1102000300 <= *All Rel* : Ok .- MissingDir$ ... 1102000300 <= *All Rel* : Ok .- DuplicateDataobj ... 1102000300 <= *All Rel* : Ok .- ObjSynMissing ... 1102000300 <= *All Rel* : Ok .- ObjSeqMissing ... 1102000300 <= *All Rel* : Ok .- OrphanedUndo ... 1102000300 <= *All Rel* : Ok .- OrphanedIndex ... 1102000300 <= *All Rel* : Ok .- OrphanedIndexPartition ... 1102000300 <= *All Rel* : Ok .- OrphanedIndexSubPartition ... 1102000300 <= *All Rel* : Ok .- OrphanedTable ... 1102000300 <= *All Rel* : Ok .- OrphanedTablePartition ... 1102000300 <= *All Rel* : Ok .- OrphanedTableSubPartition ... 1102000300 <= *All Rel* : Ok .- MissingPartCol ... 1102000300 <= *All Rel* : Ok .- OrphanedSeg$ ... 1102000300 <= *All Rel* : Ok .- OrphanedIndPartObj# ... 1102000300 > 1101000600 : n/a
.- DuplicateBlockUse … 1102000300 <= *All Rel* : Ok .- HighObjectIds ... 1102000300 > 801060000 : n/a
.- PQsequence … 1102000300 > 800060000 : n/a
.- TruncatedCluster … 1102000300 > 801070000 : n/a
.- FetUet … 1102000300 <= *All Rel* : Ok .- Uet0Check ... 1102000300 <= *All Rel* : Ok .- ExtentlessSeg ... 1102000300 <= *All Rel* : Ok .- SeglessUET ... 1102000300 <= *All Rel* : Ok .- BadInd$ ... 1102000300 <= *All Rel* : Ok .- BadTab$ ... 1102000300 <= *All Rel* : Ok .- BadIcolDepCnt ... 1102000300 > 1101000700 : n/a
.- WarnIcolDep … 1102000300 > 1101000700 : n/a
.- OnlineRebuild$ … 1102000300 <= *All Rel* : Ok .- DropForceType ... 1102000300 > 1001000200 : n/a
.- TrgAfterUpgrade … 1102000300 <= *All Rel* : Ok .- FailedInitJVMRun ... 1102000300 <= *All Rel* : Ok .- TypeReusedAfterDrop ... 1102000300 > 900010000 : n/a
.- Idgen1$TTS … 1102000300 > 900010000 : n/a
.- DroppedFuncIdx … 1102000300 > 902000100 : n/a
.- BadOwner … 1102000300 > 900010000 : n/a
.- UpgCheckc0801070 … 1102000300 <= *All Rel* : Ok .- BadPublicObjects ... 1102000300 <= *All Rel* : Ok .- BadSegFreelist ... 1102000300 <= *All Rel* : Ok .- BadCol# ... 1102000300 > 1001000200 : n/a
.- BadDepends … 1102000300 <= *All Rel* : Ok .- CheckDual ... 1102000300 <= *All Rel* : Ok .- ObjectNames ... 1102000300 <= *All Rel* : Ok .- BadCboHiLo ... 1102000300 <= *All Rel* : Ok .- ChkIotTs ... 1102000300 <= *All Rel* : Ok .- NoSegmentIndex ... 1102000300 <= *All Rel* : Ok .- BadNextObject ... 1102000300 <= *All Rel* : Ok .- OrphanIndopt ... 1102000300 > 902000800 : n/a
.- UpgFlgBitTmp … 1102000300 > 1001000100 : n/a
.- RenCharView … 1102000300 > 1001000100 : n/a
.- Upg9iTab$ … 1102000300 > 902000400 : n/a
.- Upg9iTsInd … 1102000300 > 902000500 : n/a
.- Upg10gInd$ … 1102000300 > 1002000000 : n/a
.- DroppedROTS … 1102000300 <= *All Rel* : Ok .- ChrLenSmtcs ... 1102000300 > 1101000600 : n/a
.- FilBlkZero … 1102000300 <= *All Rel* : Ok .- DbmsSchemaCopy ... 1102000300 <= *All Rel* : Ok Found 0 potential problem(s) and 0 warning(s) PL/SQL procedure successfully completed.

Scripts:sess_uncommited_transactions.sql

March 2, 2012 maintain, oracle No comments

This script is monitoring sessions that are not commited :

— |—————————————————————————-|
— | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
— |—————————————————————————-|
— | DATABASE : Oracle |
— | FILE : sess_uncommited_transactions.sql |
— | CLASS : Session Management |
— | PURPOSE : Query all users with uncommited transactions. |
— | NOTE : As with any code, ensure to test this script in a development |
— | environment before attempting to run it in production. |
— +—————————————————————————-+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 99999 HEADING ‘SID’
COLUMN serial_id FORMAT 99999999 HEADING ‘Serial ID’
COLUMN session_status FORMAT a9 HEADING ‘Status’ JUSTIFY right
COLUMN oracle_username FORMAT a14 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a12 HEADING ‘O/S User’ JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING ‘O/S PID’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a15 HEADING ‘Machine’ JUSTIFY right
COLUMN number_of_undo_records FORMAT 999,999,999,999 HEADING “# Undo Records”
COLUMN used_undo_size FORMAT 999,999,999,999 HEADING “Used Undo Size”

SELECT
s.sid sid
, lpad(s.status,9) session_status
, lpad(s.username,14) oracle_username
, lpad(s.osuser,12) os_username
, lpad(p.spid,7) os_pid
, b.used_urec number_of_undo_records
, b.used_ublk * d.value used_undo_size
, s.program session_program
, lpad(s.machine,15) session_machine
FROM
v$process p
, v$session s
, v$transaction b
, v$parameter d
WHERE
b.ses_addr = s.saddr
AND p.addr (+) = s.paddr
AND s.audsid <> userenv(‘SESSIONID’)
AND d.name = ‘db_block_size’;

Scripts:lock_internal

February 10, 2012 Internals, oracle No comments


set echo off

————————————————–
— @name: lock_internal
— @author: dion cho
— @description: my own version of dba_lock_internal
—————————————————

define __SID = “&1”
define __NAME = “&2”

col handle format a10
col type format a5

select
*
from (
— enqueue
select
sid,
type,
‘(‘||id1||’,’||id2||’)’ as “handle”,
lmode as “mod”,
request as “req”,
(select object_name from dba_objects where object_id = id1 and rownum=1) as name
from v$lock
where sid in (&__SID)
union all
— library cache lock
select
(select sid from v$session where saddr = kgllkuse) as sid,
‘lock’ as “type”,
kgllkhdl||” as “handle”,
kgllkmod as “mod”,
kgllkreq as “req”,
substr(kglnaobj, 1, 60) as name
from sys.x$kgllk
where kgllkuse in (select saddr from v$session where sid in (&__SID))
and (kgllkmod > 0 or kgllkreq > 0)
union all
— library cache pin
select
(select sid from v$session where saddr = kglpnuse) as sid,
‘pin’ as “type”,
kglpnhdl||” as “handle”,
kglpnmod as “mod”,
kglpnreq as “req”,
(select substr(kglnaobj, 1, 60) from sys.x$kglob where kglhdadr = kglpnhdl and rownum = 1) as name
from sys.x$kglpn
where kglpnuse in (select saddr from v$session where sid in (&__SID))
and (kglpnmod > 0 or kglpnreq > 0)
)
where name like ‘&__NAME’
;

set echo on

example:


SQL> @lock_internal 159 %t2%
SQL> set echo off
old 13: where sid in (&__SID)
new 13: where sid in (159)
old 24: where kgllkuse in (select saddr from v$session where sid in (&__SID))
new 24: where kgllkuse in (select saddr from v$session where sid in (159))
old 36: where kglpnuse in (select saddr from v$session where sid in (&__SID))
new 36: where kglpnuse in (select saddr from v$session where sid in (159))
old 39: where name like ‘&__NAME’
new 39: where name like ‘%t2%’

SID TYPE handle mod req NAME
———- —– ———- ———- ———- ————————————————–
159 lock 3C8C9E1C 1 0 select /*+ session_cache */ * from t2
159 lock 393E4FD0 1 0 select /*+ session_cache */ * from t2

Scripts:show_space

January 16, 2012 maintain, oracle No comments

修改过的show_space 支持ASSM


create or replace
procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default ‘MANUAL’,
p_type_1 in varchar2 default ‘TABLE’ ,
p_freespace in varchar2 default ‘N’,
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,’.’) ||
p_num );
end;
begin
p_segname := upper(p_segname_1); — rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;

if (p_type_1 = ‘i’ or p_type_1 = ‘I’) then –rainy changed
p_type := ‘INDEX’;
end if;

if (p_type_1 = ‘t’ or p_type_1 = ‘T’) then –rainy changed
p_type := ‘TABLE’;
end if;

if (p_type_1 = ‘c’ or p_type_1 = ‘C’) then –rainy changed
p_type := ‘CLUSTER’;
end if;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = ‘MANUAL’ or (p_space <> ‘auto’ and p_space <> ‘AUTO’) then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( ‘Free Blocks’, l_free_blks );
end if;
p( ‘Total Blocks’, l_total_blocks );
p( ‘Total Bytes’, l_total_bytes );
p( ‘Unused Blocks’, l_unused_blocks );
p( ‘Unused Bytes’, l_unused_bytes );
p( ‘Last Used Ext FileId’, l_LastUsedExtFileId );
p( ‘Last Used Ext BlockId’, l_LastUsedExtBlockId );
p( ‘Last Used Block’, l_LAST_USED_BLOCK );
if p_freespace = ‘Y’ then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(‘ ‘,50,’*’));
p( ‘0% — 25% free space blocks’, l_fs1_blocks);
p( ‘0% — 25% free space bytes’, l_fs1_bytes);
p( ‘25% — 50% free space blocks’, l_fs2_blocks);
p( ‘25% — 50% free space bytes’, l_fs2_bytes);
p( ‘50% — 75% free space blocks’, l_fs3_blocks);
p( ‘50% — 75% free space bytes’, l_fs3_bytes);
p( ‘75% — 100% free space blocks’, l_fs4_blocks);
p( ‘75% — 100% free space bytes’, l_fs4_bytes);
p( ‘Unused Blocks’, l_unformatted_blocks );
p( ‘Unused Bytes’, l_unformatted_bytes );
p( ‘Total Blocks’, l_full_blocks);
p( ‘Total bytes’, l_full_bytes);

end if;

end;

EXAMPLE:


SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

36729 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

73458 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

146916 rows created.

SQL> commit;

SQL> alter system checkpoint;

System altered.

SQL> exec show_space(‘T1′,’AUTO’,’T’,’Y’);
Total Blocks……………………….4096
Total Bytes………………………..33554432
Unused Blocks………………………0
Unused Bytes……………………….0
Last Used Ext FileId………………..10
Last Used Ext BlockId……………….1104905
Last Used Block…………………….128
*************************************************
0% — 25% free space blocks………….2
0% — 25% free space bytes…………..16384
25% — 50% free space blocks…………0
25% — 50% free space bytes………….0
50% — 75% free space blocks…………0
50% — 75% free space bytes………….0
75% — 100% free space blocks………..2
75% — 100% free space bytes…………16384
Unused Blocks………………………0
Unused Bytes……………………….0
Total Blocks……………………….4020
Total bytes………………………..32931840

PL/SQL procedure successfully completed.

SQL>

Scripts:xplan_extended_display_cursor

January 9, 2012 maintain, oracle No comments

Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;

set echo off verify off termout off
set doc off
doc
— ———————————————————————————————-

— Script: xplan_extended_display_cursor.sql

— Version: 0.9
— December 2011

— Author: Randolf Geist
— oracle-randolf.blogspot.com

— Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
— DBMS_XPLAN.DISPLAY_CURSOR pipelined function for a given SQL_ID and CHILD_NUMBER

— This is a prototype for an extended analysis of the data provided by the
— Runtime Profile (aka. Rowsource Statistics enabled via
— SQL_TRACE = TRUE, STATISTICS_LEVEL = ALL or GATHER_PLAN_STATISTICS hint)
— and reported via the ALLSTATS/MEMSTATS/IOSTATS formatting option of
— DBMS_XPLAN.DISPLAY_CURSOR

— Versions: This utility will work for all versions of 10g and upwards.

— Required: The same access as DBMS_XPLAN.DISPLAY_CURSOR requires. See the documentation
— of DISPLAY_CURSOR for your Oracle version for more information

— The script directly queries
— 1) V$SESSION
— 2) V$SQL_PLAN_STATISTICS_ALL

— Credits: Based on the original XPLAN implementation by Adrian Billington (http://www.oracle-developer.net/utilities.php
— resp. http://www.oracle-developer.net/content/utilities/xplan.zip)
— and inspired by Kyle Hailey’s TCF query (http://dboptimizer.com/2011/09/20/display_cursor/)

— Features: In addition to the PID (The PARENT_ID) and ORD (The order of execution, note that this doesn’t account for the special cases so it might be wrong)
— columns added by Adrian’s wrapper the following additional columns over ALLSTATS are provided:

— A_TIME_SELF : The time taken by the operation itself – this is the operation’s cumulative time minus the direct descendants operation’s cumulative time
— LIO_SELF : The LIOs done by the operation itself – this is the operation’s cumulative LIOs minus the direct descendants operation’s cumulative LIOs
— READS_SELF : The reads performed the operation itself – this is the operation’s cumulative reads minus the direct descendants operation’s cumulative reads
— WRITES_SELF : The writes performed the operation itself – this is the operation’s cumulative writes minus the direct descendants operation’s cumulative writes
— A_TIME_SELF_GRAPH : A graphical representation of A_TIME_SELF relative to the total A_TIME
— LIO_SELF_GRAPH : A graphical representation of LIO_SELF relative to the total LIO
— READS_SELF_GRAPH : A graphical representation of READS_SELF relative to the total READS
— WRITES_SELF_GRAPH : A graphical representation of WRITES_SELF relative to the total WRITES
— LIO_RATIO : Ratio of LIOs per row generated by the row source – the higher this ratio the more likely there could be a more efficient way to generate those rows (be aware of aggregation steps though)
— TCF_GRAPH : Each “+”/”-” sign represents one order of magnitude based on ratio between E_ROWS_TIMES_START and A-ROWS. Note that this will be misleading with Parallel Execution (see E_ROWS_TIMES_START)
— E_ROWS_TIMES_START : The E_ROWS multiplied by STARTS – this is useful for understanding the actual cardinality estimate for related combine child operations getting executed multiple times. Note that this will be misleading with Parallel Execution

— More information including demos can be found online at http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html

— Usage: @xplan_extended_display_cursor.sql [sql_id] [cursor_child_number] [format_option]

— If both the SQL_ID and CHILD_NUMBER are omitted the previously executed SQL_ID and CHILD_NUMBER of the session will be used
— If the SQL_ID is specified but the CHILD_NUMBER is omitted then CHILD_NUMBER 0 is assumed

— This prototype does not support processing multiple child cursors like DISPLAY_CURSOR is capable of
— when passing NULL as CHILD_NUMBER to DISPLAY_CURSOR. Hence a CHILD_NUMBER is mandatory, either
— implicitly generated (see above) or explicitly passed

— The default formatting option for the call to DBMS_XPLAN.DISPLAY_CURSOR is ALLSTATS LAST – extending this output is the primary purpose of this script

— Note: You need a veeery wide terminal setting for this prototype, something like linesize 400 should suffice

— This tool is free but comes with no warranty at all – use at your own risk

#

col plan_table_output format a400
set linesize 400 pagesize 0 tab off

/* ALLSTATS LAST is assumed as the default formatting option for DBMS_XPLAN.DISPLAY_CURSOR */
define default_fo = “ALLSTATS LAST”

column prev_sql_id new_value prev_sql_id
column prev_child_number new_value prev_cn

/* Get the previous command as default
if no SQL_ID / CHILD_NUMBER is passed */
select
prev_sql_id
, prev_child_number
from
v$session
where
sid = userenv(‘sid’)
;

— The following is a hack to use default
— values for defines
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3

select
” as “1”
, ” as “2”
, ” as “3”
from
dual
where
rownum = 0;

column si new_value si
column cn new_value cn
column fo new_value fo

/* Use passed parameters else refer to previous SQL_ID / CHILD_NUMBER
ALLSTATS LAST is default formatting option */
select
nvl(‘&1’, ‘&prev_sql_id’) as si
, coalesce(‘&2’, ‘&prev_cn’, ‘0’) as cn
, nvl(‘&3’, ‘&default_fo’) as fo
from
dual
;

column last new_value last

/* Last or all execution */
select
case
when instr(‘&fo’, ‘LAST’) > 0
then ‘last_’
end as last
from
dual
;

set termout on

with
— The next three queries are based on the original XPLAN wrapper by Adrian Billington
— to determine the PID and ORD information, only slightly modified to deal with
— the 10g special case that V$SQL_PLAN_STATISTICS_ALL doesn’t include the ID = 0 operation
— and starts with 1 instead for Rowsource Statistics
sql_plan_data as
(
select
id
, parent_id
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
),
hierarchy_data as
(
select
id
, parent_id
from
sql_plan_data
start with
id in
(
select
id
from
sql_plan_data p1
where
not exists
(
select
null
from
sql_plan_data p2
where
p2.id = p1.parent_id
)
)
connect by
prior id = parent_id
order siblings by
id desc
),
ordered_hierarchy_data as
(
select
id
, parent_id as pid
, row_number() over (order by rownum desc) as oid
, max(id) over () as maxid
, min(id) over () as minid
from
hierarchy_data
),
— The following query uses the MAX values
— rather than taking the values of PLAN OPERATION_ID = 0 (or 1 for 10g V$SQL_PLAN_STATISTICS_ALL)
— for determining the grand totals

— This is because queries that get cancelled do not
— necessarily have yet sensible values in the root plan operation

— Furthermore with Parallel Execution the elapsed time accumulated
— with the ALLSTATS option for operations performed in parallel
— will be greater than the wallclock elapsed time shown for the Query Coordinator

— Note that if you use GATHER_PLAN_STATISTICS with the default
— row sampling frequency the (LAST_)ELAPSED_TIME will be very likely
— wrong and hence the time-based graphs and self-statistics will be misleading

— Similar things might happen when cancelling queries

— For queries running with STATISTICS_LEVEL = ALL (or sample frequency set to 1)
— the A-TIME is pretty reliable
totals as
(
select
max(&last.cu_buffer_gets + &last.cr_buffer_gets) as total_lio
, max(&last.elapsed_time) as total_elapsed
, max(&last.disk_reads) as total_reads
, max(&last.disk_writes) as total_writes
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
),
— The totals for the direct descendants of an operation
— These are required for calculating the work performed
— by a (parent) operation itself
— Basically this is the SUM grouped by PARENT_ID
direct_desc_totals as
(
select
sum(&last.cu_buffer_gets + &last.cr_buffer_gets) as lio
, sum(&last.elapsed_time) as elapsed
, sum(&last.disk_reads) as reads
, sum(&last.disk_writes) as writes
, parent_id
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
group by
parent_id
),
— Putting the three together
— The statistics, direct descendant totals plus totals
extended_stats as
(
select
stats.id
, stats.parent_id
, stats.&last.elapsed_time as elapsed
, (stats.&last.cu_buffer_gets + stats.&last.cr_buffer_gets) as lio
, stats.&last.starts as starts
, stats.&last.output_rows as a_rows
, stats.cardinality as e_rows
, stats.&last.disk_reads as reads
, stats.&last.disk_writes as writes
, ddt.elapsed as ddt_elapsed
, ddt.lio as ddt_lio
, ddt.reads as ddt_reads
, ddt.writes as ddt_writes
, t.total_elapsed
, t.total_lio
, t.total_reads
, t.total_writes
from
v$sql_plan_statistics_all stats
, direct_desc_totals ddt
, totals t
where
stats.sql_id=’&si’
and stats.child_number = &cn
and ddt.parent_id (+) = stats.id
),
— Further information derived from above
derived_stats as
(
select
id
, greatest(elapsed – nvl(ddt_elapsed , 0), 0) as elapsed_self
, greatest(lio – nvl(ddt_lio, 0), 0) as lio_self
, trunc((greatest(lio – nvl(ddt_lio, 0), 0)) / nullif(a_rows, 0)) as lio_ratio
, greatest(reads – nvl(ddt_reads, 0), 0) as reads_self
, greatest(writes – nvl(ddt_writes,0) ,0) as writes_self
, total_elapsed
, total_lio
, total_reads
, total_writes
, trunc(log(10, nullif(starts * e_rows / nullif(a_rows, 0), 0))) as tcf_ratio
, starts * e_rows as e_rows_times_start
from
extended_stats
),
/* Format the data as required */
formatted_data1 as
(
select
id
, lio_ratio
, total_elapsed
, total_lio
, total_reads
, total_writes
, to_char(numtodsinterval(round(elapsed_self / 10000) * 10000 / 1000000, ‘SECOND’)) as e_time_interval
/* Imitate the DBMS_XPLAN number formatting */
, case
when lio_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when lio_self >= 10000000000000000000 then to_char(lio_self/1000000000000000000, ‘FM99999’) || ‘E’
when lio_self >= 10000000000000000 then to_char(lio_self/1000000000000000, ‘FM99999’) || ‘P’
when lio_self >= 10000000000000 then to_char(lio_self/1000000000000, ‘FM99999’) || ‘T’
when lio_self >= 10000000000 then to_char(lio_self/1000000000, ‘FM99999’) || ‘G’
when lio_self >= 10000000 then to_char(lio_self/1000000, ‘FM99999’) || ‘M’
when lio_self >= 100000 then to_char(lio_self/1000, ‘FM99999’) || ‘K’
else to_char(lio_self, ‘FM99999’) || ‘ ‘
end as lio_self_format
, case
when reads_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when reads_self >= 10000000000000000000 then to_char(reads_self/1000000000000000000, ‘FM99999’) || ‘E’
when reads_self >= 10000000000000000 then to_char(reads_self/1000000000000000, ‘FM99999’) || ‘P’
when reads_self >= 10000000000000 then to_char(reads_self/1000000000000, ‘FM99999’) || ‘T’
when reads_self >= 10000000000 then to_char(reads_self/1000000000, ‘FM99999’) || ‘G’
when reads_self >= 10000000 then to_char(reads_self/1000000, ‘FM99999’) || ‘M’
when reads_self >= 100000 then to_char(reads_self/1000, ‘FM99999’) || ‘K’
else to_char(reads_self, ‘FM99999’) || ‘ ‘
end as reads_self_format
, case
when writes_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when writes_self >= 10000000000000000000 then to_char(writes_self/1000000000000000000, ‘FM99999’) || ‘E’
when writes_self >= 10000000000000000 then to_char(writes_self/1000000000000000, ‘FM99999’) || ‘P’
when writes_self >= 10000000000000 then to_char(writes_self/1000000000000, ‘FM99999’) || ‘T’
when writes_self >= 10000000000 then to_char(writes_self/1000000000, ‘FM99999’) || ‘G’
when writes_self >= 10000000 then to_char(writes_self/1000000, ‘FM99999’) || ‘M’
when writes_self >= 100000 then to_char(writes_self/1000, ‘FM99999’) || ‘K’
else to_char(writes_self, ‘FM99999’) || ‘ ‘
end as writes_self_format
, case
when e_rows_times_start >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when e_rows_times_start >= 10000000000000000000 then to_char(e_rows_times_start/1000000000000000000, ‘FM99999’) || ‘E’
when e_rows_times_start >= 10000000000000000 then to_char(e_rows_times_start/1000000000000000, ‘FM99999’) || ‘P’
when e_rows_times_start >= 10000000000000 then to_char(e_rows_times_start/1000000000000, ‘FM99999’) || ‘T’
when e_rows_times_start >= 10000000000 then to_char(e_rows_times_start/1000000000, ‘FM99999’) || ‘G’
when e_rows_times_start >= 10000000 then to_char(e_rows_times_start/1000000, ‘FM99999’) || ‘M’
when e_rows_times_start >= 100000 then to_char(e_rows_times_start/1000, ‘FM99999’) || ‘K’
else to_char(e_rows_times_start, ‘FM99999’) || ‘ ‘
end as e_rows_times_start_format
, rpad(‘ ‘, nvl(round(elapsed_self / nullif(total_elapsed, 0) * 12), 0) + 1, ‘@’) as elapsed_self_graph
, rpad(‘ ‘, nvl(round(lio_self / nullif(total_lio, 0) * 12), 0) + 1, ‘@’) as lio_self_graph
, rpad(‘ ‘, nvl(round(reads_self / nullif(total_reads, 0) * 12), 0) + 1, ‘@’) as reads_self_graph
, rpad(‘ ‘, nvl(round(writes_self / nullif(total_writes, 0) * 12), 0) + 1, ‘@’) as writes_self_graph
, ‘ ‘ ||
case
when tcf_ratio > 0
then rpad(‘-‘, tcf_ratio, ‘-‘)
else rpad(‘+’, tcf_ratio * -1, ‘+’)
end as tcf_graph
from
derived_stats
),
/* The final formatted data */
formatted_data as
(
select
/*+ Convert the INTERVAL representation to the A-TIME representation used by DBMS_XPLAN
by turning the days into hours */
to_char(to_number(substr(e_time_interval, 2, 9)) * 24 + to_number(substr(e_time_interval, 12, 2)), ‘FM900’) ||
substr(e_time_interval, 14, 9)
as a_time_self
, a.*
from
formatted_data1 a
),
/* Combine the information with the original DBMS_XPLAN output */
xplan_data as (
select
x.plan_table_output
, o.id
, o.pid
, o.oid
, o.maxid
, o.minid
, a.a_time_self
, a.lio_self_format
, a.reads_self_format
, a.writes_self_format
, a.elapsed_self_graph
, a.lio_self_graph
, a.reads_self_graph
, a.writes_self_graph
, a.lio_ratio
, a.tcf_graph
, a.total_elapsed
, a.total_lio
, a.total_reads
, a.total_writes
, a.e_rows_times_start_format
, x.rn
from
(
select /* Take advantage of 11g table function dynamic sampling */
/*+ dynamic_sampling(dc, 2) */
/* This ROWNUM determines the order of the output/processing */
rownum as rn
, plan_table_output
from
table(dbms_xplan.display_cursor(‘&si’,&cn, ‘&fo’)) dc
) x
, ordered_hierarchy_data o
, formatted_data a
where
o.id (+) = case
when regexp_like(x.plan_table_output, ‘^\|[\* 0-9]+\|’)
then to_number(regexp_substr(x.plan_table_output, ‘[0-9]+’))
end
and a.id (+) = case
when regexp_like(x.plan_table_output, ‘^\|[\* 0-9]+\|’)
then to_number(regexp_substr(x.plan_table_output, ‘[0-9]+’))
end
)
/* Inject the additional data into the original DBMS_XPLAN output
by using the MODEL clause */
select
plan_table_output
from
xplan_data
model
dimension by (rn as r)
measures
(
cast(plan_table_output as varchar2(4000)) as plan_table_output
, id
, maxid
, minid
, pid
, oid
, a_time_self
, lio_self_format
, reads_self_format
, writes_self_format
, e_rows_times_start_format
, elapsed_self_graph
, lio_self_graph
, reads_self_graph
, writes_self_graph
, lio_ratio
, tcf_graph
, total_elapsed
, total_lio
, total_reads
, total_writes
, greatest(max(length(maxid)) over () + 3, 6) as csize
, cast(null as varchar2(128)) as inject
, cast(null as varchar2(4000)) as inject2
)
rules sequential order
(
/* Prepare the injection of the OID / PID info */
inject[r] = case
/* MINID/MAXID are the same for all rows
so it doesn’t really matter
which offset we refer to */
when id[cv(r)+1] = minid[cv(r)+1]
or id[cv(r)+3] = minid[cv(r)+3]
or id[cv(r)-1] = maxid[cv(r)-1]
then rpad(‘-‘, csize[cv()]*2, ‘-‘)
when id[cv(r)+2] = minid[cv(r)+2]
then ‘|’ || lpad(‘Pid |’, csize[cv()]) || lpad(‘Ord |’, csize[cv()])
when id[cv()] is not null
then ‘|’ || lpad(pid[cv()] || ‘ |’, csize[cv()]) || lpad(oid[cv()] || ‘ |’, csize[cv()])
end
/* Prepare the injection of the remaining info */
, inject2[r] = case
when id[cv(r)+1] = minid[cv(r)+1]
or id[cv(r)+3] = minid[cv(r)+3]
or id[cv(r)-1] = maxid[cv(r)-1]
then rpad(‘-‘,
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
14 else 0 end /* A_TIME_SELF */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
11 else 0 end /* LIO_SELF */ +
case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
11 else 0 end /* READS_SELF */ +
case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
11 else 0 end /* WRITES_SELF */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
14 else 0 end /* A_TIME_SELF_GRAPH */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
14 else 0 end /* LIO_SELF_GRAPH */ +
case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
14 else 0 end /* READS_SELF_GRAPH */ +
case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
14 else 0 end /* WRITES_SELF_GRAPH */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
11 else 0 end /* LIO_RATIO */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
11 else 0 end /* TCF_GRAPH */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
11 else 0 end /* E_ROWS_TIMES_START */
, ‘-‘)
when id[cv(r)+2] = minid[cv(r)+2]
then case when total_elapsed[cv(r)+2] > 0 then
lpad(‘A-Time Self |’ , 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘Bufs Self |’ , 11) end ||
case when total_reads[cv(r)+2] > 0 then
lpad(‘Reads Self|’ , 11) end ||
case when total_writes[cv(r)+2] > 0 then
lpad(‘Write Self|’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘A-Ti S-Graph |’, 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘Bufs S-Graph |’, 14) end ||
case when total_reads[cv(r)+2] > 0 then
lpad(‘Reads S-Graph|’, 14) end ||
case when total_writes[cv(r)+2] > 0 then
lpad(‘Write S-Graph|’, 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘LIO Ratio |’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘TCF Graph |’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘E-Rows*Sta|’ , 11) end
when id[cv()] is not null
then case when total_elapsed[cv()] > 0 then
lpad(a_time_self[cv()] || ‘ |’, 14) end ||
case when total_lio[cv()] > 0 then
lpad(lio_self_format[cv()] || ‘|’, 11) end ||
case when total_reads[cv()] > 0 then
lpad(reads_self_format[cv()] || ‘|’, 11) end ||
case when total_writes[cv()] > 0 then
lpad(writes_self_format[cv()] || ‘|’, 11) end ||
case when total_elapsed[cv()] > 0 then
rpad(elapsed_self_graph[cv()], 13) || ‘|’ end ||
case when total_lio[cv()] > 0 then
rpad(lio_self_graph[cv()], 13) || ‘|’ end ||
case when total_reads[cv()] > 0 then
rpad(reads_self_graph[cv()], 13) || ‘|’ end ||
case when total_writes[cv()] > 0 then
rpad(writes_self_graph[cv()], 13) || ‘|’ end ||
case when total_lio[cv()] > 0 then
lpad(lio_ratio[cv()] || ‘ |’, 11) end ||
case when total_elapsed[cv()] > 0 then
rpad(tcf_graph[cv()], 9) || ‘ |’ end ||
case when total_elapsed[cv()] > 0 then
lpad(e_rows_times_start_format[cv()] || ‘|’, 11) end
end
/* Putting it all together */
, plan_table_output[r] = case
when inject[cv()] like ‘—%’
then inject[cv()] || plan_table_output[cv()] || inject2[cv()]
when inject[cv()] is present
then regexp_replace(plan_table_output[cv()], ‘\|’, inject[cv()], 1, 2) || inject2[cv()]
else plan_table_output[cv()]
end
)
order by
r
;

undefine default_fo
undefine prev_sql_id
undefine prev_cn
undefine last
undefine si
undefine cn
undefine fo
undefine 1
undefine 2
undefine 3

col plan_table_output clear
col prev_sql_id clear
col prev_child_number clear
col si clear
col cn clear
col fo clear
col last clear

EXAMPLE:

[oracle@testdb2 ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.5.0 – Production on Mon Jan 9 12:58:57 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;
Session altered.

SQL>

Session altered.

SQL>
SQL> Alter session set sql_trace=true;

Session altered.

SQL> select count (*) from dba_objects;

COUNT(*)
———-
31094

SQL> @ xplan_extended_display_cursor 2jf3kh52jy9fd 0
SQL_ID 2jf3kh52jy9fd, child number 0
————————————-
select count (*) from dba_objects

Plan hash value: 2476103605

————————————————————————————————————————————————————————————————————————————–
| Id | Pid | Ord | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | A-Time Self |Bufs Self |A-Ti S-Graph |Bufs S-Graph |LIO Ratio |TCF Graph |E-Rows*Sta|
————————————————————————————————————————————————————————————————————————————–
| 0 | | 13 | SELECT STATEMENT | | 1 | | 1 |00:00:00.77 | 12166 | | | | 00:00:00.00 | 0 | | | 0 | | |
| 1 | 0 | 12 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.77 | 12166 | | | | 00:00:00.02 | 0 | | | 0 | | 1 |
| 2 | 1 | 11 | VIEW | DBA_OBJECTS | 1 | 25931 | 31094 |00:00:00.75 | 12166 | | | | 00:00:00.09 | 0 | @ | | 0 | | 25931 |
| 3 | 2 | 10 | UNION-ALL | | 1 | | 31094 |00:00:00.66 | 12166 | | | | 00:00:00.12 | 0 | @@ | | 0 | | |
|* 4 | 3 | 6 | FILTER | | 1 | | 31078 |00:00:00.53 | 12159 | | | | 00:00:00.08 | 0 | @ | | 0 | | |
|* 5 | 4 | 3 | HASH JOIN | | 1 | 32255 | 32291 |00:00:00.36 | 443 | 1593K| 1593K| 1477K (0)| 00:00:00.25 | 0 | @@@@ | | 0 | | 32255 |
| 6 | 5 | 1 | TABLE ACCESS FULL | USER$ | 1 | 65 | 66 |00:00:00.01 | 6 | | | | 00:00:00.00 | 6 | | | 0 | | 65 |
|* 7 | 5 | 2 | TABLE ACCESS FULL | OBJ$ | 1 | 32255 | 32291 |00:00:00.11 | 437 | | | | 00:00:00.11 | 437 | @@ | | 0 | | 32255 |
|* 8 | 4 | 5 | TABLE ACCESS BY INDEX ROWID| IND$ | 5855 | 1 | 5184 |00:00:00.10 | 11716 | | | | 00:00:00.06 | 5859 | @ | @@@@@@ | 1 | | 5855 |
|* 9 | 8 | 4 | INDEX UNIQUE SCAN | I_IND1 | 5855 | 1 | 5855 |00:00:00.04 | 5857 | | | | 00:00:00.04 | 5857 | @ | @@@@@@ | 1 | | 5855 |
|* 10 | 3 | 9 | HASH JOIN | | 1 | 16 | 16 |00:00:00.01 | 7 | 1517K| 1517K| 606K (0)| 00:00:00.00 | 0 | | | 0 | | 16 |
| 11 | 10 | 7 | INDEX FULL SCAN | I_LINK1 | 1 | 16 | 16 |00:00:00.01 | 1 | | | | 00:00:00.00 | 1 | | | 0 | | 16 |
| 12 | 10 | 8 | TABLE ACCESS FULL | USER$ | 1 | 65 | 66 |00:00:00.01 | 6 | | | | 00:00:00.00 | 6 | | | 0 | | 65 |
————————————————————————————————————————————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

4 – filter(((“O”.”TYPE#”<>1 AND “O”.”TYPE#”<>10) OR (“O”.”TYPE#”=1 AND =1)))
5 – access(“O”.”OWNER#”=”U”.”USER#”)
7 – filter((“O”.”LINKNAME” IS NULL AND BITAND(“O”.”FLAGS”,128)=0 AND “O”.”NAME”<>‘_NEXT_OBJECT’ AND
“O”.”NAME”<>‘_default_auditing_options_’))
8 – filter((“I”.”TYPE#”=1 OR “I”.”TYPE#”=2 OR “I”.”TYPE#”=3 OR “I”.”TYPE#”=4 OR “I”.”TYPE#”=6 OR “I”.”TYPE#”=7 OR
“I”.”TYPE#”=9))
9 – access(“I”.”OBJ#”=:B1)
10 – access(“L”.”OWNER#”=”U”.”USER#”)

36 rows selected.