Check tablespaces on database
SQL>
SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'PERMANENT';
Delete all records on tables if needs
SQL>
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME || ';' FROM ALL_TABLES WHERE OWNER = 'HR';
Before movement operation, enable row movement feature
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE ROW MOVEMENT;' FROM ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
Shrink tables
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' SHRINK SPACE;' from ALL_TABLES WHERE TABLESPACE_NAME = 'HR';
If ORA-10631 error occurs;
ALTER TABLE HR.EMPLOYEES_NEW SHRINK SPACE
Error at line 99
ORA-10631: SHRINK clause should not be specified for this object
Solution:
If FUNCTION-BASED NORMAL index stores on related table, this error occurs
1- Drop the function-based index.
2- Shrink the table.
3- Recreate the index again on the table.
Define a fresh tablespace
SQL>
CREATE TABLESPACE HR_NEW DATAFILE
'/oradata/XE/data.dbf' SIZE 1G AUTOEXTEND ON NEXT 8K MAXSIZE 5G,
'/oradata/XE/data01.dbf'' SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G,
'/oradata/XE/data02.dbf SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE 50G
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
If need, move related tablespace to a fresh tablespace
For tables:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE TS_HR_NEW;' from ALL_TABLES WHERE TABLESPACE_NAME = 'TS_HR';
For indexes:
SQL>
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE TS_HR_NEW;' FROM ALL_INDEXES WHERE TABLESPACE_NAME='TS_HR';
For lob segments:
SQL>
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ')' || ' STORE AS ( TABLESPACE TS_HR_NEW);' FROM DBA_LOBS WHERE TABLESPACE_NAME = 'TS_HR'
Rebuild unusable index for other schema's indexes:
SQL>
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM ALL_INDEXES WHERE STATUS='UNUSABLE' ;
Gather statistics of tables:
SQL>
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' || OWNER || '''' || ',' || '''' || TABLE_NAME || ''');' FROM ALL_TABLES WHERE TABLESPACE_NAME='TS_HR_NEW';
Check size of data files
SQL>
SELECT OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
PARTITION_NAME,
ROUND (BYTES / (1024 * 1024), 2) SIZE_MB,
TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE',
'TABLE PARTITION',
'TABLE SUBPARTITION',
'INDEX',
'INDEX PARTITION',
'INDEX SUBPARTITION',
'TEMPORARY',
'LOBINDEX',
'LOBSEGMENT',
'LOB PARTITION')
AND TABLESPACE_NAME LIKE '%TS_HR%'
--AND SEGMENT_NAME LIKE 'P2010201%'
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'HR'
AND ROUND (BYTES / (1024 * 1024), 2) > 10000
ORDER BY BYTES DESC;
Ref:
https://ozsoyler.blogspot.com.tr/2017/08/eg-table-shrink-usage.html
Showing posts with label shrink. Show all posts
Showing posts with label shrink. Show all posts
Friday, 5 January 2018
Tuesday, 29 August 2017
e.g. shrink table usage
Steps:
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
Labels:
dbms_stats,
oracle,
shrink
Subscribe to:
Posts (Atom)