Oracle database shutdown
alter system checkpoint;
Shutdown abort
Startup restrict
Shutdown immediate
Startup
alter system checkpoint;
Shutdown abort
Startup restrict
Shutdown immediate
Startup
Monday, 30 April 2012
Tuesday, 17 April 2012
Oracle Job metadata
How to find out Oracle Scheduler /Job text
Oracle Job Text
on dbms_metadata.get_ddlORA-31603: object of type PROCOBJ not found in schema "SYS"
select dbms_metadata.get_ddl('PROCOBJ','FILE_WATCHER','SYS') from dual;
ERROR:
ORA-31603: object "FILE_WATCHER" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','MY_TEST_JOB', 'SCOTT') AS job_def FROM dual;
SELECT dbms_metadata.get_ddl('PROCOBJ','JOB_DEFAULT_USER', 'SCOTT') from dual;
==========================
for sys Job Metadata
SQL> select dbms_metadata.get_ddl('PROCOBJ','ANUJ_PROC1','SYS') from dual;
ERROR:
ORA-31603: object "ANUJ_PROC1" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
Created a new user ...
SQL> create user C##TESTUSER identified by TESTUSER;
User created.
SQL> grant dba to C##TESTUSER ;
SQL> exec dbms_scheduler.copy_job('SYS.ANUJ_PROC1','C##TESTUSER.ANUJ_TEST');
PL/SQL procedure successfully completed.
set long 500
SQL> select dbms_metadata.get_ddl('PROCOBJ','ANUJ_TEST','C##TESTUSER') from dual;
DBMS_METADATA.GET_DDL('PROCOBJ','ANUJ_TEST','C##TESTUSER')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"ANUJ_TEST"',
job_type=>'STORED_PROCEDURE', job_action=>
'SYS.ANUJ_PROC'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('06-FEB-2018 05.52.17.000000000 AM -05:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=MINUTELY;INTERVAL=2'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
'Testing...'
);
sys.dbms_scheduler.set_attribute('"ANUJ_TEST"','NLS_ENV','NLS_LANGUAGE=''AMERICA
N'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NL
S_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MON-
RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.
SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=
''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR''
NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NC
HAR_CONV_EXCP=''FALSE''');
COMMIT;
END;
set serveroutput on
DECLARE
v_output CLOB := NULL;
v_owner VARCHAR2 (30) := 'SYS';
v_object_name VARCHAR2 (30) := 'ANUJ_PROC';
BEGIN
-- Note, we don't search for package bodies. We will extract the body
-- along with the package spec.
DBMS_OUTPUT.put_line ('Database DDL For Selected Objects Report');
FOR dd IN (SELECT owner, object_name, object_type FROM dba_objects
WHERE owner LIKE v_owner
AND object_name LIKE v_object_name
AND object_type IN ('PROCEDURE', 'PACKAGE', 'TRIGGER', 'FUNCTION'))
LOOP
SELECT DBMS_METADATA.get_ddl (dd.object_type, dd.object_name, dd.owner) INTO v_output FROM DUAL;
BEGIN
DBMS_OUTPUT.put_line (v_output);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Unable to print code for object');
DBMS_OUTPUT.put_line ( dd.owner
|| '.'
|| dd.object_name
|| ' type: '
|| dd.object_type
);
END;
END LOOP;
END;
/
Database DDL For Selected Objects Report
CREATE OR REPLACE NONEDITIONABLE PROCEDURE "SYS"."ANUJ_PROC" AS
BEGIN
insert
into SYS.TEST values ( 'AAAAAAA',sysdate) ;
commit;
END;
==================
2. Example !!!!
set linesize 300 pagesize 500
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A32 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date'
COL error# FORMAT 999999 HEADING 'Error|Nbr'
TTITLE 'Scheduled Tasks:'
select
log_date,
job_name,
status,
req_start_date,
actual_start_date,
run_duration
from dba_scheduler_job_run_details
where 1=1
and job_name like '%ANUJ%'
-- status <> 'SUCCEEDED'
order by actual_start_date;
job error
log# LOG_DATE job name step name step status code additional information
------- ----------------------------------- ------------------------------ -------------------- ------------ -------- ----------------------------------------
81068 10-FEB-18 04.00.01.149900 PM -05:00 APEX_GATHER_JOB FAILED 6550 ORA-06550: line 1, column 804:
PLS-00201: identifier 'APEX_050100' must
be declared
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
76472 09-FEB-18 04.00.00.950431 PM -05:00 APEX_GATHER_JOB FAILED 6550 ORA-06550: line 1, column 804:
PLS-00201: identifier 'APEX_050100' must
be declared
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
This job is failing
APEX_GATHER_JOB
set linesize 200 pagesize 500
col OWNER for a20
col OBJECT_NAME for a20
select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='APEX_GATHER_JOB';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- -----------------------
SYS APEX_GATHER_JOB JOB
exec dbms_scheduler.copy_job('SYS.APEX_GATHER_JOB','C##TESTUSER.APEX_GATHER_JOB_TEST');
set long 500
select dbms_metadata.get_ddl('PROCOBJ','APEX_GATHER_JOB_TEST','C##TESTUSER') from dual;
DBMS_METADATA.GET_DDL('PROCOBJ','APEX_GATHER_JOB_TEST','C##TESTUSER')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"APEX_GATHER_JOB_TEST"',
job_type=>'PLSQL_BLOCK', job_action=>
'begin dbms_stats.gather_schema_stats(ownname=>"APEX_050100", estimate_percent=>
10, cascade=>TRUE, no_invalidate=> TRUE); end;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('05-FEB-2018 04.00.00.000000000 PM -05:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=DAILY; INTERVAL=1'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE
====
Another example !!!
create table scott.emp_job as select * from scott.emp;
Table created.
CREATE OR REPLACE PROCEDURE scott.emp_log as
begin
delete from scott.emp_job where HIREDATE < sysdate -15;
end emp_log;
Procedure created.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'SCOTT_EMP_LOG_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN scott.emp_log; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
enabled => TRUE); ----<<<<
END;
/
PL/SQL procedure successfully completed.
col OWNER for a20
col JOB_NAME for a20
col enabled for a15
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='SCOTT_EMP_LOG_JOB';
OWNER JOB_NAME ENABLED
-------------------- -------------------- ---------------
SYS SCOTT_EMP_LOG_JOB TRUE
--- for sys job !!!
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','SCOTT_EMP_LOG_JOB', 'SYS') AS job_def FROM dual;
ORA-31603: object "SCOTT_EMP_LOG_JOB" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
======
copy the Job
exec dbms_scheduler.copy_job('SYS.SCOTT_EMP_LOG_JOB','SCOTT.SCOTT_EMP_LOG_JOB_DUP');
exec dbms_scheduler.copy_job('SYS.SCOTT_EMP_LOG_JOB','SCOTT.SCOTT_EMP_LOG_JOB_DUP');
PL/SQL procedure successfully completed.
col OWNER for a20
col JOB_NAME for a30
col enabled for a15
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='SCOTT_EMP_LOG_JOB_DUP';
OWNER JOB_NAME ENABLED
-------------------- ------------------------------ ---------------
SCOTT SCOTT_EMP_LOG_JOB_DUP FALSE
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','SCOTT_EMP_LOG_JOB_DUP', 'SCOTT') AS job_def FROM dual;
JOB_DEF
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"SCOTT_EMP_LOG_JOB_DUP"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN scott.emp_log; END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('09-DEC-2021 03.38.43.238324000 AM -05:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0; bysecond=0;'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
NULL
);
sys.dbms_scheduler.set_attribute('"SCOTT_EMP_LOG_JOB_DUP"','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERI
C_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''dd-mm-yy hh24:mi'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMEST
AMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY
'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
COMMIT;
END;
set linesize 500 pagesize 300
col JOB_ACTION for a40
col SCHEDULE for a30
col SCHEMA_NAME for a25
col LAST_START_DATE for a25
col START_DATE for a25
select owner as schema_name,
job_name,
job_style,
case when job_type is null
then 'PROGRAM'
else job_type end as job_type,
start_date,
case when repeat_interval is null
then schedule_name
else repeat_interval end as schedule,
last_start_date,
next_run_date,
state,
case when job_type is null
then program_name
else job_action end as job_action
from dba_scheduler_jobs
where 1=1
--and JOB_NAME='SCOTT_EMP_LOG_JOB'
order by owner, job_name;
SCHEMA_NAME JOB_NAME JOB_STYLE JOB_TYPE START_DATE SCHEDULE LAST_START_DATE NEXT_RUN_DATE STATE JOB_ACTION
------------------------- ------------------------------ ----------------- ---------------- ------------------------- ------------------------------ ------------------------- --------------------------------------------------------------------------- --------------- ----------------------------------------
SYS SCOTT_EMP_LOG_JOB REGULAR PLSQL_BLOCK 09-DEC-21 03.38.43.238324 freq=hourly; byminute=0; bysec 09-DEC-21 04.00.00.527244 09-DEC-21 05.00.00.533142 AM -05:00 SCHEDULED BEGIN scott.emp_log; END;
AM -05:00 ond=0; AM -05:00
Oracle Table Size
Oracle Table size Oracle Object size How to find oracle Object size
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('SYS', 'COL$', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
286 304 0
Saturday, 14 April 2012
How to create oracle password file
create password file
create Oracle password file
su - oracle
-bash-3.2$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asmadmin)
-bash-3.2$ $ORACLE_HOME/bin/orapwd file=/u01/oracle/product/11.2.0/db_1/dbs/orapwvihaan password=vihaan entries=5
[oracle@wcp12cr2 dbs]$ $ORACLE_HOME/bin/orapwd help=yUsage: orapwd file=<fname> entries=<users> force=<y/n> asm=<y/n>dbuniquename=<dbname> format=<legacy/12> sysbackup=<y/n> sysdg=<y/n>syskm=<y/n> delete=<y/n> input_file=<input-fname>Usage: orapwd describe file=<fname>wherefile - name of password file (required),password - password for SYS will be promptedif not specified at command line.Ignored, if input_file is specified,entries - maximum number of distinct DBA (optional),force - whether to overwrite existing file (optional),asm - indicates that the password to be stored inAutomatic Storage Management (ASM) disk groupis an ASM password. (optional).dbuniquename - unique database name used to identify databasepassword files residing in ASM diskgroup only.Ignored when asm option is specified (optional),format - use format=12 for new 12c features like SYSBACKUP, SYSDG andSYSKM support, longer identifiers, etc.If not specified, format=12 is default (optional),delete - drops a password file. Must specify 'asm','dbuniquename' or 'file'. If 'file' is specified,the file must be located on an ASM diskgroup (optional),sysbackup - create SYSBACKUP entry (optional and requires the12 format). Ignored, if input_file is specified,sysdg - create SYSDG entry (optional and requires the 12 format),Ignored, if input_file is specified,syskm - create SYSKM entry (optional and requires the 12 format),Ignored, if input_file is specified,input_file - name of input password file, from where old userentries will be migrated (optional),describe - describes the properties of specified password file(required).There must be no spaces around the equal-to (=) character.
[FORMAT = {12.2|12}] old format
On filesystem SID $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle format=12
$ORACLE_HOME/bin/orapwd describe file=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwrac1 Password file Description : format=12.2
another example
[oracle@wcp12cr2 ~]$ $ORACLE_HOME/bin/orapwd describe file=/oracle/db/ohome/dbs/orapworcl Password file Description : format=12 ignorecase=N
Now test the connection ....
-bash-3.2$ sqlplus 'sys/vihaan@prim as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 14 11:27:45 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
prim =
prim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = vihaan)
)
)
===============================================================SYS Password Management with RAC and Data Guard (Doc ID 1267828.1)
===
Then ORA-1031 error will be reported in primary alert log:
Wed Nov 17 15:33:48 2010
Error 1031 received logging on to the standby
Errors in file /u01/diag/rdbms/tl/<PRIM_INST_NAME1>/trace/<PRIM_INST_NAME1>_arc3_24629.trc:
ORA-01031: insufficient privileges
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /u01/diag/rdbms/tl/<PRIM_INST_NAME1>/trace/<PRIM_INST_NAME1>_arc3_24629.trc:
Solution:
1. For password stored on the file system:a. Simply copy (scp) the orapw<SID> file from any of the primary instance to all standby instances:
$ scp $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> oracle@<STBY_HOSTNAME1>:<$ORACLE_HOME>/dbs/
$ scp $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> oracle@<STBY_HOSTNAME2>:<$ORACLE_HOME>/dbs/
b. Rename the password file on all standby nodes to match the standby SID.on <STBY_HOSTNAME1>:
mv $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> $ORACLE_HOME/dbs/<STBY_PWD_INST_NAME1>
on <STBY_HOSTNAME2>:
mv $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> $ORACLE_HOME/dbs/<STBY_PWD_INST_NAME2>2. For password stored on the ASM diskgroup:
a. On primary, run srvctl config database -d <db_unique_name> to locate the password file
$ srvctl config database -d <DB_NAME>
Password file: +<DG_NAME>/<DB_NAME>/PASSWORD/<PWD_DB_NAME>.400.940505217b. using asmcmd to copy the file to file system
asmcmd> pwcopy +<DG_NAME>/<DB_NAME>/PASSWORD/<PWD_DB_NAME>.400.940505217 /tmpc. scp the password file to standby hosts
$ scp /tmp/<PWD_DB_NAME>.400.940505217 oracle@<standbyhost>:/tmpd. restore the password file to ASM diskgroup on standby
asmcmd> pwcopy /tmp/<PWD_DB_NAME>.400.940505217 +<DG_NAME>/<DB_NAME>DG/PASSWORD/<PWD_DB_NAME>DGe. modify the password location for standby
$ srvctl modify database -d <db_unique_name> -pwfile +<DG_NAME>/<DB_NAME>DG/PASSWORD/<PWD_DB_NAME>DG
3. It is not required to shutdown/restart any of the primary or standby instances.
4. Log shipping will resume automatically after this.
If not, alter parameter log_archive_dest_state_<n> to defer, then enable.SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';
SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';Grid ASM ASMCMD [+DATA/IBRAC/PWDFILE] > pwcreate --dbuniquename ibrac +DATA/IBRAC/PWDFILE/pwdibrac Very_Complex!1ASMCMD ASMCMD-9465: WARNING: passing password on command line is deprecated ASMCMD [+DATA/IBRAC/PWDFILE] > ls -s Block_Size Blocks Bytes Space Name 512 12 6144 0 pwdibrac => +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 ASMCMD [+DATA/IBRAC/PWDFILE] > ?? ASMCMD [+DATA/IBRAC/PWDFILE] > pwget --dbuniquename ibrac PRCD-1229 : An attempt to access configuration of database ibrac was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.2.0/dbhome_1. A +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 from database srvctl modify database -d ibrac -pwfile +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 srvctl config database -d ibrac|grep -i password Password file: +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 now you can change any password !!! SQL> alter user sys identified by sys ; :) User altered.sqlplus sys/sys@'ibrac2' as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 19 10:49:34 2023 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Aug 19 2023 10:49:29 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionorapwd describe file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}
Password file Description : format=12.2Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)
Sunday, 1 April 2012
Oracle Temp space information
Oracle Space usage
Oracle Temp
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 29 0 29
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
break on report
compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128
spool sort_use
/
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
SELECT s.username, u."USER", u.tablespace,u.contents,u.extents, u.blocks,a.sql_text,u.sqladdr,u.sqlhash
FROM v$session s,v$sort_usage u,v$sql a
WHERE s.SADDR = u.SESSION_ADDR
and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
Oracle Temp
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 29 0 29
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
break on report
compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128
spool sort_use
/
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
SELECT s.username, u."USER", u.tablespace,u.contents,u.extents, u.blocks,a.sql_text,u.sqladdr,u.sqlhash
FROM v$session s,v$sort_usage u,v$sql a
WHERE s.SADDR = u.SESSION_ADDR
and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
Subscribe to:
Posts (Atom)
