汇总的一些命令

把student guide里的一些命令按自已的理解汇总了一下。

一、获取数据一般信息的操作
1 查看数据库的版本
select version from product_component_verdion where substr(product,1,6) ='Oracle'

2 查看数据库的创建日期和归档方式
select created,log_mode from v$database;

二、有关instance的操作，还应改包括对内存进行优化，对进程进行操作的很多内容
1 参数文件
create spfile from pfile
startup pfile=$oracle_home/dbs/initdb01.ora

三、有关database的操作，这些操作应该包括对控制文件，重做日志，数据文件，归档日志，表空间、段，扩展，块等的操作。
1 控制文件
  多个控制文件
  使用alter system
  alter system set control files = '/$home/oradata/u01/ctrl01.ctl',
    '$home/oradata/u02/ctrl02.ctl ' scope=spfile;

  使用参数文件
  control_files=$home/oradata/u01/ctrl01.ctl,$home/oradata/u02/ctrl02.ctl

2 获取控制文件相关信息的命令：
   select name from v$controlfile;
   select type,record_size,records_total,records_used from v$controlfile_record_section where type='datafile';

 

3 重做日志
  Forcing log switches and checkpoints
    alter system checkpoint
    alter system switch logfile

  Adding online redo log groups
    alter database add logfile group3
     ('$home/oradata/u01/log3a.rdo','$home/oradata/u02/log3b.rdo')
     size 1m;

  Adding online redo log members
     alter database add logfile member
     '$home/oradata/u04/log1c.rdo' to group 1,
     '$home/oradata/u04/log2c.rdo' to group 2,
     '$home/oradata/u04/log3c.rdo' to group 3;

  Droping online redo log groups
     alter database drop logfile group  3;

  Droping online redo log members
     alter database drop logfile member '$home/oradata/u04/log3c.rdo';

  Clearing ,relocating or renaming online redo log files
     alter database clear logfile
       '$home/oradata/u01/log2a.rdo';

  Managing online redo logs with omf
     alter database add logfile
     alter database drop logfile group3;

  获取redo log 相关信息
  select group# ,sequence#,bytes,members,status from v$log;

  获取组成员
  select member from v$logfile

  归档日志相关信息
  select archiver from v$instance;

4 表空间
  创建locally managed tablespace
    create tablespace userdata
    datafile '/u01/oradata/userdata01.dbf' size 500m
    extent management local uniform size 256k;

  创建dictionary managed tablespaces
    create tablespace userdata
    datafile '/u01/oradata/userdata01.dbf' size 500m
    extent management dictionary
    default storage (initial 1m next 1m);

  改变表空间的存储
    alter tablespace userdata minimum extent 2m

    alter tablespace userdata default storage (
      initial 2m
      next 2m
      maxextents 999);

   创建回滚表空间
      create undo tablespace undo
        datafile '/u01/oradata/undo01.dbf' size 40M;

   创建临时表空间：
      create temporary tablespace temp
        tempfile '/u01/oradata/temp01.dbf' size 500M
        extent management local uniform size 10M;

   把表空间设为离线
      alter tablespace userdata offline
      alter tablespace userdata online

   把空间设为只读
      alter tablespace userdata read only

   移除tablespace
      drop tablespace userdata including contents and datafiles;

   Enabling automatic extension of data files:
      alter database datafile '/u01/oradata/userdata02.dbf' size 200m
      autoextend on next 10M maxsize 500M

   Changing the size of data files manually:
      alter database datafile '/u03/oradata/userdata02.dbf' resize 200M;

   Adding datafile to a tablespace
      alter tablespace app_data add datafile '/u01/oradata/userdata03.dbf' size 200M;

   Moving data file:
      alter database rename file '/u01/oradata/system01.dbf' to '/u03/oradata/system01.dbf'
      alter tablespace userdata rename datafile '/u01/oradata/userdata01.dbf' to '/u01/oradata/userdata01.dbf';

   Creating a tablespace with OMF :
      修改db_create_file_dest参数
      或动态修改alter system set db_create_file_dest = '/u01/oradata/db01';
      创建表OMF控制下的表空间
        create tablespace app_data datafile size 20M;

      查看表空间的名称和大小
        select tablespace_name,round(sum(bytes/(1024*1024)),0) ts_size from dba_data_files  group by  tablespace_name

      查看数据文件的IO
        select phyrds,phywrts,d.name from v$datafile d ,v$filestat f where d.file#=f.file#  order by d.name

      查看表空间的使用状况
        select a.tablespace_name,a.bytes total ,b.bytes used, c,bytes free,
        (b.bytes*100)/a.bytes "% Used",(c.bytes*100)/a.bytes "%free"
        from sm$ts_avil a sm$ts_used b,sm$ts_free c
        where a.tablespace_name = b.tablesapce_name and a.tablespace_name = c.tablespace_name

5 存储
  
   创建一个表空间，Segment自动管理
   CREATE TABLESPACE DATA02
   DATAFILE '/U01/ORADATA/DATA02.DBF' SIZE 5M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
   SEGMENT SPACE MANAGEMENT AUTO;

   获取segment的信息
   SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS FROM DBA_SEGMENTS WHERE OWNER='HR';
 
   SELECT EXTENTS_ID,FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE OWNER='HR' AND SEGMENT_NAME = 'EMPLOYEES';

   SELECT TABLESPACE_NAME,COUNT(*),MAX(BLOCKS),SUM(BLOCKS)  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME

6 Undo:
  automatic undo management
  自动回滚管理，要设定两个参数：
     undo_management=auto
     undo_tablespace=undotbs

  创建UNDO表空间：
     create database db01
     undo tablespace undo1 datafile 'undo1db01.dbf'
     size 20m autoextend on

     create undo tablespace undo1
     datafile 'undodb01.dbf' size 20m

   改变undo表空间
     alter tablespace undotbs
     add datafile 'undotbs2.dbf' size 30m
     autoextend on;
   还可以使用以下选项
     add datafile
     rename
     datafile [online|offline]
     begin backup
     end backup

   使用一个新的UNDO表空间
     alter system set undo_tablespace=undotbs2;

   删除一个undo表空间
     drop tablespace undotbs2

  确定一个undo tablespce里是否还有活动的事务
     select a.name,b.status from v$rollname a, v$rollstat b
     where
       a.name in (select segment_name from dba_segments where tablespace_name = 'undotbs')
     and
       a.usn = b.usn;

   UNDO data statistics
     select end_time,begin_time ,undoblks from v$undostat;

   查看undo segment 信息
     select segment_name,owner ,tablespace_name,status from dba_rollback_segs
     select n.name,s.extents,s.resize,s.hwmsize,s.xacts,s.status
       from v$rollname n,v$rollstat s where n.usn =s.usn
     select s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
       from v$session s,v$transaction t where s.saddr = t.ses_addr;

 

(未完待续 下载 )