Tablespace Scripts
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
$df -h (Linux,AIX)
$df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Tablespace Utilization Script
-----------------------------
set pages 9999 lines 300
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024 Allocated_size
,a.cur_size/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (Detailed)
-----------------------------
set pages 9999 lines 300
set heading off;
set feedback off;
col tablespace_name for a30
select 'Database Name : ' || a.name , 'Host : ' || b.host_name from v$database a,gv$instance b;
select ' ' from dual;
set heading on;
set feedback on;
break on report
compute sum of "TOTAL SIZE in MB" on report
compute sum of "ALLOCATED SIZE in MB" on report
compute sum of "USED %" on report
compute sum of "FREE %" on report
compute sum of "SPACE_NEED_ON_OS_FOR_MAXSIZE" on report
select a.tablespace_name,
round(nvl(a.total,0)) "TOTAL SIZE in MB",
round(nvl(a.asize,0)) "ALLOCATED SIZE in MB",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(a.total-a.asize+f.free,0)) "FREE",
nvl(f.maxfree,0) "MAX_FREE",
round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",
round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %",
round(a.total - a.asize) "SPACE_NEED_ON_OS_FOR_MAXSIZE"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE",sum(case when maxbytes > bytes
then maxbytes else bytes end)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name, file_name, bytes/1024/1024 SIZE_MB, autoextensible,
maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files
where tablespace_name='&tablespace_name' order by 1,2;
Database file sizes and locations
---------------------------------
set pages 9999 lines 300
set VERIFY OFF FEEDBACK OFF
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'Auto Extend'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SPOOL ${ORACLE_SID}_datafile.lst
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_data_files
UNION
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_temp_files
ORDER BY tablespace_name
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TABLESPACE DDL
--------------
set pagesize 0
set long 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile
--------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
------------------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace
--------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 4G;
If AUTOEXTEND ON
----------------
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;
If AUTOEXTEND ON
----------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;
Schemas in a Tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a Tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner
------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
$df -h (Linux,AIX)
$df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Tablespace Utilization Script
-----------------------------
set pages 9999 lines 300
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024 Allocated_size
,a.cur_size/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (Detailed)
-----------------------------
set pages 9999 lines 300
set heading off;
set feedback off;
col tablespace_name for a30
select 'Database Name : ' || a.name , 'Host : ' || b.host_name from v$database a,gv$instance b;
select ' ' from dual;
set heading on;
set feedback on;
break on report
compute sum of "TOTAL SIZE in MB" on report
compute sum of "ALLOCATED SIZE in MB" on report
compute sum of "USED %" on report
compute sum of "FREE %" on report
compute sum of "SPACE_NEED_ON_OS_FOR_MAXSIZE" on report
select a.tablespace_name,
round(nvl(a.total,0)) "TOTAL SIZE in MB",
round(nvl(a.asize,0)) "ALLOCATED SIZE in MB",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(a.total-a.asize+f.free,0)) "FREE",
nvl(f.maxfree,0) "MAX_FREE",
round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",
round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %",
round(a.total - a.asize) "SPACE_NEED_ON_OS_FOR_MAXSIZE"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE",sum(case when maxbytes > bytes
then maxbytes else bytes end)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name, file_name, bytes/1024/1024 SIZE_MB, autoextensible,
maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files
where tablespace_name='&tablespace_name' order by 1,2;
Database file sizes and locations
---------------------------------
set pages 9999 lines 300
set VERIFY OFF FEEDBACK OFF
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'Auto Extend'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SPOOL ${ORACLE_SID}_datafile.lst
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_data_files
UNION
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_temp_files
ORDER BY tablespace_name
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TABLESPACE DDL
--------------
set pagesize 0
set long 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile
--------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
------------------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace
--------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 4G;
If AUTOEXTEND ON
----------------
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;
If AUTOEXTEND ON
----------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;
Schemas in a Tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a Tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner
------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
No comments:
Post a Comment