Monday, September 28, 2020

Managing Tablespaces And Datafiles

 

Managing Tablespaces And Datafiles

You can create Locally Managed or Dictionary Managed Tablespaces. In prior versions of Oracle only Dictionary managed Tablespaces were available but from Oracle ver. 8i you can also create Locally Managed tablespaces.

To create a locally managed tablespace

SQL> create tablespace  Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' size 50m extent management dictionary local autoallocate;

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K, You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M

To Create Dictionary Managed Tablespace

SQL> create tablespace  Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' size 50m extent management dictionary;

Bigfile Tablespaces(10g)

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile

To create a bigfile tablespace

SQL> create bigfile tablespace  Ranesh3 datafile '/u01/app/oracle/oradata/stand/ranesh3.dbf' size 50g:

 

To Extend the Size of a tablespace

Option 1

You can extend the size of a tablespace by increasing the size of an existing datafile

SQL> alter  database Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' resize 100m;

Option 2

You can also extend the size of a tablespace by adding a new datafile to a tablespace

SQL> alter tablespace add datafile '/u01/app/oracle/oradata/stand/ranesh3.dbf' size 50m;

 

Option 3

SQL> alter database datafile '/u01/app/oracle/oradata/stand/ranesh3.dbf' auto extend ON next 5M maxsize 500M;

To decrease the size of a tablespace

SQL> alter  database Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' resize 30m;

 

Taking tablespaces Offline or Online

To Take a Tablespace Offline

SQL>alter tablespace ranesh offline;

To again bring it back online .

SQL>alter tablespace ranesh online;

To take individual datafile offline type the following command

SQL>alter database datafile ‘/u01/app/oracle/oradata/stand/ranesh2.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/app/oracle/oradata/stand/ranesh2.dbf’ online;

Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode.  If the datafile has become corrupt or missing when the database is running in NOARCHIVELOG mode then you can only drop it by giving the following command

SQL>alter database datafile ‘‘/u01/app/oracle/oradata/stand/ranesh2.dbf’ offline for drop;

Making a tablespace read-only

Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.

To make a tablespace read only

SQL>alter tablespace ranesh read only;

Again to make it read write

sql> alter tablespace ranesh read write;

 

Renaming Tablespaces

alter tablespace users ranesh TO ranesh3;

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.

To drop a tablespace give

SQL> drop tablespace ica;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it

SQL>drop tablespace ica including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.

But If  you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace ica including contents and datafiles;

 Viewing Information about Tablespaces and Datafiles

Oracle has provided many Data dictionaries to view information about tablespaces and datafiles.  Some of them are:

To view information about Tablespaces in a database give the following query

SQL> select * from dba_tablespaces
SQL> select * from v$tablespace;

To view information about Datafiles

SQL> select * from dba_data_files;
SQL> select * from v$datafile;

To view information about Tempfiles

SQL> select * from dba_temp_files;
SQL> select * from v$tempfile;

To view information about free space in datafiles

SQL> select * from dba_free_space;

To view information about free space in tempfiles

SQL> select * from V$TEMP_SPACE_HEADER;

 

Renaming or Relocating Datafiles belonging to a Single Tablespace

1.       Take the tablespace offline

2.       Rename or Relocate the datafiles using operating system command

3.       Give the ALTER TABLESPACE with RENAME DATAFILE option to change the filenames within the Database.

4.       Bring the tablespace Online

 

 

 

No comments:

Table import and export.

Create Data Pump Directory. The first step in Oracle Data Pump is to create an OS level directory which will be used by Oracle for perform...