分享：我的oracle9i學(xué)習(xí)筆記(一)
2024-08-29 13:50:15
供稿：網(wǎng)友
create database db01 maxlogfiles 10 maxdatafiles 1024 maxinstances 2 logfile GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15M, GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15M, GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15M, datafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100M, undo tablespace UNDO datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40M default temporary tablespace TEMP tempfile '/u01/oradata/db01/temp_01_db01.dbf' SIZE 20M extent management local uniform size 128k character set AL32UTE8 national character set AL16UTF16 set time_zone='America/New_York'; ############### 數(shù)據(jù)字典 ########## set wrap off select * from v$dba_users; grant select on table_name to user/rule; select * from user_tables; select * from all_tables; select * from dba_tables; revoke dba from user_name; shutdown immediate startup nomount select * from v$instance; select * from v$sga; select * from v$tablespace; alter session set nls_language=american; alter database mount; select * from v$database; alter database open; desc dictionary select * from dict; desc v$fixed_table; select * from v$fixed_table; set Oracle_sid=foxconn select * from dba_objects; set serveroutput on execute dbms_output.put_line('sfasd'); ############# 控制文件 ########### select * from v$database; select * from v$tablespace; select * from v$logfile; select * from v$log; select * from v$backup; /*備份用戶表空間*/ alter tablespace users begin backup; select * from v$archived_log; select * from v$controlfile; alter system set control_files='$ORACLE_HOME/oradata/u01/ctrl01.ctl', '$ORACLE_HOME/oradata/u01/ctrl02.ctl' scope=spfile; cp $ORACLE_HOME/oradata/u01/ctrl01.ctl $ORACLE_HOME/oradata/u01/ctrl02.ctl startup pfile='../initSID.ora' select * from v$parameter where name like 'control%' ; show parameter control; select * from v$controlfile_record_section; select * from v$tempfile; /*備份控制文件*/ alter database backup controlfile to '../filepath/control.bak'; /*備份控制文件，并將二進(jìn)制控制文件變?yōu)榱薬sc 的文本文件*/ alter database backup controlfile to trace; ############### redo log ############## archive log list; alter system archive log start;--啟動自動存檔 alter system switch logfile;--強行進(jìn)行一次日志switch alter system checkpoint;--強制進(jìn)行一次checkpoint alter tablspace users begin backup; alter tablespace offline; /*checkpoint 同步頻率參數(shù)FAST_START_MTTR_TARGET,同步頻率越高，系統(tǒng)恢復(fù)所需時間越短*/ show parameter fast; show parameter log_checkpoint; /*加入一個日志組*/ alter database add logfile group 3 ('/$ORACLE_HOME/oracle/ora_log_file6.rdo' size 10M);
/*加入日志組的一個成員*/ alter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3; /*刪除日志組:當(dāng)前日志組不能刪；活動的日志組不能刪；非歸檔的日志組不能刪*/ alter database drop logfile group 3; /*刪除日志組中的某個成員，但每個組的最后一個成員不能被刪除*/ alter databse drop logfile member '$ORACLE_HOME/oracle/ora_log_file6.rdo'; /*清除在線日志*/ alter database clear logfile '$ORACLE_HOME/oracle/ora_log_file6.rdo'; alter database clear logfile group 3; /*清除非歸檔日志*/ alter database clear unarchived logfile group 3; /*重命名日志文件*/ alter database rename file '$ORACLE_HOME/oracle/ora_log_file6.rdo' to '$ORACLE_HOME/oracle/ora_log_file6a.rdo'; show parameter db_create; alter system set db_create_online_log_dest_1='path_name'; select * from v$log; select * from v$logfile;/*數(shù)據(jù)庫歸檔模式到非歸檔模式的互換,要啟動到mount狀態(tài)下才能改變;startup mount; 然后再打開數(shù)據(jù)庫.*/ alter database noarchivelog/archivelog; achive log start;---啟動自動歸檔 alter system archive all;－－手工歸檔所有日志文件 select * from v$archived_log; show parameter log_archive; ###### 分析日志文件logmnr ############## 1) 在init.ora中set utl_file_dir 參數(shù) 2) 重新啟動oracle 3) create 目錄文件 desc dbms_logmnr_d; dbms_logmnr_d.build; 4) 加入日志文件 add/remove log file dhms_logmnr.add_logfile dbms_logmnr.removefile 5) start logmnr dbms_logmnr.start_logmnr 6) 分析出來的內(nèi)容查詢 v$logmnr_content --sqlredo/sqlundo 實踐： desc dbms_logmnr_d; /*對數(shù)據(jù)表做一些操作，為恢復(fù)操作做預(yù)備*/ update 表 set qty=10 where stor_id=6380; delete 表 where stor_id=7066; /***********************************/ utl_file_dir的路徑 execute dbms_logmnr_d.build('foxdict.ora','$ORACLE_HOME/oracle/admin/fox/cdump'); execute dbms_logmnr.add_logfile('$ORACLE_HOME/oracle/ora_log_file6.log',dbms_logmnr.newfile); execute dbms_logmnr.start_logmnr(dictfilename=>;'$ORACLE_HOME/oracle/admin/fox/cdump/foxdict.ora'); ######### tablespace ############## select * form v$tablespace; select * from v$datafile; /*表空間和數(shù)據(jù)文件的對應(yīng)關(guān)系*/ select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#; alter tablespace users add datafile 'path' size 10M; select * from dba_rollback_segs; /*限制用戶在某表空間的使用限額*/ alter user user_name quota 10m on tablespace_name; create tablespace xxx [datafile 'path_name/datafile_name'] [size xxx] [extent management local/dictionary] [default storage(xxx)]; exmple: create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M; create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management dictionary default storage(initial 100k next 100k pctincrease 10) offline; /*9i以后，oracle建議使用local治理，而不使用dictionary治理，因為local采用bitmap治理表空間 ，不會產(chǎn)生系統(tǒng)表空間的自愿爭用;*/ create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local uniform size 1m;
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local autoallocate; /*在創(chuàng)建表空間時，設(shè)置表空間內(nèi)的段空間治理模式，這里用的是自動治理*/ create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local uniform size 1m segment space management auto; alter tablespace userdata mininum extent 10; alter tablespace userdata default storage(initial 1m next 1m pctincrease 20); /*undo tablespace(不能被用在字典治理模下) */ create undo tablespace undo1 datafile '$ORACLE_HOME/oradata/undo101.dbf' size 40M extent management local; show parameter undo; /*temporary tablespace*/ create temporary tablespace userdata tempfile '$ORACLE_HOME/oradata/undo101.dbf' size 10m extent management local;*設(shè)置數(shù)據(jù)庫缺省的臨時表空間*/ alter database default temporary tablespace tablespace_name; /*系統(tǒng)/臨時/在線的undo表空間不能被offline*/ alter tablespace tablespace_name offline/online; alter tablespace tablespace_name read only; /*重命名用戶表空間*/ alter tablespace tablespace_name rename datafile '$ORACLE_HOME/oradata/undo101.dbf' to '$ORACLE_HOME/oradata/undo102.dbf'; /*重命名系統(tǒng)表空間 ,但在重命名前必須將數(shù)據(jù)庫shutdown,并重啟到mount狀態(tài)*/ alter database rename file '$ORACLE_HOME/oradata/system01.dbf' to '$ORACLE_HOME/oradata/system02.dbf'; drop tablespace userdata including contents and datafiles;---drop tablespce /*resize tablespace,autoextend datafile space*/ alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500M; /*resize datafile*/ alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' resize 50m; /*給表空間擴(kuò)展空間*/ alter tablespace userdata add datafile '$ORACLE_HOME/oradata/undo102.dbf' size 10m; /*將表空間設(shè)置成OMF狀態(tài)*/ alter system set db_create_file_dest='$ORACLE_HOME/oradata'; create tablespace userdata;---use OMF status to create tablespace; drop tablespace userdata;---user OMF status to drop tablespace; select * from dba_tablespace/v$tablespace/dba_data_files; /*將表的某分區(qū)移動到另一個表空間*/ alter table table_name move partition partition_name tablespace tablespace_name; ###### ORACLE storage strUCture and relationships ######### /*手工分配表空間段的分區(qū)(extend)大小*/ alter table kong.test12 allocate extent(size 1m datafile '$ORACLE_HOME/oradata/undo102.dbf'); alter table kong.test12 deallocate unused; ---釋放表中沒有用到的分區(qū) show parameter db; alter system set db_8k_cache_size=10m; ---配置8k塊的內(nèi)存空間塊參數(shù) select * from dba_extents/dba_segments/data_tablespace; select * from dba_free_space/dba_data_file/data_tablespace; /*數(shù)據(jù)對象所占用的字節(jié)數(shù)*/ select sum(bytes) from dba_extents where onwer='kong' and segment_name ='table_name'; ############ UNDO Data ################ show parameter undo; alter tablespace users offline normal;
alter tablespace users offline immediate; recover datafile '$ORACLE_HOME/oradata/undo102.dbf'; alter tablespace users online ; select * from dba_rollback_segs; alter system set undo_tablespace=undotbs1;
