oracle dba常用语句6

########## Managing Tables ###########

/*char type maxlen=2000;varchar2 type maxlen=4000 bytes

rowid 是 18 位的 64 进制字符串 (10 个 bytes 80 bits)

rowid 组成 : object#( 对象号 )--32bits,6 位

rfile#( 相对文件号 )--10bits,3 位

block#( 块号 )--22bits,6 位

row#( 行号 )--16bits,3 位

64 进制 : A-Z,a-z,0-9,/,+ 共 64 个符号

dbms_rowid 包中的函数可以提供对 rowid 的解释 */

select rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid) from table_name;

create table test2

(

id int,

lname varchar2(20) not null,

fname varchar2(20) constraint ck_1 check(fname like 'k%'),

empdate date default sysdate)

) tablespace tablespace_name;

/* 手工分配分区 , 分配的数据文件必须是表所在表空间内的数据文件 */

alter table user.table_name allocate extent(size 500k datafile '...');

/* 释放表中没有用到的空间 */

alter table table_name deallocate unused;

alter table table_name deallocate unused keep 8k;

/* 将非分区表的表空间搬到新的表空间 , 在移动表空间后，原表中的索引对象将会不可用，必须重建 */

alter table user.table_name move tablespace new_tablespace_name;

create index index_name on user.table_name(column_name) tablespace users;

alter index index_name rebuild;

drop table table_name [CASCADE CONSTRAINTS];

alter table user.table_name drop column col_name [CASCADE CONSTRAINTS CHECKPOINT 1000];---drop column

/* 给表中不用的列做标记 */

alter table user.table_name set unused column comments CASCADE CONSTRAINTS;

/*drop 表中不用的做了标记列 */

alter table user.table_name drop unused columns checkpoint 1000;

/* 当在 drop col 是出现异常，使用 CONTINUE ，防止重删前面的 column*/

ALTER TABLE USER.TABLE_NAME DROP COLUMNS CONTINUE CHECKPOINT 1000;

select * from dba_tables/dba_objects;

######## managing indexes ##########

/*create index*/

example:

/* 创建一般索引 */

create index index_name on table_name(column_name) tablespace tablespace_name;

/* 创建位图索引 */

create bitmap index index_name on table_name(column_name1,column_name2) tablespace tablespace_name;

/* 索引中不能用 pctused*/

create [bitmap] index index_name on table_name(column_name) tablespace tablespace_name pctfree 20 storage(inital 100k next 100k) ;

/* 大数据量的索引最好不要做日志 */

create [bitmap] index index_name table_name(column_name1,column_name2) tablespace_name pctfree 20 storage(inital 100k next 100k) nologging;

/* 创建反转索引 */

create index index_name on table_name(column_name) reverse;

/* 创建函数索引 */

create index index_name on table_name(function_name(column_name)) tablespace tablespace_name;

/* 建表时创建约束条件 */

create table user.table_name(column_name number(7) constraint constraint_name primary key deferrable using index storage(initial 100k next 100k) tablespace tablespace_name,column_name2 varchar2(25) constraint constraint_name not null,column_name3 number(7)) tablespace tablespace_name;

/* 给创建 bitmap index 分配的内存空间参数，以加速建索引 */

show parameter create_bit;

/* 改变索引的存储参数 */

alter index index_name pctfree 30 storage(initial 200k next 200k);

/* 给索引手工分配一个分区 */

alter index index_name allocate extent (size 200k datafile '$ORACLE/oradata/..');

/* 释放索引中没用的空间 */

alter index index_name deallocate unused;

/* 索引重建 */

alter index index_name rebuild tablespace tablespace_name;

/* 普通索引和反转索引的互换 */

alter index index_name rebuild tablespace tablespace_name reverse;

/* 重建索引时，不锁表 */

alter index index_name rebuild online;

/* 给索引整理碎片 */

alter index index_name COALESCE;

/* 分析索引 , 事实上是更新统计的过程 */

analyze index index_name validate structure;

desc index_state;

drop index index_name;

alter index index_name monitoring usage;----- 监视索引是否被用到

alter index index_name nomonitoring usage;---- 取消监视

/* 有关索引信息的视图 */

select * from dba_indexes/dba_ind_columns/dbs_ind_expressions/v$object_usage;

########## 数据完整性的管理 (Maintaining data integrity) ##########

alter table table_name drop constraint constraint_name;----drop 约束

alter table table_name add constraint constraint_name primary key(column_name1,column_name2);----- 创建主键

alter table table_name add constraint constraint_name unique(column_name1,column_name2);--- 创建唯一约束

/* 创建外键约束 */

alter table table_name add constraint constraint_name foreign key(column_name1) references table_name(column_name1);

/* 不效验老数据，只约束新的数据 [enable/disable ：约束 / 不约束新数据 ;novalidate/validate: 不对 / 对老数据进行验证 ]*/

alter table table_name add constraint constraint_name check(column_name like 'B%') enable/disable novalidate/validate;

/* 修改约束条件，延时验证， commit 时验证 */

alter table table_name modify constraint constraint_name initially deferred;

/* 修改约束条件，立即验证 */

alter table table_name modify constraint constraint_name initially immediate;

alter session set constraints=deferred/immediate;

/*drop 一个有外键的主键表 , 带 cascade constraints 参数级联删除 */

drop table table_name cascade constraints;

/* 当 truncate 外键表时，先将外键设为无效，再 truncate;*/

truncate table table_name;

/* 设约束条件无效 */

alter table table_name disable constraint constraint_name;

alter table table_name enable novalidate constraint constraint_name;

/* 将无效约束的数据行放入 exception 的表中，此表记录了违反数据约束的行的行号；在此之前，要先建 exceptions 表 */

alter table table_name add constraint constraint_name check(column_name >15) enable validate exceptions into exceptions;

/* 运行创建 exceptions 表的脚本 */

start $ORACLE_HOME/rdbms/admin/utlexcpt.sql;

/* 获取约束条件信息的表或视图 */

select * from user_constraints/dba_constraints/dba_cons_columns;