ITPub博客

在用到package dbms_metadata的时候发现问题SQL> desc dbms_metadata ERROR: ORA-03117: two-task save area overflow From metalink :Subject: Describe of DBMS_METADATA Raises 'ORA-3117: two-task save area overflow' Doc ID: 362447.1 Type: PROBLEM Modified Date : 22-MAY-2007 Status: PUBLISHEDApplies to:Oracle Server - Enterprise Edition - Version: 10.2This problem can occur on any platform.SymptomsFrom 10.2 SQL*Plus, an attempt is made to describe a large PL/SQL ob

SQL> select * from v$flash_recovery_area_usage;FILE_TYPE PERCENT_SPACE_USED------------------------------------------------------------ ------------------PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES------------------------- ---------------CONTROL FILE 0 0 0REDO LOG 0 0 0ARCHIVED LOG 92.64 0 229FILE_TYPE

1.发现问题，从statspack中抓到一个sql需要的buffer gets和disk reads很高STATSPACK SQL report for Hash Value: 2088862589 Module: InSiteXMLServer.exeDB Name DB Id Instance Inst Num Release Cluster Host------------ ----------- ------------ -------- ----------- ------- ------------INST933 3144597339 inst933 1 9.2.0.8.0 NO mesdb01Start Id Start Time End Id End Time Duration(mins)--------- ------------------- --------- ------------------- --------------7017 16-Mar-08 00:00:04 7041 17-Mar-08 00:00:04 1,44

no more addition operation for this message[@more@]

1.ORA-00600: internal error code, arguments: [504], [0x70000000006C900], [640], [7], [shared pool], [2], [0], [0x70000000006C808]solution : bug 升級到92072.ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []solution : To verify this, go to Tools > Preferences > Debugger tab page, disable the "Add debug information when compiling" option, and retry the compilation. PL/SQL Developer compile問題3.ORA-00600: internal error code, arguments: [kghpi

一般来说，OLTP 系统适合采用全局索引，而数据仓库系统或 DSS 系统适合采用本地索引。此外，用户应尽可能地使用本地索引，因为此种索引更易管理。在选择索引类型时，可以参考以下经验： 如果表的分区键（partitioning column）是索引键（index key）的子集，应使用本地索引。否则继续参考经验 2。 如果索引为唯一索引（unique），应使用全局索引。否则继续参考经验 3。 如果用户对可管理性的要求更高，应使用本地索引。否则继续参考经验 4。 如果应用系统为 OLTP，且对系统的响应时间要求较高，应使用全局索引。如果应用系统为 DSS，且对系

Developer met a error ora-30496 when modify column length.SQL> ALTER TABLE Test MODIFY (ID CHAR(10));ORA-30496: argument should be a constant.It maybe a bug , in version 9201 and cursor_sharing=similar.solution:alter session set cursor_sharing=exact;[@more@]

From alert log :< ORA-12012: error on auto execute of job 1 < ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated < ORA-06512: at "PERFSTAT.STATSPACK", line 1361 < ORA-06512: at "PERFSTAT.STATSPACK", line 2471 < ORA-06512: at "PERFSTAT.STATSPACK", line 91 < ORA-06512: at line 1environment : Redhat AS4 , oracle9208Bug number : 27847961.ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

Steps to export/import db statistics between 2 databases.Here schema_name1 = schema_name2rem 1) Create the holding table using DBMS_STATS in production DB1exec dbms_stats.create_stat_table('schema_name1','table_name1');rem 2) Move the statistics to the STATS holding table in Production DB1 for backup purpose exec dbms_stats.export_schema_stats('schema_name1','table_name1');rem 3) Create the holding table using DBMS_STATS in DB2exec dbms_stats.create_stat_table('schema_name2','table_name2');rem 4

1) create sqlnet.ora in $ORACLE_HOME/network/admin with this content# SQLNET.ORA Network Configuration File: /oracle/app/orai911/product/9.2.0.1.0/network/admin/sqlnet.ora# Generated by Oracle configuration tools.tcp.validnode_checking = yestcp.invited_nodes = (szmesdb01)2) reload oracle listener Lsnrctl reload test[@more@]

SQL> select * from test;CARD Q1 Q2---------- ---------- ----------a 1 12a 1 21a 2 43a 2 65b 1 12b 2 87b 2 17 rows selected.SQL> SELECT 2 a.card,a.q1,3 SUM(decode(a.q1, 1, a.q2, 0)) x1, 4 SUM(decode(a.q1, 2, a.q2, 0)) x25 FROM test a 6 GROUP BY a.card,a.q17 ORDER BY 1; CARD Q1 X1 X2---------- ---------- ---------- ----------a 1 33 0a 2 0 108b 1 12 0b 2 0 88SQL> select * from test1;CARD Q1 Q2 Q3 Q4---------- ---------- ---------- ---------- ----------a 3 1 2 3b 1 2 3 4SQL> SELECT 2 t.c

取消註冊catalogSQL> select db_key,db_id from db; db_key db_id 429 201401412SQL> execute dbms_rcvcat.unregisterdatabase(429,201401412); /**** MAINTENANCE COMMANDS ****/ CROSSCHECK BACKUP; delete expired backup; REPORT NEED BACKUP; DELETE OBSOLETE; DELETE ALL OBSOLETEDELETE ALL EXPIRED EXPIRED 表示备份集在磁盘或者磁带上不存在了OBSOLETE 表示根据保存策略判断备份集不再需要了LIST BACKUP SUMMARY; list backup of controlfile; list backupset; restore database validate; /* check if database can be re

常用的分析函数如下所列:row_number() over(partition by ... order by ...)rank() over(partition by ... order by ...)dense_rank() over(partition by ... order by ...)count() over(partition by ... order by ...)max() over(partition by ... order by ...)min() over(partition by ... order by ...)sum() over(partition by ... order by ...)avg() over(partition by ... order by ...)first_value() over(partition by ... order by ...)last_value() over(partition by ... order by ...)lag() over(partition by ... order b

$ more create9idb.sqlspool CreateDB.log-- shutdown immediate;-- shutdown abort;-- !strings /oracle/305/oracle/product/9.2.0/dbs/spfileinstance.ora > /oracle/305/oracle/product/9.2.0/dbs/pfileinstance.orastartup nomount pfile=/oracle/app/oracle/product/9.2.0/dbs/pfileinstance.ora;!mkdir -p /oracle/305/oradata/instance!mkdir -p /oracle/305/redo1/instance!mkdir -p /oracle/305/redo2/instance!mkdir -p /oracle/305/arch/instance!rm /oracle/305/oradata/instance/*.ctl!rm /oracle/305/oradata/instance/*

SQL> conn /as sysdbaConnected.SQL> show parameter utl SQL> @?/rdbms/admin/dbmslm.sqlPackage created.Grant succeeded.SQL> @?/rdbms/admin/dbmslmd.sqlProcedure created.No errors.Grant succeeded.PL/SQL procedure successfully completed.Package created.SQL> alter system set utl_file_dir='/oracle/304/arch' scope=spfile;System altered.SQL> shutdown immediateSQL> startupSQL> execute dbms_logmnr_d.build ('test.ora','/oracle/304/arch');PL/SQL procedure successfully completed.SQL>

1. 创建用户名,组2. 创建相同的目录Datafile redo log arch bdump cdump create udump配置tnsnames.ora listener.ora拷贝pfile 修改,创建spfile3. SQL> startup nomount; ORACLE instance started.Total System Global Area 504856256 bytesFixed Size 741056 bytesVariable Size 167772160 bytesDatabase Buffers 335544320 bytesRedo Buffers 798720 bytes-bash-3.00$ rman target / catalog system/system@inst1002RMAN> set dbid=3157572766executing command: SET

1. install 9208 patch set2. alter system set java_pool_size='150M' scope=spfile;3. shutdown immediate startup4.spool log @?/rdbms/admin/catpatch.sql shutdown immediate startup5. @?/rdbms/admin/utlrp.sql6.cd $ORACLE_HOME/install ./changePerm.sh[@more@]

System Information Source systemOracle: 9.2.0.7Destination SystemOracle: 10.2.0.2 1. run check script on source DB p copy $ORACLE_HOME_10g/rdbms/utlu102i.sql to temporary locationchange to temporary locationrun sqlplus> @utlu102i.sql 2. deactivate jobs p sqlplus '/ as sysdba'>show parameter job>alter system set job_queue_processes = 0;stop all backup jobs 3. create pfile p create pfile=’…’ from spfile; 4. shutdown database p ShutdownCold Data

1.Check package OS AS4 update 4rpm 版本必须高于下面的版本，使用rpm –qa | grep 查询compat-db-4.1.25-9 compat-gcc-32-3.2.3-47.3 compat-gcc-32-c++-3.2.3-47.3 compat-libgcc-296-2.96-132.7.2 compat-libstdc++-296-2.96-132.7.2 compat-libstdc++-33-3.2.3-47.3 gcc-3.4.3-9.EL4 gcc-c++-3.4.3-9.EL4 gnome-libs-1.4.1.2.90-44 gnome-libs-devel-1.4.1.2.90-44 libaio-devel-0.3.102-1 libaio-0.3.102-1 make-3.80-5 openmotif21-2.1.30-11 xorg-x11-deprecated-libs-devel-6.8.1-23.EL xorg-x11-deprecated-libs-6.8.1-23.EL 打

1.Check OS packageSUNWarc SUNWlibms SUNWi1of　SUNWbtool 　SUNWsprotSUNWi1cs　SUNWhea SUNWi15cs SUNWlibm　SUNWtoo SUNWxwfnt# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt2. vi /etc/systemset semsys:seminfo_semmni=100#set semsys:seminfo_semmns=1024set semsys:seminfo_semmsl=256#set semsys:seminfo_semvmx=32767set shmsys:shminfo_shmmax=34359738368set shmsys:shminfo_shmmni=1003. #/usr/sbin/groupadd oinstall#/usr/sbin/groupadd dba# /usr/