Oracle442個應用場景-----------數據庫邏輯對象管理應用場景

Oracle442個應用場景-----------數據庫邏輯對象管理應用場景

應用場景128：創建臨時表

創建一個保存臨時選擇商品信息的臨時表temp_goods

CREATE GLOBAL TEMPORARY TABLE temp_goods
(GoodsId NUMBER,
GoodsNum NUMBER,
Price NUMBER)
ON COMMIT DELETE ROWS;

創建一個臨時表空間

CREATE TEMPORARY TABLESPACE tbs_t1
TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

在表空間tb_t1中創建temp_goods1；

CREATE GLOBAL TEMPORARY TABLE temp_goods1
(GoodsId NUMBER,
GoodsNum NUMBER,
Price NUMBER)
ON COMMIT DELETE ROWS
TABLESPACE tbs_t1;

應用場景129：查看表信息的系統視圖

COL TABLE_NAME FORMAT A10
COL COLUMN_NAME FORMAT A10
COL DATA_TYPE FORMAT A10
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES';

應用場景130：在Enterprise Manager中修改表

應用場景131使用DESC語句查看表結果

查看表employee結果的語句如下：

DESC SYSTEM.Employee

使用場景132:使用ALTER TABLE語句向表中添加列

ALTER TABLE SYSTEM.Departments ADD (Description VARCHAR2(1000));

應用場景133：使用ALTER TABLE語句修改列名

ALTER TABLE SYSTEM.Departments
RENAME COLUMN Description TO Descript;

應用場景134：使用ALTER TABLE語句刪除隊列

ALTER TABLE HRMAN.Departments
RENAME COLUMN Description TO Descript;

應用場景136：創建和驗證主鍵約束

ALTER TABLE HRMAN.Departments ADD (Description varchar2(1000));
DESC HRMAN.Departments;
ALTER TABLE HRMAN.Departments
SET UNUSED (Description);
DESC HRMAN.Departments;
刪除表中所有不可用列：
ALTER TABLE HRMAN.Departments
DROP UNUSED COLUMNS;

應用場景137：創建和驗證非空約束

創建表user2並指定username列和userpwd列為非空約束

CREATE TABLE HRMAN.Users2
(UserId NUMBER,
UserName VARCHAR2(40) NOT NULL,
UserPwd VARCHAR2(40) NOT NULL,
CONSTRAINT PK_USERID PRIMARY KEY(UserId)
);

將約束中的username列設置為 not null

ALTER TABLE HRMAN.Users MODIFY UserName NOT NULL;

使用下面的語句可以將列設置為空:

ALTER TABLE HRMAN.Users MODIFY UserName NULL;

向表user2中插入數據器username值為null：

INSERT INTO HRMAN.Users2 (UserId, UserPwd) VALUES(1,'123456');

應用場景138：創建驗證唯一約束

在uername上建立唯一約束：

CREATE TABLE Users3
(UserId Number Primary Key,
UserName Varchar2(40) NOT NULL UNIQUE,
UserPwd Varchar2(40) NOT NULL
);
創建user4，其結構與表user完全相同：
CREATE TABLE HRMAN.Users4
(UserId NUMBER PRIMARY KEY,
UserName VRCHAR2(40),
UserPwd VARCHAR2(40),
CONSTRAINT UK_USERNAME UNIQUE(UserName)
);

將表user的username列設置唯一約束：
ALTER TABLE HRMAN. Users
ADD CONSTRAINT UK_USERNAME1
UNIQUE(UserName);

使用insert向表user中插入兩條記錄使他們列值是重復的：username=test-》

INSERT INTO HRMAN.Users VALUES(100, 'test', 'test');
INSERT INTO HRMAN.Users VALUES(101, 'test', '1234');

應用場景139：創建驗證檢查約束

在username列上定義檢查約束：
CREATE TABLE HRMAN.Users5
(UserId number PRIMARY KEY,
UserName varchar2(40),
UserPwd varchar2(40)
CONSTRAINT CK_USERPWD CHECK(LENGTH(UserPwd)>=6)
);

使用alter創建檢查約束：

ALTER TABLE HRMAN. Users
ADD CONSTRAINT CK_USERPWD1 CHECK(LENGTH(UserPwd)>=6);

使用insert插入記錄是userpwd的列值得長度小於6：

INSERT INTO HRMAN.Users VALUES(102, 'user', 'pwd');

應用場景140:創建和驗證外鍵約束

為表departments的dep_id列和表employee的dep_id列創建外鍵約束fk_emp_depid：、

ALTER TABLE HRMAN.Employees
ADD CONSTRAINT FK_EMP_DEPID
FOREIGN KEY(Dep_id) REFERENCES HRMAN.Departments(Dep_id);

向表中插入一條記錄：

INSERT INTO HRMAN.Employees (Emp_id, Emp_name, Dep_id)
VALUES (1, 'Johney', 3);

應用場景141:設置DEFAULT列的屬性

創建表users6，設置userpwd的默認值為：11111：

CREATE TABLE HRMAN.Users6
(UserId Number Primary Key,
UserName Varchar2(40) NOT NULL UNIQUE,
UserPwd Varchar2(40) DEFAULT('111111')
);

向表中插入一條記錄：

INSERT INTO HRMAN.Users6 (UserId, UserName) VALUES(1, 'user');

應用場景142：刪除表

DROP TABLE HRMAN.USERS;

應用場景143：在Enterprise Manager中查看和管理視圖

應用場景144：在Enterprise Manager中創建視圖

SELECT e.Emp_Id, e.Emp_Name, d.Dep_Name
FROM HRMAN.Employees e, HRMAN.Departments d
WHERE e.Dep_Id=d.Dep_Id

應用場景145：使用create view

創建視圖v_emp1

CREATE VIEW HRMAN.V_EMP
AS
SELECT e.Emp_Id, e.Emp_Name, d.Dep_Name
FROM HRMAN.Employees e, HRMAN.Departments d
WHERE e.Dep_Id=d.Dep_Id;

查看用戶hrman的視圖以及其定義的文本信息：

SELECT VIEW_NAME, TEXT FROM DBA_VIEWS
WHERE OWNER='HRMAN';

應用場景146:修改視圖

修改視圖hrman.v_emp1:

CREATE OR REPLACE VIEW HRMAN.V_EMP
AS
SELECT e.Emp_Id, e.Emp_Name, e.Sex, e.Wage, d.Dep_Name
FROM HRMAN.Employees e, HRMAN.Departments d
WHERE e.Dep_Id=d.Dep_Id;

應用場景147：刪除視圖

DROP view HR.v_emp1;

應用場景148：在Enterprise Manager中的索引管理頁面

應用場景149：在Enterprise Manager中創建索引

應用場景150：

使用表空間users保存索引：

CREATE INDEX HRMAN.IX_EMPNAME ON HRMAN.EMPLOYEES(EMP_NAME)
TABLESPACE Users;

為表HRMAN。Enmployees的列IDCard創建唯一索引：

CREATE INDEX HRMAN. UQ_IDCARD ON HRMAN.EMPLOYEES(IDCARD)
TABLESPACE Users;

查看hrman所擁有的視圖及其定義文本信息：

SELECT INDEX_NAME, TABLE_NAME FROM DBA_INDEXES
WHERE OWNER='HRMAN';

應用場景151：修改索引

ALTER INDEX HRMAN.IX_EMPNAME UNUSABLE;

ALTER INDEX HRMAN.IX_EMPNAME REBUILD;

ALTER INDEX HRMAN.IX_EMPNAME RENAME TO IX_EMPNAME_1;

應用場景152：刪除索引

DROP INDEX HRMAN.IX_EMPNAME_1;

應用場景153：在Enterprise Manager中查看和管理實體化視圖

應用場景154：在Enterprise Manager中創建視圖

應用場景155：使用CREATE MATERIALIZED VIEW 語句創建實體化視圖

創建實體化mv_emp1：

CREATE MATERIALIZED VIEW HRMAN.MV_EMP1
REFRESH FORCE
ON DEMAND
AS
SELECT e.Emp_Id, e.Emp_Name, d.Dep_Name
FROM HRMAN.Employees e, HRMAN.Departments d
WHERE e.Dep_Id=d.Dep_Id;

應用場景156：修改視圖

將刷新類型設置為CONPLETE:
ALTER MATERIALIZED VIEW HRMAN.MV_EMP
REFRESH COMPLETE
ON DEMAND;

應用場景157：刪除實體化視圖

DROP MATERIALIZED VIEW HRMAN.MV_EMP;

 

應用場景158：創建簇

CREATE CLUSTER HRMAN.HrCluster (dep_id NUMBER)
PCTUSED 80
PCTFREE 5
SIZE 500
TABLESPACE users
STORAGE (INITIAL 200K
NEXT 300K
MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 33);

應用場景159：創建簇表

在簇HRMAN.HrCluster中創建DeptInfo：

CREATE TABLE HRMAN.DeptInfo
(
Dep_id NUMBER PRIMARY KEY,
Dep_name VARCHAR2(100) NOT NULL
)
CLUSTER HRMAN.HrCluster(Dep_id);

在HRMAN.HrCluster中創建表EmpInfo：

CREATE TABLE HRMAN.EmpInfo
(
Emp_id NUMBER PRIMARY KEY,
Emp_name VARCHAR2(50) NOT NULL,
Sex VARCHAR2(2),
Title VARCHAR2(50),
Wage NUMBER(8, 2),
IdCard VARCHAR2(20),
Dep_id NUMBER
)
CLUSTER HRMAN.HrCluster(Dep_id);

應用場景160：使用DBA_CLUSTERS視圖查看簇的信息

查看HRMAN中的簇表列信息：

COL CLUSTER_NAME FORMAT A20
COL OWNER FORMAT A20
COL TABLESPACE_NAME FORMAT A20
SELECT CLUSTER_NAME, OWNER, TABLESPACE_NAME, CLUSTER_TYPE FROM DBA_CLUSTERS;

應用場景161：使用DBA_CLU_COLUMNS視圖查看簇表的列信息

COL OWNER FORMAT A10
COL CLUSTER_NAME FORMAT A15
COL CLU_COLUMN_NAME FORMAT A10
COL TABLE_NAME FORMAT A10
COL TAB_COLUMN_NAME FORMAT A15
SELECT * FROM DBA_CLU_COLUMNS
WHERE OWNER='HRMAN';

應用場景162：創建簇索引

在村HRMAN.HrCluster上創建簇索引：

CREATE INDEX HRMAN.IX_EMP_DEPT
ON CLUSTER HRMAN.HrCluster
TABLESPACE users
STORAGE (INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33);

應用場景163：修改簇

ALTER CLUSTER HRMAN.HrCluster
PCTUSED 60
PCTFREE 30;

應用場景164：刪除簇

DROP CLUSTER HRMAN.HrCluster
INCLUDING TABLES
CASCADE CONSTRAINTS;

應用場景165：創建散列簇

在HRMAN方案上創建散列簇HashCluster：

CREATE TABLE HRMAN.Area
(
AreaId NUMBER(5, 0) PRIMARY KEY,
AreaName VARCHAR2(200),
UpperId NUMBER(5, 0)
)
CLUSTER HRMAN.HashCluster(AreaId);

在散列簇上創建表area用於保存地域信息

CREATE CLUSTER STCluster (Id NUMBER)
SIZE 512 SINGLE TABLE HASHKEYS 500;

應用場景166：控制散列簇的空間使用

應用場景167：估算散列簇所需要的空間大小

應用場景168：修改和刪除散列簇

應用場景169：使用DBA_CLUSTER_HASH_EXPRESSIONS視圖查看散列函數

SELECT * FROM DBA_CLUSTER_HASH_EXPRESSIONS;

應用場景170：創建序列

CREATE SEQUENCE HRMAN.EMP_S
MINVALUE 1
NOMAXVALUE
START WITH 1
INCREMENT BY 1
NOCYCLE
CACHE 20;

應用場景171：修改序列

ALTER SEQUENCE HRMAN.EMP_S
MAXVALUE 10000;

應用場景172：刪除序列

DROP SEQUENCE HRMAN.EMP_S;

應用場景173：序列的使用

CREATE SEQUENCE HRMAN.USER_S
MINVALUE 1
NOMAXVALUE
START WITH 1
INCREMENT BY 1
NOCYCLE
CACHE 20;

INSERT INTO HRMAN.USERS VALUES(HRMAN.USER_S.NEXTVAL, 'admin', 'pass');

應用場景174：創建同義詞

CREATE PUBLIC SYNONYM HrEmployees FOR HRMAN.Employees;

應用場景175：使用系統視圖DBA_SYNONYMS查看同義詞的信息

COL OWNER FORMAT A10
COL SYNONYM_NAME FORMAT A10
COL TABLE_OWNER FORMAT A10
COL TABLE_NAME FORMAT A10
COL DB_LINK FORMAT A10
SELECT * FROM DBA_SYNONYMS
WHERE TABLE_NAME='EMPLOYEES';

應用場景176：在DML語句中使用同義詞

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A10
SELECT Emp_name, Title, Wage FROM HrEmployees;

應用場景177：刪除同義詞

DROP PUBLIC SYNONYM HrEmployees;