Oracle基础

来源：互联网发布：rpc框架源码编辑：程序博客网时间：2024/04/28 21:09

用户－方案

用户与方案是一一对应的，但建完用户时，在方案中并不能马上看到对应的方案，是因为该用户的方案对象还没有建立，你可以在方案中的任何用户方案上点右键，进行”创建“－－表，注意指明所属用户，就可。也可以用脚本建一下刚新建的用户的表，就可看到。

一个表空间只能属于一个数据库
　　每个数据库最少有一个控制文件（建议3个，分别放在不同的磁盘上）
　　每个数据库最少有一个表空间（SYSTEM表空间）
　　建立SYSTEM表空间的目的是尽量将目的相同的表存放在一起，以提高使用效率
　　每个数据库最少有两个联机日志文件
　　一个数据文件只能属于一个表空间
　　一个数据文件一旦被加入到一个表空间中，就不能再从这个表空间中移走，也不能再加入到其他表空间中建立新的表空间需要建立新的数据文件
　　数据文件被ORACLE格式化为ORACLE块，ORACLE块的大小是在第一次创建数据库时设定的，并且以后不能改变
　　单独一个事务不能跨越多个回滚段
　　索引表不含ROWID值
　　拥有不同大小的回滚段没有任何益处
　　ORACLE在初始安装时建立一个缺省数据库，实例名为ORCL
　　一个块的最大长度为16KB(有2K、4K、8K、16K)
　　每个数据库最大文件数（按块大小）
　　2K块　　　　　　　　20000个文件
　　4K块　　　　　　　　40000个文件
　　8K块或以上　　　　　65536个文件
　　oracle server可以同时启动多个数据库
　　一套操作系统上只能安装一个版本的ORACLE数据库系统
　　一套ORACLE数据库系统中可以有多个ORACLE数据库
　　每个ORACLE数据库拥有一个数据库实例（INSTANCE）
　　所以，一套操作系统上同时可以有多个oracle数据库实例启动。

Condition   : Windows XP Professional SP2 + Oracle 9i(9.2.0.1.0)
Description : Oracle 9i 开发代码
Explain     : 全文关键字均为大写，--...及/*...*/为注释。下面是Oracle 9i默认安装的用户
       system/manager          --SYSDBA 或 NORMAL ,默认系统系统管理员
       sys/change_on_install   --SYSDBA 或 NORMAL ,默认系统系统管理员
       scott/tiger       --NORMAL 普通用户
---------------------------------------------------------------------------------------------*/

--[1]// Oracle Basic 1
---------------------------------------------------------------------------------------------//
--可在登陆时转换登陆身份(转换为system登陆)
CONNECT system/Manager

--回滚事务（返回上一次操作）
--ROLLBACK;

--提交事务
COMMIT;

--查看表结构
DESC table_name;

--查询表中数据
SELECT * FROM table_name;
--或指定需返回的字段
SELECT id,name FROM table_name;

--显示当前用户
SHOW USER;

--查看当前用户所拥有的表的结构
DESC USER_TABLES;
--查看当前用户所拥有的表
SELECT table_name FROM USER_TABLES;

--查看当前用户能够访问的表的结构
DESC ALL_TABLES;
--查看当前用户能够访问的表
SELECT table_name FROM ALL_TABLES;

--查看整个数据库所拥有的表的结构
DESC DBA_TABLES;
--查看整个数据库所拥有的表
SELECT table_name FROM DBA_TABLES;

--查看数据库版本
SELECT * FROM V$VERSION;

--查看数据库实例名称
SELECT instance_name FROM V$INSTANCE;

--查看数据文件的物理位置
SELECT * FROM V$DATAFILE;

--查看日志文件的物理位置
SELECT * FROM V$LOGFILE;

--查看控制文件的物理位置
SELECT * FROM V$CONTROLFILE;

--查看表空间的信息
SELECT * FROM V$TABLESPACE;

--查看数据库参数信息
--下面列出几种参数
--简单查询方法
SHOW PARAMETER DB_BLOCK_SIZE;
SHOW PARAMETER NLS_LANGUAGE;
SHOW PARAMETER JAVA_POOL_SIZE;
SHOW PARAMETER SORT_AREA_SIZE;
--通过视图查询
SELECT * FROM V$PARAMETER WHERE NAME='JAVA_POOL_SIZE';
SELECT name,value FROM V$PARAMETER WHERE NAME='JAVA_POOL_SIZE';
--修改参数值
--!UPDATE V$PARAMETER SET VALUE='500000' WHERE name='JAVA_POOL_SIZE';

--附注-------------------------------------------------------------//
SPOOL d:/file.txt;    --联机
SPOOL OFF;            --关闭（将联机后查询到的数据保存在d盘file.txt文件下）

--命令方式打开和关闭数据库
--以sysdba身份登陆数据库
CONNECT /AS SYSDBA;
--关闭数据库
SHUTDOW IMMEDIATE;
--启动数据库
STARTUP;

--清除屏幕
CLEAR SCREEN;

--预定义格式打印系统当前日期
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

--可以利用脚本执行SQL语句（d:/test.sql）
@d:/test.sql;

--编辑当前SQL语句
EDIT;

--保存当前SQL语句
SAVE d:/test.sql;

--退出数据库
EXIT;
------------------------------------------------------------------------------------------End//

--[2]// Oracle Basic 2
---------------------------------------------------------------------------------------------//
--用户-------------------------------------------------------------//
--（注：一定要以DBA身份的用户登陆才能完成下列操作）
--查看数据库已有的用户信息
SELECT * FROM DBA_USERS;

--创建新用户（用户名：laozhao ，密码：laozhao）
CREATE USER laozhao IDENTIFIED BY laozhao;
--删除用户
DROP USER laozhao;
--用户授权（赋予CONNECT,RESOURCE角色），不授权，用户将不可用
GRANT CONNECT,RESOURCE TO laozhao;

--角色-------------------------------------------------------------//
--（注：一定要以DBA身份的用户登陆才能完成下列操作）
--查看数据库已有角色
SELECT * FROM DBA_ROLES;

--创建角色
CREATE ROLES role_name;
--删除角色
DROP ROLES role_name;
--为新建角色赋予已有的角色权限
GRANT CONNECT TO role_name;
--为新建角色赋予student表的SELECT权限
GRANT SELECT ON student TO role_name;

--权限-------------------------------------------------------------//
--查询所有系统权限
SELECT PRIVILEGE FROM DBA_SYS_PRIVS;

--查看当前用户的系统权限
SELECT * FROM USER_SYS_PRIVS;
--查看当前用户的对象权限
SELECT * FROM USER_TAB_PRIVS;

--锁---------------------------------------------------------------//
--表一旦锁住，别的用户将不可有（增、删、改）操作
--表级共享锁（多用户可以放置）
LOCK TABLE emp IN SHARE MODE;
--表级排它锁（单个用户放置）
LOCK TABLE emp IN EXCLUSIVE MODE;
--行锁
--在执行DML语句时，Oracle会自动为执行行添加行级锁

--创建表-----------------------------------------------------------//
--下面一张学院资料表，一张得分表
--表1
CREATE TABLE student
 (
 id NUMBER PRIMARY KEY,  --主键约束
 name VARCHAR2(20),
 sex VARCHAR2(3),
 age NUMBER,
 address VARCHAR2(50)
 );
--表2
CREATE TABLE score
 (
 id NUMBER NOT NULL,  --非空
 clanguage NUMBER(5,2),  --共五位数字，小数点占用两位
 java      NUMBER(5,2),
 vb        NUMBER(5,2),
 asp       NUMBER(5,2)
 );

--为上面创建的表插入数据
INSERT INTO student
 VALUES('2','JianLi','F','20','ShangHaiShi YangPu');
--也可为指定列插入
INSERT INTO student(id,address)
 VALUES('1','AnHui SuZhou');

--删除表内指定数据
DELETE student WHERE id=1;
--删除表
DROP TABLE student;

--查询语句(查询所有)
SELECT * FROM student
--查询语句(查询指定列)
SELECT id,address FROM student;
--可指定“用户名.表名”查询，如想查询scott用户下的emp表，可写为（注：权限低的用户查询不到权限高的用户表）
SELECT * FROM scott.emp;

--序列-------------------------------------------------------------//
--创建序列(创建名为stu_id的序列)
CREATE SEQUENCE stu_id
INCREMENT BY 1   --对上一数字的增量(可为负数)
START WITH 1     --序列起始值
NOMAXVALUE       --指定序列最大值，或无最大值(设置最大值只需去除no，并在其后加入最大值即可)
NOMINVALUE       --指定序列最小值，或无最小值
NOCYCLE          --指定到达最大或最小值时是否循环处理
ORDER            --保证产生的序列是按照一定的顺序(不保证可在前加入no，默认为NOORDER)
CACHE 10;        --指定在内存中可使用的序列个数，(当内存中序列用完时再生成下一批序列放入内存，以提高效率)

--查询内存中剩余的序列
SELECT stu_id.NEXTVAL FROM student;
--使用序列(NEXTVAL,CURRVAL)
--插入数据时(NEXTVAL)
INSERT INTO student(id,address)
 VALUES(stu_id.NEXTVAL,'AnHui SuZhou');
--使用当前值(CURRVAL)
INSERT INTO student(id,name)
 VALUES(stu_id.CURRVAL,'zhao');

--删除序列
DROP SEQUENCE stu_id;

--同义词-----------------------------------------------------------//
--创建同义词(公有),创建了student 的同义词 stu
CREATE PUBLIC SYNONYM stu FOR student;
--创建同义词(私有)
CREATE SYNONYM stu FOR student;

--删除同义词(注：删除时要指定公有还是私有)
DROP PUBLIC SYNONYM stu;
/*
  可把同义词(synonym)或表(table)、视图(VIEW)、快照(snapshot)、序列(sequence)、过程(procedure)、
  函数(function)、包(package)、对象类型(object type)从别的用户那里引用过来创建属于自己的，如要
  引用system用户的stu同义词（注：权限低的用户不能操作权限高的用户
*/
CREATE SYNONYM stu FOR system.stu;

--视图-------------------------------------------------------------//
--创建一个简单的视图
CREATE VIEW stu_info
 AS
 SELECT id,name FROM student;

--创建一个拥有两张（上述：Student、scort）表的视图
CREATE VIEW stu_Java
 AS
 SELECT student.id,name,java FROM student,score
 WHERE student.id = score.id;  --视图连接条件，必选

--删除试图
DROP VIEW stu_info;

--索引-------------------------------------------------------------//
--创建示例表
CREATE TABLE students
(
 id NUMBER,
 name VARCHAR2(20),
 sex char(1),
 age NUMBER
);
--BTree索引
CREATE INDEX ind_id ON students(id);
--位图索引
CREATE BITMAP INDEX ind_sex ON students(sex);
--反向键索引
CREATE INDEX ind_age ON students(age) REVERSE;
--函数索引
CREATE INDEX ind_name ON students(lower(name));

--查看所创建的索引信息
SELECT index_name,index_type FROM USER_INDEXES WHERE table_name='STUDENTS';

--删除索引
DROP INDEX ind_id;

--附注-------------------------------------------------------------//
--创建概要（配置）文件
CREATE PROFILE
 appl_profile
LIMIT
 SESSIONS_PER_USER               2  --
 CPU_PER_SESSION             10000  --hunderth of seconds
 CPU_PER_CALL                    1  --hunderth of seconds
 CONNECT_TIME            UNLIMITED  --minutes
 IDLE_TIME                      30  --minutes
 LOGICAL_READS_PER_SESSION DEFAULT  --db bLOCKs
 LOGICAL_READS_PER_CALL    DEFAULT  --db bLOCKs
 PRIVATE_SGA                   20M  --
 FAILED_LOGIN_ATTEMPTS           3  --
 PASSWORD_LIFE_TIME             30  --days
 PASSWORD_REUSE_TIME            12  --days
 PASSWORD_REUSE_MAX      UNLIMITED  --
 PASSWORD_LOCK_TIME        DEFAULT  --days
 PASSWORD_GRACE_TIME             2  --days
 PASSWORD_VERIFY_FUNCTION     NULL  --
/

--修改概要（配置）文件
ALTER PROFILE appl_profile LIMIT
 CPU_PER_CALL DEFAULT;
--删除概要（配置）文件
DROP PROFILE appl_profile;
--如要删除的概要（配置）文件已经分配给用户，则必须利用下面语句进行删除
DROP PROFILE appl_profile CASCADE;

--在创建用户时可以指定概要（配置）文件，如不指定，则使用默认概要（配置）文件
CREATE USER zhao IDENTIFIED BY zhao PROFILE appl_profile;
--如需要改变用户的概要（配置）文件
ALTER USER user_name PROFILE perfile_name;

--DBA用户可以修改默认概要（配置）文件，但任何用户都不能删除概要（配置）文件
ALTER PROFILE DEFAULT LIMIT CPU_PER_CALL 2;
------------------------------------------------------------------------------------------End//