ITPub博客

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/*

[root ~]# more /etc/resolv.conf# Added by kickstart file# -----------------------domain test.comnameserver 165.204.224.33nameserver 165.204.224.2[root ~]# grep hosts /etc/nsswitch.conf#hosts: db files ldap nis dnshosts: dns files [root ~]# time nslookup szcadminServer: 165.204.224.33Address: 165.204.224.33#53Name: szcadmin.test.comAddress: 165.204.233.5real 0m0.079suser 0m0.004ssys 0m0.008s[root ~]# time nslookup localhostServer: 165.204.224.33Addr

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>

root # mkdir /tmp/1root # mkdir /tmp/2root # mkdir /tmp/3root # mkdir /tmp/4 /tmp/5root # pwd/mnt/inst/RHEL4-U4-x86_64/rhel4u4root # lofiadm -a /mnt/inst/RHEL4-U4-x86_64/rhel4u4/RHEL4-U4-x86_64-AS-disc1.iso /dev/lofi/1root # lofiadm -a /mnt/inst/RHEL4-U4-x86_64/rhel4u4/RHEL4-U4-x86_64-AS-disc2.iso /dev/lofi/2root # lofiadm -a /mnt/inst/RHEL4-U4-x86_64/rhel4u4/RHEL4-U4-x86_64-AS-disc3.iso /dev/lofi/3root # lofiadm -a /mnt/inst/RHEL4-U4-x86_64/rhel4u4/RHEL4-U4-x86_64-AS-disc4.iso /dev/lofi/4root #

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/