Oracle Database Questions And Answers

201⟩ What Is an Oracle 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

122 views

202⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

121 views

203⟩ 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:

>.insqlplus /nolog

SQL> connect HR/globalguideline

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".

Is this answer helpful? 0 Yes | 0 No

Answer This Question

135 views

204⟩ 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/globalguideline

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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

132 views

205⟩ 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/globalguideline

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

138 views

206⟩ 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/globalguideline

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

138 views

207⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

141 views

208⟩ 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".

Is this answer helpful? 0 Yes | 0 No

Answer This Question

132 views

209⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

139 views

210⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

143 views

211⟩ How To Recover a Dropped Table in Oracle?

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

142 views

212⟩ What Is an Oracle 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

133 views

213⟩ 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/globalguideline

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

160 views

214⟩ 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/globalguideline

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

154 views

215⟩ How To Empty Your Oracle 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/globalguideline

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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

159 views

216⟩ 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/globalguideline

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

140 views

217⟩ 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/globalguideline

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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

179 views

218⟩ What Is a Table Index?

Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

141 views

219⟩ How To Run SQL Statements through the Web Interface?

If you don't like the command line interface offered by SQL*Plus, you can use the Web interface to run SQL statements. Here is how:

► Open your Web browser to http://localhost:8080/apex/

► Log in to the server with the predefined sample user account: HR/globalguideline

► Click the SQL icon

► Click the SQL Commands icon

► Enter any SQL statement like: "SELECT COUNT(*) FROM USER_TABLES;", in the text area and click Run button

► Your Oracle will execute the statement, and display the result in the result area.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

144 views

220⟩ How To Create a Table Index?

If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use the CREATE INDEX statement as shown in the following script:

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

subject VARCHAR(80) NOT NULL,

description VARCHAR(256) NOT NULL,

create_date DATE DEFAULT (sysdate));

Table created.

CREATE INDEX tip_subject ON tip(subject);

Index created.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

126 views

Oracle Database

Home Oracle Oracle Database

430 Oracle Database Questions And Answers

201⟩ What Is an Oracle Database Table?

202⟩ How Many Types of Tables Supported by Oracle?

203⟩ How To Create a New Table in Your Schema?

204⟩ How To Rename an Existing Table?

205⟩ How To Drop an Existing Table?

206⟩ How To Add a New Column to an Existing Table?

207⟩ How To Add a New Column to an Existing Table with a Default Value?

208⟩ How To Rename a Column in an Existing Table?

209⟩ How To Delete a Column in an Existing Table?

210⟩ How To View All Columns in an Existing Table?

211⟩ How To Recover a Dropped Table in Oracle?

212⟩ What Is an Oracle Recycle Bin?

213⟩ How To Turn On or Off Recycle Bin for the Instance?

214⟩ How To View the Dropped Tables in Your Recycle Bin?

215⟩ How To Empty Your Oracle Recycle Bin?

216⟩ How To Turn On or Off Recycle Bin for the Session?

217⟩ How To List All Tables in Your Schema?

218⟩ What Is a Table Index?

219⟩ How To Run SQL Statements through the Web Interface?

220⟩ How To Create a Table Index?

Quick Links:

Oracle Database

Home Oracle Oracle Database

430 Oracle Database Questions And Answers

201⟩ What Is an Oracle Database Table?

202⟩ How Many Types of Tables Supported by Oracle?

203⟩ How To Create a New Table in Your Schema?

204⟩ How To Rename an Existing Table?

205⟩ How To Drop an Existing Table?

206⟩ How To Add a New Column to an Existing Table?

207⟩ How To Add a New Column to an Existing Table with a Default Value?

208⟩ How To Rename a Column in an Existing Table?

209⟩ How To Delete a Column in an Existing Table?

210⟩ How To View All Columns in an Existing Table?

211⟩ How To Recover a Dropped Table in Oracle?

212⟩ What Is an Oracle Recycle Bin?

213⟩ How To Turn On or Off Recycle Bin for the Instance?

214⟩ How To View the Dropped Tables in Your Recycle Bin?

215⟩ How To Empty Your Oracle Recycle Bin?

216⟩ How To Turn On or Off Recycle Bin for the Session?

217⟩ How To List All Tables in Your Schema?

218⟩ What Is a Table Index?

219⟩ How To Run SQL Statements through the Web Interface?

220⟩ How To Create a Table Index?

BE THE FIRST TO KNOW

Quick Links: