• 1. Oracle基础知识分享 宋倩倩
  • 2. 1、创建、删除、启动、关闭数据库 1.1 创建数据库 1.2 删除数据库 1.3 启动数据库 1.4 关闭数据库(关闭数据库、卸载数据库、终止例程) 2、表、索引、视图、同义词、序列 2.1 表 2.2 索引 2.3 视图 2.4 同义词、序列 3、权限、用户、恢复、备份 3.1 权限角色 3.2 用户 3.3 恢复、备份 4、过程、函数、触发器 4.1 过程 4.2 函数 4.3 触发器目录
  • 3. 1、 创建、删除、启动、关闭数据库1.1 创建数据库 1.1.1 创建、管理途径: 1 DOS命令行方式 dgmgrl(Data Guard ManaGeR command-Line interface) DEMGRL>help 2 SQL*Plus命令行方式 3 DBCA(Database Configuration Assistant) 4 ONCA(Oracle Net Configuration Assistant) 5 OEM(Oracle Enterprise Manager) 1.1.2 规划 1 表空间:估计方案对象的大小、数量 2 布局:各文件所在磁盘位置,均衡磁盘I/O 3 全局数据库名:数据库名DB_NAME + 系统 标示符 DB_DOMAIL 4 初始化参数:启动数据库时使用 5 字符集:规避数据库和用户系统字符集不同产生的错误 6 块尺寸:DB_BLOCK_SIZE指定,数据库创建后不能修改
  • 4. 7 撤销表空间:代替回退段来管理撤销记录 8 备份和恢复:多路复用等技术,防数据库失败 1.1.3 创建脚本 USE [master] GO IF EXISTS(SELECT 1 FROM sysdatabases WHERE NAME=N'HkTemp') BEGIN DROP DATABASE HkTemp --如果数据库存在先删掉数据库 END GO CREATE DATABASE HkTemp ON PRIMARY --创建主数据库文件 ( NAME='HkTemp', FILENAME='E:\Databases\HkTemp.dbf', SIZE=5MB, MaxSize=20MB, FileGrowth=1MB ) LOG ON --创建日志文件 ( NAME='HkTempLog', FileName='E:\Databases\HkTemp.ldf', Size=2MB, MaxSize=20MB, FileGrowth=1MB ) GO 1、 创建、删除、启动、关闭数据库
  • 5. go --添加表 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Hksj_User') AND type in (N'U')) BEGIN CREATE TABLE Hksj_User ( Id INT IDENTITY(1,1) NOT NULL, SName NVARCHAR(20) NOT NULL, SNickName NVARCHAR(20), SPassWord NVARCHAR(30) NOT NULL, DCreateDate DATETIME , SCreator NVARCHAR(20), SEmail NVARCHAR(50), SPhone NVARCHAR(50), SIdentifyId NVARCHAR(30), DLastTimeLogOn DATETIME PRIMARY KEY CLUSTERED ( Id ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO 1、 创建、删除、启动、关闭数据库
  • 6. --添加注释 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hksj_User', @level2type=N'COLUMN',@level2name=N'SName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hksj_User', @level2type=N'COLUMN',@level2name=N'SEmail' 1、 创建、删除、启动、关闭数据库
  • 7. 1.2 删除数据库 1 使数据库处于不被使用状态 SQL> select name from v$database; SQL> SELECT host_name, instance_name, version FROM v$instance; 2 用命令删除 sql>shutdown immediatel; sql>startup mount restrict; sql>drop database orcl; 1.3 启动数据库 1.3.1 所需身份:SYSDBA SYSOPER 1.3.2 启动顺序: 1 启动监听程序 lsnrctl start(stop/status) 2 启动数据库,sql命令行下 >sqlplus / as sysdba; startup nomount启动例程,不装载数据库 startup mount启动例程,并装载数据库,但不打开数据库 startup open启动例程、装载数据库、打开数据库 startup force异常关闭后重新启动 (启动时读取参数文件startup force pfile=e:\pfileoamissid.ora) startup restrict将数据库置于open模式,但只有restricted session权限的用户 才能访问 3 转换启动模式 alter database mount(nomount/open) 4 读取数据 启动例程时读取参数文件 装载数据库时读取控制文件 1、 创建、删除、启动、关闭数据库
  • 8. 1.4 关闭数据库(关闭数据库、卸载数据库、终止例程) 1.4.1 关闭方式 1 shutdown normal阻止新连接,当前用户可提交新事务,所有用户都断开后,关闭、卸载数据库,终止例程 2 shutdown transactional阻止新连接、新事务,当前用户可提交已有事务,所有用户都断开后,关闭、卸载数据库,终止例程 3 shutdown immediate阻止新连接、新事务、已有事务,不等用户断开,关闭、卸载数据库,终止例程 4 shutdown abort阻止新连接、新事务、已有事务、正在执行sql语句,不等用户断开,关闭、卸载数据库,终止例程 1.4.2 相关服务 OracleTNSListener OracleService OracleDBConsole OracleiSQL*Plus1、 创建、删除、启动、关闭数据库
  • 9. 2.1 表 2.1.1 概述 1 构建ER图,将真实世界里的对象转换为数据库里的表 2 名称空间存放各对象的名称 2.1.2 数据类型 1 字符型 char、nchar、varchar2、nvarchar2 2 数字型 number 3 日期和时间型 date、timestamp、timestamp with time zone、timestamp with local time zone、interval year to month、interval day to second 4 二进制型 row、loong row 5 大对象型 clob、nclob、blob、bfile 6 行型 rowid、urowid2、 表、索引、视图、同义词、序列
  • 10. 2.1.3 完整性与约束 1 实体完整性:主键各个属性不能为空 2 参照完整性:外键取空值或等于被参照关系中的主键的某个值 3 用户定义完整性:属性的取值应当满足用户定义的约束条件 4 约束:not null、unique、check、primary key、foreign key 2.1.4 表类型 1 标准表:按堆组织、以无序方式存储数据 2 索引表:以“B-树”结构存放 3 簇表:由共享数据块的一组表组成 4 分区表:数据被划分为更小的部分(分区),并且存储到相应的分区中,每个分区段可以单独管理和操作 2.1.5 创建表2、 表、索引、视图、同义词、序列
  • 11. 1 标准表创建举例 CREATE TABLE Department ( DeptId number(9) NOT NULL, DeptNo CHAR(2) DEFAULT 01 NOT NULL, Name VARCHAR2(20), Address VARCHAR2(30), CONSTRAINT PK_DeptId PRIMARY KEY (DeptId) VALIDATE, CONSTRAINT UNIQ_Name UNIQUE (Name) VALIDATE, CONSTRAINT CHK_Address CHECK ( Address IN ('1号楼','2号楼','3号楼','4号楼') ) VALIDATE ); 2 事务临时表创建举例 CREATE GLOBAL TEMPORARY TABLE temp1 ( Id NUMBER(9) PRIMARY KEY, Name VARCHAR2(20) ); INSERT INTO temp1 values(1,’gogo’); select * from temp1; commit; select * from temlp1; 2、 表、索引、视图、同义词、序列
  • 12. 3 回话临时表创建举例 CREATE GLOBAL TEMPORARY TABLE temp2 ( Id NUMBER(9) PRIMARY KEY, Name VARCHAR2(20) ) ON COMMIT PRESERVE ROWS; insert into table temp2 values(1, ‘dodo’); commit; select * from temp2; conn scott/***@test1; select * from temp2; 4 索引表创建举例 CREATE TABLE IOT1 ( ID NUMBER(9), NAME VARCHAR2(20), ADDRESS VARCHAR2(30), NOTE VARCHAR2(40), CONSTRAINT PK_ID PRIMARY KEY (ID) VALIDATE ) ORGANIZATION INDEX PCTTHRESHOLD 40 INCLUDING ADDRESS OVERFLOW TABLESPACE USERS; 5 创建新表,结构和数据均来源于已存在表,举例 CREATE TABLE new_emp AS SELECT * FROM emp NOLOGGING; 6 创建新表,仅结构来源于已存在表,举例 CREATE TABLE new_emp_1 AS SELECT * FROM emp WHERE 1=2;2、 表、索引、视图、同义词、序列
  • 13. 2.1.6 更改表 1 添加列,举例 ALTER TABLE Department ADD ( Leader VARCHAR2(20), UpdateDate DATE DEFAULT sysdate ); 2 修改列数据类型,举例 ALTER TABLE Department MODIFY ( DEPTNO CHAR(6), NAME VARCHAR2(80) ); 3 删除列-先标记为不可用,举例(此操作不可逆,除非修改数据字典) ALTER TABLE Department SET UNUSED (Address, Leader) CASCADE CONSTRAINTS; 4 删除列-后删除不可用列,举例 ALTER TABLE Department DROP UNUSED COLUMNS; 2.1.7 给表或列添加注释 1 给表添加注释,举例 COMMENT ON TABLE new_emp IS ‘zhushi’; 2 给列添加注释,举例 COMMENT ON COLUMN new_emp.ename IS ‘给列添加注释’; 2.1.8 重命名、重组织表 1 重命名表,举例 RENAME new_emp TO new_emp_change; ALTER TABLE new_emp_change RENAME TO new_emp; 2 重组织表,举例(dba_data_files,all_tables) ALTER TABLE new_emp MOVE TABLESPACE myts; 2、 表、索引、视图、同义词、序列
  • 14. 2.1.9 删减、删除表 1 删减表 DELETE FROM new_emp; 删除表中数据,不回收存储空间 TRUNCATE TABLE new_emp; 删除表中数据,回收存储空间 2 删除表 DROP TABLE new_emp CASCADE CONSTRAINTS; 2.1.10 外部表 1 创建外部表(dba_directories, user_external_locations) 建立目录对象CREATE DIRECTORY dept_dir AS 'E:\EXTTAB'; CREATE TABLE ext_department ( DeptId NUMBER(9), DeptNo CHAR(2), Name VARCHAR2(20), Address VARCHAR(30) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dept_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (DeptId, DeptNo, Name, Address) ) LOCATION ('department.txt') );2、 表、索引、视图、同义词、序列
  • 15. 使用外部表SELECT * FROM ext_department; 2 更改、删除外部表 修改默认目录 ALTER TABLE ext_department DEFAULT DIRECTORY new_dept_dir; 修改文件位置 ALTER TABLE ext_department LOCATION('new_dept.txt'); 修改访问参数 ALTER TABLE ext_department ACCESS PARAMETERS (FIELDS TERMINATED BY ';'); 删除外部表 drop table ext_department; 删除目录对象 DROP DIRECTORY dept_dir; 3 数据字典视图 用户表 user_tables 用户表列 user_tab_columns 外部表 user_external_tables 外部表位置 user_external_locations2、 表、索引、视图、同义词、序列
  • 16. 2.2 索引 2.2.1 概述 有独立的命名存储结构,索引段,目的是加快检索速度 2.2.2 跟踪索引 1 创建大表 CREATE TABLE tab_idx_test AS SELECT * FROM dba_objects WHERE owner IN ('PUBLIC', 'SCOTT', 'SYS', 'SYTEM'); 2 设置显示执行计划,不显示查询结果 SET autotrace trace explain; 3 查询大表TABLE ACCESS (FULL) SELECT * FROM tab_idx_test WHERE object_name='DBA_INDEXES'; 4 为大表创建索引 CREATE INDEX idx_tab_idx_test ON tab_idx_test (object_name); 5 再查询大表TABLE ACCESS (BY INDEX ROWID) SELECT * FROM tab_idx_test WHERE object_name='DBA_INDEXES';2、 表、索引、视图、同义词、序列
  • 17. 2.2.3 索引原理 索引主要为查询搜索服务。未创建索引,查询时,数据库要将整张表从头到尾全部读取;创建索引时,数据库会对表进行一次全表搜索,获取索引列记录,并将它们排序,那么在查询时,数据库只读取到要查询的数据便可,不必从头到尾读取 2.2.4 索引的分类 单列、复合索引 唯一、非唯一索引 标准索引(B树索引) 位图索引(bitmap index) 函数索引(function index) 2.2.5 创建索引 1 标准索引创建举例 CREATE INDEX idx_emp_ename ON emp(ename) PCTFREE 30 TABLESPACE myts; 2 位图索引创建举例 CREATE BITMAP INDEX bmidx_emp_job ON scott.emp(job); 3 函数索引创建举例 CREATE INDEX funidx_salcomm ON emp (sal + comm + sal*0.2);2、 表、索引、视图、同义词、序列
  • 18. 2.2.6 更改索引 1 重命名索引举例 ALTER INDEX myindex RENAME TO idx_ename_sal; 2 合并索引举例 ALTER INDEX idx_ename_sal COALESCE; 3 重建索引举例 ALTER INDEX idx_ename_sal REBUILD; ALTER INDEX idx_ename_sal REBUILD TABLESPACE users; 4 分配索引空间举例 ALTER INDEX idx_ename_sal ALLOCATE EXTENT(SIZE 100K) 5 释放索引空间举例 ALTER INDEX idx_ename_sal DEALLOCATE UNUSED; 6 监视索引举例 (1)使索引处于被监视状态 ALTER INDEX funidx_salcomm MONITORING USAGE; (2)查询数据字典视图 SELECT index_name, monitoring, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE; 2、 表、索引、视图、同义词、序列
  • 19. (3)执行sql查询 select empno, ename, job, sal FROM emp WHERE (sal + comm + sal*2) < 2000; (4)查询数据字典视图 SELECT index_name, monitoring, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE; (5)关闭监视状态 ALTER INDEX funidx_salcomm NOMONITORING USAGE; (6)再查询数据字典视图 SELECT index_name, monitoring, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE; 2、 表、索引、视图、同义词、序列
  • 20. 2.2.7 删除索引 DROP INDEX funidx_emp_empno; 2.2.8 查看索引 1查询表的所有索引 SELECT index_name, index_type, tablespace_name, uniqueness, logging FROM user_indexes WHERE table_name='EMP'; 2查询索引对应的列 SELECT index_name, column_name, column_position, column_length FROM user_ind_columns WHERE index_name=''; 3查询函数索引的函数表达式 SELECT index_name, table_name, column_expression, column_position FROM user_ind_expresstions WHERE index_name='';2、 表、索引、视图、同义词、序列
  • 21. 2.3 视图 2.3.1 概述 视图中不保存数据,视图的定义信息被保存到数据字典中 2.3.2 创建视图 1 简单视图创建举例 CREATE VIEW v_staff AS SELECT empno, ename, job, hiredate, deptno FROM emp WITH READ ONLY; CREATE VIEW v_staff AS SELECT empno, ename, job, hiredate, deptno FROM emp WITH CHECK OPTION CONSTRAINT v_staff_chk; 2 连接视图创建举例 CREATE VIEW v_dept_emp AS SELECT a.deptno, a.dname, a.loc, b.empno, b.name, b.sal FROM dept a, emp b WHERE a.deptno=b.deptno AND a.deptno IN (10, 30); 3 复杂视图创建举例 CREATE VIEW v_emp_job_sal (job, avgsal, sumsal, maxsal, minsal) AS SELECT job, avg(sal), sum(sal), max(sal), min(sal) FROM emp GROUP BY job; 4 视图强制创建举例(基础表不存在) CREATE FORCE VIEW v_test_tab AS SELECT empno, ename FROM emp; 2.3.3 更改视图 1 更改视图定义举例 CREATE OR REPLACE VIEW v_test_tab AS SELECT c1, c2 || '+' || c3 c4 FROM test_tab; 2 重新编译视图举例 (1)选择一个视图,其状态为valid select * from all_objects where object_name=upper('v_staff'); (2)修改视图的一个基础表结构,令视图状态变为invalid (3)编译视图 ALTER VIEW v_staff COMPILE; (4)查看视图状态为valid 2、 表、索引、视图、同义词、序列
  • 22. 2.3.4 删除视图 DROP VIEW v_staff; 2.3.5 对视图进行DML操作 1 键值保存表 如果视图中的基础表的主键、唯一键在视图中仍然存在,则称这个基础表为键值保存表(key-preserved) 2 向视图插入、更新数据 (1)插入、更新数据的列仅为、全部是键值保存表的列,且视图没有with check option,插入、更新成功 (2)插入、更新数据的列包含非键值保存表的列,插入、更新失败 (3)插入、更新数据的列仅为、全部是键值保存表的列,且视图有with check option,插入、更新失败 3 删除视图中数据 (1)删除数据的列仅为键值保存表的列,删除视图成功 (2)删除数据的列包含非键值保存表的列,删除视图失败 2.3.6 查看视图信息 1 查看视图可更新的列 DBA_UPDATABLE_COLUMNS ALL_UPDATABLE_COLUMNS USER_UPDATABLE_COLUMNS 2 查看所有视图 DBA_VIEWS ALL_VIEWS USER_VIEWS 3 查看所有视图的列 DBA_TAB_COLUMNS ALL_TAB_COLUMNS USER_TAB_COLUMNS 2、 表、索引、视图、同义词、序列
  • 23. 2.3.7 物化视图概述 1 物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。 2 创建物化视图举例 示例1: create materialized view MV_ DOP_TEST refresh force on demand as select dsso.dop_id, soo.work_center_no, soo.operation_description from SHOP_ORDER_OPERATION SOO, DOP_SUPPLY_SHOP_ORD DSSO where soo.order_no=dsso.order_no and soo.release_no=dsso.release_no and soo.sequence_no=dsso.sequence_no; ---其中创建与删除物化视图与其表或视图DDL一样: DROP materialized view log on materialized_view_log_name ; 创建物化视图时创建存储的日志空间(存储物化视图的对象的改变信息) CREATE MATERIALIZED VIEW LOG ON Dop_Supply_Shop_Ord_Tab --(基表名) tablespace ifsapp_DATA --日志保存在特定的表空间 WITH ROWID ; drop materialized view MV_materialized_view_name ; 2、 表、索引、视图、同义词、序列
  • 24. 示例2: create materialized view MV_DOP_TEST TABLESPACE ifsapp_DATA --保存表空间 BUILD DEFERRED --延迟刷新不立即刷新,此建立初始视图一般没数据 refresh force --如果可以快速刷新则进行快速刷新,否则完全刷新 with rowid --根据rowid刷新(默认是主键) On demand --按照指定方式刷新 start with to_date('2008-12-11 13:20:51','YYYY-MM-DD HH24:MI:SS') next sysdate+1/48 as select dsso.dop_id, soo.work_center_no, soo.operation_description from SHOP_ORDER_OPERATION SOO, DOP_SUPPLY_SHOP_ORD DSSO where soo.order_no=dsso.order_no and soo.release_no=dsso.release_no and soo.sequence_no=dsso.sequence_no 2、 表、索引、视图、同义词、序列
  • 25. 2.4 同义词 2.4.1 概述 同义词是方案对象的一个别名,经常用于简化对象的访问和提高对象访问的安全性,其定义保存在数据字典中,不占实际存储空间 2.4.2 类型 1 公共同义词 所有用户都可使用,如数据字典视图 2 方案同义词 方案对象拥有 3 名称解析顺序 (1)当前用户拥有的对象 (2)由当前用户拥有的一个方案同义词 (3)公用同义词 2.4.3 创建同义词 1 创建公用同义词 CREATE OR REPLACE PUBLIC SYNONYM pubsyn_department FOR SCOTT.department; 2 创建方案同义词 CREATE OR REPLACE SYNONYM syn_department FOR SCOTT.department;2、 表、索引、视图、同义词、序列
  • 26. 2.4.4 使用同义词 1 使用公用同义词 SELECT * FROM pubsyn_department; 2 使用方案同义词 (1)方案对象使用方案同义词 用户myuser使用其创建的同义词 (2)其他用户使用方案同义词 无论用户1有没有用户2所创建的表的增删改查等权限,用户1都不能使用用户2表的同义词 (3)通过方案同义词访问其他方案中的对象 用户1有用户2所创建的表的增删改查等权限,用户1在自己的方案中创建了用户2的表的同义词,用户1可使用此表的同义词 2.4.5 删除同义词 1 删除公用同义词 DROP PUBLIC SYNONYM synonym_name; 2 删除方案同义词 DROP SYNONYM synonym_department; 2.4.6 查看同义词信息 DBA_SYNONYMS ALL_SYNONYMS USER_SYNONYMS 2、 表、索引、视图、同义词、序列
  • 27. 2.5 序列 2.5.1 概述 序列就是一个没有命名的顺序编号生成器,没有实际存储空间,只在数据字典中保留定义 2.5.2 序列使用范围 1 主键、外键 2 流水号 2.5.3 创建序列 CREATE SEQUENCE seq_id9 increment By 1 Start With 300 MaxValue 999999999 MinValue 1 Cache 10 Order; 2.5.4 使用序列 1 初始化序列 先SELECT seq_id9.nextval FROM dual 后SELECT seq_id9.currval FROM dual 2 SQL语句中引用序列 insert into SCOTT.emp values(seq_id9.currval, 'qianqian', 'tester', 7698, to_date('2013/11/4','YYYY/MM/DD'), '120', '', 10); 3 最大值最小值 当值达到最大值后,再次使用nextval,系统将报错2、 表、索引、视图、同义词、序列
  • 28. 2.5.5 更改序列 当表被装载且主键值与nextval发生冲突时,可修改序列,方法如下: 1 删除序列,后重新创建序列 2 选择nextval多次,使其达到理想值 3 更改increment by值,选择一个理想的nextval值,恢复increment by值 2.5.6 删除序列 DROP SEQUENCE seq_id9; 2.5.7 查看序列信息 DBA_SEQUENCES ALL_SEQUENCES USER_SEQUENCES2、 表、索引、视图、同义词、序列
  • 29. 3.1 权限角色 3.1.1 权限概述 1 系统权限:指在系统级控制数据库的存取和使用的机制。如,是否能启动、停止数据库,是否能修改数据库参数等 2 对象权限:指在对象级控制数据库的存取和使用的机制。如,用户可以存取哪个方案中的对象,是否能对该对象进行查询、插入、更行等 3.1.2 预定义角色查询 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT' ORDER BY PRIVILEGE; 3.1.3 自定义角色 1 创建角色 CREATE ROLE account_role IDENTIFIED BY 111; 2 打开角色 SET ROLE account_role IDENTIFIED BY 111; 3 删除角色 DROP ROLE account_role; 3.1.4 授予、回收权限 1 将系统权限授予角色 GRANT CRATE TRIGGER TO "ACCOUNT_ROLE" WITH ADMIN OPTION; 2 将方案权限授予角色 GRANT DELETE ON "SCOTT"."SALGRADE" TO "ACCOUNT_ROLE" WITH ADMIN OPTION; 3 回收权限 REVOKE DELETE ON "ACOTT"."SALGRADE" FROM "ACCOUNT_ROLE"; 3.1.5 角色查看 dba_role_privis;dba_sys_privis; role_sys_privis; all_table_privis; user_table_privis; dab_profiles 3、权限角色、用户、恢复、备份
  • 30. 3.2 用户 3.2.1 管理账户 1 系统自动创建的管理账户sys、system 2 系统权限sysdab、sysoper 3.2.2 身份验证 1 数据库身份验证 CREATE USER jack IDENTIFIED BY welcome; 2 外部身份验证 (1)参数文件init.ora 中参数OS_AUTHENT_PREFIX取值为“ops$” CREATE USER ops$jack IDENTIFIED EETERNALLY; (2)OS_AUTHENT_PREFIX取值为“” CREATE USER jack IDENTIFIED EETERNALLY; 3 全局身份验证 CREATE USER scott IDENTIFIED GLOBLY AS 'CN=scott, OU=division1, O=sybex, C=US'; 3.2.3 默认表空间 1 查询用户及其对应的表空间 SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS; 2 查询创建的表所在表空间 SELECT table_name, tabspace_name FROM USER_TABLES;3、权限角色、用户、恢复、备份
  • 31. 3.2.4 临时表空间 1 排序时,oracle首先使用内存中的排序区SORT_AREA_SIZE进行排序,如果内存不够,则自动在临时表空间创建临时段进行排序 2 创建用户时指定临时表空间 CREATE USER jack IDENTIFIED BY welcome TEMPORARY TABLESPACE temp; 3 创建用户后修改表空间 ALTER USER jack TEMPORARY TABLESPACE users; 3.2.5 表空间配额 1 查询表空间配额 SELECT * FROM DBA_TS_QUOTAS; 2 创建用户时指定表空间配额 CREATE USER jack IDENTIFIED BY welcome DEFAULT TABLESPACE users QUOTA 100k ON users; 3 创建用户后修改表空间配额 ALTER USER jack QUOTA 200k ON users; 3.2.6 概要文件 1 创建用户时指定概要文件 CREATE USER jack IDENTIFIED BY welcome PROFILE profile_test; 2 创建用户后修改概要文件 ALTER USER jack PROFILE profile_test;3、权限角色、用户、恢复、备份
  • 32. 3.2.7 账户状态 1 口令过期 ALTER USER jack PASSWORD EXPIRE; 2 用户锁定 ALTER USER jack ACCOUNT LOCK; 3 账户解锁 ALTER USER jack ACCOUNT UNLOCK; 3.2.8 总结 1 创建用户 CREATE USER username IDENTIFIED BY command [DEFAULT TABLESPACE tsname] [TEMPORARY TABLESPACE ttsname] [QUOTA[k|m] | [UNLIMITED] ON tsname ] [PROFILE prname] [ACCOUNT LOCK] | [ACCOUNT UNLOCK] 2 更改用户 ALTER USER username IDENTIFIED BY command [DEFAULT TABLESPACE tsname] [TEMPORARY TABLESPACE ttsname] [QUOTA[k|m] | [UNLIMITED] ON tsname ] [PROFILE prname] [ACCOUNT LOCK] | [ACCOUNT UNLOCK] 3 删除用户 DROP USER username CASCADE;3、权限角色、用户、恢复、备份
  • 33. 3.3 恢复、备份 3.3.1 概述 恢复基于备份 3.3.2 备份与恢复方法 1 脱机备份与恢复,数据库被关闭 2 联机备份与恢复,数据库处于归档模式下,24小时运行 3 逻辑备份与恢复,只备份数据,不备份物理文件 3.3.3 脱机备份与恢复 1 参数文件 SPFILEoamisSID.ORA PWDoamisSID.ORA PFILEoamisSID.ORA 2 控制文件 SELECT status, name FROM v$controlfile; 3 数据文件 SELECT status, file_name FROM dba_data_files; 4 重做日志文件 SELECT group#, status, member FROM v$logfile;3、权限角色、用户、恢复、备份
  • 34. 3.3.4 使用数据泵进行逻辑备份与恢复 1 转存文件只能放在directory指定的目录里 (1) 创建目录 CREATE DIRECTORY dump_dir AS 'E:\DUMP'; (2)给用户授予目录的读写权限 GRANT READ, WRITE ON DIRECTORY dump_dir TO system, myuser; 2 导出数据 (1) 计算转储文件大小 expdp system/password full=y estimate=statistics nologfile=y (2) 导出表 expdp myuser/password directory=dump_dir dumpfile=myusertab.dmp logfile=myus (3) 导出数据库 a 在目录E:\DUMP 创建myparfile.txt参数文件,其内容如下 dumpfile=dump_dir:mydb_%U.dmp logfile=dump_dir:mydb.log filesize=10 b 导出整个数据库 expdp system/password parfile=e:\dump\myparfile.txt 3 导入数据 (1)导入数据 impdp myuser/password directory=dump_dir dumpfile=myusertab.dmp logfile=myus (2)导入表 impdp myuser/password directory=dump_dir dumpfile=myusertab.dmp logfile=myus (3)导入方案 impdp system/password directory=dump_dir dumpfile=myuser.dmp logfile=myuser_imp.log schemas=myuser job_name=imp_schema 3、权限角色、用户、恢复、备份
  • 35. 4.1 过程 4.1.1 概述 将经常需要执行的特定的操作写成存储过程 4.1.2 过程举例 CREATE OR REPLACE PROCEDURE query_emp (v_no IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE) IS e_sal_error EXCEPTION BEGIN SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno=v_no; IF v_sal>=2500 THEN DBMS_OUTPUT.PUT_LINE('该雇员工资:'||v_sal); RAISE e_sal_error END IF EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('没有该雇员:'||v_no); WHEN e_sal_error THEN DBMS.OUTPUT.PUT_LINE('该雇员工资高于2500了'); END query_emp 4.1.3 过程执行 sql>VARIABLE a1 VARCHAR2(16); sql>VARIABLE a2 NUMBER; sql>EXECUTE query_emp(7788, :a1, :a2); sql>DBMS_OUTPUT.PUT_LINE(a1); sql>DBMS_OUTPUT.PUT_LINE(a2);4、过程、函数、触发器
  • 36. 4.2 函数 4.2.1 函数概述 将经常需要进行的计算写成函数 4.2.2 函数举例 CREATE OR REPLACE FUNTION get_salary_by_deptno (v_deptno_no IN emp.deptno%TYPE, v_emp_cnt OUT NUMBER) RETURN NUMBER IS v_sum NUMBRE(10, 2); BEGIN SELECT sum(sal), count(*) INTO v_sum, v_emp_cnt FROM emp WHERE deptno=v_dept_no; RETURN v_sum; END get_salary_by_deptno 4.2.3 函数调用 sql>VARIABLE a1 NUMBER; sql>VARIABLE a2 NUMBER; sql>EXECUTE :a1 := get_salary_by_deptno(10, :a2); sql>DBMS_OUTPUT.PUT_LINE(a2);4、过程、函数、触发器
  • 37. 4.3 触发器 4.3.1 隐式调用的存储过程 4.3.2 触发器举例 CREATE OR REPLACE TRIGGER tr_emp_time BEFORE insert OR update OR delete ON emp BEGIN IF (TO_CHAR(sysdate, 'DAY')) IN ('星期六', '星期日') OR (TO_CHAR(sysdate, 'HH24') NOT BETWEEN 8 AND 18) THEN RAISE_APPLICATION_ERROR(-20001, '不是上班时间,不能修改emp表'); END IF; END;5、sql语言
  • 38. 5.1 语言分类 数据定义语言(Data Definition Language, DDL) 数据操纵语言(Data Manipulation Language, DML) 事务控制(Transaction Control) 会话控制(Session Control) 系统控制(System Control) 5.2 数据查询 5.2.1 基本查询 1 查询所有列 selelct * from emp; 2 查询指定列 select deptno, dname from dept; 3 取消重复行 select distinct deptno, job from emp; 4 使用表达式 select ename ||'是一位'|| job as 雇员细节, to_char(hiredate, 'YYY_MM_DD') as 雇佣时间, sal*1.2 from emp 5 使用where字句 select name, hiredate from emp where hiredate>'01-1月-82'; 6 使用order by字句 select deptno, ename, sal, comm from emp where sal between 1500 and 3000 order by deptno desc, ename; 5、sql语言
  • 39. 5.2.2 分组查询 1 组处理函数(sum,count,variance,stddev) 2 group by子句,单列分组 select deptno, avg(sal), max(sal) from emp group by deptno; 3 group by子句,多列分组 select deptno, job, avg(sal), max(sal) from emp group by deptno, job; 4 group by子句,rollup横向统计结果 select deptno, job, avg(sal), max(sal) from emp group by rollup(deptno, job); 5 group by子句,cube纵向统计结果 select deptno, job, avg(sal), max(sal) from emp group by cube (deptno, job); 6 group by 子句,having select deptno, avg(sal),max(sal) from emp group by deptno having avg(sal) >2000; 5.2.3 连接查询 1 笛卡儿连接(m×n) select dept.deptno, emp.deptno, ename from dept, emp where dept.deptno < 30 and emp.job = ‘clerk’; 2 相等连接 select d.deptno, d.dname, e.ename, e.sal from dept d, emp e where d.deptno = e.deptno and d.deptno = 20; 3 不等连接 select a.ename, a.sal, b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal and a.deptno = 30; 4 自我连接 select e.ename 雇员, m.ename 管理员 from emp e, emp m where m.empno = e.mgr and e.deptno = 30; 5、sql语言
  • 40. 5.2.4 合并查询 1 UNION(两个查询结果的并集,自动去掉重复行) select empno, ename, mgr from emp where deptno = 30 union select empno, ename, mgr from emp where job = ‘manager’; 2 UNION ALL(两个查询结果的并集,不去掉重复行) select empno, ename, mgr from emp where deptno = 30 union all select empno, ename, mgr from emp where job = ‘manager’; 3 INTERSECT(两个查询结果的交集,并以第一列排序) select empno, ename, mgr from emp where deptno = 30 intersect select empno, ename, mgr from emp where job = ‘manager’; 4 MINUS(两个查询结果的差集,并以第一列排序) select empno, ename 雇员, mgr from emp where deptno = 30 minus select empno, ename, mgr from emp where job = ‘MANAGER’ order by 雇员; 5.2.5 子查询 1 单行子查询 select ename, deptno, sal from emp where sal = (select max(sal) from emp); 2 多行子查询 select ename, deptno, sal, job from emp where sal > all (select sal from emp where deptno = 20); 3 相关子查询 select deptno, (select max(sal) from b where b.deptno = a.deptno) maxsal from emp a order by deptno; 4 标量子查询 select distinct deptno, (select max(sal) from emp b where b.deptno = a.deptno) maxsal from emp a order by deptno; 5 多列子查询 select ename, deptno, sal, job from emp where (deptno, job) = (select deptno, job from emp where ename = ‘smith’); 5、sql语言
  • 41. (本页无文本内容)
  • 42. (本页无文本内容)
  • 43. (本页无文本内容)
  • 44. (本页无文本内容)
  • 45. (本页无文本内容)
  • 46. (本页无文本内容)
  • 47. (本页无文本内容)
  • 48. (本页无文本内容)
  • 49. (本页无文本内容)
  • 50. (本页无文本内容)
  • 51. (本页无文本内容)
  • 52. (本页无文本内容)
  • 53. (本页无文本内容)
  • 54. (本页无文本内容)
  • 55. (本页无文本内容)
  • 56. (本页无文本内容)
  • 57. (本页无文本内容)
  • 58. (本页无文本内容)
  • 59. (本页无文本内容)
  • 60. (本页无文本内容)