What is a Database Table?
A database table is an entity that has rows and columns. It is used to store the data of a database. Each column in a table has a data type that defines the type of data to be stored.
Example:
CREATE TABLE EMPLOYEE ( Id INTEGER, NAME VARCHAR(20));
How many types of tables supported by Oracle? Explain them.
-
Ordinary (heap-organized) table: This is the most basic type of table that stores data as heaps.
-
Clustered table: A cluster is a collection of tables sharing the same data block in which the columns are shared as well.
-
Index-organized table: Here, the data is stored in the form of a Btree index structure in which each index entry is storing the non key column value.
-
Partitioned table: Here, the data is broken into partitions that can be managed and operated individually.
Can you explain how to create a new table in your schema?
We can create a new table using the CREATE TABLE command.
Example:
CREATE TABLE EMPLOYEE ( Id INT, Name VARCHAR(100) PRIMARY KEY Id);
Explain with an example how to create a new table by selecting rows from another table.
Tables can be created by using data selected from another table using the CREATE TABLE AS command.
Example:
CREATE TABLE employee AS SELECT * FROM oldemployee
Depict a sample script for renaming an existing table.
Existing tables can be renamed using RENAME TABLE command as shown below:
RENAME TABLE table_name TO new_table_name
Example:
RENAME TABLE employee TO oldemployee
Sample script to drop an existing table.
Existing tables (with date) can be dropped using the DROP command.
Example:
DROP TABLE TABLE_NAME
DROP TABLE employee
How to Add a new column to an existing table?
New column can be added using the ALTER TABLE command as shown below:
Example:
ALTER TABLE employee
ADD DateOfBirth date
How to Add a new column to an existing table with a default value?
Column can be added to an existing table with a default value using the ALTER TABLE command.
Syntax:
ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT defaultvalue }
How to Rename a column in an existing table?
Existing tables can be renamed using RENAME TABLE command as shown below:
RENAME TABLE table_name TO new_table_name
Example:
RENAME TABLE employee TO oldemployee
How to Delete a column in an existing table?
A column can be deleted from an existing table using the ALTER TABLE command.
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name
How to View All Columns in an existing table?
All columns can be viewed using the SHOW COLUMNS syntax:
Example:
SHOW COLUMNS FROM employee;
How to recover a dropped table?
Dropped tables can be recovered using DROP TABLE flashback. It works the way recycle bin works.
Example:
FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;
The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.
What is an oracle recycle bin?
All dropped tables in oracle are stored in the recycle bin. When a table is dropped, it is renamed and placed in recycle bin along with some dependant objects like indexes, constraints (excluding foreign constraints), triggers, nested tables, LOB segments of the table.
How to Turn On or Off recycle bin for the Instance?
By default the recycle bin instance is ON. It can be changed to OFF by configuring the database and typing the command RECYCLEBIN = [ ON | OFF]
How to View the dropped tables in your recycle bin?
The dropped tables can be viewed using SHOW RECYLCEBIN command.
How to empty your recycle bin?
Recycle bin can be emptied using the PURGE RECYLCEBIN command.
How to Turn On or Off recycle bin for the session?
If the recycle bin needs to be OFF for a session, the following command can be used:
ALTER SESSION SET RECYCLEBIN=OFF
How to List All tables in your schema?
Tables can be listed using the following script:
Example:USER_TABLES view can be used to list all tables.
SELECT table_name, status, num_rows FROM USER_TABLES;
