7数据库逻辑对象管理

创建普通表：(在方案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;