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:
Post a Comment