select TABLESPACE_NAME,USED_SPACE/1024/1024/1024 USED_SPACE_GB,TABLESPACE_SIZE/1024/1024/1024 TABLESPACE_SIZE_GB,USED_PERCENT from dba_tablespace_usage_metrics
where TABLESPACE_NAME='&TBS_NAME';
Wednesday, December 19, 2012
Tbs_usage_metrics_check
select TABLESPACE_NAME,USED_SPACE/1024/1024/1024 USED_SPACE_GB,TABLESPACE_SIZE/1024/1024/1024 TABLESPACE_SIZE_GB,USED_PERCENT from dba_tablespace_usage_metrics
where TABLESPACE_NAME='&TBS_NAME';
shrink.sql
REM -----------------------------------------------------------------------------------------------
REM Name : shrinksegs.sql
REM Compatible versions : 10.x and above
REM Description : This script will initiate segment shrink for the TOP-5 Tables in reverse
REM order.
REM Note : For some 10.x versions, If the UNDO Tablespace is not present in view
REM dba_tablespace_usage_metrics, it will calculate on dba_data_files and
REM dba_free_space
REM -----------------------------------------------------------------------------------------------
REM
------------------
-- Set SQL prompt:
------------------
whenever sqlerror exit;
set pagesize 60 lines 1000 serveroutput on feedback off echo off verify off
execute dbms_output.enable(1000000);
------------------------
-- Start segment shrink:
------------------------
DECLARE
v_starttime number := dbms_utility.get_time;
v_owner dba_segments.owner%TYPE := 'PERFSTAT';
v_usercnt NUMBER;
v_maxundo NUMBER := Nvl(Trim(&&maxundo),90);
v_undo_pct NUMBER;
v_objcnt NUMBER;
v_tbscnt NUMBER;
v_noundo EXCEPTION;
v_nouser EXCEPTION;
v_invalidpct EXCEPTION;
BEGIN
SELECT Count(* )
INTO v_usercnt
FROM dba_users
WHERE username = v_owner;
IF (v_usercnt = 0) THEN
RAISE v_nouser;
END IF;
IF (v_maxundo < 0
OR v_maxundo > 100) THEN
RAISE v_invalidpct;
END IF;
SELECT Count(*)
INTO v_tbscnt
FROM dba_tablespace_usage_metrics
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace');
dbms_output.Put_line(Chr(10));
FOR i IN (SELECT owner,
segment_name
FROM (SELECT owner,
segment_name,
Sum(bytes) tabsize
FROM dba_segments
WHERE owner = v_owner
AND segment_type = 'TABLE'
GROUP BY owner,
segment_name,
segment_type
ORDER BY Sum(bytes) DESC)
WHERE ROWNUM <= 5
ORDER BY tabsize)
LOOP
IF (v_tbscnt <> 0) THEN
SELECT Nvl(Round(used_percent),0)
INTO v_undo_pct
FROM dba_tablespace_usage_metrics
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace');
ELSE
SELECT Decode(Nvl(a.maxbytes,0),0,0,Round(((Nvl(a.bytes,0) - Nvl(b.bytes,0)) / Nvl(a.maxbytes,0)) * 100,2)) pct_on_max
INTO v_undo_pct
FROM (SELECT tablespace_name tbs,
Nvl(Sum(bytes),0) bytes,
Nvl(Sum(CASE
WHEN (Decode(maxbytes,0,bytes,maxbytes) >= bytes)
THEN Decode(maxbytes,0,bytes,maxbytes)
ELSE bytes
END),0) maxbytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name tbs,
Nvl(Sum(bytes),0) bytes
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tbs = b.tbs (+)
AND a.tbs = (SELECT UPPER(VALUE)
FROM v$parameter
WHERE NAME = 'undo_tablespace');
END IF;
IF (v_undo_pct < v_maxundo) THEN
dbms_output.Put_line('INFO : Current UNDO usage : '
||v_undo_pct
||'%');
EXECUTE IMMEDIATE 'alter table '
||i.owner
||'.'
||i.segment_name
||' enable row movement';
EXECUTE IMMEDIATE 'alter table '
||i.owner
||'.'
||i.segment_name
||' shrink space cascade';
EXECUTE IMMEDIATE 'alter table '
||i.owner
||'.'
||i.segment_name
||' disable row movement';
dbms_output.Put_line(Chr(10)
||'INFO : Table '
||i.owner
||'.'
||i.segment_name
||' shrinked successfully'
||Chr(10));
ELSE
RAISE v_noundo;
END IF;
END LOOP;
dbms_output.Put_line(Chr(10));
dbms_utility.Compile_schema(SCHEMA => v_owner,compile_all => false);
dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
dbms_output.Put_line('INFO : Compilation successfully completed for '||v_owner);
SELECT Count(object_name)
INTO v_objcnt
FROM dba_objects
WHERE owner = v_owner
AND status <> 'VALID';
dbms_output.Put_line('INFO : Invalid object count in '
||v_owner
||' schema : '
||v_objcnt);
dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
dbms_output.Put_line(Chr(10));
dbms_output.put_line('INFO : Execution Time : '||round((dbms_utility.get_time-v_starttime)/100,2)||' seconds');
dbms_output.Put_line(Chr(10));
EXCEPTION
WHEN v_nouser THEN
Raise_application_error(-20002,'ERROR! : Invalid user');
WHEN v_invalidpct THEN
Raise_application_error(-20003,'ERROR! : Percentage should be in between 0 - 100');
WHEN v_noundo THEN
Raise_application_error(-20004,'WARNING! : default UNDO Tablespace usage is '
||v_undo_pct
||'% Please try later..');
WHEN OTHERS THEN
Raise_application_error(-20050,'Error while executing PL/SQL block !'
||SQLCODE
||'-Error-'
||sqlerrm);
END;
/
REM Name : shrinksegs.sql
REM Compatible versions : 10.x and above
REM Description : This script will initiate segment shrink for the TOP-5 Tables in reverse
REM order.
REM Note : For some 10.x versions, If the UNDO Tablespace is not present in view
REM dba_tablespace_usage_metrics, it will calculate on dba_data_files and
REM dba_free_space
REM -----------------------------------------------------------------------------------------------
REM
------------------
-- Set SQL prompt:
------------------
whenever sqlerror exit;
set pagesize 60 lines 1000 serveroutput on feedback off echo off verify off
execute dbms_output.enable(1000000);
------------------------
-- Start segment shrink:
------------------------
DECLARE
v_starttime number := dbms_utility.get_time;
v_owner dba_segments.owner%TYPE := 'PERFSTAT';
v_usercnt NUMBER;
v_maxundo NUMBER := Nvl(Trim(&&maxundo),90);
v_undo_pct NUMBER;
v_objcnt NUMBER;
v_tbscnt NUMBER;
v_noundo EXCEPTION;
v_nouser EXCEPTION;
v_invalidpct EXCEPTION;
BEGIN
SELECT Count(* )
INTO v_usercnt
FROM dba_users
WHERE username = v_owner;
IF (v_usercnt = 0) THEN
RAISE v_nouser;
END IF;
IF (v_maxundo < 0
OR v_maxundo > 100) THEN
RAISE v_invalidpct;
END IF;
SELECT Count(*)
INTO v_tbscnt
FROM dba_tablespace_usage_metrics
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace');
dbms_output.Put_line(Chr(10));
FOR i IN (SELECT owner,
segment_name
FROM (SELECT owner,
segment_name,
Sum(bytes) tabsize
FROM dba_segments
WHERE owner = v_owner
AND segment_type = 'TABLE'
GROUP BY owner,
segment_name,
segment_type
ORDER BY Sum(bytes) DESC)
WHERE ROWNUM <= 5
ORDER BY tabsize)
LOOP
IF (v_tbscnt <> 0) THEN
SELECT Nvl(Round(used_percent),0)
INTO v_undo_pct
FROM dba_tablespace_usage_metrics
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace');
ELSE
SELECT Decode(Nvl(a.maxbytes,0),0,0,Round(((Nvl(a.bytes,0) - Nvl(b.bytes,0)) / Nvl(a.maxbytes,0)) * 100,2)) pct_on_max
INTO v_undo_pct
FROM (SELECT tablespace_name tbs,
Nvl(Sum(bytes),0) bytes,
Nvl(Sum(CASE
WHEN (Decode(maxbytes,0,bytes,maxbytes) >= bytes)
THEN Decode(maxbytes,0,bytes,maxbytes)
ELSE bytes
END),0) maxbytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name tbs,
Nvl(Sum(bytes),0) bytes
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tbs = b.tbs (+)
AND a.tbs = (SELECT UPPER(VALUE)
FROM v$parameter
WHERE NAME = 'undo_tablespace');
END IF;
IF (v_undo_pct < v_maxundo) THEN
dbms_output.Put_line('INFO : Current UNDO usage : '
||v_undo_pct
||'%');
EXECUTE IMMEDIATE 'alter table '
||i.owner
||'.'
||i.segment_name
||' enable row movement';
EXECUTE IMMEDIATE 'alter table '
||i.owner
||'.'
||i.segment_name
||' shrink space cascade';
EXECUTE IMMEDIATE 'alter table '
||i.owner
||'.'
||i.segment_name
||' disable row movement';
dbms_output.Put_line(Chr(10)
||'INFO : Table '
||i.owner
||'.'
||i.segment_name
||' shrinked successfully'
||Chr(10));
ELSE
RAISE v_noundo;
END IF;
END LOOP;
dbms_output.Put_line(Chr(10));
dbms_utility.Compile_schema(SCHEMA => v_owner,compile_all => false);
dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
dbms_output.Put_line('INFO : Compilation successfully completed for '||v_owner);
SELECT Count(object_name)
INTO v_objcnt
FROM dba_objects
WHERE owner = v_owner
AND status <> 'VALID';
dbms_output.Put_line('INFO : Invalid object count in '
||v_owner
||' schema : '
||v_objcnt);
dbms_output.Put_line(rpad('-',length('INFO : Compilation successfully completed for '||v_owner),'-'));
dbms_output.Put_line(Chr(10));
dbms_output.put_line('INFO : Execution Time : '||round((dbms_utility.get_time-v_starttime)/100,2)||' seconds');
dbms_output.Put_line(Chr(10));
EXCEPTION
WHEN v_nouser THEN
Raise_application_error(-20002,'ERROR! : Invalid user');
WHEN v_invalidpct THEN
Raise_application_error(-20003,'ERROR! : Percentage should be in between 0 - 100');
WHEN v_noundo THEN
Raise_application_error(-20004,'WARNING! : default UNDO Tablespace usage is '
||v_undo_pct
||'% Please try later..');
WHEN OTHERS THEN
Raise_application_error(-20050,'Error while executing PL/SQL block !'
||SQLCODE
||'-Error-'
||sqlerrm);
END;
/
migration_file_paths.sql
-- destination for datafile,control file, redolog file,temp file, dump file, dump locations
-- parameter file,
--
select distinct file_path
from
(select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$datafile
union all
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$controlfile
union all
select distinct substr(member,0,instr(member,'/',-1,1)) file_path from v$logfile
union all
select distinct substr(name,0,instr(name,'/',-1,1)) file_path from v$tempfile
union all
select distinct substr(value,0,instr(value,'/',-1,1)) file_path from v$parameter where name like '%dump%'
union all
select destination from v$archive_dest where destination is not null
union all
select decode(value,null,(select substr(file_spec,0,instr(file_spec,'/lib/libqsmashr.so' ))||'dbs/'
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB'
),substr(value,0,instr(value,'/',-1,1))) file_path
from sys.v_$parameter
where name = 'spfile')
/
-- list listener log
--
lsnrctl status LISTENER_CBFCPPPM|grep -i file
Tbs_usage_check.sql
set linesize 9999
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024/1024, 0) "Size (Gb)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024/1024) "Used (Gb)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (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
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
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'
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024/1024, 0) "Size (Gb)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024/1024) "Used (Gb)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (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
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
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'
Rman_backup_restore_progress.sql
----To Check Backup Progress and Restore Progress
set lines 9999
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
------To Check Backup Progress and Restore Progress
set lines 9999
col FILENAME for a60
select SID,STATUS,open_time, BYTES/1024/1024/1024 "SOFAR GB" ,total_bytes/1024/1024/1024 TotMb, round(BYTES/TOTAL_BYTES*100,2) "% Complete" , filename from v$backup_async_io where STATUS != 'FINISHED';
expdp using parfile and using consistent parameter
nohup expdp "'/ as sysdba'" parfile=abc.par &
---abc.par is as below
DIRECTORY=EXPORT_TC
SCHEMAS=TC
dumpfile=file_name.dmp
logfile=file_name.log
flashback_time="to_timestamp(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss')"
datafile_resize.sql
set pagesize 1000
set linesize 9999
col file_name for a60
col tablespace_name for a20
select file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE,INCREMENT_BY INC,MAXBYTES/1024/1024/1024 MAX_GB_CAN_EXTEND from dba_data_files where TABLESPACE_NAME='&TBS_NAME';
-------------------------
alter database datafile '/oracle/data1/database_name/file_name01.dbf' resize 10g
alter database datafile '/oracle/data1/database_name/file_name01.dbf' AUTOEXTEND ON NEXT 10g MAXSIZE 10g;
-----------------------
set linesize 9999
col file_name for a60
col tablespace_name for a20
select file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE,INCREMENT_BY INC,MAXBYTES/1024/1024/1024 MAX_GB_CAN_EXTEND from dba_data_files where TABLESPACE_NAME='&TBS_NAME';
-------------------------
alter database datafile '/oracle/data1/database_name/file_name01.dbf' resize 10g
alter database datafile '/oracle/data1/database_name/file_name01.dbf' AUTOEXTEND ON NEXT 10g MAXSIZE 10g;
-----------------------
Subscribe to:
Posts (Atom)