eoifcfg iflist -p
eth0 192.168.76.128 PRIVATE
LD_LIBRARY_PATH =$ORACLE_HOME/lib
set colsep ,
set linesize 10000000
spool a.csv
####################################
Query
set showplan on
go
set noexec on
go
select count(*) from ACCOUNT
####################################
$ touch -d "2010-01-31 16:42" abc.sql
#####enonemous###########
du -s * |sort -n
stty columns 120
connect
sys/ora3asm@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gvcrlpldb4-vip)(Port=1526))(CONNECT_DATA=(SERVICE_NAME=+ASM1)(INSTANCE_NAME=+ASM1)(UR=A)))" as sysdba
expdp \"/ as sysdba\" DIRECTORY=${DUMP_DIR} DUMPFILE=EXP_FULL_${database}_${d}_%U.dmp LOGFILE=EXP_FULL_${database}_${d}.log FULL=Y PARALLEL=4
mail -s “test file” -c cc_emails to_emails << ++
> this is a test!
> ++
####################
delete noprompt expired archivelog all;
delete noprompt expired backupset;
#########scping in back ground##########
scp -p ......
^Z
bg
#########################################
resources:
crs_resources.ksh |grep vip
crs_relocate ora.pmccbd04-lmatora4-dr.vip
###########################
dbascapp5:/opt/oracle/GCAGENT/agent10g/bin>>./emctl config agent listtargets
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
[dbascapp5.is.bear.com:3872, oracle_emd]
[+ASM2_dbascapp5.is.bear.com, osm_instance]
[LISTENER_DBASCAPP5_dbascapp5.is.bear.com, oracle_listener]
[dbascapp5_cluster, cluster]
[dbascapp5.is.bear.com, host]
[PWHRMN20_dbascapp5_cluster_PWHRMN22, oracle_database]
[PWHRMN20_dbascapp5_cluster, rac_database]
#####################################
select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';' old_passworold_password from dba_users where username = '&user';
select name,open_mode,database_role,switchover_status from v$database;
#####################FAILED LOGIN ATTEMPTS########################
select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from dba_audit_session where returncode<>0 ANd username='ABC' group by username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS');
select OS_USERNAME,USERNAME,TERMINAL,userhost,count(*) from dba_audit_session where RETURNCODE<>0 and timestamp > sysdate - 1 and username='&username' group by OS_USERNAME,USERNAME,TERMINAL,userhost
select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') TIME from dba_audit_session where returncode<>0 ANd username='TRW_CONTAINER' group by username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') order by time;
select count(*),username,USERHOST,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') TIME from dba_audit_session where returncode<>0 ANd username='BPM_QA_THELIBRARY' and to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') like '16-FEB-2011%' group by username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),USERHOST order by time asc
select
os_username,
username,
terminal,
to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')
from
dba_audit_trail;
-----Invalid logins
Select userhost,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS'),action_name,logoff_time from DBA_AUDIT_SESSION where username ='NEOKRPRD_RT' and timestamp like '26-JAN-10' order by timestamp desc ;
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where status='INACTIVE';
Kill session procedure.
create or replace procedure kill_session
(pn_sid number
,pn_serial number)
as
lv_user varchar2(30);
begin
select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
if lv_user is not null and lv_user not in ('SYS','SYSTEM') then
execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
else
raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
end if;
end;
/
exec kill_session(250,135,2);
select sid,serial#,osuser,machine,terminal,BLOCKING_SESSION,SCHEMANAME,USERNAME,command from v$session where
select TIMESTAMP, OS_USERNAME, USERNAME, USERHOST, ACTION_NAME, RETURNCODE from dba_audit_trail
where username='EMMI_MX_TKY_STP' and RETURNCODE ='1017'
set linesize 150
col userhost for a70
SET MARKUP HTML ON SPOOL ON
spool AUDIT_REPORT_QCRBD20.html
select distinct USERID, USERHOST from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -21);
SPOOL OFF;
SET MARKUP HTML OFF
#######################################################Standby###################################
deleting standby applied archives from rman
select inst_id,recovery_mode from gv$archive_dest_status;
select 'delete noprompt archivelog sequence between '||min(sequence#)||' and '||max(sequence#)||' thread '||thread#||';' from v$archived_log where applied='YES' and deleted='NO' group by thread#, applied, deleted;
select process, status , thread# , sequence#, block#, blocks from gv$managed_standby ;
select thread#,sequence#,block#,blocks,delay_mins from v$managed_standby where process like 'MRP%';
Select thread#, max(sequence#), applied from v$archived_log where applied = 'YES' group by thread#, applied;
select thread#,max(sequence#) ,applied from v$archived_log group by thread#,applied order by 1;
select * from v$dataguard_status;
select max(archived.sequence#) primary, max(applied.sequence#) standby, archived.thread# thread from v$archived_log archived, v$archived_log applied where archived.thread#=applied.thread# and archived.archived='YES'
and applied.applied='YES'group by archived.thread#;
alter database register or replace physical logfile '/tmp/archive_2_4073.arc';
THREAD# MAX(SEQUENCE#) APPLIED
---------- -------------- ---------
1 998 YES
3 808 YES
2 898 YES
delete archivelog until sequence 990 thread 1;
delete archivelog until sequence 790 thread 3;
delete archivelog until sequence 890 thread 2
select SPACE_LIMIT/1024/1024 "Limit MB",round(SPACE_USED/1024/1024) "Used MB" from v$recovery_file_dest;
select * from v$archive_gap;
delete force noprompt archivelog until time 'trunc(sysdate-1/1440)' backed up 1 times to device type sbt;
SELECT name FROM v$archived_log WHERE sequence# in (1304);
---------to check archivegap:
SELECT high.thread#, "LowGap#", "HighGap#"
FROM
(
SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
(
SELECT a.thread#, a.sequence#
FROM
(
SELECT *
FROM v$archived_log
) a,
(
SELECT thread#, MAX(next_change#)gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread# = b.thread#
AND a.next_change# > gap1
)
GROUP BY thread#
) high,
(
SELECT thread#, MIN(sequence#) "LowGap#"
FROM
(
SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread#;
#################################################################
SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;
select 'alter user ' || name || ' identified by values ''' || password || ''';' from SYS.USER$ where NAME in (' ');
spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off
spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';'
from dba_users;
spool off
####################################################
Long running quries
-------------------
set linesize 170
col message for a40
col username for a10
select instance_name,host_name,inst_id from gv$instance;
select inst_id,sid,serial#,machine,USERNAME,OSUSER from gv$session where sid=1305;
SELECT username,inst_id,SID, SERIAL#, message,to_char(start_time,'mon-dd-yyyy hh24:mi:ss') start_time,to_char(LAST_UPDATE_TIME,'mon-dd-yyyy hh24:mi:ss') last_update, SOFAR, TOTALWORK,ROUND (SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM gV$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR <> tOTALWORK;
SELECT username,SID, SERIAL#, message,start_time,LAST_UPDATE_TIME , SOFAR, TOTALWORK,ROUND (SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> tOTALWORK;
select s.username as "USER", s.machine as "CLIENT MACHINE", sa.sql_text as "LONG RUNNING SQL", sa.elapsed_time/
1000/1000/60 "EXECUTION TIME IN MINUTES"
from v$session s, v$sqlarea sa
where s.sql_hash_value=sa.hash_value
and sa.elapsed_time>30*60*1000*1000
and s.status='ACTIVE'
/
SELECT inst_id,username,SID, SERIAL#, message,start_time,LAST_UPDATE_TIME , SOFAR, TOTALWORK,ROUND (SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM GV$SESSION_LONGOPS WHERE TOTALWORK != 0
AND SOFAR <> tOTALWORK and message like '%RMAN%';
SELECT inst_id,username,SID, SERIAL#, message,to_char(start_time,'mon-dd-yyyy hh24:mi:ss'),to_char(LAST_UPDATE_TIME,'mon-dd-yyyy hh24:mi:ss') , SOFAR, TOTALWORK,ROUND (SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM GV$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> tOTALWORK and message like '%RMAN%';
set echo off
set linesize 200
col inst_id format 9999 head 'INST_ID'
col opname format a40
col target format a20
col username format a12
col start_time format a14
col last_update format a14
col sid format 99999 head 'SID'
col serial# format 999999
set pages 1000
PROMPT ....
PROMPT **** Lists the Long Operations that occured today **
PROMPT **** (you can adjust date to expand scope) .. **
SELECT inst_id,SID,
SERIAL#,
OPNAME,
TARGET || ' ' || TARGET_DESC target,
-- round( (sofar/TOTALWORK) * 100,2) Compl ,
SOFAR ,
TOTALWORK,
round( decode(sofar,0,1,sofar)/decode(TOTALWORK,0,1, TOTALWORK)*100,2) compl ,
--username,
to_char(start_time, 'MON-DD HH24:MI') start_time,
to_char(LAST_UPDATE_TIME,'MON-DD HH24:MI') last_update,
TIME_REMAINING/3600
from gv$session_longops
-- where start_time > sysdate - 1
where opname like 'RMAN%'
and round( decode(sofar,0,1,sofar)/decode(TOTALWORK,0,1, TOTALWORK)*100,2) < 100
--where trunc(start_time) = trunc(sysdate)
order by start_time
/
################################# ASM ###########################################
col name for a20
set lines 125
PROMPT ***** Disk Group Details under ASM control ****
select NAME , TYPE, TOTAL_MB, FREE_MB, round(FREE_MB/TOTAL_MB * 100,1) PCT_FREE,
BLOCK_SIZE, SECTOR_SIZe, OFFLINE_DISKS,
STATE from v$asm_diskgroup
/
select name,TOTAL_MB,FREE_MB from v$asm_diskgroup;
Select thread#, max(sequence#), applied from v$archived_log where applied = 'YES' group by thread#, applied;
alter diskgroup DATADGT1 drop disk DATADGT1_0018;
###################################################################################
############################### FLASH RECOVERY AREA ###############################
select SPACE_LIMIT/1024/1024 "Limit MB",round(SPACE_USED/1024/1024) "Used MB" from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
select * from v$recovery_file_dest;
##################################################################################
########################################
Dataguard:
----------
alter database recover managed standby database using current logfile disconnect from session;
list archivelog all completed before 'sysdate';
delete archivelog all completed before 'sysdate';
delete archivelog all backed up 1 times to disk;
select 'rm -rf '||name from v$archived_log where first_time + 1/24 < ( SELECT MIN(first_time) FROM (SELECT MAX(first_time) FIRST_TIME FROM v$archived_log WHERE applied='YES' GROUP BY THREAD#)) and first_time + 1/24 + 1> ( SELECT MIN(first_time) FROM
(SELECT MAX(first_time) FIRST_TIME FROM v$archived_log WHERE applied='YES' GROUP BY THREAD#)) and name is not null;
Select thread#, max(sequence#), applied from v$archived_log where applied = 'YES' group by thread#, applied;
select INSTANCE_NAME, HOST_NAME from v$instance;
ex
select max(sequence#), applied from v$archived_log group by applied;
select max(sequence#) from v$log_history;
select process, status , thread# , sequence#, block#, blocks from gv$managed_standby ;
select inst_id,recovery_mode from gv$archive_dest_status;
select recovery_mode from v$archive_dest_status;
select max(sequence#) from v$log_history;
RMAN> run
2> {
3> copy archivelog '+FRADG01/UASP01_M5000/ARCHIVELOG/2009_11_30/thread_2_seq_4073.16069.704296853' to '/tmp/archive_2_seq_4073.arc';
4> }
alter database register or replace physical logfile '/tmp/archive_2_4073.arc';
set linesize 150
col message for a50
select * from v$dataguard_status;
ORA-16826: apply service state is inconsistent with the DelayMins property
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> select name,open_mode,DATABASE_ROLE,switchover_status from V$database;
alter database recover managed standby database disconnect from session parallel 4;
To open a standby database for read-only access when it is currently performing Redo Apply:
Cancel Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
Open the database for read-only access:
SQL> ALTER DATABASE OPEN;
To change the standby database from being open for read-only access to performing Redo Apply:
Terminate all active user sessions on the standby database.
Restart Redo Apply. To start Redo Apply, issue the following statement:
SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
To enable real-time apply, include the USING CURRENT LOGFILE clause:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> USING CURRENT LOGFILE;
delete noprompt expired archivelog all device type disk;
SQL> select PROCESS, SEQUENCE#, STATUS from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 62670 CLOSING
ARCH 62668 CLOSING
ARCH 62669 CLOSING
RFS 0 IDLE
RFS 0 IDLE
RFS 0 IDLE
6 rows selected.
SQL> select max(sequence#) from v$log_history;
SELECT DEST_ID "ID",
STATUS "DB_status",
DESTINATION "Archive_dest",
ERROR "Error"
FROM V$ARCHIVE_DEST WHERE DEST_ID <=5
select name,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select name,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;
######################## REDOLOG GENERATION#############################
set pagesize 100
PROMPT ** Redo Gen (sysdate-7) Day Wise Size and Number (dest_id = 1) **
PROMPT ( show_redo_generated1.sql does Day wise Logs produced)
PROMPT ( show_redo_generated2.sql does Hourly Logs produced)
PROMPT ================================================================
select to_char(COMPLETION_TIME, 'MM-DD-YYYY') Compl_day,
round( sum(BLOCKS * BLOCK_SIZE/1024/1024),0) Size_in_MB ,
count(*) Logs#
from v$archived_log
where trunc(COMPLETION_TIME) > sysdate - 7
and dest_id = 1
group by to_char(COMPLETION_TIME, 'MM-DD-YYYY')
order by compl_day
/
########################TOP CPU CONSUMING############################
ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21
ps -e -o pcpu,pid,cpu,nice,state,cputime,args --sort pcpu | sed '/^ 0.0 /d' |sort -nr|head -10
select a.username , a.program , to_char(a.LOGON_TIME,'dd-mon-yy:hh24:mi'),c.sql_text from v$session a ,
v$process b , v$sql c where
a.paddr=b.addr and a.sql_id=c.sql_id and b.spid='you OS PID' ;
select ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.status='ACTIVE'
and ss.username is not null
and VALUE/100>0
order by VALUE desc
======================================
=====================================
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
ttitle 'SQL By CPU Usage'
select * from
(select sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 11
/
set pages 22 lines 80
ttitle off
###################################################################
############### IMPORT ################################
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;
#######################################################################
sqltext using pid
-----------------
SQL> select sql_text from v$process a, v$session b, v$sqltext c where a.addr = b.paddr and b.sql_hash_value = c.hash_value and a.spid =<pid> order by piece;
select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = 14763;
select p.spid from v$session s, v$process p where s.sid=16 and s.paddr=p.addr
SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = <problem_SID_you_got_from_last_step>) ;
Blocking session
-----------------
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = 2 28528;
SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE= (select sql_hash_value from v$session where SID =336);
select sid,serial#,SCHEMANAME,OSUSER,MACHINE from v$session where sid in (435,336,323);
select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=123);
select s1.username ||
'@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username ||
'@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
gv$session
where
blocking_session is not NULL
order by
blocking_session;
column sess format A20
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;
Locks in RAC:
SELECT gvh.SID sessid, gvs.serial# serial,
gvh.inst_id instance_id
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM gv$lock
WHERE lmode = 0)
AND gvh.id1 = gvw.id1
AND gvh.id2 = gvw.id2
AND gvh.request = 0
AND gvw.lmode = 0
AND gvh.SID = gvs.SID
AND gvh.inst_id = gvs.inst_id;
Locks in RAC:
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
#############
SQL> select * from v$lock where REQUEST <> 0;
no rows selected
SQL> select * from dba_blockers;
no rows selected
SQL> select * from dba_waiters;
select oracle_username, os_user_name,locked_mode,object_name,object_type from v$locked_object a, dba_objects b where a.object_id=b.object_id;
############
Locks
-----
select c.owner,c.object_name,c.object_type, b.sid, b.serial#,b.status,b.osuser,b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
#########################################################
Current running sql:
-------------------
col OSUSER for a15
col PROGRAM for a20
set linesize 150
col username for a20
select s.sid,
s.serial#,
s.status,
s.process,
s.osuser,
a.sql_text,
p.program
from v$session s,
v$sqlarea a,
v$process p
where s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.schemaname='&SCHEMANAME'
and s.status='ACTIVE';
select s.sid,s.username,
s.status,
s.process,
s.osuser,
a.sql_text,
p.program
from gv$session s,
gv$sqlarea a,
gv$process p
where s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.status='ACTIVE'
####################CPU USAGE#################################
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
##################PHYSICAL READS#############################
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
where
phyrds > 10000
;
##########################################################
Tablespace report
-----------------
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED(MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE(MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL(MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND T.TABLESPACE_NAME NOT IN ('UNDO')
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
set pages 300 lines 125 verify off
col tablespace for a20
break on tablespace
col file for a59
col "Size (MB)" for a12
col "Max Size(MB)" for a12
accept tablespace char prompt 'Enter Tablespace Name:- '
prompt
prompt =========== Tablespace Details ===============
SELECT F.tablespace_name "Tablespace", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990') "Size (MB)", TO_CHAR(NVL(NVL(f.bytes, 0),0)/1024/1024, '99G999G990') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %" FROM (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE F.tablespace_name =upper('&TABLESPACE') AND A.TABLESPACE_NAME=upper('&TABLESPACE');
prompt
Prompt =========== Datafiles Details ================
select TABLESPACE_NAME "Tablespace", FILE_NAME "File", to_char(nvl(bytes/1024/1024,0), '99999990') "Size (MB)", to_char(nvl(MAXBYTES/1024/1024,0), '99999990') "Max Size(MB)", AUTOEXTENSIBLE from dba_data_files where tablespace_name=upper('&TABLESPACE');
################# DBA FREE SPACE####################################################
col TABLESPACE_NAME format a36
col "Free Space PCT" jus right format a17
col "Space Used PCT" jus right format a17
col "Free GB" format 999,999,999.99
col "Total GB" format 999,999,999.99
col "GB Used" format 999,999,999.99
Set Echo Off
Rem
Rem This script finds the free disk space on each different tablespace;
Rem Here the DBA_DATA_FILES and DBA_FREE_SPACE out-join to produce the result.
Rem
select ddf.TABLESPACE_NAME,
ddf.Total_Bytes/1024/1024/1024 "Total GB",
decode(ddf.Total_Bytes - dfs.Free_Bytes, null,ddf.Total_Bytes,
ddf.Total_Bytes - dfs.Free_Bytes)/1024/1024/1024 "GB Used",
decode (dfs.Free_Bytes, null, 0,dfs.Free_Bytes)/1024/1024/1024 "Free GB",
LPAD(
decode(round(dfs.Free_Bytes * 100 /ddf.Total_Bytes,2),
null, '*No space*',
round(dfs.Free_Bytes * 100 /ddf.Total_Bytes,2)) ||' %', 16)
"Free Space PCT",
LPAD(
decode(100 - round(dfs.Free_Bytes * 100 /ddf.Total_Bytes,2),
null, '*Full*',
100 - round(dfs.Free_Bytes * 100 /ddf.Total_Bytes,2)
) ||' %', 16) "Space Used PCT"
from ( select TABLESPACE_NAME,
sum(bytes) TOTAL_BYTES,
sum(blocks) Total_BLOCKS
from dba_data_files
group by TABLESPACE_NAME
) ddf,
( select TABLESPACE_NAME,
sum(BYTES) Free_Bytes,
sum(blocks) Free_Blocks
from dba_free_space
group by TABLESPACE_NAME
) dfs
where ddf.tablespace_name = dfs.tablespace_name (+)
order by tablespace_name
/
set echo on
######################################################################################################
####################################### USER TBS MAP ######################################################
set linesize 500
col owner format a32
col "Tot_Size (MB)" format 9,999,999.99
Rem ====================================================================================
Rem
Rem In the following query, it lists the user object count and size in each tablespace
Rem
Rem ====================================================================================
select owner, tablespace_name, count(*) "Obj_Num",
sum( bytes/1048576 ) "Tot_Size (MB)"
from dba_segments
group by rollup (owner, tablespace_name)
order by owner, tablespace_name;
set echo off
###################################################################################################
DATABASE GROWTH:
----------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY OFF
COLUMN month FORMAT a7 HEADING 'Month'
COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (GB)'
BREAK ON report
COMPUTE SUM OF growth ON report
SELECT
TO_CHAR(creation_time, 'RRRR-MM') month
, SUM(bytes/1024/1024/1024) growth
FROM sys.v_$datafile
where creation_time > SYSDATE-365
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
schema growth.
set feedback off
set pages 80
set linesize 150
spool /tmp/weekly_growth.txt
ttitle "Total Disk Used"
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = '&schema_name'
and space_used_delta > 0;
ttitle "Total Disk Used by Object Type"
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = '&schema_name'
group by rollup(segment_type);
#############################################################################
Temp space
----------
set pages 300 lines 125
col "tablespace" for a10
col "name" for a55
col "Size (MB)" for a12
col "Max Size(MB)" for a12
col "Free (MB)" for a12
col "Free %" for a12
prompt
prompt =========== Temporary Tablespace Details =====================
SELECT d.tablespace_name "Tablespace", d.contents "Type", d.status "Status", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990') "Size (MB)", TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990') "Free (MB)", TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
prompt
Prompt =========== Tempfiles Details ================================
SELECT d.tablespace_name "Tablespace", d.file_name "Name", TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99999990') "Size (MB)", to_char(nvl(d.MAXBYTES/1024/1024, 0),'99999990') "Max Size(MB)",TO_CHAR(NVL((d.bytes-t.bytes_cached),d.bytes)/1024/1024,'99999990') "Free (MB)", TO_CHAR(NVL((d.bytes-t.bytes_cached) / d.bytes * 100, 100), '990D00') "Free %", d.AUTOEXTENSIBLE FROM sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v WHERE (t.file_id (+)= d.file_id) AND (d.file_id = v.file#);
set pages 3000 lines 123
col osuser for a10
col username for a14
col tablespace for a10
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
select tablespace_name,bytes/1024/1024,USER_BYTES/1024/1024,MAXBYTES/1024/1024 from dba_temp_files;
select TABLESPACE_NAME, BYTES_USED/1024/1024,BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
(interested in free blocks)
select file_name ,bytes/1024/1024 from dba_temp_files where tablespace_nAME='TEMP3';
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
set linesize 200
col sql_text for a70
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
set linesize 200
col sql_text for a70
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
set linesize 170
SELECT TabSpaceName,
SUM(TotBytes) "TOTAL MB",
SUM(BytesUsed) "USED MB",
SUM(BytesFree) "FREE MB",
AVG(PCUSED) "% USED",
AVG(PCFREE) "% FREE"
FROM (
SELECT SUBSTR (df.file_id, 1, 3) "ID#",
df.tablespace_name TabSpaceName,
df.BYTES/1024/1024 TotBytes,
round (NVL(df.BYTES/1024/1024 - SUM (fs.BYTES/1024/1024),df.BYTES/1024/1024)) BytesUsed,
round (NVL(SUM (fs.BYTES/1024/1024),0)) BytesFree,
round (NVL((100 * ((SUM (fs.BYTES)) / df.BYTES)),0)) PCFREE,
round (NVL((100 * ((df.BYTES - SUM (fs.BYTES)) / df.BYTES)),100)) PCUSED
FROM SYS.DBA_TEMP_FILES df,
SYS.dba_free_space fs
WHERE df.file_id = fs.file_id (+)
GROUP BY df.tablespace_name, df.file_id,
df.tablespace_name,
df.BYTES,
df.blocks)
GROUP BY TabSpaceName
ORDER BY 6 ;
Which user how much space:
--------------------------
col SID_SERIAL for a20
col SIZE for a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
col SID_SERIAL for a20
col SIZE for a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.gv_$session a,
sys.gv_$sort_usage b,
sys.gv_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
set pages 300 lines 125
col "tablespace" for a10
col "name" for a55
col "Size (MB)" for a12
col "Max Size(MB)" for a12
col "Free (MB)" for a12
col "Free %" for a12
prompt
prompt =========== Temporary Tablespace Details =====================
SELECT d.tablespace_name "Tablespace", d.contents "Type", d.status "Status", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990') "Size (MB)", TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990') "Free (MB)", TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
prompt
Prompt =========== Tempfiles Details ================================
SELECT d.tablespace_name "Tablespace", d.file_name "Name", TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99999990') "Size (MB)", to_char(nvl(d.MAXBYTES/1024/1024, 0),'99999990') "Max Size(MB)",TO_CHAR(NVL((d.bytes-t.bytes_cached),d.bytes)/1024/1024,'99999990') "Free (MB)", TO_CHAR(NVL((d.bytes-t.bytes_cached) / d.bytes * 100, 100), '990D00') "Free %", d.AUTOEXTENSIBLE FROM sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v WHERE (t.file_id (+)= d.file_id) AND (d.file_id = v.file#);
set pages 3000 lines 123
col osuser for a10
col username for a20
col tablespace for a10
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
############################################################################
UNDO
-----
1) Let us know the current state of undo extents
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
2) show us who / what is using undo.
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B
WHERE A.SADDR=B.SES_ADDR;
3) Gives us an indication of the size required.
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select upper(value) from v$parameter where name = 'undo_tablespace'));
total undo usage:
-----------------
select sum(undoblks)/sum((end_time-begin_time)*24*60) undo_usage,sum(ssolderrcnt) OER_old_errors,sum(nospaceerrcnt) OER_space_errors from v$undostat where undoblks>0;
Which user how much space:
--------------------------
col SID_SERIAL for a10
col PROGRAM for a40
col ORAUSER for a20
col UNDOSEG for a20
set linesize 150
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
UNDO RECOMENDATION:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
UNDO USAGE:
col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999
select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%' ) allocated,
( select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name like 'UND%') free,
( select sum(bytes)/1024/1024 from dba_undo_extents
where tablespace_name like 'UND%') USed
from dual
/
SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE= (select sql_hash_value from v$session where SID =336);
select sid,serial#,SCHEMANAME,OSUSER,MACHINE from v$session where sid in (435,336,323);
####################################################################
dbms stats
-----------
exec DBMS_STATS.GATHER_table_STATS (ownname=> 'BEAR_ETL',tabname => 'ACT_PRODUCTS', estimate_percent=> 100,degree=>8,cascade=> TRUE);
scheduler jobs
--------------
col job_name for a25
set linesize 150
col job_action for a30
col job_creator for a10
col owner for a10
col SCHEDULE_OWNER for a10
COL scheduler_name for a20
col start_date for a15
col LAST_START_DATE for a15
col LAST_RUN_DURATION for a15
col NEXT_RUN_DATE for a15
col SCHEDULE_LIMIT for a15
select owner,job_name,JOB_CREATOR,JOB_ACTION,SCHEDULE_OWNER,SCHEDULE_NAME,START_DATE from dba_scheduler_jobs;
select OWNER,JOB_NAME,JOB_ACTION,START_DATE,REPEAT_INTERVAL,STATE,COMMENTS from dba_scheduler_jobs;
select JOB_NAME,START_DATE,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE,SCHEDULE_LIMIT from dba_scheduler_jobs;
SELECT owner, job_name, enabled FROM dba_scheduler_jobs;
SELECT o.name object_name, u.name object_owner FROM obj$ o, user$ u WHERE o.obj# = 252381 and o.owner# = u.user#;
begin
DBMS_SCHEDULER.RUN_JOB (
job_name => 'GATHER_STATS_JOB'
);
end;
/
begin
DBMS_SCHEDULER.RUN_JOB (
job_name => 'GATHER_STATS_JOB',
use_current_session => false);
end;
/
enablng..disabling
exec dbms_ijob.broken(JOB=>4, NEXT_DATE=>sysdate+(60/1440) , broken=>FALSE);
exec dbms_job.broken(1, TRUE);
Here is a query shows which dbms_scheduler jobs failed and why they failed:
-- What scheduled tasks failed during execution, and why?
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 That Failed:'
select log_date,job_name,status,
req_start_date,
actual_start_date,
run_duration
from
dba_scheduler_job_run_details
where
job_name = 'MYJOB' and status <> 'SUCCEEDED'
order by
actual_start_date;
###############################################################################
########################### INDEX & PARTITIONS#################################################
Unusable indexes
----------------
pbeqbatsdb5-h:/opt/oracle/brian/indexes>>cat unusableCnt.sql
set echo on
select count(*) num_indexes from dba_indexes where status = 'UNUSABLE';
select count(*) num_ind_partitions from dba_ind_partitions where status = 'UNUSABLE';
select count(*) num_ind_subpartitions from dba_ind_subpartitions where status = 'UNUSABLE';
select a.TABLE_OWNER,a.TABLE_NAME,a.PARTITION_NAME,b.INDEX_OWNER,b.INDEX_NAME from dba_tab_partitions a,dba_ind_partitions b where a.table_name='MORCLOSOUT' and a.TABLE_OWNER='PROD_MES_DBO' and a.PARTITION_NAME='OCT0110' and a.PARTITION_NAME=b.PARTITION_NAME
select a.TABLE_OWNER,a.TABLE_NAME,a.PARTITION_NAME,b.INDEX_OWNER,b.INDEX_NAME from dba_tab_partitions a,dba_ind_partitions b where a.table_name='MORCLOSOUT' and a.TABLE_OWNER='PROD_MES_DBO' and a.PARTITION_NAME='OCT0110' and a.PARTITION_NAME=b.PARTITION_NAME order by b.INDEX_NAME
quit
select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where status='UNUSABLE';
select 'alter index CED.'||INDEX_NAME||' rebuild;' from dba_indexes where owner='CED' and status='UNUSABLE';
SELECT * FROM ALL_IND_COLUMNS;
SELECT OWNER, COLUMN_NAME, TABLE_NAME from ALL_IND_COLUMNS;
------ Rebuilding partitions
select 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name || ' TABLESPACE PARTITION_2008_INDEX02;'
from dba_ind_partitions
where tablespace_name ='&tbs'
------ PARTITION AND INDEX
Try this query:
SELECT i.table_name, i.index_name, ip.partition_name,
i.status AS index_status, ip.status AS partition_status
FROM user_indexes i
JOIN user_ind_partitions ip
ON (i.index_name = ip.index_name )
ORDER BY i.table_name, i.index_name, ip.partition_name;
If partition_status is "USEABLE" then everything is ok. You don't need to rebuild anything.
If partition_status is "UNUSEABLE" then you can rebuild the unuseable partition by doing:
ALTER INDEX index name
REBUILD PARTITION partition name;
select 'alter index '||owner||'.'||INDEX_NAME||' rebuild;' from dba_indexes where status='UNUSABLE';
-----show_partitions_schema_level.sql
set pagesize 500
set linesize 140
col Tabl format a26
col Partn format a26
col Subpartn format a26
col PARTI_ROWS format 999,999,999,999
col OBJECT_NAME format A28
col SUBOBJECT_NAME format a28
col OBJECT_TYPE format A20
set verify off
PROMPT Please Enter the Schema Name
ACCEPT Schema_owner CHAR PROMPT 'SCHEMA_Name:'
SELECT
DECODE( GROUPING(TABLE_NAME), 1, '** Sum of all TABLES*', table_name ) as Tabl,
DECODE( GROUPING(PARTITION_NAME), 1, '** Sum of All PARTN**', partition_name ) as Partn,
sum(SUBPARTITION_COUNT) SubPartns,
sum(NUM_ROWS) PARTI_ROWS
from dba_tab_partitions
where table_owner = '&Schema_owner'
GROUP BY ROLLUP (TABLE_NAME , PARTITION_NAME )
/
#####################################################################
CPU usage
----------
* CPU.SQL
rem
rem CPU.SQL
rem Mike Ault
rem
rem SQL by CPU Usage (v$sqlarea)
rem
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
ttitle 'SQL By CPU Usage'
spool cpu
select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 21
/
spool off
set pages 22 lines 80
ttitle off
###################JOB Scheduling #############################
-- What scheduled tasks failed during execution, and why?
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 That Failed:'
select
log_date,
job_name,
status,
req_start_date,
actual_start_date,
run_duration
from
dba_scheduler_job_run_details
where
-- job_name = 'MYJOB'
status <> 'SUCCEEDED'
order by
actual_start_date;
SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name = 'TEST1';
#################Agent ###############################
Agent log file:
$AGENT_HOME/<hostname>/sysman/log
/local/pbd-db01/sw/oracle/product/10.2.0/agent10g/sysman/log
/export/home/oracle/GCAGENT/agent10g/pbceora02/sysman
###################MVIEW###################################
select NAME, MASTER, to_char(LAST_REFRESH,'mm-dd-yy hh24:mi:ss') from dba_mview_refresh_times
order by NAME, MASTER
/
select LOG_OWNER,MASTER,LOG_TABLE from dba_mview_logs
/
select master, to_char(oldest_pk,'mm/dd/yyyy hh24:mi:ss') OLDEST_PK,
to_char(oldest,'mm/dd/yyyy hh24:mi:ss') OLDEST,
to_char(youngest,'mm/dd/yyyy hh24:mi:ss') YOUNGEST from sys.mlog$
where master='&table_name'
/
col owner format a10
select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site,
to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+)
and l.master='&table_name'
/
######################MAX SHRINK ##########################################
####GOOD####
set verify off
set linesize 150
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
set pagesize 0
set linesize 2000
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
REM select file_name,
REM ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
REM ceil( blocks*&&blksize/1024/1024) currsize,
REM ceil( blocks*&&blksize/1024/1024) -
REM ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
REM from dba_data_files a,
REM ( select file_id, max(block_id+blocks-1) hwm
REM from dba_extents
REM group by file_id ) b
REM where a.file_id = b.file_id(+)
REM /
column cmd format a132 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.tablespace_name in ('DATA', 'INDX')
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
####NAVEENS###################
set verify off
set pages 10000 lines 132 trimspool on
column file_name format a65 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
spool resize01.txt
column value new_val blksize head "Block Size" for a20
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+);
###############################SRVCTL COMMANDS################################################
Use the following syntax to add configuration information about an existing ASM instance:
srvctl add asm -n node_name -i asm_instance_name -o oracle_home
Use the following syntax to remove an ASM instance:
srvctl remove asm -n node_name [-i asm_instance_name]
Use the following syntax to enable an ASM instance:
srvctl enable asm -n node_name [-i ] asm_instance_name
Use the following syntax to disable an ASM instance:
srvctl disable asm -n node_name [-i asm_instance_name]
The above statement is generally required when u want to disable the asm so that asm does not start automatically on reboot.
You can also use SRVCTL to start, stop, and obtain the status of an ASM instance as in the following examples.
Use the following syntax to start an ASM instance:
srvctl start asm -n node_name [-i asm_instance_name] [-o start_options] [-c <connect_str> | -q]
Use the following syntax to stop an ASM instance:
srvctl stop asm -n node_name [-i asm_instance_name] [-o stop_options] [-c <connect_str> | -q]
Use the following syntax to show the configuration of an ASM instance:
srvctl config asm -n node_name
Use the following syntax to obtain the status of an ASM instance:
srvctl status asm -n node_name
Use the following syntax to stop the database on anyone of the nodes in an RAC environment:
srvctl stop listener -n node_name
srvctl stop instance -d <db_name> -i <instance_name> -o immediate
Use the following syntax to start the database on anyone of the nodes in an RAC environment:
srvctl start listener -n node_name
srvctl start instance -d <db_name> -i <instance_name> -o immediate
Use the following command to stop the database using single command:
srvctl stop database -d <db_name> -o immediate
In order to stop the node applications like oracle vip,gsd, ons services ,use following commands
srvctl stop nodeapps -n <node_name>
Similarly to start
srvctl start nodeapps -n <node_name>
If u wish to trun srvm trace on which can be useful for diagnosisi during instance startup or shut dowm use following command:
SRVM_TRACE=TRUE; export SRVM_TRACE
srvctl stop asm -n <node_name> -i +ASM1 -o immediate>srvctl_stop_asm.log
###################################################################################################
###########################TABLE ANALYZED ##################################
col table_name for a15
select table_name, LAST_ANALYZED, SAMPLE_SIZE, NUM_ROWS, SAMPLE_SIZE/NUM_ROWS*100 from dba_tables
where table_name=upper('&TableName') and owner=upper('&Owner');
#######################################################################################################
#################################################PASSWORD CHANGE#######################
11G
MAKE PASSWORD_REUSE_TIME AS UNLIMITED IN PROFILE.
Select 'Alter user '||name||' identified by values '''||password||''';' from user$ where name in ('GNVFW');
#######################DATAFILE IO############################################
select NAME,
PHYRDS "Physical Reads",
round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
PHYWRTS "Physical Writes",
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
select sum(PHYRDS) PHYS_READS,
sum(PHYWRTS) PHYS_WRTS
from v$filestat
) pd,
v$datafile df,
v$filestat fs
where df.FILE# = fs.FILE#
order by fs.PHYBLKRD+fs.PHYBLKWRT desc
#############################QUEUE#######################################
Select QUEUE_TABLE from dba_queue_tables where owner = ‘DECIPHER’
Select QUEUE_TABLE from user_queue_tables
execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => ‘DECIPHER_AQ_DEMO’, force => true);
##############################CURSOR####################################
--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
#######################Monitor Per Session UGA, PGA and Cursor Usage ###########################
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;
select name, sum(value/1024) "Value - KB"
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name in ('session pga memory', 'session pga memory max'
'session uga memory', 'session uga memory max')
group by name
/
select * from v$pgastat;
Determine a good setting for pga_aggregate_target:
select * from v$pga_target_advice order by pga_target_for_estimate;
Show the maximum PGA usage per process:
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;
###############################################SID################################
EM PURPOSE: LOOKUP DATABASE DETAILS FOR A GIVEN UNIX PROCESS ID
REM
-----------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 50000
SET ECHO OFF FEED OFF VERI OFF
ACCEPT 1 PROMPT 'Enter SID: '
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
BEGIN
SELECT sid INTO v_sid FROM sys.v_$process p, sys.v_$session s
WHERE p.addr = s.paddr
AND (s.sid = '&1' or s.process = '&1');
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('Unable to find sid &&1!!!');
RETURN;
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
RETURN;
END;
SELECT * INTO s FROM sys.v_$session WHERE sid = v_sid;
SELECT * INTO p FROM sys.v_$process WHERE addr = s.paddr;
DBMS_OUTPUT.PUT_LINE('=====================================================================');
DBMS_OUTPUT.PUT_LINE('SID/Serial : '|| s.sid||','||s.serial#);
DBMS_OUTPUT.PUT_LINE('Foreground : '|| 'PID: '||s.process||' '||s.program);
DBMS_OUTPUT.PUT_LINE('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
DBMS_OUTPUT.PUT_LINE('Terminal : '|| s.terminal || '/ ' || p.terminal);
DBMS_OUTPUT.PUT_LINE('OS User : '|| s.osuser||' on '||s.machine);
DBMS_OUTPUT.PUT_LINE('Ora User : '|| s.username);
DBMS_OUTPUT.PUT_LINE('Status Flags: '|| s.status||' - '||s.server||' '||s.type);
DBMS_OUTPUT.PUT_LINE('Tran Active : '|| nvl(s.taddr, 'NONE'));
DBMS_OUTPUT.PUT_LINE('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60,'990.0') || ' min');
DBMS_OUTPUT.PUT_LINE('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
DBMS_OUTPUT.PUT_LINE('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
DBMS_OUTPUT.PUT_LINE('Current SQL statement:');
FOR c1 IN ( SELECT * FROM sys.v_$sqltext WHERE HASH_VALUE = s.sql_hash_value ORDER BY piece)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.sql_text);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Previous SQL statement:');
FOR c1 IN ( SELECT * FROM sys.v_$sqltext WHERE HASH_VALUE = s.prev_hash_value ORDER BY piece)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.sql_text);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Session Waits:');
FOR c1 IN ( SELECT * FROM sys.v_$session_wait WHERE sid = s.sid)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.state||': '||c1.event);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Session Longops:');
FOR c1 IN ( SELECT opname, sofar, totalwork
from sys.v_$session_longops WHERE sid = s.sid and totalwork !=0
and sofar <> totalwork)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.opname||': '||c1.sofar||';'||c1.totalwork||';'||c1.sofar/c1.totalwork*100);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Locks:');
FOR c1 IN ( SELECT DISTINCT DECODE(l.type,
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK','BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
DECODE(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S',5, 'RSX', 6, 'X', to_char(l.lmode) ) lmode,
DECODE(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', to_char(l.request) ) lrequest,
DECODE(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', DECODE(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
FROM sys.gv_$lock l, sys.obj$ o
WHERE sid = s.sid and l.id1 = o.obj#(+) )
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
END LOOP;
DBMS_OUTPUT.PUT_LINE('=====================================================================');
END;
/
#############################################PID#############################################################
###############################INVALID OBJECTS##############################################
set echo off
set feedback off
set heading off
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects
where status = 'INVALID'
and object_type <> 'SYNONYM'
and object_type <> 'PACKAGE BODY'
/
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY'
/
set echo on
set feedback on
set heading on
#################DEPENDENT OBJECTS##########################3
SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
where object_name = 'DBMS_INTERNAL_LOGSTDBY';
SELECT B.*
FROM PUBLIC_DEPENDENCY A, SYS.DBA_OBJECTS B
WHERE A.OBJECT_ID = 7331 AND A.REFERENCED_OBJECT_ID = B.OBJECT_ID AND OBJECT_TYPE = 'TABLE'
ORDER BY OWNER, OBJECT_NAME;
##############################REGISTRY ###################################
col comp_id format a20
col schema format a20
col comp_name format a40
select schema, comp_id, substr( comp_name, 1, 40) comp_name, version, modified,
status, procedure, startup
from dba_registry
order by schema, comp_id
;
##################Agent#########################
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..
Then go to the agent and do the following:
- emctl stop agent
- delete all files in $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state
- emctl clearstate agent (this should delete all state files but sometimes it is not enough, but I experienced that it is better to use this command after deletion of the files)
- emctl secure agent (and specify the password on demand) -- NOT REQUIRED
- emctl start agent
rm -r $AGENT_HOME/sysman/emd/state/*
rm -r $AGENT_HOME/sysman/emd/collection/*
rm -r $AGENT_HOME/sysman/emd/upload/*
rm $AGENT_HOME/sysman/emd/lastupld.xml
rm $AGENT_HOME/sysman/emd/agntstmp.txt
rm $AGENT_HOME/sysman/emd/blackouts.xml
rm $AGENT_HOME/sysman/emd/protocol.ini
############################LOB####################################
select 'alter table ' || t.owner || '.' || t.table_name || ' move lob ('||column_name||') store as lobsegment (tablespace PGPS_PROD);'
from all_lobs l, dba_tables t
where l.owner=t.owner
and l.table_name = t.table_name
and l.SEGMENT_NAME in (
select segment_name
from dba_segments
where segment_type like 'LOBSEGMENT'
and tablespace_name = 'USERS')
order by t.owner, t.table_name;
select 'alter table ' || t.owner || '.' || t.table_name || ' move lob ('||column_name||') store as (tablespace PORTDATA);'
from all_lobs l, dba_tables t
where l.owner=t.owner
and l.table_name = t.table_name
and l.SEGMENT_NAME in (
select segment_name
from dba_segments
where segment_type like 'LOBSEGMENT'
and tablespace_name = 'USERS')
order by t.owner, t.table_name;
alter table PGPS_PROD.EMBARCADERO_EXPLAIN_PLAN MOVE LOB(OTHER_XML) STORE AS (TABLESPACE PORTDATA);
alter table PGPS_PROD.EMBARCADERO_EXPLAIN_PLAN MOVE TABLESPACE PORTDATA LOB(OTHER_XML) STORE AS (TABLESPACE PORTDATA);
alter table PGPS_PROD.EMBARCADERO_EXPLAIN_PLAN move tablespace PORTDATA;
-------moving objects from one tablespace to other
all in one move.
select
--dba_segments.bytes, dba_lobs.*,
'alter table ' || dba_lobs.owner||'.'||dba_lobs.table_name ||' move lob('||column_name||') store as '||
dba_lobs.segment_name ||' (tablespace tbs_compress);' move_script
, dba_tables.tablespace_name table_tablespace
, (select sum(bytes)/1024/1024 from dba_segments
where segment_name=dba_tables.table_name and owner=dba_tables.owner) table_size_MB
from dba_segments, dba_lobs, dba_indexes, dba_tables
where dba_lobs.owner in ('RAD_DATA_WAREHOUSE')
and dba_indexes.tablespace_name = 'USERS'
and dba_lobs.segment_name = dba_segments.segment_name
select sum(bytes/1024/1024/1024) from dba_segments where tablespace_name in ('TS_BPM_QA_2_BDIAS','TS_BPM_QA_1_BDIAS') and owner like ('BPM_%BDIAS') and segment_type like 'LOB%';
------- move LOBS with tabels
SELECT 'ALTER TABLE '||owner||'.'||table_name ||' MOVE TABLESPACE TS_BPM_QA_1_BDIAS ;' FROM DBA_tables WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and owner='BPM_PREPROD_2_BDIAS';
SELECT 'ALTER INDEX '||owner||'.'|| index_name ||' REBUILD online TABLESPACE TS_BPM_QA_1_BDIAS;' FROM DBA_indexes WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and owner='BPM_PREPROD_2_BDIAS';
SELECT 'ALTER TABLE '||owner||'.'|| table_name ||' MOVE tablespace TS_BPM_QA_1_BDIAS LOB ('|| column_name ||') STORE AS (TABLESPACE TS_BPM_QA_1_BDIAS index TS_BPM_QA_1_BDIAS);' FROM DBA_lobs WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and owner='BPM_PREPROD_2_BDIAS';
(or)
------ move only lobs
SELECT 'ALTER TABLE '||owner||'.'|| table_name ||' MOVE LOB ('|| column_name ||') STORE AS (TABLESPACE TS_BPM_QA_1_BDIAS);' FROM DBA_lobs WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and owner='BPM_QA_2_BDIAS';
SELECT 'ALTER TABLE '||table_owner||'.'||table_name ||' MOVE partition '||partition_name||' TABLESPACE TS_BPM_QA_1_BDIAS ;' FROM dba_tab_partitions WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and table_owner='BPM_PREPROD_2_BDIAS';
SELECT 'ALTER index '||INDEX_OWNER||'.'||INDEX_NAME ||' rebuild partition '||partition_name||' TABLESPACE TS_BPM_QA_1_BDIAS ;' FROM dba_ind_partitions WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and index_owner='BPM_PREPROD_2_BDIAS';
select 'ALTER TABLE '||TABLE_OWNER||'.'|| TABLE_NAME||' MOVE PARTITION ('||PARTITION_NAME||') TABLESPACE TS_BPM_LOB_QA_1_BDIAS
LOB ('||column_name||') STORE AS (TABLESPACE '||TABLESPACE_NAME||');' from dba_lob_partitions where TABLESPACE_NAME='TS_BPM_QA_1_BDIAS' and TABLE_OWNER='BPM_QA_2_BDIAS';
ALTER TABLE current_table MOVE PARTITION partition_name
TABLESPACE destination_table_space
LOB (column_name) STORE AS (TABLESPACE current_tablespace);
alter table <tname> move partition <pname> lob (<cname>) store as ( tablespace <tablespace_name> )
select count(*) num_indexes from dba_indexes where status = 'UNUSABLE';
select count(*) num_ind_partitions from dba_ind_partitions where status = 'UNUSABLE';
select count(*) num_ind_subpartitions from dba_ind_subpartitions where status = 'UNUSABLE';
select 'ALTER INDEX ' || owner || '.' ||index_name || ' REBUILD online' ||';' from dba_indexes where status = 'UNUSABLE';
select 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name || ' TABLESPACE TS_BPM_QA_1_BDIAS;' from dba_ind_partitions where status = 'UNUSABLE';
select 'ALTER INDEX ' ||owner || '.' || index_name || ' REBUILD online;' from dba_indexes where status = 'UNUSABLE' and owner in ('BPM_QA_2_BDIAS','BPM_PREPROD_2_BDIAS');
SELECT 'ALTER TABLE '||table_owner||'.'||table_name ||' MOVE partition '||partition_name||' TABLESPACE TS_BPM_QA_1_BDIAS ;' FROM dba_tab_subpartitions WHERE tablespace_name='BPM_QA_1_BDIAS_TS' and table_owner='BPM_QA_2_BDIAS';
-------------
SELECT 'ALTER TABLE '||owner||'.'||table_name ||' MOVE TABLESPACE TS_BPM_QA_1_BDIAS ;' FROM DBA_tables WHERE tablespace_name='BPM_QA_1_BDIAS_TS';
SELECT 'ALTER INDEX '||owner||'.'|| index_name ||' REBUILD TABLESPACE <NEW TABLESPACE>;' FROM DBA_indexes WHERE tablespace_name='<OLD TABLESPACE>';
SELECT 'ALTER TABLE '||owner||'.'|| table_name ||' MOVE LOB ('|| column_name ||') STORE AS (TABLESPACE <NEW TABLESPACE>);' FROM DBA_lobs WHERE tablespace_name='<OLD TABLESPACE>';
alter table PGPS_PROD.EMBARCADERO_EXPLAIN_PLAN MOVE LOB(OTHER_XML) STORE AS (TABLESPACE PORTDATA);
alter table PGPS_PROD.EMBARCADERO_EXPLAIN_PLAN MOVE TABLESPACE PORTDATA LOB(OTHER_XML) STORE AS (TABLESPACE PORTDATA);
alter table PGPS_PROD.EMBARCADERO_EXPLAIN_PLAN move tablespace PORTDATA;
alter table lob_test move tablespace RMAN lob (data) store as ( tablespace lob_indexesindex (tablespace lobs));
ALTER INDEX <index_name> REBUILD ONLINE;
ALTER TABLE <table_name> COALESCE;
ALTER TABLE <table_name> MOVE ONLINE TABLESPACE data1 OVERFLOW TABLESPACE data2;
###############################################################################
#############################session waits#############################################
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(sys)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
and s.username like 'SYS%'
and s.status='ACTIVE'
ORDER BY sw.seconds_in_wait DESC;
column pu format a9 heading 'O/S|Login|ID'
column su format a9 heading 'Oracle|User ID'
column stat format a8 heading 'Session|Status'
column ssid format 999999 heading 'Oracle|Session|ID'
column sser format 999999 heading 'Oracle|Serial|No'
column spid format a20 heading 'O/S|Process|ID'
column txt format a100 heading 'Current Statement' wrapped
set linesize 1000
set pagesize 32000
set trimspool on
select p.username pu
, s.username su
, s.status stat
, s.sid ssid
, s.serial# sser
, substr(p.spid,1,8) spid
, substr(sa.sql_text,1,200) txt
, s.INST_ID Node
from gv$process p
, gv$session s
, gv$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and s.status = 'ACTIVE'
and s.username in ('SYS','DBSNMP')
--and s.sid=&SID
order by 1,2,7
/
###########################################################################
#################### sid details #######################################
REM sidsql.sql
-----------------------------------------------------------------------
REM PURPOSE: LOOKUP DATABASE DETAILS FOR A GIVEN UNIX PROCESS ID
REM
-----------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 50000
SET ECHO OFF FEED OFF VERI OFF
ACCEPT 1 PROMPT 'Enter SID: '
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
BEGIN
SELECT sid INTO v_sid FROM sys.v_$process p, sys.v_$session s
WHERE p.addr = s.paddr
AND (s.sid = '&1' or s.process = '&1');
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('Unable to find sid &&1!!!');
RETURN;
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
RETURN;
END;
SELECT * INTO s FROM sys.v_$session WHERE sid = v_sid;
SELECT * INTO p FROM sys.v_$process WHERE addr = s.paddr;
DBMS_OUTPUT.PUT_LINE('=====================================================================');
DBMS_OUTPUT.PUT_LINE('SID/Serial : '|| s.sid||','||s.serial#);
DBMS_OUTPUT.PUT_LINE('Foreground : '|| 'PID: '||s.process||' '||s.program);
DBMS_OUTPUT.PUT_LINE('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
DBMS_OUTPUT.PUT_LINE('Terminal : '|| s.terminal || '/ ' || p.terminal);
DBMS_OUTPUT.PUT_LINE('OS User : '|| s.osuser||' on '||s.machine);
DBMS_OUTPUT.PUT_LINE('Ora User : '|| s.username);
DBMS_OUTPUT.PUT_LINE('Status Flags: '|| s.status||' - '||s.server||' '||s.type);
DBMS_OUTPUT.PUT_LINE('Tran Active : '|| nvl(s.taddr, 'NONE'));
DBMS_OUTPUT.PUT_LINE('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60,'990.0') || ' min');
DBMS_OUTPUT.PUT_LINE('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
DBMS_OUTPUT.PUT_LINE('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
DBMS_OUTPUT.PUT_LINE('Current SQL statement:');
FOR c1 IN ( SELECT * FROM sys.v_$sqltext WHERE HASH_VALUE = s.sql_hash_value ORDER BY piece)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.sql_text);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Previous SQL statement:');
FOR c1 IN ( SELECT * FROM sys.v_$sqltext WHERE HASH_VALUE = s.prev_hash_value ORDER BY piece)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.sql_text);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Session Waits:');
FOR c1 IN ( SELECT * FROM sys.v_$session_wait WHERE sid = s.sid)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.state||': '||c1.event);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Session Longops:');
FOR c1 IN ( SELECT opname, sofar, totalwork
from sys.v_$session_longops WHERE sid = s.sid and totalwork !=0
and sofar <> totalwork)
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.opname||': '||round(c1.sofar/c1.totalwork*100,2));
END LOOP;
DBMS_OUTPUT.PUT_LINE('Locks:');
FOR c1 IN ( SELECT DISTINCT DECODE(l.type,
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK','BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
DECODE(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S',5, 'RSX', 6, 'X', to_char(l.lmode) ) lmode,
DECODE(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', to_char(l.request) ) lrequest,
DECODE(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', DECODE(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
FROM sys.gv_$lock l, sys.obj$ o
WHERE sid = s.sid and l.id1 = o.obj#(+) )
LOOP
DBMS_OUTPUT.PUT_LINE(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
END LOOP;
DBMS_OUTPUT.PUT_LINE('=====================================================================');
END;
/
#############################################################################
################################ shrink datafiles list ##########################################################
column tablespace_name format a20
column "Name" format a45
break on file_id skip 1
ttitle &1
select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper('&1')
UNION
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper('&1')
order by 1,2,3
/
####################### Flushing particular sqlid from library cache##############################################
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
ADDRESS HASH_VALUE
--------------------------------------------------------------------------------
----------
000000085FD77CF0 808321886
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
no rows selected
Oracle Support Document ID 457309.1 "How To Flush an Object out the Library Cache [SGA]"
################################################################################################################
