naming Conventions for table and column names :
Must start with a letter
Must be between 1-30 characters
can only contain a-z,a-z,0-9,_,$,#
Cannot duplicate user-defined other objects
Cannot use Oracle's reserved words
Pre-created conditions:
CREATE Table Permissions
When you build a table, you must specify:
Table name
Column name, column data type, maximum length
Data type:
VARCHAR2 (size) variable length character type (maximum length 4000 characters)
char (size) fixed length character type (maximum 2000 characters)
Number (p,s) numeric type
Date type
Long variable large character type (max. 2G)
CLOG large character type (max. 4G)
Raw Bare binary data (maximum 2000 characters)
Long raw bare binary data (2G max)
Blob binary data, Max 4G
bfile External binaries, Max 4G
Type |
Meaning |
Storage description |
Note |
CHAR |
Fixed-length strings |
Maximum length 2000bytes |
|
VARCHAR2 |
Variable-length strings , |
Maximum length 4000bytes |
Maximum length of index can be made 749 |
NCHAR |
Fixed-length strings based on character set |
Maximum length 2000bytes |
|
NVARCHAR2 |
Variable-length strings based on character set |
Maximum length 4000bytes |
|
DATE |
Date (Day - month - year) |
Dd-mm-yy (HH-MI-SS), after rigorous testing, no problem of thousand worms |
|
TIMESTAMP |
Date (Day - month - year) |
Dd-mm-yy (HH-MI-SS:FF3), after rigorous testing, no problem of thousand worms |
compared to DATE,TIMESTAMP has a decimal-seconds message |
LONG |
Super Long string |
Maximum length 2G, enough to store tome |
|
RAW |
Fixed-length binary data |
Maximum length 2000bytes |
can store multimedia image sound, etc. |
LONG RAW |
Variable-length binary data |
Maximum length 2G |
can store multimedia image sound, etc. |
Blob |
Binary data |
Maximum length 4G |
|
Clob |
Character data |
Maximum length 4G |
|
NCLOB |
Character data based on the character set |
Maximum length 4G |
|
BFILE |
Binary data stored outside the database |
Maximum length 4G |
|
ROWID |
Unique line number recorded in the datasheet |
10bytes |
. ****.**** format,* is 0 or 1 |
Nrowid |
Unique line number recorded in a Binary data table |
Maximum length 4000bytes |
|
Number (P,s) |
Number Type |
P is the integer digit,S is the decimal digit |
|
DECIMAL (P,s) |
Number Type |
P is the integer digit,S is the decimal digit |
|
INTEGER |
Integer type |
A small integer |
|
FLOAT |
Floating-point type |
Number (+), double precision |
|
REAL |
Real type |
Number (+), higher accuracy |
Create syntax:
CREATE table [schema.] TableName
(column datatype [default expr] [Constaint],
....)
To view the table structure:
DESC TableName
Types of Oracle Tables
User-defined Tables
Data dictionary
A set of tables created automatically by Oracle
Contains the database's own information for managing and maintaining the database
About data dictionaries for tables:
User_catalog,all_catalog,dba_catalog,
User_tables,all_tables,dba_tables,
User_tables Self-built tables
All_tables Self-accessible tables
Dba_tables all tables in the database
How to clone a table
To clone an entire table:
CREATE TABLE EMP as SELECT * from Scott.emp;
Clone table Structure:
CREATE TABLE EMP2 as SELECT * from Scott.emp where 1=2;
How to view disk space consumed by a table:
Dba_extents,
Dba_segments
INSERT INTO EMP SELECT * from EMP;
Select Segment_name,sum (BYTES)/1024/1024
From dba_extents where segment_name= ' EMP ' and owner= ' SYS '
GROUP by Segment_name;
Selectowner,segment_name,segment_type,
tablespace_name,bytes/1024/1024
From dba_segments WHERE segment_name= ' EMP ';
Updating table information in a data dictionary
exec dbms_stats.gather_table_stats (' SCOTT ', ' TEST ');
To modify a table:
Add New Column
ALTER TABLE TableName
Add (column datatype [default expr]);
To delete a column:
ALTER TABLE TableName
Drop Column ColName
Column renaming:
ALTER TABLE TableName
Rename column colname to New_colname
Table renaming:
ALTER TABLE tablename Rename to New_tablename
Rename Test2 to test
Modify the data type, size, and default value of a column
ALTER TABLE TableName
Modify (column datatype [default values])
To mark a column as unavailable:
ALTER TABLE TableName
Set Unused (column)
Delete a table
DROP TABLE TableName
Truncate TABLE (delete all records, keep table structure)
TRUNCATE TABLE TableName
Table Plus comments
Comment on table tablename is ' ... Comment Content ... '
Column Plus Comment
Comment on column tablename.column is ' ... '
Data dictionaries related to annotations
All_col_comments
User_col_comments
All_tab_comments
User_tab_comments
Data dictionary about the table structure:
Dba_tab_columns
Using null values and default values
Implicit: Do not specify a field when inserting a record, do not specify a value
Display: Empty value field is null when inserting record, default field is filled
Reference: http://blog.sina.com.cn/s/blog_690d63f80100jiq4.html
Creating and managing tables with SQL statements in Oracle