Datafile movement :
select 'Alter tablespace '|| tablespace_name||' offline normal; '||chr(10)||
'!cp '|| file_name ||' /d44/oradata/Dev/'||substr(file_name,instr (file_name, '/', -1, 1)+1, length(file_name))||chr(10)||
'alter database rename file ''' || file_name || ''' TO ''/d44/oradata/Dev/'|| substr(file_name,instr (file_name, '/', -1, 1)+1, length(file_name)) ||''''||';' ||chr(10)||
'Alter tablespace '||tablespace_name||' online;'||chr(10)||'-- '||bytes/1024/1024/1024||chr(10)||'-- delete this file_name after online :'||file_name
from dba_data_files where file_name like '/d41%'
and file_name like '%x%'
and bytes/1024/1024/1024 between 2 and 3;
Tablespace Utilization :
SET lines 132 pages 66 feedback off
COLUMN tablespace_name format a35 heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible format a6 heading 'Can|Auto|Extend'
COLUMN files_in_tablespace format 999 heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Space'
COLUMN total_used_space format 99,999,999,999 heading 'Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Free|Space'
COLUMN total_used_pct format 999.99 heading 'Used|PCT'
COLUMN total_free_pct format 999.99 heading 'Free|PCT'
COLUMN max_size_of_tablespace format 99,999,999,999 heading 'Max|Size'
COLUMN total_auto_used_pct format 999.99 heading 'Max|Used|PCT'
COLUMN total_auto_free_pct format 999.99 heading 'Max|Free|PCT'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES) total_tbs_free_bytes,
MAX (BYTES) max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT a.tablespace_name,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
-- files.tbs_files files_in_tablespace,
--# files.total_tbs_bytes/1024/1024 total_tablespace_space,
AUTOEXTEND.total_growth_tbs/1024/1024 max_size_of_tablespace,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
)/1024/1024 total_used_space,
--fragments.total_tbs_free_bytes total_tablespace_free_space,
/* ( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
) total_used_pct , */
-- ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
-- ) total_free_pct,
( ( ( /* AUTOEXTEND.total_growth_tbs
- (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes */
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
)
)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_used_pct
/*( ( (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_free_pct */
FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by a.tablespace_name;
Reclaim space :
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
--and file_name like '%d10%'
)
order by file_size-hwm asc
SELECT --tablespace_name,
'Alter database datafile '''||file_name||''' RESIZE ' file_name ,ceil(hwm+2)||'M;' sz, file_size-hwm can_save --, AUTOEXTENSIBLE
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm, AUTOEXTENSIBLE
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
--and file_name like '%d10%'
)
where tablespace_name not in ('SYSTEM','RBS')
and AUTOEXTENSIBLE ='YES'
order by file_size-hwm asc;
Tables with excessive wasted space
SELECT (round(blocks * block_size / 1048576))-(round(num_rows * avg_row_len * (1+(pct_free/100)) * decode (compression,'ENABLED',0.50,1.00) / 1048576)) over_a
llocated_mb,
owner, table_name, blocks, block_size, pct_free,
round(blocks * block_size / 1048576) actual_mb,
round(num_rows * avg_row_len * (1+(pct_free/100)) * decode (compression,'ENABLED',0.50,1.00) / 1048576) estimate_mb,
num_rows, avg_row_len, degree, compression, sample_size, to_char(last_analyzed,'MM/DD/RRRR') last_analyzed
from dba_tables, dba_tablespaces
where dba_tablespaces.tablespace_name = dba_tables.tablespace_name and
(blocks * block_size / 1048576) >= 10 and
abs(round(blocks * block_size / 1048576) - round(num_rows * avg_row_len * (1+(pct_free/100)) * decode (compression,'ENABLED',0.50,1.00) / 1048576)) /
(round(blocks * block_size / 1048576)) >= 0.25 and owner not in ('SYS','SYSMAN','SYSTEM','TOOLS','TOAD','PERFSTAT','SQLTXPLAIN','OEM')
order by 1 desc, owner, table_name;
Tablespace Queries :
col FILE_NAME for a70
select TABLESPACE_NAME,FILE_NAME, BYTES/1024/1024/1024 from dba_data_files where FILE_NAME like 'mountpoint%' and BYTES/1024/1024/1024 > 1
select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024 from dba_data_files where TABLESPACE_NAME = '&1'
Alter tablespace tbspace_name add datafile 'dbfilepath' size 16m autoextend on next 16m maxsize 4G;
Alter database rename file 'oldfilepath' to 'newfilepath'
ALTER DATABASE DATAFILE 'filepath' autoextend on maxsize 5G;
select 'Alter tablespace '|| tablespace_name||' offline normal; '||chr(10)||
'!cp '|| file_name ||' /d44/oradata/Dev/'||substr(file_name,instr (file_name, '/', -1, 1)+1, length(file_name))||chr(10)||
'alter database rename file ''' || file_name || ''' TO ''/d44/oradata/Dev/'|| substr(file_name,instr (file_name, '/', -1, 1)+1, length(file_name)) ||''''||';' ||chr(10)||
'Alter tablespace '||tablespace_name||' online;'||chr(10)||'-- '||bytes/1024/1024/1024||chr(10)||'-- delete this file_name after online :'||file_name
from dba_data_files where file_name like '/d41%'
and file_name like '%x%'
and bytes/1024/1024/1024 between 2 and 3;
Tablespace Utilization :
SET lines 132 pages 66 feedback off
COLUMN tablespace_name format a35 heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible format a6 heading 'Can|Auto|Extend'
COLUMN files_in_tablespace format 999 heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Space'
COLUMN total_used_space format 99,999,999,999 heading 'Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Free|Space'
COLUMN total_used_pct format 999.99 heading 'Used|PCT'
COLUMN total_free_pct format 999.99 heading 'Free|PCT'
COLUMN max_size_of_tablespace format 99,999,999,999 heading 'Max|Size'
COLUMN total_auto_used_pct format 999.99 heading 'Max|Used|PCT'
COLUMN total_auto_free_pct format 999.99 heading 'Max|Free|PCT'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES) total_tbs_free_bytes,
MAX (BYTES) max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT a.tablespace_name,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
-- files.tbs_files files_in_tablespace,
--# files.total_tbs_bytes/1024/1024 total_tablespace_space,
AUTOEXTEND.total_growth_tbs/1024/1024 max_size_of_tablespace,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
)/1024/1024 total_used_space,
--fragments.total_tbs_free_bytes total_tablespace_free_space,
/* ( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
) total_used_pct , */
-- ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
-- ) total_free_pct,
( ( ( /* AUTOEXTEND.total_growth_tbs
- (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes */
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
)
)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_used_pct
/*( ( (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_free_pct */
FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by a.tablespace_name;
Reclaim space :
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
--and file_name like '%d10%'
)
order by file_size-hwm asc
SELECT --tablespace_name,
'Alter database datafile '''||file_name||''' RESIZE ' file_name ,ceil(hwm+2)||'M;' sz, file_size-hwm can_save --, AUTOEXTENSIBLE
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm, AUTOEXTENSIBLE
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
--and file_name like '%d10%'
)
where tablespace_name not in ('SYSTEM','RBS')
and AUTOEXTENSIBLE ='YES'
order by file_size-hwm asc;
Tables with excessive wasted space
SELECT (round(blocks * block_size / 1048576))-(round(num_rows * avg_row_len * (1+(pct_free/100)) * decode (compression,'ENABLED',0.50,1.00) / 1048576)) over_a
llocated_mb,
owner, table_name, blocks, block_size, pct_free,
round(blocks * block_size / 1048576) actual_mb,
round(num_rows * avg_row_len * (1+(pct_free/100)) * decode (compression,'ENABLED',0.50,1.00) / 1048576) estimate_mb,
num_rows, avg_row_len, degree, compression, sample_size, to_char(last_analyzed,'MM/DD/RRRR') last_analyzed
from dba_tables, dba_tablespaces
where dba_tablespaces.tablespace_name = dba_tables.tablespace_name and
(blocks * block_size / 1048576) >= 10 and
abs(round(blocks * block_size / 1048576) - round(num_rows * avg_row_len * (1+(pct_free/100)) * decode (compression,'ENABLED',0.50,1.00) / 1048576)) /
(round(blocks * block_size / 1048576)) >= 0.25 and owner not in ('SYS','SYSMAN','SYSTEM','TOOLS','TOAD','PERFSTAT','SQLTXPLAIN','OEM')
order by 1 desc, owner, table_name;
Tablespace Queries :
col FILE_NAME for a70
select TABLESPACE_NAME,FILE_NAME, BYTES/1024/1024/1024 from dba_data_files where FILE_NAME like 'mountpoint%' and BYTES/1024/1024/1024 > 1
select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024 from dba_data_files where TABLESPACE_NAME = '&1'
Alter tablespace tbspace_name add datafile 'dbfilepath' size 16m autoextend on next 16m maxsize 4G;
Alter database rename file 'oldfilepath' to 'newfilepath'
ALTER DATABASE DATAFILE 'filepath' autoextend on maxsize 5G;