fbpx

Oracle Advenced Storage Management Examples

-- * 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 ;

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…