TABLESPACE MONITORING

TABLESPACE MONITORING
Unable to extend tablespace for database:


Step 1. Below query can be used for check the freespace in the tablespace

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used”, round ((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='TABLESPACE_NAME' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='TABLESPACE_NAME' group by tablespace_name) b
Where a.tbl=b.tblsp;


Step 2: Below query can be used for get the file name and file size.


set pages 2000
set lines 120
col file_name for a65

select file_name, bytes/1024/1024 "Size (M)", maxbytes/1024/1024 "Max Size",autoextensible
from dba_data_files
where tablespace_name = upper('&tablespace')
order by substr(file_name,-7);


!df -k |grep dmx3 |sort


Step 3: Use below query to add the space and change the file system according to the requirement.



ttitle off
btitle off
set pagesize 0
set heading off
set feedback off
set verify off
set echo off
set linesize 240
set pause off

rem def tablespace = &&1
ACC tablespace CHAR PROMPT 'Enter tablespace:’


col prog_text format a70 newline

spool add_df

alter system set db_create_file_dest='/d1001/oradata/dwdevl';

alter tablespace &tablespace add datafile size 11m autoextend on next 10M maxsize 16001m;

Fix unusable indexes:

We can use below query to find unusable indexes:

col index_name for a30
col partition_name for a30
col tablespace_name for a30


PROMPT *******************************************
PROMPT UNUSABLE NON-PARTITIONED INDEXES
PROMPT *******************************************

select OWNER, INDEX_NAME, TABLESPACE_NAME, STATUS
from dba_indexes
where status = 'UNUSABLE';

PROMPT *******************************************
PROMPT UNUSABLE PARTITIONED INDEXES
PROMPT *******************************************

select INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME, STATUS
from dba_ind_partitions
where status != 'USABLE'
order by 1,2;

Here is the syntax for rebuilding a non partitioned index:

alter index schema.index_name rebuild tablespace tablespace_name;

Note: replace schema.index_name and tablespace_name with the appropiate values

Here is the syntax for rebuild a partitioned index:

alter index schema.index_name rebuild partition partition_name tablespace tablespace_name;

Note: replace schema.index_name, tablespace_name, partition_name with the appropiate values



1. I use the following query to query to free rollback extents:

SELECT SEGMENT_TYPE, SEGMENT_NAME,TABLESPACE_NAME,
(MAX_EXTENTS - EXTENTS) FREE,
MAX_EXTENTS, EXTENTS, BLOCKS, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'ROLLBACK'
ORDER BY FREE;

2. Use the following query to find out that RBS tablespace is full:

select tbs.tablespace_name,
tot.bytes/1024 total,
tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used,
sum(nvl(fre.bytes,0))/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1 ;

DAB_DATA_FILES

col tablespace_name format a20
col file_name format a45
col bytes format 9999999
select tablespace_name,file_name,bytes/1024/1024 from dba_data_files
where tablespace_name='TALLY_BIG_DATA';


DAB_FREESPACE

select tablespace_name,SUM(bytes)/1024/1024 from dba_free_space
where tablespace_name='SCBIDAT01' group by tablespace_name;

select index_name from dba_indexes where tablespace_name='SCBIDAT01';



Rebuild tablespace

select 'alter index '||owner||'.'||index_name||' rebuild tablespace PMFREQIDX01;' from dba_indexes where tablespace_name='PMFREQDAT01';


select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='TALLY_BIG_DATA' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='TALLY_BIG_DATA' group by tablespace_name) b
Where a.tbl=b.tblsp;

select 'alter database datafile '||''''||b.file_name||''''||' resize
'||round(((c.max_blockid+a.blocks) * 4096)/1024/1024)||'M ;'
from dba_extents a, dba_data_files b ,
(select file_id, max(block_id) max_blockid from dba_extents group by
file_id) c
where a.file_id=b.file_id
and a.file_id=c.file_id
and a.block_id = c.max_blockid
and b.file_name like lower('%&file_name%');

ALTER TABLESPACE PMFREQIDX01 COALESCE;

alter database datafile '/tstcpq/u011/oradata/tstcpq/sltc_pricedat0101.dbf' resize 1600M;

select index_name,table_name,tablespace_name from dba_indexes where owner='PMFREQ' AND INDEX_NAME='SYS_C00132902';

alter tablespace temp default storage(pctfree 0);

alter tablespace add datafile '/devl3/u09/oradata/devl3/jldl102.dbf' size 50M;

alter database datafile '/u33/oradata/matprod/enpidat11.dbf' resize 4000m;


!find /u*/oradata/matprod/

!ls /u24/oradata/matprodenpidat12.dbf

select segment_name from dba_ROLLBACK_segs where tablespace_name='RBS';

SELECT SEGMENT_NAME,TABLESPACE_NAME from dba_ROLLBACK_segs;

SELECT USN,EXTENTS FROM V$ROLLSTAT;

select tablespace_name,owner,SUM(BYTES)/1024/1024 from dba_segments where tablespace_name LIKE 'UCMDATA%' GROUP
BY TABLESPACE_NAME,OWNER;

SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='UCM';

select tablespace_name from DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'UCM%';

select object_type,count(*) from dba_objects where OWNER='UCM' group by object_type;
COL OWNER FOR A6
COL TABLESPACE_NAME FOR A10
SELECT OWNER,TABLESPACE_NAME,SEGMENT_TYPE,COUNT(*) FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME LIKE '%UCM%'
GROUP BY OWNER,TABLESPACE_NAME,SEGMENT_TYPE;

SELECT 'ALTER INDEX UCM.'|| INDEX_NAME||' REBUILD TABLESPACE UCMINDEX;'
FROM DBA_INDEXES WHERE TABLESPACE_NAME='UCMDATA'; AND OWNER='UCM';


qiomkfile -e 51200k /glbake/u38/oradata/gltest/gl_balances_indx16.dbf

alter database datafile '/glbake/u38/oradata/gltest/gl_balances_indx16.dbf' resize 174144k;

alter tablespace MSDATADAT01 coalesce;

select snap_id,to_char(snap_time,'DD:MON:YYYY-HH24:MI') from stats$snapshot order by snap_id;


Adding space to ASM instance
alter tablespace x add datafile ‘+TIER2_DG’ size 128m autoextend on next 128m maxsize 32001m