ITPub博客

首页 > 数据库 > Oracle > 将自己最常用的一些脚本放上来,这样方便查询

将自己最常用的一些脚本放上来,这样方便查询

原创 Oracle 作者:jixuewen 时间:2007-11-05 18:57:16 0 删除 编辑
常用的脚本或者视图,记不住的都放上来。[@more@]

dictionary数据字典视图
v$fixed_table动态性能表
col A0 format a20; set colsep |
select name,value$ from props$ where name='NLS_CHARACTERSET'
alter session set nls_date_format="MM-DD-YYYY hh24:mi:ss";
sqlldr userid=omcr/a control=loadsysclass.txt data= class.txt
load data infile 'd:car.csv' append into tables jxw fileds terminated by "," (id,go)
exec statspack.snap -生成统计
@$ORACLE_HOME/rdbms/admin/spreport
dba_part_tables
dba_part_indexes查询索引的情况
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
对于端口号,需要看$ORACLE_HOME/install/portlist.ini来看。
select tablespace_name, initial_extent from dba_tablespaces
where allocation_type='UNIFORM';
查询建表的DDL
SELECT DBMS_METADATA.GET_DDL('TABLE','TABLENAME','SCHEMA') FROM DUAL;
v$fixed_view_definition查询基本表
dba_tab_partitions
alter table xxx drop partition part_xxx update global indexes;
alter index PERFNODEB_INDEX rebuild partition partition_name;
dba_ind_partitions
user_source查看过程源代码 常用对象: user_objects; v$bh; dba_dml_locks
execute dbms_logmnr.start_logmnr(DictFileName => 'd:oracledict.ora');
analyze table abc compute statistics;
execute dbms_stats.GATHER_DATABASE_STATS;
sql_trace:
alter session set events '10046 trace name context forever,level 12'
alter session set events '10046 trace name context off';

解释计划: @?/rdbms/admin/utlxplp.sql
select * from table(dbms_xplan.display);
dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
dbms_system.set_sql_trace_in_session(sid,serial#,false)
dbms_session.set_nls('NLS_DATE_LANGUAGE','AMERICAN');
revoke dba from omcr;
grant connect to omcr;
grant RESOURCE to omcr;
grant select_catalog_role to omcr;
grant create any trigger to omcr;
grant administer database trigger to omcr;
oracle的官方文档performance tuning guide and reference
alter session set plsql_warnings='enable:informational'

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

请登录后发表评论 登录
全部评论
  • 博文量
    95
  • 访问量
    1994415