分享：我的oracle9i学习笔记(二)

/*忽略回滚段的错误提示*/ alter system set undo_suppress_errors=true; /*在自动治理模式下,不会真正建立rbs1;在手工治理模式则可以建立,且是私有回滚段*/ create rollback segment rbs1 tablespace undotbs; desc dbms_flashback; /*在提交了修改的数据后,9i提供了旧数据的回闪操作,将修改前的数据只读给用户看,但这部分数据不会又恢复在表中,而是旧数据的一个映射*/ execute dbms_flashback.enable_at_time('26-JAN-04:12:17:00 pm'); execute dbms_flashback.disable; /*回滚段的统计信息*/ select end_time,begin_time,undoblks from v$undostat; /*undo表空间的大小计算公式: UndoSpace=[UR * (UPS * DBS)] + (DBS * 24) UR :UNDO_RETENTION 保留的时间(秒) UPS :每秒的回滚数据块 DBS:系统EXTENT和FILE SIZE(也就是db_block_size)*/ select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction; show parameter transactions; show parameter rollback; /*在手工治理模式下,建立公共的回滚段*/ create public rollback segment prbs1 tablespace undotbs; alter rollback segment rbs1 online;----在手工治理模式 /*在手工治理模式中,initSID.ora中指定 undo_management=manual 、rollback_segment=('rbs1','rbs2',...)、 transactions=100 、transactions_per_rollback_segment=10 然后 shutdown immediate ,startup pfile=....???.ora */ ########## 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; create global temporary table test2 on commit delete/preserve rows as select * from kong.authors; create table user.table(...) tablespace tablespace_name storage(...) pctfree10 pctused 40; alter table user.tablename pctfree 20 pctused 50 storage(...);---changing table storage /*手工分配分区,分配的数据文件必须是表所在表空间内的数据文件*/ 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;################## managing passWord security and resources #################### alter user user_name account unlock/open;----锁定/打开用户; alter user user_name password expire;---设定口令到期 /*建立口令配置文件,failed_login_attempts口令输多少次后锁，password_lock_times指多少天后口令被自动解锁*/ create profile profile_name limit failed_login_attempts 3 password_lock_times 1/1440; /*创建口令配置文件*/ create profile profile_name limit failed_login_attempts 3 password_lock_time unlimited password_life_time 30 password_reuse_time 30 password_verify_function verify_function password_grace_time 5; /*建立资源配置文件*/ create profile prfile_name limit session_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480; alter user user_name profile profile_name; /*设置口令解锁时间*/ alter profile profile_name limit password_lock_time 1/24; /*password_life_time指口令文件多少时间到期，password_grace_time指在第一次成功登录后到口令到期有多少天时间可改变口令*/ alter profile profile_name limit password_lift_time 2 password_grace_time 3; /*password_reuse_time指口令在多少天内可被重用,password_reuse_max口令可被重用的最大次数*/ alter profile profile_name limit password_reuse_time 10[password_reuse_max 3]; alter user user_name identified by input_password;-----修改用户口令 drop profile profile_name; /*建立了profile后，且指定给某个用户，则必须用CASCADE才能删除*/ drop profile profile_name CASCADE; alter system set resource_limit=true;---启用自愿限制,缺省是false /*配置资源参数*/ alter profile profile_name limit cpu_per_session 10000 connect_time 60 idle_time 5; /*资源参数(session级) cpu_per_session 每个session占用cpu的时间 单位1/100秒 sessions_per_user 答应每个用户的并行session数 connect_time 答应连接的时间 单位分钟 idle_time 连接被空闲多少时间后，被自动断开 单位分钟 logical_reads_per_session 读块数 private_sga 用户能够在SGA中使用的私有的空间数 单位bytes (call级) cpu_per_call 每次(1/100秒)调用cpu的时间 logical_reads_per_call 每次调用能够读的块数 */ alter profile profile_name limit cpu_per_call 1000 logical_reads_per_call 10; desc dbms_resouce_manager;---资源治理器包 /*获取资源信息的表或视图*/ select * from dba_users/dba_profiles; ###### Managing users ############ show parameter os; create user testuser1 identified by kxf_001; grant connect,createtable to testuser1; alter user testuser1 quota 10m on tablespace_name; /*创建用户*/ create user user_name identified by password default tablespace tablespace_name temporary tablespace tablespace_name quota 15m on tablespace_name password expire; /*数据库级设定缺省临时表空间*/ alter database default temporary tablespace tablespace_name; /*制定数据库级的缺省表空间*/ alter database default tablespace tablespace_name; /*创建os级审核的用户，需知道os_authent_prefix，表示oracle和os口令对应的前缀,'OPS$'为此参数的值，此值可以任意设置*/ create user user_name identified by externally default OPS$tablespace_name tablespace_name temporary tablespace tablespace_name quota 15m on tablespace_name password expire; /*修改用户使用表空间的限额,回滚表空间和临时表空间不答应授予限额*/ alter user user_name quota 5m on tablespace_name; /*删除用户或删除级联用户(用户对象下有对象的要用CASCADE，将其下一些对象一起删除)*/ drop user user_name [CASCADE]; /*每个用户在哪些表空间下有些什么限额*/ desc dba_ts_quotas;select * from dba_ts_quotas where username='...'; /*改变用户的缺省表空间*/ alter user user_name default tablespace tablespace_name;