set linesize 400
set pagesize 20
set heading on
select c.tablespace_name as "TABLESPACE",
ROUND((b.total_space_available/1048576),2) AS "SIZE MB",
ROUND((b.total_space_available/1048576)-(a.free_space/1048576),2) as "USED MB",
ROUND((a.free_space/1048576),2) as "FREE MB",
ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %'
as "% USED",ROUND((((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %' as "% FREE"
from (select sum(bytes) as free_space,tablespace_name
from dba_free_space -- Free space in permanent tablespace
group by tablespace_name UNION ALL
select sum(bytes_free) as free_space,tablespace_name
from v$temp_space_header -- Free space in temporary tablespaces
group by tablespace_name) a, -- Calculate the free space that each tablespace has
(select sum(user_bytes) as total_space_available,tablespace_name
from dba_data_files -- Total space in permanent tablespaces
group by tablespace_name
UNION ALL select sum(user_bytes) as total_space_available,tablespace_name
from dba_temp_files -- Total space in temporary tablespaces
group by tablespace_name) b, -- Calculate the total size of each TS
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name and -- show TS that are full so have no entries in dba_free_space
b.tablespace_name = c.tablespace_name
order by ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1) DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Check the datafile size using the tablespace name.
set linesize 150
column file_name format a50
column tablespace_name format a10
select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tablespace_name';
For Temp size
set linesize 150
column file_name format a50
column tablespace_name format a10
select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='&tablespace_name';
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
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;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
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 (ASM)
-------------------------------------------
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 '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
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 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 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
/
SELECT SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name",
tot_create_blk /1024/1024 created_size,
ROUND(max_free_blk / 1024/1024,2) max_free,
ROUND(tot_free_blk / 1024/1024,2) tot_free,
ROUND((tot_free_blk/tot_create_blk*100),3) pct_free
FROM ( SELECT tablespace_name,
SUM(bytes) tot_create_blk
FROM sys.dba_data_files
GROUP BY tablespace_name ) data_files,
( SELECT tablespace_name,
MAX(bytes) max_free_blk,
SUM(bytes) tot_free_blk
FROM sys.dba_free_space
GROUP BY tablespace_name ) free_space
WHERE data_files.tablespace_name = free_space.tablespace_name
and data_files.tablespace_name = '&tablespace'
ORDER BY data_files.tablespace_name;
============================================================================================================
No comments:
Post a Comment