-- * Migration, Chaining And PCTFREE / PCTUSED (Block Parameters)
-- * Migration
---------------
-- * Occurs only during updates
-- * when row < block size
-- 1. On update: Row length increases, exceeding the available free space in the block.
-- 2. Data needs to be stored in a new block.
-- 3. Original physical identifier of row (ROWID) is preserved.
-- 4. Index access to the block now requires two blocks access to retrieve data. (Increased
-- IO)
-- * PCTFREE
-------------
-- * Avoid migration by using PCTFREE
-- * In percent, space within a block saved for future UPDATES, cannot be used for new
-- INSERTS
-- * Default is 10%
-- * can be used for automatic and manual block management.
-- ((average row size) - (initial row size)) / (average row size) * 100 = PCTFREE
-- ( 500KB - 450KB ) / (500KB) * 100 = 10%
-- * PCTUSED
-------------
-- * The PCTUSED parameter represents the minimum percentage of the used space that the Oracle
-- server tries to maintain for each data block of the table.
-- * This parameter has a default value of 40 percent. When a data block is filled to the limit
-- determined by the value of the PCTFREE parameter, Oracle considers the block unavailable
-- for the insertion of new rows.
-- * The block is unavailable for the insertion of new rows until the percentage filled by the
-- data of that block falls below the value of the PCTUSED parameter.
-- * Until the percentage of the block falls below the value of the PCTUSED parameter, Oracle
-- uses the free space of the data block only for updating the rows contained in the data
-- block.
-- * With ASSM, each block is divided into four sections:
-- 1. FS1 (between 0 and 25% of free space)
-- When FS1 block gets the “full” status, it means that a block is no longer
-- available for inserts.
-- 2. FS2 (25% to 50% free)
-- 3. FS3 (50% to 75% free)
-- 4. FS4 (75% to 100% free).
-- 1. The block on the left is an FS3 block because it has between 50% and 75% free space.
-- 2. After some insert and update statements, PCTFREE is reached (the dashed line) and
-- it is no longer possible to insert new rows in that block. The block is now considered as -- a “full” or FS1 block.
-- 3. The block is considered for insertion again, as soon as its free space level
-- drops below the next section. In the preceding case, it gets status FS2 as soon as the
-- free space is more than 25%.
-- * PCTFREE - The maximum percent INSERTS on a table can reach
-- * PCTUSED - The minimum percent from which INSERTS are available
-- - The challenge:
-- 1. Maximize space utilization. (No unused space in blocks)
-- 2. Minimize row migrations. (Rows that migrate to other blocks after updates,
-- due to insufficient free space in their original block)
-- * Chaining:
--------------
-- - When row > block size
-- - May be caused from insert or update
-- - When a row is larger than the block size.
-- - Row is then split among two or more blocks. (Several row pieces)
-- - 1 row = Few block IOs.
-- Identify Chaining and Migration
----------------------------------
DROP TABLE longrec_tab;
CREATE TABLE longrec_tab
(a varchar2(3000),
b varchar2(3000),
c varchar2(3000)) TABLESPACE USERS;
-- Set the first column with 3K:
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO LONGREC_TAB(a) VALUES (LPAD('*',3000,'*'));
END LOOP;
END;
/
-- Check stats:
ANALYZE TABLE longrec_tab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TAB';
-- Up to this point
-- 1. No chaining / migration
-- 2. Each block contains 1 row
-- Set the second column with 3K. Each row grows from 3K to 6K:
UPDATE longrec_tab
SET b=LPAD('*',3000,'*');
ANALYZE TABLE longrec_tab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TAB';
-- Fixing Migration
ALTER TABLE longrec_tab MOVE;
ANALYZE TABLE longrec_tab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TAB';
-- * Alternatively you can by backup the rows, delete them and reinserting them
-- back to the table
-- * that one makes sense when only a small amount of rows in a table are migrated
-- Chaining
-----------
-- Updating the third column to 3K. Row grows to 9K:
UPDATE longrec_tab
SET c=LPAD('*',3000,'*');
-- Analyze
ANALYZE TABLE longrec_tab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TAB';
-- Notice same column (CHAIN_CNT) indicates chaining / migration
-- Use LIST CHAINED ROWS
-- Must create this table first
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
ANALYZE TABLE longrec_tab LIST CHAINED ROWS;
SELECT owner_name,
table_name,
head_rowid
FROM chained_rows
WHERE rownum <= 10 ;
-- If you want to know exactly which rows migrated / chained use this query
SELECT rowid , NVL(vsize(a),0) + NVL(vsize(b),0) + NVL(vsize(c),0) AS "ROWSIZE"
FROM longrec_tab
WHERE rowid IN (SELECT head_rowid FROM chained_rows)
AND rownum <= 10;
-- Fixing Chaining
SELECT tablespace_name, block_size
FROM dba_tablespaces;
conn / as sysdba
ALTER SYSTEM SET db_16k_cache_size= 16m; -- must be executed from CDB
conn sys/password@pdborcl as sysdba
DROP TABLESPACE longrec_tbs INCLUDING CONTENTS AND DATAFILES ;
CREATE TABLESPACE longrec_tbs
DATAFILE 'C:\longrec_df.dbf' SIZE 30M REUSE
BLOCKSIZE 16K;
ALTER TABLE longrec_tab MOVE TABLESPACE longrec_tbs;
-- Analyze
ANALYZE TABLE longrec_tab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN
FROM dba_tables
WHERE TABLE_NAME = 'LONGREC_TAB';
----------------------------------------------------------------------------------------
-- DEFERRED_SEGMENT_CREATION (11gR2)
----------------------------------------------------------------------------------------
-- * Oracle 11gR2 creates new tables without any storage allocated to them.
-- (Only registers them in the Data dictionary)
-- * The actual segment will be created only on the first insert.
-- * Advantages:
-- - Saves disk space for applications that create many tables upon installation.
-- (many of which might never be populated)
-- - The installation time is reduced significally.
-- * We can control this behavior in two ways:
-- 1. The DEFERRED_SEGMENT_CREATION parameter
-- - Default is TRUE.
-- - Can change at system or session level.
-- 2. The SEGMENT CREATION clause of the CREATE TABLE.
-- - SEGMENT CREATION DEFERRED | IMMEDIATE
--
-- * Supported only for regular (Heap) tables.
-- * Indexes inherit the table's definition.
conn hr/hr@pdborcl
DROP TABLE seg_test ;
CREATE TABLE seg_test(c number, d varchar2(500));
SELECT segment_name FROM user_segments
WHERE segment_name = 'SEG_TEST'
INSERT INTO seg_test VALUES(1, 'aaaaaaa');
SELECT segment_name FROM user_segments
WHERE segment_name = 'SEG_TEST';
DROP TABLE seg_test PURGE;
CREATE TABLE seg_test(c number, d varchar2(500))
SEGMENT CREATION IMMEDIATE;
SELECT segment_name FROM user_segments
WHERE segment_name = 'SEG_TEST';
conn sys/password@pdborcl as sysdba
SHOW PARAMETER deferred_segment_creation
----------------------------------------------------------------------------------------
-- Table Compression
----------------------------------------------------------------------------------------
-- * Advantages
-- 1. Reduce space
-- 2. Reduce I/O
-- * Table compression:
-- - Specified via the COMPRESS / NOCOMPRESS clause. (Default is NoCompress)
-- - Eliminates duplicate values in a data block.
-- (Effective for tables with highly redundant data)
-- - Transparent to the application. (No need to de-compress)
-- - Supported for regular (Heap) and Partitioned tables only.
-- * Oracle recommends to compress all data to reduce storage, IO and memory.
-- * Oracle Database supports two main methods of table compression:
-- 1. Basic table compression
-- - Compresses data only during loads. (Direct loads, CTAS etc)
-- - specify using COMPRESS [BASIC]. (Until 11gR2 - COMPRESS FOR DIRECT_LOAD OPERATIONS)
--
-- 2. OLTP table compression
-- - Compresses data during all DML operations on the table.
-- - specify using COMPRESS FOR OLTP. (Until 11gR2 - COMPRESS FOR ALL OPERATIONS)
--
-- * As compression occurs only when the block reaches PCTFREE, overhead is minimal:
-- Empty Block => Inserts (Not compressed) => PCTFREE is reached =>
-- Compression => Inserts....
-- * Architecture:
-- - Duplicate values in the block are stored once in a "Symbol Table"
-- at the beggining of the block.
-- - All their occurances in the block are then replaced
-- by a short reference to the symbol table.
-- - Therefore, each block un-compression is self contained.
-- Without compression:
DROP TABLE comp_tab PURGE;
CREATE TABLE comp_tab
AS SELECT * FROM hr.employees WHERE rownum <=5 ;
BEGIN
FOR i IN 1..13 LOOP
INSERT INTO comp_tab
SELECT * FROM comp_tab;
END LOOP;
END;
/
SELECT COUNT(*) FROM comp_tab;
SELECT bytes/1024 AS size_kb FROM dba_Segments
WHERE segment_name = 'COMP_TAB';
-- Add compression to the table:
ALTER TABLE comp_tab COMPRESS FOR ALL OPERATIONS ;
-- Insert the data again or simply rebuild the table:
ALTER TABLE comp_tab MOVE;
SELECT bytes/1024 AS size_kb FROM dba_Segments
WHERE segment_name = 'COMP_TAB';
DROP TABLE comp_tab ;
CREATE TABLE comp_tab (id number) COMPRESS FOR ALL OPERATIONS ;
DROP TABLE comp_tab ;
------------------------------------------------
-- Compression Advisor (11gR2)
------------------------------------------------
-- * Estimates space savings for objects.
-- * Recommends on:
-- - Compression strategy
-- - Compression algorithm.
-- - Sorts on a particular column. (Increased compression ratio)
-- - Presents tradeoffs between different compression algorithms.
-- - Can be activated using:
-- 1. OEM - Graphical interface. (For OLTP compression)
-- 2. DBMS_COMPRESSION
-- * DBMS_COMPRESSION:
-- - GET_COMPRESSION_RATIO:
-- - Calculates the possible compression ratio for a table.
-- - GET_COMPRESSION_TYPE :
-- - Returns the compression type for a given row.
DROP TABLE tab1 PURGE;
CREATE TABLE tab1 (
id NUMBER,
code VARCHAR2(20),
description VARCHAR2(50),
clob_description CLOB,
created_date DATE,
CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION tab1_part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users);
CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;
INSERT INTO tab1
SELECT level,
CASE
WHEN MOD(level,2)=0 THEN 'CODE1'
ELSE 'CODE2'
END,
CASE
WHEN MOD(level,2)=0 THEN 'Description for CODE1'
ELSE 'Description for CODE2'
END,
CASE
WHEN MOD(level,2)=0 THEN 'CLOB description for CODE1'
ELSE 'CLOB description for CODE2'
END,
CASE
WHEN MOD(level,2)=0 THEN SYSDATE
ELSE ADD_MONTHS(SYSDATE, 12)
END
FROM dual
CONNECT BY level <= 100000;
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.get_compression_ratio (
scratchtbsname => 'USERS',
ownname => 'SYS',
objname => 'TAB1',
subobjname => NULL,
comptype => DBMS_COMPRESSION.comp_advanced,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
objtype => DBMS_COMPRESSION.objtype_table
);
DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
END;
/
------------------------------------------------
-- Proactive Tablespace Monitoring
------------------------------------------------
-- * Tablespace disk space usage is proactively managed using:
-- - Alerts:
-- - Set Warning/Error usage thresholds, and receive notifications.
-- (Tablespace: Edit => Storage => Thresholds)
-- - Tracked every 10 minutes.
-- - Growth analysis:
-- - Oracle uses AWR statistics for growth trend analysis and capacity planning.
-- - Via Segment Advisor
-- * Resolve space problem by:
-- - Adding or resizing data file
-- - Setting AUTOEXTEND ON
-- - Shrinking objects
-- - UNDO - Reducing UNDO_RETENTION.
-- - TEMP - Checking for long-running queries.
-- Setting an alert threshold for a tablespace:
-----------------------------------------------
DROP TABLESPACE mytbs2 INCLUDING CONTENTS AND DATAFILES;
-- Create a 20MB tablespace with a table in it:
CREATE TABLESPACE mytbs2 DATAFILE 'C:\mytbs2.dbf' SIZE 20m REUSE;
CREATE TABLE bigtab TABLESPACE mytbs2 AS SELECT * FROM hr.employees;
EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD( -
DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, /* Metric */ -
DBMS_SERVER_ALERT.OPERATOR_GE, '60', /* Warning */ -
DBMS_SERVER_ALERT.OPERATOR_GE, '80', /* Critical */ -
1,1,'orcl', -
DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,'MYTBS2') /* Object type and name */ ;
INSERT INTO bigtab SELECT * FROM bigtab ;
SELECT bytes/1024/1024 size_mb
FROM user_segments
WHERE segment_name = 'BIGTAB';
-- Check again for outstanding alerts:
SELECT REASON, SUGGESTED_ACTION
FROM dba_outstanding_alerts;
------------------------------------------------
-- Shrinking Segments
------------------------------------------------
-- High Water Mark
------------------
DROP TABLE hr.mytab PURGE;
CREATE TABLE hr.mytab AS SELECT * FROM hr.employees ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- BLOCKS - Below HWM
-- EMPTY_BLOCKS - Above HWM
-- HWM is mainly needed for two reasons
-- 1. RMAN Backups
-- 2. Full Table Scans
-- Insert some rows to the table
INSERT INTO hr.mytab SELECT * FROM hr.mytab ;
COMMIT ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
-- HWM Changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- Delete some rows from the table
DELETE FROM hr.mytab WHERE rownum <= 200 ;
COMMIT ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
-- HWM remains the same
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- 1. ALTER TABLE .. MOVE [TABLESPACE ...]; (9i)
-- * Rebuilds the table efficiently.
-- * Will lower the HWM.
-- * Solves Row Migration.
-- * Needs a full (exclusive) table lock
-- * Indexed will be left unusable and must be rebuilt. (Because ROWIDs are changed)
-- DEMO 1
---------
ALTER TABLE hr.mytab MOVE ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
-- HWM Changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- 2. ALTER TABLE .. SHRINK SPACE ; (10g)
-- * eliminates unused space below the HWM.
-- * Will lower the HWM.
-- * Does not solve Row Migration.
-- * Can be done online (Short Row-level locks and a short table-level lock at the end)
-- * Indexes are not invalidated. (Updated whenever a row moves)
-- * The table must have "Enable Row Movement". (ALTER TABLE ..)
-- * Tablespace must be Local ASSM.
-- * Shrink advantages:
-- - Full table scans (fewer and denser blocks)
-- - Faster and smaller backups. (As RMAN skips blocks above the HWM)
-- - For Indexes - Better index access
-- (More compact tree => Less levels, Shorted range scans => Fewer I/Os)
-- * Shrink space Action:
-- 1. Compaction - Rows are moved to the beggining of the segment.
-- - Uses DELETE/INSERT internally, which do not fire triggers.
-- 2. Shrink - The high-water mark (HWM) is adjusted and the unused space is released.
-- * COMPACT performs the first phase only:
-- - If locks prevents from changing the HWM.
-- - if we have long-running queries that might attempt to read released and reclaimed
-- blocks.
-- * CASCADE - Shrinks also dependant objects.
-- * Can perform on Tables (Heap), IOT, Partitions, Indexes, LOBs & Mviews.
DROP TABLE hr.mytab PURGE;
CREATE TABLE hr.mytab AS SELECT * FROM hr.employees ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- Insert some rows to the table
INSERT INTO hr.mytab SELECT * FROM hr.mytab ;
COMMIT ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
-- HWM Changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- Delete some rows from the table
DELETE FROM hr.mytab WHERE rownum <= 200 ;
COMMIT ;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
-- HWM remains the same
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
-- Perform shrink:
ALTER TABLE hr.mytab SHRINK SPACE;
-- Must first enable row movement:
alter table "HR"."MYTAB" enable row movement;
-- Perform shrink:
ALTER TABLE hr.mytab SHRINK SPACE;
-- Analyze
ANALYZE TABLE hr.mytab COMPUTE STATISTICS;
-- HWM changed
SELECT NUM_ROWS, CHAIN_CNT, AVG_ROW_LEN, BLOCKS, EMPTY_BLOCKS
FROM dba_tables
WHERE TABLE_NAME = 'MYTAB';
------------------------------------------------
-- Segment Advisor
------------------------------------------------
-- * Identifies segments that have space available for reclamation
-- * Can be invoked at the segment or tablespace level.
-- * Executed automatically during the daily maintenance windows, or manually.
-- * Results can be viewed and implelemted immediatly.
-- * Results are stored in the AWR for future reference.
--------------------------------------------------------------------------
-- Segment Advisor
--------------------------------------------------------------------------
conn sys/password@pdborcl as sysdba
DROP TABLE hr.test;
CREATE TABLE hr.test AS SELECT * FROM all_objects;
INSERT INTO hr.test SELECT * FROM hr.test;
INSERT INTO hr.test SELECT * FROM hr.test;
INSERT INTO hr.test SELECT * FROM hr.test;
commit;
-- (At least 200000 rows)
SELECT bytes/1024/1024 size_mb
FROM dba_segments
WHERE segment_name = 'TEST' AND OWNER = 'HR';
SELECT COUNT(*) FROM hr.test ;
--------------------------------------------------------------------------
-- Create a Segment Advisor task at a tables level (HR.TEST)
--------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE seg_adv_table
(p_table VARCHAR2,
p_schema VARCHAR2 := user)
AS
l_object_id NUMBER;
v_sql CLOB :=
'SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM all_advisor_findings f
JOIN all_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = ''SEG_ADV''';
BEGIN
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(
task_name => 'SEG_ADV'
);
EXCEPTION
WHEN OTHERS THEN -- ORA-13605
NULL;
END;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'SEG_ADV',
task_desc => 'Segment Advisor on a table');
DBMS_ADVISOR.create_object (
task_name => 'SEG_ADV',
object_type => 'TABLE',
attr1 => p_schema,
attr2 => p_table,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'SEG_ADV',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'SEG_ADV');
-- p(v_sql);
END seg_adv_table;
/
exec seg_adv_table('TEST','HR')
-- Or instead of invoking P, select the findings manually:
col Message for a60
col more_info for a60
SELECT f.message,
f.more_info
FROM dba_advisor_findings f
WHERE f.task_name = 'SEG_ADV';
-- Delete some rows:
DELETE FROM hr.test
WHERE rownum < 130000;
commit;
-- And run the Segment Advisor again...
exec seg_adv_table('TEST','HR')
col Message for a60
col mode_info for a60
SELECT f.message,
f.more_info
FROM dba_advisor_findings f
WHERE f.task_name = 'SEG_ADV';
----------------------------------------------------------------------------
-- * Resumable Space Allocation
----------------------------------------------------------------------------
-- * Resumable space allocation whole purpose is to offer an alternative to termination of a large
-- database operation requiring more disk space, instead of terminating, While the operation is
-- suspended, you can allocate more disk space on the destination.
-- * Resumable Space Allocation
-------------------------------
-- * The following conditions can trigger resumable space allocation:
-- * Out of disk space in a permanent or temporary tablespace
-- * Maximum extents reached on a tablespace
-- * User space quota exceeded
-- * Resumable Commands
-----------------------
-- * Resumable SELECT Statements - will be resumable only when they run out of space
-- in a temporary tablespace.
-- * Resumable DML Commands - such as INSERT, UPDATE, and DELETE may
-- cause an out of space condition.
-- * Resumable SQL*Loader Operations - SQL*Loader import operations may
-- cause an out of space condition.
-- * Resumable DDL Commands - All DDL commands that allocate disk space for
-- new or existing segments are resumable:
-- * Configuring Resumable Space Allocation
-------------------------------------------
conn sys/password@pdborcl as sysdba
SHOW PARAMETER RESUMABLE_TIMEOUT
-- * dynamic parameter.
-- * Value of 0 (default) means Resumable Space Allocation is disabled.
-- * A non-zero value (in seconds) for RESUMABLE_TIMEOUT means Resumable Space
-- Allocation is enabled.
-- * If it is enabled at the system level, then all sessions can take advantage of
-- Resumable Space Allocation.
conn sys/password@pdborcl as sysdba
alter system set resumable_timeout = 7200;
-- * Fine grained Resumable Space Allocation
--------------------------------------------
-- * Better control of which users can enable Resumable Space Allocation, is to grant
-- the RESUMABLE system privilege.
-- * Now, HR user can enable it at will using the ALTER SESSION command.
grant resumable to hr ;
conn hr/hr@pdborcl
-- default resumable timeout value is 7200 seconds
alter session enable resumable;
-- It can be override by issuing the folowing command
alter session enable resumable timeout 10000;
-- To make it easy for the DBA to identify your resumable statement in the data dictionary
-- you can enable Resumable Space Allocation with the NAME parameter:
alter session enable resumable name 'HR - Creating Big Table';
conn sys/password@pdborcl as sysdba
select user_id, session_id, status, name, sql_text
from dba_resumable;
-- Disable Resumable Space Allocation
alter session disable resumable;
-- DEMO
-------
conn sys/password@pdborcl as sysdba
grant resumable to hr;
create tablespace hr_tbs datafile 'C:\hr_df.dbf' size 5m autoextend off;
conn hr/hr@pdborcl
CREATE TABLE hr_test (a VARCHAR2(3000)) TABLESPACE hr_tbs;
-- Fill up the table with data:
BEGIN
FOR i IN 1..5000 LOOP
INSERT INTO hr_test VALUES (LPAD('*',3000,'*'));
END LOOP;
END;
/
-- Enable Resumable Space Allocation in the HR session for 3600 seconds (60 minutes):
alter session enable resumable timeout 3600;
-- Set resumable name
alter session enable resumable name 'HR - Creating Big Table';
-- Try again
BEGIN
FOR i IN 1..5000 LOOP
INSERT INTO hr_test VALUES (LPAD('*',3000,'*'));
END LOOP;
END;
/
-- The alert log will reflect the statement suspension.
-- DBA_RESUMABLE will provide additional details about the suspended statement.
COL NAME FOR A20
COL ERROR_MSG FOR A30
SET LINES 200
select user_id, instance_id, status, name, error_msg
from dba_resumable
-- As DBA resize the DF
conn sys/password@pdborcl as sysdba
ALTER DATABASE DATAFILE 'C:\hr_df.dbf' RESIZE 50M ;
DROP TABLESPACE hr_tbs INCLUDING CONTENTS AND DATAFILES ;
