When creating tables in Oracle we have to follow some rules in order to successfully create a table:
- Table names and columns can be up to 30 characters long.
- Names of tables and columns cannot have any blank spaces.
- Numbers, underscores, and number signs are allowed in the table and column name.
- Each table that a user owns must have a unique name and each column in that table must have a unique column name.
- You can't use reserved keywords as a table or column name.
When creating a table you must define the column names and types. The four basic and common column types are:
| Datatype | Description |
| VARCHAR2(n) | When defining this type of column, you can assign a maximum size, but it's not necessary. When data is placed in this column, the length is resized to the length of the data that is in this column. The maximum length that this data type can hold is 4000 characters. |
| CHAR(n) | This is a fixed data type field. If you define this column with a length of 20 and only enter in one character, then you have wasted 19 bytes. The default size is 1 and the maximum size is 2000 characters. |
| NUMBER(p,s) | This is a numeric column. p is the precision, the total number of characters to the left of the decimal point. s is the scale, or total number of numbers to the right of the decimal point. The maximum size of this column can be 38 digits and the default length is 38. When providing a precision and scale the scale is subtracted from the precision. For example, if we take (5,2), we are telling oracle that we will be using 5 digits of the 38 and using 2 of them as a decimal value. The maximum value that this example could hold would be 999.99. |
| DATE | This data type stores the date and time between January 1, 4712 B.C. and December 31, 9999 A.D. Oracle displays dates in the DD-MON-YY format. The width of this column is 7 bytes. |
Syntax
CREATE TABLE [schema] tablename ( columnname datatype [DEFAULT value], columnname datatype [DEFAULT value], ... ); The schema is the user you want to create the table under. If I were creating the table for myself I could ommit that. However, if someone else were creating a table for me, they'd have to replace schema with paulus.
Example
CREATE TABLE exampletable ( id NUMBER(4), name VARCHAR2(64), ex_date DATE DEFAULT SYSDATE, region CHAR(2) ); This will create a table named exampletable with four columns. The first one will be called 'id' with a size of 4 digits. The second column will be called name of data type VARCHAR2 with a variable width of up to 64 characters. 'ex_date' is an example of a DATE data type using a default value which is the current time of when data is inserted. The last column uses a fixed character date type. In addition you can create table using sub-quries in the event that you want to create a table based on another table with data already in that table.
Syntax
CREATE TABLE tablename [(columnname,...)] AS (subquery) If you chose different column names in this table then you must list the column names after the table name. When creating a column list you must have the exact same number of columns defined and in the same order as you are querying them. In the event that you want to take a selected set of columns with data and not rename any of the columns then you can skip defining the column names after the table name.
Example
CREATE TABLE ex_table(id2, name2) AS ( SELECT id, name FROM exampletable );
Getting Table Information
The USER_TABLES data dictionary which is a very useful component. To see what tables are in your schema just run the following query: SELECT table_name FROM user_tables; When you have a table that you want to know more about just run the DESCRIBE or DESC command on that table. DESCRIBE tablename; or DESC tablename;
Modifying a Table
Sometimes we may need to change a table’s structure. This is where the ALTER TABLE command comes into play. We can use the ADD, MODIFY, or DROP COLUMN clause to modify a table.
Syntax
ALTER TABLE tablename ADD | MODIFY | DROP COLUMN columnname [ definition ]; ALTER TABLE tablename ADD (columnname data type [ DEFAULT], ...); ALTER TABLE tablename MODIFY ( columnname datatype [ DEFAULT ], ...); ALTER TABLE tablename DROP COLUMN columnname;
Example
ALTER TABLE ex_table ADD (newcol NUMBER(4) DEFAULT 20); ALTER TABLE ex_table MODIFY (newcol NUMBER(5)); ALTER TABLE ex_table DROP COLUMN newcol; The ADD clause is pretty straight forward, you just need to follow the standard rules for naming columns. When modifying a column, you can do the following:
- Increase or decrease the column size.
- Change the data type.
- Changing or adding a default value.
When modifying the column remember three rules:
- A column must be at least as wide as the data that's in there already.
- If the column is a number you can not decrease the precision.
- Changing the default value does not affect the data that's already in the table.
With the DROP COLUMN clause, you can only drop one column at a time. Like the ADD and MODIFY clauses there are rules that must be followed:
- You can only reference one column.
- The deletion of the column is permanent.
- If there is only one column left in the table, you can not drop that column. You would have to drop the table itself.
- You can not drop a primary key column.
UNUSED Columns
During the process of dropping a column from a very large table, all queries will take a bit longer to process. This is where the SET UNUSED clause comes into play. What happens is when the ALTER TABLE command is issued with the SET UNUSED clause, it marks a column for deletion making it unavailable. Once it's unavailable it can physically be removed at a later time.
Syntax
ALTER TABLE tablename SET UNUSED (columnname); or ALTER TABLE tablename SET UNUSED COLUMN columnname;
Example
ALTER TABLE ex_table SET UNUSED(name2); or ALTER TABLE ex_table SET UNUSED COLUMN name2;
Removing Tables and Table Data
After setting any unused columns you can then drop the columns after the database is experiencing less traffic. ALTER TABLE tablename DROP UNUSED COLUMNS; In the event that you want to dump all the data in the table with out having to reconstruct the table, you can TRUNCATE the table. Not only does this drop the data it also frees up space in the database. TRUNCATE TABLE tablename; To completely remove the table from the database you can issue the DROP TABLE command: DROP TABLE tablename [PURGE]; Oracle now has a feature that when a table is dropped, it will move it to a recycle bin. However, the DBA must have this enabled. If a table is purged, then it skips the recycle bin completely and becomes cyber dust. FLASHBACK TABLE tablename TO BEFORE DROP;