Oracle笔记3

五、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 Information

  dba_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