oracle dba 常用语句4

===========================================================
######### 创建数据库----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;