ORACLE常用SQL

原创 2007年10月01日 10:00:00


---------------------------------------------常用sql语句------------------------------------------------

 -------------------------------------DML-----------------------------------
select 语句
with tmp_name as (select 语句)
select [unique,distinct,all] list from (select 语句) table_alias,tmp_name where ...
group by ...
having ...
[union [all],intersect,minus]
order by ...
FOR UPDATE
[OF [[schema .] { table | view } .] column
 [, [[schema .] { table | view } .] column]...
]
[ NOWAIT | WAIT integer ]

delete 语句
delete from table_name where ...
delete table(select ..) p
where p.... 

update table_name
set (update_columns,..) = (select update_columns,.. from tab2 where table_name. = tab2. )  ----关联条件一定要的话
where exists (select update_columns,.. from tab2 where table_name. = tab2. )  ----只更新那些能关联上的

insert 语句
insert into table_name(list)values(,)

insert into table_name(list)
select list

 -------------------------------------DDL-----------------------------------

建tablespace
create tablespace db_zgxt DATAFILE 'zgxt.dat' SIZE 40M
 DEFAULT STORAGE (INITIAL 128K NEXT 128K
                    MINEXTENTS 1 MAXEXTENTS 999)
   ONLINE;

--INITIAL 第一块extends的大小
--MINEXTENTS  第一次创建对象时创建的extends数,第一块按 initial大小,后面的按next大小
--ONLINE 马上将该tablespace置为online状态,可以使用
 

建table
create [GLOBAL TEMPORARY] table table_name (
col1 varchar2(100)
[ constraint constraint_name check(col1 > 0) ]
[ constraint constraint_name not null ]
[ constraint constraint_name unique ]
[ constraint constraint_name pariamry key(col_names,..) [ DISABLE ] ]
)
using index tablespace tablespace_name              -------表的索引的存储表空间,为提高效率一般两者不存在同一表空间
storage
(

)
tablespace tablespace_name
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
[ LOGGING | NOLOGGING ] ----是否用日志记录该表的变化
;

 

概念
Initrans:对数据块缺省是1,对index块缺省是2。每个oracle block有块头区域,放置了1个表字典和1个行字典和事务槽(transaction slot), 当1个事务要修改块里的1行或多行前,事务用事务槽来确定该块是否属于自己。在oracle的行级锁和读一致视图技术里,这些槽担任主要角色!当事务要修改块里的行时,他首先要在块头里的可用事务槽里"签名"!然后事务修改的每行的锁字节被设置,标识那些行被锁定了。如果只1个槽或槽不够用,当另外1个事务需修改同个块里的其它行时(insert、update、delete),就需要去block的pctfree里借24字节来分配1个槽在block header area。如果这种情况经常发生就会造成:
1. 动态的分配槽,会降低性能,因为oracle要去进行块级别的空间管理,而不是仅仅修改块里的数据。
2. 因为需到pctfree里借24字节去动态产生槽,如果很多事务产生这种情况,会占比较大的空间,因为借的空间不会释放,pctfree的计算就会有误,潜在产生行迁移。

maxtrans:定义1个块可同时进行修改块里数据的最多事务,缺省值是255。但不意味着每个块能真正支持255个事务,当对块的并发事务时,需要事务槽被分配。

pctfree:当这个表的哪个数据块的可用空间低于该值时该数据块将不能再插入新数据。

修改表
alter table table_name
{add | modify} constraint constraint_name ... DISABLE
primary key(id,name)
| RENAME CONSTRAINT old_name TO new_name
| DROP
{ { PRIMARY KEY | UNIQUE ( column [, column]... ) }
  [CASCADE] [{ KEEP | DROP } INDEX]
| CONSTRAINT constraint [CASCADE]
}

移动表所在表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME

建立索引
create [bitmap | unique | ] index
on [ cluster ] table_name(cols)
nologging
tablespace tablespace_name
  pctfree 10
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )

索引移动表空间
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME
[  storage()  ]

建sequence
create sequence seq_custid start 1 incrememt by 1;


赋权
grant [role,system privilege,object privilege] to 人员或role
with {admin,grant} option --------admin,此权限的赋权能力也赋给了此人

锁表
lock table table_name IN lockmode MODE [NOWAIT];

lockmode
ROW SHARE
可以访问但是不允许别人独占全表(行share锁)

ROW EXCLUSIVE
防止别人用share mode(insert delete update默认加此锁)

SHARE UPDATE
同ROW SHARE

SHARE(表share锁)
防止更新表

SHARE ROW EXCLUSIVE
允许别人查询表的但是不允许别人用share锁定或者更新表

EXCLUSIVE
只允许别人查询该表(但是不允许别人加任何锁)

杀进程
ALTER SYSTEM KILL SESSION 'sid,serial#'

剥夺权利
revoke [role,system privilege,object privilege] from 人员或role

游标
显式游标
declare
CURSOR cur_name IS
select 语句;

begin
 OPEN cur_name ;--------一般里面若是带了变量,则在此之前賦完值再打开才去真正执行sql
/*   LOOP 方法
 LOOP
  FETCH cur_name INTO v_list;
  EXIT WHEN cur_name%NOTFOUND;
 END LOOP;
*/

/*   FOR 方法
 FOR v_cur IN cur_name LOOP
  处理语句,不用fetch

 END LOOP;
*/
 CLOSE cur_name ;

end

 


嵌套表
----创建嵌套表类型
create or replace type studentlist as table of number(5);

create table nest_test(
       stud_id         number(4),
       std             studentlist
)
nested table std store as co_tab  -----该列另存为别的表了
;


insert into nest_test
values(1,studentlist(99,100));

执行时间显示开关
set timing on

IO数显示
SET AUTOTRACE ON

修改用户的空间使用定额
alter user user_name quota 50M on tablespace_name;


使用指定的回滚段
set transaction use rollback segment SEGMENT_NAME;

回滚段
create [public] rollback segment SEGMENT_NAME tablespace tablespace_name [storage()];

alter rollback segment SEGMENT_NAME [offline | online]; ----非激活或激活

drop rollback segment SEGMENT_NAME; 

ORACLE常用SQL语句大全

一、基础1、说明:创建数据库 CREATE DATABASE database-name2、说明:删除数据库 drop database dbname3、说明:备份sql server — 创建...
  • ysy_java
  • ysy_java
  • 2016年12月14日 12:57
  • 4009

Oracle PL/SQL常用47个工具包

Oracle PL/SQL Utility Library PL/SQL 各种常用工具包代码,包含: PDF文件生成工具包 Excel文件生成、 RTF文件生成、 Zip文件压缩与解压、 ...
  • nansheng_su
  • nansheng_su
  • 2015年05月11日 20:53
  • 1169

oracle常用的SQL PLUS命令详解

dba基础课程:oracle常用的SQL PLUS命令详解 Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中,可以运行sql*plus命令与sql*plu...
  • oraclestudyroad
  • oraclestudyroad
  • 2016年08月03日 15:40
  • 635

oracle常用经典SQL查询

(po.planott >= to_timestamp('2011-06-14 0:0:0','yyyy-mm-dd hh24:mi:ss')) and (po.planott
  • wx3957156
  • wx3957156
  • 2013年12月24日 11:41
  • 1846

Oracle SQL常用内置函数总结

Oracle SQL常用内置函数总结:数字计算函数、字符处理函数、日期时间函数、转换函数、聚合分析函数...
  • yihuiworld
  • yihuiworld
  • 2015年03月28日 15:17
  • 10338

oracle基本sql语句和函数详解

一、oracle常用数据类型      一、  数据定义语言(ddl) 数据定义语言ddl(data definition language)用于改变数据库结构,包括创建、更改和删除数...
  • u013310119
  • u013310119
  • 2016年09月18日 16:07
  • 1497

个人常用Oracle SQL优化工具(脚本)介绍(未完成)

本文主要介绍个人在工作中常用的关于SQL优化工具(脚本),主要从网络上收集,读书,MOS等,如有侵权请告知。掌握一些常用的视图,就能够根据自己需求修改脚本。...
  • u011255894
  • u011255894
  • 2016年03月27日 21:46
  • 355

oracle常用sql语句

-- 首先,以超级管理员的身份登录oracle        sqlplus sys/bjsxt as sysdba        --然后,解除对scott用户的锁        alter use...
  • qq503690160
  • qq503690160
  • 2016年08月09日 08:53
  • 9580

Oracle维护常用SQL语句(查询系统表和视图)

Oracle维护常用SQL语句(查询系统表和视图) 提要: 1、查看表空间的名称及大小 2、查看表空间物理文件的名称及大小 3、查看回滚段名称及大小 4、查看控制文件 5、查看日志...
  • Hu199055
  • Hu199055
  • 2016年01月10日 14:48
  • 854

ORACLE EBS常用表及查询语句(最终整理版)

建议去看参考二 参考一:                                                              ca...
  • caixingyun
  • caixingyun
  • 2014年07月21日 14:38
  • 2352
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE常用SQL
举报原因:
原因补充:

(最多只允许输入30个字)