创建普通表：(在方案HR中)
SYS AS SYSDBA@ORCL>CREATE TABLE HR.GMY(
2 EMP_ID NUMBER,
3 EMP_NAME VARCHAR2(50) NOT NULL,
4 SEX VARCHAR2(2),
5 TITLE VARCHAR2(50),
6 WAGE NUMBER(8,2),
7 IDCARD VARCHAR2(20),
8 DEP_ID NUMBER
9 );
创建临时表(默认保存在默认临时表空间中)
ON COMMIT DELETE ROWS:创建一个事务级临时表，每次提交事务后，将删除表中的所有数据
ON COMMIT PRESERVER ROWS:创建一个会话级临时表，当会话结束后，将删除表中的所有数据
SYS AS SYSDBA@ORCL>CREATE GLOBAL TEMPORARY TABLE TEMP_GOODS
2 (GOODSLD NUMBER,
3 GOODNUM NUMBER,
4 PRIVE NUMBER)
5 ON COMMIT DELETE ROWS;
创建临时表空间
CREATE TEMPORARY TABLESPACE TBS_T1
TEMPFILE ‘TBS_T1.DBF‘ SIZE 50M REUSE AUTOEXTEND ON
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
与表相关的系统视图：
显示数据库中所有关系表的信息
DBA_TABLES
ALL_TABLES
USER_TABLES
查看表的索引信息
dba_indexes
all_indexes
user_indexes
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER=‘HR‘;
显示表，视图和簇中的列的信息
DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS
显示表和视图的注释信息
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
显示表和视图中列的注释信息
DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS
显示数据库中外部表的属性
DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
显示外部表的数据源
DBA_EXTERNAL_LOCATIONS
ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS
显示表和视图的直方图信息
DBA_TAB_HISTOGRAMS
ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS
显示表的优化统计信息
DBA_TAB_STATISTICS
ALL_TAB_STATISTICS
USER_TAB_STATISTICS
显示表和视图中列的统计和直方图信息
DBA_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS
显示从最后一次表的统计信息收集完成后，表中发生的修改操作
DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS
显示所有加密列的信息和加密算法
DBA_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS
USER_ENCRYPTED_COLUMNS
显示表中所有未使用的列
DBA_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS
显示表中部分完成的DROP COLUMNR操作值
DBA_PARTIAL_DROP_TABS
ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS
使用ALTER TABLE 语句向表中添加列
ALTER TABLE HR.RSC ADD (DESCRIPTION VARCHAR2(100));
使用ALTER TABLE语句修改列名
ALTER TABLE HR.RSC RENAME COLUMN DESCRIPTION TO DESCRIPT;
使用ALTER TABLE 语句删除列
ALTER TABLE HR.RSC DROP COLUMN DESCRIPT;
使用ALTER TABLE将列设置为不可用
ALTER TABLE HR.RSC SET UNUSED(DESCRIPTION);
删除表的所有的不可用的列
ALTER TABLE HR.RSC DROP UNUSED COLUMNS;
创建和验证主键约束(列值不允许存在重复的记录)
SYS AS SYSDBA@ORCL>CREATE TABLE HR.USERS
2 (USERID NUMBER PRIMARY KEY,
3 USERNAME VARCHAR2(40),
4 USERPWD VARCHAR2(40));
SYS AS SYSDBA@ORCL>CREATE TABLE HR.USERS1
2 (USERID NUMBER,
3 USERPWD VARCHAR2(40),
4 CONSTRAINT PK_USERID PRIMARY KEY(USERID));
SYS AS SYSDBA@ORCL>ALTER TABLE HR.USER2
2 ADD CONSTRAINT USERID_PK PRIMARY KEY(USERID);
创建和验证非空约束(列值不按受空值)
ALTER TABLE HR.USERS MODIFY USERID NOT NULL;
创建和验证惟一约束(一个表只能存在一个主键，如果其它列也具有惟一性，可以使用UNIQUE进行约束)
SYS AS SYSDBA@ORCL>ALTER TABLE HR.USERS
2 ADD CONSTRAINT USERPWD_UNI UNIQUE(USERPWD);
SYS AS SYSDBA@ORCL>CREATE TABLE HR.USER1
2 (USERID NUMBER PRIMARY KEY,
3 USERNAME VARCHAR2(20),
4 USERPWD VARCHAR2(20) NOT NULL UNIQUE,
5 CONSTRAINT UK_USERNAME UNIQUE(USERNAME));
创建和验证检查约束(通过函数对字段的属性进行约束)
SYS AS SYSDBA@ORCL>CREATE TABLE HR.USER1
2 (USERID NUMBER PRIMARY KEY,
3 USERNAME VARCHAR2(40),
4 USERPWD VARCHAR2(40),
5 CONSTRAINT CK_USERPWD CHECK(LENGTH(USERPWD)>=6));
创建和验证外键约束(用于建立两个表之间的连接关系,主表列一定要是主键约束或唯一约束)
SYS AS SYSDBA@ORCL>ALTER TABLE HR.USER1
2 ADD CONSTRAINT USERID
3 FOREIGN KEY(USERID) REFERENCES HR.USER2(USERID);
设置列的DEFAULT属性
SYS AS SYSDBA@ORCL>CREATE TABLE HR.USER3
2 (USERID NUMBER PRIMARY KEY,
3 USERNAME VARCHAR2(40) NOT NULL UNIQUE,
4 USERPWD VARCHAR2(40) DEFAULT(‘123456‘));
删除表：
DROP TABLE HR.USERS;
创建视图：
SYS AS SYSDBA@ORCL>CREATE VIEW HR.EMP_DEP
AS
SELECT E.EMPLOYEE_ID,E.FIRST_NAME,D.DEPARTMENT_NAME FROM HR.EMPLOYEES E,HR.DEPARTMENTS D WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;
查看已创建的视图：
SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE OWNER=‘HR‘;
SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE OWNER=‘HR‘ AND VIEW_NAME=‘EMP_DEP‘;
修改视图可以在OEM中修改SQL语句
删除视图：
DROP VIEW HR.EMP_DEP
创建索引:
CREATE [UNIQUE|BITMAP] INDEX [<SCHEMA_NAME>.] <INDEX_NAME>
ON [SCHEMA_NAME.] <TABLE_NAME>[TABLE_COLUMN]
(<COLUMN>[ASC|DESC])<INDEX_SUB><INDEX_attribute>
INDEX_SUB:可以指定保存索引的表空间
LOCAL STORE IN (<TABLE_SPACE>)
INDEX_ATTRIBUTE:指定索引的排序，压缩，联机状态等属性
NOSORT|SORT
REVERSE
COMPRESS<INT>|NOCOMPRESS
LOGGING|NOLOGGING
ONLINE
创建索引：
CREATE INDEX HR.GMY_EMP_NAME_IX ON HR.GMY(EMP_NAME);
CREATE UNIQUE INDEX HR.DEP_ID_UNI ON HR.GMY(DEP_ID);
查看索引信息
SELECT INDEX_NAME,TABLE_NAME FROM DBA_INDEXES WHERE OWNER=‘HR‘;
设置指定的索引不可用：
ALTER INDEX HR.GMY_EMP_NAME_IX UNUSABLE;
设置启用指定的索引：
ALTER INDEX HR.GMY_EMP_NAME_IX REBUILD;
重命名索引:
ALTER INDEX HR.GMY_EMP_NAME_IX RENAME TO RSC_NAME_IX; // TO后面不用加 方案名
删除索引：
DROP INDEX HR.RSC_NAME_IX;
实体化视图：
创建实体化视图
CREATE MATERIALIZED VIEW <name>
FAST|COMPLETE|FORCE
ON [DEMAND|COMMIT]
WITH {PRIMARY KEY | ROWID}
[USING INDEX <STORAGE_OPTION>]
[REFRESH<REFRESH_OPTION>|NEVER REFERSH]
[FORUPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
AS
SELECT statement
SYS AS SYSDBA@ORCL>CREATE MATERIALIZED VIEW HR.MV_EMP1
2 REFRESH FORCE
3 ON DEMAND
4 AS
5 SELECT * FROM EMPLOYEES;
Materialized view created.
查看实体化视图：
SYS AS SYSDBA@ORCL>SELECT MVIEW_NAME FROM DBA_MVIEWS WHERE OWNER=‘HR‘;
MVIEW_NAME
------------------------------
MV
修改实体化视图的定义
ALTER MATERIALIZED VIEW HR.MV_EMP1
REFRESH COMPLETE
ON DEMAND
删除实体化视图:
DROP MATERIALIZED VIEW HR.MV_EMP1;
ORACLE 簇
创建簇：
CREATE CLUSTER [<方案名>.]<簇名>(<键列1><数据类型>,<键列1><数据类型>)
PCTUSED <INT>
PCTFREE <INT>
INITRANS <INT>
MAXTRANS <INT>
SIZE <INT> K|M
TABLESPACE <表空间名>
STORAGE 子句
创建簇：
SYS AS SYSDBA@ORCL>CREATE CLUSTER HR.HRUSTER(DEP_ID NUMBER)
2 PCTUSED 80
3 PCTFREE 5
4 SIZE 500
5 TABLESPACE USERS
6 STORAGE(
7 INITIAL 200K
8 NEXT 300K
9 MINEXTENTS 2
10 MAXEXTENTS UNLIMITED
11 PCTINCREASE 33);
创建簇表：
SYS AS SYSDBA@ORCL>CREATE TABLE HR.DEPTINFO(
2 DEP_ID NUMBER PRIMARY KEY,
3 DEP_NAME VARCHAR2(100) NOT NULL
4 )
5 CLUSTER HR.HRUSTER(DEP_ID);
SYS AS SYSDBA@ORCL>CREATE TABLE HR.EMPINFO
2 (
3 EMP_ID NUMBER PRIMARY KEY,
4 EMP_NAME VARCHAR2(50) NOT NULL,
5 SEX VARCHAR2(2),
6 TITLE VARCHAR2(50),
7 WAGE NUMBER(8,2),
8 IDCARD VARCHAR2(20),
9 DEP_ID NUMBER)
10 CLUSTER HR.HRUSTER(DEP_ID);
使用DBA_CLUSTERS视图查看簇信息
SYS AS SYSDBA@ORCL>SELECT CLUSTER_NAME,OWNER,TABLESPACE_NAME,CLUSTER_TYPE FROM DBA_CLUSTERS;
CLUSTER_NAME OWNER TABLESPACE_NAME CLUST
------------------------------ ------------------------------ ------------------------------ -----
SMON_SCN_TO_TIME_AUX SYS SYSAUX INDEX
C_COBJ# SYS SYSTEM INDEX
C_FILE#_BLOCK# SYS SYSTEM INDEX
C_MLOG# SYS SYSTEM INDEX
C_OBJ# SYS SYSTEM INDEX
C_OBJ#_INTCOL# SYS SYSTEM INDEX
C_RG# SYS SYSTEM INDEX
C_TOID_VERSION# SYS SYSTEM INDEX
C_TS# SYS SYSTEM INDEX
C_USER# SYS SYSTEM INDEX
HRUSTER HR USERS INDEX
使用DBA_CLU_COLUMNS视图查看簇表的列信息
SELECT * FROM DBA_CLU_COLUMNS WHERE OWNER=‘HR‘;
创建簇索引：(创建时表不能有任何数据)
SYS AS SYSDBA@ORCL>CREATE INDEX HR.EMP_DEP_IX
2 ON CLUSTER HR.HRUSTER
3 TABLESPACE USERS
4 STORAGE
5 (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 10 PCTINCREASE 33);
修改簇：
ALTER CLUSTER|<方案名>｜<簇名>
SIZE <INT> K|M|G
NEXT <INT> K|M|G
MINEXTENTS <INT>
MAXEXTENTS <INT>
PCTUSED <INT>
PCTFREE <INT>
INITRANS <INT>
MAXTRANS <INT>
TABLESPACE <TABLESPACE_NAME>
STORAGE <sentence>
SYS AS SYSDBA@ORCL>ALTER CLUSTER HR.HRUSTER
2 PCTUSED 80
3 PCTFREE 20;
Cluster altered.
删除簇：
SYS AS SYSDBA@ORCL>DROP CLUSTER HR.HRUSTER
2 INCLUDING TABLES
3 CASCADE CONSTRAINTS;
Cluster dropped.
创建散列簇
CREATE CLUSTER |<SCHEMA_NAME>|<CLUSTER_NAME>(<KEY_COLUMN><DATA_TYPE>,...)
TABLESPACE <TABLESPACE_NAME>
PCTUSED <INT>
PCTFREE <INT>
INITRANS <INT>
MAXTRANS <INT>
SIZE <INT> K|M
STORAGE clause
HASH IS <CLUSTER_KEY_COLUMN> HASHKEYS <HASH_VALUE_MAX_NUM>
SYS AS SYSDBA@ORCL>CREATE CLUSTER HR.HASHCLUSTER(AREAID NUMBER(5,0))
2 TABLESPACE USERS
3 STORAGE(
4 INITIAL 200K
5 NEXT 50K
6 MINEXTENTS 2
7 MAXEXTENTS UNLIMITED
8 PCTINCREASE 33)
9 HASH IS AREAID HASHKEYS 200;
在散列值中创建表：
SYS AS SYSDBA@ORCL>CREATE TABLE HR.HATB_AREA
2 (
3 AREAID NUMBER(5,0) PRIMARY KEY,
4 AREANAME VARCHAR2(200),
5 UPPERLD NUMBER(5,0)
6 )
7 CLUSTER HR.HASHCLUSTER(AREAID);
Table created.
使用DBA_CLUSTER_HASH_EXPRESSIONS查看散列函数
SELECT * FROM DBA_CLUSTER_HASH_EXPRESSIONS;
创建序列
SYS AS SYSDBA@ORCL>CREATE SEQUENCE HR.GMY_SEQ
2 MINVALUE 1
3 NOMAXVALUE
4 START WITH 1
5 INCREMENT BY 1
6 NOCYCLE
7 CACHE 20;
修改序列：
SYS AS SYSDBA@ORCL>ALTER SEQUENCE HR.RSC_SEQ
2 MAXVALUE 100000;
序列的使用：
获取最新的序列号：SEQ_NAME.NEXTVAL
SYS AS SYSDBA@ORCL>INSERT INTO HR.RSC VALUES(HR.RSC_SEQ.NEXTVAL,‘RSC‘);
SYS AS SYSDBA@ORCL>SELECT * from hr.rsc;
RSC_ID RSC_NAME
---------- --------------------
2 RSC
创建同义词：
CREATE [PUBLIC] SYNONYM [<方案名>.]<同义词> FOR [<方案名>.]<对象名称> [@<数据库链名>]
SYS AS SYSDBA@ORCL>CREATE SYNONYM HR.HRGMY FOR HR.GMY;
创建公共同义词:
CREATE PUBLIC SYNONYM HRGMY FOR HR.GMY;
使用系统视图DBA_SYNONYMS 查看同义词的信息(USER_SYNONYMS,ALL_SYNONYMS)
SYS AS SYSDBA@ORCL>desc DBA_SYNONYMS;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER NOT NULL VARCHAR2(30)
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
删除同义词：
DROP SYNONYM HR.HRRSC;
DROP PUBLIC SYNONYM HRGMY;
原文：http://rscpass.blog.51cto.com/771159/1612743