What to look for in STATSPACK report
Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Oracle Internet Directory
Hi! Does anybody out there have any experience with the setup and administration of Oracle Internet Directory (OID)? Do you have any white papers or presentations on how to do so? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Delete Java Classes
Hi!
How do I delete Java classes from a schema?
I have a script that queries user_objects and genereates "drop table", "drop
synonym" etc. statements.
After the script is run, there are still java classes left in the schema. I
can use Enterprise Manager to drop them, but I would want to do it within
sql*plus.
Any ideas?
dbms_java.dropjava(' -schema INTFACE') doesn't seem to work.
What does this statement do exactly?
This is 9.2 on HP-UX.
Thanks,
Helmut
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daiminger, Helmut
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).
Anybody familiar with OWB setup and config out there?
Hi! Is anybody out there familiar with OWB setup an config? We are having problems with the "OWB-specific-listener" required for OWB. This is OWB 9.0.4 on HP-UX 11.11 Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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 can I make Oracle not use all processors in machine?
Hi! We are currently discussing Oracle licensing issues. Due to the number of users, named user licensing is not an option for us. Our HP-boxes are having 10 processors each. Since the CPU's are 90% idle, we are thinking about limiting Oracle to only using 4 out of the 10 CPUs. Is this possible (i.e. via the parameter cpu_count)? The other 6 CPU's will then be used by other applications ion the box (e.g. BEA WebLogic etc.). This is 9.2 on HP-UX 11.11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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 is Oracle process using 25 MB of RAM when idle?
Hi Stephane, thanks for your reply. We are measuring the values by getting the OS process ID of a specific Oracle connection and then trach that process ID using glance (on HP-UX). Since the SGA is ab 1.5 GB, it is definitely not attached to the memory consumed by each process. I know that this is an issue on Solaris. We tried and used a whole bunch of different processes and they were all using 20-25 MB of RAM (doing nothing). This number seems just a little bit high to me... Example: If I have an SGA with 1 GB, 200 MB of pga-aggregate-target and 200 users connecting to the datbase (although only about 10% of them are active at the same point in time). This would mean that my memory consumption is: 1 GB + 200 MB + 200*25 MB = 6.2 GB... Regards, Helmut -Original Message- Sent: Monday, November 17, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Helmut, I don't know how you are measuring your numbers, but beware that what the operating system reports is often somewhat misleading. Typically, shared memory is often 'attributed' to each and every process linked to it. When you think about it it makes sense, but at the same time it does mean that n processes will really use much less than n * the amount of memory reported as used by one process. This is true both of the 'program' part of user memory (shared libraries) and of the 'data' part of it (SGA). When your process connects, it attaches the SGA and some shared libraries, and more shared libraries come into play as it starts doing something. You may have a better view of what is really used by your process by checking into V$SESSTAT, which holds a number of values about it. HTH, SF >- --- Original Message --- - >From: "Daiminger, Helmut" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Sent: Sun, 16 Nov 2003 23:09:26 > >Hi, > >we are running 9.2 on HP-UX here. > >We have pg_aggregate_target configured, but I >realized (in my opinion) very >high memory consumption of Oracle Unix processes. > >a) How come that one Oracle Connection (i.e. >dedicated Unix process on HP) >is using up at least 22 MB of RAM? It is using 22 >MB if the user is just >connected, not doing anything. > >Any way I can modify this? > >b) If the user is querying data and the like, the >memory consumption goes up >to 60 MB. How come? > >Thanks! > >Regards, >Helmut > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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.net -- Author: Daiminger, Helmut 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).
Why is Oracle process using 25 MB of RAM when idle?
Hi, we are running 9.2 on HP-UX here. We have pg_aggregate_target configured, but I realized (in my opinion) very high memory consumption of Oracle Unix processes. a) How come that one Oracle Connection (i.e. dedicated Unix process on HP) is using up at least 22 MB of RAM? It is using 22 MB if the user is just connected, not doing anything. Any way I can modify this? b) If the user is querying data and the like, the memory consumption goes up to 60 MB. How come? Thanks! Regards, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Memory consumption on HP-UX
Hi, how do I find out how much memory Oracle uses on an HP-UX box? Finding the shared memory portion (i.e. SGA) is fairly easy... But how do I find out how much memory each dedicated user process is consuming? Or is the rule of thumb like this: no matter whether you have 10 or 500 users, the memory consumed by the user processes will never exceed pg_aggregate_target? This would mean that the maximum memory consumption is SGA + PGA_AGGREGATE_TARGET. No matter how many users are on the system (of course you would size PGA_aggregate_target accordingly beforehand). This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
9i on Win2k Cluster
Hi, does anybody out there have a paper that describes how to set up 9i on a Win2k cluster? We need to test the cluster capabilites fo the database. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Oracle Warehouse Builder (OWB) mailing list
Hi! Does anybody out there know of a mailing list / newsgroup / user group concerning Oracle Warehouse Builder? Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Moving projects from development/test to production
Hi! A somewhat off-topic question this time. I am put in charge of defining the procedure of moving projects from test/development into the production environment. This is to be seen from the entire IT-perspective (i.e. not just databases, but also Unix, Oracle and SAN). I.e. we should come up with check-lists and the like; although having an eye on quality assurance... We urgently need to set procedures up for that since the last time this was a nightmare... Did anybody out there work on a similar project? What are the procedures that you are following? Any input would be appreciated. This is 9.2 on HP-UX 11. Thanks, Helmut Helmut Daiminger WWK Lebensversicherung a.G. Marsstrasse 37 80292 München Telefon: (0 89) 51 14 - 3490 Fax: (0 89) 51 14 - 27 62 mailto:[EMAIL PROTECTED] http://www.wwk.de *** select 'bye for now' from sys.dual *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: oracle internet directory
Title: Nachricht Hi Paul, please see answers below... 1. Is this a stand alone OID or is it part of 9iAS infrastructure or OCS? This will be stand alone. Probably we need to integrate it with Windows Active Directory. Not sure yet. We are still discussing about it. 2. What have you done up to this point? If you are at 9.2.0.4 do not use the DBCA OID template to create the database. There is a nasty bug with the seed database. It installs a 9.2.0.1 OID database and then you have upgrade the schema to 9.2.0.4. Create a non-OID database and use OIDCA to create the schema for OID. Nothing so far. See above. 3. what problems are you having? none yet. But I'm sure there's gonna be plenty... ;) Thanks, Helmut
oracle internet directory
Hi! Does anybody out there have any experience with the setup and implementation of Oracle Internet Directory in a 9.2 environment? This is 9.2 on HP-UX 11. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Conflicting Java VM after Oracle 9.2 client install
Hi! Does anybody have any exprience with conflicting client JVMs? We are installing software though Microsoft SMS software packaging on the Clients (PC running XP). The deal is that another application (PVCS Dimensions) works fine if it is distributed on the systems without the Oracle 9.2 client. Because it can then use the Microsoft Virtual Machine that comes with Internet Explorer. But if we also install the Oracle Client 9.2, the Sun Java VM is installed as well and then the PVCS client doesn't work anymore. The question is: can I install the Oracle Client without having the Sun VM installed? Or is there a way that I can modify my Oracle installation that it doesn't change Internet Explorer settings to use the Sun VM? This is 9.2 client on Win XP. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Solved: Find CPU clock speed on HP-UX 11
Hi! I got the answer from an Unix Admin... There are three ways: 1) If you can reboot the machine you can see it on startup. 2) If not, use SAM (type sam at # prompt) and go to performance monitors, system properties. 3) echo 'itick_per_usec/D' | adb /stand/vmunix /dev/mem echo 'runningprocs/D' | adb /stand/vmunix /dev/mem hth, Helmut -Original Message- Sent: Thursday, October 16, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Hi I am also not aware of HP but on solaris u can find using prtdiag command U can find this command in /usr/platform/sun4u/sbin Regards Pawan Dalmia Database Administrator Orange-9820018753 Extn -5064 -Original Message- Sent: Thursday, October 16, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi! I'm new to HP-UX and need to find out how many processors are in a mchine and at what clock speed they operate. I can get the number of CPUs through glance or top, but I have no idea about the clock speed. How would I get that information? I would know how to do it on Solaris though... ;) Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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). "The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you." -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pawan Dalmia 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.net -- Author: Daiminger, Helmut 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).
Find CPU clock speed on HP-UX 11
Hi! I'm new to HP-UX and need to find out how many processors are in a mchine and at what clock speed they operate. I can get the number of CPUs through glance or top, but I have no idea about the clock speed. How would I get that information? I would know how to do it on Solaris though... ;) Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Problem with undo tablespace and snapshot too old
Hi! We are experiencing a weird problem here... We have automatic undo management enabled and the undo tablespace is 6 GB in size. undo_retention is set to 30 minutes. when a certain transaction runs, it fails with ORA-1555 Snapshot too old, although the undo tablespace only uses 700 MB (out of 6 GB possible). That loos weird to me... Then our other DBA suggested to cut the size of the buffer cache in half and let the transaction run again. We have done that and it worked flawlessly... WHY??? What is the relation between the buffer cache size und rollback (i.e. undo retention)? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
Experience wsith Oracle Warehouse Builder (OWB)
Hi! Does anybody out there have any experience with Oracle Warehouse Builder (9.0.4 or later)? I am supposed to get my feet wet with it, but have no clues as where to start... This is 9i on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
RMAN problem: RMAN-20242
Hi there! We are having problems with an Oracle backup. The compiling of the backup command fails with the error message: RMAN-20242: specification does not match any archivelog in the recovery catalog But RMAN is only supposed to backup any archived logs that are there and then insert them in the catalog... Did anybody experience anything similar? This is 8.1.7 on HP-UX with Legato Networker Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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).
AW: two instances on same box (Suse Linux ES 8)
Title: AW: two instances on same box (Suse Linux ES 8) yes, by starting the 9.01 instance first, then editing /etc/profile.d/oracle.sh file and then start the second instance from a second terminal window. Not very elegant, but it worked... -- Helmut > -Ursprüngliche Nachricht- > Von: Jack van Zanen [mailto:[EMAIL PROTECTED]] > Gesendet: Montag, 22. September 2003 13:20 > An: Multiple recipients of list ORACLE-L > Betreff: RE: two instances on same box (Suse Linux ES 8) > > > Have you ever succeded in starting the 9.2.0 database? > > > jack > > -Original Message- > Sent: Monday, September 22, 2003 12:40 PM > To: Multiple recipients of list ORACLE-L > > > Hello Jack, > The problem is that the NLS environment does not get reset > when using oraenv. > It resets ORACLE_HOME and ORACLE_SID, but ORA_NLS33 always > has the values of the first instance. > I had a look at the oraenv script, and there is no "NLS" > string in there... > So when you try to switch to the second instance and start > it, you get th error message that "missing or wrong value for > NLS variable". Any idea? > Thanks, > Helmut > > > > -Ursprüngliche Nachricht- > > Von: Jack van Zanen [mailto:[EMAIL PROTECTED]] > > Gesendet: Montag, 22. September 2003 12:20 > > An: Multiple recipients of list ORACLE-L > > Betreff: RE: two instances on same box (Suse Linux ES 8) > > > > > > What are the error messages?? > > > > Should be able to run w/o problems > > > > > > > > Jack > > > > > > -Original Message- > > Sent: Monday, September 22, 2003 11:55 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi! > > We are running 2 Oracle instances on one Suse Linux box > > (under the same Unix oracle user). > > We have two oracle homes > > 9.0.1 (/opt/oracle/iasdb) > > and > > 9.2.0 (/opt/oracle/loga) > > The first instance starts up fine, but the second one fails. > > It seems to me that switching the environments fails. > > How do I switch between environments? ORAENV and dbhome don't > > work too well. > > > > oratab looks fine. > > Does anybody have experience with multiple instance on Linux > > (especially Suse). not sure whether Red Hat is similar... > > This is 9i on Suse Linux Enterprise Server 8. > > Thanks, > > Helmut > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jack van Zanen > > 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.net > -- > Author: Jack van Zanen > 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). >
AW: two instances on same box (Suse Linux ES 8)
Title: AW: two instances on same box (Suse Linux ES 8) Hello Jack, The problem is that the NLS environment does not get reset when using oraenv. It resets ORACLE_HOME and ORACLE_SID, but ORA_NLS33 always has the values of the first instance. I had a look at the oraenv script, and there is no "NLS" string in there... So when you try to switch to the second instance and start it, you get th error message that "missing or wrong value for NLS variable". Any idea? Thanks, Helmut > -Ursprüngliche Nachricht- > Von: Jack van Zanen [mailto:[EMAIL PROTECTED]] > Gesendet: Montag, 22. September 2003 12:20 > An: Multiple recipients of list ORACLE-L > Betreff: RE: two instances on same box (Suse Linux ES 8) > > > What are the error messages?? > > Should be able to run w/o problems > > > > Jack > > > -Original Message- > Sent: Monday, September 22, 2003 11:55 AM > To: Multiple recipients of list ORACLE-L > > > Hi! > We are running 2 Oracle instances on one Suse Linux box > (under the same Unix oracle user). > We have two oracle homes > 9.0.1 (/opt/oracle/iasdb) > and > 9.2.0 (/opt/oracle/loga) > The first instance starts up fine, but the second one fails. > It seems to me that switching the environments fails. > How do I switch between environments? ORAENV and dbhome don't > work too well. > > oratab looks fine. > Does anybody have experience with multiple instance on Linux > (especially Suse). not sure whether Red Hat is similar... > This is 9i on Suse Linux Enterprise Server 8. > Thanks, > Helmut > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jack van Zanen > 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). >
two instances on same box (Suse Linux ES 8)
Title: two instances on same box (Suse Linux ES 8) Hi! We are running 2 Oracle instances on one Suse Linux box (under the same Unix oracle user). We have two oracle homes 9.0.1 (/opt/oracle/iasdb) and 9.2.0 (/opt/oracle/loga) The first instance starts up fine, but the second one fails. It seems to me that switching the environments fails. How do I switch between environments? ORAENV and dbhome don't work too well. oratab looks fine. Does anybody have experience with multiple instance on Linux (especially Suse). not sure whether Red Hat is similar... This is 9i on Suse Linux Enterprise Server 8. Thanks, Helmut
IBM AIX 32-Bit
Title: IBM AIX 32-Bit Hi there! I got a question: Can I run Oracle 64 Bit on IBM AIX 32 Bit? I know that AIX 64 Bit can run either Oracle 32 Bit or 64 Bit. Does it also work the other way round? Thanks, Helmut
Oracle 9i Rel. 2 on Red Hat Advanced Server 2.1
Title: Oracle 9i Rel. 2 on Red Hat Advanced Server 2.1 Hi! Is anybody using Oracle 9i on Red Hat Advanced Server? I just installed RAS 2.1 and did not notice much difference to a regular Red Hat Installation... What is the beig difference between Advanced Server and "regular" Red Har Linux? Thanks, Helmut
Recommendation for "cheap" HA solution
Title: Recommendation for "cheap" HA solution Hi! We are looking into establishing some sort of high availability solution here. We are running 9.2.0 on Sun Fire 280 (2 processors). Since we are on a tight budget, we are looking into various solutions for HA. One option would be to use Sun Cluster Server or Veritas Cluster Server. If one box fails, the db just fails over to the other node. The problem is that we don't have a cluster guy here... The other Option would be to use RAC, but this is the most expensive solution, I guess... Does anybody use any other HA solution that is affordable? Failover time should be less than 15 minutes, although "frequent" outages (i.e. once a month or so) are tolerable. Don't blame me for these requirements; it was not my idea... This is 9.2.0 on Sun Solaris. Thanks, Helmut
9i startup on Linux
Title: 9i startup on Linux Hi! I am having problems getting 9.2.0.3 started on Red Hat Linux 8. I've done the standard linking in rc3.d and the like. But when I reboot the machine, Oracle is not automatically started when using an SPFILE. If I dump the spfile to a text init.ora, the output is a follows: [EMAIL PROTECTED] root]# /bin/su - $ORA_OWNER -c $ORA_HOME/bin/dbstart SQL*Plus: Release 9.2.0.3.0 - Production on Thu Apr 3 08:25:26 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> ERROR: ORA-12162: TNS:service name is incorrectly specified SQL> ORA-12162: TNS:service name is incorrectly specified SQL> Database "" warm started. SQL*Plus: Release 9.2.0.3.0 - Production on Thu Apr 3 08:25:27 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> Connected to an idle instance. SQL> ORACLE instance started. Total System Global Area 105976704 bytes Fixed Size 451456 bytes Variable Size 83886080 bytes Database Buffers 20971520 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production Database "OLLIE" warm started. Is there anything special about dbstart/dbshut in regard to 9i? Thanks, Helmut
AW: Unix command
Title: AW: Unix command Roland, try: ls *.txt or if you insist on using the grep command: ls |grep .txt or ls -l|grep .txt hth, Helmut > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Gesendet: Dienstag, 25. März 2003 09:14 > An: Multiple recipients of list ORACLE-L > Betreff: Unix command > > > Any one whom could help me with grep command. > > I would like to "catch" all files in a directory which ends with .txt > > The files in the directory that fits that condition is: > > ia123456.txt > ia654321.txt > > > > How should I write the unix command? > > Thanks in advance > > Roland > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > 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). >
RMAN question
Title: RMAN question Hi! How do I make RMAN delete all obsolete backups? We are using a recovery catalog. Example: RMAN> report obsolete; RMAN-03022: compiling command: report Report of obsolete backups and copies Type Key Completion Time Filename/Handle -- -- Backup Set 523573 29012003-16:19:33 Backup Piece 523575 29012003-16:19:33 03ee44q5_1_1 Backup Set 525544 31012003-03:47:24 Backup Piece 525546 31012003-03:47:24 06ee80te_1_1 Backup Set 525545 31012003-08:04:25 Backup Piece 525547 31012003-08:04:25 07ee8d2m_1_1 Backup Set 526282 01022003-03:34:14 Backup Piece 526284 01022003-03:34:14 0aeealdg_1_1 Backup Set 526283 01022003-07:55:02 Backup Piece 526285 01022003-07:55:02 0beeb0m0_1_1 Backup Set 527884 04022003-03:52:28 Backup Piece 527886 04022003-03:52:28 0eeeiiig_1_1 Backup Set 527885 04022003-08:12:52 Backup Piece 527887 04022003-08:12:52 0feeius4_1_1 Backup Set 528851 05022003-03:51:41 Backup Piece 528853 05022003-03:51:41 0ieel6sk_1_1 Backup Set 528852 05022003-08:13:00 Backup Piece 528854 05022003-08:13:00 0jeelj6h_1_1 Backup Set 529486 06022003-03:43:12 Backup Piece 529488 06022003-03:43:12 0meenrds_1_1 Backup Set 529487 06022003-08:00:57 Backup Piece 529489 06022003-08:00:57 0neeo72i_1_1 Now I want to delete those obsolete backups... RMAN> delete obsolete; RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-00558: error encountered while parsing input commands RMAN-01005: syntax error: found "obsolete": expecting one of: "expired, script" RMAN-01007: at line 1 column 9 file: standard input What would be the correct syntax? This is 8.1.7 on solaris. Thanks, Helmut
RMAN question
Title: RMAN question Hi! How do I make RMAN delete all obsolete backups? We are using a recovery catalog. Example: RMAN> report obsolete; RMAN-03022: compiling command: report Report of obsolete backups and copies Type Key Completion Time Filename/Handle -- -- Backup Set 523573 29012003-16:19:33 Backup Piece 523575 29012003-16:19:33 03ee44q5_1_1 Backup Set 525544 31012003-03:47:24 Backup Piece 525546 31012003-03:47:24 06ee80te_1_1 Backup Set 525545 31012003-08:04:25 Backup Piece 525547 31012003-08:04:25 07ee8d2m_1_1 Backup Set 526282 01022003-03:34:14 Backup Piece 526284 01022003-03:34:14 0aeealdg_1_1 Backup Set 526283 01022003-07:55:02 Backup Piece 526285 01022003-07:55:02 0beeb0m0_1_1 Backup Set 527884 04022003-03:52:28 Backup Piece 527886 04022003-03:52:28 0eeeiiig_1_1 Backup Set 527885 04022003-08:12:52 Backup Piece 527887 04022003-08:12:52 0feeius4_1_1 Backup Set 528851 05022003-03:51:41 Backup Piece 528853 05022003-03:51:41 0ieel6sk_1_1 Backup Set 528852 05022003-08:13:00 Backup Piece 528854 05022003-08:13:00 0jeelj6h_1_1 Backup Set 529486 06022003-03:43:12 Backup Piece 529488 06022003-03:43:12 0meenrds_1_1 Backup Set 529487 06022003-08:00:57 Backup Piece 529489 06022003-08:00:57 0neeo72i_1_1 Now I want to delete those obsolete backups... RMAN> delete obsolete; RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-00558: error encountered while parsing input commands RMAN-01005: syntax error: found "obsolete": expecting one of: "expired, script" RMAN-01007: at line 1 column 9 file: standard input What would be the correct syntax? This is 8.1.7 on solaris. Thanks, Helmut
8i: how to find current scn number?
Title: 8i: how to find current scn number? Hi! How do I find out the current scn number that the database is at? In 9i I could use dbms_flashback package... This is 8.1.7 on Solaris 8. Thanks, Helmut
Change NLS variables in logon trigger...
Title: Change NLS variables in logon trigger...
Hi there!
I' having a logon trigger on 9.2.0.2 which sets
DBMS_SESSION.set_nls('nls_language','''GERMAN''');
DBMS_SESSION.set_nls('nls_territory','''GERMANY''');
But I'm getting the following error message:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot SET NLS in a trigger
ORA-06512: at "SYS.DBMS_SESSION", line 141
ORA-06512: at line 2
This trigger works perfectly fine on 7.3.4 and 8.x.x
Error ORA-4092 just says that I can't do a commit or rollback in a trigger...
This 9.2.0.2 on HP-UX.
Any ideas?
Thanks,
Helmut
EMC EDM backup utility and 9i
Title: EMC EDM backup utility and 9i Hi! Is anybody out there using EMC's EDM backup utility to back up their Oracle databases? We're running into problems when trying to back up 9i. 8i works fine. Thanks, Helmut
create tablespace script
Title: create tablespace script Hi! I want to write a "create tablespace" script that creates all "create tablespace" statements for a database. I got this script working if each tablesspace has only one datafile. But how would I handle it if a tablespace consists of 2 datafiles, e.g. datafile 5 and 87 from dba_data_files... Is there an id for the datafiles within the tablespace??? Any ideas? Thanks, Helmut
Freeware tool to browse data in Oracle tables?
Title: Freeware tool to browse data in Oracle tables? Hi! Does anybody out there know of a freeware tool that allows you to browse through Oracle tables and edit data? I know this can be done through ODBC and Excel, but I need this for "regular" users who need this tool to be quite simple... Thanks, Helmut
Can't start Apache for OEM web interface
Title: Can't start Apache for OEM web interface Hi! I am trying to configure the Enterprise Manager Console to be accessed from a web browser. Does anybody have any experience with this? My client is XP and IE6. The database (9.2.0.2) and Apache web server are running on Solaris 8. I did a standard Apache install (through OUI). dagobert ; DUCK - /export/home/u01/app/oracle/product/9.2.0/Apache/Apache/bin >apachectl stop apachectl stop: httpd (pid 9945?) not running > dagobert ; DUCK - /export/home/u01/app/oracle/product/9.2.0/Apache/Apache/bin >apachectl start apachectl start: httpd could not be started > dagobert ; DUCK - /export/home/u01/app/oracle/product/9.2.0/Apache/Apache/bin >ps -ef|grep httpd oracle 2 23167 0 16:21:13 pts/1 0:00 grep httpd The error_log file says: [Wed Jan 15 16:21:06 2003] [crit] (2)No such file or directory: Apache JServ enc ountered a fatal error; check your ApJServLogFile for details if none are presen t in this file. Exiting. What is ApJServLogFile and where can I find it??? Thanks, Helmut
AW: How to create two db-links within one schema when global_name
Title: AW: How to create two db-links within one schema when global_names=tr Hi Jonathan, thanks for your reply. The deal is that you can't use DB1@HR and DB1@SHIP if you are using global_names=true; the link name has to be the same than the global database name. But what do I do, if I need the same user to have two db-links to different schemas on the remote database? Any idea? Thanks, Helmut -Ursprüngliche Nachricht- Von: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 8. Januar 2003 12:14 An: Multiple recipients of list ORACLE-L Betreff: Re: How to create two db-links within one schema when global_names=tr Look up the details of 'connection qualifiers'. create public database link DB1@HR connect to hr identified by pwd_hr using 'DB1'; create public database link DB1@SHIP connect to shipping identified by pwd_shipping using 'DB1'; Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 January 2003 10:57 global_names=tr >Hi! > >Is there a way to create two db-links within one schema when >global_names=true? > >e.g. >create public database link DB1 connect to hr identified by pwd_hr using >'DB1'; >create public database link DB1 (???) connect to shipping identified by >pwd_shipping using 'DB1'; > >This is 8.1.7 on Solaris. > >Thanks, >Helmut > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 to create two db-links within one schema when global_names=tr
Title: How to create two db-links within one schema when global_names=true? Hi! Is there a way to create two db-links within one schema when global_names=true? e.g. create public database link DB1 connect to hr identified by pwd_hr using 'DB1'; create public database link DB1 (???) connect to shipping identified by pwd_shipping using 'DB1'; This is 8.1.7 on Solaris. Thanks, Helmut
Extent sizes when using LMT with autoallocate clause
Title: Extent sizes when using LMT with autoallocate clause Hi! I was wondering what the allocated extent sizes for locally managed tablespaces with the autoallocate clause are? The first extents are 64k in size. How many 64k chunks are allocated? What are the next sizes? CREATE TABLESPACE local_auto DATAFILE '/u02/oradata/local_auto01.dbf' SIZE 1M reuse EXTENT MANAGEMENT LOCAL AUTOALLOCATE; This is 9.2.0.2 on Solaris. Thanks, Helmut
to_date function and NLS settings on client
Title: to_date function and NLS settings on client
Hello!
We have several Windows clients connecting to our db-server. Every client issues the statement:
select from thabe where date = to_date('01.10.1950','DD-MM-');
Some clients return the correct number of rows where as others return nothing.
I know that this has something to to with the NLS settings on the client.
How do I find out the NLS settings on the client? Just in the registry?
How coem this does not work although I explicity specify a format mask?
This is 8.1.7 on Solaris.
Thanks,
Helmut
Oracle's File Mapping
Title: Oracle's File Mapping Hi! Did anybody out there attend the Open World presentation "Optimize Performance with Oracle's File Mapping Feature"? Could you please shed some light on this? Is this an Oracle optional product? Or is it a Veritas product? Thanks, Helmut
AW: Differences between Oracle 9.2.0 EE and SE
Title: AW: Differences between Oracle 9.2.0 EE and SE Rick, there is a note on Metalink that gives you all the differences: 112591.1 But it only covers releases up to 8i. 9i should be somewhat similar (of course stuff like RAC only works with EE). As far as I know, there is no document from Oracle Support that gives you that info for 9iR2. hth, Helmut -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 6. November 2002 15:49 An: Multiple recipients of list ORACLE-L Betreff: Differences between Oracle 9.2.0 EE and SE I have given up on trying to find document detailing differences between Oracle 9i (9.2.0) EE and SE. Can someone provide a link,etc for this? Thanks Rick -- 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).
sqlplus /nolog
Title: sqlplus /nolog Hi! I was wondering what sqlplus /nolog actually does on Unix? Is it only used for not listing username/password when doing a ps ? Or anything else? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Can't create job...
Title: Can't create job... Hi! I'm experiencing a weird problem here... Why can't I create this job??? SQL> variable jobno number; SQL> begin 2 dbms_job.submit(:jobno, 'PROBLEM_MELDUNG', sysdate, 'sysdate+1/3600'); 3 commit; 4 end; 5 / begin * ERROR at line 1: ORA-06550: line 1, column 109: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( @ % ; The symbol ";" was substituted for "END" to continue. ORA-06512: at "SYS.DBMS_JOB", line 79 ORA-06512: at "SYS.DBMS_JOB", line 131 ORA-06512: at line 2 PROBLEM_MELDUNG is a procedure that updates a table in the user's schema. Nothing fancy. This is 8.1.7 on Sun Solaris. Thanks, Helmut
Unix question: how to display SID and path in prompt
Title: Unix question: how to display SID and path in prompt Hi! In my .profile of the oracle user (we're mostly using ksh here), I have set up the prompt that it gives me the host name and database SID. # always displays host name and Oracle SID as prompt PS1="`hostname`;`echo $ORACLE_SID`$ " How can I extend this prompt to also include the current directory that I'm in? e.g. prod1;PCLDB1; u010/app/oracle/admin/PCLDB1 Thanks, Helmut
How do I restore an old RMAN backup
Title: How do I restore an old RMAN backup Hi! I want to put a copy of the production database onto a test box. The deal is that I need to use the full backup taken on August 1st. How do I tell RMAN not to restore from the last backup, but use the one that was created almost two weeks ago? This is 8.1.7 on Solaris. Thanks, Helmut
RMAN: How to restore backup to a different box
Title: RMAN: How to restore backup to a different box 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
Off topic: Backup MySQL database
Title: Off topic: Backup MySQL database Hi! Sorry to bother you with MySQL, but I don't have a clue about it... We are running a little MySQL database here and nobody is really familiar with it (to put it politely). Now we are discussing a backup "strategy" for it. The word is that you simply need to copy the data files to a different disk (or tape) and that's it. Is that right? Even when the database is running and open for users? Does anybody have some hints on how to back up MySQL databases? Thanks, Helmut
catalog.sql takes forever after applying 8.1.7.4.0 patch
Title: catalog.sql takes forever after applying 8.1.7.4.0 patch Hi! I just installed the 8.1.7.4.0 patch on Sun Solaris. I followed the instructions closely and when it says to run catalog.sql, I started the script, but it just sits there and does seemingly nothing (for approx 30 minutes now...). "top" shows no load on the box. Any idea what's going on? This is 8.1.7 on Solaris. Thanks, Helmut
How to move 200 GB db from prod to dev?
Title: 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
Oracle 32 Bit running on Solaris 64 Bit
Title: 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
how can listener list on two ports for the same db?
Title: how can listener list on two ports for the same db? Hi! We have a db server (in a cluster) with two NICs and two IP-Adresses (i.e. logical names in the cluster). How do I configure the listener so that it listenes for both logical names of the machine? LISTCMDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = kfplcmdb)(PORT = 1522)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = kfalcmdb)(PORT = 1522)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_CMDB)) ) ) ) SID_LIST_LISTCMDB = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc_CMDB) (ORACLE_HOME = /cmdb/u0x/u01/app/oracle/product/8.1.7) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = CMDB) (ORACLE_HOME = /cmdb/u0x/u01/app/oracle/product/8.1.7) (SID_NAME = CMDB) ) ) Sitting on our RMAN database machine, pinging kfplcmdb does not work but pinging kfalcmdb works fine. On th emachine to be backed up: why can't I use kfalcmdb in the listener.ora file? The listener works fine when using kfplcmdb. This is 8.1.7 on Solaris 8. Thanks, Helmut
script for comparing two schemas in two databases
Title: script for comparing two schemas in two databases Hi! Does anybody out there have a script that allows me to compare two schemas in two different databases (i.e. development and production db)? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Export problem
Title: Export problem Hi! Can I export a 7.3.3 database with a 8.1.7 export utility? Theoretically it should work. EXP-8: ORACLE error 942 encountered ORA-00942: Tabelle oder View nicht vorhanden EXP-00024: Export views not installed, please notify your DBA EXP-0: Export terminated unsuccessfully But why am I getting these error messges? What scripts need to be run to solve this problem? This is 7.3.3 / 8.1.7 on Win NT. Thanks, Helmut
How to use profiles??
Title: How to use profiles?? Hi! We are experiencing performance problems on one of our production boxes, because some sessions are using up all the resources. All the users connect to the db through an application server using the same database user (approx 30 simultaneous connections). I was thinking about using profiles to limit the amount of resources a session can use. But I am pretty unsure to what values I should set the single criterias. The db is running on a Sun server, 4 CPUs, 4 GB of RAM and a Raid 0+1 disk array. What would be reasonable ballpark numbers to set for cpu_per_session cpu_per_call logical_reads_per_session logical_reads_per_call What would happen if cpu_per_session is set to 500 (i.e. 5 seconds) and the session used up all that CPU time? Would the session error out then? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Best way to move db from AIX to Solaris
Title: Best way to move db from AIX to Solaris Hi! We want to migrate an Oracle db (approx. 50 GB) from AIX to Sun Solaris. What would be the best way to achieve this? Export/Import I guess, right? This is 8.1.7 on Sun Solaris/AIX. Thanks, Helmut
How to duplicate production database onto development box
Title: How to duplicate production database onto development box Hi! We want to put an exact copy of our production database (approx. 200 GB) onto a development box. What would be the best way to achieve this? Export/import would take kinda long... ;) Would transportable tablespaces be the way to go? This is 8.1.7 on Sun Solaris. Thanks, Helmut
max open cursors exceeded
Title: max open cursors exceeded Hi! We are having problems with Oracle interMedia text and open cursors used: our instance has open_cursors et to 1000. We check the number of open cursors regularly using: break on report comp sum of curs on report select User_Name, SID, count(*) Curs from v$open_cursor group by User_Name, SID order by User_Name, SID; the corresponding sql text is extracted from the dd using: select SID, User_Name, SQL_Text from v$open_cursor order by User_Name, SID, SQL_Text; ususally, the number of open cursors used is about 300. if we are using interMedia text option, it usually rises and rises every day. after bouncing the instance, we are back to slightly below 300. Questions: a) MAX_OPEN_CURSORS is set to 1000 - when exceeding that threshold, we're experiencing problems; how can we monitor which users uses up all the cursors (and how many). b) What exactly is displayed when querying v$open_cursor? - I know htat there are parsed statements of the user/session - but this should be more than just a PL/SQL "declare cursor... open...fetch...close cursor", right? What about statements issued in SQL*Plus or through JDBC etc.? c) The results from v$open_cursor is equivalent to the currently running transaction, right? So when are those entries removed again? When the transaction commits or when the sessions ends? Or when issuing a PL/SQL "close cursor". Or are those entries overwritten? by whom? when? d) what does querying "select * from v$sql_cursor" return? Any ideas? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Async I/O on Sun Solaris
Title: Async I/O on Sun Solaris Hi! How can I find out whether my operating system supports async I/O? Can I turn this on/off? What is the relation between async I/O and the usage of Oracle I/O slaves? Can anybody shed some light on this? This is 8.1.7 on Sun Solaris 8. Thanks, Helmut
Fragmentation of data dictionary
Title: Fragmentation of data dictionary Hi! I was wondering whether the Oracle data dictionary gets fragmented and whether the dba needs to do something about it. E.g. if granting tons of rights through grants directly to a user (and later revoke them), does the data dictionary get fragmented (i.e. the x$ tables that hold that information)? Would it be beneficial to reorganise the dd (or at least rebuild the indexes)? Same thing with creating tons of temporary tables that are created during a session and get dropped at the end of a session. Does this fragment the dd? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Weird connection behavior
Title: Weird connection behavior Hi! I'm experiencing a weird behavior here: when I try to connect to an Instance - named ITSP - (I am locally on the server): user/pw@itsp, the connect works fine. But when I try to connect to that same database from my windows client, using user/pw@itsp, I get the message: Oracle not available. Any idea what is going on here? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Migrating from 8.1.6 EE to 8.1.7 SE
Title: Migrating from 8.1.6 EE to 8.1.7 SE Hi! Our company wants to upgrade some of our databases from 8.1.6 to 8.1.7. Right now, we are using 8.1.6 Enterprise Edition, but when we migrate to 8.1.7 we want to use Standard Edition. What would be the procedure to migrate from 8.1.6. EE to 8.1.7 SE? Do we have to go to 8.1.7 EE first? This is on Win2k. Thanks, Helmut
AW: RE: How to find out what caused job to fail?
Title: AW: RE: How to find out what caused job to fail? Roland, you can read the alert log file with vi (or notepad if you are on windows). It can be found in background_dump_dest (look in the init.ora for it). The naming convention is alert_.log. Oracle writes lots of information about what is going on in the database (e.g. tablespace modifications, log switches and tons more) into that file. Trace files are written to background_dump_dest (for background processes), user_dump_dest (for user processes) or core_dump_dest (Oracle core dumps). All files are plain text files. hth, Helmut > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Gesendet: Montag, 4. Februar 2002 10:36 > An: Multiple recipients of list ORACLE-L > Betreff: Ang: RE: How to find out what caused job to fail? > > > > But how do I use the alert log and where canI find the trace file? > > > Roland > > > > > [EMAIL PROTECTED]@fatcity.com den 2002-02-04 01:05 PST > > Sänd svar till [EMAIL PROTECTED] > > Sänt av: [EMAIL PROTECTED] > > > Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Kopia: > > Hi, you can find the reason in alert log or trace files. > -Original Message- > From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 04, 2002 4:40 PM > To: Multiple recipients of list ORACLE-L > Subject: How to find out what caused job to fail? > > > > Hi! > > > Is there a way to find out, why a database job failed? > in dba_jobs (or user_jobs), I can only see that it failed, > but no indication of the reason why it failed. Any ideas? > > > This is 8.1.7 on Solaris. > > > Thanks, > Helmut > > > > > > > > > > > > > > -- > 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). >
How to find out what caused job to fail?
Title: How to find out what caused job to fail? Hi! Is there a way to find out, why a database job failed? in dba_jobs (or user_jobs), I can only see that it failed, but no indication of the reason why it failed. Any ideas? This is 8.1.7 on Solaris. Thanks, Helmut
Different RMAN capabilities in Standard/Enterprise Edition?
Title: Scheibenbremsen für Magura "Julie" Hi! I was told that RMAN has limited capabilties when used with Oracle Server Standard Edition (e.g. no incremental backups). Is that true? Are all RMAN features only available with Enterprise Edition? This is 8.1.6 on Win2k. Thanks, Helmut
Connect as sysdba on 9i
Title: Connect as sysdba on 9i Hi! I have a question concerning "connect sys as sydba" on 9i. When I open sqlplus and connect as sysdba (I'm logged in as the Unix oracle user): sqlplus "sys as sysdba" Oracle asks for a password. Even if I type in the wrong password, I am connected to Oracle. So does Oracle just use the OS authentication (like connect internal did on 8i)? But why is Oracle asking for a password in the first place then? Any ideas? This is 9.0.1 on Sun Solaris. Thanks, Helmut
How to find out free space below High Water Mark in table
Title: How to find out free space below High Water Mark in table Hi! I deleted several thousand rows from a table, but it looks like the table is still taking up the same amount of space in the tablespace. Which it indeed does, because delete does not reset the HWM. Does anybody out there have a script that gives me the free space in a table below the high water mark? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Problem with job
Title: Problem with job Hi! I created a new job in a database: variable jobno number; begin dbms_job.submit (:jobno, 'statspack.snap;', sysdate, 'trunc (sysdate,''HH24'') + trunc ((sysdate - trunc (sysdate, ''HH24''))*96+1)/96'); commit; end; The job was successfully created. But when I try to run the job, I get the following error message: BEGIN dbms_job.run(127); * ERROR at line 1: ORA-12011: execution of 1 jobs failed ORA-06512: at "SYS.DBMS_IJOB", line 405 ORA-06512: at "SYS.DBMS_JOB", line 267 ORA-06512: at line 1 Any idea what's wrong here? This is 8.1.7 on Sun Solaris. Thanks, Helmut
How to backup MTS database with RMAN
Title: How to backup MTS database with RMAN Hi! Since I'm pretty new to RMAN I have a rather basic question: is it possible to backup databases running in multithreaded server mode with RMAN? Or does this have to be dedicated server mode? $ rman target sys/@kpmgi rcvcat rman/x@admserv Recovery Manager: Release 8.1.7.2.0 - Production RMAN-06005: connected to target database: KPMGI (DBID=3995384462) RMAN-06008: connected to recovery catalog database RMAN> register database; RMAN-03022: compiling command: register RMAN-03023: executing command: register RMAN-08006: database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-03026: error recovery releasing channel resources RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-07005: error during channel cleanup RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEUSECURRENT RMAN-03008: error while performing automatic resync of recovery catalog RMAN-07004: unhandled exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT Do I just have to take out mts_dispatchers = "(protocol=TCP)" from the init.ora file? This is 8.1.7.2.0 on Sun Solaris. Thanks, Helmut
Weid exp/imp problem
Title: Weid exp/imp problem
Hi!
I'm experiencing a weird problem here... I'm about to move one user's object from the development box to a test box. The user's rights on both boxes are identical.
What I do is this:
- export user (using exp) from development. Works flawlessly.
- import user into the other box (user setup and tablespaces are identical)
An I get the following errors which doesn't make a lot of sense to me...
...
. . importing table "TABELLEN" 37 rows imported
. . importing table "TABELLEN_ZUORDNUNGEN" 28 rows imported
. . importing table "TMP$TEST" 1 rows imported
. . importing table "TMP_FUNKTIONS_PARAMETER" 0 rows imported
. . importing table "TMP_FUNKTIONS_SPALTEN" 0 rows imported
. . importing table "USEREXIT" 5 rows imported
. . importing table "USEREXIT_TYPE" 3 rows imported
. . importing table "ZYKLUS" 7 rows imported
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_BNGR_FK" FOREIGN"
" KEY ("BNGR_ID") REFERENCES "BENUTZER_GRUPPEN" ("ID") ENABLE NOVALIDATE"
IMP-3: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "BENUTZER_GRUPPEN_ZUORD" ADD CONSTRAINT "BNGRZ_OW_FK" FOREIGN K"
"EY ("OW_ID") REFERENCES "OWNER" ("ID") ENABLE NOVALIDATE"
IMP-3: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
...
Any ideas why this is happening?
This is 8.1.7 on Sun Solaris.
Thanks,
Helmut
Autostart of database and listener on Sun Solaris
Title: Autostart of database and listener on Sun Solaris Hi! Since I'm fairly new to Solaris, I have a rather basic question: how to I enable autostart of the database and the listener when the Solaris box is rebooted? This is 8.1.7 on Solaris 7&8. Thanks, Helmut
Install of two client versions on same box
Title: Install of two client versions on same box Hi! Is it possible to install two client versions (8.1.6 and 8.1.7) on the same Sun Solaris box? The reason for that is that we are using BEA Weblogic, which requires a specific Oracle client version and our application needs another one. Don't ask me about the sense behind it... This is 8.1.6/8.1.7 on Sun Solaris. Thanks, Helmut
Pwer Point Presentation for 9i Real Application Cluster (RAC)
Title: Pwer Point Presentation for 9i Real Application Cluster (RAC) Hi! My company is thinking about having a closer look at 9i Real Application Clusters. Does anybody out there have any white papers or Power Point presentations on that topic? Thanks, Helmut
difference between temporary tablespaces
Title: difference between temporary tablespaces Hi! I'm getting kinda confused concerning the two types of temporary tablespaces: create tablespace NAME temporary... is the "old" way of creating a temporary tablespace for sorting purposes. It only allows for sort segments to be stored there. And it can only be dictionary managed, right? create temporary tablespace NAME tempfile... is the "new" way for it. It can either be dictionary managed or locally managed. But the complete reference says that this tablespace will only be used for temporary tables. Does this tablespace also allow for sort segments? Or do I need both types of tablespaces? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Find DBA users
Title: Find DBA users Hi! Is there a data dictionary view that gives me all users who have geen granted the DBA role? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Move data from Oracle into Access
Title: Move data from Oracle into Access Hi! What would be the easiest way to dump out an Oracle table and move it into Access? (approx. 100,000 rows) No flaming, please... ;) I assume that there is no tool for Access to read Oracle dmp-files, right? Is spooling it out into an ASCII file (blanks or comma-seperated) the only way? I just need a single table (no refs or the like). Any ideas? This is 8.1.7 on Solaris. Thanks, Helmut
how to extract text from LONG field
Title: how to extract text from LONG field Hi! Is there a way that I can extract the source code of a trigger from dba_triggers? The problem is that the information is stored in a LONG colum. SQLWKS> desc dba_triggers Column Name Null? Type -- OWNER VARCHAR2(30) TRIGGER_NAME VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(216) TABLE_OWNER VARCHAR2(30) BASE_OBJECT_TYPE VARCHAR2(16) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) REFERENCING_NAMES VARCHAR2(128) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) ACTION_TYPE VARCHAR2(11) TRIGGER_BODY LONG select table_owner, table_name from dba_triggers where trigger_body like '%T_JOURNAL%'; does not work, because the LONG field can't be searched with LIKE. to_char conversion doesn't work either. Any ideas? This is 8.1.7 on Sun Solaris. Thanks, Helmut
How to read trace file
Title: How to read trace file Hi! A developer handed me a trace file that he has taken from a session. How do I read the output? It looks totally different from the ones that I know (formatted by tkprof). The Performace Tuning Book from Oracle Press doesn't have anything about that trace output in it as well. Could anybody please point me in the right direction? PARSING IN CURSOR #1 len=262 dep=0 uid=46 oct=3 lid=46 tim=1737274602 hv=542987539 ad='8ad3051c' SELECT t0.access_key_fi, t0.box_name, t0.content_fi, t0.menu_id, t0.internal_name, t0.ordinal, t0.page_key, t0.parent_menu_fi, t0.publish_from, t0.publish_priority, t0.publish_to, t0.published_at, t0.short_description, t0.style_fi, t0.target FROM NM_SIT_MENU t0 END OF STMT PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1737274602 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1737274602 FETCH #1:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=24,dep=0,og=4,tim=1737274603 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274603 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274604 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274605 FETCH #1:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274605 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274606 FETCH #1:c=0,e=1,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274607 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274607 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274608 FETCH #1:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274608 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274610 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274615 FETCH #1:c=1,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274620 FETCH #1:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274628 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274629 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274630 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274630 FETCH #1:c=1,e=0,p=0,cr=2,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274631 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274632 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274632 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274633 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274634 FETCH #1:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274635 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274635 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274636 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274637 FETCH #1:c=0,e=1,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274638 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274638 FETCH #1:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274639 FETCH #1:c=0,e=1,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274640 FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=24,dep=0,og=4,tim=1737274640 FETCH #1:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=11,dep=0,og=4,tim=1737274641 XCTEND rlbk=0, rd_only=1 STAT #1 id=1 cnt=755 pid=0 pos=0 obj=26567 op='TABLE ACCESS FULL NM_SIT_MENU ' This is 8.1.7 on Sun Solaris. Thanks, Helmut
session marked for kill
Title: session marked for kill Hi! I got a call from one of our developers and was told that his app was hung on the dev box. So I issued an "alter system kill session" command. It took about 30 seconds then I got the message: session marked for kill. But the connection still showed up in v$session, even after 5 minutes it was still there. Then I decided to shut down the db (which was ok since he was the only developer on the box): immediate didn't work (i.e. it took about 3 mins and nothing happened), so I issued a shutdown abort and restarted the instance. Any idea what was going on? Why did it take so long? Was it all rollback activity? This is 8.1.7 on sun Solaris. Thanks, Helmut
Materialized View over 2 tables
Title: Materialized View over 2 tables Hi! Can I have a materialized view to be refreshed "fast", when it is created over 2 base tables? Fast refreshes work fine as long as the materialized view is based on 1 base table. But if the view is based on more base tables, it only seems to work with a complete refresh. Does anybody have any experience with that problem? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Oracle Installer line mode
Title: Oracle Installer line mode Hi! Is it possible to run an 8.1.7 installation in line mode on Sun Solaris? Or is it mandatory to use the Java installer? Thanks, Helmut
analyze partitioned indexes
Title: analyze partitioned indexes Hi, I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes. What I'm doing is: delete from admin.tb_index_stats where index_owner = '&1'; commit; FOR EACH_ROW IN (SELECT OWNER || '.' || INDEX_NAME as INDEX_NAME FROM DBA_INDEXES WHERE OWNER = '&1') LOOP t_tables(t_tables.COUNT + 1) := EACH_ROW.INDEX_NAME; END LOOP; FOR i IN 1 .. t_tables.COUNT LOOP BEGIN EXECUTE IMMEDIATE 'ANALYZE INDEX ' || t_tables(i) || ' VALIDATE STRUCTURE'; Will this also work for all the partitions in a partitioned index? Or what would be a way to get all the index partitions and analyze them separately? This is 8.1.7 on Sun Solaris. Thanks, Helmut
How to find SQL statement in SQL area
Title: How to find SQL statement in SQL area Hi! How do I find the ENTIRE SQL statement that is dogging my box? select sql_text from v$sqlarea where hash_value=123456 gives me the first part of the SQL statement; but since the statement is pretty long, the sql_text column does not contain the entire statement. Is there a way to retrieve the entire statement? I also know the session ID of the application issuing the statement. This is 8.1.7 on Sun Solaris. Thanks, Helmut
