ORACLE常用SQL

---------------------------------------------常用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;