* Tablespace consists of one or more datafiles
* Data files belong to only one database.
Tablespace Types
The Primary types of tablespaces in an Oracle database are permanent, undo, and temporary.
Permanent:
The SYSTEM and SYSAUX tablespaces are two examples of permanent tablespaces.
Any segments that need to be retained by a user or an application beyond the boundaries of a session or transaction should be stored in a permanent tablespace.
SYSTEM:
The SYSTEM tablespace is used by the oracle database server to manage the database. It contains the administrative information about the database. These are all contained in the SYS schema, and can be accessed only by the user SYS, or other administrative users with the required privilege.
SYSAUX:
This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tables in prior releases of Oracle, now use the SYSAUX tablespace. Every Oracle Database 10g or higher-level database must have a SYSAUX tablespace.
TEMP:
This tablespace is used to store temporary tables and indexes when processing SQL statements. It would, for example, be used for sort work space.
Every database should have a temporary tablespace that is assigned to users as their temporary tablespace.
In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when the user account is created, then Oracle assigns this tablespace to the user.
UNDOTBS1:
This is the undo tablespace used by the database server to store undo information. Every database must have an undo tablespace that is created during database creation.
Multiple undo tablespaces can exist in a database, but only one undo tablespace can be active at any given time. Undo tablespaces are used for rolling back transactions etc.
USERS:
This tablespace is used to store permanent user objects and data.
In the preconfigured database, the USERS tablespace is the default tablespace for all objects created by nonsystem users.
For the SYS and SYSTEM users (the system users), the default permanent tablespace remains SYSTEM.
* Tablespace is a logical area where we create tables, indexes etc.
* Each tablespace is associated with one datafile (.dbf)
* When the entire datafile is occupied then we can either increase the existing file size (resize) or add one more datafile using ALTER tablespace command.
* In Oracle 10g we can rename a tablespace
* By default any tablespace datafile is getting updated because of users DML transactions
(Oracle will generate Redo log information which will be written to current redo log) LOGGING=YES (Brand new table space) when we created a table space.
In case if we decide that the transactions need not be LOGGED then we can choose LOGGING=NO at the tablespace level.
* When we create a tablespace then we see PLUGGEDIN=NO, where as when we transport a tablespace from one database to another then we can observe PLUGGEDIN=YES at the target database.
* From Oracle 8 onwards we can create a tablespace which will hold temporary objects (usually meant to support for sorting operations). Users are not allowed to create any Permanent objects like table or Index in the TEMP tablespace since type is TEMP.
Examples:
$sqlplus “/as sysdba”
SQL>startup<enter>
SQL>desc v$database;
Note: start with v$ are dynamic views.
Dynamic views can open at any stage i.e. mount, nomount or open.
How to change from nomount to mount?
SQL>alter database mount;
How to open a database?
SQL>alter database open;
SQL>select instance_name, status from v$instance;
How to shutdown a database?
SQL>alter database close; This will come to mount stage. This is similar to shutdown.
If you try to open
SQL>alter database open;
ERROR: database has been previously opened and closed
How to dismount the database?
SQL>alter database dismount;
That is nomount stage.
SQL>shut abort;
Oracle instance shut down.
SQL>startup;
Oracle database open.
SQL>select name, open_mode from v$database;
SQL> desc dba_tablespaces;
How to create a tablespace?
SQL>create tablespace exampletbs datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ size 1m;
How to add a datafile to an existing tablespace?
SQL>alter tablespace exampletbs add/rename/drop datafile
‘/u01/app/oracle/oradata/HRDEV/exapmletbs1.dbf’ size 1m;
SQL>desc dba_data_files;
SQL>select tablespace_name, file_name, file_id from dba_data_files;
How to drop a datafile?
SQL>alter tablespace exampletbs drop datafile 6;
Or
SQL>alter tablespace exampletbs drop datafile
‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’;
How to drop an entire tablespace including contents?
SQL>drop tablespace exampletbs including contents;
It will drop tablespace but not remove physical datafiles.
To drop tablespace as well as datafiles use the following SQL.
SQL>drop tablespace exampletbs including contents and datafiles;
If tablespace is empty (there is no datafile ) then
SQL>drop tablespace exampletbs;
How to reuse existing datafile.
Create one more tablespace.
SQL>create tablespace exampletbs datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ reuse;
How to reuse existing datafile.
How to alter datafile?
SQL>alter database datafile 5 resize 10m;
Or
SQL>alter database datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ resize 15m;
How to rename a datafile?
SQL>alter tablespace exampletbs offline;
SQL>exit
Go to datafile location.
HRDEV]$cp exampletbs1.dbf exampletbs2.dbf
OR
Go to Home directory.
$cd
$sqlplus ‘/as sysdba’
SQL>alter tablespace exampletbs rename datafile ‘/u01/app/oracle/oradata/HRDEV/exampletbs1.dbf’ to ‘/u01/app/oracle/oradata/HRDEV/exampletbs3.dbf’;
How to make tablespace online?
SQL>alter tablespace exampletbs online;
How to rename a tablespace? This feature is only from oracle 10g.
SQL>alter tablespace exampletbs rename to exampletbsdba;
No comments:
Post a Comment