Oracle current session wait

oracle session wait

SET TIME ON VERIFY OFF
set linesize 200
COLUMN event FORMAT A30 HEADING 'Event' TRUNCATE
COLUMN wait_state FORMAT A7 HEADING 'State'
COLUMN seconds_in_wait FORMAT 999999999 HEADING 'Time|Waiting'
COLUMN wait_time FORMAT 999999 HEADING 'Time|Waited'
COLUMN p1 FORMAT A20 HEADING 'P1' TRUNCATE
COLUMN p2 FORMAT A20 HEADING 'P2' TRUNCATE
COLUMN p3 FORMAT A20 HEADING 'P3' TRUNCATE
col sid_serial format a15
col username format a20

SELECT vs.sid||','||vs.serial# sid_serial,
vs.username username,
vs.event,
CASE WHEN vs.state = 'WAITING' THEN vs.state
ELSE 'WAITED'
END wait_state,
vs.seconds_in_wait,
vs.wait_time,
DECODE(vs.p1text, NULL, NULL, vs.p1text||'='||vs.p1) p1,
DECODE(vs.p2text, NULL, NULL, vs.p2text||'='||vs.p2) p2,
DECODE(vs.p3text, NULL, NULL, vs.p3text||'='||vs.p3) p3
FROM v$session vs

Oracle Create user script

Create user script

===============

from

Author : Daniel W. Fink




DEFINE schema_owner = &1
-- WHENEVER SQLERROR EXIT FAILURE

SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
COLUMN ddl_string FORMAT A100 WORD_WRAP
COLUMN row_order FORMAT 999 NOPRINT

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

COLUMN rundate FORMAT A8 NEW_VALUE ddl_date NOPRINT
COLUMN dbname FORMAT A10 NEW_VALUE db_name NOPRINT
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT
COLUMN maxtextlength FORMAT 9999 NEW_VALUE max_text_length NOPRINT
COLUMN schemaid NEW_VALUE schema_id NOPRINT

SELECT u.user# schemaid
FROM sys.user$ u
WHERE u.name = UPPER('&&schema_owner')
/

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') rundate
FROM dual
/

SELECT UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')) dbname
FROM dual
/

SELECT 'schema_'||'&&db_name'||'_'||'&&schema_owner'||'_'||'&&ddl_date'||'_ddl.log' spoolname
FROM dual
/

SELECT MAX(LENGTH(s.source)) maxtextlength
FROM sys.obj$ o,
sys.source$ s
WHERE o.owner# = &&schema_id
AND o.obj# = s.obj#
/

SET LINESIZE &&max_text_length

-- SPOOL schema_&&db_name\_&&schema_owner\_&&ddl_date\_ddl.sql
SPOOL schema_ddl.sql

PROMPT WHENEVER SQLERROR EXIT FAILURE
PROMPT WHENEVER OSERROR EXIT FAILURE
PROMPT SPOOL &&spool_name
PROMPT
PROMPT

SELECT 0 row_order, '-- Object Count' ddl_string
FROM dual
UNION
SELECT 1 row_order, '-- '||DECODE(o.type#,
1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM',
6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE',
14, 'TYPE BODY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 42, 'MATERIALIZED VIEW',
43, 'DIMENSION', 56, 'JAVA DATA', 'UNDEFINED')||' -- '||COUNT(1)
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
GROUP BY 1, o.type#
UNION
SELECT 2 row_order, CHR(10)||CHR(10)
FROM dual
/

PROMPT
PROMPT -- Profile Creation
PROMPT

SELECT DBMS_METADATA.GET_DDL('PROFILE', pr.name) ddl_string
FROM (SELECT DISTINCT pi.name
FROM sys.profname$ pi
WHERE pi.name != 'DEFAULT') pr
/

PROMPT
PROMPT -- User Creation
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER', '&&schema_owner')) ddl_string
FROM dual
/

PROMPT
PROMPT -- User Tablespace Quotas
PROMPT
-- This is failing with an error message, causing the script to terminate. No workaround yet.

/*
SELECT CASE
WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&&schema_owner')
ELSE NULL
END ddl_string
FROM sys.ts$ ts,
sys.tsq$ tq
WHERE tq.user# = &&schema_id
AND ts.ts# = tq.ts#
/
*/

PROMPT
PROMPT -- User Role
PROMPT

SELECT /*+ ordered */ 'GRANT "'||u.name||'" TO "'||upper('&&schema_owner')||'"'||
CASE WHEN min(sa.option$) = 1 THEN ' WITH ADMIN OPTION;' ELSE ';' END ddl_string
FROM sys.sysauth$ sa,
sys.user$ u
WHERE sa.grantee# = &&schema_id
AND u.user# = sa.privilege#
AND sa.grantee# != 1
GROUP BY u.name
/

PROMPT
PROMPT -- User System Privileges
PROMPT
-- If the dbms_metadata call for system grants does not find any for the schema owner
-- it fails with an error message, causing the script to terminate. The SELECT is used as a
-- workaround. A TAR has been filed w/Oracle (response 'expected behaviour' - RFE filed)

SELECT CASE
WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&&schema_owner')
ELSE NULL
END ddl_string
FROM sys.sysauth$ sa
WHERE sa.grantee# = &&schema_id
/

PROMPT
PROMPT -- User Object Privileges
PROMPT
-- If the dbms_metadata call for object grants does not find any for the schema owner
-- it fails with an error message, causing the script to terminate. The SELECT is used as a
-- workaround. A TAR has been filed w/Oracle (response 'expected behaviour' - RFE filed)

SELECT CASE
WHEN COUNT(1) != 0 THEN DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&&schema_owner')
ELSE NULL
END ddl_string
FROM sys.objauth$ oa
WHERE oa.grantee# = &&schema_id
/


PROMPT
PROMPT -- Schema Sequences
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('SEQUENCE', o.name,'&&schema_owner')) ddl_string
FROM sys.seq$ s,
sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.obj# = s.obj#
/


PROMPT
PROMPT -- Schema Database Links
PROMPT

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('DB_LINK', l.name,'&&schema_owner')) ddl_string
FROM sys.link$ l
WHERE l.owner# = &&schema_id
/


PROMPT
PROMPT -- Schema Directories
PROMPT

SELECT DBMS_METADATA.GET_DDL('DIRECTORY', o.name, '&&schema_owner') ddl_string
FROM sys.obj$ o,
sys.dir$ d
WHERE o.owner# = &&schema_id
AND o.obj# = d.obj#
/

PROMPT
PROMPT -- Schema Tables
PROMPT

/* Add the BITAND(o.flags, 128) to exclude tables in the recyclebin */

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE', o.name,'&&schema_owner')) ddl_string
FROM sys.obj$ o,
sys.tab$ t
WHERE o.owner# = &&schema_id
AND o.obj# = t.obj#
AND BITAND(o.flags, 128) = 0
/

PROMPT
PROMPT -- Schema Table RI Constraints
PROMPT

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT', oc.name, '&&schema_owner')||'/' ddl_string
FROM sys.con$ oc,
sys.obj$ o,
sys.cdef$ c
WHERE oc.owner# = &&schema_id
AND oc.con# = c.con#
AND c.obj# = o.obj#
AND c.type# = 4
/

PROMPT
PROMPT -- Schema Indexes
PROMPT
--- This is used to exclude primary key and unique key indexes that have already been defined
--- as part of the table generation statement.

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX', o.name,'&&schema_owner')) ddl_string
FROM sys.ind$ i,
sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.obj# = i.obj#
AND bitand(i.property,1) = 1
AND i.type# != 8
/

PROMPT
PROMPT -- Schema Views
PROMPT
-- View extraction is not functioning properly in 9.2. Breaks occur in middle of words (column_names, clauses, etc).
-- Bug has been accepted with Oracle. No response of when/if backport to 9.2.0.5 will be available

-- SELECT REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('VIEW', v.view_name,'&&schema_owner')), '","','", "') ddl_string
-- FROM dba_views v
-- WHERE v.owner = '&&schema_owner'
-- /

-- Here is a workaround version
COLUMN viewname NOPRINT

CREATE GLOBAL TEMPORARY TABLE parsed_view_text
(view_name VARCHAR2(30),
text_id NUMBER,
view_text VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

DECLARE
num_iter NUMBER := 0;
whole_clob CLOB;
parsed_string VARCHAR2(32767);
start_pos NUMBER := 1;
num_chars NUMBER := 3000;

CURSOR view_text_cur IS
SELECT o.name view_name, v.text text, v.textlength text_length, v.cols view_columns
FROM sys.obj$ o,
sys.view$ v
WHERE o.obj# = v.obj#
AND o.owner# = &&schema_id;

view_text_rec view_text_cur%ROWTYPE;

BEGIN

FOR view_text_rec IN view_text_cur
LOOP
whole_clob := TO_CLOB(view_text_rec.text);

DBMS_OUTPUT.PUT_LINE('View Name: '||view_text_rec.view_name||' Text Length :'|| view_text_rec.text_length);
LOOP
IF (view_text_rec.text_length - start_pos) < 3000
THEN
parsed_string := SUBSTR(whole_clob, start_pos);
INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string||CHR(10)||'/');
EXIT;
END if;
parsed_string := SUBSTR(whole_clob, start_pos, 3000);
num_chars := GREATEST(INSTR(parsed_string, ', ', -1, 1), INSTR(parsed_string, ',"', -1, 1),
(INSTR(parsed_string, '),', -1, 1)+1), INSTR(parsed_string, ')', -1, 1));
parsed_string := SUBSTR(whole_clob, start_pos, num_chars);
INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string);
start_pos := start_pos + num_chars;
num_iter := num_iter + 1;
END LOOP;
COMMIT;
start_pos := 1;
num_chars := 3000;
num_iter := 1;
END LOOP;
END;
/

SELECT 0 row_order,
o.name viewname,
'CREATE OR REPLACE FORCE VIEW "'||'&&schema_owner'||'"."'||o.name||'"' ddl_string
FROM sys.obj$ o,
sys.view$ v
WHERE o.obj# = v.obj#
AND o.owner# = &&schema_iD
UNION
SELECT decode(c.col#, 0, to_number(null), c.col#) row_order,
o.name viewname,
CASE WHEN decode(c.col#, 0, to_number(null), c.col#) = 1 THEN '("'||c.name||'",'
WHEN decode(c.col#, 0, to_number(null), c.col#) = v.cols THEN ' "'||c.name||'") AS '
ELSE ' "'||c.name||'",'
END ddl_string
FROM sys.col$ c,
sys.obj$ o,
sys.view$ v
WHERE o.obj# = c.obj#
AND o.owner# = &&schema_id
AND o.obj# = v.obj#
UNION
SELECT pv.text_id row_order,
pv.view_name viewname,
REPLACE(TO_CHAR(pv.view_text), '","', '", "') ddl_string
FROM parsed_view_text pv
ORDER BY viewname, row_order
/

TRUNCATE TABLE parsed_view_text;
DROP TABLE parsed_view_text;

SET LINESIZE 4005
COLUMN ddl_string FORMAT A4000

PROMPT
PROMPT -- Schema Functions
PROMPT

SELECT DBMS_METADATA.GET_DDL('FUNCTION', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.type# = 8
/

PROMPT
PROMPT -- Schema Packages (specs and body)
PROMPT

SELECT DBMS_METADATA.GET_DDL('PACKAGE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.type# = 9
/

PROMPT
PROMPT -- Schema Procedures
PROMPT

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', o.name,'&&schema_owner') ddl_string
FROM sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.type# = 7
/

COLUMN ddl_string FORMAT A125
SET LINESIZE 132

PROMPT
PROMPT -- Schema Synonyms
PROMPT

SELECT 'CREATE SYNONYM "&&schema_owner"."'||o.name||'" FOR "'||s.owner||'"."'||s.name||NVL2(s.node, '@'||s.node||'";', '";') ddl_string
FROM sys.syn$ s,
sys.obj$ o
WHERE o.owner# = &&schema_id
AND o.obj# = s.obj#
AND o.type# = 5
/

PROMPT
PROMPT -- End of ddl
PROMPT

PROMPT SET LINESIZE 132 PAGESIZE 45 FEEDBACK OFF
PROMPT COLUMN line FORMAT 9999
PROMPT COLUMN text FORMAT A40 WORD_WRAP
PROMPT
PROMPT -- Checking for errors
PROMPT
PROMPT SELECT name, type, line, text
PROMPT FROM dba_errors
PROMPT WHERE owner = '&&schema_owner'
PROMPT /

PROMPT SPOOL OFF

SPOOL OFF

UNDEFINE schema_owner

=================================================================================
Or


Use Datapump




SQL> CREATE DIRECTORY anujtmp AS '/tmp';

Directory created.



SQL> CREATE DIRECTORY anujtmp AS '/tmp';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:~> expdp system/sys dumpfile=scott_metadata.dmp schemas=scott content=metadata_only

Export: Release 11.2.0.1.0 - Production on Thu Sep 1 13:13:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=scott_metadata.dmp schemas=scott content=metadata_only
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/opt/app/oracle/admin/orcl/dpdump/scott_metadata.dmp <<<<<<<<<<<<<<<<< default location
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:16:37




or with DIRECTORY



oracle@apt-amd-02:~> expdp system/sys dumpfile=scott_metadata.dmp schemas=scott content=metadata_only DIRECTORY=anujtmp

Export: Release 11.2.0.1.0 - Production on Thu Sep 1 13:33:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=scott_metadata.dmp schemas=scott content=metadata_only DIRECTORY=anujtmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/tmp/scott_metadata.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:35:04

oracle security

http://blog.red-database-security.com/category/oracle-security/

Unix find command on shell script

cat mfind.sh


echo "enter the path:\c"
read path
echo "enter the file name:\c"
read filename
find $path -name $filename -print > x
nl=`wc -l < x`
if [ $nl -eq 0 ]
then
echo File not found
else
cat < x
fi

Unix tar command

tar :Description The "tar" command stands for tape archive.

This command is used to create new archives, list files in existing archives, and extract files from archives.

c - add to archive, using an absolute path
x - extract from archive
t - read the contents of an archive
f - you work with a file, otherwise it's a tape
p - preserve file's permissions and modes
v - verbose, display the result
-I - use an include list
X - use an exclude list





Examples tar cvf /dev/rct0 /home

This command writes a tar archive to the tape device /dev/rct0. It copies the files in the /home directory, and all subdirectories of /home to the tape device.




tar cvf /tmp/home.tar /home

This command creates a tar archive named /tmp/home.tar. The tar command copies the files in the /home directory, and all subdirectories of /home.


tar cvf /tmp/home.tar /home


tar -cvf /users/anuj/b.tar *.sql

tar xvf b.tar -----extract

tar tvf b.tar ----------list the file

tar rvf b.tar *.* appends files at end of archive


Using tar

Using tar to create a file archive on a tape device:

# tar -cvf /dev/rmt0 file.name

or

# tar -cvf /dev/rmt0 .

or for multiple directory hierarchies

# tar -crvf my.tar `find /tmp/junk -type f` `find /var/tmp -type f`

using tar to list the entries in a file archive on a tape device:
# tar -tvf /dev/rmt0

using tar to retrieve a file from a tape device:
# tar -xvf /dev/rmt0 file.name


To list the contents of your archive issue the following command:

tar tf my_arch.tar

To extract the contents of your archive:

tar xvfp my_arch1.tar

Note, that everything will be extracted exactly at the same location from where it was taken. If some directories do no exist then they will be created.


If you want to include into your archive several different directories you might use the following command:

tar cvf my_arch.tar -I include.lst


create an archive file called "sasdir.tar". In this case, the tar command when executed in your home directory will be:

tar -cvf sasdir.tar -C sasdir .
Suppose, you wish to archive the subdirectories "sasdir" and "spssdir" both of which are in your home directory. In this case the tar command executed in your home directory will be:

tar -cvf sasdir.tar -C sasdir . -C ../spssdir .

Oracle Event Numbers info ?

Oracle event info
Oracle event
Event Number
oracle event info



Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

$cd $ORACLE_HOME/rdbms/mesg


oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/mesg> ls -ltr oraus.msg
-rw-r--r-- 1 oracle oinstall 4777005 2009-08-14 22:59 oraus.msg


oracle@apt-amd-02:/opt/app/oracle/product/11.2/rdbms/mesg> tail -10 oraus.msg
// *Document: NO
// *Cause: A LOB update using DBMS_LOB or a related interface was attempted on a HYBRID COLUMNAR compressed table with an unsupported index type. Virtual, functional, domain, and join indexes do not support these operations.
// *Action: Drop the index or do not perform LOB updates on this table.
//

64307, 00000, "hybrid columnar compression is only supported in tablespaces residing on Exadata storage"
// *Document: YES
// *Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
// *Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.



=====================


to set event

sid Session identifier
serial# Session serial number
event no The event number 10000 to 10999
level The level to set. See file $ORACLE_HOME/rdbms/mesg/oraus.msg for details
of events and possible level settings
nm Name, usually just set it to a NULL string ''


sql> exec dbms_system.set_ev(9, 1188, 10046, 12, '');


===============================================


set events can be found through dbms_system.read_ev

set serveroutput on

declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||to_char(event_level));
end if;
end loop;
end;
/

Event 10513 set at level 2

PL/SQL procedure successfully completed.




SQL> alter system set events '10513 trace name context off' ;

System altered.

SQL> declare
event_level number;
begin
for i in 10000..10999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||to_char(event_level));
end if;
end loop;
end;
/

PL/SQL procedure successfully completed.




SQL> ALTER SYSTEM SET event='10235 trace name context forever,level 2','27072 trace name errorstack level 3' COMMENT='TEST' SCOPE=SPFILE;

System altered.



SQL> ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;

System altered.


ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

Oracle Shutdown Immediate Hang

 



oracle Shutdown hang 

or
before shutdown 


prompt  long time wait for shutdown to get completed if sum value is high

after kill session  long transaction , then run this query 

--- dictionary managed tablespaces, extents are maintained in uet$

prompt Is SMON clearing the temp space?  
prompt removed from the table for used extents UET$ ;
prompt placed on the table for free extents FET$ ;

select count(f.block#) "Should Increase >", count(u.block#) "Should Decrease <" from fet$ f ,uet$ u;



--  monitor clean up the session 

select sum(used_ublk) from v$transaction;  
                                                              
                                                              
 event="10513 trace name context forever, level 2" 
                                                              
          
  alter system set events '10513 trace name context forever, level 2' ;

System altered.


on spfile

SQL> alter system set event='10513 trace name context forever, level 2' scope=spfile;

System altered.


SQL> alter system reset event scope=spfile sid='*';

System altered.


alter system reset parameter  sid=’sid|*’



Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.

-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.



SQL> alter system reset control_file_record_keep_time scope=spfile sid='*';

System altered.

****** Don't change the value of a parameter with '' or null to reset it to default ******




col NAME format a20
col DISPLAY_VALUE format a60
select NAME ,DISPLAY_VALUE from  V$SPPARAMETER where NAME like '%even%';


NAME                 DISPLAY_VALUE
-------------------- ------------------------------------------------------------
event                10513 trace name context forever, level 2
xml_db_events



col DISPLAY_VALUE format a60
select NAME ,DISPLAY_VALUE from  V$SYSTEM_PARAMETER where NAME like '%even%';


NAME                 DISPLAY_VALUE
-------------------- ------------------------------------------------------------
event
xml_db_events        enable




shutdown immediate to get complete without transaction recovery.SMON will not do a transaction recovery untill this event 

select SID, SERIAL#, EVENT#, EVENT from v$session  where  SERIAL# >1 ;


Recovery after killing a large transaction
The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well.


alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", 
undoblockstotal-undoblocksdone "ToDo", 
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 
"Finish at" 
FROM v$fast_start_transactions; 

  
  

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
 undoblockstotal-undoblocksdone "ToDo", DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))  "Finish at" 
FROM v$fast_start_transactions; 
  

SELECT state, undoblocksdone, undoblockstotal, cputime   FROM v$fast_start_transactions;

V$FAST_START_TRANSACTIONS contains information both for transactions that the Oracle server is recovering(the STATE is RECOVERING) and for transactions that the Oracle server has recovered(the STATE is RECOVERED). New columns are added to this view as;

• XID: Transaction ID of this transaction
• PXID: Transaction ID of the parent transaction
• RCVSERVERS: Number of servers working on this transaction including the coordinator server(It can be 1 if only SMON is doing the recovery).




- In some versions the cputime does not work (always 0), hence the estimated completion time will not be displayed
- In some cases the v$fast_start_transactions view will not work. If this is the case then you can query the internal data dictionary
view x$ktuxe, the 'ktuxesiz' column represents the remaining number of undo blocks required for rollback:

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'; 


The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well.

First we need to determine the progress SMON is making. It is very possible the SMON process to work with one rollback segment. You can find it using the following query:

col LOGON format a10
col ACTION format a10
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10


SELECT r.NAME "RB Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE, 
v$session.PROGRAM, v$session.module, action 
FROM v$lock l, v$process p, v$rollname r, v$session, 
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments 
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg 
WHERE l.SID = p.pid(+) AND 
v$session.SID = l.SID AND 
TRUNC (l.id1(+)/65536)=r.usn
-- AND l.TYPE(+) = 'TX' AND 
-- l.lmode(+) = 6 
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;

RB Segment Name                   SIZE_MB LOGON             SID    SERIAL# SPID                     PROCESS                  USERNAME   STATUS   OSUSER
------------------------------ ---------- ---------- ---------- ---------- ------------------------ ------------------------ ---------- -------- ------------------------------
MACHINE                                                          PROGRAM    MODULE     ACTION
---------------------------------------------------------------- ---------- ---------- ----------
_SYSSMU1_3780397527$                10.13 3 Days + 2         54      13021                          6325                     SYS        INACTIVE oracle
                                          0:10:54
apt-amd-02                                                       sqlplus@ap sqlplus@ap
                                                                 t-amd-02 ( t-amd-02 (
                                                                 TNS V1-V3) TNS V1-V3)