Breaking down values in a large table
Listers, I have a table of 125M rows (not partitioned) which I am exporting. I want to break the export into 4 dmp files using the query command on the pk column. I am looking at how the best way of finding the values of the PK (number) which are at 25%, 50% and 75% ish for the table so that I can get 4 evenly sized exports My query line in the parameter file will be along the lines of where 1) log_no xx 2) log_no = xx and yy 3) log_no = yy and zz 4) log_no = zz I am thinking of a sql something like the following Select /*+ index ffs(table_name index_name) */ Log_no , floor(log_no / 4), count(*) From table_name group by floor(log_no / 4), log_no Version is 8.1.7.1 Can anybody help please Thanks John
RE: Breaking down values in a large table
Tom, Lisa, Stephane I did consider that but I was originally exporting using direct export and I could get the whole table out in 4 hours (compressed via a pipe). However the import takes 24 hours. I was looking at using a normal (via the buffer ) export with a query in so that I could import the 4 exports separately and in parallel. I think the issue with filesize/file and split is that it is still only a single import which will take 24 hours plus. I know the min and max log_no and I will try an export based around dividing the max log_no by 4 . The breakdown will not be accurate but it should be good enough for me Thanks for your input - welcome as always John -Original Message- Sent: 11 February 2003 15:27 To: [EMAIL PROTECTED] Listers, I have a table of 125M rows (not partitioned) which I am exporting. I want to break the export into 4 dmp files using the query command on the pk column. I am looking at how the best way of finding the values of the PK (number) which are at 25%, 50% and 75% ish for the table so that I can get 4 evenly sized exports My query line in the parameter file will be along the lines of where 1) log_no xx 2) log_no = xx and yy 3) log_no = yy and zz 4) log_no = zz I am thinking of a sql something like the following Select /*+ index ffs(table_name index_name) */ Log_no , floor(log_no / 4), count(*) From table_name group by floor(log_no / 4), log_no Version is 8.1.7.1 Can anybody help please Thanks John John, I am not sure it is worth the trouble. If you specify the PK : a) if Oracle uses the index to fetch each row it will probably be slower than what it should be b) if it doesn't you will scan your table four times instead of once. I presume you are under Unix ? And that you are on a multi-CPU machine ? In which case I'd rather try to set parallelism on the table, create a named pipe, export to the pipe and use 'split' if you really want 4 files. All in all, it will probably be faster. NOW, if what you ultimately want is being able to reload in parallel to 4 different partitions, it may of course be different but then you will sacrifice export speed to lesser import slowness ... and I'd rather think in terms of future partitions than mere number of rows. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN compatibility issues
You can upgrade the 816 catalog to be 817 compliant with the command RMAN upgrade catalog You need to be connected to an 8.1.7 target first to do this. It effectively recreates two packages DBMS_RCVMAN and DBMS_RCVCAT John -Original Message- Sent: 20 November 2002 16:39 To: Multiple recipients of list ORACLE-L Rachna - Yes, RMAN is VERY picky about Oracle versions. What I understand is that you are trying to use an 816 catalog to back up an 817 database. I think that you usually must keep your catalog at the level of the target database or higher. In other words, you could use an 817 catalog db to back up an 816 target, but not the other way around. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 20, 2002 9:21 AM To: Multiple recipients of list ORACLE-L DBAs, I experienced following while trying to test interoperatibility between 816 and 817. Any comments, experiences? Target - 817 Catalog - 816 rman rcvcat rman/rman@rcatqual connect target register database; . RMAN-06429: RCVCAT database is not compatible with this version of RMAN . Target - 816 Catalog - 817 rman rcvcat rman/rman@rcattest connect target register database; . RMAN-08006: database registered in recovery catalog . -Rachna -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachna Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Enabling export transportable tablespaces
Banarasi Are you using the Standard Edition of 8i, if so then tts is not available. You need the Enterprise edition HTH John -Original Message- Sent: 19 November 2002 13:18 To: Multiple recipients of list ORACLE-L Hi gurus please help me!! I was using ORACLE 8.1.7 on window NT 4.0 I need to move some of my production tablespaces to development system. I was unable to use transprtable tablespace feature. When i executed the EXP transport_tablespaces=y tablespaces= users file=tts.dmp the output will be like this Export done in WE8ISO8859p1 character set and WE8ISO8859p1 NCHAR character set EXP-00017: feature Export transportable tablespaces is needed, but not present in database ORA-00439: feature not enabled:Export transportable tablespaces EXP-0: Export terminated unsuccessfully I found Export transportable tablespaces parameter in V$OPTION and it was set to false. How I can be able to set it to true. Early replies appriciated Banarasi Babu T -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Banarasi Babu TIndonet -HYD INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query elapsed time
Dick V$session_longops has a column elapsed_seconds and a sid column HTH John -Original Message- Sent: 15 November 2002 18:59 To: Multiple recipients of list ORACLE-L Quick question, Does anyone know of a location in the V$ tables where the elapsed time of the current query is stored?? Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: listener.log parsing utility
Patrice,
I wrote the enclosed awk script to check the listener log and count the
number of connections from each target.
I am sure it could be adapted to suit your needs
The first few line of output looks like
Service :Host :User :tcp:ip address :No of Connections
===
service1 :C :\Oracle\Ora901\b:xxx:dedicated24
service2 :xxx :SMITHL1 :tcp:xx.xx.xxx.xxx47
/u00/oracle/john 227$ cat a.sh
Script starts here
echo Service :Host :User :tcp:ip address
echo ===
grep CONNECT listener.log |\
awk -F= '{print $3 :1: $6 :2: $7 :3: $9 :4: $10 :5: $11}' |\
sed 's/).*:1:/:/' | \
sed 's/).*:2:/:/' | \
sed 's/).*:3:/:/' | \
sed 's/).*:4:/:/' | \
sed 's/).*:5:/:/' | \
sed 's/).*$//' | \
awk -F: '{printf(%-8.8s:%-16.16s:%-16.16s:%-3.3s:%-10.15s\n, \
$1,$2,$3,$4,$5)}' | sort /tmp/j.lis
grep -v PROGRAM /tmp/j.lis /tmp/j1.lis
cat /tmp/j1.lis | awk '{if (data[$0]++ == 0)lines[++count] = $0}\
END{for (i = 1; i = count; i++)print lines[i],\tdata[lines[i]]}'\
|sort -n +4
rm /tmp/j.lis
rm /tmp/j1.lis
script ends here
HTH
John
-Original Message-
Sent: 14 November 2002 13:09
To: Multiple recipients of list ORACLE-L
Is there such a utility on the 'net somewhere?
I would like to parse the log automatically, and get summaries of connection
attempts, rejected connections, errors (if any), who connects, for how long.
I have auditing set up in our db and can get some of that info, but I would
like to monitor the contents of listener.log as well.
Just curious.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO | Région des Maritimes, MPO
E-Mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: cant set multiblock read count 8 !!!
Rahul, I believe there is an O/S maximum setting of this parameter to be 64K (this covers AIX and HP and and may affect others) This fits in with your finding so I am sure it is correct John -Original Message- Sent: 12 November 2002 09:11 To: Multiple recipients of list ORACLE-L list !! my ora7.3.2 instance on AIX with a db_block_size of 4k has a multi block read count of 16 !! i thought i would create a new instance with a block size of 8k, so i can set the max multiblock read count to more than 16, BUT, after creating the new instance the db_multiblock_read_count *always* defaults to 8 !! here the new instance params db_files = 16 db_file_multiblock_read_count = 32 db_block_buffers = 3840 db_file_simultaneous_writes = 8 db_block_lru_latches = 8 shared_pool_size = 31457280 which of these parameter is affecting the value of multiblock read count ??? TIA Rahul PS: i hv checked the multiblock reads using ixora script... always 8 !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL loader
Hello Nirmal,
The FILLER command is available in sqlloader to allow you to miss a field
out
something like
LOAD
(field 1 ...
field2 ...
dum_rec FILLER,
field3
)
may work but I am not sure that this will work in 7.3 of Oracle as I think
FILLER came out in 8.0x
However an easy way ( I was going to say the easiest but I figured someone
would send in a 1 character script to achieve the same thing)
is to use awk to concatanate the fields as in the following example
cat j.txt
a ccc
cat j.txt | awk '{print $1,$2 $3,$4 }'
a ccc
I think that gives you what you want and you just use your loader routine
to load the amended data
HTH
John
-Original Message-
Sent: 10 November 2002 07:33
To: Multiple recipients of list ORACLE-L
Hi list,
LOAD DATA
INFILE 'c:\temp\file2.csv'
APPEND
INTO TABLE dc_temp1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
(
name, old_po_box, dn, gsm, nregc,
cr_no, new_po_box , remark, ncli)
The above is my control file for loading data.
i need to refer the two columns of data from the datafile
'c:\temp\file2.csv'
should go into one column(remark) of my table.
Oracle7.3.1. Any ideas pls.
Thanks.
Nirmal.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nirmal Kumar Muthu Kumaran
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Data Purging Strategy
In response to a post on data purging Tim Gorman wrote some on SAN-based disk, some on NAS-based storage. Can someone please explain the differences between these technologies please. My understanding that a SAN is a group of disks which are available on a network and are not 'owned' by a server and have no direct cables into a server. I also understood NAS to be network based disk (duh!) Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Purging Strategy
Hey Dennis, Mark Leith is the only person on this list allowed to mention 3rd party products. I am sure he bought the franchise from Jared :) John -Original Message- Sent: 06 November 2002 14:15 To: Multiple recipients of list ORACLE-L Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition: - If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database. - PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L Prem, I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing. Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution. Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the get the data from tape and reload it game with all of it's problems (writing an offload program, table structure changes offload program versions). Try and keep this as simple as possible. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L Dear List, I need some inputs from you all regarding purging data from the database. This is the requirement We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data. This is the strategy we have designed for this. When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database. In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables. Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Exams - What to study out of?
Just bought Oracle Press 9i New Features by the illustrious Robert Freeman. Seems good and I have also seen another Oracle Press Book 9i New Features Exam Guide by Daniel Benjamin. This follows the Corioulus format of a chapter and questions and a big test at the end. The worrying thing about Robert's book is that in the introduction he states that due to lack of space he has had to miss certain features out. Unfortunately he does not even list the ones he has missed out. However these books should be used in conjunction with the Oracle documentation so should it should not be too much of an issue. John -Original Message- Sent: 06 November 2002 14:34 To: Multiple recipients of list ORACLE-L Unfortunately there are no Corioulus books for 9i. Ken - Original Message - To: Multiple mailto:ORACLE-L;fatcity.com recipients of list ORACLE-L Sent: Wednesday, November 06, 2002 8:08 AM But I did. All I did was study the Corioulus book - and took the exams. -Original Message- mailto:DWILLIAMS;LIFETOUCH.COM ] Sent: Tuesday, November 05, 2002 4:34 PM To: Multiple recipients of list ORACLE-L Ken - Wow, I'm impressed. I took and passed one APICS module years and years ago and I can testify that they aren't easy. And I took the module related to my daily job. Can you share any tips on how to study for the SQL exam? I find I learn MUCH more from someone that was forced to reassess their strategy than from people that say oh, I took the manual home two nights and breezed through it. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 2:04 PM To: Multiple recipients of list ORACLE-L I agree that the SQL exam is very difficult. I've taken it twice and missed passing by just a couple of points. But I will persist. Interestingly enough when I got my APICS CPIM certification I took six exams and passed them all on the first try. The passing grade is 90%. Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 05, 2002 11:03 AM Eva - Everything I've heard says that all the exam questions come out of the Oracle University class Student Guides. To put it bluntly, your goal is to pass the exam. You could study the Oracle manuals, but there is a lot of material to cover and what strikes you as important might not be covered on the exam, and vice-versa. Essentially you are getting two levels of filtering. Somebody read the manuals and used that to prepare student guides, then a group of Oracle instructors read the student guide and prepared questions. I think that most of the exam guides were prepared from the Oracle Student Guides and the author has at least taken the exam, so they have a general idea of how the test is approached. Everyone has their favorite exam preparation book and I consider this a worthwhile investment, I bought Couchman - http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL0 http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0 H http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6WIANMIL 0Hsourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=007 2133414 sourceid=00400731866779927243bfdate=11%2D05%2D2002+10%3A16%3A17isbn=00721 33414 Two issues: Which exam track are you planning to take? 9i? 8i? The advice I received is don't take the SQL exam as your first exam. Take the DBA exam first. The SQL exam is reported to be quite hard because it covers all the odd SQL functions and can really catch you unawares. As a practicing DBA you should find the DBA exam pretty easy. I didn't personally take that path for my own reasons, but I still consider it good advice. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 05, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by
RE: ORA-01089
Don't know the exact circumstances but the following note from Metalink (1014091.102) may be appropriate However the best way to learn anything is to ask the senior DBA what he meant. An explanation from him may include looking at the alert log and the scripts that were used. That is more than most people on this list can offer Good luck John Problem Description: You issue a shutdown immediate and receive the following error: ORA-01089: immediate shutdown in progress - no operations are permitted Cause: The SHUTDOWN IMMEDIATE command was used to shut down a running Oracle instance, terminating any active operations. Action: Wait for the instance to be restarted or contact the database administrator. You may be running a shutdown script that performs a shutdown abort, then a startup, followed by a shutdown immediate. The error is recorded in the alert log, and a trace file is generated by one or more snp processes. Solution Description: = The error is actually expected behavior based on the conditions present during the shutdown immediate. The following workarounds are available: Workaround 1: = Modify your shutdown script to issue a shutdown normal instead of a shutdown immediate. Issuing a shutdown normal allows the snp processes to complete their work prior to shutting down, thus avoiding the error. The implication of this workaround is that the database will not shut down until all user and snp processes have completed. Workaround 2: = Increase the JOB_QUEUE_INTERVAL parameter in the INIT.ORA file. *NOTE: Increase by increments and test to determine what interval is necessary to avoid the error. This increases the time before the snp processes wake up after starting up the database, allowing the shutdown immediate to be issued before the snp processes wake up. Workaround 3: = Start the database with a different INIT.ORA file. Modify the startup script to use a separate INIT.ORA file that does not include the JOB_QUEUE_PROCESSES or JOB_QUEUE_INTERVAL parameters. For Oracle 8i versions you can set the JOB_QUEUE_PROCESSES to 0, this will ensure that no snp processes startup. Since no snp processes will start during the startup, no error will occur during the shutdown immediate. Explanation: During the startup portion of the shutdown script, Oracle starts a number of snp processes based on the init.ora parameter JOB_QUEUE_PROCESSES. Depending on how long Oracle takes to process the shutdown immediate portion of the shutdown script and the value specified in the init.ora parameter JOB_QUEUE_INTERVAL, one or more of the snp processes may wake up to check the jobs queue. The smaller the value of JOB_QUEUE_INTERVAL, the sooner the snp processes will wake up. If these processes wake up between the time the database was started and the shutdown immediate was issued, the ORA-1089 error will occur. The snp process cannot continue because a shutdown is in process. References: === [BUG:775116] ORA-604, ORA-1089, AND ORA-7445 DURING SHUTDOWN IMMEDIATE -Original Message- Sent: 06 November 2002 14:59 To: Multiple recipients of list ORACLE-L Hello Gurus, This morning our database locked us out with the error code ORA-01089. Circumstances surrounding this occurrence were that a cold backup by Tivoli is taken performed every night at 4AM. When I spoke with our senior DBA, he confirmed that the database never shutdown properly before the backup and sure enough, I could see the processes this morning from yesterday. This is the second occurrence in 5 days. So my first question is, why would this happen? And secondly, how do I deal with it? Further to my conversation with the senior DBA, I was informed that indeed a shutdown abort command was issued but only after it was confirmed that everything was ok. What does he mean by that statement? What is he checking for before issuing the abort command? Thanks in advance and I would like to apologize if this question has been posed earlier at some point in time. I did search the archives but didn't find a satisfactory explanation. Saira Somani IT Support/Analyst Hospital Logistics Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OCP Exams - What to study out of?
Denham, I have just passed my 8i upgrade exam and added some notes to a page about getting OCP certification. It can be accessed on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm Hope you find it interesting John -Original Message- Sent: 05 November 2002 12:09 To: Multiple recipients of list ORACLE-L Hello Everyone, 1. I have now made the humungous decision to start studying and to write the OCP exams. 2. Do I study out of the Oracle Manuals? 3. I do have the Sybex Study Guides, would studying these be all that is needed? 4. Or do the questions come out of the Course material ( Which I have not attended ). 5. I do have some of the Oreilly Insect Books - would these be any help? Any views, opinions etc appreciated. Regards Denham Eva Oracle DBA UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity. Dennis Ritchie. _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-02050
Ray, I think you need to check DBA_2PC_PENDING on both the local and remote database to see if anything is still in there. That gives the osuser, terminal and host name so you may be able to get something from that. Of course if there is nothing in that table then the transaction has been rolled back already John -Original Message- Sent: 05 November 2002 13:43 To: Multiple recipients of list ORACLE-L ORA-02050: transaction 8.82.26033 rolled back, some remote DBs may be in-doubt The transaction seems to have completely rolled back. My question is, is there any way to relate transaction id 8.82.26033 to an application or table row or something at a higher layer? Thanks. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Stand-by database implementation ...
One of the best sources of information is a white paper by Lawrence To called Graceful Switchover and Switchback for Standby Databases (Metalink note 90817.1) Another one under the White papers sectiopn is note 91570.1 but I have not looked at that John -Original Message- Sent: 29 October 2002 15:24 To: Multiple recipients of list ORACLE-L Looks like I'm gonna implement a stand-by database on Windows 2000 using Oracle 8.1.7.4 Anyone have any good article, how-tos, caviots I need to consider or read up on? Lizz __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lizz Pena INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dropping multiple tables
No specific way of deleting selected multiple objects as far as I am aware.
You could drop a tablespace including contents if you wanted to delete all
objects in that tablespace or you can drop user cascade.
The best bet is to create some dynamic sql to just create a script
Something like the following will do it
select 'drop table '||table_name||';' from user_tables where table_name in
('TABLEA','TABLEB' etc);
or
select 'drop table '||table_name||';' from user_tables ;
and then spool the list to a file and delete all the lines you are not
interested in.
This is 8i and below , I don't know what version you are on and there might
be something new in 9i but somehow I doubt it as normal mode is either to
delete a selected table or delete all tables from a user or tablespace
John
-Original Message-
Sent: 22 October 2002 10:34
To: Multiple recipients of list ORACLE-L
What is the syntax to delete multiple objects, or more specifically multiple
tables from a user in 1 go.
I need to delete 50+ tables/objects without deleting them 1 by 1.
Thanks
Clint
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Please help, comment required urgently
Thanks for your contributions on this Tim (and on everything else you respond to). These little tips of how to analyze statspack reports properly all add up and whilst I did look at the report and I did glean some of you what suggested I certainly did not pick up all that you spotted John -Original Message- Sent: 18 October 2002 14:54 To: Multiple recipients of list ORACLE-L George, Two things jump out together: * The SQL statement with hash value = 3509998681 is consuming about 25% of the total response-time (i.e. total processing plus total wait) on the system. This SQL statement is executing 900 times during the one-hour sample period... * Waits on the cache buffers chains are consuming another 16% of total response-time With these two things consuming 41% of everything consumed by the database instance during this time period, there is no chance that anything else is more important... Chances are excellent that these two things are related. Since the SQL statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e. physical reads) to it's credit, this indicates a buffer-cache hit-ratio of over 99.7%, which is sure proof that something is seriously wrong! :-) My guess is that the query is using an inappropriate and/or inefficient index for a long, long, long range-scan operation, which is racking up all of those buffer gets. What do you expect from the rule-based optimizer? If you were running CBO and this happened, I'd suggest gathering column-level histogram statistics on the table. My guess also is that many concurrent users are running this statement during the course of the sample period, causing the latch contention for cache buffers in the Buffer Cache, thus the relationship between the two symptoms? I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48; don't know what CPU_COUNT is, but obviously this change has had zero impact on the latch contention problem. Tuning the SQL will fix the problem; accomodating the problem by configuring more latches has no impact. Tuning that one SQL statement (plus a few of it's look-alikes, also listed in the report) will resolve the major performance issues you are experiencing. In fact, it will have a miraculous impact... Hope this helps... -Tim - Original Message - [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:ORACLE-L;fatcity.com [EMAIL PROTECTED] Sent: Friday, October 18, 2002 2:53 AM Hi guys, I need a second opinion on the following Statspack output, I got my suspicions but my manager and the client is not buying what I am say, Not knowing anything of the system architecture please look at the output and say what would concern you. What assumptions/recommendations you would make. Thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web:http://www.didata.co.za http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Difference - Rebuild and Analyze index
I think you are correct Charlie,
In 8i statistics can be collected when you are creating or altering an
index. You must use compute statistics (estimate is not an option).
You can rebuild an index and compute statistics, however you cannot do it
using the online keyword
John
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: 16 October 2002 18:44
To: Multiple recipients of list ORACLE-L
At least on my DB's doing ALTER INDEX index_name REBUILD does in fact
populate the statistics.
I discovered this by accident on one RBO DB which started giving poor
performnce after doing some
REBUILDs (OPTOMIZER_MODE=CHOOSE).
ora ak
ora_magic@yahoo.To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
com cc:
Sent by: Subject: Re: Difference -
Rebuild and Analyze index
[EMAIL PROTECTED]
10/16/2002 08:49
AM
Please respond to
ORACLE-L
Yes Marul , I think there is a big differenece in them . When you are
rebulding the index means you are re-organzing the entries in the index ,
may be removing the entries for the rows deleted in past and so forth so
on. But this doesn't generate any statistics about index.
When you analyze index, you generate information ( statistics) about the
data in index , like leaf blocks or depth of index etc.
None of these should require orther , but they have some effect .
Like if you rebuild index , after that you dont have latest statistics and
queries may not perform upto mark .
-oramagic
Marul Mehta [EMAIL PROTECTED] wrote:
Hi,
Can anybody please tell me the difference between -
SQL execute DBMS_UTILITY.ANALYZE_SCHEMA('BLAH','COMPUTE',NULL,30,'FOR
ALL INDEXES');
and
SQL select 'ALTER INDEX ' || INDEX_NAME || ' REBUILD ONLINE;' from
USER_INDEXES
If I execute any one the above do i need to execute the other also?
After how many days/hour it should be executed.
TIA,
Marul.
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos, more
faith.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Certifications Exam Discount Code
For Oracle Partners the code OPP is supposed to give 35% discount. It worked for me with no questions asked John -Original Message- Sent: 16 October 2002 22:26 To: Multiple recipients of list ORACLE-L Hello List, Code OTN 20 gives 20% discount. Somebody posted a code for 30% discount for Oracle Certifications. Could you please repost it or is there any other better discount going on. Thanks Shaibal _ Surf the Web without missing calls! Get MSN Broadband. http://resourcecenter.msn.com/access/plans/freeactivation.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Multiple Listeners
There has been much discussion regarding single or multiple listeners and there seems to be a split between those who advocate one method over the other. I am currently working at a site with a large number of systems/databases. They are well organised and one of the standards that is in place is to have a listener per database with the same name as the database. The lowest port will be 1526 to avoid any issues with auto registration on port 1521. The whole concept works very well in my experience; the small overhead on the server is outweighed by the ability to stop a listener individually without affecting any other service. John
RE: [Q] Public and private rollback segment?
Private ones have to be specified in the init.ora file as well John -Original Message- Sent: 17 October 2002 17:10 To: Multiple recipients of list ORACLE-L Can't remember where I copied this from, but I believe it came from metalink. I don't have a version associated with it, so things might have changed in v9. HTH Barb Public vs. Private Rollback Segments A common misconception about `Private' rollback segments is that they are segments reserved for a particular use or a particular transaction. The only difference between Public and Private rollback segments is in relation to the Parallel Server Option. A public rollback segment can be acquired implicitly by any instance in a parallel server environment. A private rollback segment must be explicitly acquired by a particular instance using the rollback_segments parameter. If not using OPS, the difference between the two is insignificant. -- From: dist cash[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Thursday, October 17, 2002 9:18 AM To: Multiple recipients of list ORACLE-L Subject: [Q] Public and private rollback segment? We have ORACLE 8.1.7.4 running on NT. My questions are: 1. What difference between public rollback segment and private segement? 2. what is benefit on public segment than private segment? 3. does public segment only use on paraller server (RAC)? Thanks. _ Choose an Internet access plan right for you -- try MSN! http://resourcecenter.msn.com/access/plans/default.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dist cash INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Difference - Rebuild and Analyze index
Marul,
These are 2 different utilities with 2 different functions and end results
Analyze schema with compute looks at all the indexes in that schema , reads
every block and produces statistics which the cost based optimiser can use
to determine the best execution path
Rebuilding an index can be used for one of 2 purposes. Either to move an
index to another tablespace without dropping it first (or re-enabling the
index after the table itself has been moved) or to defragment the index.
Only you can now how your system is being used, whether a lot of
updates/inserts/deletes are taking place, what the growth rate is. From that
information you can determine when to rebuild an object.
However the analyze routine does need to be run at least once if you wish to
have Oracle select the best execution path. After that it needs to be run on
any objects (tables/indexes) where volume or content has changed
significantly from the last time the analyze was run.
John
-Original Message-
Sent: 16 October 2002 12:44
To: Multiple recipients of list ORACLE-L
Hi,
Can anybody please tell me the difference between -
SQL execute DBMS_UTILITY.ANALYZE_SCHEMA('BLAH','COMPUTE',NULL,30,'FOR ALL
INDEXES');
and
SQL select 'ALTER INDEX ' || INDEX_NAME || ' REBUILD ONLINE;' from
USER_INDEXES
If I execute any one the above do i need to execute the other also?
After how many days/hour it should be executed.
TIA,
Marul.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Drop snapshot ORA-03113 - part 2
Have you tried execute sys.dbms_ijob.remove(xx); An undocumented provedure that I picked up on from this list a few weeks ago John -Original Message- Sent: 16 October 2002 17:04 To: Multiple recipients of list ORACLE-L DBA_JOBS shows that there is job associated with phantom refresh group. Can't remove job because user that owns it does not exist (remember fromuser/touser). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Barac INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Verrrry interesting article at MetaLink
Jared, You should feel upset as well as flattered. In my view you have a strong case against Oracle for some form of compensation, as a minimum you should be credited in the article. Whether you can win against a big corporation is another matter of course. I and many others can confirm that the document has been on your site for at least 3 - 4 years (although they state the article to be originally dated 1998). I would certainly be in contact with Oracle about this. Perhaps a good starting point would be your sales rep or contact at wherever you are working now. John -Original Message- Sent: 10 October 2002 08:13 To: Multiple recipients of list ORACLE-L Dear list, I found something rather interesting on MetaLink today. While doing a little research on UTL_FILE, I came across document # 1050919.6. This document deals with how to dump a table to an ascii file. http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=1050919.6 I began to think the code looked a little familiar. Then I saw the temporary file name of '_dtmp.sql', which was rather reminiscent of file names I use. Further perusal revealed that the comments were written by yours truly, and match word for word those I added to the dump.sql script years ago. See for yourself: http://www.cybcon.com/~jkstill/util/zips/dump.sql I don't know whether to be flattered or upset. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Loading a flat file into Oracle
Santosh. Putting a subject on your e-mails is useful to everyone I suggest you look at sqlloader. There are some good pages on the FAQ associated with this site http://www.orafaq.com http://www.orafaq.com HTH John -Original Message- Sent: 10 October 2002 11:59 To: Multiple recipients of list ORACLE-L Hello all, I have a client who will be doing the data capture and give me the data in a flat file. what i need to do in oracle is load from that flat file to my database. any scripts available ?? please send me across. ASAP Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Shutdown/Startup user
Sean, The role sysoper is already provided Details are There are two main administrative privileges in Oracle: SYSOPER and SYSDBA These are special privileges as they allow access to a database instance even when it is not running and so control of these privileges is totally outside of the database itself. SYSOPER privilege allows operations such as: Instance startup, mount database open ; Instance shutdown, dismount database close ; Alter database BACKUP, ARCHIVE LOG, and RECOVER. This privilege allows the user to perform basic operational tasks without the ability to look at user data. John -Original Message- Sent: 10 October 2002 13:29 To: Multiple recipients of list ORACLE-L Does anyone know if it is possible to create a user who only has rights to Shutdown a database?. I'm thinking about scenario that if the password for this user were ever compromised, e.g. seen in a script then worst case scenario would be database might be shutdown. Indulge me on this please as I acknowledge that a shutdown could be source of major revenue loss. This is been driven by requirement to have a script to cleanly shutdown a database potentially outside hours when DBA is not on site. If anyone has any other practical suggestions for management of this requirement I'd appreciate hearing them too. Oracle 7.3.3, 8.0.5, 8.1.7 NT4, W2K - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How much memory is an oracle shadow process using
Thanks for your script. The whole site is an excellent resource. Thanks Tim, I have run your script and also run a query against statistic 15 and 20 from v$sessstat (max UGA and PGA memory used ) SQL SQL select name,statistic#,sum(value/1024/1024) Curr Mb 2 from v$sesstat a, v$database c 3 where statistic# in (16,21) 4 group by name,statistic# 5 / NAME STATISTIC#Curr Mb - -- -- SID16 2.8621788 SID 21 23.4703255 Running the oramem.sh script I return the following Total RAM = 16384Mb, Swap = 19779.85Mb used, 2686.51Mb free Total memory consumption by Oracle instance SID: # Procs # Procs Max Sum ForegrndBackgrndShm Kb Priv Kb Total Kb == === 27 16 424600 106144 530744 So oracle shows 26Mb used where using a pmap command returns about 105Mb. I think I am comparing like with like here but obviously the results don't show that Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How much memory is an oracle shadow process using
Thanks for the help Igor but I have managed to work that bit out for myself. I did think that the posting was reasonably comprehensive and I also mentioned what the 2 statistics were used for. Perhaps the only thing I did not mention was that these specific queries are running against an 8.1.7.3 database but I am really looking for a generic answer anyway John -Original Message- Sent: 07 October 2002 15:09 To: Multiple recipients of list ORACLE-L Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Look them up in V$STATNAME. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 07, 2002 9:24 AM Thanks for your script. The whole site is an excellent resource. Thanks Tim, I have run your script and also run a query against statistic 15 and 20 from v$sessstat (max UGA and PGA memory used ) SQL SQL select name,statistic#,sum(value/1024/1024) Curr Mb 2 from v$sesstat a, v$database c 3 where statistic# in (16,21) 4 group by name,statistic# 5 / NAME STATISTIC#Curr Mb - -- -- SID16 2.8621788 SID 21 23.4703255 Running the oramem.sh script I return the following Total RAM = 16384Mb, Swap = 19779.85Mb used, 2686.51Mb free Total memory consumption by Oracle instance SID: # Procs # Procs Max Sum ForegrndBackgrndShm Kb Priv Kb Total Kb == === 27 16 424600 106144 530744 So oracle shows 26Mb used where using a pmap command returns about 105Mb. I think I am comparing like with like here but obviously the results don't show that Does anybody have any other insight as to how what exactly the values in statistic# 16 21 can be used to indicate overall memory usage by Oracle processes Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database is 32 or 64 Bit ?
I posted the note below a few weeks ago, hope it helps John Listers, Here is a little summary of commands to identify the bit version of an o/s and 2 methods of identifying whether a database is a 32 bit or 64 bit installation Operating System Compaq Tru 64 - will be 64 bit HP-UX /usr/sbin/swlist | grep -E '32|64' returns HPUXEng64RT B.11.00.01 English HP-UX 64-bit Runtime Environment if 64 bit Sun isalist -v If the return contains the phrase 'sparcv9' then it is a 64 bit o/s Oracle Version To check Oracle version - 2 methods do a file on $ORACLEHOME/bin/oracle returns either ELF-32 or ELF-64 executable Within sqlplus desc v$session and look for the definition of saddr (if raw(4) then 32 bit else if raw(8) 64 bit) -Original Message- Sent: 04 October 2002 07:53 To: Multiple recipients of list ORACLE-L Given a Database . It is 32 Bit or 64 Bit , how can it be found ? Assuming Cold Backup of Database Sent from Elsewhere -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How much memory is an oracle shadow process using
I know this has been discussed before and I have monitored the discussions but I am not sure that I have seen a clear summary and set of conclusions. I am trying to identify how much memory is used by the instance and all connections. I can show sga to give a total memory of the base instance /usr/sbin/pmap -x pid gives a rather verbose output and I struggle to work out exactly which of the lines is the one I am most interested in I have also tried ps -eo vsz,pid |grep 5225 where 5225 is the pid of an oracle connection and that returns a value that includes the SGA and also bigger than the return from the sql script below I run the following script to show me how much pga memory has been used for each process select name,sid,value/1024/1024 Curr Mb from v$sesstat a, v$database c where statistic# = 20 # shows current session PGA However I am convinced that that query is not accurate as it seems to return some very small values on systems that are quite busy. Also statistic# =21 which is the maximum for each process does not vary much from current which disturbs me a little as we have a lot of constant connections Has anybody got a easier/more accurate method of determing memory usage Thanks in anticipation John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: rman fun :), nightmare and long
The controlfile gets backed up automatically when you do a RMAN full backup. I have been having a debate this morning regarding a situation where we do weekly full backups using RMAN and and a daily RMAN archivelog all delete input. I contend we should do a archivelog all delete input INCLUDING controlfile. My colleague states that this is only of value for when all controlfiles are lost. (which we both agree is highly unlikely but possible). I am asured that if we had no controlfile available we could restore controlfile and it would go back to the copy it has which could be 1 week old and then roll forward (after calling restore database). RMAN would apply any changes necessary (of which there would be none in this scenario) and create an updated copy of the current controlfile) So Joe, you only needed a copy of the control file because of the scenario you were running and you would not need to take a specific copy in the normal run of events? Is my understanding correct?. I know that no recovery/DR scenario can be considered normal but I am particularly interested if any situation where we need to recover from the last backup either a full database to a SCN or point in time or recover a single datafile Thanks John -Original Message- Sent: 04 October 2002 12:58 To: Multiple recipients of list ORACLE-L Connor, my problem(fault) was I didnt make a copy of the control file(and in 8.1.7, you don't get it backed up by default like in 9i, right?). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Why do I receive the mails late?
I have raised this before and never seen a good explanation. It is very frustrating to see a reply to a post prior to the post appearing. What is even more frustrating is when you hit the send button and realise you have made a mistake or mistyped something. There is no way of withdrawing it and you still have 2 hours before you see it on the list John -Original Message- Sent: 03 October 2002 16:59 To: Multiple recipients of list ORACLE-L I receive the mails of this late after a long delay approx 1-2 hours. Is this common? Sometimes i get the reply first and then i get the question Regards Naveen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Remember me? Oracle DBA veteran considering getting certifi
Paula, Your experience sounds very similar to mine which I documented on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm . I used the Exam Cram series and was very happy with them. I am booked for the 8i upgrade next week but despite using 8i for however long it has been available I cannot believe how much there is to learn. I can see myself putting off the exam once again John -Original Message- Sent: 30 September 2002 04:48 To: Multiple recipients of list ORACLE-L Sorry I didn't respond sooner - been up to my neck recovering from a bad controller. Anyway - 8i. If Mike Ault wrote a cram book for 9i upgrade I would get that one too. Please don't tell me that 8i ceritfication is retired. -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Saturday, September 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Which version you are talking about? 8i or 9i upgrade certification Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 28 Sep 2002 08:53:19 -0800 Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Yes, 10Gb datafiles. I think we have a 35Gb datafile somewhere but I have not looked at that database myself John -Original Message- Sent: 26 September 2002 12:03 To: Multiple recipients of list ORACLE-L 10001Mb? the uniform extent sizes map to what I'll be using as well. good to know I'm not way off track --- [EMAIL PROTECTED] wrote: We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Tim,
I cannot speak for the 35Gb datafile which is a one-off but for the systems
where we have
datafiles files 4Gb we invariably use EDM to break a mirror and then
RMAN proxy against that.
The mirror is then kept off-line until we are ready to do the next backup.
At that point the mirror is 're-silvered' ('timesliced' is another
expression used) and then the process starts again.
The archived redo logs are kept on a seperate volume group and secured via
RMAN.
If a large datafile has to be recovered then that vg is re-silvered (which
is very fast) and then changes from the redo logs applied.
I agree that recovery of a large datafile will take longer if the required
file is older than the copy of the broken disk. I assume that this was
discussed and agreed with the business beforehand. If we had to recover to
a point in time greater than 1 day ago then I suggest we would be in
significant trouble anyway
John
-Original Message-
Sent: 26 September 2002 14:28
To: Multiple recipients of list ORACLE-L
Datafile sizing affects the speed of backup and restore, since each datafile
can only be backed up or restored by one process at a time. As a result, I
try to keep datafiles at uniform sizes of 2-4 Gb max. How do such large and
variable-sized datafiles impact your backups and restores? Just curious...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 6:28 AM
Yes, 10Gb datafiles.
I think we have a 35Gb datafile somewhere but I have not looked at that
database myself
John
-Original Message-
Sent: 26 September 2002 12:03
To: Multiple recipients of list ORACLE-L
10001Mb?
the uniform extent sizes map to what I'll be using as well. good to
know I'm not way off track
--- [EMAIL PROTECTED] wrote:
We tend to use multiples of 1Gb and add 1 Mb to the file so that we
get
2001, 10001 Mb etc
Solaris 2.8
LMT uniform extents range from 64K to 20Mb
John
-Original Message-
Sent: 25 September 2002 19:44
To: Multiple recipients of list ORACLE-L
We created two datafiles of 16GB+64K all LMT autoallocate ... never
gave a
problem. A basic testing concluded that fixed size allocation of 128M
caused
unnecessary delays whereas autoallocate was much faster. I don't know
the
full details yet, but I'll know soon.
Of course this is undergoing lot of testing (the whole application,
no
problems with datafiles yet), but we will probably settle for about
4GB+64K.
This is AIX5L 64 bit running Oracle 9iR2.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Hemant wrote Therefore, to reduce the contention for the hot blocks, I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ? How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ? I would guess the following Hemant, After changing the PCTFREE/PCTUSED values I do not think existing blocks will be changed until sufficient rows are deleted so that a block will be available on the freelist. If as you say there is only 1 row to a block then that would need to be deleted so that the block became available on the freelist and the new values would apply once a new row was inserted. Moving on from there it seems logical to me (that may be where I am going wrong!!) that an existing empty block will have it's values changed and will remain on the freelist but with revised parameters. I cannot say for certain without performing some tests but that is my gut feel HTH John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: patch backout
Managing patches with Oracle Applications is always a difficult task. The various sites I have worked on seem to have the same general policy 1)Do not apply a patch unless absolutely necessary. A lot depends on the type of patch, ie a simple fix is different from a big patch that can have all sorts of implications. 2)Try and apply a megapatch or patchset wherever possible. 3)Also test beforehand. Invariably a copy of the database at the same level is kept and a patchset will be applied and user testing will take place. Focuse especially on areas that have been customised. 4)Proper testing requires extensive user involvement. This is costly and has to be well planned. That is why applying a megapatch takes a bit of planning. It is also why it is easier to justify the cost and work involved if a number of issues are going to be addressed at the same time. 5) Minor, single issues patches can be applied (after testing of course) where the risk of failure is small. The best way is to ensure that you have a clean backup prior to application of the patch and therefore a good point to recover to. HTH John -Original Message- Sent: 23 September 2002 22:53 To: Multiple recipients of list ORACLE-L Hi List , What strategy you guys adapt for rolling back a database patch ( I am talking about application patch not the oracle software patch ) . For example if some table updates or some stored procs are going in .. how you guys backout patch if something doesn't work after the patch . I was thinking of taking export before applying patch and keeping it but that will be time consuming ..considering data . Other strategy might be for each ddl there should be an undo ddl and for each dml there should be an undo dml . but that's complicates the life considering number of changes that might go in patch . Any other ideas ?? Bp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hkchital INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: excessive SMON on openvms
Ron, Is it normal for your system manger to suspend Oracle processes? John -Original Message- Sent: 23 September 2002 20:24 To: Multiple recipients of list ORACLE-L List, I have a new server that I installed Oracle 8.1.7.3 with partitions and LMT. Some of the tables are quite large( in excess of 10 GIG) and I was creating the indexes when the communication channel was lost. Of course the rollback occurred but it was calculated to take in excess of 8 hours to complete. This was determined by SELECT count(*) from dba_extents where segment_name = 'TEMP'; the answer was 104313. 5 minutes later the answer was 103849. I had thought that all would go as planned and went on vacatio--- for a week as planned. This is a new development server that I am trying to set up before creating the database for our production server. Later during the rollback I got the snapshot to old message. I'll live with it for now but the next day I received can't allocate bytes in shared memory error and SMON went to 100 % CPU and stayed that way for 4 days. The sysadmin suspended the SMON process while I was away. I returned today and shutdown the database with shutdown abort, shutdown immediate hung. I restarted the database and all appeared well. I have a script that sums values in dba_free_space by tablespace_name and that appeared to be hung( not responding). I selected * from dba_free_space and the tablespace_name ='TEMP' had thousands of extents. I decided to halt the database and STARTUP MOUNT and ALTER DATABASE DATAFILE '...' OFFLINE DROP the datafile containing the TEMP tablespace. No problem as the database is not in archivelog mode. Then I open the database and DROP the TABLESPACE TEMP INCLUDING CONTENTS. This should allow me to create a new TEMP datafile and tablespace. The DROP TABLESPACE TEMP has been running for 4 hours now. I do not have exclusive use of the CPU as this server functions as a company production server for other processes besides Oracle. My questions; About how long would you guess that the drop tablespace action should take to complete? How do I check the progress and can I stop the progress and pick up where it stopped ( the production people are nervous that it will not be done when they need the server) Does any one have an Oracle Database and other production functions on the same server using OpenVMS? Thanks, Ron ROR mô¿ôm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA work load
I assume it is when penalty payments come into play when SLA targets are not met. Therefore payments for provision and support of an Oracle database are rebated John -Original Message- Sent: 24 September 2002 14:03 To: Multiple recipients of list ORACLE-L What is a rebatable SLA? -Original Message- Sent: Tuesday, September 24, 2002 1:38 AM To: Multiple recipients of list ORACLE-L I'm in a 24x7 shop where I am the only DBA ... and I have lasted over 2 years! I look after about 12 Oracle production databases - all of which have a 98% rebatable SLA attached to them. I also have 6 SQL Server databases with the same rebatable SLA. Thankfully, our environment is stable (knock on wood). Whenever we run into a huge problem and there is too much work going on I have the option of getting a loan DBA from another part of the company. This has happened about 3 times - two times I was on holiday. -Original Message- Sent: Tuesday, 24 September 2002 2:13 PM To: Multiple recipients of list ORACLE-L As metrics, Gb per DBA or databases per DBA are quite irrelevant. A single DBA, well-rested, experienced, and with proper planning and support, can manage hundreds of databases and dozens of Tb of data. On the other hand, some database production environments are so chaotic as to consume several DBAs and reduce them all to tears of exhaustion and frustration... The question needs to be viewed from a more mundane perspective. Take the number hours in a week. There are 168 of them, the world over. If the business has the expectation of 24x7 coverage, then at least four people are needed, each working approximately 40 hours per week. Period. Two FTE (full-time equivalent) can expect to cover normal weekday hours (i.e. 7am-7pm weekdays), one FTE to cover week-day off-hours, and one more FTE to cover weekend off-hours, vacation backfill, training backfill, and sick-time backfill. Let's not forget maternity and paternity leave backfill. I am not saying that this will be the division of labor, but if you figure that it will be likely that there will be meetings to attend as well as work to perform during normal working hours on the weekdays, then it will likely work out to something like this... Of course, I expect to hear from people who are single-handedly managing a 24x7 shop. Many people are forced through that wringer for a time... .there is another prolific member of this list to whom I related this formula, six years ago. He was the sole Oracle DBA in a 24x7 shop, supporting a fast-growing company that is now the market leader in its industry. I related this rule of thumb: four systems/database administrators in a 24x7 shop is sustainable over time. Three systems/database administrators in a 24x7 shop is sustainable for a short period of time, but ultimately leads to burnout and turnover. Two systems/database administrators in a 24x7 environment is totally unsustainable, as one of them (if not both) will always be in an active job search at any one time. And rightly so... He asked, What if there is only one DBA in a 24x7 shop?. I grinned, saying that they would not last more than a month or two. He replied that he was now entering his third month in just such an environment... .I think he lasted another 3 months or so, but ultimately with the inevitable result. A truly heroic performance, but somewhat reminiscent of Wile E Coyote trying to scramble back to the cliff's edge, having been lured into thin air by the Road Runner... --- Of course, if you don't have a 24x7 environment enforced by service-level agreements, then your mileage may vary. Obviously, there are environments that get by quite well on 1, 2, or 3 DBAs, but I am certain that they are not truly 24x7 nor is instability in those environments... But the point is that the job of database administrator is like any other critical support role. Only the medical profession is so criminally idiotic as to expect and demand 30- and 40-hour shifts from its most valuable personnel... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 7:43 PM I'm trying to justify hiring another DBA, and management wants more justification. I have put together the usual reasons, but they want Industry Standards, like how many Databases can one DBA manage. Or how many GB/DBA or endusers/DBA? Does anyone keep these kind of stats? thanks __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: tony ynot INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an ALTER TABLE tablename PCTFREE 99 PCTUSED1, does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data file size growing causing high disk space.
David, Have you got autoextend on the datafiles?. Are you sure the datafiles are growing and it is not that somebody has placed an export dmp file there (just guessing that from the name of the filesystem!!) I am not sure what you when when you say you have datafiles saved there?. Are they the current datafiles or old copies. If they are old copies then you could compress them or archive them off to tape or move them elsewhere. HTH John -Original Message- Sent: 23 September 2002 17:24 To: Multiple recipients of list ORACLE-L I have data files save under /export/home/oradata directory and this directory shows 100% full disk space because of the growth of these files. What should I do? Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
oraperf report - 2 queries
I have just tried using www.oraperf.com again after about a year since the last time. The output both in terms of formatting, readability and comment are much improved on what was already a valuable resource Well done Anjo 2 questions that the list (or even Anjo) may be able to answer for me With regard to processes the report states Another option is to decrease the init.ora parameter processes. The LGWR needs to scan all processes to find each process that is waiting for the commit to be written. The current value for processes is 1300. Try setting it close to the number of process that you really need. What I do not understand is that processes is a maximum number so LGWR may have to read all processes that exist but that number is well below the 1300 level but I cannot see an overhead in having the processes set to high in that respect. I do agree that other values are calculated based upon the value of the processes setting and so that is a good reason to reduce the value to a more realistic one of about 500. As a side note Steve Adaams states that there is no negative impact of having too high a processes value (In 8 not necessarily 8i) (http://www.ixora.com.au/q+a/params.htm The second point is with regard to recommending the use of a smaller log buffer and a smaller log_io_size (recommended to be 3 times avg redo size per commit). I was wondering if anyone knows where the current value is recorded. It is not available from V$parameter and I assume it is in one of the X$ views. My log buffer is 4M and the redo log block size is 512 but I am stumped as to the value of _log_io_size ( I think it should be 8192 ie 4M/512) Thanks John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: runInstaller - Version to use
The installer in 8.1.7.0 goes into the oui (Oracle Installer directory) If I go to $ORACLE_BASE (which contains directories for my various installations 8.0.6, 8.1.7.4 etc) there is a directory called OraInventory that contains a file called oui.loc. Cat that file and a variable INSTLOC tells where the installer is installed (assuming that it was selected when the 8.1.7.0 install was run). In that directory is another directory called install and the runInstaller binary is in there. It needs to be run from a Xterm though I suspect using the 8.1.7.3 installer would be OK if you cannot find the 8.1.7.0 disks (assuming that none of the above works and the installer was never installed). John -Original Message- Sent: 26 August 2002 20:08 To: Multiple recipients of list ORACLE-L I am in the process of applying 8.1.74 patch set to my systems, but I do not have a copy of runInstaller installed under ORACLE_HOME/bin. According to the Docs, if it is not there I need the one from the base disk (8.1.7.0.0), now the only Set of disks I can find is 8.1.7 release 3, which I am assume it 8.1.7.3. These disks have not been opened, which indicates to me that they are not the set that was used for the current install. Can I use the runInstaller from 8.1.7.3 or do I need the one from 8.1.7.0.0 Thank You Darren -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Moving to UK - what are Oracle contracts like?
Grant, I agree with Lewis, things are tight here. Very hard to find work and you have to compete hard on rate to have a chance. You have to work as a ltd company - does not cost much to set up , or use an umbrella company. I would recommend joing the Professional Contractors Group (http://www.pcgroup.org.uk/) if you are considering contracting. The insurance is worthwhile but the discussion forums (general, ir35 matters, technical, commercial, accounting) etc can be very useful. Conversely I am thinking about Australia (Sydney) long-term - why are you leaving there and coming to sunny England? John -Original Message- Sent: 16 August 2002 09:59 To: Multiple recipients of list ORACLE-L Grant, Well good luck. Things are still tight here in the UK (see a recent post about FTV's) and there are many contractors on the bench. A recent group forum topic touched on the fact that 35% of new signers-on (the dole) are from IT. Last year I had 2 stints of 6 weeks between contracts and consider myself fortunate that it wasn't longer. It was great spending 3 months with the family but it sure did eat into the savings. Right now, there is more work OUTSIDE of London in areas such as the Midlands, Birmingham and York as well as quite a bit on mainland Europe. If you plan to come to London looking for a contract prepare yourself. Lewis Bishop --- Barclays Enable/ISS/OPTS - Oracle OCP Database Consultant Phone - 020 8298 3418 Mobile - 07950 380857 Email - [EMAIL PROTECTED] -Original Message- Sent: 16 August 2002 08:48 To: Multiple recipients of list ORACLE-L This header confirms that this email message has been swept for the presence of computer viruses. Corporate IT THE WOOLWICH -- Very sorry about OT post - please feel free to flame/respond to me personally. I'm moving to the UK, and wonder what structure most contractors are employed under. I've read about IR35 (pretty much the same as the alienation of income rule here is Aus). Do people work as sole traders, set up their own plc, work for a body shop, or some combination of the above? Apologies again for the bandwidth consumption. Ciao Fuzzy :-) -- Woo Hoo! - H. Simpson -- The contents of this post are my opinions only If swallowed seek medical advice (Apologies for the excess signature) This email message (and attachments) may contain information confidential to TOWER Software. If you are not the intended recipient you cannot use, distribute or copy the message or message attachments. If you are not the intended recipient, please notify the sender by return email immediately and delete all copies of the message and attachments. Opinions, conclusions and other information in this message and attachments that do not relate to the official business of TOWER Software, are not given or endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information, attachments and opinions contained in this message are those of its author only and do not necessarily represent those of The Woolwich and or any other members of the Barclays Group and are intended solely for the use of the individual or entity to whom they are addressed. The sender may not be authorised to give financial advice, and nothing in this message should be construed as offering such advice. The message may contain privileged and confidential information and you may not copy, distribute or take any action in reliance on it. If you have received this email in error please notify the Information Security Manager at [EMAIL PROTECTED] Replies to this email may be monitored for operational or business reasons. Woolwich plc. Registered in England Number : 3295699. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bishop Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing:
How to tell if an o/s and oracle installation is 32 or 64 bit
Listers, Here is a little summary of commands to identify the bit version of an o/s and 2 methods of identifying whether a database is a 32 bit or 64 bit installation Operating System Compaq Tru 64 - will be 64 bit HP-UX /usr/sbin/swlist | grep -E '32|64' returns HPUXEng64RT B.11.00.01 English HP-UX 64-bit Runtime Environment if 64 bit Sun isalist -v If the return contains the phrase 'sparcv9' then it is a 64 bit o/s Oracle Version To check Oracle version - 2 methods do a file on $ORACLEHOME/bin/oracle returns either ELF-32 or ELF-64 executable Within sqlplus desc v$session and look for the definition of saddr (if raw(4) then 32 bit else if raw(8) 64 bit) John Hallas Office: ( 016356 ) 77929 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Bitmap Header in a Uniform LMT?
I have been looking at LMT recently I created a 10001M datafile at 8K block size for a 64K locally managed extents Noticed from dba_free_space that the largest contiguous free space was 3968M Dumped the file header and got the same results as Paul / Jeremiah but I did note that for a 8k block size there is a file header block, and then a space header block followed by 6 lmt bitmap blocks before the data blocks start I assume the figure of 3968M is the maximum no of bits that can be stored in single block and therefore the datafile for the sizes listed above could be a maximum of 3968M * 6? Am I on the correct track? John -Original Message- Sent: 09 August 2002 21:54 To: Multiple recipients of list ORACLE-L Raj - My apologies for being late with a reply. Your latest message prompted me to recall that Jeremiah Wilton investigated this (or a similar aspect) back in April. I went into Google and typed LMT bitmap headers (without the quotes), and it retrieved the discussion. I have inserted it below for your review in case you were not aware of that discussion. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g38Lpc327439 for [EMAIL PROTECTED]; Mon, 8 Apr 2002 17:51:38 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA08313; Mon, 8 Apr 2002 14:58:27 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0043EF61; Mon, 08 Apr 2002 13:03:20 -0800 Message-ID: [EMAIL PROTECTED] Date: Mon, 08 Apr 2002 13:03:20 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Following on from my previous note: Jeremiah, From your bitmap control, You have FF occurring 3 times followed by 3F which is 255, 255, 255, 63 which is 0011 So, least signficant bit first, 1100 which is used, used, ... (30 times) , free, free This corresponds with the first: 30 (the bit before the first free bit) Paul -Original Message- Sent: Monday, April 08, 2002 3:23 PM To: '[EMAIL PROTECTED]' From the 'Data Management and Storage Internal notes, Bitmapped Tablespace File Structure A new bitmapped tablespace file has the following structure: File Header 1 block Bitmapped File Space Header 1 block Head portion of of Bitmap BlocksN blocks Useful file blocks U units (A unit is a number of blocks) Tail portion of Bitmap Blocks M blocks If a Unit = B blocks, then the total file size = 1 + 1 + N + U*B + M Bitmapped File Space Header .. (lots to type, I can if you really need it) Bitmap blocks have 2 parts : Bitmap control structure Vector Dump The fields in the bitmap control structure are: RelFNo: Relative file number to which the bitmap belongs BeginBlock: Which block number does the first bit represent Flag: Zero for permanent files, one for temp files First: Where to start looking for the free space (bit before first free bit) Free: Number of free slots (bits) in the bitmap (not the file) To read the bitmap, take each two-byte pair, least significant bit first. If there are not eight bits, pad to eight bits with zeroes. Hence 0x0F = 15 = . When written least significant bit first, the bitmap looks like this -- used, used, used, used, free, free, free, free Scanning for the first free extent will start at the 4th bit. HTH Paul -Original Message- Sent: Monday, April 08, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Out of curiosity I decided I wanted to look at what composed the extent map in locally-managed tablespaces. I dumped the first 5 blocks of the tablespace's first datafile with 'alter system dump datafile ...' The results surprised me, as they appeared to consist of almost no data. The LMT in question contains a variety of segments and extents. How is the LMT bitmap organized? Start dump data blocks tsn: 1 file#: 2 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command Start dump data blocks tsn: 1 file#: 2 minblk 2 maxblk 2 frmt: 0x02 chkval: 0x type: 0x1d=KTFB Bitmapped File Space Header File Space Header Block: Header Control: RelFno: 2, Unit: 8192, Size: 524352, Flag: 1 Initial Area: 3, Tail: 524292,
RE: RMAN: How to restore backup to a different box
Helmut, Check out the newname command that can be used when duplicating a database set newname for datafile 1 TO '$ORACLE_HOME/dbs/newdb_data_01.f'; set newname for datafile 2 TO '$ORACLE_HOME/dbs/newdb_data_02.f'; set newname for datafile 3 TO '$ORACLE_HOME/dbs/newdb_data_11.f'; set newname for datafile 4 TO '$ORACLE_HOME/dbs/newdb_data_12.f'; set newname for datafile 5 TO '$ORACLE_HOME/dbs/newdb_data_21.f'; set newname for datafile 6 TO '$ORACLE_HOME/dbs/newdb_data_22.f'; HTH John -Original Message- Sent: 12 August 2002 17:24 To: Multiple recipients of list ORACLE-L Helmut - I am struggling with this myself. What is your goal? Are you just trying to create a test database, or trying to perform a disaster recovery test? For creating a test database, take a look at the RMAN DUPLICATE command. As to your specific question of changing the file path, the RMAN SWITCH command should be able to do what you need. Another idea is to have your Unix system administrator create the file paths you need so that you don't have to perform any changes in RMAN. For example, if you need a path /u01, create a file path or mount point with that name on the test system. One less thing to wrestle with in RMAN. I do not find that RMAN takes kindly to changes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 12, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Hi! I need to restore a RMAN backup (full db backup) of our production database to a test machine, which has a different file system layout. Production box: /u01, /u02, /u03, /u04, /u05 On the test box, all the data files need to be restored under /export (i.e. /export/u01, /export/u02 etc.) How do I achieve this using RMAN? This is 8.1.7 on Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance Problem after Migration
Scott, I don't understand I have tried to capture a session, but I need to get a repository up to look at the trace that was generated. No mention of Oracle versions or even O/S levels but I am sure you have Statspack available which should give you a good start. So have an overall view of the system then focus on a specific user, trace a session and check what events are being waited on. Has the EMC array been configured properly by experienced SAs or was this a first attempt by someone? John -Original Message- Sent: 01 August 2002 22:28 To: Multiple recipients of list ORACLE-L I have an interesting problem. I recently migrated a database from a Digital Unix system to a Sun Solaris system, with an EMC disk array. Since I was going to be migrating the database, I decided to double the block size from 4k to 8k. I also created the tablespaces on the new box as locally managed, with fixed extent sizes. Then I did a full database export, ftp'd the file, and imported it. It went well, or so I thought. The application works, but it is much slower than it was on the original (Digital) system. One side effect was that I didn't change the db_block_buffers, so that part of the SGA essentially doubled in size. The library cache hit rate was always around 99%, but the data cache hit rate used to only be about 85%, now it is 95 - 99%. All of the sorts are being done in memory, with memory to spare (52G yesterday, not used). According to the statistics, the database should be screaming. But the users are complaining that the online screens are taking much longer to come up. They say that the screens used to come up in 1 - 2 seconds, now it's taking about 10. Just for fun, I tried deleting the statistics and changing the optimizer_mode from choose to rule. That made things worse, which was what I expected, but it was worth a try. I have tried to capture a session, but I need to get a repository up to look at the trace that was generated. Until then, I'm pretty baffled. I'd appreciate any ideas that anyone has on this. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Heterogenous Services
And is it needed?. I am sure I have set up odbc connections between Oracle 8i databases on Unix and Access/Excel and I have never heard of Heterogeneous Services before -Original Message- Sent: 31 July 2002 13:24 To: Multiple recipients of list ORACLE-L I did a bit of research on Heterogeneous Services, apparently it lets you create ODBC connections between Oracle on UNIX and Windows apps. Can this be true? Does it actually work? What is the performance like? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) . -Original Message- Sent: Wednesday, July 31, 2002 6:48 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.7.3 Tru64 5.1 MSAccess 2000 Preparing to be shot down in flames but I have been looking through some documentation and also some White Papers but for some reason I just don't get it. I need to see Access tables from an Oracle DB. The paper I am reading (from Metalink) states that I should be able to do this via Heterogeneous Services and ODBC agent but I cannot see how it all hangs together. Has anyone done this and if so could you point me in the right direction please. I don't need an idiots guide (or maybe I do) but a gentle nudge in the right direction should suffice. Regards Lee The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: There are even more plots to go around.
Well I would not like to get stuck in a corner with you at a party Eric I thought it was a good light-hearted read but I am very superficial (on the surface of course) John -Original Message- Sent: 31 July 2002 10:53 To: Multiple recipients of list ORACLE-L insipid, boring, devoid of substance On 30 Jul 2002 at 14:29, Gogala, Mladen wrote: Microsoft Bids to Acquire Catholic Church ... try this instead: http://www.transform.org/transform/dlc/rapids/rapids.html - http://www.amazon.com/exec/obidos/ASIN/0195111303 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TRANSLATE
Not very elegant but it works
create table test (a varchar2(30));
SQL insert into test values
2 ('comma'||chr(44)||'squote'||chr(34)||'dquote'||chr(39))
3 /
1 row created.
SQL select * from test;
A
comma,squotedquote'
select replace(replace(replace(a,chr(44),''),chr(34),''),chr(39),'') New
String from test
New String
commasquotedquote
HTH
John
-Original Message-
Sent: 31 July 2002 13:24
To: Multiple recipients of list ORACLE-L
Hi,
how can i use TRANSLATE to take out commas(,)single
quotes(') and double quotes() from a string ?
Cheers
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Imran Ashraf
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Faster Sqlloader Question
See Oracle SQ Loader - The definitive guide by Jonathan Gennick Amazon URL is http://www.amazon.com/exec/obidos/ASIN/1565929489/qid%3D1028034789/sr%3D11-1 /ref%3Dsr%5F11%5F1/104-2680480-7921519#product-details Also there is a good review (and examples) by a member of this list - Stephen Andert at http://oracle.oreilly.com/news/oraclesqlload_0401.html HTH John -Original Message- Sent: 30 July 2002 13:59 To: Multiple recipients of list ORACLE-L How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pfile/spfile Sync Problems
John,
I have been discussing this with a colleague and our question is - what are
you hoping to achieve?
If you are using a spfileSID.ora file then that will be used every time you
startup so in what circumstances would you use the pfile?
PS one suggestion re the problem creating the trigger is to create it as
user sys rather than another user
John
-Original Message-
Sent: 29 July 2002 21:48
To: Multiple recipients of list ORACLE-L
Hi all,
I'm in the process of migrating 8i to 9i over the next couple weeks. One of
my outstanding tasks it to find a way to keep the pfile/spfile synchronized
as
much as reasonable possible. I figured the easiest way would be to include
startup and shutdown triggers to regenerate them from each other and include
similar work in the backup. No, it's not perfect, but should be sufficient
to meet the immediate need, after all pfile will eventually go away...maybe.
Anyway, I wrote the following segment which works great at the command line:
1 DECLARE
2 spfile VARCHAR2(120);
3 pfileVARCHAR2(120);
4 cursor_handleINTEGER;
5 return_cdINTEGER;
6 BEGIN
7SELECT rtrim(a.value,'bdump')||'pfile/spfile'||b.value||'.ora',
8 rtrim(a.value,'bdump')||'pfile/init'||b.value||'.ora'
9INTO spfile, pfile
10FROM v$parameter a, v$parameter b
11WHERE a.name = 'background_dump_dest' and b.name = 'db_name';
12cursor_handle:=DBMS_SQL.OPEN_CURSOR;
13DBMS_SQL.PARSE(cursor_handle,'CREATE SPFILE='''||spfile||''' FROM /
PFILE='''||pfile||,dbms_sql.native);
14return_cd:=DBMS_SQL.EXECUTE(cursor_handle);
15DBMS_SQL.CLOSE_CURSOR(cursor_handle);
16EXCEPTION
17WHEN OTHERS THEN
18 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
19* END;
When I turn it into a trigger:
CREATE OR REPLACE TRIGGER sys.sync_spfile AFTER STARTUP ON DATABASE
{everything else the same}
It compiles fine. However it doesn't execute on startup for some reason.
Any ideas?
TIA,
John P Weatherman
Database Administrator
Replacements Ltd.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Weatherman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ONLINE index creation in 8.1.7.2
Rich, There was a definite bug with online index creates in 8.1.7.1. I think it was fixed in 8.1.7.3 but I am sure it is OK in 8.1.7.4 John -Original Message- Sent: 29 July 2002 16:29 To: Multiple recipients of list ORACLE-L Hi all, Has anyone experienced problems using the ONLINE option to create an index on a production 8.1.7.2 DB (HP/UX 11.0, if that matters)? I need to create a largish (for us -- ~300 MB) index on an audit table, but I don't want to lock it while the index is created. I've heard of bugs using ONLINE with indexes and am being paranoid, as usual. TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Select Query - Help required
A similar question was asked a while ago and I kept the answer as I thought it might be useful The following PL/SQL snippet should demonstrate a way to do what you want set serveroutput on 100 Declare sn varchar2(2000); cursor c_devices is select name from ashoke; Begin for v_devices in c_devices loop exit when c_devices%notfound; sn := sn || ',' || v_devices.name; end loop; dbms_output.put_line(sn); End; / John -Original Message- Sent: 29 July 2002 15:08 To: Multiple recipients of list ORACLE-L Gurus, Please read the following problem and help me if you have any solution. Select product_id from tname where id = 2; Product_ID -- A B C D But I want the output as follows: Select product_id from tname where id = 2; Product ID - ABCD. Thanks in advance. regards, Karthik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: karthikeyan S INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: where should i start to learn to become a DBA?
Well you started off in the best position for a DBA Martin,
looking down on the rest of them :)
John
-Original Message-
Sent: 25 July 2002 14:04
To: Multiple recipients of list ORACLE-L
In my case, I was invited to a meeting attended by 6 people. The meeting
room had 5 chairs. When the new CIO asked who wants to be the DBA?, I was
the only one standing.
P.S. I really wanted to be the systems programmer.
From: shuan [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: where should i start to learn to become a DBA?
Date: Wed, 24 Jul 2002 22:53:19 -0800
Hi DBAs!
I'm working as a System Programmer for only one year.
And i'm interested to Oracle, wish to become DBA like you guys.
I did read some Oracle reference books, searching the web to find more
about Oracle,
and of course, here in this list, reading you guys' experiences for already
few months.
I've keep trying to learn the Oracle by myself(good or bad?).
When face problems, trying to play around myself here and there in Oracle,
RTFM, FAQs...anything that can find the similar problem.
To be honest, i don't think i learn much.
Maybe it's because i don't know the proper way to learn it.
Well...i don't read books page by page,
i only read those i'm curious at the beginning.
But i feel i'm getting confusing. I don't know whether i'm going in the
correct way.
It's so many times that i have to reinstall the Oracle.
('Cos i dun know how to make it running again, a lot of errors.)
So, can you guys give me some advices of
which section should i start as a beginner?
What's the next? and so on...and so on...
Or any links to point to?
mm...I'm playing with Oracle 8.1.6 in Win2000 on my PC.
Sometimes...play around with Oracle 8.0.5 on Linux(Which is corporate DB
Server, shh...)
Thanks for any advices.
Have a nice day!
_
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Martin Brown
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Rant
Referring to the last answer about getting a coffee I don't actually think that is a bad answer!! If he meant I will not rush in and do something rash, rather I will plan what I am going to do and check the book to make sure that I am correct then I think that is a perfectly acceptable approach. You can strike me from the list now after that rash comment John -Original Message- Sent: 22 July 2002 16:24 To: Multiple recipients of list ORACLE-L mkb, Your surprised? Over the last 6 years I've interviewed many a candidate while we added two DBA's to the group. I've gotten a lot of answers like this: Question: How do you create a table? Answer: The developer sends me a script. I run script. Question: How do you shutdown a database? Answer: Turn off the power to the computer. Question: How do you change the block size of a database. Answer: Change it in init.ora, restart database. Question: How do you add a datafile to a tablespace? Answer: You can't. Question: What are archived redo logs? Answer: There is no such thing. BTW: these folks had an OCP certificate. Best answer to a question I've asked: Question: You have a database crash at 6AM, what do you do. Answer: Get a cup of coffee first, then look in recovery manual. We hired the guy, he's still here 2 years later and just recently got his OCP. Dick Goulet Reply Separator Author: mkb [EMAIL PROTECTED] Date: 7/22/2002 6:58 AM Ok, I need to vent a little. Last week, I was asked to do some tech interviews over the phones for a mid level DBA position. Someone with about 2-3 years experience. I don't consider myself a real smart DBA, nor do I think that I ask particularly tough questions. The questions that I ask potential candidates are soley based on what is on the resume. So I figure if someone has, say, hot backups or SQL tuning on their resumes, I'd expect them to be able to hold a fairly intelligent conversation about these topics. No such luck! What really frustrated me, and what I really want to get out of my system, is that nobody that I talked to, had a real good concept of hot backups. Forget about recovery. I asked each and every candidate who claimed to have done hot backups, just give me a high level overview of how you do a hot backup. Don't care about syntax, just give me the mechanics. The answers I got were completely off base, baffling and frustrating. Some of these folks claimed to have 5 years experience!!! 'Well, we use scripts to do these, so I'm not sure how these are done...' (But it says on your resume you've done this???) 'Oh, I take the tablespace offline, and copy the datafile to tape...' (Unless I'm mistaken, that's not how a hot backup is done, right?) 'Well, I use the export utility, and as the backup starts, it is written to the dump file.' (Huh? What?) 'During this time, everything is written to the redo logs and not to the tablespace...' (You've been reading one of those books, haven't you?) I also asked them how they'd put a tablespace in backup mode. Simple enough, right? Not one of them got it right. Not even close. Didn't have clue as to what I was talking about. Fair enough, you don't know. Well how about a simple recovery scenario. I asked every candidate how they would do an online recover of a datafile while the database was still in use. No ideas. Not even close. I dunno, perhaps I'm spoilt by being a member of this list? Perhaps I expect every candidate to be as knowledgeable as you guys? Perhaps I'm asking too much? Rant over. Thanks for listening. mkb __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
RE: OFF TOPIC:Be careful from www.crescent.cc company
Surely in your case that is all hypothetical isn't it Lee :) -Original Message- Sent: 18 July 2002 14:49 To: Multiple recipients of list ORACLE-L Caveat Emptor my friend. I would have never have sent either money or educational qualifications (esp. originals) either domestically or abroad to anyone, no matter what. Once bitten twice shy is a good way to look at it from your point of view. -Original Message- Sent: 18 July 2002 14:13 To: Multiple recipients of list ORACLE-L Hello, Guys, Thought of making all of you aware with dirty game played by Crescent Cc (www.crescent.cc). I got trapped with this company's offer letter and lost money, lost job, got lots of frustration etc. .etc Sharing this with all of you just thinking that - you guys will think twice before taking job with such fraud companies. This company takes either Original degree certificate or some big amount $$ from candidates to bring them in US. This is OK, if they at least fulfill their promises. But, this company simply puts candidate on hold I lost money... my few friends also lost money, couple of them sent original degree certificate... and company is not returning either their certificate or money... I am not sure - whether such companies are really existing in US? Can any one of you guys check if you stay in same town where company is having office? You will see n number of people calling before you sending certificate/money to them. Once they get certificate/money, they don't care about candidates. Few of those names are - Siva, Vidya Sharma, Yolandé Vanzyl, Andrew Tadikonda, Sebastian, Mehnaz Ahmed etc... etc Their email addresses are - Vidya - [EMAIL PROTECTED] Andrew Tadikonda - [EMAIL PROTECTED] Mehnaz Ahmed - [EMAIL PROTECTED] Yolandé Vanzyl - [EMAIL PROTECTED] Sebastian - [EMAIL PROTECTED] Siva - Don't know email id I am sharing my personal experience with all of you. Now, decision is up to you.. Just tried accessing www.crescent.cc website and seems to be down. Oops, has company shut down? Thanks. Want to sell your car? advertise on Yahoo Autos Classifieds. It's Free!! visit http://in.autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?SK=20OracleDBA?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: download leads to sales call
Yes, that tend to do that with the less experienced, I think they call it hand holding :) -Original Message- Sent: 10 July 2002 01:08 To: Multiple recipients of list ORACLE-L oh yea they call me everytime i download something. joe Jonathan Gennick wrote: Last week I downloaded Oracle9i for Linux from Technet. Today I just got a call from an Oracle salesperson wanting to know what I was doing with it. I suppose it's not unreasonable for them to call and ask what I'm doing with their software, but is it normal procedure? I can't remember whether this happened last time I did a Technet download. Jonathan Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB_FILE_NAME_CONVERT issue
If you have a proper OFA setup then the problem is not to big.
The syntax is
db_file_name_convert=('/vobs/oracle/dbs','/fs2/oracle/stdby')
log_file_name_convert=('/vobs/oracle/dbs','/fs2/oracle/stdby')
where you are changing files that are in /vobs/oracle/dbs on prod to
/fs2/oracle/stdby/ on for the standby.
Oracle suggest that should cover most of your files and the other can be
renamed using alter database rename file syntax
HTH
John
-Original Message-
Sent: 08 July 2002 10:48
To: Multiple recipients of list ORACLE-L
Hi,
I am not using RMAN, This is what I try to do.
I try to create standby database manually
1. Copy all datafiles to new location (so call standby location)
2. create initstandby.ora (almost the same with the production)
2.a. DB_FILE_NAME_CONVERT = (production path, standby path) is one of them
2.b. let oracle know where is your initstandby.ora
3. alter database create standby controlfile as 'standbycontrol.ctl';
3. startup nomount pfile = where is your initstandby.ora
4. alter database mount standby database;
5. recover standby database; - this will apply daily archived log,
make sure your standby up to date.
6. shutdown; - never open this database
The problem is I don't know how to let Oracle know that my production
datafile paths are more than one.
I think Oracle 9i can do that with DB_FILE_NAME_CONVERT (para1, para2,
para3, para4).
I Don't think is very funny if I have to rename all my production data files
before I create standby control file. :(
Thanks
Sinardy
-Original Message-
Sent: 08 July 2002 16:03
To: Multiple recipients of list ORACLE-L
Hi,
I don't know the syntax you are looking for but you could explicitly rename
every file in your RMAN script (set newname for datafile command)
Jack
Sinardy Xing
SinardyXing@bkgcTo: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
omsvc.com cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: RE:
DB_FILE_NAME_CONVERT issue
[EMAIL PROTECTED]
08-07-2002 09:28
Please respond to
ORACLE-L
Hi guys,
Anyone that work in ASIA time please help me : )
Sinardy
-Original Message-
Sent: 08 July 2002 12:58
To: Multiple recipients of list ORACLE-L
Hi guys,
I try to create standby database and my datafile are all over my hard disk,
what should I do to my DB_FILE_NAME_CONVERT to let my standby database know
this issue ?
example
/prod_data1/system01.dbf
/prod_data2/user01.dbf
/prod_data3/rbs01.dbf
my standby datafile
/standby_data1/system01.dbf
/standby_data1/user01.dbf
/standby_data2/rbs01.dbf
I try this idiot way but there are no such parameters in v$parameter :+)
DB_FILE_NAME_CONVERT1 = (/prod_data1, /standby_data1)
DB_FILE_NAME_CONVERT2 = (/prod_data2, /standby_data1)
DB_FILE_NAME_CONVERT3 = (/prod_data3, /standby_data2)
Thanks so much.
Oh by the way I am using 8i
Sinardy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sinardy Xing
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sinardy Xing
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst
Young, niet toegestaan. Ernst Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een
RE: OCP discount
OPP gets you 35% discount (state member of OTN) John -Original Message- Sent: 05 July 2002 16:13 To: Multiple recipients of list ORACLE-L Anybody knows of a current magic code for a discount on OCP exams. I am particularly looking into the 8i DBA upgrade if this does matter. Thanks. Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: LogMiner errors
David,
I recall that there is a limit (42 characters?) on the name of the
dictionary file.
2 ways round it. Create the file in the top level ie /export/dict.ora or
create a symbolic link with a shorter name.
I have a page on my web site which mentions this problem
(www.hcresources.co.uk).
Unfortunately I do not (yet) have internet access at my new site and cannot
recall what exactly is on the page (I do recall it is not yet finished)
HTH
John
-Original Message-
Sent: 02 July 2002 19:19
To: Multiple recipients of list ORACLE-L
I tried to run LogMiner to view transactions on Oracle8.0 using LogMiner and
received following errors. Can someone please help me to fix errors. This
is HIGH priority that I am working to try to find out who messed up our
database per my Manager.
SQL begin
2 sys.dbms_logmnr.start_logmnr(
3 DictFileName = '/export/home/oracle/LogMiner/dictionary.ora',
4 StartTime = to_date('01-Jul-2002 08:00:00AM','DD-MON-
HH:MI:SSAM'),
5 EndTime = to_date('02-Jul-2002 12:00:00AM','DD-MON- HH:MI:SSAM'));
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01280: Fatal LogMiner Error.
ORA-06512: at SYS.DBMS_LOGMNR, line 42
ORA-06512: at line 2
Thanks,
David
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nguyen, David M
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Count rows in textfile
Try the following which will give the number of line in the file cat filename | wc -l HTH John -Original Message- Sent: 03 July 2002 09:43 To: Multiple recipients of list ORACLE-L Anyone whom has a good example on how to check how many rows (without opening the file) does a text file consists of? What is the command? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OPS and redos
Thank you Scott for supplying a well-written summary of SCN usage within OPS nodes. I was interested in If the gap in the log sequence gets to big Oracle will force the idle node to do log switches I was not aware of that and whilst I believe you, I cannot see what the reason behind it is. Have you any thoughts on why it is set like that I suppose it is quite easy to test if you happen to have a OPS development setup handy (which I do not at the moment) John -Original Message- Sent: 02 July 2002 19:44 To: Multiple recipients of list ORACLE-L Fawzia, What are you querying to get the SCN information. I would also be curious where you read about the recovery issues in OPS environment? Oracle maintains what is called a snapshot SCN and global SCN. The snapshot SCN is usually maintained in the SGA and the global SCN is maintained by the DLM. All transactions committed in an OPS environment use the global SCN and that SCN is always incrementing even in if both instances are idle. In an OPS environment it is possible for multiple transactions on different nodes to commit with the same SCN but each transaction will commit with an SCN that is greater whether these transactions are on the same node or different nodes. The bottom line is that Oracle basically treats SCN's with committed transactions the same whether it is a single instance or OPS. The snapshot SCN is used for queries and the snapshot SCN could be older than current or global SCN. This SCN only effects queries not DML. The only issue here is using this snapshot SCN could give read consistency on transactions that where committed on the on the other node. an example Time 10:00:00 - Node2 issues DML and commits updates deptno from 10 to 11 committed with current SCN 1000, current SCN is now incremented to 1001 Time 10:00:01 - Node1 issues a select on deptno and sees 10 (should see 11 because the transaction was commmited) because the snapshot SCN is 998 and the transaction was committed with newer SCN we must perform a read consistent read. PMON is the process that synchs current or global SCN with the snapshot SCN and PMON usually does this every 3 seconds so if you issue the query Time 10:00:05 (5 seconds after the commit) - Node1 issues the select on deptno and now sees 11. This is because PMON sync-ed up the snapshot SCN with the current or global SCN and now my SCN for my query is newer than the SCN the transaction you committed with so there is no need to perform a CR you can just use the current version. The above issue is really only a problem is a system if you have a lot of DML on 1 node and a lot of selects on the other node. It this is an issue you can set MAX_COMMIT_PROPAGATION_DELAY=0 and this will force the queries to always use the current or global SCN and now you should see the committed data as soon as it is committed because both the DML and queries are use the current or global SCN. I find odd that you have SCN of 500 and 2000. The way Oracle maintains SCN's in an OPS environment shouldn't allow for this large of a gap in SCN's. This looks more like log sequence numbers not SCN's. If these are indeed log sequence then this gap is not an issue. Recovery is based more on SCN's not log sequence numbers. If the gap in the log sequence gets to big Oracle will force the idle node to do log switches and if you are archiving you will see archive logs 1 OS block in size. When Oracle does this you should a KK #34;Redo log Kick#34; lock being allocated. Hope this helps, Scott --- #34;Malik, Fawzia#34; lt;[EMAIL PROTECTED]gt; wrote: gt; gt; gt; Hi gurus, gt; gt; I have a query..Basically we have an OPS set up here gt; (8.0.6) and I have gt; noticed that the scn on node A is 500 and the scn on gt; node B is 2000. I am gt; concerned about this w.r.t recovery- surely this gt; would be an issue ??I gt; logged a call with oracle and they said it wasnt an gt; issue, but then I read gt; in a document that it WAS an issue and could gt; potentially lose everything gt; between 500 abd 2000..and that the workaround would gt; be to add more logs to A gt; or to modify the check point interval gt; gt; Please can you advise?? gt; gt; Rgds gt; gt; Fawzia gt; gt; gt; ** gt; Information in this email is confidential and may be gt; privileged. gt; It is intended for the addressee only. If you have gt; received it in error, gt; please notify the sender immediately and delete it gt; from your system. gt; You should not otherwise copy it, retransmit it or gt; use or disclose its gt; contents to anyone. gt; Thank you for your co-operation. gt; ** gt; gt; -- gt; Please see the official ORACLE-L FAQ: gt; http://www.orafaq.com gt; -- gt; Author: Malik, Fawzia gt; INET: [EMAIL PROTECTED] gt; gt; Fat City Network Services-- (858) 538-5051 FAX: gt; (858) 538-5051 gt; San Diego,
RE: how to tell if OPS
I know it means either logging or or searching a init.ora file but how about select name,value from v$parameter where name = 'parallel_server'; I cannot see anything obvious from ps -ef John -Original Message- Sent: 03 July 2002 16:18 To: Multiple recipients of list ORACLE-L Is there any easy way to tell whether an instance is running under OPS or not? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_space_admin.tablespace_migrate_to_local
John,
Funnily enough I was just looking at this in the docs.
It appears you can specify a extent size but not whether auto allocate or
uniform. But the docs do state
Tablespaces migrated to locally managed format are user-managed which I
think gives you the answer.
To migrate a tablespace 'TS1' with minimum extent size 1m, use
execute dbms_space_admin.tablespace_migrate_to_local('TS1', 512, 2);
The example (above) itself is unclear as it states it is setting 1M extents
but the size is quoted as 512 (unless the db_block_size is 2K which I assume
is the case)
HTH
John
-Original Message-
Sent: 03 July 2002 12:43
To: Multiple recipients of list ORACLE-L
Can this be used to specify the allocation, autoallocate or uniform?
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Dunn
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RE: Difference Between DBMS/RDBMS
Didn't Larry call you direct to let you know Mike?. Very inconsiderate of him -Original Message- Sent: 26 June 2002 11:44 To: Multiple recipients of list ORACLE-L Live and learn =) I wasn't using VMS at the time but all the same, I'm surprised I haven't heard about it. Many thanks, Mike -Original Message- Sent: 26 June 2002 11:18 To: Multiple recipients of list ORACLE-L In fact, there was a 6.1. Lasted only less than a year. It was one of the very early Parallel Server versions available. Only ran on VMS, IIRC. At least, that's the only port of it I managed to install. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - No there was never a 6.1 or a 6.2 unless I missed a meeting. The latest version of 6 I worked on was 6.0.37. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Flush Shared Pool Area
Normally this is done when you are getting ORA-4030 (maybe ORA-4031 as well) errors. Basically the shared pool is fragmented and a object is trying to load but cannot find sufficient contiguous free space. Flushing the shared pool clears everything out and the package should load. Bear in mind that when flushing the shared pool any objects that are marked as kept will not be flushed out. It is normal to load often used packages into the shared pool just after startup so that these are kept and space is used efficiently. HTH John -Original Message- Sent: 25 June 2002 11:03 To: Multiple recipients of list ORACLE-L Hi All In which cases the alter system flush shared_pool is necessary ? Regards Kamel Benlatreche -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: GL2Z/ INF DBA BENLATRECHE INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*LOAD question
See post below from Stephen Andert (21/6/02) David, start shameless self-plug I wrote an article last year about tuning SQL*Loader. It is at http://oracle.oreilly.com/news/oraclesqlload_0401.html I experienced a huge improvement in performance and others have told me that they were able to achieve similar improvements. end shameless self-plug Stephen Also see the ORAFAQ mentioned at the bottom of each post on this list. That holds some very good info on sqlloader HTH John [EMAIL PROTECTED] 06/20/02 04:20PM I try to insert 14,000 rows into oracle database using SQLLDR and it takes too long to finish (around an hour). Is there a way to improve SQLLDR to make it run faster? For example, if modify parameter file will help? -Original Message- Sent: 25 June 2002 10:53 To: Multiple recipients of list ORACLE-L I have 150 000 lines to insert with sqlload and it takes 8 minutes, my feeling is this is too long. I tried in DIRECT mode, but I got some error, Direct mone does not work with pl/sql instriction like procedure or Sysdate value. I retrived any pl/sql instruction and any value like sysdate. I amiliorate the perf with 20 sec off. How can boost sqlloader? Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HP-UX 11.0/8.1.6.2.0/Optimizer
Ferenc, Looks like you got your way with the result. Depressing but true What happened to your web site, is it still up and if so what is the URL Regards John -Original Message- Sent: 25 June 2002 13:33 To: Multiple recipients of list ORACLE-L Mike Siebel does not support CBO either, and I have seen your exact problem. If you have the segment level degree of parallelism on any of the tables or indexes in the query with a non serial degree of parallelism, the optimizer immediately invokes CBO for the query, regardless of what optimizer_mode is set to, and of course in the absence of statistics, a query written for CBO will stink like nothing stinketh, especially on large data set. I tore my hair out for a day with such a query at a customer's site, and like you, all I could say is 'ba-a-a-a-a-a-a'. But I will never forget it. I'd love to chat more, but the game is about to start, and I want to see Germany hand justice to Korea, though it will be difficult because the Germans only have 11 players, and the Koreans up to now have had 14 on the field (team plus ref plus two lines men ). At least I have my priorities straight. Hope that helps you. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia Only Robinson Crusoe had all his work done by Friday -Original Message- Sent: Monday, 24 June 2002 4:34 PM To: Multiple recipients of list ORACLE-L Hi All: Here's a strange thing. I did a reorg of a very nasty tablespace over the weekend. I broke it out into 4 new tablespaces for the large tables and the rest into a single tablespace. This database has 'optimizer_mode = rule' set in the initSID.ora file because the Cognos application can't seem to handle the CBO, so I did not compute any statistics as part of the process. Sounds like routine maintenance, right? Nope. It went weird. One query, which included an outer join and a sub-query went from about 2 minutes to not finishing in over two hours. All indexes and objects were back in the DB. I verified that about a dozen times, all with manglement breathing down my neck. I EXPLAINED the query till I was blue in the face. I rebuilt (again!) all the indexes. No joy. Finally, I thought oh heck...might as well analyze them. Shazzam. Back to 2 minutes. Huh? But Optimizer-mode is RULE!! How? Why? I look stupid and so does my whole DBA group. Does anybody have any insights about this behavior? Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ferenc Mantfeld INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent : UTL file problem
I think a '/' is missing after vijay.
The documnentation states
"The file location and file name parameters are supplied to the FOPEN
function as separate strings, so that the file location can be checked
against the list of accessible directories as specified in the
initialization file. Together, the file location and name must represent a
legal filename on the system, and the directory must be accessible"
Mote however that if you define utl_file_dir=* this can override o/s level
file permissions and can allow users to amend or delete files that they
should not be allowed to normally. See the following notes
The parameter specification UTL_FILE_DIR = * has a special meaning. This
entry turns off directory access checking, and it makes any directory
accessible to the UTL_FILE functions.
Caution:
The '*' option should be used with great caution. Oracle does not recommend
that you use this option in production systems. Also, do not include '.'
(the current directory for UNIX) in the accessible directories list.
To ensure security on file systems that enable symbolic links, users must
not be allowed WRITE permission to directories accessible by PL/SQL file I/O
functions. The symbolic links and PL/SQL file I/O could be used to
circumvent normal operating system permission checking and allow users
read/write access to directories to which they would not otherwise have
access.
HTH
John
-Original Message-
Sent: 21 June 2002 12:58
To: Multiple recipients of list ORACLE-L
my prog looks like this (utl_file_dir=* is present in initSID.ora file):
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
dbms_output.put_line('1');
fileHandler := UTL_FILE.FOPEN('/users/vijay', 'myoutput.lst', 'W');
dbms_output.put_line('2');
UTL_FILE.PUTF(fileHandler, 'hai vijay \n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-2, 'ERROR: Invalid path for file.');
END;
and when i am running this script i am getting a message like this:
1
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 5
what can be the problem...
regards,
vijay
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vijaya Chander V.S
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ora 24314: service handle not initialized, when connecting as
Version of database?. Have you set ORACLE_HOME as well as ORACLE_SID? What command are you typing. Are you running from an authorised account that allows internal connection (within the dba group) John -Original Message- Sent: 21 June 2002 13:37 To: Multiple recipients of list ORACLE-L Internal Hello I´m trying to connect as internal and I get the error 24314:service handle not initialized. Before executing the sqlplus command, I have set the oracle_sid variable to my db one. Any idea? TIA -- Beatriz Martínez Jiménez Ingeniera Informática Tfno: 983 546646 [EMAIL PROTECTED] Fundación CIDAUT Centro de Investigación y desarrollo en Automoción Parque Tecnológico de Boecillo, p.209. 47151 Boecillo, Valladolid. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Beatriz =?iso-8859-1?Q?Mart=EDnez=20Jim=E9nez?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Data Types
Concepts manual VARCHAR2 and VARCHAR Datatypes BM_707The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes, not characters) between 1 and 4000 for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error. BM_3417For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50. VARCHAR Datatype The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, VARCHAR might store variable-length character strings compared with different comparison semantics. Therefore, to avoid possible changes in behavior you should always use the VARCHAR2 datatype to store variable-length character strings. NCHAR and NVARCHAR2 Datatype The NCHAR and NVARCHAR2 datatypes store NLS character data. The NCHAR datatype stores fixed-length character strings that correspond to a fixed-length or variable-length national character set. The NVARCHAR2 datatype stores variable-length character strings. When you create a table with an NCHAR or NVARCHAR2 column, you specify a maximum size that is either the number of characters (for a fixed-length national character set) or the number of bytes (for a variable-length national character set). * BM_4243The maximum length for an NCHAR column is 2000 bytes, or the number of characters that can be stored in 2000 bytes. * BM_4242The maximum length for an NVARCHAR2 column is 4000 bytes, or the number of characters that can be stored in 4000 bytes. *-Original Message- Sent: 21 June 2002 07:23 To: Multiple recipients of list ORACLE-L i wanted to know the exact difference between the following data types - NCHAR NVARCHAR2 VARCHAR2 VARCHAR Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance Tuning and Backup Recovery
Here are replies to a couple Sam, 1) If optimiser mode is chose and any table in a query has stats then CBO will be used. If stats do not exist on any table Oracle has a hard-coded default value which is 100 if I recall a note on here correctly. If stats do exist but they are very old then they will still be used possibly giving a poor path. The best bet is to analyze the tables. 2) 50 user running explain plan at the same time? Some system However the best bet is to select something which you can identify with the trace. Something like 'select this is sams session from dual;' Then do a grep on the udump directory searching for that phrase. 4) Put a trace on that particular session and get the user to run the application and then you can view the trace files afterwards. (see my notes on www.hcresources.co.uk re tracing sessions) Check that performance is bad all the time rather than just at a peak time etc HTH John -Original Message- Sent: 11 June 2002 14:24 To: Multiple recipients of list ORACLE-L Hi, I don't have much onhand experience, I thought list is the appropriate place to get information. These are related to Oracle Performance Tuning and Backup Recovery. I have a system of Oracle 8i under HP-Unix and with ARCHIVELOG on. It has been running and stable since last 2-3 years. This was a brief overview. Que 1: Assuming that I am using Cost based Optimizer (CBO); I have a query which is a join of three tables out of which only one table have been analyzed and in first case rest two were analyzed but long time back and after that I have changed the structure of the table and in second case the other two tables have not been analyzed. Will Oracle use CBO ? I know it will but then what will be the behavior of optimizer ? Will it take the best guess for the other two tables in both the cases and if yes then those best guesses will be depending upon on what ? Que 2: Suppose u have ran EXPLAIN PLAN for a particular query at sqlplus prompt and there are around 50 more users running EXPLAIN PLAN at the same time from the UNIX bos itself. In this case the OSUSER, TERMINAL, PROGRAM will be same (u know it better). Now suppose I want to run TKPROF against the trace file how will I identify which will be my trace file in USER_DUMP_DEST directory ? Que 3: Supposing if I have Oracle Report running on Oracle. I passed 3 parameters first time it took me around 3 seconds to execute and 4 parameters second time its hogging the system taking almost 25 minutes to run. There are no concatenated indexs on the underlying tables. What might be the cause and how would u go about identifying it ? Que 4: A user starts complaining about a particular part of an appln. What will be my first steps to optimize it ? Que 5: Let's say we have a situation where at point A I took a complete db backup. Somewhere after point B I lost an online redo log file. I recovered the db using the bck taken at point A and archived redo log files and started the new incarnation at point B. I took a complete logical backup at point B and taking incremental backup after that. Now again at point C I took the complete bck. If I have dropped a tablespace somewhere in between point B and C which are the possible ways to recover it and which one will be the fastest ? (Of course if possible). A---B---C---D If I am not clear on any of the part please let me know and I'll do the needful. Thanks __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: AW: off topic: anyone form Amsterdam, Holland / other maj
Funny being a non-American I understood the sentence to mean the opposite you did :) -Original Message- Sent: 11 June 2002 16:49 To: Multiple recipients of list ORACLE-L Stefan, I was in Holland some years ago, alright many a year ago (1983). I did not find the people there unfriendly and pushy. As a matter of fact I found them very friendly. A little hard to understand since English is a foreign language over there, but friendly and patient none the less. Dick Goulet Reply Separator Author: Stefan Jahnke [EMAIL PROTECTED] Date: 6/11/2002 4:38 AM Hi Andy I can only give you some information about the Duesseldorf area in Germany and the Zuerich area in Switzerland. D'dorf: As a combined sys admin / dba with 6 years experience, I guess you can expect about 6 Euros per year. If you want more, you have to be extraordinarily good. If you are not, you might have to accept less. I assume you're talking about a permanent position here. Cost of living is pretty high. You can expect to pay LOADS of taxes (IRS is really greedy here ;). Housing should be about 800 Euros for a 70 square meter apartment (but not downtown, that's exponentially more expensive unless you're really lucky). Food and clothing is also pretty expensive. My wife and I spend about 600 Euros per month on food. If you need a car, you'll have to pay a lot for insurance and tax (really varies, depending on the size of the engine and how likely it is to be stolen;). Public transportation is a good alternative. Zuerich: As for Switzerland, multiply the housing and food cost by 2 and there you are ... easy. Public transportation is perfect plus you pay less tax. You also can expect the salary here to be about 30% higher then in Germany, so that makes up for it. Be warned: If you're american, you might get a culture shock regarding unfriendly and pushy people. I guess Amsterdam is comparable (just guessing). Regards, Stefan -Urspr?ngliche Nachricht- Von: Andrey Bronfin [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 11. Juni 2002 12:13 An: Multiple recipients of list ORACLE-L Betreff: off topic: anyone form Amsterdam, Holland / other major european Hi ! Sorry for this off topic posting. I will probably be offered a Solaris sys admin / Oracle DBA position in Amsteram, the Netherlands. Unfortunately , i have no idea of what order of magnitude salary should i get / request. I have 6 years of experience in the field. So i wanted to ask the list memebers ( who are from Amsterdam or other major European cities , which is compatible to Amsterdam in terms of costs of living / salaries) a couple of questions: what would be a reasonable salary for a relocating person of my qualifications ? What is the living cost there , i.e. an appartment to rent , food , leisure, travel , taxes ,etc.. ? How does Amsterdam compares price-wise to other Dutch cities and to other European capitals ? Thanks a lot in advance to everybody who will respond either directly to me or to the list. Cheers. P.S : Please forgive me if this off topic question bothers you ;-) DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include
RE: Oracle 32 Bit running on Solaris 64 Bit
Title: Oracle 32 Bit running on Solaris 64 Bit As far as I am aware there is no advantagein running 32 s/w on a 64 bit o/s. In fact the reverse is true, there could be significant disadvantages. Any impact would be at a very low level and not noticeable from a user perspective (unless data became corrupted :) Check on Metalink or log a TAR John -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: 06 June 2002 15:28To: Multiple recipients of list ORACLE-LSubject: Oracle 32 Bit running on Solaris 64 Bit Hi there! We are running 32-Bit Oracle Software on Sun Solaris 8 (64-Bit). What is the advantage of doing this? Why don't we use 64-Bit Orlacle on 64-Bit Solaris? Nobody here can answer my question and the systems were set up by a consultant. So nobody really knows why this was done... Since we are talking about productions systems, upgrading Oracle Software is not an option... This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: How to move 200 GB db from prod to dev?
Title: How to move 200 GB db from prod to dev? A few options. Do you use RMAN for backups? , if so duplicate the database and rename the files as part of that process. Otherwise clone the database by copying the files at the o/s level and change the instance name. Several documents on metalink show how to do this. If you have used an OFA setup then renaming the datafiles should not be that difficult, it will only be the directory named after the sid that willl need changing On a database that size export would take some time but it is another option. John -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]Sent: 06 June 2002 15:28To: Multiple recipients of list ORACLE-LSubject: How to move 200 GB db from prod to dev? Hi! We are supposed to clone our production database onto a new development box (both boxes are Sun Solaris). The db is about 200 GB in size. What would be the best way to achieve this? Simply copying over the files won't work, since the instance names are different: Production: SID=PCLDB1 = e.g. /u02/oradata/PCLDB1/system01.dbf Development: SID=ROLAND = e.g. /u02/oradata/ROLAND/system01.dbf So would export/import the entire db be the only way? (But writing out dump file that big should be a little disk space problem...) Renaming all the datafiles (approx. 100) would be kind of annoying... Any ideas? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: Re: Textfile into oracle
Roland, If you are saying that you have 100 columns in the spreadsheet but you only want to copy 9 columns into a table with 9 fields then you have 2 choices. Reduce the spreadsheet to only 9 columns (by copying to another worksheet) and then saving as a CSV file and importing by sqlloader. I always think it best to limit the data at the source end before transfer rather than afterwards. The other option is to use sqlloader to only pull out the fields that you want. I have had a quick look at the online manuals but cannot see how to do that unless you have a fixed position file which I assume is not the case (the syntax is below just in case) LOAD DATA INFILE 'ulcase5.dat' BADFILE 'ulcase5.bad' DISCARDFILE 'ulcase5.dsc' 1) REPLACE 2)INTO TABLE emp (empno POSITION(1:4) INTEGER EXTERNAL, enamePOSITION(6:15)CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL) There is a good sqlloader web-site (mentioned on this list a couple of months ago). Unfortunately I have just started a new contract and do not have internet access as yet. Do a search on Google HTH John -Original Message- Sent: 06 June 2002 15:28 To: Multiple recipients of list ORACLE-L Could anyone please give me an example on how the sqlloader script would look like , How many fields would it be,? I cant find anything ofthis in themanual. I have 100 fields in the excel file but only 9-10 field names... Thanks in advance Roland Alexandre Gorbatchev [EMAIL PROTECTED]@fatcity.com den 2002-06-06 03:03 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Kopia: Hello Roland, The easiest way is to save it from excel as flat comma-separated text file. Then you should use Oracle SQL*Loader tool and yes you will have to pre-create the table with all fields you need. (99,9% that's what you need) You cannot import .xls file into Oracle database directly with standard oracle tools. I mean with structure, not into the lob filed. You could access file from PL/SQL and write your own import procedure. (not a trivial task and not feasible probably :-) Can use odbc from Excel directly. May be there are some third-party tools to import. Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 12:03 PM Hallo, I have this excelfile. Is it possible to import this into an oracle table. This file is supposed to be located on unix machine from the beginning and the import into an oracle table would be done from unix. How should I name the fields in the oracle table. I mean should I use the 9-10 different field names in the excelfile. There is at least 100 different columns in this excelfile. Is it possible to import a file like that into oracle table, Please help me with some hints. (See attached file: try.xls) Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Upgrade Question
of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public
RE: Networking problem
Title: Networking problem Just a couple of thoughts to try and narrow the problem down. 1) Have you tried a single client to the 8.1.7 database and then to the 9.2 database. If so then is it disconnecting from both or just the 9i one. That would be a test worth trying as you are then focusing on the database rather than a client problem if it only disconnected from the 9i system. From what you are saying I am guessing that is the likely cause anyhow. 2) Does the 9i system have any sort of profile set up for the accounts that you are connecting to that will disconnect on excess activity (cpu used etc ). I suppose that could be another test - try 2 connections to different accounts (say system and a standard user one) and see if they both fail or just the standard user again looking like a profile type setting HTH John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Steve Main Sent: 31 May 2002 23:14 To: Multiple recipients of list ORACLE-L Subject: Networking problem Hello list, I am having trouble with clients losing their connections to a 9.2 database. We have a Solaris 7 box running 8.1.7 and 9.2.0 and the clients that connect to this box will lose the connection to the 9.2.0 database after about 20 minutes. The connection to the 8.1.7 database has no problems. The client software is 9.2, 8.1.7 and 8.1.5 and they all behave the same way. The listener on the server is 9.2.0. Now here's the kicker I have another server set up the same way with a 9.2 database and I don't lose that connection. I have just started tracing in the hopes it will point to something but I thought I would throw this out to everyone for ideas Thanks Steve
RE: updated rows count
The SQL%ROWCOUNT and SQL%FOUND can be used to test for the status after an update operation. If SQL%FOUND is true then SQL%ROECOUNT will hold the number of rows updated. If the transaction has failed then %FOUND will be FALSE (and %NOTFOUND will be true) and %ROWCOUNT will be 0 HTH John -Original Message- Sent: 30 May 2002 14:49 To: Multiple recipients of list ORACLE-L Hi All! My client run big update 20,000 statements. I need to know how many rows was updated (it's could be any number). I thinking about triggers , but this table can be updated not only with this big update. Other users can update rows too. I need to know updated rows number just for this big update. Thanks. update assethdr set atopsys= 'MS Windows 95 4.00',atdept='256MB', atcapcty=850, atcapun=1, atglcode='19.99GB', atus1='05/20/2002' where ataltnbr='000'; update assethdr set atopsys= 'MS Windows 95 4.00',atdept='256MB', atcapcty= 850, atcapun=1, atglcode='19.99GB', atus1='05/17/2002' where ataltnbr=''; update assethdr set atopsys= 'MS Windows 2000 Pro 5.00',atdept='128MB', atcapcty= 400, atcapun=1, atglcode='6.43GB', atus1='05/20/2002' where ataltnbr='0296'; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question on V$ping table
Are you sure you are comparing the v$ping table from the same instance each time. Easy mistake to make (lol ) John -Original Message- Sent: 29 May 2002 20:34 To: Multiple recipients of list ORACLE-L This view is based on v$bh which is cumulative... So... YES! Regards, Michael Sale Author: Oracle9i for Windows(R) 2000 Tips Techniques http://www.amazon.com/exec/obidos/ASIN/0072194626 -Original Message- Bryan Sent: Wednesday, May 29, 2002 11:19 AM To: Multiple recipients of list ORACLE-L Hello everyone, I am trying to track down excess pinging in an OPS cluster and I was looking at the contents of the v$ping table. I looked at the contents last night before I left and saw numbers in a fairly high range, but when I did the same query this morning the numbers were much lower than the previous night.Can someone tell me if the contents of the v$ping table cumlative or are they as of the the time of the query? Thanks, Bryan Rodrigues -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael P Sale INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Shared Pool Tuneup
sys.dbms_shared_pool.keep('SYS.PSTUBT');
exit;
HTH,
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 21 May 2002 12:26:53 -0800
Hamid,
what happens , if an object is getting executed once in a while but takes
huge sharable memory, we may not be getting full use of pinning it in the
shared pool, except wasting the memory, So we need to consider the number of
executions also. if the number of executions are high for any object/SQL,
its good idea to keep it in shared pool.
--Madhu
-Original Message-
Sent: Tuesday, May 21, 2002 2:47 PM
To: Multiple recipients of list ORACLE-L
Hi List,
I have run some scripts for Tune up shared pool,here is the result of one
script which i run :
Script:
SELECT name,sharable_mem
FROM v$db_object_cache
WHERE sharable_mem 1
AND (TYPE = 'PACKAGE' OR TYPE = 'PACKAGE BODY' OR
TYPE = 'FUNCTION' OR
TYPE = 'PROCEDURE')
AND KEPT = 'NO'
ORDER BY 2 DESC
here is the result:
NAMESHARABLE_MEM
- --
DBMS_JAVA 56373
DBMS_STANDARD 24405
DBMS_UTILITY24212
DBMS_SPACE_ADMIN20832
DBMS_UTILITY20508
DBMS_JAVA 15189
DBMS_OUTPUT 13063
DBMS_APPLICATION_INFO 12461
DBMS_SHARED_POOL11148
DBMS_SHARED_POOL10648
Question is, do i have to pin all of these objects in my shared_pool or NOT?
Thanks
Hamid Alavi
Office 818 737-0526
Cell818 402-1987
=== Confidentiality Statement ===
The information contained in this message and any attachments is
intended only for the use of the individual or entity to which it is
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL
and exempt from disclosure under applicable law. If you have received
this message in error, you are prohibited from copying, distributing, or
using the information. Please contact the sender immediately by return
e-mail and delete the original message from your system.
= End Confidentiality Statement =
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hamid Alavi
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
MOHAMMAD RAFIQ
_
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Hallas
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Trigger or ????
Title: Trigger or Why not set a dbms_job up to recreate the sequence or even a cronjob which is quite easy to set to run at 00:01 John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Burton, Laura L. Sent: 14 May 2002 15:58 To: Multiple recipients of list ORACLE-L Subject: Trigger or I have a need to reset a sequence number at 00:01 everyday. I thought about creating a trigger to check the time, but thought that there might be a better way than checking the time every time a record is being added. I also thought about checking the max date on the table and comparing against the system date. When system date max then reset the sequence number. I like this logic better and thought of holes with using the time. The only problem I have is that this seems like a lot of overhead every time I add a record, which will be often. Is a trigger the only method available to me? Thanks, Laura
RE: Prod problem, please help!!! Any resolution?
Title: RE: Prod problem, please help!!! Any resolution? Glad you asked Hannah because I was wondering the same John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: 01 May 2002 13:18 To: Multiple recipients of list ORACLE-L Subject: RE: Prod problem, please help!!! Any resolution? Hi, Just wondering if you made any progress? Let us know. Hannah -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Prod problem, please help!!!
Tracy what do the Sys Admin staff say. How long since the last reboot of the server, has there been any kernel changes recently, any rogue processes? I always try and think away from Oracle initially and get other people involved as well. It is easy to assume it is just an Oracle issue (which it may well be of course) Good luck John -Original Message- [EMAIL PROTECTED] Sent: 30 April 2002 20:39 To: Multiple recipients of list ORACLE-L Tracy, You can start by using the query below to determine what the sessions are actually waiting on: select s.username username, e.event event, s.sid, e.p1text, e.p1, e.p2text, e.p2, e.wait_time, e.seconds_in_wait, e.state from v$session s, v$session_wait e where s.username is not null and s.sid = e.sid -- skip sqlnet idle session messages and e.event not like '%message%client' order by s.username, upper(e.event); Jared Tracy Rahmlow [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/30/2002 12:21 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Prod problem, please help!!! I have opened a TAR with Oracle and am waiting a resolution. In the meantime, I am wondering if anybody else has any ideas while I wait. Specifics: IBM AIX 4.3 Rdms 8.1.7.3 Database ~75g OLTP database with approximately 500 dedicated connections and 500 shared connections with Oracle's MTS. Problem: The database hangs, and no user is able to connect to the instance, except locally through srvmgrl. Even within svrmgrl, we are unable to select anything from the database without the query hanging. However, we can abort the instance (shutdown abort) and start it up again just fine. This has happened on 4-22, 4-29 4-30 in the early afternoon. Usually, this is also our peak busy rate for the week. We are executing MTS for 4 applications, all other applications connect through dedicated server. The alert log contains a message unable to start a shared server process. This week it was #41 and last week it was #25. Normally, we do not exceed 5 shared servers. Another thing I noticed is that there is no time allocated to any of the newly created shared servers. It is as if, it can not process any work through existing shared servers and decides to allocate another one, until finally it freezes. I am not sure if this is a MTS problem because I would suspect that I should be able to establish a dedicated server connection. And I can not. I think that this is just a symptom of the underlying problem. It would appear to me that we are running out of a resource, however our sysadms do not see any resource problems. Does anybody have any ideas how to debug this? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i new features, the saga continues?
Joe, That feature was really useful and interesting. I would be pleased to see you continue it John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack C. Applewhite Sent: 25 April 2002 16:33 To: Multiple recipients of list ORACLE-L Subject: RE: 9i new features, the saga continues? Yes! Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of JOE TESTA Sent: Thursday, April 25, 2002 8:14 AM To: Multiple recipients of list ORACLE-L Subject: 9i new features, the saga continues? Ok life has slowed down some and i've got some free time to potentially pickup the 9i new features saga i did about 6 months ago. Anyone still interested? The next on the list was 9i data guard. joe
RE: Get the Latest Date
Try the max function to pick the latest timestamp. I assume the column is of type date John -Original Message- Sent: 12 April 2002 11:43 To: Multiple recipients of list ORACLE-L Hi, I have read up quite a lot before posting this message so please bear with me if this question is trivial. I have table which stores session information of users. I have to develop a report which gives me the number of times users have logged in ( which is straightforward ) as well as their last access time. Since every user has multiple records in the table, I was trying to find a way to get me just one row per user which returns the latest date, rather than checking for the latest date in the client logic. Is there any function in Oracle which would return the latest date ? Thank You, Gavin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'Mello INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 1Z0-007 Exam
Ken, If this is the 9i upgrade exam then there was a discussion on this recently (about 3-4 weeks ago) Search the archives for a post by Mike Hateley and responses from Robert Freeman. If is not the 9i upgrade which exam is it? John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of KENNETH JANUSZ Sent: 12 April 2002 16:26 To: Multiple recipients of list ORACLE-L Subject: 1Z0-007 Exam Has anyone taken this exam? If so, I would like your feedback. Thanks, Ken Janusz, CPIM
RE: Developer access in test database
Suzy, You are much too generous. I always have found that asking for a change control form delays things by a few days!! John -Original Message- Sent: 12 April 2002 18:43 To: Multiple recipients of list ORACLE-L I generally give developers access only to the schema's they are developing for. In some cases, they might also get select_catalog_role. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL statement with hints or without hints
I worked at one consultancy/software house where you were not supposed to use hints under any circumstance. This was because they wanted to make the code truly portable (I believe SQL Server ignores Oracle hints as if they were comments anyway, correct me if I am wrong). I found this very frustrating and kept fixing code using hints to achieve good performance to prove that it was achievable. However I firmly believe that Oracle provided hints and therefore you should use them if they improve performance. I think it is harder to argue against them than it is to support their use. John -Original Message- Sent: 12 April 2002 16:05 To: Multiple recipients of list ORACLE-L Hi Raj, my two cents, fwiw: Interesting. I have worked places where hints were frowned upon (why is Oracle not doing it's job? Hey, you, DBA, you fix it. No, I don't like your hint. Fix it) However I feel the opposite way. I have also worked in very liberal environments (make it work! now!). I truly believe that we, as developers, know the data better than the optimizer. Don't get me wrong, not all sql statements require hints, but there are statements that will benefit from having them. Determining which is which is the trick, based upon application behavior. I also think that a hybrid environment requires more assistance via hints than pure oltp or dss. Hybrids provide their own unique type of challenge. omg did I just refer to myself as a developer... Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 10:03 AM To: Multiple recipients of list ORACLE-L Subject: SQL statement with hints or without hints Hi all, We are running a kind of hybrid mode, mainly RBO with some tables analyzed (mainly for intermedia). Last week we had an interesting situation, when tuning a huge SQL, we created an index and the query worked fine, but later in the day developers complained that their queries are running slower. We finally took out the new index and things were fine after that, but this brought out few important issues, 1. Developers do not put hints in their SQL statements. 2. They implicitly reply on a set of indexes that have worked for them. 3. Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower. 4. As an effect of 3, new queries that can't make use of these set indexes will always be slower. Some of these queries can't be rewritten to make use of the existing indexes due to business logic involved. My question is, do you, in your organization recommend putting hints in the SQLs all the time, some times or not at all ?? It doesn't really matter if you use RBO or CBO. When the developers questioned me, I told them 'Oracle optimizer is not an exact science, especially in a mixed RBO/CBO mode, so it is bound to make some wrong choices and that is precisely why Oracle calls these things as hints, so we tell the optimizer to do the right thing. I am in a good mood to write a short note explaining developers why they (must) use hints in their SQL statements. Your input is greatly appreciated TIA Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! File: ESPN_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Solaris 2.8
David, Solaris 2.8 (why they call it Version 8 and specify it as 2.8 I don't know) is in use for production at a lot of sites. I am sure there are bugs and problems with it the same as any other O/S but generally I think it is a safe bet to install Oracle on Solaris 2.8 . Just ensure you check Metalink for the correct/supported version of Oracle. John -Original Message- Sent: 12 April 2002 18:53 To: Multiple recipients of list ORACLE-L I have Oracle8 running on solaris8 for more than a year and don't see any problem. David -Original Message- Sent: Friday, April 12, 2002 10:26 AM To: Multiple recipients of list ORACLE-L Hi friends and gurus, Anybody has had (or has heard from friends of) any bad experience with Oracle 8 running on Solaris 8. I know that the first supported version on Solaris 8 is 8.0.5. If you know of some I would appreciate f you could share it, and specify which version and patch release. Thanks a lot. Djordje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djordje Jankovic INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Another RMAN Problem --- Urgent !!
Hi Samir,
Have you got your environment variables set up correctly. These would be the
following ones
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export NB_ORA_SERV=server name
export NB_ORA_CLIENT=client name
export NB_ORA_SCHED=
export NB_ORA_CLASS=
These will all be set up in the rman backup scripts (I expect)
John
-Original Message-
Sent: 08 April 2002 14:44
To: Multiple recipients of list ORACLE-L
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf'
to '/disk01/oradata/test/data/system01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/rbs01sid1.dbf'
to '/disk01/oradata/test/data/rbs01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/temp01sid1.dbf'
to '/disk01/oradata/test/data/temp01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/tools01sid1.dbf'
to '/disk01/oradata/test/data/tools01sid1.dbf';
set newname for datafile
'/disk01/oradata/sid1/data/users01sid1.dbf'
to '/disk01/oradata/test/data/users01sid1.dbf';
duplicate target database to test
logfile
group 1 '/disk03/oradata/test/logs/log01a01sid1.dbf' size 180M,
group 2 '/disk03/oradata/test/logs/log02a01sid1.dbf' size 180M,
group 3 '/disk03/oradata/test/logs/log03a01sid1.dbf' size 180M;
}
What happens is that RMAN connects to the target, catalog and auxiliary
database and goes into the
RMAN prompt and just hangs thereafter some time, when I type 'exit'
there out of frustration, I
get the following errors :
./create_dupdb.sh[5]: run: not found
./create_dupdb.sh[9]: allocate: not found
./create_dupdb.sh[11]: allocate: not found
./create_dupdb.sh[13]: setlimit: not found
./create_dupdb.sh[14]: setlimit: not found
./create_dupdb.sh[17]: /disk01/oradata/sid1/data/system01sid1.dbf: cannot
execute
./create_dupdb.sh[18]: to: not found
./create_dupdb.sh[21]: /disk01/oradata/sid1/data/rbs01sid1.dbf: cannot
execute
./create_dupdb.sh[22]: to: not found
./create_dupdb.sh[29]: /disk01/oradata/sid1/data/temp01sid1.dbf: cannot
execute
./create_dupdb.sh[30]: to: not found
./create_dupdb.sh[33]: /disk01/oradata/sid1/data/tools01sid1.dbf: cannot
execute
./create_dupdb.sh[34]: to: not found
./create_dupdb.sh[37]: /disk01/oradata/sid1/data/users01sid1.dbf: cannot
execute
./create_dupdb.sh[38]: to: not found
./create_dupdb.sh[77]: duplicate: not found
./create_dupdb.sh[79]: logfile: not found
./create_dupdb.sh[80]: group: cannot execute
./create_dupdb.sh[81]: group: cannot execute
./create_dupdb.sh[82]: group: cannot execute
./create_dupdb.sh[83]: syntax error at line 84 : `}' unexpected
Could anybody please help me in identifying what is the problem and why does
it say 'run:not found' and the
other subsequent lines ?? This is highly urgent for me !!
Thanks and Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.
If you have received this email in error please notify
RE: Another RMAN Problem --- Urgent !!
The auxiliary database needs to have a remote_login_password file. This can
be created running the orapwd command
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs orapwSID password = xx
entries=10
Then add the line remote_login_passwordfile=exclusive to the init.ora and
start the database in exclusive mode (Ensure that this works by performing a
sqlplus internal @tnsnames_alias with the correct password and ensuring that
a connection has been made.
I have had some problems trying to remotely connect using a service so I add
alias to my tnsnames.ora using a SID rather than a service.
John
-Original Message-
Sent: 08 April 2002 16:25
To: Multiple recipients of list ORACLE-L
Jay and Tom,
Thanks a million for your replies. I have now ran the script by creating it
and
then executing it from the RMAN prompt. It worked but I got the following
error :
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
Recovery Manager: Release 8.1.7.2.0 - Production
RMAN-06005: connected to target database: SID1 (DBID=647056675)
RMAN-06008: connected to recovery catalog database
RMAN-06020: connected to auxiliary database
RMAN run {execute script dup_db;}
RMAN-03021: executing script: dup_db
RMAN-03022: compiling command: set
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dupdb_d1
RMAN-08500: channel dupdb_d1: sid=43 devtype=SBT_TAPE
RMAN-08526: channel dupdb_d1: VERITAS NetBackup for Oracle8 - Release 3.4GA
(030800)
RMAN-03022: compiling command: set limit
RMAN-03023: executing command: set limit
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: Duplicate Db
RMAN-03026: error recovery releasing channel resources
RMAN-08031: released channel: dupdb_d1
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-03015: error occurred in stored script dup_db
RMAN-03002: failure during compilation of command
RMAN-03013: command type: Duplicate Db
RMAN-05500: the auxiliary database must be not mounted when issuing a
DUPLICATE command
Can you tell me if there is anything wrong with my Connect String ?? If so,
how do I
specify the connect string correctly ?? The auxiliary is DEFINITELY not
mounted here since
I have started it in NOMOUNT mode. The target db is the one I am trying to
duplicate and
the auxiliary db is the duplicate db I am attempting to create. The target
db is obviously
open. I am not using a password file since I am working directly on the
server.
Regards,
Samir
Samir Sarkar
Oracle DBA
SchlumbergerSema
Email : [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 08 April 2002 14:06
To: [EMAIL PROTECTED]; SARKAR, Samir
Your input to RMAN should be a script or here list. Scrap the shell
script, put your run script into a file, run RMAN from the command line
and call the rman run script that you just created.. Also, set
NLS_DATE_FORMAT and NLS_LANG. Depending on your database version the
to_date function will not work as you have it in set until time.
Jay
[EMAIL PROTECTED] 04/08/02 09:43AM
Dear All,
I am totally out of my depth here and this is a production db which needs to
be restored urgently.
I shall explain my scenario first. Our application team had lost some data
after last Wednesday night's
backup. They have a tool to have a dump of the data from the database from
which they can extract the
data if needed. This dump was taken on Thursday morning. Due to some ***hole
they lost some more data
on Thursday during the day. They recovered this data from the database dump
which they had taken on
Thursday morning.
They now want the data which was lost on Wednesday. Since there was no
database dump available prior to
this, I suggested that we can create a duplicate database upto a point of
time (as on Wednesday night) to a
different location on the server. They could then take a database dump with
their tool and extract the lost records.
This is my script which I compiled with help from Metalink and an old
post from John Hallas (the backup of our target database is taken on tape
with Veritas Netbackup) :
#!/bin/ksh
rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary /
run {
set until time to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS);
allocate channel c_dlt1 type 'SBT_TAPE';
allocate auxiliary channel dupdb_d1 type disk;
setlimit channel c_dlt1 kbytes 67108864 maxopenfiles 128 readrate 256;
setlimit channel dupdb_d1 kbytes 67108864 maxopenfiles 128 readrate 256;
set newname for datafile
'/disk01/oradata/sid1/data/system01sid1.dbf
RE: Another RMAN Problem --- Urgent !!
Tim Gorman wrote For a DUPLICATE DATABASE operation, you don't have to connect to the TARGET at all, if I recall correctly; a DUPLICATE DATABASE operation doesn't involve the TARGET. Sounds funny, until you consider that the operation is reading from tape to the new AUXILIARY database instance. However logical all that sounds Tim, it is incorrect. For some bizarre reason when duplicating a database you have to have 3 connections open (assuming you have a recovery catalogue) The target (which I prefer to call source), the auxiliary (which is the target in my view) and the catalogue. I have raised this question before with Oracle and I did get an answer as to why you still needed to connect to the target database. I cannot remember what the answer was but I though it pretty weak at the time. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Loader
David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY , ENCLOSED BY '' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char to_date(:DateTested,'mm/dd/ hh:mi:ss pm'), TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Another RMAN Problem --- Urgent !!
Congrats, glad we have been of use. And probably a faster response than logging a tar John -Original Message- Sent: 08 April 2002 18:38 To: Multiple recipients of list ORACLE-L John, Jack, Tom, Jay and all those who replied, Thanks so much for ur help. I have been able to resolve my problem. I was connecting to both the target and auxiliary databases with '/' as the connect string and as a result, RMAN was getting confused. I had to necessarily create a password file and connect to RMAN using the connect string : rman target / catalog rman_sid1/rman_sid1@rman817 auxiliary internel/password@test as John and Jay suggested. I also had to put an entry in the tnsnames.ora file for the service name of the auxiliary db which was named 'test'. I also added an entry for the auxiliary db in the listener.ora and restarted it. As for the script, I had to make another change..the line : duplicate target database to test had to be modified to :duplicate target database to test The Restore operation is in progress now...its a big db so it will take some time.will bother you folks again if it fails ;-) Regards, Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Tuning - How to avoid TOCHAR function against a date
I have not got a system to test this out on at the moment but can you do a substr on the to_char so that the format matches the date_key Something like substr((TO_CHAR(:b1,'DD-MON-'),11) John -Original Message- [EMAIL PROTECTED] Sent: 08 April 2002 18:57 To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
