ORACLE基本定义、操作语句

ORACLE基本定义、操作语句

一、           表

1．创建表

CREATE TABLE TAB_NAME

(

       COL_01  VARCHAR2(10) NOT NULL,

       COL_02  NUMBER(8,2),

       COL_03  DATE

);

2．添加主键约束

ALTER TABLE TAB_NAME ADD CONSTRAINT PK_COL_01 PRIMARY KEY(COL_01);

3．添加唯一性约束

ALTER TABLE TAB_NAME ADD CONSTRAINT UK_COL_02 UNIQUE(COL_02);

4．添加外键约束

ALTER TABLE TAB_NAME ADD CONSTRAINT FK_COL0_03 FOREIGN KEY(COL_03) REFERENCES TAB_2(COL_03);

5．添加check约束

ALTER TABLE TAB_NAME ADD CONSTRAINT CHK_COL_03 CHECK(COL_01 <> ‘ABC’);

6．创建索引

――创建唯一索引

CREATE UNIQUE INDEX IDX_NAME ON TAB_NAME(COL_01);

――创建非唯一索引

CREATE INDEX IDX_NAME ON TAB_NAME(COL_01);

7．给表添加一个新列

ALTER TABLE  TAB_NAME  ADD  COL_04  VARCHAR2(10);

8．修改列的数据类型

ALTER TABLE  TAB_NAME  MODIFY  COL_04  NUMBER(8);

9．删除一列

ALTER TABLE TAB_NAME DROP COLUMN COL_04;

10．更改表的名称

ALTER TABLE  TAB_NAME  RENAME  TO  TAB_NEW_NAME;

11．更改表的列名

ALTER TABLE TAB_NAME RENAME COLUMN COL_04 TO COL_05;

12．给表和列添加注释说明

--add comments to the table

COMMENT ON TABLE TAB_NAME IS ‘示例表’;

--add comments to the column

COMMENT ON COLUMN TAB_NAME.COL_01 IS ‘列名’;

13．删除表

DROP TABLE TAB_NAME;

14．MERGE

merge into tj_test1 tt1

using tj_test tt
on (tt1.id=tt.id)
when matched then
update set
tt1.name=tt.name,
tt1.age=tt.age
when not matched then
insert values(
tt.id,
tt.name,
tt.age)

alter   table   Tab_Name   modify(字段   varchar(20),   字段   varchar(50));

二、           视图

1．  创建视图

CREATE OR REPLACE VIEW  VW_NAME  AS

       SELECT A.COL_01,

                     A.COL_02,

                     A.COL_03,

                     B.COL_04

       FROM  TAB_ONE A,TAB_TWO B

WHERE  A.COL_01 = B.COL_01;

三、           序列

1．  创建序列

CREATE SEQUENCE SQ_NAME

                                    INCREMENT BY 10  --序列增量

                                    START WITH 20    --开始值

                                    MAXVALUE 1000    --最大值

                                    MINVALUE 20    --最小值

                                    NOCACHE  /*如果要指定cache给序列，应该写成CACHE N

                                                         其中N为要指定的数值*/

                                    NOCYCLE;   /*如果序列到了最大值还要继续取值，

                                                                就写成 CYCLE*/

2．  修改序列

ALTER SEQUENCE SQ_NAME

                                 INCREMENT BY 10  --序列增量

                                    START WITH 20    --开始值

                                    MAXVALUE 1000    --最大值

                                    MINVALUE 20    --最小值

                                    CACHE  5 

                                    CYCLE;  

注：如果想要按不同的值从新开始一个序列，该序列必须删掉重建

四、           同义词

1．  创建同义词

CREATE SYNONYM SY_NAME FOR TAB_NAME;

五、           函数

创建函数

       CREATE OR REPLACE FUNCTION FN_NAME([参数1]，[参数2]，[…..])

RETURN return_type

       AS

              --定义变量

       BEGIN

              --函数内容

              Return [返回值];

       END;

六、           存储过程

创建过程

       CREATE OR REPLACE PROCEDURE SP_NAME([参数1]，[参数2]，[…..])

       AS

              --定义变量

       BEGIN

              --过程内容

       END;

七、           触发器

创建过程

CREATE OR REPLACE TRIGGER TR_NAME

AFTER[BEFORE ] [INSERT/UPDATE/DROP] ON TAB_NAME

REFERENCING OLD AS old NEW AS new

FOR EACH ROW

DECLARE

       --定义变量

BEGIN

       --触发器内容

END;

八、           包和包体

创建包

CREATE OR REPLACE PACKAGE PK_NAME

AS

       --包内容

END PK_NAME;

创建保体

CREATE OR REPLACE PACKAGE BODY PK_NAME

AS

       --包体的内容

END PK_NAME;

九、           用户

1．创建一个用户

CREATE USER USR_NAME

IDENTIFIED BY PASSWORD

DEFAULT TABLESPACE TAB_SPACE_NAME

TEMPORARY TABLESPACE TMP_TABSPACE_NAME

PROFILE DEFAULT;

2．给该用户授权

GRANT 权限类型 TO USR_NAME;

3．撤销用户权限

REVOKE权限类型 FROM USR_NAME;

4．修改用户密码

ALTER USER USR_NAME IDENTIFIED BY NEW_PSD;

5．设定用户缺省的角色

ALTER USER USR_NAME DEFAULT ROLE ROLE_NAME;

设定缺省角色后，用户登陆后就只有该缺省的角色权限了。

这时可以使用下列命令来让其它角色权限生效(激活角色)：

SET ROLE ROLE_NAME IDENTIFIED BY  PWD;

6．给用户授予表空间使用权限

alter user user_name quota unlimited on tablespace_name;

十、           角色

1．创建一个角色

CREATE ROLE ROLE_NAME IDENTIFIED BY PWD;

2．给角色授权

GRANT 权限类型 TO ROLE_NAME  [WITH  ADMIN  OPTION];

3．撤销角色权限

REVOKE 权限类型 FROM ROLE_NAME;

4．取消角色的密码

ALTER ROLE  ROLE_NAME  NOT  IDENTIFIED;

5．设置当前用户要生效的角色

(注：角色的生效是一个什么概念呢？假设用户a有b1,b2,b3三个角色，那么如果b1未生效，则b1所包含的权限对于a来讲是不拥有的，只有角色生效了，角色内的权限才作用于用户，最大可生效角色数由参数MAX_ENABLED_ROLES设定；在用户登录后，oracle将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。）
sql>set role role1;//使role1生效
sql>set role role,role2;//使role1,role2生效
sql>set role role1 identified by password1;//使用带有口令的role1生效
sql>set role all;//使用该用户的所有角色生效
sql>set role none;//设置所有角色失效
sql>set role all except role1;//除role1外的该用户的所有其它角色生效。
sql>select * from SESSION_ROLES;//查看当前用户的生效的角色。

6．修改指定用户，设置其默认角色
sql>alter user user1 default role role1;
sql>alter user user1 default role all except role1;

十一、表空间

       1．创建表空间

       drop tablespace tabspace_name INCLUDING CONTENTS;

CREATE TABLESPACE tabspace_name DATAFILE ‘/home/oracle/oradata/lgdb/ tabspace_name.dbf‘ SIZE 100M REUSE

    AUTOEXTEND ON MAXSIZE UNLIMITED

    DEFAULT STORAGE(INITIAL 320K

               NEXT 320K

               MINEXTENTS 1

               MAXEXTENTS UNLIMITED

               PCTINCREASE 0);

       临时表空间：

       CREATE temporary TABLESPACE "TEMP03"

    tempfile ‘/home/oracletg/odspxdb/oradata/odspx/temp03.dbf‘

    SIZE 4000M reuse

2．增加数据文件的大小

ALTER DATABASE DATAFILE  ‘/home/oracle/oradata/lgdb/ tabspace_name.dbf‘ RESIZE 500M;

3．给表空间添加数据文件

ALTER TABLESPACE tabspace_name ADD DATAFILE ‘/home/oracle/oradata/lgdb/ tabspace_name2.dbf‘ SIZE 100M ONLINE;

SQLPLUS中常用的set参数

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -

STYLE TYPE=‘TEXT/CSS‘><!--BODY {background: ffffc6} --></STYLE>"

SET ECHO OFF

SPOOL employee.htm">

SELECT FIRST_NAME, LAST_NAME, SALARY

FROM EMP_DETAILS_VIEW

WHERE SALARY>12000;

SPOOL OFF

SET MARKUP HTML OFF

SET ECHO ON

常用的数据字典及SQL

Oracle中的数据字典有静态和动态之分。静态数据字典主要是在用户访问数据字典时不会发生改变的，但动态数据字典是依赖数据库运行的性能的，反映数据库运行的一些内在信息，所以在访问这类数据字典时往往不是一成不变的。以下分别就这两类数据字典来论述。

1． 静态数据字典

这类数据字典主要是由表和视图组成，应该注意的是，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*。

user_*

该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）

all_*

该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）

dba_*

该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）

从上面的描述可以看出，三者之间存储的数据肯定会有重叠，其实它们除了访问范围的不同以外（因为权限不一样，所以访问对象的范围不一样），其他均具有一致性。具体来说，由于数据字典视图是由SYS（系统用户）所拥有的，所以在却省情况下，只有SYS和拥有DBA系统权限的用户可以看到所有的视图。没有DBA权限的用户只能看到user_*和all_*视。如果没有被授予相关的SELECT权限的话，他们是不能看到 dba_*视图的。

由于三者具有相似性，下面以user_为例介绍几个常用的静态视图：

user_users视图

主要描述当前用户的信息，主要包括当前用户名、帐户id、帐户状态、表空间名、创建时间等。例如执行下列命令即可返回这些信息。
select * from user_users

user_tables视图

主要描述当前用户拥有的所有表的信息，主要包括表名、表空间名、簇名等。通过此视图可以清楚了解当前用户可以操作的表有哪些。执行命令为：select * from user_tables

user_objects视图

主要描述当前用户拥有的所有对象的信息，对象包括表、视图、存储过程、触发器、包、索引、序列等。

user_tab_privs视图

该视图主要是存储当前用户下对所有表的权限信息。

2．  动态数据字典

Oracle包含了一些潜在的由系统管理员如SYS维护的表和视图，由于当数据库运行的时候它们会不断进行更新，所以称它们为动态数据字典（或者是动态性能视图）。这些视图提供了关于内存和磁盘的运行情况，所以我们只能对其进行只读访问而不能修改它们。

Oracle中这些动态性能视图都是以v$开头的视图。

v$access

该视图显示数据库中锁定的数据库对象以及访问这些对象的会话对象（session对象）。

v$session

该视图列出当前会话的详细信息。

v$active_instance

该视图主要描述当前数据库下的活动的实例的信息。

v$context

该视图列出当前会话的属性信息。比如命名空间、属性值等。

查看哪些表被锁和锁的模式

select   p.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name,b.locked_mode

from   v$process   p,v$session   a,   v$locked_object   b,all_objects   c  

where   p.addr=a.paddr   and   a.process=b.process   and   c.object_id=b.object_id

或

SELECT  S.SID SESSION_ID, S.USERNAME,

DECODE(LMODE, 0, ‘None‘, 1, ‘Null‘, 2, ‘Row-S (SS)‘, 3, ‘Row-X (SX)‘,  4, ‘Share‘, 5, ‘S/Row-X (SSX)‘,  6, ‘Exclusive‘, TO_CHAR(LMODE)) MODE_HELD,

DECODE(REQUEST, 0, ‘None‘, 1, ‘Null‘, 2, ‘Row-S (SS)‘, 3, ‘Row-X (SX)‘, 4, ‘Share‘, 5, ‘S/Row-X (SSX)‘, 6, ‘Exclusive‘, TO_CHAR(REQUEST)) MODE_REQUESTED,

O.OWNER||‘.‘||O.OBJECT_NAME||‘ (‘||O.OBJECT_TYPE||‘)‘, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2

FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S

WHERE L.SID = S.SID AND  L.ID1 = O.OBJECT_ID

或

select b.owner,b.object_name,l.session_id,l.locked_mode

from v$locked_object l, dba_objects b

where b.object_id=l.object_id

ORACLE锁有一下几种模式：

0：none

1：null 空

2：Row-S 行共用(RS)：共用表锁，sub share 

3：Row-X 行独占(RX)：用于行的修改，sub exclusive 

4：Share 共用锁(S)：阻止其他DML操作，share

5：S/Row-X 共用行独占(SRX)：阻止其他事务操作，share/sub exclusive 

6：exclusive 独占(X)：独立访问使用，exclusive

如果锁长期没有释放，我们可以杀掉该锁所在的进程

alter system kill session ‘sid,serial#‘;

3．  查询TEMP空间的使用

临时表空间曾经最大使用情况：

select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,used_space "used_space(m)", round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",nvl(free_space, 0) "free_space(m)" from (select tablespace_name,round(sum(bytes) / (1024 * 1024), 2) space,sum(blocks) blocks from dba_temp_files group by tablespace_name) d,(select tablespace_name,round(sum(bytes_used) / (1024 * 1024), 2) used_space, round(sum(bytes_free) / (1024 * 1024), 2) free_space from v$temp_space_header group by tablespace_name) f where d.tablespace_name = f.tablespace_name(+)

当前的临时表空间使用情况：

select sum(blocks*8192)/1024/1024 from v$sort_usage