Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Oracle Database Tablespace Management.

  1. #1
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Oracle Database Tablespace Management.

    To check for all the tablespaces in your database
    issue the following query.


    SQL> select tablespace_name from dba_tablespaces;

    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TEMP
    USERS
    EXAMPLE

    6 rows selected.

    To check for tablespace space related information
    issue the following query.


    SQL> select tablespace_name, file_name, bytes/1024/1024,
    maxbytes/1024/1024, autoextensible, increment_by from dba_data_files;


    TABLESPACE_NAME
    ------------------------------
    FILE_NAME
    ------------------------------------------------------
    BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
    --------------- ------------------ --- ------------
    USERS
    /u01/app/oracle/oradata/orcl/users01.dbf
    5 32767.9844 YES 160

    UNDOTBS1
    /u01/app/oracle/oradata/orcl/undotbs01.dbf
    60 32767.9844 YES 640

    SYSAUX
    /u01/app/oracle/oradata/orcl/sysaux01.dbf
    480 32767.9844 YES 1280

    SYSTEM
    /u01/app/oracle/oradata/orcl/system01.dbf
    670 32767.9844 YES 1280

    EXAMPLE
    /u01/app/oracle/oradata/orcl/example01.dbf
    100 32767.9844 YES 80



    # tablespace_name = Name of the tablespace
    # file_name = Name of the datafile
    # bytes = size of the file in bytes
    # maxbytes = Maximum data file size in bytes.
    # autoextensible = whether auto-extended or not.
    # increment_by = Number of blocks used for autoextension.



    To check for temporary tablespace space related information
    issue the following query.



    SQL> select tablespace_name, file_name, bytes/1024/1024,
    maxbytes/1024/1024, autoextensible, increment_by from dba_temp_files
    /

    TABLESPACE_NAME
    ------------------------------
    FILE_NAME
    ----------------------------------------------------
    BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
    --------------- ------------------ --- ------------
    TEMP
    /u01/app/oracle/oradata/orcl/temp01.dbf
    20 32767.9844 YES 80




    The dba_free_space view describes the free extents available
    in all the tablespaces in the database.


    SQL> select sum(bytes)/1024/1024 mbytes, tablespace_name
    from dba_free_space group by tablespace_name;

    MBYTES TABLESPACE_NAME
    ---------- ------------------------------
    25.875 SYSAUX
    7.0625 UNDOTBS1
    .9375 USERS
    21.5625 EXAMPLE


    Tablespace Creation

    SQL> create tablespace test datafile
    '/u01/app/oracle/oradata/orcl/test01.dbf'
    size 50m autoextend on next 100M
    extent management local
    segment space management auto;

    Tablespace created.


    SQL> select tablespace_name from dba_tablespaces
    /

    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    SYSAUX
    UNDOTBS1
    TEMP
    USERS
    EXAMPLE
    TEST

    7 rows selected.



    SQL> select tablespace_name, file_name, bytes/1024/1024,
    maxbytes/1024/1024, autoextensible, increment_by from
    dba_data_files where tablespace_name='TEST';

    TABLESPACE_NAME
    ------------------------------
    FILE_NAME
    ------------------------------------------------------
    BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
    --------------- ------------------ --- ------------
    TEST
    /u01/app/oracle/oradata/orcl/test01.dbf
    50 32767.9844 YES 12800



    Add a datafile

    SQL> alter tablespace test add datafile
    '/u01/app/oracle/oradata/orcl/test02.dbf'
    size 50M autoextend on next 100M;

    Tablespace altered.


    SQL> select tablespace_name, file_name, bytes/1024/1024,
    maxbytes/1024/1024, autoextensible,
    increment_by from dba_data_files
    where tablespace_name='TEST';

    TABLESPACE_NAME
    ------------------------------
    FILE_NAME
    -------------------------------------------------------
    BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
    --------------- ------------------ --- ------------
    TEST
    /u01/app/oracle/oradata/orcl/test01.dbf
    50 32767.9844 YES 12800

    TEST
    /u01/app/oracle/oradata/orcl/test02.dbf
    50 32767.9844 YES 12800



    Bring a Datafile Online or Offline in Archive Log Mode.

    alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' offline;

    alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' online;

    Drop a datafile


    SQL> alter tablespace test drop datafile
    '/u01/app/oracle/oradata/orcl/test02.dbf';

    Tablespace altered.


    SQL> select tablespace_name, file_name, bytes/1024/1024,
    maxbytes/1024/1024, autoextensible,
    increment_by from dba_data_files
    where tablespace_name='TEST';

    TABLESPACE_NAME
    ------------------------------
    FILE_NAME
    ----------------------------------------------------------
    BYTES/1024/1024 MAXBYTES/1024/1024 AUT INCREMENT_BY
    --------------- ------------------ --- ------------
    TEST
    /u01/app/oracle/oradata/orcl/test01.dbf
    50 32767.9844 YES 12800

    Altering Tablespace Size.

    SQL> alter database datafile '/u01/app/oracle/oradata/orcl/
    test01.dbf' resize 1g;


    Drop a tablespace


    SQL> drop tablespace test including contents and datafiles;

    Tablespace dropped.


    Note: In order to calculate the value of "increment by" in MegaBytes
    you have to know your block size.

    Example:

    SQL> select block_size from dba_tablespaces
    where tablespace_name='TEST';

    BLOCK_SIZE
    ----------
    8192

    So value for the increment by clause would be =

    (increment_by*block_size)/(1024*1024)

    In my case it is (12800*8192)/(1024*1024)=100M

    Changing a Tablespace’s Write Mode.

    To alter the tablespace to read only mode.

    SQL> alter tablespace test read only;


    To alter the tablespace to read write mode.

    SQL> alter tablespace test read write;



    Renaming a Tablespace and datafiles.

    SQL> alter tablespace test rename to abc;

    Renaming a tablespace does not rename its datafiles.
    So, to rename or relocate a datafile do the following.

    Scenario 1: When the database is up.

    SQL> alter tablespace abc offline;

    If you are renaming or relocating the datafile execute the following command.

    $mv /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/abc01.dbf

    Update the control file with the ALTER TABLESPACE statement:

    alter tablespace abc
    rename datafile
    '/u01/app/oracle/oradata/orcl/test01.dbf'
    to
    '/u01/app/oracle/oradata/orcl/abc01.dbf'

    SQL> alter tablespace abc online;

    Scenario 2: When the database is down.

    SQL> shutdown immediate

    $mv /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/abc01.dbf

    SQL>startup mount

    SQL> alter database rename file '/u01/app/oracle/oradata/orcl/test01.dbf'
    to
    '/u01/app/oracle/oradata/orcl/abc01.dbf';

    SQL> alter database open;


    Controlling the Generation of Redo.

    In some situations you may not require generation of redo log
    data. For example in data warehouse environment where you
    perform direct path inserts or use SQL*LOADER to load data.
    In these situations you can use NOLOGGING option to turn off
    redo generation.

    If you are creating a new tablespace you can use the option
    as following:

    SQL> create tablespace test datafile
    '/u01/app/oracle/oradata/orcl/test01.dbf'
    size 50m autoextend on next 100M
    extent management local
    segment space management auto
    nologging;

    If the tablespace is already created you can use the
    following mode to alter its logging status.

    SQL>alter tablespace test nologging:

    SQL> select tablespace_name, logging from dba_talespaces where
    tablespace_name='TEST';

    TABLESPACE_NAME LOGGING
    ---------------------- ---------

    TEST NOLOGGING
    Last edited by ajaychandi; 01-12-2012 at 10:53 PM.

  2. #2
    Best practices :-

    - Do not create objects in the SYSTEM tablespace. The system tablespace is reserved for the data dictionary.
    - Don't create tablespaces, with hundreds of small datafiles, these files needs to be checkpointed, resulting is unnecessary processing.

+ Reply to Thread

Similar Threads

  1. Oracle Database User Management.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 06-13-2013, 12:53 PM
  2. Oracle Database Online Redo Log Files Management.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-13-2012, 10:40 PM
  3. What happens when we put either database or tablespace in begin backup mode
    By nemat in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 01-13-2012, 02:16 PM
  4. Oracle Database Temporary Tablespace Management.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-12-2012, 11:12 PM
  5. Oracle Database Undo Tablespace Mangement.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-12-2012, 11:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics