Migration and Chaining
-- * 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
---------------------------------------------------------------------------------------- -- 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
---------------------------------------------------------------------------------------- -- 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
------------------------------------------------ -- 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
------------------------------------------------ -- 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 ---------------------------------------------------------------------------- -- * 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 ;