【笔记】Oracle SQL语句 | 进阶篇

之前整理了Oracle SQL基本语句，主要针对Oracle的初学者：

【笔记】Oracle SQL语句 | 基础篇

随着学习的深入和工作需求的提高，需要从会用会写进阶到会管理会维护，因此整理了SQL语句进阶篇。这部分的学习一个是要跟Oracle体系的知识点结合以来一起学，明白语句背后的原理，另一个是要在具体的管理维护中多实践多操作，才能熟练。

  

----------1. 用户管理/权限-------------
------1.1 用户管理
---创建
create user mr 
identified by mrsoft  --externally/globally as 'CN=user'
default tablespace users
temporary tablespace temp
quota 10m on tbsp_1

---修改
alter user east quota 20m on tbsp_1;
alter user east identified by 123456;  --密码
alter user SH account unlock;  --解锁

---删除
drop user df cascade;

------1.2 用户权限
---授权
--系统
grant connect,resource to east;
grant create session,create table to dongfang with admin option; --再授权
--对象
grant select,insert,delete on scott.emp to xifang;

---回收
revoke resource to east;
revoke delete on scott.emp from xifang;

---查询
--DBA_USERS  用户基本信息表
--DBA_SYS_PRIVS  系统权限
--DBA_TAB_PRIVS  对象权限
--USER_SYS_PRIVS  用户系统权限
--ROLE_SYS_PRIVS  用户角色
--ALL_TABLES  可以查询的基本信息
--USER_TAB_PRIVS  用户将权限授予哪些用户
--ALL_TAB_PRIVS  哪些用户给自己授权
select * from USER_SYS_PRIVS;

------1.3 用户角色
---预定义
--connect/resource/dba/exp_full_database/imp_full_database

---创建
create role designer identified by 123456;
grant create view,create table to designer;

---授予
grant designer to dongfang;

---管理
select * from role_sys_privs where role = 'DESIGNER';
alter role designer not identified;
alter role designer identified by mrsoft;
set role designer;  --生效  indentified by mrsoft;

---删除
drop role designer;

------1.4 资源配置PROFILE
---管理密码
create profile lock_account limit
failed_login_attempts 5
password_lock_time 7;  
--password_life_time/password_grace_time
--password_reuse_time/password_reuse_max
--password_verify_function

alter user dongfang profile lock_account;

---管理资源
alter system set resource_limit = true;

alter profile password_lift_time limit
cpu_per_session 20000
sessions_per_user 10
cpu_per_call 500
password_life_time 180
failed_login_attempts 10;

---删除
drop profile password_life_time cascade;

---查询
select profile from dba_users where username = 'SCOTT';
select resource_name,resource_type,limit from dba_profiles 
where profile = 'DEFAULT';


----------2. 数据对象-------------
------2.1 数据表
---创建
create table students(
  stuno number(10) not null,  --非空约束
  stuname varchar2(8),
  id varchar2(18) constraint ID_UK unique,  --唯一性约束
  sex char(2),
  age int constraint AGE_CK check(age > 0 and age < 120) disable,  --禁用
  departno varchar2(2) not null,
  classno varchar2(4) not null,
  regdate date default sysdate,
  ---blob/clob/bfile
  constraint STU_PK primary key(stuno)  ---主键约束
)tablespace tbsp_1  --表空间
storage(initial 256k)  --存储参数next/minextents(AUTOALLOCATE)
pctfree 20  --数据块最小空闲空间比例，达到后标记不可用
pctused 40  --数据库是否可用界限
initrans 10  --允许并发事务数目
nologging;  --DDL操作不产生日志

---维护
--字段
alter table students add(province varchar2(10));
alter table students drop column province;
alter table students drop (sex,age);
alter table students modify departno varchar2(4);
--重命名
alter table students rename to students_bak;
--表空间
alter table students move tablespace tbsp_2;
--存储参数
alter table students pctfree 25 pctused 45;
---删除
drop table students cascade constraints;  --同时删除视图，约束或触发器等
flashback table students to before drop;  --闪回
--状态
alter table students read only;  --read write
--约束
--
alter table students modify stuno not null;  ---null
--
alter table students add constraint STUD_PK(stuno);
alter table students drop constraint STUD_PK;
--
alter table students add constraint IDs_UK unique(id);
alter table students drop constraint IDs_UK;
--
alter table students add constraint DEPART_FK foreign key(department_id)
      reference departments(department_id);  --外键约束
alter table students drop constraint depart_FK;
--
alter table students enable validate constraint depart_FK;  --novalidate
alter table students disable constraint depart_FK;


------2.2 索引
---创建
create index emp_deptno_index ---bitmap index
on emp(deptno)  --emp(lower(job))
pctfree 25  --reverse
tablespace users;

---合并
alter index emp_deptno_index coalesce deallocate unused;

---重建
alter index emp_deptno_index rebuild;

---删除
drop index emp_job_fun;

---查询
select table_name,index_name,index_type from dba_indexes 
where owner = 'HR';  --表索引 sys
select column_name,column_length from user_ind_columns 
where index_name = 'EMP_DEPTNO_INDEX';  --索引列 scott
select tablespace_name,segment_type,bytes from user_segments
where segment_name = 'EMP_DEPTNO_INDEX';  --索引段 scott
select column_expression from user_ind_expressions
where index_name = 'EMP_JOB_FUN';  --函数索引

------2.3 视图
---创建
create or replace view emp_view as
  select d.dname,d.loc,e.empno,e.ename
  from emp e, dept d
  where e.deptno = d.deptno and d.deptno = 20
with read only;

---查看
select * from emp_view;
desc emp_view;

---重编译
alter view emp_view compile;

---删除
drop view emp_view;

------2.4 同义词
---创建
create public synonym public_dept for scott.dept;
create synonym private_dept for dept;

---删除
drop public synonym public_dept;
drop synonym private_dept;

------2.5 序列
---创建
alter sequence empno_seq
start with 100
maxvalue 100000  --minvalue/nominvalue/nomaxvalue
increment by 200
cache 100
cycle  --nocycle
order  --noorder

---管理
alter sequence empno_seq
maxvalue 500000
increment by 200;

---删除
drop sequence empno_seq;


----------3. 表/索引分区-------------
------3.1 表分区
---创建
--范围
create table ware_retail_part
(
  id integer primary key,
  retail_date date,
  ware_name varchar2(50)
)
partition by range(retail_date)
(
  partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,
  partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,
  partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,
  partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2 
);

--散列
create table goods
(
  id number,
  goodname varchar2(50)
)
storage(initial 2048k)
partition by hash(id)
(
 partition par1 tablespace tbsp_1,
 partition par2 tablespace tbsp_2
);

--列表
create table clients
(
  id integer primary key,
  name varchar2(50),
  province varchar2(20)
)
partition by list(province)
(
  partition shandong values('山东省'),
  partition guangdong values('广东省'),
  partition yunnan values('云南省')
);

--组合
create table person2
(
  id number primary key,
  name varchar2(20),
  sex varchar2(2)
)
partition by range(id)  --范围分区
subpartition by hash(name)  --hash子分区
subpartitions 2 store in(tbsp_1,tbsp_2)  --存储在两个不同的命名空间中
(
  partition par1 values less than(5000),
  partition par2 values less than(10000),
  partition par3 values less than(maxvalue)
);

--Interval
create table saleRecord
(
 id number primary key,
 goodsname varchar2(50),
 saledate date,
 quantity number
)
partition by range(saledate)
interval (numtoyminterval(1,'year'))
(
  partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))
);

--应用
insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'PC');
insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'TV');

select * from ware_retail_part partition(par_02);

---管理
--添加
alter table clients
add partition hebei values('河北省')
storage(initial 10K next 20k) tablespace tbsp_1
nologging;

--合并
alter table person coalesce partition;
alter table person2 modify partition par3 coalesce subpartition;

--删除
--disable constraint/drop/enable constraint
delete from ware_retail_part where retail_date>=to_date('2011-10-01','yyyy-mm-dd');  --数据
alter table ware_retail_part drop partition par_04;  --表分区
alter index ware_index rebuild;  --重建索引

--并入
alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;
alter table sales modify partition part_sea4 rebuild unusable local indexes;  --重建局部索引

------3.2 索引分区
---创建
--本地
--create tablespace ts_1/ts_2/ts_3;
--create table studentgrade partition by range(grade);
create index grade_index on studentgrade(grade)
local
(
  partition p1 tablespace ts_1,
  partition p2 tablespace ts_2,
  partition p3 tablespace ts_3
);  --dba_ind_partitions
--全局
create index index_SalePrice on Books(SalePrice)
global partition by range(SalePrice)
(
  partition p1 values less than (30),
  partition p2 values less than (50),
  partition p3 values less than (maxvalue)
);

---管理
--删除
alter index index_saleprice drop partition p2;
alter index index_saleprice drop partition p1;
alter index index_saleprice rebulid partition p3;
--重命名
alter index index_saleprice rename partition p3 to p_new;

----------4. 数据库管理-------------
------4.1 数据文件/表空间
---查看
select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

---默认
select segment_type,segment_name,owner from dba_segments where tablespace_name='USERS';
--SYSTEM/SYSAUT/UNDOTBS1/USERS/EXAMPLE/TEMP

---创建
--本地化管理方式
create tablespace tbs_test_1 datafile 'D:OracleFilesOracleDatadatafile1.dbf'
size 10m
extent management local uniform size 256K;  --autoallocate
--段空间管理方式
create tablespace tbs_test_3 datafile 'D:OracleFilesOracleDatadatafile3.dbf'
size 20m
extent management local autoallocate
segment space management manual;  --auto
--非标准块
alter system set db_16k_cache_size = 16M scope=both;
create tablespace tbs_test_5 datafile 'D:OracleFilesOracleDatadatafile5.dbf'
size 64m reuse
autoextend on next 4m maxsize unlimited
blocksize 16k
extent management local autoallocate
segment space management auto;
--大文件
create bigfile tablespace tbs_test_big datafile 'D:OracleFilesOracleDatadatafilebig.dbf'
size 2g;

---维护
--默认
alter database default temporary tablespace temp_1;
alter database default tablespace tbs_example;
--状态
alter tablespace tbs_test_3 read only;  --read write
--重命名
alter tablespace tbs_test_3 rename to tbs_test_3_new;
--删除
drop tablespace tbs_test_1 including contents cascade constraint;
--文件
alter tablespace users add datafile 'e:appAdministratororadataorclusers02.dbf'
size 10m autoextend on next 5m maxsize unlimited;  --添加
alter tablespace users drop datafile 'e:appAdministratororadataorclusers02.dbf';  --删除
alter database datafile 'D:OracleFilesOracleDatadatafile2.dbf'
autoextend on next 10m maxsize unlimited;  --自动扩展

---撤销表空间
--创建
create undo tablespace undo_tbs_1
datafile 'D:OracleFilesOracleDataundotbs1.dbf'
size100M;
--修改
alter tablespace undo_tbs_1 
add datafile 'D:OracleFilesOracleDataundotbs_add.dbf' 
size 2g;
--切换
alter system set undo_tablespace=undo_tbs_1;
--删除
alter system set undo_tablespace=undotbs1;
drop tablespace undo_tbs_1;
--查询
show parameter undo_tablespace;  --undo_management/undo_retention
select tablespace_name from dba_tablespaces where contents = 'UNDO';
select to_char(begin_time,'hh24:mi:ss'), to_char(end_time,'hh24:mi:ss'), undoblks
from v$undostat order by begin_time;  --表空间统计信息
select rn.name,rs.xacts,rs.writes,rs.extents
from v$rollname rn,v$rollstat rs where rn.usn = rs.usn;  --段统计信息
select name,status from v$transaction;  --活动事务
select segment_name, extent_id,bytes,status from dba_undo_extents
where segment_name='_SYSSMU3_991555123$';  --UNDO区信息

---临时表空间
--创建
create temporary tablespace temp_01 tempfile 'D:OracleFiles	empfiles	emp_01.tpf' size 300m;
alter database default temporary tablespace temp_01;
--重命名/删除同上
--查询
select file_name,bytes,tablespace_name from dba_temp_files;
---临时表空间组
create temporary tablespace tp1 tempfile 'D:OracleFiles	empfiles	p1.tpf' 
size 10m tablespace group group1;
create temporary tablespace tp2 tempfile 'D:OracleFiles	empfiles	p2.tpf' 
size 20m tablespace group group1;  --创建
create temporary tablespace tp3 tempfile 'D:OracleFiles	empfiles	p3.tpf' 
size 10m tablespace group group3;
alter tablespace tp1 tablespace group group3;  --转移
alter user hr temporary tablespace group3;  --分配
alter database orcl default temporary tablespace group3;  --默认
drop tablespace tp1 including contents and datafiles;

------4.2 控制文件
---多路复用
alter system set control_file=
  'D:PROGRAMORACLEORADATAORCLCONTROL01.CTL',
  'D:PROGRAMORACLEFLASH_RECOVERY_AREAORCLCONTROL02.CTL',
  'D:OracleFilesControlFilesCONTROL03.CTL'
scope=spfile;  --参数设置后复制文件
select name from v$controlfile;  --查看

---创建
select member from v$logfile;  --查看日志文件
select name from v$datafile;  --查看数据文件
select name from v$controlfile;  --查看控制文件
shutdown immediate;  --关闭数据库，然后备份文件
startup nomount;  --启动数据库实例，不加载数据库
create controlfile reused --创建新控制文件
database "orcl"
logfile
group 1 'D:PROGRAMORACLEORADATAORCLREDO01.LOG',
group 2 'D:PROGRAMORACLEORADATAORCLREDO02.LOG',
group 3 'D:PROGRAMORACLEORADATAORCLREDO03.LOG'
datafile
'D:PROGRAMORACLEORADATAORCLSYSTEM01.DBF',
'D:PROGRAMORACLEORADATAORCLSYSAUX01.DBF',
'D:PROGRAMORACLEORADATAORCLUNDOTBS01.DBF',
'D:PROGRAMORACLEORADATAORCLUSERS01.DBF',
'D:PROGRAMORACLEORADATAORCLEXAMPLE01.DBF',
'D:PROGRAMORACLEPRODUCT11.2.0DBHOME_1ORADATAKPLAYERKPLAYER.DBF'
maxlogfiles 50
maxlogmembers 3
maxinstances 6
maxdatafiles 200
noresetlogs
noarchivelog;
alter system set control_files=  --编辑参数
  'E:PROGADMINORADATAORCLCONTROL01.CTL',
  'E:PROGADMINFLASH_RECOVERY_AREAORCLCONTROL02.CTL'
scope=spfile;
alter database open;  --打开数据库 [resetlogs]

---备份
alter database backup controlfile 
to 'D:OracleFilesControlFilesctf.bak';  --二进制文件
alter database backup controlfile to trace;  --脚本文件
show parameter user_dump_dest;

---恢复
--关闭->复制覆盖->重启
--关闭->编辑CONTROL_FILES->重启

---删除
--关闭->编辑CONTROL_FILES->重启

---查询
--v$controlfile  所有控制文件名称和状态
--v$controlfile_record_section  控制文件各记录文档段信息
--v$parameter  系统所有初始化参数

------4.3 重做日志文件
---增加
alter database add logfile group 5
('D:OracleFilesLogFilesREDO4_A.LOG',
'E:OracleFilesLogFilesREDO4_B.LOG')
size 20M;  --添加新的重做日志文件组
alter database add logfile member
'E:OracleFilesLogFilesREDO4_C.LOG' to group 4;  --创建日志成员文件
alter database add logfile member
'D:OracleFilesLogFilesREDO1_new.LOG' to 
('E:appAdministratororadataorclREDO01.LOG') ;  --指定成员名称

---删除
--日志成员
alter database drop logfile member 'E:OracleFilesLogFilesREDO4_C.LOG';
--日志文件组
alter database drop logfile group 5;
--清空
alter database clear logfile group 4;

---更改
--关闭->复制源文件到目标位置->启动加载但不打开mount
alter database rename file
  'D:OracleFilesLogFilesREDO1_new.LOG',
  'D:OracleFilesLogFilesREDO4_A.LOG'
to
  'E:OracleFilesLogFilesREDO1_new.LOG',
  'E:OracleFilesLogFilesREDO4_A.LOG';
--打开数据库

--查看
--v$log v$logfile v$log_history

------4.4 归档日志文件
---切换
select log_mode from v$database;  --noarchivelog/archivelog
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

---进程
alter system set log_archive_max_processes = 3;

---位置
--本地
alter system set log_archive_dest_1='location=D:OracleFilesarchive1 optional';
alter system set log_archive_dest_2='location=D:OracleFilesarchive2 mandatory';
alter system set log_archive_dest_3='location=D:OracleFilesarchive3 mandatory reopen=400';
alter system set log_archive_min_succeed_dest=3;  --最小归档数
alter system set log_archive_dest_state_4=defer;  --禁用位置
--远程
alter system set log_archive_dest_1='service=MRKJ';

---查看
--v$database v$archived_log v$archive_dest v$archive_processes v$backup_redolog
archive log list;

----------5. 数据库维护-------------
------5.1 数据库控制
---事务
set transaction read only;  --read write
exec dbms_transaction.read_only;
set transaction use rollback segment system;  --分配回滚段
savepoint sp01;  --设置保存点
insert into jobs_temp values('DESIGN','DS',3000,5000);
rollback to sp01;  --回滚

---锁
lock table dept_temp in row share mode;
--row exclusive mode/share mode/share row exclusive mode/exclusive mode

------5.2 数据导入/导出
---导出
create directory dump_dir as 'd:dump';
grant read,write on directory dump_dir to scott;
--表
expdp scott/1qaz2wsx directory=dump_dir dumpfile=tab.dmp tables=emp,dept
--模式
expdp system/1qaz2wsx directory = dump_dir dumpfile=schema.dmp schemas=scott,hr
--表空间
expdp system/1qaz2wsx directory = dump_dir dumpfile = tablespace.dmp tablespaces=tbsp_1
--数据库
expdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y
--content/query/logfile/status

---导入
impdp system/1qaz2wsx directory=dump_dir dumpfile=tab.dmp 
tables=scott.dept,scott.emp remap_schema=scott:system  --表
impdp system/1qaz2wsx directory=dump_dir dumpfile=schema.dmp 
schemas=scott remap_schema=scott:system;  --模式
impdp system/1qaz2wsx directory=dump_dir dumpfile=tablespace.dmp 
tablespaces=tbsp_1  --表空间
impdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y  --数据库
--remap_schema/remap_tablesapce/sqlfile/table_exists_action/transport_datafiles

------SQL Loader
sqlldr --用法
---自由格式
/*  --student.ctl
load data
  infile 'd:datastudent.txt'
  into table student
  (stuno position(01:04) integer external,
   stuname position(11:14) char,
   sex position(21:22) char,
   old position(29:30) integer external
  )
*/
sqlldr system/1qaz2wsx control=d:datastudent.ctl log=d:datastu_log
---固定格式
/*  --persons.ctl
load data
infile 'd:datapersons.csv'
append into table persons
fields terminated by ','
(code,name,sex,old)
*/
sqlldr system/1qaz2wsx control=d:datapersons.ctl

注：部分SQL语句来源于《Oracle 11g从入门到精通（第2版）》——清华大学出版社