Set Environment
-----------------
export ORACLE_SID=
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin:.
To Check Database
---------------------
set pages 200 lines 170
col HOST_NAME for a15
select instance_name,name db_name,host_name,version db_version from gv$instance,v$database;
Table Space Report
---------------------
col tablespace_name format a15
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;
Datafiles of a particular TableSpace
-------------------------------------
set pages 200
set lines 175
col tablespace_name for a27
col file_name for a70
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_data_files where tablespace_name='TABLESPACENAME' order by 1,2;
ASM Space Report
--------------------
SET LINESIZE 150
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 (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
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
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
gv$asm_diskgroup
ORDER BY
name;
/
OTHER ASM QUERIES
--------------------------
select distinct name, total_mb/1024 , (total_mb-free_mb)/1024 , free_mb/1024, SYSDATE from GV$ASM_DISKGROUP;
select GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB from gv$asm_diskgroup order by NAME;
select table_name from dict where table_name like '%ASM%';
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from v$asm_disk;
select INST_ID,GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from gv$asm_disk;
Filesystem
-----------
$ df -h /datafile/path/
or
$ df -g /datafile/path/
Filesystem GB blocks Free %Used Iused %Iused Mounted on
set pages 200 lines 160
spool ./resize.log
set echo on feed on time on term on
To resize a datafile
-------------------
alter database datafile '/datafile/path/datafilename.dbf' resize 4096m;
To resize a datafile (ASM)
----------------------------
ALTER DATABASE DATAFILE '&FILE_NAME' RESIZE 4096M;
spool off;
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE
------------------------------------------------------------------------------------------------
For datafile
-----------
select file_name from dba_data_files where file_name like '%&datafilename%';
set pages 200 lines 160
spool ./add.log
set echo on feed on time on term on
To add a new datafile in a tablespace
--------------------------------------
alter tablespace <TABLESPACE NAME> add datafile '/datafile/path/datafilename.dbf' size 4096m;
To add a new datafile in a tablespace (ASM)
-----------------------------------------------
ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;
To Create a new tablespace (ASM)
--------------------------------------
CREATE TABLESPACE <TABLESPACE NAME> DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M 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;
spool off;
Take Table Space Report.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Tablespaces that are >=80% full, and how much to add to make them 80% again
---------------------------------------------------------------------------
set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
, nvl(sum(bytes)/1024/1024, 0) free_mb
from dba_tablespaces ts, dba_free_space fs
where ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/
"Hope for the best, prepare for the worst"
-Dharmendra
No comments:
Post a Comment