TABLES in Oracle

TABLES in Oracle

• TABLE is a database object which is used to store data in database.
• TABLE stores data in rows and column format
• A TABLE structure can be modified post creation of the table

Naming convention of a TABLE:

• Table name must starts with a character
• In older version prior 12c second version, table name length was restricted to 30 bytes only. But from 12c second version onwards oracle allows table name length to 128 bytes long.
• Oracle allows only below characters while choosing a table name:
  • A – Z
  • a – z
  • 0 – 9
  • _
  • $
  • #
• The table name should not be duplicated with another object name under the same user
• Table name should not be equal with an oracle server reserved words

Data Types used in Table columns:

Data Type	Description
VARCHAR2(size)	Variable – length character data. In older version maximum size was 4000 bytes or characters. In latest version like 12c second version onwards there is a DB parameter MAX_STRING_SIZE which controls the VARCHAR2 size. If MAX_STRING_SIZE value sets at STANDARD then VARCHAR2 allows older maximum size, i.e. 4000 bytes or characters. But if MAX_STRING_SIZE is set at EXTENDED then VARCHAR2 allows maximum size of 32767 bytes or characters.
CHAR(size)	Fixed – length character data. Maximum size of CHAR is 2000 bytes.
NUMBER(precision, size)	Variable – length numeric data. Maximum value is (38 9's) x10125 and minimum value is -(38 9's) x10125
DATE	It stores date and time values
LONG	Variable – length character data upto 2 GB. A table can have only one LONG column A LONG column can not be used in GROUP BY and ORDER BY clause No Constraint can be defined on a LONG column A LONG column can not be copied when a table is created by using a subquery
CLOB	CLOB stands for Character Large Object. It can store both single byte or multibyte characters. Both fixed – width or variable – width characters are supported. Maximum size allowed is 4 GB.
RAW and LONG RAW	RAW and LONG RAW data types are used for data which are not to be interpreted by Oracle. They are needed to store binary data or byte strings. LONG RAW can be used to store graphics, sounds, documents or arrays of binary data. RAW data can only be queried or inserted. But other DML operations are not allowed. RAW column can be indexed but LONG RAW can not be indexed. Maximum size of RAW is 2000 bytes if MAX_STRING_SIZE value sets at STANDARD. If the same is set at EXTENDED then maximum value is 32767. LONG RAW has maximum value up to 2 GB A table can have only one LONG RAW column. Currently RAW and LONG RAW are used for backward compatibility in existing application. Otherwise one should use BFILE and BLOB.
BLOB	BLOB stands for Binary Large Objects. It stores unstructured binary large objects, e.g. digitized information like image, audio, video etc. Maximum size of BLOB is 4 GB.
BFILE	BFILEs are Large Binary objects stored in operating system outside of database tablespace. A BFILE is accessed and referenced by a locator which is stored in the database table and points to the BFILE data. Due to its existence at outside the database BFILE is a read-only data type. Its not participated in any transaction. Maximum size of BFILE is 4 GB.

Types of Table in Oracle

• USER TABLES
• DATA DICTIONARY TABLES

USER TABLES:

User tables are created by user and store the user information.

CREATING USER TABLES:

To create a table we need to mention the table name, column name, column size and column data types.

CREATE TABLE EMP_TABLES
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_ID_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2)
);

Confirming the table structure:

DESC EMP_TABLES;

Inserting data into EMP_TABLES table:

INSERT INTO EMP_TABLES(EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       JOB_ID,
                       SALARY
                      )
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMPLOYEES;

Selecting data from EMP_TABLES:

SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMP_TABLES
ORDER BY EMPLOYEE_ID;

DATA DICTIONARY TABLES:

Data dictionary tables are created by oracle server. They store the details of every data base objects and tables created by users.

Lets check the data dictionary tables for EMP_TABLES:

SELECT OBJECT_NAME, 
       OBJECT_ID, 
       OBJECT_TYPE, 
       CREATED, 
       LAST_DDL_TIME, 
       ORACLE_MAINTAINED 
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'EMP_TABLES';

SELECT TABLE_NAME, 
       TABLESPACE_NAME, 
       STATUS, 
       TABLE_LOCK 
FROM USER_TABLES
WHERE TABLE_NAME = 'EMP_TABLES';

SELECT * FROM USER_CATALOG
WHERE TABLE_NAME = 'EMP_TABLES';

Checking the columns details of EMP_TABLE:

SELECT TABLE_NAME, 
       COLUMN_NAME, 
       DATA_TYPE, 
       DATA_LENGTH, 
       DATA_PRECISION, 
       DATA_SCALE, 
       NULLABLE, 
       COLUMN_ID 
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP_TABLES';

Checking the constraint of EMP_TABLES table:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       STATUS, 
       INDEX_OWNER, 
       INDEX_NAME 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_TABLES';

Checking the column – constraint mapping of EMP_TABLES table:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME, 
       POSITION 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_TABLES';

Table Creation Methods:

CREATING TABLE BY DEFINING THE STRUCTURE MANUALLY:

DROP TABLE EMP_TABLES;

CREATE TABLE EMP_TABLES
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_ID_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2)
);

DESC EMP_TABLES;

CREATING TABLE BY COPYING THE STRUCTURE OF ANOTHER TABLE:

COPYING THE COMPLETE STRUCTURE OF ANOTHER TABLE:

CREATE TABLE EMP_TABLES_COPY_COMP
AS
SELECT * FROM EMP_TABLES;

DESC EMP_TABLES_COPY_COMP;

When table is created by CREATE TABLE AS SELECT statement then PRIMARY KEY and other constraints are not copied into target table. But NOT NULL constraints are copied.

COPYING THE PARTIAL STRUCTURE OF ANOTHER TABLE:

CREATE TABLE EMP_TABLES_COPY_PART
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       JOB_ID
FROM EMP_TABLES;

DESC EMP_TABLES_COPY_PART;

CREATING TABLE FROM SUBQUERY:

CREATE TABLE EMP_TABLES_COPY_SUBQ
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME||' '||LAST_NAME FULL_NAME,
       JOB_ID,
       SALARY * 12 YEARLY_SALARY
FROM EMP_TABLES;

DESC EMP_TABLES_COPY_SUBQ;

Table Modification Methods:

ADDING A COLUMN:

DESC EMP_TABLES_COPY_PART;

ALTER TABLE EMP_TABLES_COPY_PART ADD(LAST_NAME VARCHAR2(30));

DESC EMP_TABLES_COPY_PART;

ADDING MULTIPLE COLUMNS:

ALTER TABLE EMP_TABLES_COPY_PART ADD(SALARY NUMBER(8, 2),
                                     DEPARTMENT_ID NUMBER(4));

DESC EMP_TABLES_COPY_PART;

MODIFYING A COLUMN:

ALTER TABLE EMP_TABLES_COPY_PART MODIFY(FIRST_NAME VARCHAR2(50));

DROPPING COLUMNS:

ALTER TABLE EMP_TABLES_COPY_PART DROP (LAST_NAME,  
                                       SALARY,
                                       DEPARTMENT_ID
                                      );

DESC EMP_TABLES_COPY_PART;

SET UNUSED OPTION:

Dropping a column from a high volume table is a high resource intensive operation. So we can mark them as UNUSED to drop them later when the system resources are lower. Once a column is set as UNUSED then we cannot access the column. it can only be dropped later. It will not be queried through SELECT * command or will not be visible in DESC command.

DESC EMP_TABLES_COPY_PART;

SELECT * FROM EMP_TABLES_COPY_PART
ORDER BY EMPLOYEE_ID;

ALTER TABLE EMP_TABLES_COPY_PART
SET UNUSED (JOB_ID);

DESC EMP_TABLES_COPY_PART;

SELECT * FROM EMP_TABLES_COPY_PART
ORDER BY EMPLOYEE_ID;

Querying data dictionary:

SELECT * FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME = 'EMP_TABLES_COPY_PART';

RENAMING A COLUMN:

DESC EMP_TABLES_COPY_PART;

ALTER TABLE EMP_TABLES_COPY_PART RENAME COLUMN EMPLOYEE_ID TO EMPID;

DESC EMP_TABLES_COPY_PART;

RENAMING A TABLE:

ALTER TABLE EMP_TABLES_COPY_PART RENAME TO EMP_TABLES_PARTLY_COPIED;

DESC EMP_TABLES_COPY_PART;

DESC EMP_TABLES_PARTLY_COPIED;

DROPPING A TABLE:

DROP TABLE EMP_TABLES_PARTLY_COPIED;

TRUNCATING A TABLE:

TRUNCATE table command removes all the records from the table permanently.

SELECT * FROM EMP_TABLES_COPY_SUBQ;

SELECT COUNT(1) TOTAL_COUNT FROM EMP_TABLES_COPY_SUBQ;

TRUNCATE TABLE EMP_TABLES_COPY_SUBQ;

SELECT COUNT(1) TOTAL_COUNT FROM EMP_TABLES_COPY_SUBQ;

ADDING COMMENTS TO A TABLE:

COMMENT ON TABLE EMP_TABLES_COPY_SUBQ IS 'TABLE IS CREATED FROM SUB QUERY';

SELECT * FROM USER_TAB_COMMENTS
WHERE TABLE_NAME = 'EMP_TABLES_COPY_SUBQ';

ADDING COMMENTS TO A TABLE COLUMN:

COMMENT ON COLUMN EMP_TABLES_COPY_SUBQ.EMPLOYEE_ID IS 'EMPLOYEE_ID IS THE UNIQUE IDENTIFICATION NUMBER OF EACH EMPLOYEE';

SELECT * FROM USER_COL_COMMENTS
WHERE TABLE_NAME = 'EMP_TABLES_COPY_SUBQ';

RELATED TOPICS:

• SINLE TABLE INSERT
• MULTIPLE TABLE INSERT
• UPDATE TABLE OPERATION
• DELETE TABLE OPERATION
• TRUNCATE TABLE OPERATION