五、Using Data Dictionary and Dynamic Performance Views
数据字典的表
dictionary,user_object,all_object,dba_object
select * from v$controlfile;
select dbid,name from v$database;
select instance_name from v$instance;
六、Maintaining the Control File
1、Add Spfile Control File
(1) alter system set control_files='/home/oracle/oradata/TS/control01.ctl',
'/home/oracle/oradata/TS/control02.ctl',
'/home/oracle/oradata/TS/control03.ctl',
'/home/oracle/oradata/TS/control04.ctl'
scope = spfile;
(2) shutdown immediate;
(3) cp control01.ctl control04.ctl
(4) startup;
v$parameter
v$controlfile
v$controlfile_record_section
2、Advanced Topic
Creating New Control Files
Backing up Control Files
七、Maintaining Online Redo Log Files
v$log
v$logfile
v$log_history
1、Force Log Switched & CK
select group#,thread#,sequence#,status from v$log;
alter system switch logfile;
select group#,thread#,sequence#,status from v$log;
2、Checkpoints can be forced by
--Setting FAST_START_MTTR_TARGET parameter
alter system set fast_start_mttr_target=600 scope=both;
--ALTER SYSTEM CHECKPOINT command
alter system checkpoint;
3、Adding Redo Files Groups
alter database add logfile group 4 ('/db01/redo04.log') size 5M;
alter database add logfile member '/db02/redo04.log' to group 4;
select * from v$logfile;
4、删除日志文件
--删除日志文件的组成员
alter database drop logfile member '/db02/redo04.log';
--删除组
alter database drop logfile group 4;
select * from v$logfile;
5、Relocate & Rename
(1) ALTER DATABASE RENAME FILE command
--Shutdown the database
--Copy the online redo log files to the new location
--Place the database in MOUNT mode
--Execute the command
alter database rename file '/db01/redo03.log' to '/db01/redo03.rdo';
--Open database for normal operation
alter database open;
(2) Add new members and drop old members
6、Clear Redo Files
(1) alter database clear logfile command can be userd to reinitialize an online redo log file
alter database clear logfile group 2;
(2) use the unarchived keyword to avoid archiving the corrupted online redo log file
alter database clear unarchived logfile group 2;
7、检查数据是否为archive模式
select archiver from v$instance;
select log_mode from v$database;
八、Managing Tablespace & Data Files
1、创建表空间
select * from v$tablespace;
select file_name,tablespace_name from dba_data_files;
create tablespace pual datafile '/db01/pual01.dbf' size 20m;
2、
select tablespace_name,contents,extent_management from dba_tablespaces;
select * from database_properties;
create temporary tablespace mytemp tempfile '/db01/mytemp01.dbf' size 10m extent management local;
alter database default temporary tablespace mytemp;
3、Read-Only Tablespaces
alter tablespace userdata read only;
alter tablespace TS1 read write;
4、Taking a Tablespace Offline
alter tablespace TS1 offline;
alter tablespace TS1 online;
5、Resizing a Tablespace
select a.tablespace_name,a.bytes/1024/1024 bytes_used,
b.largest/1024/1024 largest,
round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
from
(select tablespace_name,sum(bytes) bytes from dba_data_files
group by tablespace_name ) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc
alter tablespace TS1 add datafile '/db01/ts12.dbf' size 200m autoextend on next 10m maxsize 2000m;
select file_name,tablespace_name,autoextensible from dba_data_files;
alter database '/db01/ts11.dbf' autoextend on next 10m maxsize 100m;
alter database datafile '/db01/ts11.dbf' resize 2000m;
--临时表空间
select * from dba_temp_files;
6、Methods for Moving DF
--先把表空间offline
alter tablespace TS1 offline;
--然后拷贝数据到目标目录
alter tablespace TS1 rename datafile '/db01/ts11.dbf' to '/db02/ts11.dbf';
--先把数据库shutdown然后拷贝数据到目标目录
--再startup mount
alter database rename file '/db02/ts11.dbf' to '/db01/ts11.dbf';
--最后
alter database open;
7、Dropping Tablespaces
drop tablespace TS1 including contents and datafiles;
8、Tablespace Informationdba_tablespaces
v$tablespace
dba_data_files
v$datafile
dba_temp_files
v$tempfile
九、 Storage Structure & Relationships
--set autot off(查看执行情况)
--set autot on
1、
select tablespace_name,block_size from dba_tablespaces;
2、Configuring ASSM
create tablespace data02 datafile '/db01/data11.dbf' size 5M
extent management local uniform size 6K
segment space management auto;
3、Manual Data Block Mgmt
select * from user_objects;
select segment_name from user_segments;
4、Getting Storage Information
DBA_EXTENDS
DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
DBA_FREE_SPACE
select segment_name,segment_type from user_segments;
十、Managing Undo Data
--更改uodo表空间
alter system set undo_tablespace=undotbs2;
select addr,used_ublk from v$transaction;
select end_time,begin_time,undoblks from v$undostat;
##决定undo表空间大小
--一秒钟最高峰需要多少块
select max(undoblks/((end_time-begin_time)*24*3600)) maxundo from v$undostat;
select sum(undoblks) / sum((end_time-begin_time)*24*3600) from v$undostat; --平均块
--系统需要undo空间
show parameter undo_retention
show parameter db_block_size
所需空间=undo_retention*db_block_size*maxundo
DBA_SEGMENTS
DBA_ROLLBACK_SEGS
select segment_name,tablespace_name from dba_rollback_segs;
V$ROLLNAME
V$ROLLSTAT
V$UNDOSTAT
V$SESSION
V$TRANSACTION