/*
|| Oracle 10g Storage Enhancements Listing
||
|| Contains examples of new Oracle 10g Tablespace features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new storage features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/

----- 
-- Listing 1.1: Creating a BIGFILE Tablespace
-----

-- Set the database default to create BIGFILE tablespaces
-- (SMALLFILE is the initial setting unless changed)
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

-- Create a sample BIGFILE tablespace. Note that the BIGFILE
-- directive isn't necessary since the default tablespace type
-- has been set for the database
DROP TABLESPACE big_lmpt INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE big_lmpt 
    DATAFILE '/u02/oradata/orcl/big_lmpt01.dbf' 
    SIZE 1G REUSE
    EXTENT MANAGEMENT LOCAL
    UNIFORM SIZE 128M
    SEGMENT SPACE MANAGEMENT AUTO; 

----- 
-- Listing 1.2: Querying Oracle 10g tablespace properties and features
-----

-- Show the default tablespace type (BIGFILE vs. SMALLFILE) for the database
TTITLE 'Default Tablespace Type (BIGFILE / SMALLFILE)'
COL property_name   HEADING 'Database Property'
COL property_value  HEADING 'Tablespace|Type'
SELECT 
     property_name
    ,property_value
  FROM database_properties
 WHERE property_name = 'DEFAULT_TBS_TYPE'
;

-- Are there any existing BIGFILE tablespaces?
TTITLE 'Tablespace Status (From DBA_TABLESPACES)'
COL tablespace_name                 HEADING 'Tablespace Name'
COL bigfile             FORMAT A5   HEADING 'Big|File?'
COL status                          HEADING 'Status'
COL contents                        HEADING 'Contents'
COL extent_management               HEADING 'Extent|Mgmt'
SELECT 
     tablespace_name
    ,status
    ,contents
    ,bigfile 
    ,extent_management
  FROM dba_tablespaces
 ORDER BY tablespace_name
;

TTITLE 'Tablespace Status (From V$TABLESPACE)'
COL name                            HEADING 'Tablespace Name'
COL bigfile             FORMAT A5   HEADING 'Big|File?'
COL flashback_on        FORMAT A5   HEADING 'Flash|Back|On?'
SELECT 
    name
   ,bigfile 
   ,flashback_on
  FROM v$tablespace
 ORDER BY name
;

----- 
-- Listing 1.3: Specifying Database-Level Permanent and Temporary Tablespaces
-----

-- Specify Permanent and Temporary Tablespaces in a CREATE DATABASE statement
CREATE DATABASE mysample
    DATAFILE '/u01/app/oracle/oradata/mysample/system01.dbf' 
        SIZE 200M REUSE
    SYSAUX 
        DATAFILE '/u01/app/oracle/oradata/mysample/sysaux01.dbf' 
        SIZE 300M REUSE
    DEFAULT TABLESPACE others
        DATAFILE '/u02/oradata/mysample/others01.dbf' 
        SIZE 100M 
        SEGMENT SPACE MANAGEMENT AUTO 
    DEFAULT TEMPORARY TABLESPACE temp
        TEMPFILE '/u03/oradata/mysample/temp01.tmp' 
        SIZE 60M
    UNDO TABLESPACE untotbs
        DATAFILE '/u04/oradata/mysample/undotbs01.dbf'
        SIZE 100M;

-- Alter an existing database to specify a default Permanent Tablespace. The
-- tablespace must already exist!
ALTER DATABASE DEFAULT TABLESPACE others;

-- Alter an existing database to specify a default Temporary Tablespace. The
-- tablespace must already exist!
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

----- 
-- Listing 1.4: Creating Temporary Tablespace Groups
-----

-- Create a new temporary tablespace and assign it to a new
-- temporary tablespace group named SORTGRP1. Oracle will create
-- the temporary tablespace group if it doesn't exist
CREATE TEMPORARY TABLESPACE sortwk01 
    TEMPFILE '/u01/app/oradata/orcl/sortwk01.tmp'
    SIZE 128M REUSE
    TABLESPACE GROUP sortgrp1;

-- Create another new temporary tablespace and assign it to an
-- existing temporary tablespace group (SORTGRP1)
CREATE TEMPORARY TABLESPACE sortwk02 
    TEMPFILE '/u02/oradata/orcl/sortwk02.tmp'
    SIZE 128M REUSE
    TABLESPACE GROUP sortgrp1;

-- Create another new temporary tablespace, but don't assign it 
-- to any temporary tablespace group
CREATE TEMPORARY TABLESPACE sortwk03 
    TEMPFILE '/u03/oradata/orcl/sortwk03.tmp'
    SIZE 256M REUSE;
    
-- Add temporary tablespace SORTWK03 to sort group SORTGRP1
ALTER TABLESPACE sortwk03 TABLESPACE GROUP sortgrp1; 

-- Shift a user to the SORTGRP1 temporary tablespace group
ALTER USER hr TEMPORARY TABLESPACE sortgrp1;

-----
-- Now drop all these temporary tablespaces. Once they are all removed, 
-- Oracle will implicitly delete tablespace group SORTGRP1. Oracle will
-- not permit them to be dropped, however, until any user that's been
-- assigned to the temporary tablespace group is migrated to another 
-- existing temporary tablespace or temporary tablespace group!
-----
ALTER USER hr TEMPORARY TABLESPACE temp;
DROP TABLESPACE sortwk03;
DROP TABLESPACE sortwk02;
DROP TABLESPACE sortwk01;