1. 数据库
select name from v$database; : 数据库名称
select db_unique_name from v$database; : 数据库唯一名称
select dbid from v$database; : 数据库id
select to_char(created, ‘yyyy/mm/dd hh24:mi:ss’) from v$database; ：数据库创建时间
select log_mode from v$database; : 日志归档模式
select open_mode from v$database; : 打开模式
select database_role from v$database; : 数据库角色
select value from v$parameter where name = ‘spfile’; : 服务器参数文件
select value from v$parameter2 where name=‘control_files’; : 控制文件列表
2. 数据文件
select name from v$tablespace; : 表空间列表
select name from v$datafiel; : 数据文件列表
select name from v$tempfile; : 临时文件列表
select group#, bytes/1024/1024 || ‘M’from v$log; : 日志组列表
sleect max(length(member)) from v$logfile;
column member format a36
select group#, member from v$logfile; : 在线日志文件列表
3. 备份文件
select count(*) from v$archived_log;
select count(*) from v4archived_log where name is not null;
select name from v$archived_log where name is not null; : 归档日志文件列表
select count(*) from v$backup_piece;
select count(*) from v$backup_piece where handle is not null;
select handle from v$backup_piece where start_time >= sysdata - 1 order by handle; : 备份文件列表
4. 用户与模式对象
select username from dba_users order by created; : 数据库用户对象
desc dba_objects
select owner, object_type, count(*) from dba_objects group by owner, object_type order by owner, object_type; : 模式对象数量列表
5. 实例与会话
select host_name from v$instance; : 主机名称
select instance_name from v$instance; : 实例名称
select value from v$parameter where name=‘service_names’; : 服务名称
select version from v$instance; : 数据库软件版本
select to_char(startup_time, ‘yyyy/mm/dd/hh24:mi:ss’) from v$isntance; : 实例启动状态
select status from v$instance; : 实例状态
select sid, serial#, username from v$session; ：当前会话列表
6. 文件的大小与分布
select sum(bytes)/1024/1024 || ‘M’ from v$datafile;
select round(sum(bytes)/1024/1024/1024) || ‘G’ from v$datafile; : 数据文件总大小
select sum(bytes)/1024/1024 || ‘M’ from v$tempfile;
select founc(sum(bytes)/1024/1024/1024) || ‘M’ from v$tempfile; : 临时文件总大小
select sum(bytes*members)/1024/1024 || ‘G’ from v$log;
select sum(block_size*file_size_blks)/1024/1024/1024) || ‘G’ from v$log; : 在线日志文件总大小
select sum(block_size*file_size_blks)/1024/1024 || ‘M’ from v$controlfile; : 控制文件总大小
selece count(*) from v$archived_log;
select min(block_size*blocks) from v$archived_log;
select max(block_size*blocks)/1024/1024 || ‘M’ from v$arhived_log;
select sum(block_size*blocks)/1024/1024/1024 || ‘G’ from v$archived_log;
select round(nvl(sum(block_size*blocks),0)/1024/1024/1024) || ‘G’ from v$archived_log where name is not null; : 归档日志文件总大小
select count(*0) from v$backup_piece;
select sum(bytes)/1024/1024/1024 | ‘G’ from v$backup_piece;
select round(sum(bytes)/1024/1024/1024) || ‘G’ from v$backup_piece where handle is not null; : 备份文件总大小
select distinct substr(name, 1, instr(name, ‘/‘, -1) from v$datafile; : 数据文件分布
select distinct substr(name, 1, instr(name, ‘/‘, -1)) from v$datafile; : 临时文件分布
select distinct substr(member, 1, instr(member, ‘/‘, -1) from v$logfile; : 在线日志文件分布
select distince substr(name, 1, instr(name, ‘/‘, -1)) from v$controlfile; : 控制文件分布
select distinct substr(name, 1, instr(name, ‘/‘, -1)) from v$archived_log; : 归档日志文件分布
select distinct substr(handle, 1, instr(handle, ‘/‘ -1)) from v$backup_piece; : 备份文件分布
select substr(value, 1, instr(value, ‘/‘, -1)) from v$parameter where name=‘spfile’;
select value from v$parameter where name = ‘background_dump_dest’;
ho ls -l /oprt/app/oracle/admin/DAL2AP/bdump/alert* : 告警日志位置
select value from v$parameter where name=‘background_dump_dest’; : 后台跟踪日志位置
select value from v$parameter where name = ‘user_dump_dest’; : 用户跟踪日志位置
select value from v$parameter where name = ‘core_dump_dest’; : 内核跟踪日志位置
7. 磁盘空间使用情况
各表空间占用率 ：
column “Toral_Space” fro a10
column “Free_Space” for a10
column “Used_Space” for a10
column “Free_Percentage” fro a6
colummn “Used_Percentage” fro a16
set pagesize 0
select df.tablespace_name, round(df.bytes/1024/1024, 2) || ‘M’ as “Total_Space”, round(nvl(fs.bytes, 0)/1024/1024, 2) || ‘M’ as “Free_Space”, round((df.bytes-nvl(fs.bytes, 0))/1024/1024, 2) || ‘M’ as “Used_Space”, round(nvl(fs.bytes, 0)/df.bytes, 4)*100 || ‘%’ as “Free_Persentage”, round(1-nvl(fs.bytes, 0)/df.bytes, 4)*100 || ‘%’ as “Used_Percentage” from (select tablespace_name, sum(bytes) as bytes from dba_data_files group by tablespace_name) df, (select tablespace_name, sum(bytes) as bytes from dba_data_files group by tablespace_name) df, (select tablespace_name, sum(bytes) as bytes from dba_free_space group by tablespace_name)fs where df.tablespace_name = fs.tablespace_name(+) order by df.tablespace_name;
主机资源使用情况 ：
desc v$resource_limit
select max(length(resource_name)) from v$resource_limit;
column resource_name format a21
select resource_name, current_utilization, max_utilization, initial_allocation, limit_value from v$resoure_limit;
数据库版本 ：
desc v$version
select banner from v$version;
数据库选项 ：
desc v$option
select max(length(parameter)), max(length(value)) from v$option;
column parameter format a35
column value fromat a5
select parameter, value from v$option;
最大进程数 ：
select value from v$parameter where name=‘process’;
最大会话数 ：
select value from v$parameter where name = ‘sessions’;
数据块大小 ：
select value from v$parameter where name = ‘db_block_size’;
游标共享模式 ：
select value from v$parameter where name = ‘cursor_sharing’;
多块读批量 ：
select value from v$parameter where name = ‘db_file_multiblock_read_dount’;
回滚段表空间 ：
select value from v$parameter where name = ‘undo_tablespace’;
回滚段保留时间 ：
select value from v$parameter where name = ‘undo_retention’;
回滚段管理模式 ：
select value from v$parameter where name = ‘undo_management’;
系统全局区目标大小 ：
select value/1024/1024 || ‘M’ from v$parameter where name = ’sga_target’;
系统全局区最大值 ：
select value/1024/1024 || ‘M’ from v$parameter where name = ‘pga_aggregate_target’;
程序全局区目标 ：
select value/1024/1024 || ‘M’ from v$parameter where name = ‘pga_aggregate_target’;
时间统计 ：
select value from v$parameter where name = ‘timed_statistics’;
初始化参数文件 ：
select value from v$parameter where name = ‘ifile’;
数据库名称 ：
select value from v$parameter where name = ‘db_name’;
数据库唯一名称 ：
select value from v$parameter where name = ‘db_unique_name’;
实例名称 ；
select value from v$parameter where name = ‘instance_name’;
服务名称 ：
select value from v$parameter where name = ‘service_names’;
段的总数量 ：
select count(*) from user_segments;
各类型段的数量 ：
select segment_type group by segment_type order by segment_type;
大段列表 ：
desc user_segments
select max(length(segment_name)) from user_segments;
column segment_name format a30
column mb format a8
select max(length(tablespace_name)) from dba_tablespaces;
column tablespace_name format a13
select segment_name, tablespace_name, round(bytes/1024/1024) || ‘MB’ as MB, extents from user_segments where segment_type = ‘TABLE’ and bytes >= (1024*1024)*10 and rownum <= 5 order by bytes desc;
段的基本信息 ：
select segment_name, partition_name, segemnt_type, segment_subtype, tablespace_name, bytes, blocks, extents from user_segments where segment_name = ‘POSTPAY_BILLED_REVENUE’;
区的列表 ：
select segment_name, extent_id, bytes from user_extents where segment_name = ‘POSTPAY_BILLED_REVENUE’ order by extent_id’
区的大小分布 ：
select segment_name, bytes/1024/1024, count(*) from user_extents where segment_name = ‘POSTPAY_BILLED_REVENUE’ group by segment_name, bytes order by bytes;
用户存储空间消耗 ：
select user, round(sum(bytes/1024/1024/1024), 2) as sace_GB from user_segments;
前20个大段 ：
select segment_name, segment_type, tablespace_name, bytes, round(bytes/1024/1024/1024, 3) as space_GB from (select segment_name, segment_type, tablespace_name, bytes, extents from user_segments order by bytes desc) where rownu, <= 10;
表的基本信息 ：
select table_name, tablespace_name, num_rows, avg_row_len, last_analyzed, sample_size from user_tables where table_name = ‘POSTPAY_BILLED_REVENUE’;
字段的基本信息 ：
select table_name, column_id, column_name, data_type, data_length, nullable, num_distinct, num_nulls from user_tab_columns where table_name = ‘POSTPAY_BILLED_REVENUE’ order by column_id;
表的备注 ：
select table_name, table_type, comments from user_tab_comments where table_name = ‘POSTPAY_BILLED_REVENUE’;
字段的备注 ：
select table_name, column_name, comments from user_dol_comments where table_name = ‘POSTPAY_BILLED_REVENUE’;
表的约束 ：
select owner, constraint_name, constraint_type, table_name from user_constraints where table_naem = ‘POSTPAY_BILLED_REVENUE’ and constraint_type = ‘P’;
字段的约束 ：
select owner, constraint_name, table_name, column_name, position from user_cons_columns where constraint_name = ‘POSTRAY_BILLED_REVENUE_PK’ order by position;
所以的列表 ：
select table_name, index_type, uniqueness, tablespace_name, leaf_block, distinct_keys, clustering_factor, num_rows, last_analyzed from user_indexes where table_name = ‘POSTPAY_BILLED_REVENUE’;
索引的字段 ：
select table_name, index_name, column_position, column_name from user_ind_columns where table_name = ‘POSTPAY_BILLED_REVENUE’ order by index_name, column_position;
对象总数量 ：
select count(*) from user_objects;
对象分类数量 ：
select object_type, count(*) from user_objects group by object_type order by object_type;
数据库链接列表 ：
select * from user_db_links;
大对象列表 ：
select * from user_lobs;
物化视图列表 ：
select * from user_mviews;
视图列表 ：
select * from user_views;
了解业务表的例子 ：
select segment_name, segment_type, tablespace_name, round(bytes/1024/1024/1024, 2) || ‘GB’ as space_GB from user_segments where segment_name = ‘CUSTOMER’ and segment_type = ‘TABLE’;
select table_name, tablespace_name, num_rows, last_analyzed from user_tables where table_name = ‘CUSTOMER’;
select owner, constraint_name, constraint_type, table_name from user_constraints where table_name = ‘CUSTOMER’ and constraint_type = ‘P’;
LONG的显示设置 ：
show long
set long 7763
分页设置 ：
show pagesize
set pagesize 0
视图长度 ：
select owner, view_name, text_length from dba_views where view_name = ‘&view_name’;
视图定义 ：
select owner, view_name, text_length from dba_views where view_name = ‘&view_name’;
视图定义 ：
select text from dba_views where view_name = ‘&view_name’;
视图定义 ：
select dbms_metadata.get_ddl(‘VIEW’, ‘&view_name’, ‘CAMPAIGN’) from dual;
表的归属 ：
select owner, table_name, segment_type, bytes from dba_segments where segment_name = ‘&segment_name’;
多个段的大小 ：
select owner || ‘.’ ||segment_name as segment, segment_type, bytes/1024/1024 || ‘M’ as MB from dba_segments where segment_name in (‘&segment_name’);
索引的归属 ：
select owner,index_name, table_owner, table_name from dba_indexes where index_name = ‘&index_name’;
表的索引 ：
select owner, index_name, table_name from dba_indexes where table_name = ‘&table_name’;
表的索引字段 ：
select index_name, column_name, column_position from dba_ind_dolumns where table_name = ‘&table_name’;
索引的字段 ：
select index_name, column_name, column_position from dba_ind_columns where index_name = ‘&table_name’;
数据分布 ：
explain plan for select contactdatetime from campaign.UA_CONTACTHISTORY a where contactdatetime>systmestamp-200 and rownum=1;
select PLAN_ID, operation, options, object_name, id, parent_id from plan_table;
explian plan for select CONTACTDATETIME, CUSTOMERID from campaign.UA_DTLCONTACTHIST where CONTACTDATETIME>sysdate_&daysbefore and rownum=1;
select PLAN_ID, operation, options, object_name, id, parent_id from plan_table;
select to_char(CONTACTEATETIME, ‘yyyy-mm-dd’) as contactdatetime, CUSTOMERID from campaign.UA_DTLCONTACTHIST where CONTACTDATETIME>sysdate=&daysbefore and rownum=1;
explain plan for select count(CONTACTDATETIME) from campaign.UA_DTLCONTACTHIST where CONTACTDATETIME>sysdate-&daysbefore and CONTACDATETIME<sysdate=&daysbefore;
数据分布 ：
select to_char(CONTACTDATETIME, ‘yyyy-mm-dd’) as contact_datetime, count(CONTACTDATETIME) as contact_count from campaign.UA_DTLCONTACTHIST where CONTACTDATE
创建口令文件 ：orapwd
eg :
orapwd file = ‘$ORACLE_HOME/dbs/orapcms40’ password = oracle entries = 5 force = y;
文件系统（filesystme) ：方便管理
使用裸设备写入磁盘数据速度增快(raw)
自动存储管理(asm) : 兼顾性能和管理
把redolog中的文件内容dump下来，看其条目
SMON : 实例恢复
PMON :
startup : 等于以下三个命令
startup nomount
alter database mount
alter database open
startup nomount : 非安装启动（可执行重建文件，重建数据库）读初始化参数文件(init.ora)，启动实例(instance)
startup mount : 安装启动（可执行数据库日志文档，数据库介质恢复，使数据文件联机或脱机，重新定位数据文件，重做日志文件）打开控制文件
startup open : 打开数据文件， 日志文件
startup restrict : 约束方式启动， 只允许具有一定特权的用户访问
startup force : 强制启动方式
startup pfile=参数名 ：带初始化参数文件的启动方式
startup exclusive : 独占方式启动，只允许一个例程使用数据库
启动ASM实例 ：
export ORACLE_SID=‘+ASM’
sqlplus /nolog
conn / as sysasm
startup
磁盘组
文件
磁盘
物理快
安装ASM : http://www.cnblogs.com/Richardzhu/articles/2972256.html
故障组
盘符改变
ASM_POWER_LIMIT : 在系统上配置负载（控制rebalance的速度）
管理磁盘组 ：
create diskgroup
alter diskgroup
drop diskgroup
ASMCMD : 实用程序
ASMCA : asm configuration assistant
RMAN (recovery manager)
rman target /
backup database;
list backup; ： 列出备份的原数据信息
delete obsolete; : 删除过期的备份
RUN命令块（批处理）
eg :
run
{
allocate channel c1 device type disk format “/disk2%u”;
backup as backupset database;
sql ‘alter system archive log current’;
}
note : 有的命令只能放在run块运行，有些不能，有些都可
开启归档模式 ：
mount模式下
alter database archivelog
恢复窗口保留策略
快速恢复区
crosscheck backup; : 交叉检查备份文件
crosscheck archivelog all; : 交叉检查归档日志文件
delete expired archivelog all; : 删除失效的归档日志文件
list archivelog;
list copy;
list backup;
误删归档文件后该怎么做：
catalog x : 把一个文件加到控制文件
catalog start with ‘文件路径’
oerr ora 00313 : 查看错误号信息
recover database until time
官方手册 ：automatic storage management administrator’s guide
catalog : 恢复目录
常用于数据库较多的企业
rman target / @cmdfile1 : 执行cmdfile1脚本
查看控制文件内容 ：
1. 备份到跟踪文件 ：
alter database backup controlfile to trace as ‘/oradata/ctl.txt’;
查看udump下的trace
2. 或者dump该文件
3. strings control01.ctl
4. 查视图v$controlfile_record_section :
select * from v$controlfile_record_section;
5. 转储控制文件 ：
alter system set events ‘immediate trace name controlfile level 10’;
搭建catlog :
1. 创建catalog用户 ：
create user rcowner identified by rcpass temporary tablespace temp default tablespace rcat_ts quota unlimited on rcat_ts;
grant recovery_category_catlog , connect, resource to rcowner;
2. 连接 ：
connect catalog username／password@network_service_name
3. 在恢复目录中注册数据库
rman target
register database; : 同步数据
修改dbid ：
select dbid from v$database; : 查询dbid
使用dbnewid更改dbid
eg :
nid target=user/password@srv_name [dbname = new_dbname]
catalog backuppiece ‘file_name’;
恢复目录重新同步 ：
创建rman存储脚本 ：
create global script script_name
run块
显示 ：print script script_name
更新 ：replace script script_name
删除 ：delete script script_name
重新穿件丢失的恢复目录 ：
resync catalog
catalog start with
导入和导出恢复目录 ：
审计恢复目录 ：
upgrade catalog;(要执行两边）
删除恢复目录 ：
drop catalog;
使用虚拟专用目录 ：
创建rman虚拟专用目录 ：
1. 创建rman基本目录 ：
connect catalog catowner/oracle@catdb;
creae catalog;
2.
3.
4. 为11g客户机创建虚拟目录 ：
5. 对以前未列入目录的
target端的备份数据信息 到catalog端