Create Tablespace :
===================
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
UNIFORM;
/* Default Uniform size will take 1Mb and Alternative to uniqform is AUTOALLOCATE */
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
UNIFORM SIZE 5M;
CREATE TABLESPACE test01
DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 100M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
CREATE TABLESPACE test01 DATAFILE ‘/u09/oradata/test/test01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* Alternative to EXTENT is LOCAL */
Drop Tablespace :
=================
DROP TABLESPACE test01; /* This will drop the tablespace only if it is empty */
DROP TABLESPACE test01 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. */
DROP TABLESPACE test01 INCLUDING CONTENTS AND DATAFILES;
/* For reverential integrity constraints to drop */
===============================================
DROP TABLESPACE test01 CASCADE CONSTRAINTS;
Add datafile :
=============
ALTER TABLESPACE test01 ADD DATAFILE ‘/finance10/app/oracle/finance/test01.dbf’ SIZE 1000M;
Resize datafile:
==============
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ RESIZE 500m;
Take Offline/Online Datafile :
==============================
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ OFFLINE;
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.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:
ALTER DATABASE DATAFILE ‘/finance10/oradata/data_09.dbf’ OFFLINE DROP;
*/
Autoextent :
==========
ALTER TABLESPACE data01
ADD DATAFILE ‘/finance10/oradata/data01.dbf’ SIZE 200M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 1000M;
Rename Tablespace :
====================
SQL> ALTER TABLESPACE test01 RENAME TO test02;
Tablespace altered.
SQL>
You can rename both permanent and temporary tablespaces, but there are a few restrictions:
• You can’t rename the System and Sysaux tablespaces.
• The tablespace being renamed must have all its data files online.
• If the tablespace is read-only, renaming it doesn’t update the file headers of its data files.
Rename Datafile :
================
Sometimes, you may need to rename a data file. The process for this is straightforward:
1. Take the data file offline by taking its tablespace offline. Use the following command:
SQL> ALTER TABLESPACE test01 OFFLINE NORMAL;
Tablespace altered.
SQL>
2. Rename the file using an operating system utility such as cp or mv in UNIX, or copy in
Windows.
$ cp /u01/app/oracle/test01.dbf /u02/app/oracle/test01.dbf
3. Rename the data file in the database by using the following command:
SQL> ALTER TABLESPACE test01
2 RENAME DATAFILE
3 ‘/u01/app/oracle/test01.dbf’
4 TO
5* ‘/u02/app/oracle/test01.dbf’;
Tablespace altered.
SQL>
Read or Read write mode:
===================
SQL> ALTER TABLESPACE test01 READ ONLY;
If you want to make this read-only tablespace writable again, you can use the following
command:
SQL> ALTER TABLESPACE test01 READ WRITE;
Oflline /Online : Except System, all can be turn to offline.
=========================================
ALTER TABLESPACE index_01 OFFLINE NORMAL;
ALTER TABLESPACE index_01 ONLINE;
Temporary tabelspace :
=================
CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE ‘temp01.dbf’ SIZE 500M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE temp_demo TEMPFILE ‘temp01.dbf’ SIZE 500M extent management local uniform size 5M;
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The AUTOALLOCATE clause is not allowed for temporary tablespaces like as for user tablespaces.
Resize Temporaray tablepace:
=====================
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/prod1/temp01.dbf’ RESIZE 50M;
Drop Temp Tablespace:
==================
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/prod1/temp01.dbf’ DROP INCLUDING DATAFILES;
Shrink Temporary Tablespace :
=============================
SQL> alter tablespace temp shrink space;
SQL> alter tablespace temp shrink space keep 100m;
SQL> alter tablespace temp shrink tempfile ‘/u01/app/oracle/oradata/or11/temp01.dbf’;
SQL> alter tablespace temp shrink space tempfile ‘/u01/app/oracle/oradata/prod1/temp02.dbf’ keep 100m;
Imp views:
==========
V$SORT_SEGMENT and V$TEMPSEG_USAGE
Temporary Tablespace Groups:
=======================
When you assign the first temporary tablespace to a tablespace group, you automatically create the temporary tablespace group.
desc dba_tablespace_groups
You can also use the DBA_USERS view to find out which temporary tablespaces or temporary
tablespace groups are assigned to each user….
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE ‘/u01/oracle/oradata/temp01_01.dbf’ SIZE 500M TABLESPACE GROUP tmpgrp1;
ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1
The preceding statement will cause Oracle to create a new group named tmpgrp1,
No group:
========
SQL> CREATE TEMPORARY TABLESPACE temp02
TEMPFILE ‘/u01/oracle/oradata/temp02_01.dbf’ SIZE 500M
TABLESPACE GROUP ”;
SQL> CREATE TEMPORARY TABLESPACE temp03
TEMPFILE ‘/u01/oracle/oradata/temp03_01.dbf’ SIZE 500M;
Tablespace created.
Setting a Group as the Default Temporary Tablespace for the Database:
===================================================
SQL> ALTER TABLESPACE DEFAULT TEMPORARY TABLESPACE tmpgrp1;
Using Temporary Tablespace Groups When You Create and Alter Users :
=================================================================
When you create new users, you can assign them to a temporary tablespace group instead of to the usual single temporary tablespace. Here’s an example:
SQL> CREATE USER salapati IDENTIFIED BY sammyy1
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE tmpgrp1;
User created.
SQL>
Bigfile Tablespaces:
==============
Creating Bigfile Tablespaces:
CREATE BIGFILE TABLESPACE bigtbs_01 DATAFILE ‘/u01/oracle/data/bigtbs_01.dbf’ SIZE 100GB;
Changing the Default Tablespace Type:
============================
SQL> ALTER TABLESPACE SET DEFAULT BIGFILE TABLESPACE;
Altering a Bigfile Tablespace:
=====================
SQL> ALTER TABLESPACE bigtbs RESIZE 120G;
SQL> ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
deallocate unused space from table:
==================================
SQL> ALTER TABLE persons DEALLOCATE UNUSED;
To encrypt any colum of table : just use keyword as “ENCRYPT”.
=============================================
ssn NUMBER(9) ENCRYPT,where ssn is the column name.
Adding a Column to a Table:
====================
ALTER TABLE emp ADD (retired char(1));
Dropping a Column from a Table:
========================
SQL> ALTER TABLE emp DROP (retired);
Table altered.
For lagrge tables, can set the column as unused that will not be visible to table,views or constraints:
==============================================================
SQL> ALTER TABLE emp SET UNUSED (hiredate, mgr);
so during maintainence window we can drop all unused coloumns :
SQL> ALTER TABLE emp DROP UNUSED COLUMNS;
Renaming a Table Column:
========================
SQL> ALTER TABLE emp RENAME COLUMN retired TO non_active;
Renaming a Table:
=============
SQL> ALTER TABLE emp RENAME TO emp;
Removing All the Data from a Table:
==========================
TRUNCATE TABLE test; : DDL Comand => No Rollback
DELETE * FROM TABLE; : DML Command => Can Rollback
Creating a New Table with the CTAS Option:
=================================
SQL> CREATE TABLE emp_new AS SELECT * FROM emp;
Table created.
PARALLEL and NOLOGGING to speed up data loading into table:
=================================================
SQL> CREATE TABLE employee_new AS SELECT * FROM employees PARALLEL DEGREE 4,NOLOGGING;
Table created.
For more speed move table from one tablespace to other tablespace:
================================================
SQL> ALTER TABLE employee MOVE new_tablespace;
/* When you move a table, the ROWIDs of the rows change, thus making the indexes on the table unusable. You must either re-create the indexes or rebuild them after you move the table. */
Dropping Tables:
===============
SQL> drop table TEST1.EMP_BONUS_BKP1;
Table dropped.
Elapsed: 00:00:00.02
SQL> select count(*) from TEST1.EMP_BONUS_BKP1;
select count(*) from TEST1.EMP_BONUS_BKP1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
SQL> flashback table TEST1.EMP_BONUS_BKP1 to before drop;
Flashback complete.
Elapsed: 00:00:00.01
SQL> select count(*) from TEST1.EMP_BONUS_BKP1;
COUNT(*)
———-
2
Elapsed: 00:00:00.02
SQL> drop table TEST1.EMP_BONUS_BKP1 purge;
Table dropped.
Elapsed: 00:00:00.87
SQL> flashback table TEST1.EMP_BONUS_BKP1 to before drop;
flashback table TEST1.EMP_BONUS_BKP1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
Elapsed: 00:00:00.01
SQL>
Read-Only Tables :
==================
In earlier releases of the Oracle database, you could have read-only tablespaces but not readonly
tables. One of the more useful but simple to implement new features in Oracle Database
11g is the read-only table feature. Simply specify the clause read only in the alter table statement
as shown in the following example, where we first create a normal read-write table and
then convert that into a read-only table:
SQL> create table test1 (name varchar2(30));
Table created.
SQL> alter table test1 read only;
Table altered.
SQL>
The new column READ_ONLY in the DBA_TABLES view shows whether a column has readonly
status. You can return a read-only table to a read-write status by using the read write
clause in the alter table statement, as shown in the following example:
SQL> alter table test1 read write;
Table altered.
SQL>
Oracle Database with Vishal 