1. 表空间
创建一个自动增长的表空间tp_orders
CREATE TABLESPACE tp_orders
DATAFILE 'E:\Data\WORKTBS01.DBF'
SIZE 10M AUTOEXTEND ON;
创建tp_hr表空间
--推荐创建表空间示例
CREATE TABLESPACE tp_hr
DATAFILE
'd:\data\tp_hr01.dbf' SIZE 60M;
--查看表空间
SELECTfile_name,tablespace_name,bytes,autoextensible
FROM dba_data_files
WHERE tablespace_name='TP_HR';
调整tp_hr表空间大小
--方法一:更改数据文件的大小
ALTER DATABASE DATAFILE
'D:\DATA\tp_hr01.dbf'
RESIZE 80M;
--方法二:向表空间内添加数据文件
ALTER TABLESPACE tp_hr
ADD DATAFILE
'E:\DATA\tp_hr02.DBF' SIZE 20M
AUTOEXTEND ON;
删除tp_hr表空间
DROP TABLESPACE tp_hr;
DROP TABLESPACE tp_hr INCLUDING CONTENTS;
小
小
2. 用户
创建用户A_oe
CREATE USER A_oe
IDENTIFIED BY bdqn
DEFAULT TABLESPACE tp_orders
TEMPORARY TABLESPACE TEMP;
将A_hr. employee表的访问权限授予A_oe用户
GRANT connect, resource TO A_oe; --授予CONNECT和RESOURCE两个角色
GRANT SELECT ON A_hr.employee TO A_oe;
创建A_hr用户
CREATE USER A_hr IDENTIFIED BY bdqn
DEFAULT TABLESPACE tp_hr
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON tp_hr
QUOTA 10M ON tp_bak
PASSWORD EXPIRE;
查询A_hr用户
--查询A_hr用户
SELECT *
FROM dba_users
WHERE username='A_HR';
查看表空间限额
SELECT *
FROM dba_ts_quotas
WHERE username='A_HR';
更改表空间中的用户限额
ALTER USER A_hr QUOTA 20M ON tp_bak;
删除用户
DROP USER A_hr CASCADE;
给户A_hr用户授权,回收权限
GRANT connect, resource TO A_hr; --授予CONNECT和RESOURCE两个角色
REVOKE connect, resource FROM A_hr; --撤销CONNECT和RESOURCE两个角色
GRANT SELECT ON SCOTT.emp TO A_hr; --允许用户查看 EMP 表中的记录
GRANT UPDATE ON SCOTT.emp TO A_hr; --允许用户更新 EMP 表中的记录
3. 序列
使用序列生成部门表中部门编号的值
CREATE SEQUENCE dept_seq
START WITH 60
INCREMENT BY 10
MAXVALUE 10000
NOCYCLE
CACHE 30;
INSERT INTO deptVALUES(dept_seq.nextval,'PERSONNEL','BEIJING');
INSERT INTO deptVALUES(dept_seq.nextval,'MARKET','SHANGHAI');
SELECT * FROM dept;
创建序列
CREATE TABLE Stock_Received
(
STOCK_ID VARCHAR2 (5),
STOCK_DATE DATE NOT NULL,
COST NUMBER (10)
)
CREATE SEQUENCE myseq
START WITH 1000
INCREMENT BY 10
MAXVALUE 1100
CYCLE
CACHE 30;
使用
INSERT INTO Stock_Received VALUES(myseq.nextVal,sysdate,1000);
小
小
小
4. 数据库迁移
数据库迁移前准备工作
DROP SEQUENCE dept_seq;
数据库迁移恢复工作
--1.恢复序列对象
CREATE SEQUENCE dept_seq
START WITH 80
INCREMENT BY 10
MAXVALUE 10000
NOCYCLE
CACHE 30;
--2.恢复部门表
CREATE TABLE deptBak AS SELECT * FROM dept;
--3.正常操作,插入数据
INSERT INTO deptVALUES(dept_seq.nextval,'LAB','LONDON');
5. 同义词
在A_hr模式下创建dept表的公有同义词 p_sy_dept
--使用SYSTEM用户登录,A_hr获得创建公有同义词权限
GRANT CREATE PUBLIC SYNONYM TO A_hr;
--在A_hr模式下创建公有同义词public_sy_dept作为A_hr用户dept表的别名
CREATE PUBLIC SYNONYM public_sy_dept FORdept;
--将查询dept的权限授予pulic角色。
GRANT SELECT ON dept TO PUBLIC;
--在A_oe模式下访问公有同义词
SELECT * FROM public_sy_dept;
创建同义词
--使用SYSTEM用户登录,A_hr获得创建公有同义词权限
GRANT CREATE PUBLIC SYNONYM TO A_hr;
--在A_hr模式下创建公有同义词p_Stock_Received作为A_hr用户Stock_Received表的别名
CREATE PUBLIC SYNONYM p_Stock_Received FORStock_Received;
--将查询dept的权限授予pulic角色。
GRANT SELECT ON Stock_Received TO PUBLIC;
--在A_oe模式下访问公有同义词
SELECT * FROM p_Stock_Received;
私有同义词
--使用SYSTEM用户登录,授予A_oe用户获得访问A_hr模式下的employee表的权限
GRANT SELECT ON A_hr.employee TO A_oe;
GRANT CREATE SYNONYM TO A_oe;
--连接A_oe用户
--创建同义词SY_EMP
CREATE SYNONYM SY_EMP FOR A_hr.employee;
--访问同义词
SELECT * FROM SY_EMP;
公有同义词
--使用SYSTEM用户登录,A_hr获得创建公有同义词权限
GRANT CREATE PUBLIC SYNONYM TO A_hr;
--在A_hr模式下创建公有同义词public_sy_emp作为A_hr用户employee表的别名
CREATE PUBLIC SYNONYM public_sy_emp FORemployee;
--在A_oe模式下访问公有同义词
SELECT * FROM public_sy_emp;
删除同义词
--删除私有同义词
DROP SYNONYM A_oe.sy_emp;
--删除公有同义词
DROP PUBLIC SYNONYM A_hr.public_sy_emp;
6. 索引
创建索引
CREATE UNIQUE INDEXindex_unique_customer_id ON customers(customer_id);
或者
CREATE INDEX index_reverse_customer_id ONcustomers(customer_id) REVERSE;
CREATE BITMAP INDEX index_bit_nls_territoryON customers(nls_territory);
CREATE INDEX index_ename ONcustomers(cust_first_name,cust_last_name);
创建唯一索引
CREATE UNIQUE INDEX index_unique_Stock_IDON Stock_Received (Stock_ID);
在玩具表中,需要标识列toyid作为标识,不需要有任何含义,可以作为主键
--创建toys表
CREATE TABLE toys(
toyid NUMBER NOT NULL,
toyname VARCHAR2(20),
toyprice NUMBER
);
--插入数据
INSERT INTO toys (toyid, toyname, toyprice)
VALUES (seq1.NEXTVAL, 'TWENTY', 25);
INSERT INTO toys (toyid, toyname, toyprice)
VALUES (seq1.NEXTVAL,'MAGIC PENCIL',75);
--查询数据
SELECT * FROM toys;
SELECT seq1.CURRVAL FROM dual;
--修改序列
ALTER SEQUENCE seq1
MAXVALUE 5000
CYCLE;
--删除序列
DROP SEQUENCE seq1;
--使用SYS_GUID函数
SELECT sys_guid() FROM dual;
在薪水级别(salgrade)表中,为级别编号(grade)列创建唯一索引
CREATE UNIQUE INDEX index_unique_grade ONsalgrade(grade);
在员工(employee)表中,为员工编号(empno)列创建反向键索引
CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;
在员工(employee)表中,为工种(job)列创建位图索引
CREATE BITMAP INDEX index_bit_job ONemployee(job);
在员工(employee)表中,为员工名称(ename)列创建大写函数索引
CREATE INDEX index_ename ON employee(UPPER(ename));
删除索引
DROP INDEX index_bit_job;
反向键索引更改为正常B树索引
ALTER INDEX index_reverse_empno REBUILDNOREVERSE;
将索引移到指定表空间
ALTER INDEX index_name REBUILD TABLESPACEtablespace_name;
7. 分区
范围分区
CREATE TABLE rangeOrders
(
order_id NUMBER(12)
,order_date DATE NOT NULL
,order_mode VARCHAR2(8)
,customer_id NUMBER(6) NOT NULL
,order_status NUMBER(2)
,order_total NUMBER(8,2)
,sales_rep_id NUMBER(6)
,promotion_id NUMBER(6)
)
PARTITION BY RANGE (order_date)
(
PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),
PARTITION Part2 VALUES LESS THAN(to_date('2006-01-01', 'yyyy-mm-dd')),
PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),
PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),
PARTITION Part5 VALUES LESS THAN (to_date('2009-01-01', 'yyyy-mm-dd'))
);
--要查看每一分区的数据
SELECT * FROM rangeOrderspartition(Part1);
SELECT * FROM rangeOrderspartition(Part2);
SELECT * FROM rangeOrderspartition(Part3);
SELECT * FROM rangeOrderspartition(Part4);
SELECT * FROM rangeOrderspartition(Part5);
--要查看在第三季度的数据
SELECT * FROM SALES1 partition(P3);
--要删除第三季度的数据
DELETE FROM SALES1 partition(P3);
--插入'2013/01/01'数据
insert into rangeOrders values(1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);
--报错后,修正
DROP TABLE rangeOrders;
CREATE TABLE rangeOrders
(
order_id NUMBER(12)
,order_date DATE NOT NULL
,order_mode VARCHAR2(8)
,customer_id NUMBER(6) NOT NULL
,order_status NUMBER(2)
,order_total NUMBER(8,2)
,sales_rep_id NUMBER(6)
,promotion_id NUMBER(6)
)
PARTITION BY RANGE (order_date)
(
PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),
PARTITION Part2 VALUES LESS THAN (to_date('2006-01-01', 'yyyy-mm-dd')),
PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),
PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),
PARTITION Part5 VALUES LESS THAN (MAXVALUE)
);
--要删除第三季度的数据
DELETE FROM rangeOrders partition(Part3);
利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表
--1.利用现有表Orders创建间隔分区表intervalOrders
CREATE TABLE intervalOrders
PARTITIONBY RANGE(order_date)
INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
(PARTITIONP1 VALUES LESS THAN (to_date('2005-01-01','yyyy/mm/dd')))
AS SELECT * FROM Orders;
--2.查询分区情况
SELECTtable_name,partition_name
FROMuser_tab_partitions
WHERE table_name=UPPER('intervalOrders');
--3.向表插入'2013/01/01'数据
insert into intervalOrders values(1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);
--插入数据
INSERTINTO sales2 VALUES (1,'a',to_date('2013-08-1'),10,'1');
--获得分区情况
SELECTtable_name,partition_name
FROMuser_tab_partitions
WHERE table_name=UPPER('sales2');
--查询输出结果,系统自动根据输入数据情况创建新分区“SYS_P82”
TABLE_NAME PARTITION_NAME
----------------------------
SALES2 P1
SALES2 SYS_P82
--查询分区数据
SELECT* FROM sales2 PARTITION(sys_P82);
--4.再次查询某一分区数据
创建范围分区表
CREATE TABLE Stock_Received
(
STOCK_ID VARCHAR2 (5),
STOCK_DATE DATE NOT NULL,
COST NUMBER (10)
)
PARTITION BY RANGE (STOCK_DATE)
(
PARTITION S1 VALUES LESS THAN (TO_DATE ('01/04/2001', 'DD/MM/YYYY')),
PARTITION S2 VALUES LESS THAN (TO_DATE ('01/07/2001', 'DD/MM/YYYY')),
PARTITION S3 VALUES LESS THAN (MAXVALUE)
);
利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表
/*准备工作*/
--1.创建普通SALES表
CREATE TABLE SALES
(
SALES_ID NUMBER,
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE NOT NULL
);
--2.自行向SALES表插入数据
/*实施步骤*/
--1.创建间隔分区表SALES3
CREATE TABLE SALES3
PARTITIONBY RANGE(SALES_DATE)
INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
(PARTITIONP1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')))
AS SELECT * FROM SALES; --SALES表为已经创建的表
--2.查询分区情况
SELECTtable_name,partition_name
FROMuser_tab_partitions
WHERE table_name=UPPER('sales3');
--3.查询某一分区数据
--4.自行向SALES3表插入数据
--5.再次查询某一分区数据