oracle操作.txt

Oracle操作完全操作迷你手册

######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql ############# 

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'; 

############### 数据字典 ########## 

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'; 

/*备份控制文件，并将二进制控制文件变为了asc 的文本文件*/ 
alter database backup controlfile to trace; 

############### redo log ############## 

archive log list; 

alter system archive log start;--启动自动存档 

alter system switch logfile;--强行进行一次日志switch 

alter system checkpoint;--强制进行一次checkpoint 

alter tablspace users begin backup; 

alter tablespace offline; 

/*checkpoint 同步频率参数FAST_START_MTTR_TARGET,同步频率越高，系统恢复所需时间越短*/ 
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; 

/*删除日志组:当前日志组不能删；活动的日志组不能删；非归档的日志组不能删*/ 
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; 

/*数据库归档模式到非归档模式的互换,要启动到mount状态下才能改变;startup mount;然后再打开数据库.*/ 
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 参数 
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) 分析出来的内容查询 v$logmnr_content --sqlredo/sqlundo 

实践： 

desc dbms_logmnr_d; 

/*对数据表做一些操作，为恢复操作做准备*/ 
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; 

/*表空间和数据文件的对应关系*/ 
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管理表空间 ，不会产生系统表空间的自愿争用;*/ 
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; 
/*在创建表空间时，设置表空间内的段空间管理模式，这里用的是自动管理*/ 
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; 

/*设置数据库缺省的临时表空间*/ 
alter database default temporary tablespace tablespace_name; 

/*系统/临时/在线的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'; 

/*重命名系统表空间 ,但在重命名前必须将数据库shutdown,并重启到mount状态*/ 
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; 

/*给表空间扩展空间*/ 
alter tablespace userdata add datafile '$ORACLE_HOME/oradata/undo102.dbf' size 10m; 

/*将表空间设置成OMF状态*/ 
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; 

/*将表的某分区移动到另一个表空间*/ 
alter table table_name move partition partition_name tablespace tablespace_name; 

###### ORACLE storage structure and relationships ######### 

/*手工分配表空间段的分区(extend)大小*/ 
alter table kong.test12 allocate extent(size 1m datafile '$ORACLE_HOME/oradata/undo102.dbf'); 

alter table kong.test12 deallocate unused; ---释放表中没有用到的分区 

show parameter db; 

alter system set db_8k_cache_size=10m; ---配置8k块的内存空间块参数 

select * from dba_extents/dba_segments/data_tablespace; 

select * from dba_free_space/dba_data_file/data_tablespace; 

/*数据对象所占用的字节数*/ 
select sum(bytes) from dba_extents where onwer='kong' and segment_name ='table_name'; 

############ UNDO Data ################