Tablespace Management

By default each database will have five tablespaces in oracle 10g.

1. System : Tablespace-contains data dictionary of database
2. Sysaux : Contains database statistics
3. Undo : Contains Pre Image data
4. Temporary : Temporary operations are performed in Temporary Tablespace if PGA is not enough
5. Users : Default tablespace for all DB users / Application schemas

Creating Tablespace:-

Syntax:
SQL> create tablespace tablespace_name
datafile 'location of datafile/datafile_name.dbf' size 50m;

Example:
SQL> create tablespace test01
datafile '/oradata/oracle/db/test01.dbf' size 50M;

To check Tablespace information:

SQL> select tablespace_name from dba_tablespaces;

Adding the Space to Tablespace (Adding Datafile) :-

Syntax:
SQL> alter tablespace tablespace_name add datafile
'datafile location/datafile_name.dbf ' size 30m;
Example:
SQL> alter tablespace test add datafile
'/oradata/oracle/db/test/test02.dbf ' size 30M;

Deleting a datafile from Tablespace:

Syntax:
SQL> alter tablespace tablespace_name drop datafile
'datafile location/datafile_name.dbf'

Example:
SQL> alter tablespace test drop datafile
‘/oradata/oracle/db/test/test02.dbf’;

Droping a Tablespace:

Sql> drop tablespace test;       (It will drop tablespace and datafile at database level)

Sql> drop tablespace test including contents and datafiles;
(It will drop tablespace logically(database level) and physically(o/s level))

Reusing Orphan datafile:

By using "drop tablespace tablespace_name" command we can drop tablespace at
logical level but physically those datafiles are exist and we can reuse those datafile (Orphan) by
using following command.

Syntax:
SQL> create tablespace tablespace_name datafile
'datafile location/datafile_name.dbf' reuse;

Example:
SQL> create tablespace test datafile
'/oradata/oracle/db/test/test02.dbf' reuse;

Making a Tablespace as read only:

SQL> alter tablespace test read only;

SQL> select tablespace_name,status from dba_tablespaces;   To view the status of tablespace

SQL> alter tablespace test read write;

Making a Tablespace offline:

Sql> alter tablespace test offline;  Users can not access this tablespace in this state.

Sql> alter tablespace test online;

Renaming of Tablespace:

Syntax:
SQL> alter tablespace old_tablespace TO new_tablespace;

Example:
SQL> alter tablespace test rename to test2;

Renaming a Datafile in Tablespace:

Steps:-
1.make Tablespace offline
SQL> alter tablespace test offline;

2. at os level rename the datafile
$cd   /oradata/oracle/db/test/
$mv   test01.dbf    test005.dbf
3. Update the Controlfile for this Datafile.
SQL> alter database rename file ‘/oradata/oracle/db/test/test01.dbf’ to
‘/oradata/oracle/db/test/test005.dbf;

4. Online the Tablespace
Sql> alter tablespace test online;

Select tablespace_name, file_name from dba_data_files;

Relocating a Datafile in Tablespace:

Steps:-
1.make Tablespace offline
SQL> alter tablespace test offline;

2. at os level rename the datafile
$cd    /oradata/oracle/db/test/
$mv   test01.dbf    /u01/test/test01.dbf
3. update the Controlfile for this datafile.
SQL> alter database rename file ‘/oradata/oracle/db/test/test01.dbf’ to
‘/u01/test/test01.dbf’;

4. online the Tablespace
SQL> alter tablespace test online;

SQL>Select tablespace_name,file_name from dba_data_files;

To check Database size:

SQL> Select sum(bytes)/1024/1024 “size in MB” from dba_data_files;

To check free space in Database:

SQL> Select sum(bytes)/1024/1024 from dba_free_space;

Views:-
V$tablespace V$datafile
Dba_tablespaces
User_tablespaces
Dba_data_files Dba_segments            
Dba_extents sm$ts_free
Sm$ts_used sm$ts_avail

Resize Datafiles:-

Syntax:
SQL> alter database datafile 'Location of Datafile/datafile_name.dbf' 

           resize 20M;

Example:
SQL> alter database test02
datafile '/oradata/oracle/db/test/test02.dbf'  resize 20M;

Create Temporary Tablespace:-

Syntax:
SQL> create temporary tablespace tempTB_name
tempfile 'temp file location.tempfile_name.dbf'  size 20M;
Example:
SQL> create temporary tablespace temp_test
tempfile '/oradata/oracle/db/test/temp_test.dbf' size 20M;

(v$sort_segment, v$tempseg_usage for the space info of temp t.s)

Create undo Tablespace:-

Syntax:
SQL> create undo tablespace undoTB_name
datafile 'location of datafile/datafile_name.dbf' size 50m;
Example:
SQL> create undo tablespace undotbs02
datafile '/oradata/oracle/db/test/undotbs02.dbf'  size 50M;

Adding Datafile to Undo Tablespace:

SQL> alter tablespace undotbs02
add datafile '/oradata/oracle/db/test/undotbs03.dbf'  size 50m;

Switching Undo Tablespaces:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02 scope=both;

Views for undo Tablespace:

V$undostat
V$rollstat
V$transaction
dba_undo_extents

View for Sysaux tablespace:
V$sysaux_occupants

Managing the Online Redo Logs

                  The online redo logs are Oracle’s means of ensuring that all the changes made by the users are logged, in case there’s a failure before those changes can be written to permanent storage. Thus, redo logs are fundamental for the recovery process.
Oracle organizes its redo log files in redo log groups, and you need to have at least two different groups of redo logs with at least one member in each. You need to have at least two redo groups, because even when one redo log is being archived, the log writer should be able to write to an active redo log. Although your database will run just fine with only one member in each redo log group, Oracle strongly recommends that you multiplex the online redo logs. Multiplexing simply means that you maintain more than one member in each of your redo log groups. All members of a redo log group are identical—multiplexing is designed to protect against the loss of a single copy of a log file. When you multiplex the online redo log files, the log writer writes simultaneously to all the members of a group.

Creating Online Redo Log Groups:- 

Below command will add a new Redolog Group and its members.
Syntax:
 SQL> alter database add logfile group 4 ('logfile_location/log01.log','logfile_location/log01.log') size 10M;
Example: 
SQL> alter database add logfile group 4 
('/oradata/oracle/datafiles/catalog/redo04.log',
 '/oradata/oracle/datafiles/catalog/redo05.log') size 10M;

Adding Member to a existing Group:-

Syntax:-

SQL> alter database add logfile member 
'Log_file_location/name.log' to group 1;

Example:-

sql>alter database add logfile member
'/oradata/oracle/datafiles/catalog/redo01_02.log' to group 1;

Note: We don't have to specify the size for new redo log member being added to group 1 the new member will simply be sized the same as the existing members of the group.

Rename Redolog Files:-

1. Shut down the database and start it up in the mount mode:
SQL> shutdown immediate
SQL> startup mount 

2. Move the files to the new location with an operating system command:
$ mv /oradata/oracle/datafile/redo01.log /u01/datafiles/redo001.log

3. Use the ALTER DATABASE RENAME datafile command to rename the file within the control file:
SQL> alter database rename file '/oradata/oracle/datafile/redo01.log'
to '/u01/datafiles/redo001.log';

Dropping Online Redo Logs:-

You can drop an entire redo log group by using the following command:
SQL> alter database drop logfile group 4;

To drop a single member of an online redo log group, use this command:
SQL> alter database drop logfile member
 '/u01/datafiles/mul_redo01.log';

If the redo log file you want to drop is active, Oracle won’t let you drop it. You need to use the following command to switch the log file first, after which you can drop it:
SQL> alter system switch logfile;

Online Redo Log Corruption:-

You can set the DB_BLOCK_CHECKSUM initialization parameter to make sure Oracle checks for corruption in the redo logs before they’re archived. 

If the online redo logs are corrupted, the file can’t be archived, and one solution is to just drop and re-create them. But if there are only two log groups, you can’t do this, as Oracle insists on having a minimum of two online redo log groups at all times. However, you can create a new (3rd) redo log group, and then drop the corrupted redo log group. Also, you can’t drop an online redo log file if the log file is part of the current group. Your strategy then would be to reinitialize the log file by using the following statement:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
If the log group has not been archived yet, you can use the following statement:
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

Monitoring the Redo Logs:-

You can use two key dynamic views, V$LOG and V$LOGFILE, to monitor the online redo logs.
The V$LOGFILE view provides the full filename of the redo logs, their status, and type

SQL>select * from v$logfile;
 

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3  ONLINE /oradata/oracle/datafiles/catalog/redo03.log NO
2  ONLINE /oradata/oracle/datafiles/catalog/redo02.log NO
1  ONLINE /oradata/oracle/datafiles/catalog/redo01.log NO
The V$LOG view gives detailed information about the size and status of the redo logs, as well as showing whether the logs have been archived:
SQL> select group#, sequence#, bytes/1024/1024, archived, members from v$log;

GROUP# SEQUENCE# BYTES ARC MEMBERS
---------- ---------- --------------- --- ----------    
1             0             50       YES       1
2            55            50       NO        1
3           54             50       YES       1

Constraints

SQL constraints are used to specify rules for the data in a table. These are used to limit the type of data that can go into a table. We use constraints for

* Uniqueness of values in certain column.
* Matching of column values across columns (primary key and foreign key)
* Values in certain columns with specified range.
* This ensures the accuracy and reliability of the data .

Types of constraints:

Constraints could be column level or table level.
Column level:-
If the constraints are defined with the column definition, it is called as column level constraint. Column level constraint can be applied to any one column at a time.

Table level:-
A constraint given at the table level is called as Table Constraint. It may refer to more than one column of the table. A typical example is PRIMARY KEY constraint that is used to define composite primary key. A column level constraint can be given even at the table level, but a constraint that deals with more than one column must be given only at the table level.

Commonly used Constraints:
Following are commonly used constraints available in SQL.
NOT NULL Constraint: Ensures that a column cannot have NULL value.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures hat all values in a column satisfy certain conditions.

Not Null:

NOT NULL constraint restricts a column from having a NULL value.
Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column.
It enforces a column to contain a proper value.
One important point to note about NOT NULL constraint is that it cannot be defined at table level

Syntax:

SQL> CREATE TABLE CUSTOMERS(
Tname dataype     NOT NULL,
Tname dataype     NOT NULL,
Tname dataype    NOT NULL,
Tname dataype);

Example:

SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25));

To add not null constraint on existing tables column.
Syntax:

SQL> ALTER TABLE table_name MODIFY
column_name NOT NULL;

Example:

SQL> alter table test modify Age not null;
UNIQUE Constraint:

The UNIQUE Constraint prevents two records from having identical values in a particular
column. In the CUSTOMERS table, for example, you might want to prevent two or more
people from having identical age.

Syntax:
SQL> CREATE TABLE CUSTOMERS(
Tname datatype        NOT NULL,
Tname datatype       NOT NULL,
Tname datatype          NOT NULL UNIQUE,
Tname datatype);

Example:

SQL> CREATE TABLE CUSTOMERS(
ID INT                                  NOT NULL,
NAME VARCHAR (20)      NOT NULL,
AGE INT                          NOT NULL UNIQUE,
ADDRESS CHAR (25) );
If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following:

SQL> ALTER TABLE CUSTOMERS MODIFY
          AGE INT NOT NULL UNIQUE;You can also use following syntax, which supports naming the constraint in multiple columns as well:

SQL> ALTER TABLE CUSTOMERS ADD CONSTRAINT conname UNIQUE(AGE, SALARY);
           DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

Syntax:
SQL> alter table table_name drop constraint constraint_name;

Example:
SQL> ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;

Primary key Constraint:

A primary key is a field in a table which uniquely identifies each row/record in a database table.
Primary keys must contain unique values.
A primary key column cannot have NULL values.
A table can have only one primary key.
primary key When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Create Primary Key:
syntax :
SQL> CREATE TABLE table_name (
Column_name dataype,
Column_name dataype,
Column_name dataype,
PRIMARY KEY (ID) );
Example:
SQL> CREATE TABLE CUSTOMERS(
ID INT       NOT NULL,
NAME VARCHAR (20)           NOT NULL,
AGE INT           NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Constraint cust_const PRIMARY KEY (ID) );
To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL

Syntax:
SQL> ALTER TABLE table_name ADD CONSTRAINT PK_dept PRIMARY KEY (column_name);
Example:
SQL> alter table customers add constriant  Ps_dept primary key (ID);

Delete Primary Key:
You can clear the primary key constraints from the table.

Syntax:
SQL> ALTER TABLE table_name DROP PRIMARY KEY

Example:
SQL> alter table dept drop primary key;
                               or
SQL> alter table dept drop constraint PK_dept ;

foreign key:

A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

syntax :
SQL> CREATE TABLE table_name (
Column_name dataype,
Column_name dataype,
Column_name dataype,
PRIMARY KEY (column_name) );

SQL> create table table_name (
Column_name datatype,
Column_name datatype,
Constraint cons_name foreign_key prefremces
pri_table_name (column_name);
Example:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Constraint cust_const PRIMARY KEY (ID) );
SQL> create table order_det (
order_id number(20),
order_name varchar2(20),
constraints for_ord foreign key(order_id) references cust(id))
Add foreign key on existing table.

Syntax:
SQL> alter table table_name add contraint
Const_name foreign key (column_name)
References pri_table_name(p_columname);
Example:
SQL> alter table oreder_details add constraint
Ord_forg foreing_key(order_id)
References cust(id);
DROP a FOREIGN KEY Constraint:
To drop a FOREIGN KEY constraint, use the following SQL:

Syntax:
SQL> ALTER TABLE table_name DROP FOREIGN KEY;

Example:
SQL> alter table order_det drop foreign key;

CHECK Constraint:

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Syntax:
SQL> CREATE TABLE table_name (
Cname datatype    NOT NULL,
Cname datatype    NOT NULL,
Cname datatype    NOT NULL CHECK (AGE >= 18));
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:

SQL> CREATE TABLE CUSTOMERS(
ID INT    NOT NULL,
NAME VARCHAR(20)    NOT NULL,
AGE INT NOT    NULL CHECK (AGE >= 18));
If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column

SQL> ALTER TABLE table_name MODIFY column_name NOT NULL CHECK (column_name >= 18 );You can also use following syntax, which supports
naming the constraint in multiple columns as well:

SQL> ALTER TABLE table_name ADD CONSTRAINT constraintname CHECK(colname >= 18);
To drop a CHECK constraint, use the following SQL.

Syntax:
SQL> ALTER TABLE table_name DROP CONSTRAINT const_name;

Example:
SQL> alter table cust drop constraint check_my;