oracle查询二维检查表,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/，如需转载，请注明出处，否则将追究法律责任。