Fairly Boring News Article on Oracle
http://story.news.yahoo.com/news?tmpl=story&cid=581&ncid=738&e=2&u=/nm/20020409/tc_nm/tech_oracle_dc_7 Cheers, JoJo
RE: Cold NT backups
You may want to look at robocopy.exe from the NT resource kit. mark -Original Message- George A. Sent: Tuesday, April 02, 2002 7:58 AM To: Multiple recipients of list ORACLE-L Group, We are doing a cold backup to a remote server. I am using OCOPY that fails intermittently with the following message: OCOPY - Insufficient disk space on target drive There is more than sufficient space on the drive to hold the file. I filed a TAR and was told that the error message is generic in nature and they could not tell me exactly what is failing. Can anyone PLEASE give me some suggestions. The file is always SYSTEM01.DBF. All the rest of the files copy across without a problem. TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. 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: Mark Keating 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).
RESOLVED : UWIN 3.0 and ORA-12560 error
Never mind... Found out that when variable LOCAL is set to $ORACLE_SID, this error goes away ! Now I can retire for the night :) - Kirti -Original Message- Sent: Monday, April 08, 2002 9:51 PM To: '[EMAIL PROTECTED]' Hello, I am getting this ORA-12560: TNS Protocol Adapter error, while connecting to the local database on my Windoze NT Workstation. This happens when using the ksh environment in UWIN 3.0. I have done all the debugging I can think of. Everything works fine via DOS Command window. I have checked Registry entry and it looked okay to me (ORACLE_SID, ORACLE_HOME etc). My env variables (PATH, ORACLE_SID, ORACLE_HOME etc) are all fine. I can connect via SQL*Net, no problem there. It's just the regular connection under ksh environment that is getting this error. Has anyone seen this error with UWIN 3.0? I have UWIN 2.0 on my home PC and it works fine. I sure can use some help in resolving this ORA-12560 error. Thanks in advance. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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).
UWIN 3.0 and ORA-12560 error
Hello, I am getting this ORA-12560: TNS Protocol Adapter error, while connecting to the local database on my Windoze NT Workstation. This happens when using the ksh environment in UWIN 3.0. I have done all the debugging I can think of. Everything works fine via DOS Command window. I have checked Registry entry and it looked okay to me (ORACLE_SID, ORACLE_HOME etc). My env variables (PATH, ORACLE_SID, ORACLE_HOME etc) are all fine. I can connect via SQL*Net, no problem there. It's just the regular connection under ksh environment that is getting this error. Has anyone seen this error with UWIN 3.0? I have UWIN 2.0 on my home PC and it works fine. I sure can use some help in resolving this ORA-12560 error. Thanks in advance. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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).
Insert append generating redo
Hi, I'm trying the following insert /*+ append */ into t1 as select * from t2; t1 is created with nologging attribute. The insert is not using the hint at all. I can select on t1 (before any commit) which I should not be able to do if the append hint was used. Any ways to get the hing used ? (Oracle 817/NT) TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Decyphering LMT space bitmap
Thanks Jonathan. >I think you'll find that generally the initial amount >of file allocated is 64K (including the two header >blocks) - the fact that you noted the bitmap was in >the "next 6 blocks" suggests you were using an >8K block size. >The actual space allocated at file creation time >is dependent on the file size, block size, and >extent size. I did the tests about 2 years ago and did notice that as you indicated that it depends on file size, block size and extent size and yes it was an 8K block and I did do the test for 16K block size and got the first 2 blocks for the header and then the next 2 for bitmaps >If you have a very small block size, very small extents, >and extremely large files then Oracle may allocate >an extra 64K (and I guess further multiples of 64K >if necessary). I noticed that I got the maximum free extents tracked when I added an extra 64K to the file size. Unfortunately at that time I did not have enough disk space to create areal large file and a table with lots of extents. >I have yet to find out what the typical behaviour is >for a tablespace with a 32K block. I would guess >that the total file header would be 128K rather than >64K - 2 blocks for the general file header, 2 blocks >for the bitmap. Could anyone confirm that, thanks ? I will try and test that to see how Oracle allocates blocks. Thanks once again. Regards, Madhavan See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur 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: Decyphering LMT space bitmap
There's a funny thing about the: | Tail portion of Bitmap Blocks M blocks It doesn't really exist despite Oracle documents to the contrary. Try creating a file with some waste space at the end of it so that the 'Tail' points to the start of the waste space; then increase the file size by one extent. The 'Tail' will apparently move. A potentially important detail for using LMTs (with uniform space management) - the file size should be a multiple of the extent size plus 64K (or in special cases, perhaps 128K is needed) to avoid wasting the best part of one extent at the end of the file. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 April 2002 21:52 |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 Blocks N blocks |Useful file blocks U units (A unit is a number |of blocks) |Tail portion of Bitmap Blocks M blocks | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan 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: 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 to read user dump file
vi. Scott Shafer San Antonio, TX 210-581-6217 > -Original Message- > From: Yahoo [SMTP:[EMAIL PROTECTED]] > Sent: Monday, April 08, 2002 5:24 PM > To: Multiple recipients of list ORACLE-L > Subject: How to read user dump file > > Hi: > Can anyone of you tell me what tool to use in order to get a report > out from user dump file? Many thanks! -- 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: Decyphering LMT space bitmap
I think you'll find that generally the initial amount
of file allocated is 64K (including the two header
blocks) - the fact that you noted the bitmap was in
the "next 6 blocks" suggests you were using an
8K block size.
The actual space allocated at file creation time
is dependent on the file size, block size, and
extent size.
If you have a very small block size, very small extents,
and extremely large files then Oracle may allocate
an extra 64K (and I guess further multiples of 64K
if necessary).
If you create a file that is extremely small, then
Oracle can allocate a single block for the bitmap
and, if the file then grows, further sections of bitmap
may be allocated in the middle of the file.
I have yet to find out what the typical behaviour is
for a tablespace with a 32K block. I would guess
that the total file header would be 128K rather than
64K - 2 blocks for the general file header, 2 blocks
for the bitmap. Could anyone confirm that, thanks ?
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 08 April 2002 22:55
|
|The bitmap itself is stored in 6 blocks after that , blocks 3 thru 8.
Each of the bit tracks four extents represented by an F (hex for 15).
| 0
|
{further details cut}
|
|When I did my test and I had raised the question at that time that
the max data that can be stored is 129024 * 6 (blocks) = 774144
extents and if the extent size for argument is 1K then the max size
the bitmap can track is 774144K or 7M data file. I have not found an
answer yet.
|
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan 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: 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: IOUG conference get together
Joe, > So for those of us going to the IOUG Conf, do we want to plan a get > together? For sure!! As far as I could determine, the following are going to be there (other feel free to add): 1. Joe Testa 2. Gaja Vaidyanatha 3. Kirti Deshpande 4. Rachel Carmichael 5. Ari Kaplan (On the IOUG Board!) 6. Robert Freeman 7. Stephen Karniotis (Focus Area Manager for DBA Track!) 8. John Kanagaraj (yours truly) It looks like there could be some from the other list - I will pull those names together. Some will be presenting, so could I have dates/times/what is being presented so I can put that out too? Please send it to me directly so we don't clutter the list! Joe/someone who knows SD Conv. center - do we have a common meeting place/time? John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: IOUG conference get together
I'm all for it - anyone have a suggestion for a time and location? Brian -Original Message- Sent: Monday, April 08, 2002 5:18 PM To: Multiple recipients of list ORACLE-L So for those of us going to the IOUG Conf, do we want to plan a get together? Throwing this out for ideas, etc. Joe -- 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: Brian McGraw 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: two listeners problem ???]
Check your tnsnames.ora and verify in CONNECT_DATA you have SERVICE_NAME (instead of SID) and the value of SERVICE_NAME=db_name.db_domain. For example, if your db_name=orcl01 and db_domain=world then SERVICE_NAME=orcl01.world. Janet Linsy wrote: > > Hi, > > After I issued lsnrctl start ORACL816, the 816 > listener started, but the status shows "the listener > supports no services." > > STATUS of the LISTENER > > Alias LISTENER816 > Version TNSLSNR for Solaris: Version > 8.1.6.0.0 - Production > Start Date08-APR-2002 13:23:25 > Uptime0 days 0 hr. 7 min. 16 sec > Trace Level off > Security OFF > SNMP OFF > Listener Parameter File > /export/apps/oracle/admin/product/8.1.6/network/admi > listener.ora > Listener Log File > /export/apps/oracle/admin/product/8.1.6/network/log/ > stener816.log > The listener supports no services > The command completed successfully > > I shutdown and restart the instance, it still cannot > be registered in the listener. > > As a result, when I tried to log in, I got: > ORA-12514: TNS:listener could not resolve SERVICE_NAME > given in connect descriptor > > Any suggestion? Much appreciated! > > Janet > > --- Oracle User <[EMAIL PROTECTED]> wrote: > > > > Did you try starting them as start . > > In your case, it should > > be > > > > start listener816/listener817 > > > > Also try giving 1525 instead of 1522. > > You can also try to enable logging so you can find > > out exactly what went > > wrong. Did you get any errors while trying to start > > the listener? > > > > Hope this helps. If not get back to me and I shall > > help you. > > > > Rgds > > Raj > > > > >From: Janet Linsy <[EMAIL PROTECTED]> > > >To: "LazyDBA.com Discussion" > > <[EMAIL PROTECTED]> > > >Subject: two listeners problem ??? > > >Date: Fri, 5 Apr 2002 22:09:15 -0800 (PST) > > > > > >Hi, > > > > > >I have two databases ORCL817 and ORCL816 in version > > >817, 816 on the same machine Sun 5.7. I'd like to > > >configure seperate listener for each database. I > > used > > >different port, listener name (1521 and LISTENER817 > > >for ORCL817 , 1522 and LISTENER816 for ORCL816), > > but > > >somehow the listener on 1522 cannot be started. > > >Why??? > > > > > >Here is the listener.ora and tnsnames.ora for both > > >database: > > > > > >listener.ora for 816 > > >= > > >LISTENER816 = > > > (DESCRIPTION_LIST = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1522)) > > > ) > > > ) > > > (DESCRIPTION = > > > (PROTOCOL_STACK = > > > (PRESENTATION = GIOP) > > > (SESSION = RAW) > > > ) > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 2481)) > > > ) > > > ) > > > > > >SID_LIST_LISTENER = > > > (SID_LIST = > > > (SID_DESC = > > > (GLOBAL_DBNAME = ORCL816) > > > (ORACLE_HOME = > > >/export/apps/oracle/admin/product/8.1.6) > > > (SID_NAME = ORCL816) > > > ) > > > > > >tnsnames.ora for 816 > > >= > > >ORCL816 = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1522)) > > > ) > > > (CONNECT_DATA = > > > (SERVICE_NAME = ORCL816) > > > ) > > > ) > > > > > >listener.ora for 817 > > >= > > >LISTENER817 = > > > (DESCRIPTION_LIST = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1521)) > > > ) > > > ) > > > (DESCRIPTION = > > > (PROTOCOL_STACK = > > > (PRESENTATION = GIOP) > > > (SESSION = RAW) > > > ) > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 2481)) > > > ) > > > ) > > > > > >SID_LIST_LISTENER = > > > (SID_LIST = > > > (SID_DESC = > > > (GLOBAL_DBNAME = ORCL817) > > > (ORACLE_HOME = > > >/export/apps/oracle/admin/product/8.1.7) > > > (SID_NAME = ORCL817) > > > ) > > > ) > > > > > >tnsnames.ora > > >= > > >ORCL817 = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1521)) > > > ) > > > (CONNECT_DATA = > > > (SERVICE_NAME = ORCL817) > > > ) > > > ) > > > > > >Anyone knows what went wrong? Thank you! > > > > > >Janet > > > > > >__ > > >Do You Yahoo!? > > >Yahoo! Tax Center - online filing with TurboTax > > >http://taxes.yahoo.com/ > > > > > > > > >Oracle documentation is here: > > >http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > > >To unsubscribe: send a blank email to > > [EMAIL PROTECTED] > > >To subscribe: send a blank email to > > [EMAIL PROTECTED] > > >Visit the
IOUG conference get together
So for those of us going to the IOUG Conf, do we want to plan a get together? Throwing this out for ideas, etc. Joe -- 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).
Re: Decyphering LMT space bitmap
That looks the way it should be. You have 30 extents in the tablespace, and 30 bits near the start of the bitmap are set. You'll have to create a bitmap tablespace and use extents in it one at a time, but quite literally from the block 3 onwards the bitmap is one bit per extent - it's just a question of working out the byte and word swapping in the dump. Temporary tablespaces are different, though, as the 'bitmap' is actually 2 bytes per extent because temporary extents 'belong' to instances and each 'bit' needs to have the current owning instance associated with it. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 April 2002 20:08 |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, First: 30, Free: 34 | |Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 |frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap |File Space Bitmap Block: |BitMap Control: |RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 |FF3F | | |... all zeros | |Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 |frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap |File Space Bitmap Block: |BitMap Control: |RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 | | | |... all zeros | | |FWIW: | |SQL> select count (*) from dba_extents where file_id = 2; | | COUNT(*) |-- |30 | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan 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: 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).
Test Message - Please ignore
--- Madhavan Amruthur See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur 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 read user dump file
Hi: Can anyone of you tell me what tool to use in order to get a report out from user dump file? Many thanks!
Re: Decyphering LMT space bitmap
Hi Jeremiah, >How is the LMT bitmap organized? I did some research about the bitmap structure for an LMT tablespace a while ago. The first 2 blocks are Block 1 - For File Header Block 2 - For File Space Bitmap Header The bitmap itself is stored in 6 blocks after that , blocks 3 thru 8. Each of the bit tracks four extents represented by an F (hex for 15). 0 For eg: if you create a table with 1 extent then there will be a 1 which 1 2 to the power of 0 = 1 and if there 2 extents then its 2^0 + 2^1 = 3 for 4 extents its 2^0+2^1+2^2+2^3 = 15 =F so the values will be either 0,1,3,7,or F for each bit. In your case FF3F 0 there are 7 F's = 7*4 = 28 extents + 2 (represented by the 3) and = 30. The rest are 0's because there is no data yet as there are only 30 extents. Each of these blocks 129024 extents. When I did my test and I had raised the question at that time that the max data that can be stored is 129024 * 6 (blocks) = 774144 extents and if the extent size for argument is 1K then the max size the bitmap can track is 774144K or 7M data file. I have not found an answer yet. You will find that there are about 504 lines of 64 bits each. I have a detailed record of the test if you are interested that I can mail to you. Hope this helps. Regards, --- Madhavan Amruthur See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur 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: Foreign Objects in the System Tablespace.
I was not referring to you personally Paul. I obviously have no idea what you set your pctincrease to. -Original Message- Paul R. Sent: Monday, April 08, 2002 12:22 PM To: Multiple recipients of list ORACLE-L Hello, Well, I'm puzzled. I have pctincrease set to 0, so I guess I'm not 'scared'. And I see nothing wrong in having DBSNMP under SYSTEM tblsp, along with SYS and SYSTEM. DBSNMP is hardly a major growth user. I have oversight of all dev/test/prod databases, so I know what's going on day to day. And the expression that you were prob. meant to use was 'you can not teach an old dog new tricks'. I certainly qualify for that, as I have been using computers (programming, proj. mgt., dba) since 1960, long enough to know better than to slight people for telling someone how they approach a situation. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 12:28 PM To: Multiple recipients of list ORACLE-L I am with Lisa. But then again there are folks out there who are still scared to change the pctincrease to 0 on the SYSTEM tablespace. I no longer care unless its on one of my databases. Sometimes you just can't teach a dog new tricks:-( -Original Message- Sent: Monday, April 08, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Paul, what's your reasoning behind this? I have always changed the default and temp tablespace of everything except SYS to something else. I was under the impression the SYSTEM tablespace should only hold the data dictionary. Lisa Koivu Oracle Database Tank Rash. Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Sherman, Paul R. [SMTP:[EMAIL PROTECTED]] > Sent: Monday, April 08, 2002 11:29 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Foreign Objects in the System Tablespace. > > Jay, > > I always set up my production databases having SYSTEM, SYS, and DBSNMP > with > default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, > OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for > temp > tblsp). I have never had any problems doing it this way. > > Thank you, > > Paul Sherman > DBA > voice - 781-501-4143 (office) > fax- 781-278-8341 (office) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Monday, April 08, 2002 10:09 AM > To: Multiple recipients of list ORACLE-L > > > I am trying to determine what Oracle "officially" considers foreign > objects in the SYSTEM tablespace. If you check out Note 122669.1, section > 7.1, Oracle recommends a query to find foreign objects in your system > tablespace. This query will report such users as: > > AURORA$JIS$UTILITY$ > CTXSYS > MDSYS > ORDSYS > OSE$HTTP$ADMIN > OUTLN > > It is part of our normal procedures to setup a TOOLS tablespace, and set > this as the default tablespace for the user SYSTEM. Objects such as > SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed > around MetaLink and posted in one of the forums, but I'm not really > getting > any concrete answers as to which users should be permitted to have objects > in the SYSTEM tablespace. I do know that it is OK to move OUTLN to > another > tablespace. > Comments would be appreciated. > > Thanks, > Jay > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Hostetter > 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: Sherman, Paul R. > 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: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: two listeners problem ???
Check your tnsnames.ora and verify in CONNECT_DATA you have SERVICE_NAME (instead of SID) and the value of SERVICE_NAME=db_name.db_domain. For example, if your db_name=orcl01 and db_domain=world then SERVICE_NAME=orcl01.world. Janet Linsy wrote: > > Hi, > > After I issued lsnrctl start ORACL816, the 816 > listener started, but the status shows "the listener > supports no services." > > STATUS of the LISTENER > > Alias LISTENER816 > Version TNSLSNR for Solaris: Version > 8.1.6.0.0 - Production > Start Date08-APR-2002 13:23:25 > Uptime0 days 0 hr. 7 min. 16 sec > Trace Level off > Security OFF > SNMP OFF > Listener Parameter File > /export/apps/oracle/admin/product/8.1.6/network/admi > listener.ora > Listener Log File > /export/apps/oracle/admin/product/8.1.6/network/log/ > stener816.log > The listener supports no services > The command completed successfully > > I shutdown and restart the instance, it still cannot > be registered in the listener. > > As a result, when I tried to log in, I got: > ORA-12514: TNS:listener could not resolve SERVICE_NAME > given in connect descriptor > > Any suggestion? Much appreciated! > > Janet > > --- Oracle User <[EMAIL PROTECTED]> wrote: > > > > Did you try starting them as start . > > In your case, it should > > be > > > > start listener816/listener817 > > > > Also try giving 1525 instead of 1522. > > You can also try to enable logging so you can find > > out exactly what went > > wrong. Did you get any errors while trying to start > > the listener? > > > > Hope this helps. If not get back to me and I shall > > help you. > > > > Rgds > > Raj > > > > >From: Janet Linsy <[EMAIL PROTECTED]> > > >To: "LazyDBA.com Discussion" > > <[EMAIL PROTECTED]> > > >Subject: two listeners problem ??? > > >Date: Fri, 5 Apr 2002 22:09:15 -0800 (PST) > > > > > >Hi, > > > > > >I have two databases ORCL817 and ORCL816 in version > > >817, 816 on the same machine Sun 5.7. I'd like to > > >configure seperate listener for each database. I > > used > > >different port, listener name (1521 and LISTENER817 > > >for ORCL817 , 1522 and LISTENER816 for ORCL816), > > but > > >somehow the listener on 1522 cannot be started. > > >Why??? > > > > > >Here is the listener.ora and tnsnames.ora for both > > >database: > > > > > >listener.ora for 816 > > >= > > >LISTENER816 = > > > (DESCRIPTION_LIST = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1522)) > > > ) > > > ) > > > (DESCRIPTION = > > > (PROTOCOL_STACK = > > > (PRESENTATION = GIOP) > > > (SESSION = RAW) > > > ) > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 2481)) > > > ) > > > ) > > > > > >SID_LIST_LISTENER = > > > (SID_LIST = > > > (SID_DESC = > > > (GLOBAL_DBNAME = ORCL816) > > > (ORACLE_HOME = > > >/export/apps/oracle/admin/product/8.1.6) > > > (SID_NAME = ORCL816) > > > ) > > > > > >tnsnames.ora for 816 > > >= > > >ORCL816 = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1522)) > > > ) > > > (CONNECT_DATA = > > > (SERVICE_NAME = ORCL816) > > > ) > > > ) > > > > > >listener.ora for 817 > > >= > > >LISTENER817 = > > > (DESCRIPTION_LIST = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1521)) > > > ) > > > ) > > > (DESCRIPTION = > > > (PROTOCOL_STACK = > > > (PRESENTATION = GIOP) > > > (SESSION = RAW) > > > ) > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 2481)) > > > ) > > > ) > > > > > >SID_LIST_LISTENER = > > > (SID_LIST = > > > (SID_DESC = > > > (GLOBAL_DBNAME = ORCL817) > > > (ORACLE_HOME = > > >/export/apps/oracle/admin/product/8.1.7) > > > (SID_NAME = ORCL817) > > > ) > > > ) > > > > > >tnsnames.ora > > >= > > >ORCL817 = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = > > >gatech-denver1)(PORT = 1521)) > > > ) > > > (CONNECT_DATA = > > > (SERVICE_NAME = ORCL817) > > > ) > > > ) > > > > > >Anyone knows what went wrong? Thank you! > > > > > >Janet > > > > > >__ > > >Do You Yahoo!? > > >Yahoo! Tax Center - online filing with TurboTax > > >http://taxes.yahoo.com/ > > > > > > > > >Oracle documentation is here: > > >http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > > >To unsubscribe: send a blank email to > > [EMAIL PROTECTED] > > >To subscribe: send a blank email to > > [EMAIL PROTECTED] > > >Visit the l
RE: Using OID
OiD! After several weeks of pain, here's what I've learned:
1) Create your database(s) manually. The GUI creates the traditional
"the least we need to get it going without a real DBA" database. Note
159031.1 on Metalink will guide you thru the basic create.
2) If you intend on using replication (a good idea), study up on Oracle
ASR, but realize that OiD doesn't use ASR in the traditional way, at least
according to Oracle Support. In other words, if OiD has problems
replicating, it's an OiD problem and not an ASR problem, as far as Oracle
Support is concerned.
3) According to Oracle Support, you cannot use hot backups as a
reliable means of backup/recovery for OiD in a replicated environment.
While I agree with their reasoning in theory, I believe that a good DBA (and
me, too!) can still use it, but with care on the recovery. For more info,
see the OiD Admin Guide.
4) Speaking of the OiD Admin Guide: Read it, learn it, study it, know
it. All 688 pages of it. The concepts in there are KEY! The one that
burned me is the concept of a Remote Definition Site (RDS). You're
"primary" server is the MDS (Master Definition Site). We tried to treat our
second "backup" OiD server as a read-only. Don't do it. Treat all other
replication nodes as RDSs. It will save you tons of headaches.
5) Why isn't "RDS" mentioned specifically in the OiD Admin Guide?
Because of a lack of coherent documentation. Lookup all the articles you
can on Metalink regarding OiD. Some haven't been updated for v3, but
they're still good.
6) Do not use any version below 3.0.1 of OiD, which requires (and comes
with) Oracle 9i. We had too many bugs, especially in the OiD Administrator
program with v2.x.
7) Use Linux. There are some nasty little gotchas in NT/2000 that I
really despise (keep reading).
8) Only use an Oracle Certified platform and version of the OS. Oracle
Support will have a cow udderwise.
9+) Use scripts to startup and shutdown OiD. If you try and do it
manually and shut the oidmon down before the LDAP and replication daemons,
the daemons won't shutdown. On Linux, you can restart the oidmon, and the
daemons should shutdown, but on NT/2000 they will hang there forever until
you re-freaking-boot. Who writes this crap? There's no rebooting on
Linux/Unix! I haven't tried OiD on Unix (I think OiD v3's available on
HP/Solaris), so I can't say what'll happen there. As an aside, many OiD
tools are Unix/Linux shell script, which are not directly available on
Windohs. Just another reason to avoid Windows for OiD.
10) I just started to test moving from ONames to OiD. Apparently
there's no way to create the "OracleContext" LDAP tree manually, so you've
got to use the Oracle Net Config Assist ("netca"). I'm trying to determine
if the "update" it does to the OiD DBs schwanzes up the rest of OiD first
before continuing.
I intend to make my creation of a replicated OiD setup on RedHat 7.1
available on my website, but I just haven't had the time yet, and my PC and
Alpha/Linux box are sitting on a concrete slab at home while I redo my
basement. :(
HTH! GL! I'll let you know when I get that page up.:)
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
> -Original Message-
> From: Yechiel Adar [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, April 07, 2002 11:18 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Using OID
>
>
> Hello list
>
> We intend to implement OID as replacement for tnsnames (at first).
>
> Pit falls, Real time experience etc...???
>
> Yechiel Adar, Mehish.
>
--
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).
RE: two listeners problem ???
Why not post the latest of your listener.ora file. It should be quite a bit different then the original that made it to the list. -Original Message- Sent: Monday, April 08, 2002 1:33 PM To: Multiple recipients of list ORACLE-L Hi, After I issued lsnrctl start ORACL816, the 816 listener started, but the status shows "the listener supports no services." STATUS of the LISTENER Alias LISTENER816 Version TNSLSNR for Solaris: Version 8.1.6.0.0 - Production Start Date08-APR-2002 13:23:25 Uptime0 days 0 hr. 7 min. 16 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /export/apps/oracle/admin/product/8.1.6/network/admi listener.ora Listener Log File /export/apps/oracle/admin/product/8.1.6/network/log/ stener816.log The listener supports no services The command completed successfully I shutdown and restart the instance, it still cannot be registered in the listener. As a result, when I tried to log in, I got: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor Any suggestion? Much appreciated! Janet --- Oracle User <[EMAIL PROTECTED]> wrote: > > Did you try starting them as start . > In your case, it should > be > > start listener816/listener817 > > Also try giving 1525 instead of 1522. > You can also try to enable logging so you can find > out exactly what went > wrong. Did you get any errors while trying to start > the listener? > > Hope this helps. If not get back to me and I shall > help you. > > Rgds > Raj > > >From: Janet Linsy <[EMAIL PROTECTED]> > >To: "LazyDBA.com Discussion" > <[EMAIL PROTECTED]> > >Subject: two listeners problem ??? > >Date: Fri, 5 Apr 2002 22:09:15 -0800 (PST) > > > >Hi, > > > >I have two databases ORCL817 and ORCL816 in version > >817, 816 on the same machine Sun 5.7. I'd like to > >configure seperate listener for each database. I > used > >different port, listener name (1521 and LISTENER817 > >for ORCL817 , 1522 and LISTENER816 for ORCL816), > but > >somehow the listener on 1522 cannot be started. > >Why??? > > > >Here is the listener.ora and tnsnames.ora for both > >database: > > > >listener.ora for 816 > >= > >LISTENER816 = > > (DESCRIPTION_LIST = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1522)) > > ) > > ) > > (DESCRIPTION = > > (PROTOCOL_STACK = > > (PRESENTATION = GIOP) > > (SESSION = RAW) > > ) > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 2481)) > > ) > > ) > > > >SID_LIST_LISTENER = > > (SID_LIST = > > (SID_DESC = > > (GLOBAL_DBNAME = ORCL816) > > (ORACLE_HOME = > >/export/apps/oracle/admin/product/8.1.6) > > (SID_NAME = ORCL816) > > ) > > > >tnsnames.ora for 816 > >= > >ORCL816 = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1522)) > > ) > > (CONNECT_DATA = > > (SERVICE_NAME = ORCL816) > > ) > > ) > > > >listener.ora for 817 > >= > >LISTENER817 = > > (DESCRIPTION_LIST = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1521)) > > ) > > ) > > (DESCRIPTION = > > (PROTOCOL_STACK = > > (PRESENTATION = GIOP) > > (SESSION = RAW) > > ) > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 2481)) > > ) > > ) > > > >SID_LIST_LISTENER = > > (SID_LIST = > > (SID_DESC = > > (GLOBAL_DBNAME = ORCL817) > > (ORACLE_HOME = > >/export/apps/oracle/admin/product/8.1.7) > > (SID_NAME = ORCL817) > > ) > > ) > > > >tnsnames.ora > >= > >ORCL817 = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1521)) > > ) > > (CONNECT_DATA = > > (SERVICE_NAME = ORCL817) > > ) > > ) > > > >Anyone knows what went wrong? Thank you! > > > >Janet > > > >__ > >Do You Yahoo!? > >Yahoo! Tax Center - online filing with TurboTax > >http://taxes.yahoo.com/ > > > > > >Oracle documentation is here: > >http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > >To unsubscribe: send a blank email to > [EMAIL PROTECTED] > >To subscribe: send a blank email to > [EMAIL PROTECTED] > >Visit the list archive: > http://www.LAZYDBA.com/odbareadmail.pl > >Tell yer mates about http://www.farAwayJobs.com > >By using this list you agree to these > >terms:http://www.lazydba.com/legal.html > > > > > > > _ > Get your FREE download of MSN Explorer at > http://explorer.msn.com/intl.a
Re: Re[2]: SQL Tuning - How to avoid TOCHAR function against a date
Bob, Thanks for your reply. I am testing this afternoon. Cherie Robert Eskridge To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re[2]: SQL Tuning - How to avoid TOCHAR function against a Sent by: date [EMAIL PROTECTED] om 04/08/02 02:44 PM Please respond to ORACLE-L How about something like: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1)+86399/86400; It's not the prettiest thing in the world, but it keeps the use of the index on ORACLE_DATE and an adjacent comment that there are 86400 seconds in the day should make it readable enough. -rje R> I don't think you can do it.. I mean, you could change it to trunc the R> oracle_date field (that eliminates the minutes) and then do a to_date R> of :b1 but you will still be operating on the oracle_date field. R> Okay, I HATE to suggest this, but since the table is small: R> add another field to the table oracle_date_2 as a date field. Update R> the table set oracle_date_2=trunc(oracle_date) R> add a trigger to fill in oracle_date_2 when you insert a row or update R> the oracle_date column R> create an index on oracle_date_2 and change the query to use that R> column R> --- [EMAIL PROTECTED] wrote: >> >> 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 -- Autho
RE: SQL Tuning - How to avoid TOCHAR function against a date
Tom, It is probably too late for this original design but it is not too late for a new data warehouse that is in development. Jared has made a recommendation for better date columns that may help eliminate these problems. I have forwarded that table design on to the application owner. Thanks for your reply. Cherie "Mercadante, Thomas F" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <[EMAIL PROTECTED] cc: ate.ny.us> Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date Sent by: [EMAIL PROTECTED] 04/08/02 02:35 PM Please respond to ORACLE-L let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: > > 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
RE: RMAN madness !!
Set the environment variables for your shell. You don't have to change your database
settings.
e.g. (in ksh)
NLS_DATE_FORMAT=MMDDHH24MISS
export NLS_DATE_FORMAT
NLS_LANG=AMERICAN_AMERICA.WE8DEC
export NLS_LANG
Jay
>>> "SARKAR, Samir" <[EMAIL PROTECTED]> 04/08/02 01:44PM >>>
Jay,
Thanks so much for ur help. Could u please tell me whether I shall have to
set the NLS_DATE_FORMAT and NLS_LANG in the parameter file of the auxiliary
db alone or on
the target db (the main prod db) as well ???
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 18:28
To: [EMAIL PROTECTED]; SARKAR, Samir
You need to change this line
set until time 'to_date(''20-MAR-2002
20:00:00'',''DD-MON-HH24:MI:SS'')';
to
set until time '2002032020';
This is bug 1887009. This isn't fixed until 9.0.2.
You must set
NLS_DATE_FORMAT=MMDDHH24MISS (or your favorite date format)
and
NLS_LANG= (whatever your nls lang is for the database)
I learned the hard way, that if you don't set NLS_LANG, then NLS_DATE_FORMAT
is ignored (at least in 8.1.7 on Tru64).
Don't forget to shutdown your auxiliary db, delete it's files, then start it
up again, NOMOUNT.
Jay
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
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: Decyphering LMT space bitmap
Jeremiah, Everything looks normal. Block #3 shows the first couple bytes as "FF3F000...". When you decipher "FF3F", you will see 30 (out of a possible 32) bits in those 4 bytes (a.k.a. 8 "nibbles") set to "1" instead of "0", indicating that those are used extents. Your COUNT(*) on DBA_SEGMENTS confirms exactly that number... Looks pretty straightforward. Now, is your LMT using "UNIFORM SIZE" or "AUTOALLOCATE" (i.e. default)? Thanks! -Tim - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 08, 2002 1:44 PM > 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, First: 30, Free: 34 > > Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 > frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap > File Space Bitmap Block: > BitMap Control: > RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 > FF3F > > > .. all zeros > > Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 > frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap > File Space Bitmap Block: > BitMap Control: > RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 > > > > .. all zeros > > > FWIW: > > SQL> select count (*) from dba_extents where file_id = 2; > > COUNT(*) > -- > 30 > > SQL> select extent_management from dba_data_files df, dba_tablespaces > ts where df.tablespace_name = ts.tablespace_name and file_id = 2; > > EXTENT_MAN > -- > LOCAL > > > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jeremiah Wilton > 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: Tim Gorman 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: Decyphering LMT space bitmap
Jeremiah, Let me explain before guru X$GOPAL woke up. I guess time is midnight in India. Here are the some lines before your dump: Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Let's convert your HEX bitmap vectors to binary. It's 0xFF3F in your case. In other words, it consists of 4 bytes. They are 0xFF, 0xFF, 0xFF, 0x3F. The binary equivalents: , , , 0011 . To read this bitmap, we should take the least significant bit the first for each byte pair: The new bitmap will become: , , , 1100 Now, Each '1' represents used extent, each '0' represents free extent. If We look at the position of first '0', you will see that it's 31th position. Note that this is one more than the First value in the dump given above. Because Oracle starts looking for free space from the First value in the dump. You had found 30 extents in dba_extents. Now, we saw it in the block dump too. There is no problem. btw, I had a free product named iOraDumpReader at http://www.unal-bilisim.com/products/ioradumpreader/ioradumpreader.html . It interepretes almost all block dumps. But, since there is no enough hit to this page, I'm not currently working on it. regards... Jeremiah Wilton wrote: 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, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL> select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL> select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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). -- Danisment Gazi Unal http://www.unal-bilisim.com
RE: Urgent --- Locking problem
Hello List, I am seeing Locks and the OS process is SNP process , I have to run the same job which will refresh the MVs. I am stuck due to the locks on the database , when I have tried to kill the session , it says me its is Marked for kill. Can anybody suggest me what to do ??? Its one kind of urgent Thanks Madhu -Original Message- Sent: Monday, April 08, 2002 3:23 PM To: Multiple recipients of list ORACLE-L hello All, I tried to kill a session and now it is showing me as marked as "killed". But It is still holding the Locks. And I need to rerun the same . Its holding a lock type of 'JI' in exclusive mode . Can anybody suggest me what to do now ??? PS: Its a job which refreshes the Materialized views , so it will be using the SNP process Thanks, Madhu V Reddy Database Support Services (952) 324-0392 ( work ) -- 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). -- 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).
RE: SQL*Loader
I used this string: "to_date(rtrim(ltrim(:DateTested,''),''),'mm/dd/ hh:mi:ss pm')" TESTCASESTATUSID TESTCASEID USERID columns continue... -- -- 858533944 741617974 808202803 858534200 741945654 808202803 858534456 741356086 808202803 858534712 741421622 808202803 858534968 741487158 808202803 5 rows went in, but this is what I got! Any ideas? thanks, David. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mercadante, > Thomas F > Sent: Monday, April 08, 2002 12:57 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL*Loader > > > Dave, > > I bet you the single quotes around the data string are messing > you up. You > could try the following: > > DATETESTED char "rtrim(ltrim(to_date(:DateTested,'mm/dd/ hh:mi:ss > pm'),),)", > > Hope this helps. > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Monday, April 08, 2002 12:16 PM > 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: Mercadante, Thomas F > 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: 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).
Re: two listeners problem ???
Hi, After I issued lsnrctl start ORACL816, the 816 listener started, but the status shows "the listener supports no services." STATUS of the LISTENER Alias LISTENER816 Version TNSLSNR for Solaris: Version 8.1.6.0.0 - Production Start Date08-APR-2002 13:23:25 Uptime0 days 0 hr. 7 min. 16 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /export/apps/oracle/admin/product/8.1.6/network/admi listener.ora Listener Log File /export/apps/oracle/admin/product/8.1.6/network/log/ stener816.log The listener supports no services The command completed successfully I shutdown and restart the instance, it still cannot be registered in the listener. As a result, when I tried to log in, I got: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor Any suggestion? Much appreciated! Janet --- Oracle User <[EMAIL PROTECTED]> wrote: > > Did you try starting them as start . > In your case, it should > be > > start listener816/listener817 > > Also try giving 1525 instead of 1522. > You can also try to enable logging so you can find > out exactly what went > wrong. Did you get any errors while trying to start > the listener? > > Hope this helps. If not get back to me and I shall > help you. > > Rgds > Raj > > >From: Janet Linsy <[EMAIL PROTECTED]> > >To: "LazyDBA.com Discussion" > <[EMAIL PROTECTED]> > >Subject: two listeners problem ??? > >Date: Fri, 5 Apr 2002 22:09:15 -0800 (PST) > > > >Hi, > > > >I have two databases ORCL817 and ORCL816 in version > >817, 816 on the same machine Sun 5.7. I'd like to > >configure seperate listener for each database. I > used > >different port, listener name (1521 and LISTENER817 > >for ORCL817 , 1522 and LISTENER816 for ORCL816), > but > >somehow the listener on 1522 cannot be started. > >Why??? > > > >Here is the listener.ora and tnsnames.ora for both > >database: > > > >listener.ora for 816 > >= > >LISTENER816 = > > (DESCRIPTION_LIST = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1522)) > > ) > > ) > > (DESCRIPTION = > > (PROTOCOL_STACK = > > (PRESENTATION = GIOP) > > (SESSION = RAW) > > ) > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 2481)) > > ) > > ) > > > >SID_LIST_LISTENER = > > (SID_LIST = > > (SID_DESC = > > (GLOBAL_DBNAME = ORCL816) > > (ORACLE_HOME = > >/export/apps/oracle/admin/product/8.1.6) > > (SID_NAME = ORCL816) > > ) > > > >tnsnames.ora for 816 > >= > >ORCL816 = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1522)) > > ) > > (CONNECT_DATA = > > (SERVICE_NAME = ORCL816) > > ) > > ) > > > >listener.ora for 817 > >= > >LISTENER817 = > > (DESCRIPTION_LIST = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1521)) > > ) > > ) > > (DESCRIPTION = > > (PROTOCOL_STACK = > > (PRESENTATION = GIOP) > > (SESSION = RAW) > > ) > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 2481)) > > ) > > ) > > > >SID_LIST_LISTENER = > > (SID_LIST = > > (SID_DESC = > > (GLOBAL_DBNAME = ORCL817) > > (ORACLE_HOME = > >/export/apps/oracle/admin/product/8.1.7) > > (SID_NAME = ORCL817) > > ) > > ) > > > >tnsnames.ora > >= > >ORCL817 = > > (DESCRIPTION = > > (ADDRESS_LIST = > > (ADDRESS = (PROTOCOL = TCP)(HOST = > >gatech-denver1)(PORT = 1521)) > > ) > > (CONNECT_DATA = > > (SERVICE_NAME = ORCL817) > > ) > > ) > > > >Anyone knows what went wrong? Thank you! > > > >Janet > > > >__ > >Do You Yahoo!? > >Yahoo! Tax Center - online filing with TurboTax > >http://taxes.yahoo.com/ > > > > > >Oracle documentation is here: > >http://tahiti.oracle.com/pls/tahiti/tahiti.homepage > >To unsubscribe: send a blank email to > [EMAIL PROTECTED] > >To subscribe: send a blank email to > [EMAIL PROTECTED] > >Visit the list archive: > http://www.LAZYDBA.com/odbareadmail.pl > >Tell yer mates about http://www.farAwayJobs.com > >By using this list you agree to these > >terms:http://www.lazydba.com/legal.html > > > > > > > _ > Get your FREE download of MSN Explorer at > http://explorer.msn.com/intl.asp. > __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy IN
Urgent --- Locking problem
hello All, I tried to kill a session and now it is showing me as marked as "killed". But It is still holding the Locks. And I need to rerun the same . Its holding a lock type of 'JI' in exclusive mode . Can anybody suggest me what to do now ??? PS: Its a job which refreshes the Materialized views , so it will be using the SNP process Thanks, Madhu V Reddy Database Support Services (952) 324-0392 ( work ) -- 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).
RE: Decyphering LMT space bitmap
>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, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL> select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL> select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Decyphering LMT space bitmap
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, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL> select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL> select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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 --
RE: SQL Tuning - How to avoid TOCHAR function against a date
Tom, I realize that there would not be an index but I was trying to eliminate some overhead by using the TRUNC function as compaired to the to_char for the fields. Cherie, If the table is not to large how about pinning it to save on disk reads? Ron ROR mª¿ªm >>> [EMAIL PROTECTED] 04/08/02 03:35PM >>> Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm >>> [EMAIL PROTECTED] 04/08/02 01:56PM >>> 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: Ron Rogers 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-Ma il 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: Mercadante, Thomas F 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: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Die
RE: Oracle Replication - is it "on" by default?
Simple snapshot replication (provided by materialized views or mviews) is enabled when you use the Enterprise Edition of the database at any time as long as job_queue_processes is > 0, and, of course, you have defined some mviews. This is one way replication. If you want 2 way (advanced, multi-master) replication, then in 8i you will need to run catrep.sql after running catalog and catproc. In 9i, catrep is run as a part of catproc, so with 9i you will also have advanced replication installed by default. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, April 08, 2002 11:25 AM To: Multiple recipients of list ORACLE-L Paul - The only book I've found specific to Oracle replication is: Oracle Distributed Systems by Charles Dye. It is pretty good. Replication isn't the easiest thing to learn. The fundamental questions to ask are: is your application designed to work with replication? Are you just trying to replicate for reporting purposes? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 8:13 AM To: Multiple recipients of list ORACLE-L Hi folks, we've never identified any requirement here for using any kind of replication. Consequently I know nothing whatsoever about Oracle Replication. Now I've been asked whether or not we use Oracle Replication and, if so, whether it can be disabled. So can anyone tell me whether Replication is a feature automatically included in Oracle 8i Enterprise Edition? And is there an easy way of telling whether or not it's "on"? If it's "on", can it be turned "off" (if that's a meaningful question!), and if so, how? I've tried briefly RTFMing, but although the manuals contain a wealth of info about how to use Replication, I can see nothing about how to tell whether it's active or not, and how to switch it on or off. Any pointers, please, anyone? Paul Vincent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Vincent 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: 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: 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).
RE: Foreign Objects in the System Tablespace.
Hello, Well, if that's the case, then, going forward, I suppose it's ok to keep SYSTEM objects in TOOLS, but it sure seems strange. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 2:43 PM To: Multiple recipients of list ORACLE-L If you let the Assistant create scripts to create the database, you will find that Oracle now automatically changes SYSTEM's default tablespace to TOOLS. The ONLY account that should have a default tablespace of SYSTEM is SYS. Rachel --- "Sherman, Paul R." <[EMAIL PROTECTED]> wrote: > Jay, > > I always set up my production databases having SYSTEM, SYS, and > DBSNMP with > default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like > ORACLE, > OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP > for temp > tblsp). I have never had any problems doing it this way. > > Thank you, > > Paul Sherman > DBA > voice - 781-501-4143 (office) > fax- 781-278-8341 (office) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Monday, April 08, 2002 10:09 AM > To: Multiple recipients of list ORACLE-L > > > I am trying to determine what Oracle "officially" considers foreign > objects in the SYSTEM tablespace. If you check out Note 122669.1, > section > 7.1, Oracle recommends a query to find foreign objects in your system > tablespace. This query will report such users as: > > AURORA$JIS$UTILITY$ > CTXSYS > MDSYS > ORDSYS > OSE$HTTP$ADMIN > OUTLN > > It is part of our normal procedures to setup a TOOLS tablespace, > and set > this as the default tablespace for the user SYSTEM. Objects such as > SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've > browsed > around MetaLink and posted in one of the forums, but I'm not really > getting > any concrete answers as to which users should be permitted to have > objects > in the SYSTEM tablespace. I do know that it is OK to move OUTLN to > another > tablespace. > Comments would be appreciated. > > Thanks, > Jay > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Hostetter > 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: Sherman, Paul R. > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 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: Sherman, Paul R. 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 suppose if you wanted to collect statistics about hourly usage, then the minutes info would be necessary but then, most people don't think about how they really want to use the date when they add a date field --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > let's face it Rachel, the date column is probably incorrect as the > table was > designed. knowing that it is important in queries, and that the > minutes > cause problems during query, your suggestion should have been > incorporated > in the original design (or truncing the oracle_date field via a > trigger). > both the blessing and curse of the DATE column. great for performing > date > math, but a pain when it comes to queries. > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Monday, April 08, 2002 2:54 PM > To: Multiple recipients of list ORACLE-L > > > I don't think you can do it.. I mean, you could change it to trunc > the > oracle_date field (that eliminates the minutes) and then do a to_date > of :b1 but you will still be operating on the oracle_date field. > > Okay, I HATE to suggest this, but since the table is small: > > add another field to the table oracle_date_2 as a date field. Update > the table set oracle_date_2=trunc(oracle_date) > > add a trigger to fill in oracle_date_2 when you insert a row or > update > the oracle_date column > > > create an index on oracle_date_2 and change the query to use that > column > > > --- [EMAIL PROTECTED] wrote: > > > > 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). > > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.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 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE
Re: utl_file_dir question
Hi, I am still not clear how to do this, I created 15 users in oracle...user1~user15. On the server site, utl_file_dir should be set to d:\dataware\sisfiles\user1. to user2. I checked the property of d:\dataware\sisfiles, we have sis group have all the permission on the folder. How can I set all the 15 users assign them to sis group? I think my question is how all the users can get the r/w permission? Thanks, Joan [EMAIL PROTECTED] wrote: > > Not an easy question. > > Set up a folder (directory) on the NT server called STUDENTS. Set your > utl_file_dir parameter to that folder. From the server's SERVICES panel, > stop and start the instance (so the parameter will take effect). Under > STUDENTS, create folders for each of your students and make them sharable. > You will need to have file sharing enabled on the server. To do that, > right click on the NETWORK NEIGHBORHOOD icon on the server. Pick > PROPERTIES and under that pick FILE AND PRINT SHARING. Tell it that you > want to be able to give others access to your files. From the student's > client machines, attach each student's folder as a network drive. You'll > probably want to make it so that they will automatically reconnect on > logon. > > > Joan Hsieh > @tufts.edu> <[EMAIL PROTECTED]> > Sent by: rootcc: > Subject: utl_file_dir question > > 04/08/2002 > 11:13 AM > Please > respond to > ORACLE-L > > > > I have couple question. We need to set up 15 trainer > in the training room by tomorrow for a pl/sql class. I need to set up > utl_file_dir for them. This is NT environment. > question 1: If I create 15 users in oracle, do I need to ask Nt admin > to create system users for them as well? > 2: It should be 15 different directories for each of them. How to set it > in int.ora? > 3: Since the training room are remote access to server. The utl_file_dir > will write to local or server? > > Those are sound pretty easy question. I never set this up before and > they want it now. I will check the manual as well. > > Thanks in advance, > > Joan > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Joan Hsieh > 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: Joan Hsieh 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[2]: SQL Tuning - How to avoid TOCHAR function against a date
How about something like: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1)+86399/86400; It's not the prettiest thing in the world, but it keeps the use of the index on ORACLE_DATE and an adjacent comment that there are 86400 seconds in the day should make it readable enough. -rje R> I don't think you can do it.. I mean, you could change it to trunc the R> oracle_date field (that eliminates the minutes) and then do a to_date R> of :b1 but you will still be operating on the oracle_date field. R> Okay, I HATE to suggest this, but since the table is small: R> add another field to the table oracle_date_2 as a date field. Update R> the table set oracle_date_2=trunc(oracle_date) R> add a trigger to fill in oracle_date_2 when you insert a row or update R> the oracle_date column R> create an index on oracle_date_2 and change the query to use that R> column R> --- [EMAIL PROTECTED] wrote: >> >> 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: Robert Eskridge 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).
Decyphering LMT space bitmap
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, First: 30, Free: 34 Start dump data blocks tsn: 1 file#: 2 minblk 3 maxblk 3 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 5, Flag: 0, First: 30, Free: 128994 FF3F ... all zeros Start dump data blocks tsn: 1 file#: 2 minblk 4 maxblk 4 frmt: 0x02 chkval: 0x type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 2, BeginBlock: 1056964613, Flag: 0, First: 0, Free: 129024 ... all zeros FWIW: SQL> select count (*) from dba_extents where file_id = 2; COUNT(*) -- 30 SQL> select extent_management from dba_data_files df, dba_tablespaces ts where df.tablespace_name = ts.tablespace_name and file_id = 2; EXTENT_MAN -- LOCAL -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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
let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: > > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet ac
RE: RMAN madness !!
Sure John..here it is :
run
{
set until time "to_date('20-MAR-2002 20:00:00','DD-MON- HH24:MI:SS')";
allocate auxiliary channel dupdb_d1 type 'SBT_TAPE';
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/rbslarge01sid1.dbf' to
'/disk01/oradata/test/data/rbslarge01sid1.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;
}
By the way, Jay suggested that I will have to set the NLS_DATE_FORMAT to
MONDDHH24MISS and
issue the command
set until time '2002032020';
He referred to this as a bug. He also asked me to set the NLS_LANG. Any idea
of how to set this ??
I do not think it is set in the parameter file. My character set is
WE8ISO8859P1.
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
-Original Message-
Sent: 08 April 2002 19:59
To: Multiple recipients of list ORACLE-L
I am a bit confused now Samir. Your earlier script did not mention a
I cannot see where you have the line duplicate target database to
"test"
Can you post you're revised script and we can check it out
John
-Original Message-
Sent: 08 April 2002 19:04
To: Multiple recipients of list ORACLE-L
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
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: 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: 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: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test"
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/dat
RE: SQL Tuning - How to avoid TOCHAR function against a date
Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm >>> [EMAIL PROTECTED] 04/08/02 01:56PM >>> 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: Ron Rogers 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: Mercadante, Thomas F 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: RMAN madness !!
I am a bit confused now Samir. Your earlier script did not mention a
I cannot see where you have the line duplicate target database to
"test"
Can you post you're revised script and we can check it out
John
-Original Message-
Sent: 08 April 2002 19:04
To: Multiple recipients of list ORACLE-L
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
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: 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: 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: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test"
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03028: fatal error code: 600
RMAN-03013: command type: Duplicate Db
RMAN-00600: internal error, arguments [15120] [] [] [] []
RMAN-01005: syntax error: found "single-quoted-string": expecting one of:
";"
RMAN-01007: at line 2 column 30 file: Memory Script
Any ideas anybody ?? My head is all mitered by now.
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
Schl
RE: SQL*Loader
David, In the FIELD clause, shouldn't it be OPTIONALLY ENCLOSED BY ? -Original Message- Sent: Monday, April 08, 2002 12:16 PM 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: Hand, Michael T 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: Foreign Objects in the System Tablespace.
Hello, Well, I'm puzzled. I have pctincrease set to 0, so I guess I'm not 'scared'. And I see nothing wrong in having DBSNMP under SYSTEM tblsp, along with SYS and SYSTEM. DBSNMP is hardly a major growth user. I have oversight of all dev/test/prod databases, so I know what's going on day to day. And the expression that you were prob. meant to use was 'you can not teach an old dog new tricks'. I certainly qualify for that, as I have been using computers (programming, proj. mgt., dba) since 1960, long enough to know better than to slight people for telling someone how they approach a situation. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 12:28 PM To: Multiple recipients of list ORACLE-L I am with Lisa. But then again there are folks out there who are still scared to change the pctincrease to 0 on the SYSTEM tablespace. I no longer care unless its on one of my databases. Sometimes you just can't teach a dog new tricks:-( -Original Message- Sent: Monday, April 08, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Paul, what's your reasoning behind this? I have always changed the default and temp tablespace of everything except SYS to something else. I was under the impression the SYSTEM tablespace should only hold the data dictionary. Lisa Koivu Oracle Database Tank Rash. Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Sherman, Paul R. [SMTP:[EMAIL PROTECTED]] > Sent: Monday, April 08, 2002 11:29 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Foreign Objects in the System Tablespace. > > Jay, > > I always set up my production databases having SYSTEM, SYS, and DBSNMP > with > default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, > OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for > temp > tblsp). I have never had any problems doing it this way. > > Thank you, > > Paul Sherman > DBA > voice - 781-501-4143 (office) > fax- 781-278-8341 (office) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Monday, April 08, 2002 10:09 AM > To: Multiple recipients of list ORACLE-L > > > I am trying to determine what Oracle "officially" considers foreign > objects in the SYSTEM tablespace. If you check out Note 122669.1, section > 7.1, Oracle recommends a query to find foreign objects in your system > tablespace. This query will report such users as: > > AURORA$JIS$UTILITY$ > CTXSYS > MDSYS > ORDSYS > OSE$HTTP$ADMIN > OUTLN > > It is part of our normal procedures to setup a TOOLS tablespace, and set > this as the default tablespace for the user SYSTEM. Objects such as > SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed > around MetaLink and posted in one of the forums, but I'm not really > getting > any concrete answers as to which users should be permitted to have objects > in the SYSTEM tablespace. I do know that it is OK to move OUTLN to > another > tablespace. > Comments would be appreciated. > > Thanks, > Jay > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Hostetter > 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: Sherman, Paul R. > 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: 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
Re: SQL Tuning - How to avoid TOCHAR function against a date
Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm >>> [EMAIL PROTECTED] 04/08/02 01:56PM >>> 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: Ron Rogers 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
Something like: WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1) + 1 - 1/(24*60*60) -Original Message- Sent: Monday, April 08, 2002 12:57 PM 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: Norrell, Brian 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
Iain, I will do some tests. Theoretically, yes, a range scan should be better than a full table scan. Thanks for your helpful recommendation. Cherie "Nicoll, Iain (Calanais)"To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> anais.com> Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date 04/08/02 12:37 PM Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE >= trunc(:b1) and oracle_date < trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM 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: 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: Foreign Objects in the System Tablespace.
If you let the Assistant create scripts to create the database, you will find that Oracle now automatically changes SYSTEM's default tablespace to TOOLS. The ONLY account that should have a default tablespace of SYSTEM is SYS. Rachel --- "Sherman, Paul R." <[EMAIL PROTECTED]> wrote: > Jay, > > I always set up my production databases having SYSTEM, SYS, and > DBSNMP with > default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like > ORACLE, > OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP > for temp > tblsp). I have never had any problems doing it this way. > > Thank you, > > Paul Sherman > DBA > voice - 781-501-4143 (office) > fax- 781-278-8341 (office) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Monday, April 08, 2002 10:09 AM > To: Multiple recipients of list ORACLE-L > > > I am trying to determine what Oracle "officially" considers foreign > objects in the SYSTEM tablespace. If you check out Note 122669.1, > section > 7.1, Oracle recommends a query to find foreign objects in your system > tablespace. This query will report such users as: > > AURORA$JIS$UTILITY$ > CTXSYS > MDSYS > ORDSYS > OSE$HTTP$ADMIN > OUTLN > > It is part of our normal procedures to setup a TOOLS tablespace, > and set > this as the default tablespace for the user SYSTEM. Objects such as > SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've > browsed > around MetaLink and posted in one of the forums, but I'm not really > getting > any concrete answers as to which users should be permitted to have > objects > in the SYSTEM tablespace. I do know that it is OK to move OUTLN to > another > tablespace. > Comments would be appreciated. > > Thanks, > Jay > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Hostetter > 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: Sherman, Paul R. > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 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: RMAN madness !!
Jay,
Thanks so much for ur help. Could u please tell me whether I shall have to
set the NLS_DATE_FORMAT and NLS_LANG in the parameter file of the auxiliary
db alone or on
the target db (the main prod db) as well ???
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 18:28
To: [EMAIL PROTECTED]; SARKAR, Samir
You need to change this line
set until time 'to_date(''20-MAR-2002
20:00:00'',''DD-MON-HH24:MI:SS'')';
to
set until time '2002032020';
This is bug 1887009. This isn't fixed until 9.0.2.
You must set
NLS_DATE_FORMAT=MMDDHH24MISS (or your favorite date format)
and
NLS_LANG= (whatever your nls lang is for the database)
I learned the hard way, that if you don't set NLS_LANG, then NLS_DATE_FORMAT
is ignored (at least in 8.1.7 on Tru64).
Don't forget to shutdown your auxiliary db, delete it's files, then start it
up again, NOMOUNT.
Jay
>>> [EMAIL PROTECTED] 04/08/02 02:03PM >>>
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
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: 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: 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: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test"
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of c
RE: Seeking opinions
Paul, I know what. . . to prevent whining in the future, suggest creating a hidden file for the rollback tablespace on /u004 and a soft link from the proper location. No wait, don't do that, they might go for it. ;) Mike -Original Message- Sent: Friday, April 05, 2002 9:53 AM To: Multiple recipients of list ORACLE-L What happens when you run out of disk space on a mount point? It happened to me. I created a new datafile on another mount point, and then had to hear staff DBAs whining, "why is there a rollback segment tablespace file on /u004?", as though it was a major disaster. Really, when people get into this mindset of "file A MUST be in directory B", they lose all sight of what they should be doing (ensuring a smoothly running database) and concentrate more on whether every file is in the right place so that they don't have to query the data dictionary. OK, I feel better now. :-) --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > I had an SA teach me that one, and it's saved me from making a REALLY > stupid mistake a number of times. > > As for your client's standards, I can see why they want to impose > standards and that's a good thing. But they are a bit too rigid with > it, as others have said, what happens if you run out of disk space on > a > mount point? > > I probably have bored most of the people on this list to death with > this, but I still believe that the ONLY way to make sure if a > datafile > is part of the database is to query the database. All you need is one > forgetful person who misplaces or misnames a file. > > Rachel > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > > > That's a great idea. Henceforth I'm going to do the same! Thanks. > > > > > > --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > > > and, on a Unix box, I ALWAYS do an "fuser" before deleting a > file. > > > Just > > > in case. > > > > > > > > > --- Jonathan Gennick <[EMAIL PROTECTED]> wrote: > > > > On Tue, 02 Apr 2002 07:43:34 -0800, you wrote: > > > > > > > > >Great point. I had recently created a DB file and forgot to > put > > > the > > > > ".dbf" > > > > >extension on it. If someone didn't query the DD of the DB > > first, > > > > they might > > > > >have thought it was a junk/temp file (they would have to > ignore > > > the > > > > file's > > > > >timestamp) and deleted it. > > > > > > > > Yeah, naming convention or no, I can't imagine not looking > > > > at v$datafile or dba_datafiles just to be sure. > > > > > > > > Jonathan Gennick --- Brighten the corner where you are > > > > mailto:[EMAIL PROTECTED] > > > > http://Gennick.com * http://MichiganWaterfalls.com * > > > > http://ValleySpur.com > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > -- > > > > Author: Jonathan Gennick > > > > 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). > > > > > > > > > __ > > > Do You Yahoo!? > > > Yahoo! Tax Center - online filing with TurboTax > > > http://taxes.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 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). > > > > > > __ > > Do You Yahoo!? > > Yahoo! Tax Center - online filing with TurboTax > > http://taxes.yahoo.com/ > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Paul Baumgartel > > 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 m
Re: SQL Tuning - How to avoid TOCHAR function against a date
I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: > > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 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
John, I will test it out. Thanks for your helpful recommendation. Cherie "John Hallas" ces.co.uk> cc: Sent by:Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date [EMAIL PROTECTED] 04/08/02 01:20 PM Please respond to ORACLE-L 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
RE: SQL Tuning - How to avoid TOCHAR function against a date
Could you maybe calculate a range of date values that encompasses the period you want and use BETWEEN on the raw date column? I'm thinking something along the lines of: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE BETWEEN TRUNC(:b1) AND TRUNC(:b1) + .9 ; but like, more elegant. 8^) HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 10:57 AM 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: Pardee, Roy E 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: RMAN madness !!
You need to change this line
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-HH24:MI:SS'')';
to
set until time '2002032020';
This is bug 1887009. This isn't fixed until 9.0.2.
You must set
NLS_DATE_FORMAT=MMDDHH24MISS (or your favorite date format)
and
NLS_LANG= (whatever your nls lang is for the database)
I learned the hard way, that if you don't set NLS_LANG, then NLS_DATE_FORMAT is
ignored (at least in 8.1.7 on Tru64).
Don't forget to shutdown your auxiliary db, delete it's files, then start it up again,
NOMOUNT.
Jay
>>> [EMAIL PROTECTED] 04/08/02 02:03PM >>>
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
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: 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: 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: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test"
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03028: fatal error code: 600
RMAN-03013: command type: Duplicate Db
RMAN-00600: internal error, arguments [15120] [] [] [] []
RMAN-01005: syntax error: found "single-quoted-string": expecting one of:
";"
RMAN-01007: at line 2 column 30 file: Memory Script
Any ideas anybody ?? My head is all mitered by now.
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)
Re: two listeners problem ???
Why not just run the 817 listener for both databases? Only one listener can be bound to a port, so using your config you could run 816 on 1521 & 817 on 1522. But I'd stick with using just the 817 listener on ports 1521 & 1526. Janet Linsy wrote: > > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Janet Linsy > 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE >= trunc(:b1) and oracle_date < trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM 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: Nicoll, Iain (Calanais) 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: two listeners problem ???
Hamid - The following is a low probability of solving your problem, but maybe you have exhausted the most likely fixes. As I recall from your original question, listener816 works and uses port 1521. listener817 doesn't work and uses port 1522. Could this be your problem? Why don't you shut down 816, switch 817 to use 1521, and see if it works. Something else may be using 1522. At our site we have to use 1526 as the second port. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 12:57 PM To: Multiple recipients of list ORACLE-L My problem still NOT solved, I have setup lister817 separatley and check my tnsname.ora also when I try to start the listener817 got the listener already started message but when I run lsnrctl services , I can not see the service in the list. My next test is to add the 817 listener to 816 and start it under 816, what do you think , any idea? I have done all the recommendation from the list but still no hope. Thanks all for your support. -Original Message- Sent: Monday, April 08, 2002 9:52 AM To: Multiple recipients of list ORACLE-L I joined in a bit late on this thread, so you might have your problem fixed. Your SID_LIST_LISTENER entry in the listener.ora should say SID_LIST_LISTENER816 and SID_LIST_LISTENER817. When starting the listener, do the following: (w/environment set for 816) lsnrctl start listener816 (w/environment set for 817) lsnrctl start listener817 In theory, you don't have to specify the sids, because the databases can register themselves with the listener, but I have had issues with this, so I just use the old sid list method. Which reminds me, you databases will all register with the default listener (on port 1521) unless you tell them otherwise with this init.ora entry: local_listener = "(address = (protocol = tcp)(host = gatech-devner1)(port = 1522))" I forget when this local_listener entry was implemented, but I know it is there for 8.1.7. One reason for having different listeners is for running databases in a cluster. Depending on how you have your cluster configured, you will need at least one listener per node. Jay Hostetter Oracle DBA D. & E. Communications Ephrata, PA USA >>> [EMAIL PROTECTED] 04/08/02 12:16PM >>> Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, April 06, 2002 8:08 AM > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services
RE: RE: Oracle vs. MS SQL
CPU pricing for enterprise edition is $15k per CPU. $40k is undoubtedly with a number of options, advanced replication, partitioning, ... Jared DENNIS WILLIAMS <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 04/08/02 07:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: RE: Oracle vs. MS SQL Gene - The $40K is the Enterprise Edition pricing as I recall. Can you move to Standard Edition? If you are using EE features, then chances are that MS SQL won't do the job. Also you can point out the eWeek benchmark between Oracle and MS SQL. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 05, 2002 6:23 PM To: Multiple recipients of list ORACLE-L OK, timing is impeccable. My boss just got the Oracle Bill, new licensing model $40k per processor for web based apps and flipped. I have some MSCE's working here pushing him to switch to SQL*server. Does anyone know where I can find reasons to stay w/ Oracle? Some things already mentioned here, but the MSCE's would say this list is bias, go figure :) Does SQLServer 2000 support blobs, row level locking, etc? Thanks, Gene PS. Do I move on to another Oracle shop or switch to SQLserver? OMG, the thought of working only on windoze makes me puke. I know this answer! >>> [EMAIL PROTECTED] 04/05/02 14:11 PM >>> There are some technical points worth considering. For example, SQL Server does not have true row level locking. It's table level locking, or some really creative SQL to fake it. This has a direct impact on scalability and performance. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Oops, a couple of items I didn't make clear: > - I was never able to compare the cost of Oracle > support with the cost of > Microsoft support. Oracle prices annual maintenance, > which includes the > right to upgrade to a new version of Oracle. MS > prices out per incident or > for all MS software at a location. If you can > estimate the number of > calls/month, then you could compare. > - Oracle DBA salary vs. MS SQL DBA salary. I feel > the difference is > primarily due to less experience, training. I find > it ironic that this > probably causes less reliability for Microsoft > (Microsoft has even > complained that organizations don't assign their > best people to administer > MS products), yet then Microsoft brags about how you > can save money because > their people ar cheaper. > > -Original Message- > Sent: Friday, April 05, 2002 11:14 AM > To: Multiple recipients of list ORACLE-L > > > I recently prepared a total cost comparison between > Oracle and MS SQL. I > appreciate the support several people on this list > provided me. In return, > here are some of the main points I learned. > - For smaller systems, investigate whether Oracle > Standard Edition will > meet your requirements. For example, most people > assume that to use > replication, you need EE. For our purposes the basic > replication that comes > with SE was adequate. > - Microsoft also offers SQL in both EE and SE > versions. Thanks very much > to Gints Plivna for providing me a > feature-by-feature comparison between the > different versions. MS SE is not equivalent to > Oracle SE. In most cases, the > more valid comparison is between MS SQL EE and > Oracle SE. > - For maintenance, there are two parts to > consider: Upgrade privilege and > support. Oracle bundles both of these together. Make > sure Microsoft is > priced with Software Assurance, which gives upgrade > privilege. Microsoft > prices support by the incident or by the location > (all Microsoft software). > I was never able to get a comparison. > - MS SQL EE with Software Assurance is actually > more expensive than Oracle > SE. Priced by the CPU. > - Since pricing is by CPU and RISC systems offer > higher database > performance (according to many people on this list) > and Oracle offers higher > performance in a head-to-head comparison (according > to the recently > published Eweek benchmark), I compared Oracle SE on > a 1-CPU Sun box with MS > SQL on a 2-CPU Intel box. The Intel box was cheaper, > but those two CPUs > really kill you on licensing! In my mind I am > convinced that both setups > could offer equivalent performance. > - I was provided figures that the average DBA > salary (including health, > vacation, etc.) on Oracle is $85,000 and on MS SQL > $68,500. A lading > industry analyst stated that the main reason MS SQL > sites have less > reliability is because there are few processes to > ensure high availability, > high performance. Developing these processes in the > MS SQL world is more > trial-and-error while these are well-documented in > the Oracle world. I would > add that several authors that participate in this > list have created that > documentation. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -- > Please see th
RE: two listeners problem ???
Leave your tnsnames.ora files alone for now - they aren't relevant to getting the listeners started. Post your current listener.ora files and the output from your commands to start the listeners. Make sure they are down before you try to start them. Jay >>> [EMAIL PROTECTED] 04/08/02 01:56PM >>> My problem still NOT solved, I have setup lister817 separatley and check my tnsname.ora also when I try to start the listener817 got the listener already started message but when I run lsnrctl services , I can not see the service in the list. My next test is to add the 817 listener to 816 and start it under 816, what do you think , any idea? I have done all the recommendation from the list but still no hope. Thanks all for your support. -Original Message- Sent: Monday, April 08, 2002 9:52 AM To: Multiple recipients of list ORACLE-L I joined in a bit late on this thread, so you might have your problem fixed. Your SID_LIST_LISTENER entry in the listener.ora should say SID_LIST_LISTENER816 and SID_LIST_LISTENER817. When starting the listener, do the following: (w/environment set for 816) lsnrctl start listener816 (w/environment set for 817) lsnrctl start listener817 In theory, you don't have to specify the sids, because the databases can register themselves with the listener, but I have had issues with this, so I just use the old sid list method. Which reminds me, you databases will all register with the default listener (on port 1521) unless you tell them otherwise with this init.ora entry: local_listener = "(address = (protocol = tcp)(host = gatech-devner1)(port = 1522))" I forget when this local_listener entry was implemented, but I know it is there for 8.1.7. One reason for having different listeners is for running databases in a cluster. Depending on how you have your cluster configured, you will need at least one listener per node. Jay Hostetter Oracle DBA D. & E. Communications Ephrata, PA USA >>> [EMAIL PROTECTED] 04/08/02 12:16PM >>> Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, April 06, 2002 8:08 AM > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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 'ListGu
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).
RE: two listeners problem ???
Hello, It should not be all that uncommon. We have 7 databases each on our development and test machines that are tied to their production counterparts, and, over time, some of the databases have lagged behind others (lots of reasons for that, esp. mgt. priorities), leaving us with multiple listeners (7.3.4, 8.1.6, and 8.1.7) on some of the machines. It takes a bit longer to recycle, and you have to think a minute before you act, but not too bad. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 12:16 PM To: Multiple recipients of list ORACLE-L Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, April 06, 2002 8:08 AM > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Janet Linsy > 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: Yechiel Adar 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: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists ---
RE: SQL*Loader
Dave, I just ran the following and it worked just fine: Sql_Loader file: LOAD DATA INFILE 'test_loader1.dat' replace INTO TABLE test ( DATETIME POSITION(01:21) CHAR "to_date(rtrim(ltrim(:datetime,),),'-mm-dd-hh24.mi.ss')", USERID POSITION(23:30) CHAR) Data File: '1999-02-05-09.26.13' 416000ZHUP '1999-02-05-09.26.13' 416000ZHUP '1999-02-05-09.26.13' 416000ZHUP Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 1:57 PM To: Multiple recipients of list ORACLE-L Here is an example of my logfile that it is generated: SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation:none specified Path used: Conventional Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype -- -- - -- --- TESTCASESTATUSIDFIRST 4 INTEGER TESTCASEID NEXT 4 INTEGER USERID NEXT 4 INTEGER CORDID NEXT 4 INTEGER UATASSIGNED NEXT 4 INTEGER PASSFAILSTATUSID NEXT 4 INTEGER DATETESTED NEXT * ," CHARACTER SQL string for column : "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')" TASKID NEXT 4 INTEGER RETEST NEXT 4 INTEGER ASSIDNEXT 4 INTEGER NONVALID NEXT 4 INTEGER Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Table APP_DEV.TESTCASEUATSTATUS: 0 Rows successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array:0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded:0 Run began on Mon Apr 08 10:06:31 2002 Run ended on Mon Apr 08 10:06:36 2002 Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92 thanks alot, David Ehresmann. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Hallas > Sent: Monday, April 08, 2002 12:10 PM > To: Multiple recipients of list ORACLE-L > Subject: 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
RMAN madness !!
Hi All,
I am back againand yes, my script didn't work completely. It did
write the data files
to the respective locations but it returned the following error during the
process of duplicating
the db :
RMAN-03021: executing script: Memory Script
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: 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: 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: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dupdb_d1: starting datafile backupset restore
RMAN-08502: set_count=335 set_stamp=456984311 creation_time=20-MAR-2002
RMAN-08089: channel dupdb_d1: specifying datafile(s) to restore from backup
set
RMAN-08523: restoring datafile 1 to
/disk01/oradata/test/data/system01sid1.dbf
RMAN-08523: restoring datafile 2 to
/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08523: restoring datafile 3 to
/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08523: restoring datafile 4 to
/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08523: restoring datafile 5 to
/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08523: restoring datafile 6 to
/disk01/oradata/test/data/users01sid1.dbf
RMAN-08023: channel dupdb_d1: restored backup piece 1
RMAN-08511: piece handle=DB_BKUP_INCR_0_SID1_456984311_335_1
tag=DB_BKUP_INCR_0 params=NULL
RMAN-08024: channel dupdb_d1: restore complete
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test"
RESETLOGS ARCHIVELOG
MAXLOGFILES 48
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES10
MAXLOGHISTORY 2042
LOGFILE
GROUP 1 ( '/disk03/oradata/test/logs/log01a01sid1.dbf' ) SIZE 188743680
,
GROUP 2 ( '/disk03/oradata/test/logs/log02a01sid1.dbf' ) SIZE 188743680
,
GROUP 3 ( '/disk03/oradata/test/logs/log03a01sid1.dbf' ) SIZE 188743680
DATAFILE
'/disk01/oradata/test/data/system01sid1.dbf'
CHARACTER SET WE8ISO8859P1
RMAN-03027: printing stored script: Memory Script
{
switch clone datafile all;
}
RMAN-03021: executing script: Memory Script
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=1 stamp=458671845
filename=/disk01/oradata/test/data/rbs01sid1.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=2 stamp=458671845
filename=/disk01/oradata/test/data/rbslarge01sid1.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=3 stamp=458671845
filename=/disk01/oradata/test/data/temp01sid1.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=4 stamp=458671845
filename=/disk01/oradata/test/data/tools01sid1.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=5 stamp=458671845
filename=/disk01/oradata/test/data/users01sid1.dbf
RMAN-08015: datafile 7 switched to datafile copy
RMAN-03027: printing stored script: Memory Script
{
set until time 'to_date(''20-MAR-2002 20:00:00'',''DD-MON-
HH24:MI:SS'')';
RMAN-00571: ===
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03028: fatal error code: 600
RMAN-03013: command type: Duplicate Db
RMAN-00600: internal error, arguments [15120] [] [] [] []
RMAN-01005: syntax error: found "single-quoted-string": expecting one of:
";"
RMAN-01007: at line 2 column 30 file: Memory Script
Any ideas anybody ?? My head is all mitered by now.
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 the SchlumbergerSema
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*Loader
Here is an example of my logfile that it is generated: SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation:none specified Path used: Conventional Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype -- -- - -- --- TESTCASESTATUSIDFIRST 4 INTEGER TESTCASEID NEXT 4 INTEGER USERID NEXT 4 INTEGER CORDID NEXT 4 INTEGER UATASSIGNED NEXT 4 INTEGER PASSFAILSTATUSID NEXT 4 INTEGER DATETESTED NEXT * ," CHARACTER SQL string for column : "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')" TASKID NEXT 4 INTEGER RETEST NEXT 4 INTEGER ASSIDNEXT 4 INTEGER NONVALID NEXT 4 INTEGER Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Table APP_DEV.TESTCASEUATSTATUS: 0 Rows successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array:0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded:0 Run began on Mon Apr 08 10:06:31 2002 Run ended on Mon Apr 08 10:06:36 2002 Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92 thanks alot, David Ehresmann. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Hallas > Sent: Monday, April 08, 2002 12:10 PM > To: Multiple recipients of list ORACLE-L > Subject: 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
RE: SQL*Loader
Dave, I bet you the single quotes around the data string are messing you up. You could try the following: DATETESTED char "rtrim(ltrim(to_date(:DateTested,'mm/dd/ hh:mi:ss pm'),),)", Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 12:16 PM 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: Mercadante, Thomas F 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).
SQL Tuning - How to avoid TOCHAR function against a date
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).
RE: two listeners problem ???
My problem still NOT solved, I have setup lister817 separatley and check my tnsname.ora also when I try to start the listener817 got the listener already started message but when I run lsnrctl services , I can not see the service in the list. My next test is to add the 817 listener to 816 and start it under 816, what do you think , any idea? I have done all the recommendation from the list but still no hope. Thanks all for your support. -Original Message- Sent: Monday, April 08, 2002 9:52 AM To: Multiple recipients of list ORACLE-L I joined in a bit late on this thread, so you might have your problem fixed. Your SID_LIST_LISTENER entry in the listener.ora should say SID_LIST_LISTENER816 and SID_LIST_LISTENER817. When starting the listener, do the following: (w/environment set for 816) lsnrctl start listener816 (w/environment set for 817) lsnrctl start listener817 In theory, you don't have to specify the sids, because the databases can register themselves with the listener, but I have had issues with this, so I just use the old sid list method. Which reminds me, you databases will all register with the default listener (on port 1521) unless you tell them otherwise with this init.ora entry: local_listener = "(address = (protocol = tcp)(host = gatech-devner1)(port = 1522))" I forget when this local_listener entry was implemented, but I know it is there for 8.1.7. One reason for having different listeners is for running databases in a cluster. Depending on how you have your cluster configured, you will need at least one listener per node. Jay Hostetter Oracle DBA D. & E. Communications Ephrata, PA USA >>> [EMAIL PROTECTED] 04/08/02 12:16PM >>> Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, April 06, 2002 8:08 AM > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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
RE: Another RMAN Problem ---> Urgent !!
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).
RE: SQL*Loader
Hum, I could be reading this wrong but it looks like you are converting a char to a date and then putting it into a char column If its a char on the table then don't convert it to a date first. Otherwise change the data type to date. DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", -Original Message- Sent: Monday, April 08, 2002 10:10 AM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: very interesting problem with V$SESSION and web applications....
Hello Bunyamin We have the same problem. I think that you can solve it with LDAP and enterprise users. Yechiel Adar Mehish - Original Message - From: Bunyamin K. Karadeniz To: Multiple recipients of list ORACLE-L Sent: Friday, April 05, 2002 11:15 PM Subject: very interesting problem with V$SESSION and web applications Dear Gurus , I have a comic question . ? We have a db and ias and portal . users log in by using portal login page . The problem is : because application server connects to db , in v$session the machines are all the application server machine . Although the users are db users , when you login from portal , the usernames are portal30 and portal30_sso .. So how will I know which user is which session ? V$session gives no help ... May be comic :) But can not find an answer .. Investigating portal for writing into v$session as the real username ..But no other thing comes into my mind Any idea please ... Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA.
RE: pl/sql statement
If you've declared the variable usercnt_tmp, then you should be fine if you just leave out the " from dual" bit and re-order the statements, e.g., select count(*) intousercnt_tmp fromprod.consenid ; hth, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 9:57 AM To: Multiple recipients of list ORACLE-L How can I do something like this; select count(*) from prod.consenid into usercnt_tmp from dual I want to send the amount of COUNT(*) into a variable. I get the following error ORA-06550: line 5, column 36: PLS-00103: Encountered the symbol "INTO" when expecting one of the following: . , @ ; for group having intersect minus order start union where connect ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "END" Is this something that I can do. I am probably overlooking something very obvious. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave 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: Pardee, Roy E 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-L Digest -- Volume 2002, Number 094
I got the same error when connecting in NT with a user that is not an NT administrator. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Friday, April 05, 2002 2:13 AM > the after trigger is missing the lone ranger? > > ORACLE-L Digest -- Volume 2002, Number 094 > > -- > > > > From: "Kimberly Smith" <[EMAIL PROTECTED]> > > Date: Wed, 3 Apr 2002 05:29:24 -0800 > > Subject: RE: sysdba > > > > Well, with all this info I can't imagine that someone will > > not be able to help you. > > > > -Original Message- > > paPIpapupapePO > > Sent: Tuesday, April 02, 2002 9:18 PM > > To: Multiple recipients of list ORACLE-L > > > > > > i cant connect as sysdba. > > ORA-01031 - insuffecient privileges. > > > > can anybody help me? > > > > > -- > 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: Yechiel Adar 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: Send mail from pl/sql
Ahh...the files on the link are for Forms sending from a Windows client via DDE (in which case the e-mail client matters), and not thru PL/SQL (in which case the e-mail client doesn't matter), like the original question stated. That's why I was confused. Thx! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Abdul Aleem [mailto:[EMAIL PROTECTED]] > Sent: Saturday, April 06, 2002 3:53 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Send mail from pl/sql > > > The client's importance is because the site I mentioned > perhaps has forms > for two types of e-mail clients. > I did sent you a procedure/function using UTL_SMTP with the > e-mail address > of original sender. > > Peter Koltez's site address is: > http://ourworld.compuserve.com/homepages/peter_koletzke/tip.ht > m#formstips1 > > HTH! > > Aleem > > > -Original Message- > Sent: Friday, April 05, 2002 8:33 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Send mail from pl/sql > > Two questions: > > 1) Why should the client matter? > 2) What is the web address of the site you mention? > > TIA! > > Rich Jesse System/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, > Sussex, WI USA > > > > -Original Message- > > From: Abdul Aleem [mailto:[EMAIL PROTECTED]] > > Sent: Friday, April 05, 2002 3:18 AM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: Send mail from pl/sql > > > > > > Which mail client are you looking it for. If it is for MS > > outlook, you can > > check at Perter Koltez's site > > > > Aleem > > > > -Original Message- > > Sent: Friday, April 05, 2002 12:33 PM > > To: Multiple recipients of list ORACLE-L > > Subject:Send mail from pl/sql > > > > Hallo, > > > > Does anyone have a good example on a procedure how to send > email from > > Pl/SQL. What I want to happen is to do a select statement > > and then send a > > mail with the result to the address [EMAIL PROTECTED] > > Please help me with this. I would appreciate it very miuch. > > > > Thanks in advance > > > > Roland -- 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).
RE: pl/sql statement
select count(*) into usercnt_tmp from prod.consenid; 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! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: pl/sql statement
select count(*) into usercnt_tmp from prod.consenid; "Farnsworth, Dave" wrote: > > How can I do something like this; > > select count(*) from prod.consenid into usercnt_tmp from dual > > I want to send the amount of COUNT(*) into a variable. I get the following error > > ORA-06550: line 5, column 36: > PLS-00103: Encountered the symbol "INTO" when expecting one of the following: > . , @ ; for > group having intersect > minus order start union where connect > ORA-06550: line 6, column 1: > PLS-00103: Encountered the symbol "END" > > Is this something that I can do. I am probably overlooking something very obvious. > > Thanks, > > Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis M. Heisler 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: utl_file_dir question
Joan, The oracle user, whoever owns the installation, needs to have r/w on the directory. I've only done it in Unix, but it's the same on NT, I believe. PS - here's a link to the online docs, they should help you out too: http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/ a76936/utl_file.htm#1000704 -Original Message- Sent: Monday, April 08, 2002 12:23 PM To: Multiple recipients of list ORACLE-L Thanks Arslan, I appreciate your timing response. Now the last question, for the r/w permission on the directory, if I created all the users in oracle, like user01,user02... I am not sure how to grant permission to the directory? Joan Arslan Bahar wrote: > > 2. like this > utl_file_dir =g:\oracle\utl_file, > g:\oracle\utl_file\change_svceng, g:\oracle\utl_file\change_usage > > 3. server > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, April 08, 2002 6:13 PM > > > I have couple question. We need to set up 15 trainer > > in the training room by tomorrow for a pl/sql class. I need to set > > up utl_file_dir for them. This is NT environment. question 1: If I > > create 15 users in oracle, do I need to ask Nt admin to create > > system users for them as well? > > 2: It should be 15 different directories for each of them. How to > > set it in int.ora? > > 3: Since the training room are remote access to server. The > > utl_file_dir will write to local or server? > > > > Those are sound pretty easy question. I never set this up before and > > they want it now. I will check the manual as well. > > > > Thanks in advance, > > > > Joan > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Joan Hsieh > > 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: Arslan Bahar > 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: Joan Hsieh 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: Sutton, Reed 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: pl/sql statement-Never Mind
Just as I predicted, I overlooked something obvious. What a knucklehead I am today. -Original Message- Sent: Monday, April 08, 2002 11:57 AM To: Multiple recipients of list ORACLE-L How can I do something like this; select count(*) from prod.consenid into usercnt_tmp from dual I want to send the amount of COUNT(*) into a variable. I get the following error ORA-06550: line 5, column 36: PLS-00103: Encountered the symbol "INTO" when expecting one of the following: . , @ ; for group having intersect minus order start union where connect ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "END" Is this something that I can do. I am probably overlooking something very obvious. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave 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: Farnsworth, Dave 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 !!
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).
pl/sql statement
How can I do something like this; select count(*) from prod.consenid into usercnt_tmp from dual I want to send the amount of COUNT(*) into a variable. I get the following error ORA-06550: line 5, column 36: PLS-00103: Encountered the symbol "INTO" when expecting one of the following: . , @ ; for group having intersect minus order start union where connect ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "END" Is this something that I can do. I am probably overlooking something very obvious. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave 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: Buf Hit Ratio
Glenn - V$SYSSTAT holds cumulative values since the instance was started. You need to measure it over a period of time. Note the values, then note them again in one hour, and measure the difference. Or use Oracle's utility STATSPACK or the older utlbstat/utlestat. Even better, get Oracle Performance Tuning 101 and learn why these ratio aren't the best way to tune your database. http://www.amazon.com/exec/obidos/ASIN/0072131454/qid=1018280809/sr=8-1/ref= sr_8_7_1/002-7587220-4526465 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 08, 2002 10:39 AM To: Multiple recipients of list ORACLE-L I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? SQL> list 1 select A.value + B.value "logical_reads", 2 C.value "phys_reads", 3 D.value "phy_writes", 4 (A.value+B.value)-C.value "log_minus_phys", 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) 6 "Buffer Hit Ratio" 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D 8 where A.statistic# = 38 9 AND B.statistic# = 39 10 AND C.statistic# = 40 11* AND D.statistic# = 44 SQL> / logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio 18,446,744,070,414,253,130 18,446,744,069,433,707,5592,043,488 980,545,5710 Here are some other stats; DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES --- PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS -- - 08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 18,446,744,047,946,114,866966,679 1,032,014,671 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis 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: 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).
Re: two listeners problem ???
I joined in a bit late on this thread, so you might have your problem fixed. Your SID_LIST_LISTENER entry in the listener.ora should say SID_LIST_LISTENER816 and SID_LIST_LISTENER817. When starting the listener, do the following: (w/environment set for 816) lsnrctl start listener816 (w/environment set for 817) lsnrctl start listener817 In theory, you don't have to specify the sids, because the databases can register themselves with the listener, but I have had issues with this, so I just use the old sid list method. Which reminds me, you databases will all register with the default listener (on port 1521) unless you tell them otherwise with this init.ora entry: local_listener = "(address = (protocol = tcp)(host = gatech-devner1)(port = 1522))" I forget when this local_listener entry was implemented, but I know it is there for 8.1.7. One reason for having different listeners is for running databases in a cluster. Depending on how you have your cluster configured, you will need at least one listener per node. Jay Hostetter Oracle DBA D. & E. Communications Ephrata, PA USA >>> [EMAIL PROTECTED] 04/08/02 12:16PM >>> Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, April 06, 2002 8:08 AM > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter 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: Foreign Objects in the System Tablespace.
I am with Lisa. But then again there are folks out there who are still scared to change the pctincrease to 0 on the SYSTEM tablespace. I no longer care unless its on one of my databases. Sometimes you just can't teach a dog new tricks:-( -Original Message- Sent: Monday, April 08, 2002 8:53 AM To: Multiple recipients of list ORACLE-L Paul, what's your reasoning behind this? I have always changed the default and temp tablespace of everything except SYS to something else. I was under the impression the SYSTEM tablespace should only hold the data dictionary. Lisa Koivu Oracle Database Tank Rash. Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Sherman, Paul R. [SMTP:[EMAIL PROTECTED]] > Sent: Monday, April 08, 2002 11:29 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Foreign Objects in the System Tablespace. > > Jay, > > I always set up my production databases having SYSTEM, SYS, and DBSNMP > with > default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, > OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for > temp > tblsp). I have never had any problems doing it this way. > > Thank you, > > Paul Sherman > DBA > voice - 781-501-4143 (office) > fax- 781-278-8341 (office) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Monday, April 08, 2002 10:09 AM > To: Multiple recipients of list ORACLE-L > > > I am trying to determine what Oracle "officially" considers foreign > objects in the SYSTEM tablespace. If you check out Note 122669.1, section > 7.1, Oracle recommends a query to find foreign objects in your system > tablespace. This query will report such users as: > > AURORA$JIS$UTILITY$ > CTXSYS > MDSYS > ORDSYS > OSE$HTTP$ADMIN > OUTLN > > It is part of our normal procedures to setup a TOOLS tablespace, and set > this as the default tablespace for the user SYSTEM. Objects such as > SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed > around MetaLink and posted in one of the forums, but I'm not really > getting > any concrete answers as to which users should be permitted to have objects > in the SYSTEM tablespace. I do know that it is OK to move OUTLN to > another > tablespace. > Comments would be appreciated. > > Thanks, > Jay > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Hostetter > 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: Sherman, Paul R. > 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: 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: Kimberly Smith 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: Buf Hit Ratio
Given that a 1 GHz CPU can do about 100,000 logical I/Os per second, and the largest machine is currently (I think) only 128 CPUs, I can only guess three possibilities: Your instance has been up for the last 500 years The statistics numbers don't match the ones that the Oracle code is supposed to match. You are running a 64-bit version of Oracle, and the code the exposes v$sysstat (or rather the x$ underlying it) is not quite in-line with the actual memory content. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 08 April 2002 16:15 |I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this? | |SQL> list | 1 select A.value + B.value "logical_reads", | 2 C.value "phys_reads", | 3 D.value "phy_writes", | 4 (A.value+B.value)-C.value "log_minus_phys", | 5 round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) | 6 "Buffer Hit Ratio" | 7 from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D | 8 where A.statistic# = 38 | 9 AND B.statistic# = 39 | 10 AND C.statistic# = 40 | 11* AND D.statistic# = 44 |SQL> / | | logical_reads phys_reads phy_writes log_minus_phys Buffer Hit Ratio | --- - | 18,446,744,070,414,253,130 18,446,744,069,433,707,559 2,043,488 980,545,5710 | |Here are some other stats; | |DTSTAMPPHYSICAL_READSDB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_WRITES | -- -- --- | PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS | -- - |08-APR-2002 10:35:16 18,446,744,069,434,437,169 171,781,916 18,446,744,070,280,471,598 2,103,859 | 18,446,744,047,946,114,866966,679 1,032,014,671 |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan 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: 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: utl_file_dir question
Thanks Arslan, I appreciate your timing response. Now the last question, for the r/w permission on the directory, if I created all the users in oracle, like user01,user02... I am not sure how to grant permission to the directory? Joan Arslan Bahar wrote: > > 2. like this > utl_file_dir =g:\oracle\utl_file, > g:\oracle\utl_file\change_svceng, g:\oracle\utl_file\change_usage > > 3. server > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, April 08, 2002 6:13 PM > > > I have couple question. We need to set up 15 trainer > > in the training room by tomorrow for a pl/sql class. I need to set up > > utl_file_dir for them. This is NT environment. > > question 1: If I create 15 users in oracle, do I need to ask Nt admin > > to create system users for them as well? > > 2: It should be 15 different directories for each of them. How to set it > > in int.ora? > > 3: Since the training room are remote access to server. The utl_file_dir > > will write to local or server? > > > > Those are sound pretty easy question. I never set this up before and > > they want it now. I will check the manual as well. > > > > Thanks in advance, > > > > Joan > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Joan Hsieh > > 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: Arslan Bahar > 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: Joan Hsieh 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).
SQL*Loader
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).
Re: two listeners problem ???
Please excuse my idiotic curiosity. Why do you need 2 listeners on the same machine? I always install the listener of the higher version and disable the previous one. Yechiel Adar. Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, April 06, 2002 8:08 AM > Hi, > > I have two databases ORCL817 and ORCL816 in version > 817, 816 on the same machine Sun 5.7. I'd like to > configure seperate listener for each database. I used > different port, listener name (1521 and LISTENER817 > for ORCL817 , 1522 and LISTENER816 for ORCL816), but > somehow the listener on 1522 cannot be started. > Why??? > > Here is the listener.ora and tnsnames.ora for both > database: > > listener.ora for 816 > = > LISTENER816 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL816) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.6) > (SID_NAME = ORCL816) > ) > > tnsnames.ora for 816 > = > ORCL816 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1522)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL816) > ) > ) > > listener.ora for 817 > = > LISTENER817 = > (DESCRIPTION_LIST = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > ) > (DESCRIPTION = > (PROTOCOL_STACK = > (PRESENTATION = GIOP) > (SESSION = RAW) > ) > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 2481)) > ) > ) > > SID_LIST_LISTENER = > (SID_LIST = > (SID_DESC = > (GLOBAL_DBNAME = ORCL817) > (ORACLE_HOME = > /export/apps/oracle/admin/product/8.1.7) > (SID_NAME = ORCL817) > ) > ) > > tnsnames.ora > = > ORCL817 = > (DESCRIPTION = > (ADDRESS_LIST = > (ADDRESS = (PROTOCOL = TCP)(HOST = > gatech-denver1)(PORT = 1521)) > ) > (CONNECT_DATA = > (SERVICE_NAME = ORCL817) > ) > ) > > Anyone knows what went wrong? Thank you! > > Janet > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Janet Linsy > 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: Yechiel Adar 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 !!
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/system01si
Ang: RE: Pl/SQL code help
But when I have fixed that error and then have the then statement put before the lvsql statement I get this errormessage: What does that mean? How to fix it? PLS-00103: Encountered the symbol "THEN" when expecting one of the following: ( - + mod null avg count current max mi Thanks in advance Roland "Thomas, John" <[EMAIL PROTECTED]>@fatcity.com den 2002-04-08 07:24 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Roland, I think you have a quote in the wrong place: If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || Should probably read: If 'A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 ' || > Cheers, > > John Thomas -Original Message- Sent: 08 April 2002 13:43 To: Multiple recipients of list ORACLE-L Hallo, I have some trouble with this pl/sql procedure. I would like that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in the if statement, whatis wrong with this? It get the erromressage: LS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe.. when I compile the whole procedure. Please help me with this. (If the borttags_flagg = 1 then it will continue the loop and check for next one. etc... If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || then (this lvsql is to be run only if field borttags_flagg = 0) lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUMhttp://www.orafaq.com -- Author: Thomas, John 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: utl_file_dir question
Not an easy question. Set up a folder (directory) on the NT server called STUDENTS. Set your utl_file_dir parameter to that folder. From the server's SERVICES panel, stop and start the instance (so the parameter will take effect). Under STUDENTS, create folders for each of your students and make them sharable. You will need to have file sharing enabled on the server. To do that, right click on the NETWORK NEIGHBORHOOD icon on the server. Pick PROPERTIES and under that pick FILE AND PRINT SHARING. Tell it that you want to be able to give others access to your files. From the student's client machines, attach each student's folder as a network drive. You'll probably want to make it so that they will automatically reconnect on logon. Joan Hsieh <[EMAIL PROTECTED]> Sent by: rootcc: Subject: utl_file_dir question 04/08/2002 11:13 AM Please respond to ORACLE-L I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh 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: Slightly OT: Perl Q./THX
Hi All, We forgot to export the variables export. Jack Jared Still <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> om> cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Re: Slightly OT: Perl Q. [EMAIL PROTECTED] 05-04-2002 19:49 Please respond to ORACLE-L Jack, How about examples? 1) a shell script to set some ENV vars 2) a perl script that should see the newly set vars 3) your command line for running them. Jared On Wednesday 03 April 2002 00:53, Jack van Zanen wrote: > Hi All (Jared in particular), > > > OS: AIX > We are trying the following: > > We have a script that executes and sets all sorts of environment variables. > Than after this we execute a perl script that reads the environment > variables, however the environment variables set in the first script are > not picked up. > We tried executing the variables script with [ . script] (dot space > scriptname) as well but still no luck. > > How can we make the perl script pick up these variables? > > > TIA and sorry for the OT but I am not using perl so much as to join another > mailing list > > Jack > > === > 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 verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene > voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos toegezonden. > = > The information contained in this communication is confidential and is > intended solely for the use of the individual or entity to whom it is > addressed. You should not copy, disclose or distribute this communication > without the authority of Ernst & Young. Ernst & Young is neither liable for > the proper and complete transmission of the information contained in this > communication nor for any delay in its receipt. Ernst & Young does not > guarantee that the integrity of this communication has been maintained nor > that the communication is free of viruses, interceptions or interference. > > If you are not the intended recipient of this communication please return > the communication to the sender and delete and destroy all copies. > > In carrying out its engagements, Ernst & Young applies general terms and > conditions, which contain a clause that limits its liability. A copy of > these terms and conditions is available on request free of charge. > === -- Please see the official ORACLE-L FAQ: http:
PL/SQL help - need some quick help
Hallo, I have some trouble to get this pl/sql code right. Anyone whpom could help me with this. It is important. Hallo, I have some trouble with this pl/sql procedure. I would like that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in the if statement, whatis wrong with this? It get the erromressage: LS-00103: Encountered the symbol "||AvdNr||" when expecting one of the following: . ( * @ % & = - + < / > at in mod not rem then <> or != or ~= >= <= <> and or like betwe.. when I compile the whole procedure. Please help me with this. (If the borttags_flagg = 1 then it will continue the loop and check for next one. etc... If A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' || then (this lvsql is to be run only if field borttags_flagg = 0) lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' || --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' || 'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' || --PBK.LPKORGEANREL ' || 'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' || 'AND ICA_ARTIKEL.DATUM testplsql.SQL Description: Binary data
RE: Foreign Objects in the System Tablespace.
Paul, what's your reasoning behind this? I have always changed the default and temp tablespace of everything except SYS to something else. I was under the impression the SYSTEM tablespace should only hold the data dictionary. Lisa Koivu Oracle Database Tank Rash. Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Sherman, Paul R. [SMTP:[EMAIL PROTECTED]] > Sent: Monday, April 08, 2002 11:29 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Foreign Objects in the System Tablespace. > > Jay, > > I always set up my production databases having SYSTEM, SYS, and DBSNMP > with > default tblsp of SYSTEM; SYS gets temp tblsp of TEMP. Users like ORACLE, > OUTLN, TRACESVR, ORDSYS, etc. get TOOLS tblsp for default (and TEMP for > temp > tblsp). I have never had any problems doing it this way. > > Thank you, > > Paul Sherman > DBA > voice - 781-501-4143 (office) > fax- 781-278-8341 (office) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Monday, April 08, 2002 10:09 AM > To: Multiple recipients of list ORACLE-L > > > I am trying to determine what Oracle "officially" considers foreign > objects in the SYSTEM tablespace. If you check out Note 122669.1, section > 7.1, Oracle recommends a query to find foreign objects in your system > tablespace. This query will report such users as: > > AURORA$JIS$UTILITY$ > CTXSYS > MDSYS > ORDSYS > OSE$HTTP$ADMIN > OUTLN > > It is part of our normal procedures to setup a TOOLS tablespace, and set > this as the default tablespace for the user SYSTEM. Objects such as > SQLPLUS_PRODUCT_PROFILE will be created in this tablespace. I've browsed > around MetaLink and posted in one of the forums, but I'm not really > getting > any concrete answers as to which users should be permitted to have objects > in the SYSTEM tablespace. I do know that it is OK to move OUTLN to > another > tablespace. > Comments would be appreciated. > > Thanks, > Jay > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jay Hostetter > 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: Sherman, Paul R. > 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: 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).
RE: Another RMAN Problem ---> Urgent !!
Hi,
Forgive my ignorance but if I look at your connect string you connect to
both the target as auxiliary with the /
This in my opinion means that they both connect to the same instance and
judging from the error and what you wrote it looks as if you are connecting
to production with your auxiliary connection. Be glad it fails!!!
Jack
"SARKAR, Samir"
<[EMAIL PROTECTED]To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>
ma.slb.com>cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: [EMAIL PROTECTED] Subject: RE: Another RMAN
Problem ---> Urgent !!
08-04-2002 17:24
Please respond to 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
Re: anyone know how to change tablespace names?
you really don't want to do this by mucking in the data dictionary. the only SUPPORTED way is to export, drop the tablespace, recreate it with the correct name,grant the user (or users) quota on the new tablespace while revoking quota on all others (so they are forced into this one) and import. You could create the objects in the new tablespace before the import, instead of removing the quota on the other tablespaces. --- "Magaliff, Bill" <[EMAIL PROTECTED]> wrote: > is there a back-end way to effect an "alter tablespace rename to ..." > type > of thing? > > yes, I know Oracle doesn't support mucking with the data dictionary, > but . . > . > > thx > -bill > > Bill Magaliff > Framework, Inc. > 914-631-2322 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Magaliff, Bill > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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 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: utl_file_dir question
Hi 1.No, Utl_file writes as the Oracle process 2. I believe it is per instance and not per user. (you can create 15 directories and put all 15 in the init.ora file) 3. Utl_file writes on the server, not client jack Joan Hsieh .edu>cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: utl_file_dir question [EMAIL PROTECTED] 08-04-2002 17:13 Please respond to ORACLE-L I have couple question. We need to set up 15 trainer in the training room by tomorrow for a pl/sql class. I need to set up utl_file_dir for them. This is NT environment. question 1: If I create 15 users in oracle, do I need to ask Nt admin to create system users for them as well? 2: It should be 15 different directories for each of them. How to set it in int.ora? 3: Since the training room are remote access to server. The utl_file_dir will write to local or server? Those are sound pretty easy question. I never set this up before and they want it now. I will check the manual as well. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh 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 verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the inten
RE: Another RMAN Problem ---> Urgent !!
Samir,
Glad I could help with your initial problem.
I'm afraid I can't help you here. I've never done this before. Over to
Jay...
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, April 08, 2002 10:25 AM
To: 'Jay Hostetter'
Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
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'
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/tools01si
Re: utl_file_dir question
2. like this utl_file_dir =g:\oracle\utl_file, g:\oracle\utl_file\change_svceng, g:\oracle\utl_file\change_usage 3. server - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 08, 2002 6:13 PM > I have couple question. We need to set up 15 trainer > in the training room by tomorrow for a pl/sql class. I need to set up > utl_file_dir for them. This is NT environment. > question 1: If I create 15 users in oracle, do I need to ask Nt admin > to create system users for them as well? > 2: It should be 15 different directories for each of them. How to set it > in int.ora? > 3: Since the training room are remote access to server. The utl_file_dir > will write to local or server? > > Those are sound pretty easy question. I never set this up before and > they want it now. I will check the manual as well. > > Thanks in advance, > > Joan > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Joan Hsieh > 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: Arslan Bahar 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).
