Table spaces and operation with table spaces

                                 

                                            

                                                Table spaces and operation with table spaces

What is Tablespace

• Table space is collection of data files.
• One data file belongs to one table space
• But a table space contains many data files.
• That’s physical structure of table space
• But logically table space contains segments.
• A segment can represent a table, indexes, objects etc.
• In 10 g or onward minimum two tablespace are required.

These are: 

        1- SYSTEM tablespace 

        2- SYSAUX tablespace 

        

        By defaults, system create 6 tablespaces

Special Kind of Tablespaces 

    1- Big file Tablespace 

            • Maximum size could be 128 TB 

            • Manage whole tablespace as unit 

            • Does not worry about its size 

    2- Temporary tablespace 

         

            • Used to contains temporary segments 

            • Like UNION, JOIN, ORDER BY

    3- System Tablespace

        By defaults system tablespace used as temporary tablespace. 

            • Permanent in nature but segments are temporary in nature. 

            • Mandatory in the system 

            • Contains very important data

    It contains Data dictionary:

        

     it is a Meta data, means data about data 

            • For example: when we create a table, who create the table, what columns are there in the table.

    Tablespaces and Data Files 

    • The size of tablespace is the total size of all the data files in the tablespace. 

    • Smallest unit of storage is a Block in database. 

    • We can define the Block size when database created, and we can’t alter it. 

    • You can add more space to a tablespace by adding more data files in the tablespace. 

 Types of Table Spaces:

        Permanent :

            You use permanent tablespaces to store your user and application data. Each user is assigned a default permanent tablespace. 

             Example: System Table Space

     

    Undo:

        It Create and manage undo data in the undo tablespace. In Oracle it is use to role back the transactions and to help with database recovery. A database instance can have only one active undo tablespace. 

     Read: 

        only It is a permanent tablespace that can only be read, no writes can take place, but the tablespace can be made read/write.

Temporary: 

     Temporary tablespaces are used for storing temporary data. An Oracle database gets a temporary tablespace when the database is created. You would create another temporary tablespace if you were creating a temporary tablespace group 

To create temporary table Space: 

        

            ➢ CREATE TABLESPACE TEMPDATAFILE'/disk5/oradata/MYDB01/temp01.dbf' SIZE 300M DEFAULT STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0 MAXEXTENTS UNLIMITED) TEMPORARY;

Creating a Tablespace 

        • Using the CREATE TABLESPACE statement creates a tablespace. 

        • CREATE TABLESPACE APPLICATION_DATA, 

TIP: 

        • The tablespace name cannot exceed 30 characters. 

        • The name should begin with an alphabetic character and can contain alphabetic characters, numeric characters, and the special characters #, _, and$. 

Dictionary-Managed Tablespaces 

    

            In dictionary-managed tablespaces, all extent information is stored in the data dictionary. A simple example of a dictionary-managed tablespace creation command is as follows: 

            ➢ CREATE TABLESPACE APPL_DATA DATAFILE '/disk3/oradata/DB01/appl_data01.dbf' SIZE 100M;

EXTENT MANAGEMENT DICTIONARY;

            You may need to create more files if there are any operating system limits on the file size. For example, if you need to allocate 6GB for the tablespace, and the operating system allows only a 2GB maximum, you need three data files for the tablespace. The statement is then as follows: 

➢ CREATE TABLESPACE APPL_DATADATAFILE '/disk3/oradata/DB01/appl_data01.dbf' SIZE 2000M, '/disk3/oradata/DB01/appl_data02.dbf' SIZE 2000M, '/disk4/oradata/DB01/appl_data03.dbf' SIZE 2000M;

DEFAULT STORAGE 

    Specifies the default storage parameters for new objects that are created in the tablespace. 

BLOCKSIZE 

    

    Specifies the block size that is used for the objects created in the tablespace. By default, this block size is the database block size, which you define using the DB_BLOCK_SIZE parameter when creating the database.

 INITIAL 

        Specifies the size of the object’s (segment’s) first extent. NEXT specifies the size of the segment’s next and successive extents. The size is specified in bytes. You can also specify the size in KB by post-fixing the size with K, or you can specify MB by post-fixing the size with M. 

MINEXTENTS 

        Specifies the total number of extents allocated to the segment at the time of creation. Using this parameter, you can allocate a large amount of space when you create an object, even if the space available is not contiguous. 

MINIMUM EXTENT 

        Specifies that the extent sizes are a multiple of the size specified. You can use this clause to control fragmentation in the tablespace by allocating extents of at least the size specified and as always a multiple of the size specified. 

LOGGING 

        Specifies that the DDL operations and direct-load INSERT are recorded in the redo log files. LOGGING is the default, and you can omit the clause. When you specify NOLOGGING, data is modified with minimal logging and hence the commands complete faster. 

ONLINE 

        Specifies that the tablespace be created online or available as soon as it is created. ONLINE is the default, 

PERMANENT 

        Specifies whether the tablespace is to be used to create permanent objects such as tables, indexes, and so on. PERMANENT is the default, and hence you can omit it.

Using Non-standard Block Sizes

        When creating the database, you specify the block size in the initialization parameter using the parameter DB_BLOCK_SIZE This specification is known as the standard block size for the database. When creating a tablespace with a non-standard block size, you must specify the BLOCKSIZE clause in the CREATE TABLESPACE statement. The initialization parameter is DB_nK_CACHE_SIZE ; n is the non-standard block size. It can have the values 2, 4, 8, 16, or 32,

Altering a Tablespace

You can alter a tablespace using the ALTER TABLESPACE command. This command allow you to do the following: 

    

        - Change the default storage parameters of a dictionary-managed tablespace

        - Change the extent allocation and LOGGING/NOLOGGING modes 

        - Change the tablespace from PERMANENT to TEMPORARY or vice versa 

        - Change the availability of the tablespace 

        - Make the tablespace read-only or read-write 

        - Coalesce the contiguous free space 

        - Add more space by adding new data files or temporary files 

        - Rename files belonging to the tablespace 

        - Begin and end a backup

Table Space Availability

OBJECTIVE: 

            Change the status of tablespaces: You can control the availability of certain tablespaces by placing them offline or online. When you make a tablespace offline, the segments in that tablespace are not accessible. The data stored in other tablespaces is available for use. When making a tablespace unavailable, you can use the following four options:

NORMAL

        This option is the default. By normally we can change the state of the tablespace to offline or online. To offline the tablespace: 

         ➢ALTER TABLESPACE USER_DATA OFFLINE NORMAL

TEMPORARY

         Oracle performs a checkpoint on all online data files. It does not ensure that the data files are available. You might need to perform a media recovery on the offline data files when the tablespace is brought online. For example: 

        ➢ ALTER TABLESPACE USER_DATA OFFLINE TEMPORARY 

IMMEDIATE 

        Oracle does not perform a checkpoint and does not make sure that all data files are available. You must perform a media recovery when the tablespace is brought back online. For example: 

        ➢ ALTER TABLESPACE USER_DATA OFFLINE IMMEDIATE; 

FOR RECOVER 

        This option places the tablespace offline for point-in-time recovery. You can copy the data files belonging to the tablespace from a backup and apply the archive log files. For example:

         ➢ ALTER TABLESPACE USER_DATA OFFLINE FOR RECOVER; 

Read-Only Tablespace 

        If you do not want users to change any data in the tablespace, you can specify that it is read only. All objects in the tablespace are available for queries. INSERT, UPDATE, and DELETE operations on the data are not allowed. 

        ➢ ALTER TABLESPACE USERS READ ONLY; 

        To change a tablespace to read-write mode, use the following command: 

        ➢ ALTER TABLESPACE USERS READ WRITE; 

Adding Space to a Tablespace 

OBJECTIVE: 

            Change the size of the tablespace 

        ➢ ALTER TABLESPACE USERS ADD DATAFILE'/disk5/oradata/DB01/users02.dbf' SIZE 25M For temporary Tablespace 

        ➢ ALTER TABLESPACE USER_TEMP ADDTEMPFILE'/disk4/oradata/DB01/user_temp01.dbf' SIZE 100M;

 Dropping a Tablespace 

        You use the DROP TABLESPACE statement to drop a tablespace from the database. 

                ➢ DROP TABLESPACE USER_DATA;