整理的 Oracle 基于SQL Plus 以及技术相关

_______put in order MyOracle copyright @ kewen_vip I. ORACLE的主要特点： ********************************************************************************** 1. 支持多用户、大事务量的事务处理 2. 数据安全性和完整性控制 3. 支持分布式数据处理 4. 可移植性 II. ORACLE 技术体系结构: ********************************************************************************** Oracle服务器 | ___________________________________________________________________________ | (物理组件) | (逻辑组件) 数据库 数据库实例 | | _________________ ________________ | (物理组件) | (逻辑组件) | (物理组件) | (逻辑组件) 归档日志文件 逻辑划分 后台进程PGA 内存结构SGA |*************|************* |************* |*********** |__数据文件 |__数据库 |__程序进程PMON |__共享池 |__控制文件 |__表空间 |__系统进程SMON |_数据缓冲区 |__日志文件 |__段 |__数据读写DBWR |_日志缓冲区 |__口令文件 |__区 |__日志读写LGW |********** |__参数文件 |__数据块 |__检查点CKPT |************* |__模式 |*************** |************* 注释： a. ORACLE 数据库: 一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件等）. b. ORACLE 实例: 管理数据库的后台进程和内存结构的集合称为 Oracle 实例 . c. 物理结构（物理组件）： 物理结构包含数据库中的一组操作系统文件 . d. 逻辑结构（逻辑组件）： 逻辑结构指数据库创建之后形成的逻辑概念之间的关系 . e. 共享池 : 共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域, 共享池由库缓存和数据字典缓存组成, 共享池的大小直接影响数据库 的性能。 f. 数据缓冲区 : 用于存储从磁盘数据文件中读入的数据，所有用户共享, 服务器进程将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可 以在内存中找 到，不需要再从磁盘读 取，提高了读取速度, 数据缓冲区的大小对数据库的读取速度有直接的影响。 g. 日志缓冲区 : 日志记录数据库的所有修改信息，日志信息首先产生于日志缓冲区, 当日志缓冲区的日志数据达到一定数量时，由后台进程将日志数据写 入日志文件中, 相对来说，日志缓冲 区对数据库的性能影响较小。 h. 程序全局区（PGA）: 包含单个服务器进程所需的数据和控制信息; 在用户进程连接到数据库并创建一个会话时自动分配的，保存每个与Oracle 数据库连接的用 户进程所需的 信息; 为非共享区，只 能单个进程使用，当一个用户会话结束，PGA释放 . i. 用户进程 : 用户进程是一个需要与Oracle服务器进行交互的程序。当用户运行一个应用程序准备向数据库服务器发送请求时，即创建了用户进程 . j. 服务器进程 : 服务器进程用于处理连接到该实例的用户进程的请求。当用户连接至 Oracle 数据库实例创建会话时，即产生服务器进程 . k. 后台进程 : 后台进程是Oracle数据库为了<script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js"></script><script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"></script>保持最佳系统性能和协调多个用户请求而设置的。 Oracle 实例启动时即创建一系列后台进程 . l. PMON 进程监控进程 : 清理出现故障的进程; 释放所有当前挂起的锁定; 释放故障进程使用的资源。 m. SMON 系统监控进程 : 在实例失败之后，重新打开数据库时自动恢复实例; 整理数据文件的自由空间，将相邻区域结合起来; 释放不再使用的临时段。 n. DBWR 数据写入进程 : 管理数据缓冲区，将最近使用过的块保留在内存中; 将修改后的缓冲区数据写入数据文件中。 o. LGWR 日志写入进程 : 负责将日志缓冲区中的日志数据写入日志文件； 系统有多个日志文件，该进程以循环的方式将数据写入文件。 p. 数据文件 : 数据文件用于存储数据库数据，如表、索引数据等。 q. 控制文件 : 控制文件是记录数据库物理结构的二进制文件。 r. 日志文件 : 日志文件记录对数据库的所有修改信息，用于故障恢复 . s. 表空间 : 表空间是数据库中的基本逻辑结构，一系列数据文件的集合。一个表空间可以包含多个数据文件，但是一个数据文件只能属于一个表空间. t. 表分区 : 表分区可以将表划分为若干个区，以提高访问的效率和数据的安全。同时可以更加细化的将每张表都进行分区,有利于后期的精细恢复。 y. 视图 : 视图以经过定制的方式显示来自一个或多个表的数据; 提供了另外一种级别的表安全性; 隐藏的数据的复杂性; 简化的用户的SQL命令; 隔离基表结构的改变; 通过重命名 列，从另一个角度提供数据 IV. 基于Oracle用户实例( sys/sys、system/manager、scott/tiger)的一般操作: ********************************************************************************************************************************* 1. 表空间 a. 创建表空间: SQL> create tablespace ts1 2 datafile 'e:\data.dbf' size 1M 3 autoextend on 4 / 表空间已创建。 b. 删除表空间： SQL> drop tablespace ts1 including contents and datafiles; 表空间已丢弃。 2. 创建用户(表空间归属) SQL> create user aa identified by aa default tablespace ts1; 用户已创建 3. 授权 a. 用户授权: SQL> grant connect ,resource to aa; 授权成功。 b. 用户撤权 SQL> revoke connect, resource from aa; 撤销成功。 c. 权限传递(admin为系统权限) SQL> grant connect, resource to dd with admin option 撤销成功。 4. 用户登录 SQL> conn aa/aa 以连接 5. 创建表 SQL> create table userinfo(id number, name varchar2(5)); 表已创建。 SQL> create table classinfo(id number, userID number, name varchar2(5)); 表已创建。 6. 数据插入 SQL> insert into userinfo values(1, 'user1'); 已创建 1 行。 SQL> insert into userinfo values(2, 'user2'); 已创建 1 行。 SQL> insert into userinfo values(3, 'user3'); 已创建 1 行。 7. 数据查看 SQL> select * from userinfo; ID NAME ---------- ---------- 1 user1 2 user2 3 user3 8. 创建主键 SQL> alter table userinfo add constraint pk_userinfo_id primary key(id); 表已更改。 9. 创建外键 SQL> alter table classinfo add constraint fk_classinfo_userinfo_userId foreign key(userId) references userinfo(id); 表已更改。 10. 增加字段 SQL> alter table userinfo add age number; 表已更改。 11. 修改字段 alter table userinfo modify name varchar2(10); 表已更改。 12. 删除约束 SQL> alter table classinfo add constraint pk_id primary key(id); 表已更改。 SQL> alter table classinfo drop constraint pk_id; 表已更改。 13. 插入其他表中的数据 : SQL> create table tb1 as select * from userinfo; 表已创建。 14. 创建表分区 SQL> create table userinfo(id number , name varchar2(10), age number) 2 partition by range (age) 3 ( 4 partition p1 values less than (50), 5 partition p2 values less than (maxvalue) 6* ) SQL> / 表已创建。 SQL> select * from userinfo partition (p1); ID NAME AGE ---------- -------------------- ---------- 1 aa 20 SQL> select * from userinfo partition (p2); ID NAME AGE ---------- -------------------- ---------- 2 bb 50 3 cc 60 15. 创建同义词 [SQL> grant create public synonym to aa] system/manager SQL> create synonym mytba for aa.userinfo; 同义词已创建。 SQL> select * from mytba; 16. 创建序列 SQL> create sequence aa_seq 2 start with 1 3 increment by 1 4 maxvalue 50 5 minvalue 1 6 nocycle 7* cache 25 SQL> / 序列已创建。 SQL> insert into userinfo values(aa_seq.nextval, 'sa', aa_seq.nextval); 17. 创建视图 SQL> create table tb1(id number, name varchar2(10), className varchar2(10)); 表已创建。 SQL> create table tb2(id number, name varchar2(10)); 表已创建。 SQL> create view view_tb1_tb2 as 2 select tb1.name, tb2.name as ClassName from tb1, tb2 视图已建立。 18. 数据库的导出 CMD> exp Scott/tiger file=e:\DBFile 19. 数据库的导入 CMD> imp system/manager file=e:\DBFile.dmp ignore=y full=y 20. 触发器 SQL> create trigger tri 2 before insert on userinfo 3 for each row 4 begin 5 select seq1.nextval into :new.id from dual; 6 end; 7 / 触发器已创建 SQL> select seq1.nextval from dual; NEXTVAL ---------- 1 21.自动增长 1)create sequence test_id increment by 1 start with 1 nocache; 2)create or replace trigger test_trigger before insert on test for each row begin select test_id.nextval into :new.id from dual; end; / Oracle有关DBA_系统数据字典的相应信息数据: ********************************************************************************************************************************* 1. 常用数据字典 USER_ 记录用户对象的信息，如user_tables包含用户创建的所有表；user_views,user_constraints等; ALL_ 记录用户对象的信息及被授权访问的对象信息； DBA_ 记录数据库实例的所有对象的信息，如DBA_USERS包含数据库实例中所有用户的信息，DBA的信息包含user和all的信息； V$ 当前实例的动态视图，包含系统管理和优化使用的视图； GV_ 分布环境下所有实例的动态视图，包含系统管理和优化使用的视图，这里的GV表示 Global v$的意思;如当前分布环境下启动多个实例： 2. 基本的数据字典： DBA_TABLES 所有用户的所有表的信息； DBA_TAB_COLUMNS 所有用户的表的列(字段)信息； DBA_VIEWS 所有用户的所有视图信息； DBA_SYNONYMS 所有用户同义词信息； DBA_SEQUENCES 所有用户序列信息； DBA_CONSTRAINTS 所有用户的表约束信息； DBA_INDEXES 所有用户索引的简要信息； DBA_IND_COLUMNS 所有用户索引的列信息； DBA_TRIGGERS 所有用户触发器信息 ； DBA_SOURCE所有用户存储过程源代码信息； DBA_PROCEDUS 所有用户存储过程； DBA_SEGMENTS 所有用户段（表，索引，Cluster）使用空间信息； DBA_EXTENTS 所有用户段的扩展段信息； DBA_OBJECTS 所有用户对象的基本信息（包括素引，表，视图，序列等）； CAT 当前用户可以访问的所有的基表 ； TAB 当前用户创建的所有基表，视图，同义词等； DICT 构成数据字典的所有表的信息； 3. 与数据库组件相关的数据字典： 数据库： V$DATABASE 同义词 V_$DATABASE,记录系统的运行情况； 表空间： DBA_TABLESPACES 记录系统表空间的基本信息； DBA_DATA_FILES 记录系统数据文件及表空间的基本信息； DBA_FREE_SPACE 记录系统表空间的剩余空间的信息； 控制文件：V$CONTROLFILE 记录系统控制文件的路径信息； V$PARAMETER 记录系统各参数的基本信息； v$CONTROLFILE_RECORD_SECTION 记录系统控制运行的基本信息； 数据文件：DBA_DATA_FILES 记录系统数据文件及表空间的基本信息； v$DATAFILE 记录来自控制文件的数据文件信息； v$FILESTAT 记录数据文件读写的基本信息 ； 4. 查看控制文件 SQL> select name from v$controlfile; 5. 查看日志文件 SQL> select member from v$logfile; 6. 查看数据文件 SQL> select * from v$datafile; 7. 查看当前用户创建的表 SQL> select TABLE_NAME from user_tables;