ORACLE基本定义、操作语句

一、表

1．创建表

CREATETABLETAB_NAME

(

COL_01VARCHAR2(10)NOTNULL,

COL_02NUMBER(8,2),

COL_03DATE

);

2．添加主键约束

ALTERTABLETAB_NAMEADDCONSTRAINTPK_COL_01PRIMARYKEY(COL_01);

3．添加唯一性约束

ALTERTABLETAB_NAMEADDCONSTRAINTUK_COL_02UNIQUE(COL_02);

4．添加外键约束

ALTERTABLETAB_NAMEADDCONSTRAINTFK_COL0_03FOREIGNKEY(COL_03)REFERENCESTAB_2(COL_03);

5．添加check约束

ALTERTABLETAB_NAMEADDCONSTRAINTCHK_COL_03CHECK(COL_01<>‘ABC’);

6．创建索引

――创建唯一索引

CREATEUNIQUEINDEXIDX_NAMEONTAB_NAME(COL_01);

――创建非唯一索引

CREATEINDEXIDX_NAMEONTAB_NAME(COL_01);

7．给表添加一个新列

ALTERTABLETAB_NAMEADDCOL_04VARCHAR2(10);

8．修改列的数据类型

ALTERTABLETAB_NAMEMODIFYCOL_04NUMBER(8);

9．删除一列

ALTERTABLETAB_NAMEDROPCOLUMNCOL_04;

10．更改表的名称

ALTERTABLETAB_NAMERENAMETOTAB_NEW_NAME;

11．更改表的列名

ALTERTABLETAB_NAMERENAMECOLUMNCOL_04TOCOL_05;

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

--addcommentstothetable

COMMENTONTABLETAB_NAMEIS‘示例表’;

--addcommentstothecolumn

COMMENTONCOLUMNTAB_NAME.COL_01IS‘列名’;

13．删除表

DROPTABLETAB_NAME;

14．MERGE

mergeintotj_test1tt1

usingtj_testtt
on(tt1.id=tt.id)
whenmatchedthen
updateset
tt1.name=tt.name,
tt1.age=tt.age
whennotmatchedthen
insertvalues(
tt.id,
tt.name,
tt.age)

altertableTab_Namemodify(字段varchar(20),字段varchar(50));

二、视图

1．创建视图

CREATEORREPLACEVIEWVW_NAMEAS

SELECTA.COL_01,

A.COL_02,

A.COL_03,

B.COL_04

FROMTAB_ONEA,TAB_TWOB

WHEREA.COL_01=B.COL_01;

三、序列

1．创建序列

CREATESEQUENCESQ_NAME

INCREMENTBY10--序列增量

STARTWITH20--开始值

MAXVALUE1000--最大值

MINVALUE20--最小值

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

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

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

就写成CYCLE*/

2．修改序列

ALTERSEQUENCESQ_NAME

INCREMENTBY10--序列增量

STARTWITH20--开始值

MAXVALUE1000--最大值

MINVALUE20--最小值

CACHE5

CYCLE;

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

四、同义词

1．创建同义词

CREATESYNONYMSY_NAMEFORTAB_NAME;

五、函数

创建函数

CREATEORREPLACEFUNCTIONFN_NAME([参数1]，[参数2]，[…..])

RETURNreturn_type

AS

--定义变量

BEGIN

--函数内容

Return[返回值];

END;

六、存储过程

创建过程

CREATEORREPLACEPROCEDURESP_NAME([参数1]，[参数2]，[…..])

AS

--定义变量

BEGIN

--过程内容

END;

七、触发器

创建过程

CREATEORREPLACETRIGGERTR_NAME

AFTER[BEFORE][INSERT/UPDATE/DROP]ONTAB_NAME

REFERENCINGOLDASoldNEWASnew

FOREACHROW

DECLARE

--定义变量

BEGIN

--触发器内容

END;

八、包和包体

创建包

CREATEORREPLACEPACKAGEPK_NAME

AS

--包内容

ENDPK_NAME;

创建包体

CREATEORREPLACEPACKAGEBODYPK_NAME

AS

--包体的内容

ENDPK_NAME;

九、用户

1．创建一个用户

CREATEUSERUSR_NAME

IDENTIFIEDBYPASSWORD

DEFAULTTABLESPACETAB_SPACE_NAME

TEMPORARYTABLESPACETMP_TABSPACE_NAME

PROFILEDEFAULT;

2．给该用户授权(常用权限有：connectresource)

GRANT权限类型TOUSR_NAME;

3．撤销用户权限

REVOKE权限类型FROMUSR_NAME;

4．修改用户密码

ALTERUSERUSR_NAMEIDENTIFIEDBYNEW_PSD;

5．设定用户缺省的角色

ALTERUSERUSR_NAMEDEFAULTROLEROLE_NAME;

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

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

SETROLEROLE_NAMEIDENTIFIEDBYPWD;

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

alteruseruser_namequotaunlimitedontablespace_name;

十、角色

1．创建一个角色

CREATEROLEROLE_NAMEIDENTIFIEDBYPWD;

2．给角色授权

GRANT权限类型TOROLE_NAME[WITHADMINOPTION];

3．撤销角色权限

REVOKE权限类型FROMROLE_NAME;

4．取消角色的密码

ALTERROLEROLE_NAMENOTIDENTIFIED;

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

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

6．修改指定用户，设置其默认角色
sql>alteruseruser1defaultrolerole1;
sql>alteruseruser1defaultroleallexceptrole1;

十一、表空间

1．创建表空间

droptablespacetabspace_nameINCLUDINGCONTENTS;

CREATETABLESPACEtabspace_nameDATAFILE'/home/oracle/oradata/lgdb/tabspace_name.dbf'SIZE100MREUSE

AUTOEXTENDONMAXSIZEUNLIMITED

DEFAULTSTORAGE(INITIAL320K

NEXT320K

MINEXTENTS1

MAXEXTENTSUNLIMITED

PCTINCREASE0);

临时表空间：

CREATEtemporaryTABLESPACE"TEMP03"

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

SIZE4000Mreuse

2．增加数据文件的大小

ALTERDATABASEDATAFILE'/home/oracle/oradata/lgdb/tabspace_name.dbf'RESIZE500M;

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

ALTERTABLESPACEtabspace_nameADDDATAFILE'/home/oracle/oradata/lgdb/tabspace_name2.dbf'SIZE100MONLINE;

SQLPLUS中常用的set参数

参数名	说明
appinfo
arraysize	一次可以取的行的数目。1－5000，即系统查询出多少行后将结果返回给用户
autocommit	用于在操作中是自动提交：ON/IMMEDIATE或是部分提交N或是不自动提交OFF N表示允许在用户发出COMMIT后，可以提交的执行命令数量（将n条DML语句所做的改进结果进行提交，等n＋1、n＋2…..条语句产生的结果将不提交）
autoprint	自动打印变量的值。 如在sqlplus中定义了一个变量，在这些过程时引用了该变量，执行完过程后终端自动打印该变量的值。
autorecovery	在做备份恢复的时候有用
autotrace	设置自动跟踪DML语句生成执行计划的参数：ON打开自动跟踪，OFF关闭，TRACEONLY只生成执行计划而不返回执行结果
blockterminator	EntertheBLOCKTERMINATORtostopSQLcommandentrywithoutrunningtheSQLcommand
btitle	页脚标题
cmdsep	用于将多个sqlplus命令连接在一行的连接符号如：SQL>setcmddep+ SQL>TTITLELEFT'SALARIES'+COLUMNSALARYFORMAT$99,999
colsep	用于设置查询结果中各列之间的间隔符号 如：SQL>setcolsep‘|’ 执行一个查询语句后，显示的结果中各列以’|’字符隔开
compatibility	指定oraclesql的语法分析器的版本 SETCOM[PATIBILITY]{V7|V8|NATIVE}
concat	用于想要在define的变量之后连接其他的字符串时使用 如：SQL>definestr=’a’ SQL>select‘&str’fromdual; 执行结果如下： ' ---- .a SQL>setconcat, SQL>select‘&str,AA’fromdual; 执行结果如下： 'AA ---- aaa
copycommit	设置copy命令执行后，多少行数据开始commit
COPYTYPECHECK	SetsthesuppressionofthecomparisonofdatatypeswhileinsertingorappendingtotableswiththeCOPYcommand.ThisistofacilitatecopyingtoDB2,whichrequiresthataCHARbecopiedtoaDB2DATE.
define	定义变量时使用的命令
describe	用于显示表和视图的结构,同义词,或指定函数和过程的详述
echo	命令文件执行时，是否在终端上显示命令本身：ON/OFF如：SQL>startd:\a.sql Echo为on则在终端上显示a.sql中的命令
editfile	设定edit默认情况下使用的文件名，文件名的长度和操作系统允许值相同
embedded	Controlswhereonapageeachreportbegins.OFFforceseachreporttostartatthetopofanewpage.ONallowsareporttobeginanywhereonapage
escape	设置换码符号 ESC[APE]{\|c|ON|OFF}如为ON则换码符号默认设置为’\’
flagger	判断命令是否符合ANSI/ISOSQL92标准，和altersessionsetflagger=full命令相同
flush	设定向用户显示设备输出数据的开关。设为OFF则缓存在操作系统的buffer中，设定为ON则操作系统无法缓存。一般在非对话形式或者使用commandfile，不需要确认输出结果时设定为关闭。设定为OFF也可以减少I/O发生，提高性能。
heading	控制列名信息的显示：ON/OFF
headsep	设置让列换行显示的标识符 如：SQL>setheadsep‘\’ SQL>colsystem_idheading‘system\id’ SQL>selectsystem_idfromsrc2stdwar; 显示结果如下： System Id ---------- 6 7
instance	设置系统默认的实例 如：SQL>disconnect SQL>SETINSTANCELGDB LGDB为数据库一个实例 要设置回系统默认的实例 SQL>setinstancelocal
linesize	设置行的最大显示位数
lno	显示当前的行数
loboffset	设置clob数据类型的显示长度，超过将换行显示或截取
logsource	指定本地的logfile给恢复使用，默认的值是oracle初始化文件init.ora中LOG_ARCHIVE_DEST的值，setlogsource后面不带参数，将设置回默认的值
long	长类型显示的字节数，默认的是80个字符 值必须小于Maxdata的值 SQL>showMaxdata
longchunksize	设置增量的字节数
markup	设置HTML输出到文本如： SETMARKUPHTMLONSPOOLONHEAD"<TITLE>SQL*PlusReport</title>- STYLETYPE='TEXT/CSS'><!--BODY{background:ffffc6}--></STYLE>" SETECHOOFF SPOOLemployee.htm"> SELECTFIRST_NAME,LAST_NAME,SALARY FROMEMP_DETAILS_VIEW WHERESALARY>12000; SPOOLOFF SETMARKUPHTMLOFF SETECHOON
newpage	设置每页的开头空白行
null	设置结果为null时显示的值 如：setnull‘nodata’ 当查询结果中有空值时就显示nodata
numformat	Setsthedefaultformatfordisplayingnumbers
numwidth	Setsthedefaultwidthfordisplayingnumbers
pagesize	设置每页显示的行数
PAUSE	暂停ON/OFF/TEXT为ON时当报表超过一页时，每显示一屏就暂停显示，等待用户打回车键，再接着显示；OFF：页于页不停顿，TEXT：页于页停顿，并向用户提示信息
pno	页数
recsep	RECSEPtellsSQL*Pluswheretomaketherecordseparation.Forexample,ifyousetRECSEPtoWRAPPED,SQL*Plusprintsarecordseparatoronlyafterwrappedlines.IfyousetRECSEPtoEACH,SQL*Plusprintsarecordseparatorfollowingeveryrow.IfyousetRECSEPtoOFF,SQL*Plusdoesnotprintarecordseparator.
recsepchar	Definesthecharactertodisplayorprinttoseparaterecords
release
repfooter
repheader
serveroutput	使终端上显示dbms_output包中put_line()过程的执行结果
shiftinout
showmode
spool	将执行结果输出到文件： Spoold:\a.sql Select*fromtab_name; Spooloff–停止输出 Spoolout SPOOLOUT关闭该文件并在系统缺省的打印机上输出
sqlblanklines	允许有空白行在DML语句中ON/OFF
sqlcase
sqlcode
sqlcontinue
sqlnumber
sqlpluscompatibility
sqlprefix
sqlprompt
sqlterminator
suffix
tab
termout	用于设置在屏幕上显示或不显示所输出的信息
time
timing	是否显示DML语句的执行时间：ON/OFF
trimout
trimspool
ttitle	页头标题
underline
USER	显示当前的用户名
verify
wrap	控制换行：ON换行显示，OFF超过长度不换行而截取掉多余的长度
feedback	查询结束时给出结果、记录数的信息ON/OFF 如：执行select*fromtab_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*fromuser_users

user_tables视图

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

user_objects视图

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

user_tab_privs视图

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

2．动态数据字典

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

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

v$access

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

v$session

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

v$active_instance

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

v$context

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

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

selectp.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name,b.locked_mode

fromv$processp,v$sessiona,v$locked_objectb,all_objectsc

wherep.addr=a.paddranda.process=b.processandc.object_id=b.object_id

或

SELECTS.SIDSESSION_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.TYPELOCK_TYPE,L.ID1LOCK_ID1,L.ID2LOCK_ID2

FROMV$LOCKL,SYS.DBA_OBJECTSO,V$SESSIONS

WHEREL.SID=S.SIDANDL.ID1=O.OBJECT_ID

或

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

fromv$locked_objectl,dba_objectsb

whereb.object_id=l.object_id

ORACLE锁有一下几种模式：

0：none

1：null空

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

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

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

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

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

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

altersystemkillsession'sid,serial#';

3．查询TEMP空间的使用

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

selectd.tablespace_name,space"sum_space(m)",blockssum_blocks,used_space"used_space(m)",round(nvl(used_space,0)/space*100,2)"used_rate(%)",nvl(free_space,0)"free_space(m)"from(selecttablespace_name,round(sum(bytes)/(1024*1024),2)space,sum(blocks)blocksfromdba_temp_filesgroupbytablespace_name)d,(selecttablespace_name,round(sum(bytes_used)/(1024*1024),2)used_space,round(sum(bytes_free)/(1024*1024),2)free_spacefromv$temp_space_headergroupbytablespace_name)fwhered.tablespace_name=f.tablespace_name(+)

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

selectsum(blocks*8192)/1024/1024fromv$sort_usage