分享：我的oracle9i學習筆記(二)
2024-08-29 13:50:15
供稿：網友
/*忽略回滾段的錯誤提示*/ 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;
