Tables are the primary data storage elements in an Oracle database, organised into rows and columns.
Each column has a name and a specific datatype and size, such as CHAR(16), VARCHAR2(50), TIMESTAMP(6), or NUMBER.
Example: EMPLOYEE table:
Each row contains complete information of one entity. In the example table, the first row contains information of Tom, who is a Manager and is working in Sales Department
Table and column names must meet the following requirements:
Must be from 1 to 30 bytes in length.
Must begin with a letter. Can include letters, numbers, the underscore symbol (_), the pound symbol (#), and the dollar symbol ($).
(Oracle does not encourage use of pound and dollar symbols).
Cannot be a reserved word such as NUMBER or INDEX.
If the name is enclosed in double quotation marks (" "), the only requirement is that the name be from 1 to 30 bytes long and not contain an embedded double quotation mark.
Each column name must be unique within a table.
Each table must have unique name within the namespace.
Namespace is a domain of allowable names for the set of schema objects.
Tables, views, sequences, private synonyms, procedures, functions, packages, materialized views, and user-defined types use the same namespace. In addition to the namespace shared by tables and views, the database has separate namespaces for each of the following:
|Database Triggers||Private Database Links||Dimensions|
|Public Synomyms||Public Dat abase Links||Tablespaces|
|Profiles||Parameter files|| |
CREATE TABLE tbl_a(e_name, desgn VARCHAR2(64), salary NUMBER);
The above table can be specified a tablespace in which it is to be placed in as follows:
CREATE TABLE tbl_b(e_name, desgn VARCHAR2(64), salary NUMBER) TAPLESPACE users;
Commas delimit or separate the column definitions, which start with the column name: ename, desgn, salary in this example.
To display the structure of a table, use DESCRIBE command.
SQL> describe tbl_1
CREATE TABLE tbl_c TABLESPACE archives AS SELECT * FROM tbl_a;
Temporary Table's contents are are transitory and only visible to the session that inserted data into it. The definition of the table persists, but the data lasts only for either the duration of the transaction (ON COMMIT DELETE ROWS) or for the duration of the session (ON COMMIT PRESERVE ROWS).
CREATE GLOBAL TEMPORARY TABLE my_session (category VARCHAR2(16), running_count NUMBER) ON COMMIT DELETE ROWS;
Programs can manipulate data in temporary tables or join them to permanent tables in the same manner as any other table.
While entering data into a table some columns can have default value. If no data is entered, default value is placed in that column for a particular row. For example, assume that salary value is set to 10000 as default. When no data is entered, default value 10000 is inserted. In a case where we are not sure of this value of 10000, NULL can be entered. Note that setting default value does not ensure a value, as the value can be later set to NULL.
Default values can be either a fixed value or an SQL expression that does not reference a PL/SQL function, other columns, or the pseudocolumns ROWNUM, NEXTVAL, CURRVAL, LEVEL, or PRIOR.
Default values are defined as part of a column specification either at table creation time OR after a table has been created.
CREATE TABLE tbl_1( log_id NUMBER, who VARCHAR2(64) DEFAULT USER,
when TIMESTAMP DEFAULT SYSTIMESTAMP, what VARCHAR2(200) ) TABLESPACE users;
ALTER TABLE tbl_1 MODIFY who VARCHAR2(64) DEFAULT USER;
Descriptive comments can be added to tables and columns in order to better describe the content or usage of these objects. Comments can be a maximum of 4,000 bytes in length and can have embedded white space and punctuation.
Use the COMMENT ON statement to assign a comment to either a table or a column, like this:
COMMENT ON TABLE change_log IS
'This table is where you record changes to the configuration of the DEMO system';
COMMENT ON COLUMN change_log.log_id IS
'System generated key for change log table Populated with the change_seq sequence.';
The comment must be enclosed in single quotes, but can span physical lines.
To display the comments on a table, query the data dictionary views:
DBA_TAB_COMMENTS, ALL_TAB_COMMENTS, or USER_TAB_COMMENTS
To display the comments on a column, query the data dictionary views:
DBA_COL_COMMENTS, ALL_COL_COMMENTS, or USER_COL_COMMENTS
SELECT owner, table_name, comments FROM all_tab_comments
WHERE table_name = 'CHANGE_LOG';
SELECT table_name, column_name, comments FROM user_col_comments
WHERE table_name = 'CHANGE_LOG' AND column_name = 'LOG_ID';
To change the name of the change_log table to demo_change_log, execute this:
RENAME tbl_1 TO tbl_2;
ALTER TABLE tbl_a RENAME TO tbl_b;
Use the ALTER TABLE statement to add columns. To add multiple columns, enclose a comma-delimited list of columns
ALTER TABLE table_name ADD column_spec;
ALTER TABLE change_log ADD new_column VARCHAR2(45);
ALTER TABLE change_log ADD (col_1 VARCHAR2(45), col_2 VARCHAR2(60));
The column specification includes COLUMN name, DATATYPE and DEFAULT_VALUE if any.
To remove a column, use the ALTER TABLE DROP COLUMN statement: To drop multiple columns, don’t use the keyword COLUMN. Enclose the comma- delimited list of columns in parentheses:
ALTER TABLE table_a DROP COLUMN col_1;
ALTER TABLE table_a DROP (col_1, col_2);
Use the ALTER TABLE MODIFY statement to make column-level changes. As with the ADD and DROP options, there are two syntactical options: one for modifying a single column and one for modifying multiple columns.
To make changes to a single column, specify the column name together with the new characteristics. For example, to change the column EMP_NAME from VARCHAR2(200) to VARCHAR2(250), execute: To change multiple columns, enclose a comma-delimited list of modified column specifications in parentheses, like this:
ALTER TABLE change_log MODIFY what VARCHAR2(250);
ALTER TABLE change_log MODIFY (what VARCHAR2(250), who VARCHAR2(50) DEFAULT user);
Viewing the Attributes of a Table Several data dictionary views can be used to display the attributes of a table. Easy method is to use DESCRIBE command from SQL*Plus or iSQL*Plus:
The DESCRIBE command displays the column names, datatypes, and nullity of each column. To see the table physical attributes, query the ALL_TABLES or USER_TABLES dictionary view, like this: To see what constraints are declared on a table, use the ALL_CONSTRAINTS and ALL_CONS_ COLUMNS views, like this:
SQL> describe employees
SELECT * FROM user_tables
WHERE table_name = 'EMPLOYEES';
SELECT constraint_name, constraint_type, r_constraint_name
WHERE table_name = 'EMPLOYEES';
The CONSTRAINT_TYPE column indicates the kind of constraint: C is for Check P is for Primary Key R is for Referential (or Foreign Key) U is for Unique
NOT NULL constraints are stored both as a column attribute and as a check constraint.
The SEARCH_CONDITION column of the USER_CONSTRAINTS view is only applicable for CHECK constraints: The columns participating in a FOREIGN KEY constraint can be found in the ALL_CONS_ COLUMNS view, like this:
SELECT search_condition FROM user_constraints
WHERE constraint_name = 'SYS_C005286';
SELECT column_name, position FROM all_cons_columns
WHERE constraint_name = 'EMP_DEPT_FK';
Viewing the Contents of a Table To view the contents of a table, simply use the SELECT statement with comma-separated column list. Use * to view all the columns (i.e., to view entire table). Use WHERE Clause to filter the rows.
SELECT * FROM employees;
SELECT employee_id, first_name, last_name, hire_date FROM employees
WHERE hire_date > SYSDATE – 90;