RE: Undocumented Parameters
Got this one from somebody, don't remember who, though... select rpad (a.indx ,10) "number" ,rpad (b.KSPPINM ,50) "name" , rpad (a.KSPPSTVL,50) "value" , rpad (a.KSPPSTDF,10) "default" from x$KSPPCV a , X$KSPPI b where a.indx = b.indx; HTH, Mandar. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Robertson Lee - lerobeSent: Thursday, May 30, 2002 4:13 PMTo: Multiple recipients of list ORACLE-LSubject: Undocumented Parameters Someone posted a bit of SQL to list out the undocumented parameters and guess what ... I deleted it. Could someone repost please ?? TIA Lee The information contained in this communication isconfidential, is intended only for the use of the recipientnamed above, and may be legally privileged. If the reader of this message is not the intended recipient, you arehereby notified that any dissemination, distribution orcopying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computersystem.
RE: Best way to drop and recreate and instance?
Hi Mark, Regarding your problem with dropping a user, I've seen something like that before - this was 817 on Solaris, and the DBA there was getting a ORA-00942 at recursive SQL when trying any CTAS. I ran a trace on the statement and found that Oracle was looking for a sys.snap$ table which didn't exist (don't remember the name now). I also found that this database didn't have a single sys.snap$ table while another one on the same release had about 7-8. Later heard from the DBA that they weren't able to get replication working on the database either. Luckily for him, this was a test instance and hence not a serious issue.but I still never found out why those tables were missing. I had suggesting recreating the tables from sql.bsq (figured the db was knackered anyway, might as well try it), but I don't think he ever got around to doing it. I'd be very interested to know if a trace reveals anything to you... Hope this helps a bit.. Mandar. -Original Message- Sent: Wednesday, April 24, 2002 6:33 PM To: Multiple recipients of list ORACLE-L Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L Hi All, I have *finally* got time to drop one of our test instances and recreate it (tomorrow), and wanted some feedback from all of you as to the best way to go around this? The current instance is 8.1.7. on a Win2K machine.. The reason that I'm dropping/recreating (some of you may remember this) is because I simply *CANNOT* drop a user: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop user scott cascade; drop user scott cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist LOL! If anybody has seen this since I last asked the list, and has a solution, please speak up now as this will save me the time of dropping/recreating the instance.. Moving on - what would you consider the best approach to dropping and recreating an instance on a Win32 platform? In the past (as these are purely test instances) I have simply blasted the whole directories (Oracle and all), then deleted any mention of Oracle within the registry (after taking a backup of course), and moved on to a completely fresh install (usually to go to a higher version). This time though, I would like to keep 8.1.7 installed on this box, and would prefer to keep Oracle installed, and simply drop and recreate a fresh (empty) instance.. How would you go about doing this? Advanced thanks. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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 Leith 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: Mandar Shete 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
RE: temporary tablespace
Hi Seema, Your file is growing because you have set AUTOEXTEND ON. When you shut the instance down, Oracle will release the temp segments, but not actually reduce the file size. You can try shrinking the file (preferably after bouncing the instance) if possible, or you can add a new temp tablespace, make it the users' default temp space, and drop the original one (after checking, of course, that its not being used - see v$sort_segment, v$sort_usage). HTH, Mandar. -Original Message- Sent: Tuesday, April 16, 2002 4:18 AM To: Multiple recipients of list ORACLE-L Hi I created temporary tablespace by following command create tablespace TEMP datafile '/xxx/yyy/temp01.dbf' size 800M autoextend on next 2m default storage (initial 1024K next 1024K minextents 1 maxextents 2147483645 pctincrease 0) temporary online / Is this correct? I am looking that it is filling the disk partitions. is temp tablespace will release the space once i shutdown the database? I am thinking i used to use create temporary tablespace temp tempfile 'filename' size would be good syntax? if i offline this tablespace and create another temporary tablespace and ssign that tablespace to users which are using earlier?What will beimpact? tHx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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: Mandar Shete 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: Errormessage
Hi Roland, check if your temp tablespace is getting filled up (look at dba_free_space, v$sort_segment, v$sort_usage). Are you getting any other messages with this one ? Mandar. [EMAIL PROTECTED] wrote: Hallo I run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema. SELECT RIK2.VARE.EAN_NR, gt; RIK2.VARE.LEVNR, gt; RIK2.ART_HIERARKI_CALC.VGR, gt; RIK2.VARE.SORTIMENT, gt; RIK2.VARE.VARENAVN gt; FROM RIK2.VARE, RIK2.ART_HIERARKI_CALC gt; WHERE RIK2.VARE.SELSKAP='11' AND gt; RIK2.VARE.VARE_SNR=10 gt; AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENR gt; ORDER BY VGR gt; gt; gt; ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_2001102321319121061730690 Content-Type: text/html; charset=us-ascii PHi Roland, check if your temp tablespace is getting filled up (look at dba_free_space, v$sort_segment, v$sort_usage). Are you getting any other messages with this one ?/P PMandar./P PBRBRBI[EMAIL PROTECTED]/B/I wrote:BRBR/P BLOCKQUOTE style=BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5pxBRHalloBRBRBRBRBRBRI run this select statement and get this errormessage. What should I do to solve it? I have a databaselink from the actual schema to another schema.BRBRBRBRBRBRSELECT RIK2.VARE.EAN_NR,BRgt; RIK2.VARE.LEVNR,BRgt; RIK2.ART_HIERARKI_CALC.VGR,BRgt; RIK2.VARE.SORTIMENT,BRgt; RIK2.VARE.VARENAVNBRgt; FROM RIK2.VARE, RIK2.ART_HIERARKI_CALCBRgt; WHERE RIK2.VARE.SELSKAP='11' ANDBRgt; RIK2.VARE.VARE_SNR=10BRgt; AND RIK2.VARE.VARENR=RIK2.ART_HIERARKI_CALC.VARENRBRgt; ORDER BY VGRBRgt;BRgt;BRgt; ORA-03232: unable to allocate an extent of 62 blocks from tablespace 3BRBRBRThanks in advanceBRBRBRBRBRBRRolandBRBR-- BRPlease see the official ORACLE-L FAQ: http://www.orafaq.comBR-- BRAuthor: BRINET: [EMAIL PROTECTED]BRBRFat City Network Services -- (858) 538-5051 FAX: (858) 538-5051BRSan ! ! ! Diego, California -- Public Internet access / Mailing ListsBRBRTo REMOVE yourself from this mailing list, send an E-Mail messageBRto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inBRthe message BODY, include a line containing: UNSUB ORACLE-LBR(or the name of mailing list you want to be removed from). You mayBRalso send the HELP command for other information (like subscribing)./BLOCKQUOTEBR hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/font/a/bbrBuy Music, Video, CD-ROM, Audio-Books and Music Accessories from A href=http://www.planetm.co.in;http://www.planetm.co.in/A --=_MAILER_ATTACH_BOUNDARY1_2001102321319121061730690-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete 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).
Export Hanging
, CD-ROM, Audio-Books and Music Accessories from A href=http://www.planetm.co.in;http://www.planetm.co.in/A --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Export Hanging
;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; file openamp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; 3amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; 0lt;BRgt;63a! ! ! mp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; SQL*Net message to clientamp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; $4859amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;0amp;nbsp; lt;BRgt;63amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; SQL*Net more data to clientamp;nbsp;amp;nbsp;amp;nbsp; 9282amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp! ! ! ;amp;nbsp;0amp;nbsp; lt;BRgt;63amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;SQL*Net message from clientamp;nbsp;amp;nbsp;244858amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp;amp;nbsp; 0lt;BRgt;lt;/Pgt; lt;Pgt;I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriouslyamp;nbsp;since we don't take regular exports.lt;/Pgt; lt;Pgt;I'm about to raise a TAR on this one, but any pointers before that would be useful.lt;/Pgt; lt;Pgt;TIA,lt;/Pgt; lt;Pgt;Mandar.lt;/Pgt; lt;Pgt;amp;nbsp;lt;/Pgt; lt;Pgt;amp;nbsp;lt;/Pgt;lt;/BLOCKQUOTEgt; lt;hrgt;lt;font face=Arial size=2gt;lt;bgt;Get Your Private, Free E-mail from Indiatimes at lt;/fontgt;lt;a href=http://email.indiatimes.comgt;lt;font face=Arial size=2gt;http://email.indiatimes.comlt;/fontgt;lt;/agt;lt;/bgt;lt;brgt;Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from lt;A href=http://www.planetm.co.ingt;http://www.planetm.co.inlt;/Agt; --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) S8-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). Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_2001102212023232124898138 Content-Type: text/html; charset=us-ascii PYou're right Lisa, forgot to mention that. But we do have more than enough disk space available. Everything else seems to be ok as well with the OS - logged in as administrator, so no privilege issues either./P PMandar./P PBRBRBIKoivu, Lisa[EMAIL PROTECTED]/B/I wrote: BR/P BLOCKQUOTE style=BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN META content=MS Exchange Server version 5.5.2653.12 name=Generator PFONT color=#ff face=GeorgiaMandar, did you have enough disk available for the export file?nbsp; You don't say anything about checking your filesystem./FONT /P PFONT face=GeorgiaLisa Koivu/FONT BRFONT face=GeorgiaOracle Database Monkey/FONT BRFONT face=GeorgiaFairfield Resorts, Inc./FONT BRFONT face=Georgia954-935-4117/FONT /PBR UL PFONT face=Arial size=1-Original Message-/FONT BRBFONT face=Arial size=1From:nbsp;nbsp;/FONT/B FONT face=Arial size=1Mandar Shete [SMTP:[EMAIL PROTECTED]]/FONT BRBFONT face=Arial size=1Sent:nbsp;nbsp;/FONT/B FONT face=Arial size=1Monday, October 22, 2001 9:50 AM/FONT BRBFONT size=1 úCE=ArialTo:nbsp;nbsp;nbsp;nbsp;/FONT/B FONT size=1 úCE=ArialMultiple recipients of list ORACLE-L/FONT BRBFONT size=1 úCE=ArialSubject:nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;/FONT/B FONT face=Arial size=1Export Hanging/FONT /P PFONT size=2 úCE=Arial--=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705/FONT BRFONT face=Arial size=2Content-Type: text/plain; charset=us-ascii/FONT /PBR PFONT face=Arial size=2Hi All,/FONT /PBRBR PFONT face=Arial size=2Oracle 8.1.6.1 NT 4 SP5/FONT /PBR PFONT face=Arial size=2I'm facing
Re: RE: Export Hanging
Thanks Denis - I should have mentioned it - nothing in the alert log either. Mandar. [EMAIL PROTECTED] wrote: Mandar - Have you checked you alert log to see if Oracle is writing anything useful there? Maybe you have, I just didn't see where you mentioned it. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 22, 2001 8:50 AM To: Multiple recipients of list ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/plain; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS 63 latch free 2 2 63 db file sequential read 178 0 63 db file scattered read 6195 0 63 file open 3 0 63 SQL*Net message to client 244859 0 63 SQL*Net more data to client 149282 0 63 SQL*Net message from client 244858 0 I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports. I'm about to raise a TAR on this one, but any pointers before that would be useful. TIA, Mandar. Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/html; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT ;amp;nb sp; TOTAL_WAITS TOTAL_TIMEOUTS 63 latch free amp;nbs p; 2amp;nb sp; 2 63 db file sequential read 178amp; nbsp; 0 63amp;nbs p; amp;nb! ! ! sp;db file scattered read 6195 0 63 file open amp;nbs p; 3amp;nb sp; 0 63 SQL*Net message to client 244859 amp;nbs p; 0 63 SQL*Net more data to client 149282 amp;nbs p; ! ! ! ; 0 63 SQL*Net message from client 244858amp;nbs p; 0 I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports. I'm about to raise a TAR on this one, but any pointers before that would be useful. TIA, Mandar. Get Your Private, Free E-mail from Indiatimes at face=Arial size=2gt;http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from href=http://www.planetm.co.ingt;http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete 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
Re: Re: Export Hanging
Thanks Prasad. I have looked in Metalink but could not find anything that relates exactly to my situation - as I mentioned before, I can't see any non-idle wait in either v$session_wait or v$session_event. But I'll try tracing the session to see if that helps (though I still can't figure out why I can't see the session waiting on anything). If, as you said, the session was hanging while trying to read a table, wouldn't I see at least some db file reads ? In your case how did did you find that the session was waiting on that particular event ? Mandar. [EMAIL PROTECTED] wrote: Hi Mandar, We had similar problem when we did full db export. What we found was, export process was hanging when it was reading one of the system table (in our case, it is SYS.EXU8REFIC). When we saw the execution plan, it is using ALL_ROWS (there are no stats on sys objects but still it is doing ALL_ROWS). We recreated the view with RULE hint and export worked without any problems. I also did research in metalink and I found that the same solution was given to similar questions by oracle tech support. If you have access to metalink, please search with 'export hung' and you will get related information. hope this helps. Prasad Mandar Shete times.comgt; cc: Sent by: Subject: Export Hanging [EMAIL PROTECTED] 10/22/2001 09:50 AM Please respond to ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/plain; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS 63 latch free 2 2 63 db file sequential read 178 0 63 db file scattered read 6195 0 63 file open 3 0 63 SQL*Net message to client 244859 0 63 SQL*Net more data to client 149282 0 63 SQL*Net message from client 244858 0 I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports. I'm about to raise a TAR on this one, but any pointers before that would be useful. TIA, Mandar. Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/html; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS 63 latch free 2 2 63 db file sequential read 178 0 63 amp;nb! ! ! ! sp;db file scattered read 6195 0 63 file open 3 0 63 SQL*Net message to client 244859 0 63 SQL*Net more data to client149282 ! ! ! ! ; 0 63 SQL*Net message from client 244858 0 I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports. I'm about to raise a TAR on this one, but any pointers before that would be useful. TIA, Mandar. Get Your Private, Free E-mail from Indiatimes at =Arial size=2gt;http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.inhttp://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051
Re: queries on licences
1. What is the typical ratio of users to licenses in a concurrent licensing scheme? Oracle no longer has a concurrent user licensing scheme. Its either named user or UPU (which has also changed to no. of CPUs, I believe) 2. How does Oracle keep track of the licenses? You can keep track of user licenses by setting LICENSE_MAX_USERS init parameter. However, this will not track your named user licenses if one Oracle username is used by multiple physical users. 3. Suppose someone logs in and does not do any transaction, will it be counted as a used license? As said above, concurrent user licensing is no longer used. 4. Does Oracle logs you off if you don't do any transaction for time? Is there a time in the day like mid-night when the users are automatically logged off? There is no such time for automatic logoff. If you want to log idle users off you can use profiles with the IDLE_TIME parameter. You can check Oracle's licensing policy on the Oracle site (if I remember correctly) HTH, Mandar. Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_20011011191633294702567 Content-Type: text/html; charset=us-ascii BLOCKQUOTE style=BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px PBRBR1. What is the typical ratio of users to licenses in a concurrent licensing scheme?BR/P POracle no longer has a concurrent user licensing scheme. Its either named user or UPU (whichnbsp;has also changed to no. of CPUs, I believe) BRBR2. How does Oracle keep track of the licenses?BR/P PYou can keep track of user licenses by setting LICENSE_MAX_USERS init parameter. However, this will not track your named user licensesnbsp;if one Oracle username is used by multiple physical users.BRBR3. Suppose someone logs in and does not do any transaction, will it be counted as a used license?BR/P PAsnbsp;said above, concurrent user licensing is no longer used./P P4. Does Oracle logs you off if you don't do any transaction for time? Is there a time in the day like mid-night when the users are automatically logged off?BR/P PThere is no such time for automatic logoff. If you want to log idle users off you can use profiles with the IDLE_TIME parameter./P PYou can check Oracle's licensing policy on the Oracle site (if I remember correctly)/P PHTH,/P PMandar./P/BLOCKQUOTE hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/font/a/bbrBuy Music, Video, CD-ROM, Audio-Books and Music Accessories from A href=http://www.planetm.co.in;http://www.planetm.co.in/A --=_MAILER_ATTACH_BOUNDARY1_20011011191633294702567-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete 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).
