ORACLE基本SQL语句

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参数

参数名 appinfo arraysize autocommit 一次可以取的行的数目。1－5000，即系统查询出多少行后将结果返回给用户 用于在操作中是自动提交：ON/IMMEDIATE 或是部分提交 N 或是不自动提交 OFF N 表示允许在用户发出COMMIT后，可以提交的执行命令数量（将n条DML语句所做的改进结果进行提交，等n＋1、n＋2…..条语句产生的结果将不提交） 自动打印变量的值。 如在sqlplus中定义了一个变量，在这些过程时引用了该变量，执行完过程后终端自动打印该变量的值。 在做备份恢复的时候有用 设置自动跟踪DML语句生成执行计划的参数：ON 打开自动跟踪，OFF关闭，TRACEONLY 只生成执行计划而不返回执行结果 Enter the BLOCKTERMINATOR to stop SQL 说明 autoprint autorecovery autotrace blockterminator

command entry without running the SQL command btitle cmdsep 页脚标题 用于将多个sqlplus命令连接在一行的连接符号 如：SQL>set cmddep + SQL> TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999 用于设置查询结果中各列之间的间隔符号 如：SQL>set colsep ‘|’ 执行一个查询语句后，显示的结果中各列以’|’字符隔开 指定oracle sql的语法分析器的版本 SET COM[PATIBILITY]{V7|V8|NATIVE} 用于想要在define的变量之后连接其他的字符串时使用 如：SQL>define str=’a’ SQL>select ‘&str’ from dual; 执行结果如下： ' ---- .a SQL>set concat , SQL>select ‘&str,AA’ from dual; 执行结果如下： 'AA ---- aaa 设置copy命令执行后，多少行数据开始commit Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command. This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE. 定义变量时使用的命令 用于显示表和视图的结构,同义词,或指定函数和过程的详述 colsep compatibility concat copycommit COPYTYPECHECK define describe echo 命令文件执行时，是否在终端上显示命令本身：ON/OFF 如：SQL>start d:\\a.sql Echo 为on则在终端上显示a.sql中的命令 设定edit默认情况下使用的文件名，文件名的长度和操作系统允许值相同 Controls where on a page each report begins. OFF forces each report to start at the top of a editfile embedded

new page. ON allows a report to begin anywhere on a page escape 设置换码符号 ESC[APE] {\\|c|ON|OFF} 如为ON则换码符号默认设置为’\\’ flagger 判断命令是否符合ANSI/ISO SQL92标准，和alter session set flagger=full命令相同 设定向用户显示设备输出数据的开关。设为OFF则缓存在操作系统的buffer中，设定为ON则操作系统无法缓存。一般在非对话形式或者使用command file，不需要确认输出结果时设定为关闭。设定为OFF也可以减少I/O发生，提高性能。 控制列名信息的显示：ON/OFF 设置让列换行显示的标识符 如：SQL>set headsep ‘\\’ SQL>col system_id heading ‘system\\id’ SQL>select system_id from src2stdwar; 显示结果如下： System Id ---------- 6 7 设置系统默认的实例 如：SQL>disconnect SQL> SET INSTANCE LGDB LGDB为数据库一个实例 要设置回系统默认的实例 SQL>set instance local 设置行的最大显示位数 显示当前的行数 设置clob 数据类型的显示长度，超过将换行显示或截取 指定本地的log file给恢复使用，默认的值是oracle初始化文件init.ora中LOG_ARCHIVE_DEST的值，set logsource后面不带参数，将设置回默认的值 长类型显示的字节数，默认的是80个字符 值必须小于Maxdata的值 SQL>show Maxdata 设置增量的字节数 设置HTML输出到文本 如： flush heading headsep instance linesize lno loboffset logsource long longchunksize markup

SET MARKUP HTML ON SPOOL ON HEAD \" - STYLE TYPE='TEXT/CSS'>\" 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 newpage null 设置每页的开头空白行 设置结果为null时显示的值 如：set null ‘no data’ 当查询结果中有空值时就显示no data Sets the default format for displaying numbers Sets the default width for displaying numbers 设置每页显示的行数 暂停 ON/OFF/TEXT 为ON时当报表超过一页时，每显示一屏就暂停显示，等待用户打回车键，再接着显示；OFF：页于页不停顿，TEXT：页于页停顿，并向用户提示信息 页数 RECSEP tells SQL*Plus where to make the record separation. For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator. Defines the character to display or print to separate records 使终端上显示dbms_output包中put_line()过程的执行结果 将执行结果输出到文件： numformat numwidth pagesize PAUSE pno recsep recsepchar release repfooter repheader serveroutput shiftinout showmode spool

Spool d:\\a.sql Select * from tab_name ; Spool off –停止输出 Spool out SPOOL OUT关闭该文件并在系统缺省的打印机上输出 sqlblanklines sqlcase sqlcode sqlcontinue sqlnumber sqlpluscompatibility sqlprefix sqlprompt sqlterminator suffix tab termout time timing trimout trimspool ttitle underline USER verify wrap feedback 允许有空白行在DML语句中ON/OFF 用于设置在屏幕上显示或不显示所输出的信息 是否显示DML语句的执行时间：ON/OFF 页头标题 显示当前的用户名 控制换行：ON 换行显示，OFF超过长度不换行而截取掉多余的长度 查询结束时给出结果、记录数的信息ON/OFF 如：执行select * from tab_name后 在后面显示：已选择2行。 常用的数据字典及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)\

sum_blocks,used_space \"used_space(m)\* 100, 2) \"used_rate(%)ablespace_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