Oracle常用系统表及常用语句集合(3)

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;