Oracle Database

If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

EMP_EMAIL_UK  EMPLOYEES   UNIQUE

EMP_EMP_ID_PK   EMPLOYEES   UNIQUE

EMP_DEPARTMENT_IX  EMPLOYEES   NONUNIQUE

EMP_JOB_IX   EMPLOYEES   NONUNIQUE

EMP_MANAGER_IX  EMPLOYEES   NONUNIQUE

EMP_NAME_IX   EMPLOYEES   NONUNIQUE

As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.

An index associated with a constraint because this constraint is required to have an index. There are two types of constraints are required to have indexes: UNIQUE and PRIMARY KEY. When you defines a UNIQUE or PRIMARY KEY constraint in a table, Oracle will automatically create an index for that constraint. The following script shows you an example:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004123   STUDENT   UNIQUE

SYS_C004124   STUDENT   UNIQUE

The result confirms that Oracle automatically created two indexes for you.

Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004153   STUDENT   UNIQUE

SYS_C004154   STUDENT   UNIQUE

ALTER INDEX SYS_C004153 RENAME TO student_pk;

Statement processed.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

STUDENT_PK   STUDENT   UNIQUE

SYS_C004154   STUDENT   UNIQUE

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004129   STUDENT   UNIQUE

SYS_C004130   STUDENT   UNIQUE

STUDENT_BIRTH_DATE STUDENT   NONUNIQUE

DROP INDEX STUDENT_BIRTH_DATE;

Index dropped.

If you drop a table, what happens to its indexes? The answer is that if a table is dropped, all its indexes will be dropped too. Try the following script to see yourself:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004141   STUDENT   UNIQUE

SYS_C004142   STUDENT   UNIQUE

STUDENT_BIRTH_DATE STUDENT   NONUNIQUE

DROP TABLE STUDENT;

Table dropped.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

no data found

No records in USER_INDEXES view found for your name STUDENT, after you dropped STUDENT table.

If you have the recycle bin feature turned on, dropped indexes are stored in the recycle bin. But it seems to be command to restore a dropped index out of the recycle bin. FLASHBACK INDEX is not a valid statement. See the following script:

ALTER SESSION SET recyclebin = on;

Statement processed.

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

DROP INDEX STUDENT_BIRTH_DATE;

Index dropped.

SELECT object_name, original_name, type, droptime

FROM recyclebin;

OBJECT_NAME   ORIGINAL_NAME TYPE DROPTIME

----------------------- ------------------ ----- ----------

BIN$1LlsjTxERKq+C7A==$0 STUDENT_BIRTH_DATE INDEX 2006-04-01

FLASHBACK INDEX student_birth_date TO BEFORE DROP;

ORA-00905: missing keyword

If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recycle bin. However, the indexes' names will not be the original names. Indexes will be recovered with the system assigned names when they were dropped into the cycle bin. The following SQL script shows you this behavior:

ALTER SESSION SET recyclebin = on;

Statement processed.

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_birth_date ON student(birth_date);

Index created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004141   STUDENT   UNIQUE

SYS_C004142   STUDENT   UNIQUE

STUDENT_BIRTH_DATE STUDENT   NONUNIQUE

DROP TABLE student;

Table dropped.

You can a list of indexes in your schema from the USER_INDEXES view, but it will not give you the columns used in each index in the USER_INDEXES view. If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you:

SELECT index_name, table_name, column_name

FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';

INDEX_NAME   TABLE_NAME  COLUMN_NAME

-------------------- ---------------- ----------------

EMP_EMAIL_UK  EMPLOYEES  EMAIL

EMP_EMP_ID_PK  EMPLOYEES  EMPLOYEE_ID

EMP_DEPARTMENT_IX EMPLOYEES  DEPARTMENT_ID

EMP_JOB_IX   EMPLOYEES  JOB_ID

EMP_MANAGER_IX  EMPLOYEES  MANAGER_ID

EMP_NAME_IX  EMPLOYEES  LAST_NAME

EMP_NAME_IX  EMPLOYEES  FIRST_NAME

If you know a group of multiple columns will be always used together as search criteria, you should create a single index for that group of columns with the "ON table_name(col1, col2, ...)" clause. Here is an example of one index for two columns:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL,

birth_date DATE NOT NULL,

social_number VARCHAR(80) UNIQUE NOT NULL);

Table created.

CREATE INDEX student_names ON student(first_name,last_name);

Index created.

SELECT index_name, table_name, uniqueness

FROM USER_INDEXES WHERE table_name = 'STUDENT';

INDEX_NAME   TABLE_NAME  UNIQUENES

----------------------- --------------------- ---------

SYS_C004123   STUDENT   UNIQUE

SYS_C004124   STUDENT   UNIQUE

STUDENT_NAMES   STUDENT   NONUNIQUE

When you create a new database, Oracle server will create 4 required tablespaces for the new database:

► SYSTEM Tablespace - Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

► SYSAUX Tablespace - The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

► UNDO Tablespace - UNDO tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.

► TEMP Tablespace - When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/globalguideline

Connected.

SQL> col tablespace_name format a16;

SQL> col file_name format a36;

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES

2 FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME     BYTES

--------------- ------------------------------- ---------

USERS   ORACLEXEORADATAXEUSERS.DBF 104857600

SYSAUX  ORACLEXEORADATAXESYSAUX.DBF 461373440

UNDO  ORACLEXEORADATAXEUNDO.DBF 94371840

SYSTEM  ORACLEXEORADATAXESYSTEM.DBF 356515840

If you want a new dataspace, you can use the CREATE TABLESPACE ... DATAFILE statement as shown in the following script:

SQL> CREATE TABLESPACE my_space

2 DATAFILE '/temp/my_space.dbf' SIZE 10M;

Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS

2 FROM USER_TABLESPACES;

TABLESPACE_NAME STATUS  CONTENTS

---------------- --------------- ---------

SYSTEM   ONLINE  PERMANENT

UNDO   ONLINE  UNDO

SYSAUX   ONLINE  PERMANENT

TEMP   ONLINE  TEMPORARY

USERS  ONLINE  PERMANENT

MY_SPACE  ONLINE  PERMANENT

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES

2 FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME     BYTES

--------------- -------------------------------- ---------

USERS   ORACLEXEORADATAXEUSERS.DBF 104857600

SYSAUX  ORACLEXEORADATAXESYSAUX.DBF 461373440

UNDO  ORACLEXEORADATAXEUNDO.DBF  94371840

SYSTEM  ORACLEXEORADATAXESYSTEM.DBF 356515840

MY_SPACE  TEMPMY_SPACE.DBF   10485760

So one statement created two structures: a tablespace and a data file. If you check your file system with Windows file explorer, you will see the data file is located

You can easily rename a tablespace by using the ALTER TABLESPACE ... RENAME TO statement as shown in the example below:

SQL> CREATE TABLESPACE my_space

2 DATAFILE '/temp/my_space.dbf' SIZE 10M;

Tablespace created.

SQL> ALTER TABLESPACE my_space RENAME TO your_space;

Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS

2 FROM USER_TABLESPACES;

NAME STATUS  CONTENTS

---------------- --------------- ---------

SYSTEM   ONLINE  PERMANENT

UNDO   ONLINE  UNDO

SYSAUX   ONLINE  PERMANENT

TEMP   ONLINE  TEMPORARY

USERS  ONLINE  PERMANENT

YOUR_SPACE  ONLINE  PERMANENT