I am data-modeling those days. Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) […]
Month: April 2007
How do i store the counts of all tables …
How do i store the counts of all tables … My answer to the question above using dbms_xmlgen SQL> select 2 table_name, 3 to_number( 4 extractvalue( 5 xmltype( 6 dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name)) 7 ,’/ROWSET/ROW/C’)) count 8 from user_tables; TABLE_NAME COUNT —————————— —— DEPT 4 EMP 14 BONUS 0 SALGRADE 5
CPU2007Apr
I just downloaded and installed the Critical Patch Update April 2007 $ lsnrctl stop … The command completed successfully $ sqlplus / as sysdba SQL> shutdown immediate … ORACLE instance shut down. SQL> quit $ cd /tmp/5901891 $ ORACLE_HOME/OPatch/opatch apply … Finish at Wed Apr 18 10:28:17 CEST 2007 $ lsnrctl start … The command […]
Anagram Help – Oracle9i Enterprise …
Anagram Help – Oracle9i Enterprise … I just posted a solution on the OTN forums where I am using a regular table function and a recursive procedure to generate anagrams select * from table(anagram.f(‘ABC’)); COLUMN_VALUE ———— ABC ACB BAC BCA CAB CBA
backup database keep forever logs
If you want to keep some backups forever, you maybe tried RMAN> backup database keep forever logs; Starting backup at 13.04.2007 13:58:04 … backup will never be obsolete archived logs required to recover from this backup will expire when this backup expires … Finished backup at 13.04.2007 13:58:23 but the problem is that the archivelogs […]
10’000 columns in a query
You cannot have more than 1000 columns in a view, but what about a query? I tried select 1,2,3,4,….,10000 from dual; it seems to work fine. However, when increasing, I am facing various errors : select 1,2,3,4,….,17000 from dual; * ERROR at line 1: ORA-00600: internal error code, arguments: [15201], [], [], [], [], [], […]
variable in a view
Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session Here we go SQL> create or replace package p […]
long lines in ps output
In Solaris, the ps output is truncated, so if you have a command with many long parameters, you will not see them all. If you are interested to see the parameters of a java program, /usr/bin/ps -ef will not give you the expected output $ ps -ef | grep java ldapusr 10744 10692 0 09:50:23 […]