1. What Is a Database Table?
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
2. How Many Types of Tables Supported by Oracle?
Oracle supports 4 types of tables based on how data is organized in storage:
◈ Ordinary (heap-organized) table - This is the basic, general purpose type of table. Its data is stored as an unordered collection (heap)
◈ Clustered table - A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
◈ Index-organized table - Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
◈ Partitioned table - Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
3. How To Create a New Table in Your Schema?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));
Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".
4. How To Create a New Table by Selecting Rows from Another Table?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary FROM emp_dept_10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.
This is really a quick and easy way to create a table.
5. How To Rename an Existing Table?
If you don't like the name of an existing table, you change it by using the CREATE TABLE ... RENAME TO statement. Here is a sample script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> ALTER TABLE emp_dept_10 RENAME TO emp_dept_dba;
Table altered.
SQL> SELECT first_name, last_name, salary FROM emp_dept_dba;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
6. How To Drop an Existing Table?
If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
Be careful, when you use the DROP TABLE statement. All data rows are gone too.
7. How To Add a New Column to an Existing Table?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_110
2 AS SELECT * FROM employees WHERE department_id=110;
Table created.
SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.
SQL> SELECT first_name, last_name, vacation
2 FROM emp_dept_110;
FIRST_NAME LAST_NAME VACATION
-------------------- ------------------------- ----------
Shelley Higgins
William Gietz
This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.
8. How To Add a New Column to an Existing Table with a Default Value?
If you want to add a new column to an existing table, and insert a default value in this column on all existing data rows, you can use the ALTER TABLE ... ADD statement with the DEFAULT clause. Here is an example script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> ALTER TABLE emp_dept_90
2 ADD (vacation NUMBER DEFAULT 10);
Table altered.
SQL> SELECT first_name, last_name, vacation
2 FROM emp_dept_90;
FIRST_NAME LAST_NAME VACATION
-------------------- ------------------------- ----------
Steven King 10
Neena Kochhar 10
Lex De Haan 10
As you can see, the "DEFAULT 10" clause did inserted 10 to all existing data rows.
9. How To Rename a Column in an Existing Table?
Let's say you have an existing with an existing column, but you don't like the name of that column, can you rename that column name? The answer is yes. You can use the ALTER TABLE ... RENAME COLUMN statement to do this. See the following SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT first_name, last_name FROM emp_dept_90;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
SQL> ALTER TABLE emp_dept_90 RENAME COLUMN first_name
2 TO fname;
Table altered.
SQL> SELECT fname, last_name FROM emp_dept_90;
FNAME LAST_NAME
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
As you can see the column "first_name" is nicely changed to "fname".
10. How To Delete a Column in an Existing Table?
If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT last_name FROM emp_dept_90;
LAST_NAME
-------------------------
King
Kochhar
De Haan
SQL> ALTER TABLE emp_dept_90 DROP COLUMN last_name;
Table altered.
SQL> SELECT last_name FROM emp_dept_90;
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier
As you can see the column "last_name" is gone.
11. How To View All Columns in an Existing Table?
If you have an existing table and want to know how many columns are in the table and how they are defined, you can use the system view USER_TAB_COLUMNS as shown in the following tutorial exercise:
SQL> COL data_type FORMAT A12;
SQL> SELECT column_name, data_type, data_length
FROM user_tab_columns WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_TYPE DATA_LENGTH
------------------------------ ------------ -----------
EMPLOYEE_ID NUMBER 22
FIRST_NAME VARCHAR2 20
LAST_NAME VARCHAR2 25
EMAIL VARCHAR2 25
PHONE_NUMBER VARCHAR2 20
HIRE_DATE DATE 7
JOB_ID VARCHAR2 10
SALARY NUMBER 22
COMMISSION_PCT NUMBER 22
MANAGER_ID NUMBER 22
DEPARTMENT_ID NUMBER 22
12. How To Recover a Dropped Table?
If you accidentally dropped a table, can you recover it back? The answer is yes, if you have the recycle bin feature turned on. You can use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover a dropped table from the recycle bin as shown in the following SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> FLASHBACK TABLE emp_dept_90 TO BEFORE DROP
2 RENAME TO emp_dept_bck;
Flashback complete.
SQL> SELECT COUNT(*) FROM emp_dept_bck;
COUNT(*)
----------
3
The FLASHBASK statement in this script recovered the dropped table "emp_dept_90" to new name "emp_dept_bck". All the data rows are recovered nicely.
13. What Is Recycle Bin?
Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parameter file.
14. How To Turn On or Off Recycle Bin for the Instance?
You can turn on or off the recycle bin feature for an instance in the instance parameter file with "recyclebin=on/off". You can also turn on or off the recycle bin feature on the running instance with a SQL*Plus command, if you log in as SYSTEM. See the following example:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------ ----------- -------
recyclebin string on
SQL> ALTER SYSTEM SET RECYCLEBIN = OFF;
System altered.
SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------- ----------- -------
recyclebin string OFF
Warning: Turning off the recycle bin feature will give your users hard times on recovering dropped tables.
15. How To View the Dropped Tables in Your Recycle Bin?
You can look what's in your recycle bin through the predefined view called RECYCLEBIN. You can use the SELECT statement to list the dropped tables as shown in the following script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime
2 FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ ------------- ---------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10 06-04-01:18:57:
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10 06-04-01:19:59:
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90 06-04-01:20:47:
As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple times, you need to restore by using the object name in the recycle bin with FLASHBACK statement.
Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.
16. How To Empty Your Recycle Bin?
If your recycle bin is full, or you just want to clean your recycle bin to get rid of all the dropped tables, you can empty it by using the PURGE statement in two formats:
◉ PURGE RECYCLEBIN - Removes all dropped tables from your recycle bin.
◉ PURGE TABLE table_name - Removes the specified table from your recycle bin.
Here is an example script on how to use the PURGE statement:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
5
SQL> PURGE TABLE emp_dept_90;
Table purged.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
4
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
17. How To Turn On or Off Recycle Bin for the Session?
If you want to control the recycle bin feature in your own session, you can use the ALTER SESSION statement to turn on or off. Here is an example SQL script:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
SQL> ALTER SESSION SET recyclebin = off;
Session altered.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
Warning: Turning off the recycle bin feature in your session will give yourself hard times on recovering dropped tables.
18. How To List All Tables in Your Schema?
If you log in with your Oracle account, and you want to get a list of all tables in your schema, you can get it through the USER_TABLES view with a SELECT statement, as shown in the following SQL script:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT table_name, status, num_rows FROM USER_TABLES;
TABLE_NAME STATUS NUM_ROWS
-------------------------- -------- ----------
REGIONS VALID 4
LOCATIONS VALID 23
DEPARTMENTS VALID 27
JOBS VALID 19
EMPLOYEES VALID 107
JOB_HISTORY VALID 10
COUNTRIES VALID 25
7 rows selected.
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
2. How Many Types of Tables Supported by Oracle?
Oracle supports 4 types of tables based on how data is organized in storage:
◈ Ordinary (heap-organized) table - This is the basic, general purpose type of table. Its data is stored as an unordered collection (heap)
◈ Clustered table - A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
◈ Index-organized table - Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
◈ Partitioned table - Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
3. How To Create a New Table in Your Schema?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));
Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".
4. How To Create a New Table by Selecting Rows from Another Table?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary FROM emp_dept_10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.
This is really a quick and easy way to create a table.
5. How To Rename an Existing Table?
If you don't like the name of an existing table, you change it by using the CREATE TABLE ... RENAME TO statement. Here is a sample script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> ALTER TABLE emp_dept_10 RENAME TO emp_dept_dba;
Table altered.
SQL> SELECT first_name, last_name, salary FROM emp_dept_dba;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
6. How To Drop an Existing Table?
If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
Be careful, when you use the DROP TABLE statement. All data rows are gone too.
7. How To Add a New Column to an Existing Table?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_110
2 AS SELECT * FROM employees WHERE department_id=110;
Table created.
SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.
SQL> SELECT first_name, last_name, vacation
2 FROM emp_dept_110;
FIRST_NAME LAST_NAME VACATION
-------------------- ------------------------- ----------
Shelley Higgins
William Gietz
This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.
8. How To Add a New Column to an Existing Table with a Default Value?
If you want to add a new column to an existing table, and insert a default value in this column on all existing data rows, you can use the ALTER TABLE ... ADD statement with the DEFAULT clause. Here is an example script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> ALTER TABLE emp_dept_90
2 ADD (vacation NUMBER DEFAULT 10);
Table altered.
SQL> SELECT first_name, last_name, vacation
2 FROM emp_dept_90;
FIRST_NAME LAST_NAME VACATION
-------------------- ------------------------- ----------
Steven King 10
Neena Kochhar 10
Lex De Haan 10
As you can see, the "DEFAULT 10" clause did inserted 10 to all existing data rows.
9. How To Rename a Column in an Existing Table?
Let's say you have an existing with an existing column, but you don't like the name of that column, can you rename that column name? The answer is yes. You can use the ALTER TABLE ... RENAME COLUMN statement to do this. See the following SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT first_name, last_name FROM emp_dept_90;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
SQL> ALTER TABLE emp_dept_90 RENAME COLUMN first_name
2 TO fname;
Table altered.
SQL> SELECT fname, last_name FROM emp_dept_90;
FNAME LAST_NAME
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
As you can see the column "first_name" is nicely changed to "fname".
10. How To Delete a Column in an Existing Table?
If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT last_name FROM emp_dept_90;
LAST_NAME
-------------------------
King
Kochhar
De Haan
SQL> ALTER TABLE emp_dept_90 DROP COLUMN last_name;
Table altered.
SQL> SELECT last_name FROM emp_dept_90;
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier
As you can see the column "last_name" is gone.
11. How To View All Columns in an Existing Table?
If you have an existing table and want to know how many columns are in the table and how they are defined, you can use the system view USER_TAB_COLUMNS as shown in the following tutorial exercise:
SQL> COL data_type FORMAT A12;
SQL> SELECT column_name, data_type, data_length
FROM user_tab_columns WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_TYPE DATA_LENGTH
------------------------------ ------------ -----------
EMPLOYEE_ID NUMBER 22
FIRST_NAME VARCHAR2 20
LAST_NAME VARCHAR2 25
EMAIL VARCHAR2 25
PHONE_NUMBER VARCHAR2 20
HIRE_DATE DATE 7
JOB_ID VARCHAR2 10
SALARY NUMBER 22
COMMISSION_PCT NUMBER 22
MANAGER_ID NUMBER 22
DEPARTMENT_ID NUMBER 22
12. How To Recover a Dropped Table?
If you accidentally dropped a table, can you recover it back? The answer is yes, if you have the recycle bin feature turned on. You can use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover a dropped table from the recycle bin as shown in the following SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> FLASHBACK TABLE emp_dept_90 TO BEFORE DROP
2 RENAME TO emp_dept_bck;
Flashback complete.
SQL> SELECT COUNT(*) FROM emp_dept_bck;
COUNT(*)
----------
3
The FLASHBASK statement in this script recovered the dropped table "emp_dept_90" to new name "emp_dept_bck". All the data rows are recovered nicely.
13. What Is Recycle Bin?
Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parameter file.
14. How To Turn On or Off Recycle Bin for the Instance?
You can turn on or off the recycle bin feature for an instance in the instance parameter file with "recyclebin=on/off". You can also turn on or off the recycle bin feature on the running instance with a SQL*Plus command, if you log in as SYSTEM. See the following example:
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------ ----------- -------
recyclebin string on
SQL> ALTER SYSTEM SET RECYCLEBIN = OFF;
System altered.
SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------- ----------- -------
recyclebin string OFF
Warning: Turning off the recycle bin feature will give your users hard times on recovering dropped tables.
15. How To View the Dropped Tables in Your Recycle Bin?
You can look what's in your recycle bin through the predefined view called RECYCLEBIN. You can use the SELECT statement to list the dropped tables as shown in the following script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime
2 FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ ------------- ---------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10 06-04-01:18:57:
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10 06-04-01:19:59:
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90 06-04-01:20:47:
As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple times, you need to restore by using the object name in the recycle bin with FLASHBACK statement.
Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.
16. How To Empty Your Recycle Bin?
If your recycle bin is full, or you just want to clean your recycle bin to get rid of all the dropped tables, you can empty it by using the PURGE statement in two formats:
◉ PURGE RECYCLEBIN - Removes all dropped tables from your recycle bin.
◉ PURGE TABLE table_name - Removes the specified table from your recycle bin.
Here is an example script on how to use the PURGE statement:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
5
SQL> PURGE TABLE emp_dept_90;
Table purged.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
4
SQL> PURGE RECYCLEBIN;
Recyclebin purged.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
17. How To Turn On or Off Recycle Bin for the Session?
If you want to control the recycle bin feature in your own session, you can use the ALTER SESSION statement to turn on or off. Here is an example SQL script:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
SQL> ALTER SESSION SET recyclebin = off;
Session altered.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
Warning: Turning off the recycle bin feature in your session will give yourself hard times on recovering dropped tables.
18. How To List All Tables in Your Schema?
If you log in with your Oracle account, and you want to get a list of all tables in your schema, you can get it through the USER_TABLES view with a SELECT statement, as shown in the following SQL script:
SQL> connect HR/fyicenter
Connected.
SQL> SELECT table_name, status, num_rows FROM USER_TABLES;
TABLE_NAME STATUS NUM_ROWS
-------------------------- -------- ----------
REGIONS VALID 4
LOCATIONS VALID 23
DEPARTMENTS VALID 27
JOBS VALID 19
EMPLOYEES VALID 107
JOB_HISTORY VALID 10
COUNTRIES VALID 25
7 rows selected.
Posts
0 comments:
Post a Comment