Continuation of table maintenance revisited (again)...
Oracle has a table dictionary called user_tables which gives detailed information about the
tables, an object table called user_objects which gives information about objects and the
user_catalog. If you want to see specific characteristics you can do a desc of user_objects
or sys.user.objects for example, look at the types and then do a select using that type as a
column name. Distinct can be used to avoid repetition. The code below shows a partial list.
SQL CODE:
SQL> Desc user_objects;
Name Null? Type
------------------------------- -------- ----
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(13)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(75)
STATUS VARCHAR2(7)
SQL> SELECT object_name, object_type, created
2 FROM user_objects;
OBJECT_NAME
--------------------------------
OBJECT_TYPE CREATED
------------- ---------
BONUS
TABLE 16-SEP-96
DEPARTMENT
TABLE 17-JUN-99
DEPT
TABLE 16-SEP-96
DEPTARTMENT
TABLE 23-JUN-99
DEPTNO_PK
INDEX 23-JUN-99
DONATION
TABLE 23-JUN-99
If you want to look at the tables, views etc. that you have created, you can do a SELECT *
FROM USER_CATALOG or just SELECT * FROM CAT. This will print out the list as shown below.
SQL CODE:
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BONUS TABLE
DEPARTMENT TABLE
DEPT TABLE
DEPTARTMENT TABLE
DONATION TABLE
DONOR TABLE
DRIVE TABLE
EMP TABLE
EMPLOYEE TABLE
FIRST_TABLE TABLE
INVCUST TABLE
INVEN TABLE
INVENTRY TABLE
LOCATION TABLE
MAINTAIN TABLE
MOVEMAIN TABLE
ORDERZ TABLE
ORDLINE TABLE
SALARYGR TABLE
SALGRADE TABLE
SALSREP TABLE
TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST1 TABLE
TESTNULL TABLE
23 rows selected.
The rules for table and column names follow fairly common types of rules. They must:
- Begin with a letter
- Be no more than 30 characters
- Contain only letters (upper and lower case), numbers, the underscore character, dollar signs and the number sign (_, $, #)
- Must be unique within a unit so as to not cause confusion and must not be a reserved word.
- Not case sensitive
I find it helpful to give the same data the same name in different tables so I can remember
the name.
Data Definition Language (DDL) statements are used to create, change or delete database
structures. DDL statements are automatically committed when they are executed.
CREATE
Here are a few more things about the create. When you create a table you can set a DEFAULT
value to be entered if the INSERT does not put data into the column. When doing this, you
cannot use another column name, it must be a literal, an expression or a function and the
data type must match.
SQL CODE:
SQL> CREATE TABLE test1
2 (idnum NUMBER (3), name VARCHAR(2), dept CHAR(2) DEFAULT 'CI', dateapply DATE DEFAULT sysdate);
Table created.
SQL> DESC test1;
Name Null? Type
------------------------------- -------- ----
IDNUM NUMBER(3)
NAME VARCHAR2(2)
DEPT CHAR(2)
DATEAPPLY DATE
SQL> INSERT INTO test1 (idnum, name)
2 VALUES (111, 'AB');
1 row created.
SQL> SELECT * FROM test1;
IDNUM NA DE DATEAPPLY
--------- -- -- ---------
111 AB CI 24-JUN-99
SQL> INSERT INTO test1 (idnum, name)
2 VALUES (112, 'BC');
1 row created.
SQL> INSERT INTO test1 (idnum, name, dateapply)
2 VALUES (115,'CD', '12-JUN-99');
SQL> INSERT INTO test1 (idnum, dept)
2 VALUES (116,'BU');
SQL> INSERT INTO test1 (idnum, dept, dateapply)
2 VALUES (115, 'GS', last_day(sysdate));
SQL> SELECT * FROM test1;
IDNUM NA DE DATEAPPLY
--------- -- -- ---------
111 AB CI 24-JUN-99
112 BC CI 24-JUN-99
115 CD CI 12-JUN-99
116 BU 24-JUN-99
115 GS 30-JUN-99
You can create a whole new table from an existing table by using a subquery. In this example,
I am creating a table called newmain from the maintain table. I am putting selected fields the
same and I am altering the price field by adding 1 to it and giving it a new name in the new
table. Just as I only wanted certain columns, I only wanted certain rows. This is handled in
the WHERE clause.
SQL CODE:
SQL> DESC maintain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(12)
PRICE NUMBER(6,2)
PURCHASED DATE
DEPT CHAR(2)
COST NUMBER(6,2)
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
SQL> CREATE TABLE newmain
2 AS
3 SELECT idno, itemname, price+1 updtprice, dept
4 FROM maintain
5 WHERE cost > 10;
Table created.
SQL> DESC newmain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(12)
UPDTPRICE NUMBER
DEPT CHAR(2)
SQL> SELECT * FROM newmain;
IDN ITEMNAME UPDTPRICE DE
--- ------------ --------- --
123 Teddy Bear 21 TY
234 Dump Truck 16.95 TY
678 Warrior 16.99 TY
DROP TABLE
The DROP TABLE command is used to drop a table which removes the definition of the table and
all data in the table. It is not reversible since it is a Data Definition Language (DDL)
statement and is immediately committed when executed and there is now warning about are you
sure etc., so be careful. In the example below I created a table called test1x from test1,
showed the successful creation, dropped the table and showed that it was no longer available.
SQL CODE:
SQL> CREATE TABLE test1x
2 AS
3 SELECT * FROM test1;
Table created.
SQL> SELECT * from test1x;
IDNUM NA DE DATEAPPLY
--------- -- -- ---------
111 AB CI 24-JUN-99
112 BC CI 24-JUN-99
115 CD CI 12-JUN-99
116 BU 24-JUN-99
115 GS 30-JUN-99
SQL> DROP TABLE test1x;
Table dropped.
SQL> SELECT * FROM test1x;
SELECT * FROM test1x
*
ERROR at line 1:
ORA-00942: table or view does not exist
RENAME
A table, view, sequence or synonym (coming) can be renamed using the rename. This is a DDL so
it commits upon execution.
SQL CODE:
SQL> CREATE TABLE test12
2 AS
3 SELECT * FROM test1;
Table created.
SQL> SELECT * FROM test12;
IDNUM NA DE DATEAPPLY
--------- -- -- ---------
111 AB CI 24-JUN-99
112 BC CI 24-JUN-99
115 CD CI 12-JUN-99
116 BU 24-JUN-99
115 GS 30-JUN-99
SQL> RENAME test12 TO test2;
Table renamed.
SQL> SELECT * FROM test2;
IDNUM NA DE DATEAPPLY
--------- -- -- ---------
111 AB CI 24-JUN-99
112 BC CI 24-JUN-99
115 CD CI 12-JUN-99
116 BU 24-JUN-99
115 GS 30-JUN-99
SQL> DESC test2;
Name Null? Type
------------------------------- -------- ----
IDNUM NUMBER(3)
NAME VARCHAR2(2)
DEPT CHAR(2)
DATEAPPLY DATE
TRUNCATE
The truncate statement removes all of the rows in the table and releases the storage space. It
is a DDL so it is committed upon execution. The DELETE can also be used to remove all rows
however it does no release the storage space and it can be rolled back because it is not a DDL
statement.
SQL CODE:
SQL> TRUNCATE TABLE test2;
Table truncated.
SQL> SELECT * FROM test2;
no rows selected
SQL> DESC test2;
Name Null? Type
------------------------------- -------- ----
IDNUM NUMBER(3)
NAME VARCHAR2(2)
DEPT CHAR(2)
DATEAPPLY DATE
ALTER
Don't forget that you can alter a table by adding or modifying columns, this was covered in
another handout. Here I am going to alter test2 to give the name more characters.
SQL CODE:
SQL> DESC test1;
Name Null? Type
------------------------------- -------- ----
IDNUM NUMBER(3)
NAME VARCHAR2(2)
DEPT CHAR(2)
DATEAPPLY DATE
SQL> ALTER TABLE test1
2 MODIFY (name VARCHAR2(15));
Table altered.
SQL> DESC test1;
Name Null? Type
------------------------------- -------- ----
IDNUM NUMBER(3)
NAME VARCHAR2(15)
DEPT CHAR(2)
DATEAPPLY DATE
COMMENTS
The comment command can be used to add comments to a table or to a column within the table.
To view the comments you can query the data dictionary. The data dictionaries that can hold
the comments are (ALL_COL_COMMENTS, USER_COL_COMMENTS, ALL_TAB_COMMENTS, USER_TAB_COMMENTS).
To get rid of a comment you can set it to an empty string which is done with the IS ' ' clause.
SQL CODE:
SQL> COMMENT ON TABLE TEST1
2 IS 'This is a test table created and modified frequently';
Comment created.