ORACLE基本定义、操作语句
来源:互联网 发布:erdai cms 编辑:程序博客网 时间:2024/06/05 14:10
一、 表
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.给该用户授权 (常用权限有:connect resource)
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
一次可以取的行的数目。1-5000,即系统查询出多少行后将结果返回给用户
autocommit
用于在操作中是自动提交:ON/IMMEDIATE 或是部分提交 N 或是不自动提交 OFF
N 表示允许在用户发出COMMIT后,可以提交的执行命令数量(将n条DML语句所做的改进结果进行提交,等n+1、n+2…..条语句产生的结果将不提交)
autoprint
自动打印变量的值。
如在sqlplus中定义了一个变量,在这些过程时引用了该变量,执行完过程后终端自动打印该变量的值。
autorecovery
在做备份恢复的时候有用
autotrace
设置自动跟踪DML语句生成执行计划的参数:ON 打开自动跟踪,OFF关闭,TRACEONLY 只生成执行计划而不返回执行结果
blockterminator
Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command
btitle
页脚标题
cmdsep
用于将多个sqlplus命令连接在一行的连接符号 如:SQL>set cmddep +
SQL> TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
colsep
用于设置查询结果中各列之间的间隔符号
如:SQL>set colsep ‘|’
执行一个查询语句后,显示的结果中各列以’|’字符隔开
compatibility
指定oracle sql的语法分析器的版本
SET COM[PATIBILITY]{V7|V8|NATIVE}
concat
用于想要在define的变量之后连接其他的字符串时使用
如:SQL>define str=’a’
SQL>select ‘&str’ from dual;
执行结果如下:
'
----
.a
SQL>set concat ,
SQL>select ‘&str,AA’ from dual;
执行结果如下:
'AA
----
aaa
copycommit
设置copy命令执行后,多少行数据开始commit
COPYTYPECHECK
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.
define
定义变量时使用的命令
describe
用于显示表和视图的结构,同义词,或指定函数和过程的详述
echo
命令文件执行时,是否在终端上显示命令本身:ON/OFF 如:SQL>start d:\a.sql
Echo 为on则在终端上显示a.sql中的命令
editfile
设定edit默认情况下使用的文件名,文件名的长度和操作系统允许值相同
embedded
Controls where on a page each report begins. OFF forces each report to start at the top of a 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命令相同
flush
设定向用户显示设备输出数据的开关。设为OFF则缓存在操作系统的buffer中,设定为ON则操作系统无法缓存。一般在非对话形式或者使用command file,不需要确认输出结果时设定为关闭。设定为OFF也可以减少I/O发生,提高性能。
heading
控制列名信息的显示:ON/OFF
headsep
设置让列换行显示的标识符
如:SQL>set headsep ‘\’
SQL>col system_id heading ‘system\id’
SQL>select system_id from src2stdwar;
显示结果如下:
System
Id
----------
6
7
instance
设置系统默认的实例
如:SQL>disconnect
SQL> SET INSTANCE LGDB
LGDB为数据库一个实例
要设置回系统默认的实例
SQL>set instance local
linesize
设置行的最大显示位数
lno
显示当前的行数
loboffset
设置clob 数据类型的显示长度,超过将换行显示或截取
logsource
指定本地的log file给恢复使用,默认的值是oracle初始化文件init.ora中LOG_ARCHIVE_DEST的值,set logsource后面不带参数,将设置回默认的值
long
长类型显示的字节数,默认的是80个字符
值必须小于Maxdata的值
SQL>show Maxdata
longchunksize
设置增量的字节数
markup
设置HTML输出到文本 如:
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
newpage
设置每页的开头空白行
null
设置结果为null时显示的值
如:set null ‘no data’
当查询结果中有空值时就显示no data
numformat
Sets the default format for displaying numbers
numwidth
Sets the default width for displaying numbers
pagesize
设置每页显示的行数
PAUSE
暂停 ON/OFF/TEXT 为ON时当报表超过一页时,每显示一屏就暂停显示,等待用户打回车键,再接着显示;OFF:页于页不停顿,TEXT:页于页停顿,并向用户提示信息
pno
页数
recsep
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.
recsepchar
Defines the character to display or print to separate records
release
repfooter
repheader
serveroutput
使终端上显示dbms_output包中put_line()过程的执行结果
shiftinout
showmode
spool
将执行结果输出到文件:
Spool d:\a.sql
Select * from tab_name ;
Spool off –停止输出
Spool out
SPOOL OUT关闭该文件并在系统缺省的打印机上输出
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 * 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)",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
- ORACLE基本定义、操作语句
- oracle基本操作语句
- oracle基本操作语句
- oracle基本操作语句
- ORACLE基本操作语句
- oracle基本操作语句
- oracle基本操作语句总结
- Oracle开发基本操作语句
- oracle 一些基本操作语句
- oracle基本操作语句(适合初学者)
- Oracle数据库操作基本语句随笔
- oracle表的基本操作语句
- oracle数据库的基本操作语句
- 基本的oracle查询操作语句
- Oracle中SQL基本操作语句
- Oracle 第四记 基本操作语句
- Oracle基本操作七:PL、SQL 定义变量,操作数据
- Oracle学习(一):基本操作和基本查询语句
- 关于使用putty私钥连接linux出现失败的原因解决方案
- C++读取txt
- 一道竞赛题
- 【转载】HBase性能优化方法总结(1):配置优化
- ARM架构与体系学习(一)——初识启动文件
- ORACLE基本定义、操作语句
- linux下软件安装卸载专题
- 【并查集】Is it a tree?
- SharedPreferences 的用法
- IEC61850开发,smv9-1测试工具
- 第五章 函数 ---变量的作用域----静态局部变量
- android获取手机信息
- 文档附件在线查看(类似百度文库的实现)
- 基于CGI的嵌入式web server设计