oracle 常用查询/oracle common query

create table tom1 (id int,age varchar(10),name varchar(20));
insert into tom1 values(1,'20','tom');
select * from tom1;

##查找当前SGA值
show parameter sga_target
select * from v$sgastat;

##查找在给定时间内分配给PGA的最大值
select value from v$pgastat where name='maximum PGA allocated'；

##sga_target + 分配给PGA的最大值 =MEMORY_MAX_TARGET

##检查内存组件当前尺寸（SGA/PGA）
v$memory_dynamic_components

##得到设置MEMORY_TARGET 参数的调优建议
select * from v$memory_target_advice order by memory_size;

##检验自动统计数据收集作业信息
select job_name from dba_scheduler_jobs where job_name like 'GATHER_STAT%';

##禁用统计数据的自动收集功能
BEGIN
dbms_scheduler.disable('gather_stat_job');
END;
/

##设置表空间容量预警
execute dbms_server_alert.set_threshold(-
dbms_server_alert.tablespace_pct_full,dbms_server_alert.operator_ge,'80',-
dbms_server_alert.operator_ge,'95',1,1,null,-
dbms_server_alert.object_type_tablespace,'TEST_ALERT');

##检查表空间预警
select reason from dba_outstanding_alerts;

##查找特定监控项的当前阈值
select metrics_name, warning_value, critical_value, consecutive_occurrences from dba_thresholds where metrics_name like ‘%CPU Time%';

##需要借助AWR快照数据的工具
ADDM/SQL Tuning Advisor/Undo Advisor/Segment Advisor

##执行当前活动会话的分析（每秒从V$SESSION中收集会话数据样本）
V$ACTIVE_SESSION_HISTORY
                                          
##执行历史活动会话的分析
DBA_HIST_ACTIVE_SESSION_HISTORY


##查找活动连接        *****
select process,osuser,username,machine,logon_time ,sql_text from v$session a,v$sqltext b where a.sql_address=b.address;

##查看oracle下用户的状态
select username,account_status from dba_users;

##解锁用户
alter user SECFIN_DEV account unlock
##
##查找当前用户下的所有表名
select unique tname from col;

##查找当前用户所拥有的表信息
select * from user_tables;

##查找oracle用户
select * from dba_users;
select * from all_users;
select * from user_tables;


##查找schedule jobs 信息
select owner,job_name,job_type from dba_scheduler_jobs;

##自动维护任务
Automatic Optimizer Statistics Collection(自动优化器统计数据收集)
Automatic Segment Advisor(自动段顾问)
Automatic Sql Tuning Advisor(自动SQL调优顾问)

##ADR结构
select * from v$diag_info;


##配置OEM
emca -config dbcontrol db

########################################################################################################
##***********************************  ADDM  *********************************************************##
########################################################################################################

##查找DBIO默认值
select parameter_value from dba_adviser_def_parameters where advisor_name='ADDM' and parameter_name='DBIO_EXPECTED';

##执行手动ADDM
EXEC dbms_workload_repository.create_snapshot();

##视图显示了数据库中的全部ADDM建议
DBA_ADVISOR_RECOMMENDATIONS

##视图显示了数据库中所有顾问的的判定
DBA_ADVISOR_FINDINGS

##视图显示了对所有建议的解释
DBA_ADVISOR_RATIONALE

##视图显示了执行ADDM建议所需要的所有行为
DBA_ADVISOR_ACTIONS











自动区管理  ？
自动段空间管理 ？
oracle_base/oracle_home ?
oracle B树  ?
LD_LIBRARY_PATH ?
######keep in mind###
##several views for management tablespace
dba_tablespaces
dba_free_spaces
dba_segments
dba_data_files
dba_tablespace_groups
v$datafile
v$filestat
##transfer files between different databases
dbms_file_transfer.copy_file
dbms_file_transfer.get_file
dbms_file_transfer.put_file
##check each objects space_usage
dbms_space
##Management tables
dba_tables
dba_tab_partitions
dba_tab_columns ##check the table's column length/values
dbms_metadata  ##fetch the ddl of create table/index
v$object_usage ##chcek if the specify index was been used or not
dba_constraints ##provide all the info of constraints
dba_cons_columns ##check each columns info
dba_synonyms ##check all the info about synonyms
dba_triggers ##check all the info about trigger
dba_sequences ##check all the info about sequences
dba_db_links ##check all the info about links
dba_objects ##all the object info
dba_tables ##check all the info about tables
dba_views ##check all the info about views (including the view's contents)
dba_mviews ##check all the info about materialized view
dba_ind_columns ##check each columns index info
index_stats ##check the effective of index
v$undostat  ##find out the longest select query
dba_rollback_segs  ##check which undo segment are online or not
##select segment_name, table_space_name, status from dba_rollback_segs
v$transaction
dba_undo_extents  ##provide undo extents info based on the undo space
select current_scn from v$database; ##check the current scn
select comp_id, comp_name, version, status from dba_server_registry; ##check each component's status after upgrade
select name, value, isdefault from v$parameter; ##check all the initial parameter (init.ora/spfile)
show parameter [keyword] ##check all the initial parameter (init.ora/spfile)
archive log list ##check if archive mode or not
select instance_name, status, shutdown_pending, active_state from v$instance  ##monitor the instance of oracle from oracle dictionary
select name, log_mode, flashback_on from v$database;  ##check all the info for oracle database
select * from v$pwfile_users;  ##check which user are belong to sysdb/sysoper
select * from dba_directories;  ##check where is the default pump directory
select sid, serial#, from v$session s,dba_datapump_sessions d where s.saddr = d.saddr  ##check the info of data pump seesion
select opname, target_desc, sofar, totalwork from v$session_longops;  ##estimate the time of the process of expdp/impdp;
execute sys.dbms_tts.transport_set_check('tbs1','tbs2',true)  transport_set_violation ##check if all the objects are existed in the same tbs
select t.endian_format from v$transportable_platform t, v$database d where t.platform_name = d.platform_name;  ##check the endian type
DB_RECOVERY_FILE_DEST_SIZE  ##inital parameter of flash recovery area size
DB_RECOVERY_FILE_DEST  ##inital parameter of flash recovery area destination
v$recovery_file_dest  ##check the situation of flashback area
v$flash_recovery_area_usage  ##check the space usage of each type objects
select * from v$rman_configuration;  ##check which parameter configured
select operation, status from v$rman_status;  ##check the process of rman restore/recovery
select file#, status, error, recover, tablespace_name, name, from v$datafile_header where recover ='YES' or (recover is null and error is not null);  ##check which data file need to restore or recovery
select file#, error, online_status, change#, time from v$recover_file;  ##check the status of datafile
select repair_id, advise_id, summary, rank from v$ir_repair;  ##check the suggestions from data recovery adviser
select name, scn, storage_size, time, guarantee_flashback_database from v$restore_point;  ##check the info of restore point
select flashback_on from v$database;  ##check if the database running in flashback mode
recover datafile 8 block 21;  ##repair the data block corruption based rman tool
select * from v$database_block_corruption;  ##check out how many corruption block existed on database
recover corruption list;  ##list the block corruption










 
select filename, status, bytes from v$block_change_tracking;  ##trace the block
select file_name from dba_data_files  ##check out all the data files
select member from v$logfile  ##check out all the online redo logs
select name from v$controlfile  ##check out all the control file
select recovery_estimated_ios, eitimated_mttr, target_mttr, from v$instance_recovery;  ##check what's

the best time for fast recovery








######different
dba_tables VS dba_all_tables VS dba_external_tables VS dba_tab_partitions VS dba_part_tables VS

dba_tab_modifications









36、如何查询数据库中有哪些存储过程？
SELECT * FROM dba_objects WHERE object_type='PROCEDURE' and owner='STERN'';


37、如何得到创建存储过程(函数、包)的脚本？
SELECT text FROM user_source WHERE name='PROCEDURE_NAME' and type='PROCEDURE(FUNCTION/PACKAGE)';


38、如何知道存储过程中使用了哪些表？
select referenced_type,referenced_owner,referenced_name from dba_dependencies
where name='PROCEDURE_NAME' and owner='USER' and upper(referenced_type)='TABLE';


39、显示存储过程的编译错误？
show error


40、如何移动表champion从表空间kb8到表空间kb24？
ALTER TABLE champion MOVE TABLESPACE kb24;
ALTER INDEX champion _idx REBUILD  TABLESPACE kb24;


41、查询某一用户下所有表的记录数
select table_name,num_rows from dba_tables where owner='STERN';


42、何时使用基于函数的索引？
当sql语句的条件经常使用某个表达式或者某个函数时，用户可以创建基于函数的索引
CREATE INDEX AI_idx ON AI(upper(PG));


43、解决导出时候不导出空表问题：
show parameter deferred_segment_creation;
alter system set deferred_segment_creation=false;


44、查看数据库的版本
Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';


45、查看数据库信息及归档模式：
select dbid,name,log_mode,current_scn,CREATED from v$database;


46、如何备份sqlplus的环境变量值？
store set f:\nbastore\td;


47、查询数据库处于何种状态？
select open_mode from v$database;
select status from v$instance;

48、ORA-00205：error in identifying control file,check alert log for more info
控制文件受损，重命名并替换受损的控制文件。


49、查询用户fisher的表空间限额
select tablespace_name,username,max_bytes from dba_ts_quotas where username='FISHER';


50、如何确定数据库启动时使用SPFILE还是PFILE？
show parameter spfile;


51、查看默认临时表空间和查询临时表空间？
select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
select distinct tablespace_name from dba_temp_files;


52、如何查看回滚表空间？
show parameter undo_tablespace
select tablespace_name from dba_tablespaces where contents='UNDO';


53、查出一个用户所拥有的角色
select granted_role from dba_role_privs where grantee='DAVID';


54、查看一个表或者索引所占用的空间
SELECT owner, segment_name,  SUM(bytes)/1024/1024
     FROM dba_segments
    WHERE owner= 'STERN' And segment_name='SHARK'
   GROUP BY owner,segment_name；


55、验证是否使用本地管理以及ASSM
select tablespace_name,extent_management,segment_space_management from dba_tablespaces;


56、查询有哪些数据库实例在运行
select inst_name from v$active_instances;


57、alter index index_name rebuild和Alter index  index_name coalesce？

表在频繁的增删改后，就会造成index对应的block不完整，和系统碎片的情况是一致的，造成空间浪费，加大index的I/O，影响性能。
rebuilding indexes有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数，重新构建一个高效的索引，但重构时会锁表。而coalescing indexes对于有些索引中存在大量空间浪费的情况下，整理索引碎片合并索引中同一级的leaf block，效率高，不锁表。


58、如何使用直接加载大批量数据？
直接加载最常见的是和NOLOGGING一起使用，可以有效的减少REDO的生成量；
如果直接加载的表上有索引，oracle不会像加载数据的方式来处理索引的数据，但因需要维护索引，同样会产生很多的REDO；
当使用直接加载时，通常是针对一些数据量非常大的表。如果这些表存在索引，将会带来很大的性能影响，这时可以考虑先将索引DISABLE掉，然后加载数据，之后再重新建立索引。


59、alter table move与alter table shrink space？
alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片。
alter table move操作后，数据的rowid发生了改变table上的index需要rebuild，而且执行过程中占用很多表空间：alter table shrink space执行后不会导致索引失效，但调整HWM位置，释放空闲数据块，在业务特别繁忙的系统上可酌情使用。


60、purge和flashback？
当执行drop语句意外删除了表时，表仍然存在于该表空间中，并且被放置在一个oracle称为“回收站”的逻辑容器中，可以使用flashback进行恢复；
使用DROP TABLE PURGE则是永久删除该表，相当于window系统中使用shift+detelte进行文件删除。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27799221/viewspace-2128722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27799221/viewspace-2128722/