tablespace

Space Management in Oracle

-- create system managed tablespace using OFA

     CREATE SMALLFILE TABLESPACE USERS

     LOGGING

     DATAFILE SIZE 2M

     AUTOEXTEND ON

     MAXSIZE UNLIMITED

     EXTENT MANAGEMENT LOCAL

     SEGMENT SPACE MANAGEMENT AUTO;

# to add datafile

alter tablespace USERS add datafile size 10M autoextend on;

   

-- create system management tablespace using filesystem

     CREATE TABLESPACE USERS                                                           

     DATAFILE '/ora01/oracle/admin/demo/demo_users_01.dbf'

     SIZE 1M REUSE

     AUTOEXTEND ON

     MAXSIZE UNLIMITED                                                        

     EXTENT MANAGEMENT LOCAL                                                  

     SEGMENT SPACE MANAGEMENT AUTO;      

-- create system managed tablespace using ASM

CREATE SMALLFILE TABLESPACE USERS

   DATAFILE '+ORADATA001'

   SIZE 50M REUSE

   AUTOEXTEND ON

   NEXT 50M

   MAXSIZE 32767M

   LOGGING

   EXTENT MANAGEMENT LOCAL

   SEGMENT SPACE MANAGEMENT AUTO;

ALTER DATABASE DEFAULT TABLESPACE USERS;

# add datafile to tablespace using ASM
 1. check
      db_create_file_dest                  string      +ORADATA001
 2. add
 alter tablespace USERS
   add datafile size 10M autoextend on;

-- create uniform tablespace using ASM

CREATE SMALL TABLESPACE USERS

     DATAFILE '+ORADATA001'

     SIZE 50M

     AUTOEXTEND ON

     NEXT 50M

     MAXSIZE UNLIMITED

     EXTENT MANAGEMENT LOCAL

     UNIFORM SIZE 1M

     SEGMENT SPACE MANAGEMENT AUTO;

 

-- create uniform tablespace using filesystem

    CREATE TABLESPACE USERS

     DATAFILE '/ora01/oracle/admin/demo/demo_users_01.dbf' SIZE 1M

     EXTENT MANAGEMENT LOCAL

     UNIFORM SIZE 128K

     SEGMENT SPACE MANAGEMENT AUTO;

 -- create temporary tablespaces

     CREATE SMALL TEMPORARY TABLESPACE TEMP

     TEMPFILE '/ora01/oracle/admin/demo/demo_temp_01.dbf'

     SIZE 250M  

     EXTENT MANAGEMENT LOCAL

     UNIFORM SIZE 1M;

    

-- Create temporary tablespace TEMP using ASM

CREATE SMALLFILE TEMPORARY TABLESPACE TEMP

    TEMPFILE '+ORADATA001'

    SIZE 60M REUSE

    AUTOEXTEND ON

    NEXT 640K

    MAXSIZE 32767M

    EXTENT MANAGEMENT LOCAL

    UNIFORM SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"

-- add datafile to temporary tablespace

    ALTER TABLESPACE TEMP ADD

    TEMPFILE ‘/ora01/oracle/admin/demo/demo_temp_02.dbf'

    SIZE 250M

    REUSE AUTOEXTEND OFF;

-- to resize temporary tablespace file size

    ALTER DATABASE TEMPFILE '/ora01/oracle/admin/demo/demo_temp_01.dbf' RESIZE 4000M;

    -- generate temporary tablespace ddl

  select 'ALTER TABLESPACE '||ts.name||'

    ADD TEMPFILE '''||tf.name||'''

    size '||tf.create_bytes/1024/1024||'m reuse;'

    from v$tempfile tf, v$tablespace ts

    where tf.ts# = ts.ts#;

-- query temporary tablespace filename

    set lines 120

    col FILE_NAME format a60

    select file_name , bytes/(1024*1024*1024) from dba_temp_files

-- create UNDO tablespace

CREATE SMALLFILE UNDO

   TABLESPACE UNDOTBS1

   DATAFILE     '+ORADATA001'

   SIZE 500M REUSE

   AUTOEXTEND ON

   NEXT 50M

   MAXSIZE 32767M;

Hope this help. Regards Rupam