Re: Order rows
Jared, Oracle gives you a chance to use it. And it's very common to use. Isn't it? There is no conception of phisical location in realational theory. I'm not saying that pure theory is the best for practical use, though. :) BTW, Oracle stores ROWID in indexes... instead of primary key (which is stored only in case of indexes on IOT). Of course, that's speed up access, but... Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 11:50 PM In fact ROWID is the Oracle implementation and against RDBMS rules. :) ROWID gives information about phisical location of the record. That MUST NOT be in PURE relational database. Nowadays, there is no pure relational database implementation. That doesn't really count, as Oracle does not store that as part of the data. It is generated from datafile and datablock information at runtime. Jared -- 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: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL Loader
Hi friends, I have to load a input file into a oracle table . The table has only one field which will store each line as a single row . The order of the file is very important for my further process. sometimes the order of the file is changed when it is stored in the file. there is no sequence or unique id present in the file. Can you tell me why this ordering is being changed .when u re-run the sql loader , it is working properly. not able to simulate why this is happening . Is it any way to specify in loader just fetch a single row from the file and write it before fetching next. Best Regards, Shankar This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
RE: Calling an External Java Class from PL/SQL
You can call them by building a wrapper in the Java in DB and have it call the external procedure. You can expose that internal Java to the PL/SQL RTFM and get back with any further queries. The Oracle Java documents have quite a bit of useful info on this Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Vadim Gorbounov [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 16-05-2002 05:14 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:RE: Calling an External Java Class from PL/SQL Hi, Pete, PL/SQL has an interface to built-in Oracle JServer. You can load external class here and call it, but at this point class is not external anymore, right? If you need access to the class on a different JVM - true external class, Java provides you RMI, EJB, Corba interfaces. Oracle implements these Java specifications in JServer, that is you can develop Java classes providing access to remote (i.e. external) classes, deploy these classes to Oracle JServer and create PL/SQL wrapper. URLs http://technet.oracle.com/docs/products/oracle9i/doc_library/901_doc/java.90 1/a90210/toc.htm http://technet.oracle.com/docs/products/oracle9i/doc_library/901_doc/java.90 1/a90187/toc.htm http://technet.oracle.com/docs/products/oracle9i/doc_library/901_doc/java.90 1/a90188/toc.htm HTH Vadim -Original Message- Sent: Wednesday, May 15, 2002 1:53 PM To: Multiple recipients of list ORACLE-L We have a developer who has asked this question. How do you call an external java class from a pl/sql stored procedure? You can stick my knowledge of java in a thimble. Can this be done, if so can you point me to a url with an example or two. Thanks, Pete = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter Barnett 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: Vadim Gorbounov 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). STG48418 Description: Binary data
reset database
Title: RE: [Q] what difference between count(0), count(1) and Hi, Please can someone explain the consequences of "reset database" in rman?? Does this mean all the previous backups are lost??Is there a way around ?? Any help/advice would be greatly appreciated... Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. **
Re: SQL Loader
Hello Shankar, You can'n rely on rowid in your case. It would be much better to add additional field into your table and write rownum to it. You can generate record number automatically by SQL Loader. Here is simple example how to do it: LOAD DATA INFILE 'yourfile.dat' BADFILE 'load.bad' INSERT INTO TABLE YOUR_TABLE trailing nullcols ( R_NUMBER RECNUM, TABLE_ROW) you can also use SEQUENCE keyword instead of RECNUM. Thursday, May 16, 2002, 2:53:21 PM, you wrote: RCCC Hi friends, RCCC I have to load a input file into a oracle table . The table has only RCCC one field which will store each line as a single row . The order of the file RCCC is very important for my further process. sometimes the order of the file is RCCC changed when it is stored in the file. there is no sequence or unique id RCCC present in the file. RCCC Can you tell me why this ordering is being changed .when u re-run RCCC the sql loader , it is working properly. not able to simulate why this is RCCC happening . RCCC Is it any way to specify in loader just fetch a single row from the RCCC file and write it before fetching next. RCCC Best Regards, RCCC Shankar -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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).
I/O EVENTS
Hi Can anybody explain the events like SQL*Net message from client,rdbms ipc message PX Idle Wait ,slave wait ... Can I assume a i/o bottleneck from the following statistics as most of the i/o events are having high wait time. select * from v$system_event order by TIME_WAITED; The last few entries are as follows. EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT --- SQL*Net more data to client 888137 0 12365 0.013922402 db file parallel write 24450 17606 7.200818 db file scattered read 78230 18336 2.34385786 db file sequential read 27779 0 55490 1.99755211 control file parallel write 27194 0 70593 2.59590351 log file sync 29700 1 145295 4.89208754 log file parallel write 30511 2 146311 4.7953525 io done 35551 567 270796 7.61711344 smon timer 278 273 8404285 30231.241 pmon timer 27350 27349 8413237 307.613784 SQL*Net message from client 498526 0 17655561 35.415527 rdbms ipc message 112693 80775 33193981294.552288 PX Idle Wait164145 164139 33650049205.001974 slave wait 433146 392872 4083965194.2861091 Regards Bhulu __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: S B 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 CONVERT A LARGE VARCHAR2 TO NUMBER???
BDY.RTF Description: RTF file
IMPORTING FROM 8.1.6 to 8.1.5
hi all i am facing problem , when i am importing oracle 8.1.6 backup dmp file into oracle 8.1.5 database. pls give solution. bye srinivas rao . Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience
Compressing Export Dumps
Hi all,
My export dumps are too big (80 GB) for my filesystem and I'm looking for a
way
to compress them on the fly -ie without taking *.dmp to disk first but
straight *.dmp.gz
Anybody with an idea on how to archive this ?
Thanking you,
---
CSW
æ¬zǶ¨}ø©ND ±@Bm§ÿðÃ
+iöªrºØh(¦¢u¶¥xDO¢m©lÙÜ¢èjТ·#^·
+'«¾' ³Î|ç9ÓE9óüçNuI©Ãè( ©b~ç£înX{^®wiÇ ²ÏÌj)bâ²Û¡ 9Q2¢êìzWß®+j)b
+ b²Û ֧Ƣg¬±¨ ¶â²Ñ®®çÚµÈÉÊ^pNÊ^X§ËËFº»Ø§¶¦zËàN
+§r[y©bç(Ö¢)àPÔä@± ¢»azv¦zæj)b
+b²Ü¨»§¶Úz·¦¢÷~ºb¦k¥² Ûaxq =Ê©Ý~趫wè®fåG¬¹» ®â
RE: reset database
Title: RE: [Q] what difference between count(0), count(1) and
sorry
I usually do- I cant access the web at the mo thats all- but thanks for
this..
-Original Message-From: Peter Gram
[mailto:[EMAIL PROTECTED]]Sent: 16 May 2002
10:05To: Malik, FawziaCc:
[EMAIL PROTECTED]Subject: Re: reset
database
pls. next time read the manual. if the manual gives
problem then describe the problem !
Oracle8i Recovery Manager User's Guide and
ReferenceRelease 2 (8.1.6)Part Number A76990-01
Library
Product
Contents
Index
Recovery
Manager Command Syntax, 36 of 50
reset
Syntax
Purpose
To create a new database incarnation record in the recovery
catalog. RMAN considers the new incarnation as the current incarnation of the
database. All subsequent backups and redo log archiving operations performed
by the target database will be associated with the new database incarnation.
Requirements
Execute reset
database only at the RMAN prompt.
You must be using a recovery catalog.
You must issue a reset database command
before you can use RMAN with a target database that has been opened with the
RESETLOGS option. If you do not, then RMAN refuses to access the recovery
catalog because it cannot distinguish between a RESETLOGS operation and an
accidental restore of an old control file. The reset database command gives
confirmation to RMAN that you issued a RESETLOGS command.
Keywords
and Parameters
to incarnation
primary_key
changes the incarnation that RMAN considers to be current to
an older incarnation. This option is useful in the rare circumstance in
which you want to undo the effects of a RESETLOGS by restoring backups
of a prior incarnation of the database.
Specify the primary key of the DBINC record for the database
incarnation. Obtain the key value using the list incarnation of databasecommand. After you issue the reset
database to incarnation command, issue restore and recover commands to restore the database files from the
prior incarnation and recover them.
Examples
Resetting a Database
After RESETLOGS
The following example resets a database after performing
incomplete media recovery: run {
allocate channel dev1 type disk;
set until logseq 1234 thread 1;
restore database skip tablespace readonly;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS";
release channel dev1;
}
reset database;
Resetting an Old
Incarnation
The following command makes an old incarnation of database PROD1
current again: # obtain primary key of old incarnation
list incarnation of database prod1;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CURReset SCNReset Time
--- --- --- -- ----- --
1 2 PROD1 1224038686 NO 102-JUL-98
1 582 PROD1 1224038686 YES5972710-JUL-98
shutdown immediate;
# reset database to old incarnation
reset database to incarnation 2;
# recover it
run {
allocate channel dev1 type disk;
restore controlfile;
startup mount;
restore database;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS";
release channel dev1;
}
Related
Topics
"list"
"restore"
"recover"
Malik, Fawzia wrote:
Hi,
Please can someone explain the consequences of "reset database" in
rman?? Does this mean all the previous backups are lost??Is there a way
around ??
Any help/advice would be greatly appreciated...
Rgds
Fawzia**Information
in this email is confidential and may be privileged. It is intended for
the addressee only. If you have received it in error,please notify the
sender immediately and delete it from your system. You should not
otherwise copy it, retransmit it or use or disclose itscontents to
anyone. Thank you for your
co-operation.**--
/regards
Peter Gram
Mobil : +45 2527 7107
Fax : +45 4466 8856
Miracle A/S
Kratvej 2
2760 Måløv
http://miracleas.dk
/*
The process of preparing programs for a digital computer is especially
attractive, not only because it can be economically and scientifically
rewarding, but also because it can be an aesthetic experience much like
composing poetry or music
Donald Knuth
*/
prodicon.gif
Description: GIF image
The previous attachment was filtered out by the
RE: SQL Loader
Hi friends, I have to load a input file into a oracle table . The table has only one field which will store each line as a single row . The order of the file is very important for my further process. Perhaps you should re-read a paper published in 1970 in 'Communications of the ACM' by an Edward Codd. IMHO you do not need a relational database at this stage of your process. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: I/O EVENTS
Can I assume a i/o bottleneck from the following select * from v$system_event order by TIME_WAITED; No. Wait events may only make up a small amount of processing that Oracle is doing for you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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 refresh fast a MV from another MV?
Hi DBA's, I have the following problem, on a node I have some tables an a MV_master (done with FAST refresh) build over these tables. I have to bribg this MV_master on the DB servers but It's not possible to do this using snapshot log on MV_master and building the MV_slave's using the refresh FAST (you get the ORA-12015); so I could use only the COMPLETE refresh but it's too long for me. How can I workaround this problem? Any suggestion will be greatly appreciated! Thanks in advance to all. Francesco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Francesco Cantisano 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: Compressing Export Dumps
BDY.RTF Description: RTF file
RE: I/O EVENTS
Hi, far better than getting individual descriptions is to research for yourself (this isn't an RTFM message honestly). Good sources of information for this subject are Anjo Kolk's excellent document on wait events and enqueues (http://www.dbatoolbox.com/WP2001/dbamisc/events.pdf) and the Oracle documentation set, specifically the Oracle Server Reference (Appendix A I think), wait events. Cheers, Mike -Original Message- Sent: 16 May 2002 10:33 To: Multiple recipients of list ORACLE-L Hi Can anybody explain the events like SQL*Net message from client,rdbms ipc message PX Idle Wait ,slave wait ... Can I assume a i/o bottleneck from the following statistics as most of the i/o events are having high wait time. select * from v$system_event order by TIME_WAITED; The last few entries are as follows. EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT --- SQL*Net more data to client 888137 0 12365 0.013922402 db file parallel write 24450 17606 7.200818 db file scattered read 78230 18336 2.34385786 db file sequential read 27779 0 55490 1.99755211 control file parallel write 27194 0 70593 2.59590351 log file sync 29700 1 145295 4.89208754 log file parallel write 30511 2 146311 4.7953525 io done 35551 567 270796 7.61711344 smon timer 278 273 8404285 30231.241 pmon timer 27350 27349 8413237 307.613784 SQL*Net message from client 498526 0 17655561 35.415527 rdbms ipc message 112693 80775 33193981294.552288 PX Idle Wait164145 164139 33650049205.001974 slave wait 433146 392872 4083965194.2861091 Regards Bhulu This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DBA Studio - Used (M) query
Can someone please tell me how the value displayed in the Used M column is calculated/dervied. I'm running DBA Studio (stand alone) and I'm trying to relate it back to DBA view data. Any help would be much appreciated. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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 refresh fast a MV from another MV?
Hi DBA's,=0D I have the following problem,=0D on a node I have some tables an a MV_master (done with FAST refresh) build = over these tables.=0D I have to bribg this MV_master on the DB servers but =0D It's not possible to do this using snapshot log on MV_master and building t= he MV_slave's using the refresh FAST (you get the ORA-12015); so I could us= e only the COMPLETE refresh but it's too long for me.=0D How can I workaround this problem?=0D =0D Any suggestion will be greatly appreciated!=0D Thanks in advance to all.=0D =0D Francesco=0D Perhaps you should consider moving files around - transportable tablespaces spring to mind. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Compressing Export Dumps
Hello Simon, You can make it like this: mkfifo yourfifo gzip yourfifo outfile.dmp.gz exp ... file=yourfifo rm yourfifo Thursday, May 16, 2002, 4:38:40 PM, you wrote: SW Hi all, SW My export dumps are too big (80 GB) for my filesystem and I'm looking for a SW way SW to compress them on the fly -ie without taking *.dmp to disk first but SW straight *.dmp.gz SW Anybody with an idea on how to archive this ? SW Thanking you, SW --- SW CSW -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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
Client is not accepting to use the utl_file for reading data from the file. i have to fetch each line from the file and process it for storing the information in various tables. so apart from sql loader can u suggest other method (excluding utl_file) for doing the same operation. Best Regards, Shankar -Original Message- Sent: Thursday, May 16, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Hi friends, I have to load a input file into a oracle table . The table has only one field which will store each line as a single row . The order of the file is very important for my further process. Perhaps you should re-read a paper published in 1970 in 'Communications of the ACM' by an Edward Codd. IMHO you do not need a relational database at this stage of your process. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Re: Script for deleting old archive logs from NT
Arun, Here are a couple of files (a .bat and .sql) that let me maintain a constant number of Archived Redo Logs online. The first batch file executes SQL*Plus to produce two other batch files to delete the excess logs and move some others, maintaining, in this case about 450 logs. It ran every hour - smooth as silk. Hope it helps. Archived_Redo_Logs_MoveDelete_main.bat --- Set ORACLE_SID=orcl SQLPlus internal @C: \Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_sub.sql Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat Archived_Redo_Logs_MoveDelete_sub.sql Set FeedBack Off Set LineSize 200 Set PageSize 0 Set TrimSpool On -- Delete backup archived redo logs if there are over 450 total arc`d logs. Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) || '.LOG' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 601 Minus Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) || '.LOG' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 451 ; Spool Off -- Move some arc`d redo logs to backup if there are over 200 in Archive directory. Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T: \Oracle\OraData\orcl\Archive_Backup' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 401 Minus Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T: \Oracle\OraData\orcl\Archive_Backup' From ( Select To_Char(Sequence#,'0') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum 201 ; Spool Off Exit Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Arun Chakrapani To: Multiple recipients of list ORACLE-L ArunC@1800FLO[EMAIL PROTECTED] WERS.comcc: Sent by: Subject: Script for deleting old archive logs [EMAIL PROTECTED]from NT om 05/15/2002 04:43 PM Please respond to ORACLE-L Does anybody has script to delete old archive logs on NT when the disk reaches certain percentage. Please let me know Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: IMPORTING FROM 8.1.6 to 8.1.5
On Thu, May 16, 2002 at 01:53:20AM -0800, Pati Srinivas Rao wrote: hi all i am facing problem , when i am importing oracle 8.1.6 backup dmp file into oracle 8.1.5 database. You have to use the exp from the lowest ora version, 8.1.5, on the 8.1.6 host. Note:132904.1 has the compatibility matrix. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Deadlock ORA-60
[EMAIL PROTECTED] wrote: kick the power cable to your server... Could work. There a story (urban legend?) about a Sybase server at a brokerage house in NYC that would not return correct results for an important SQL statement. They'd been working on it for weeks with Sybase. don't know about that one, but i was involved in one where the backup wouldn't run at night. i'd run the thing in the morning and it worked fine but it wouldn't work at night. seems the cleaning crew needed another plug to vacuum the server room and just unplugged that server every night, then plugged it back in when they left. and the SA on the box couldn't tell me that was happening.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Software is mind work. Having the right frame of mind is essential. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: Compressing Export Dumps
If you are on Unix, you can pipe the export into a split command and break the file into multiples and compress on the fly. There's a note on metalink about it (note 30528.1) Also, I *think* in 8.1.7 you can specify the size and names of the export files, so that Oracle will automatically break the large file into several smaller ones. Check the Utilities manual |+--- || | || | || [EMAIL PROTECTED]| || o.ug | || | || 05/16/2002 | || 05:38 AM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Compressing Export Dumps| | Hi all, My export dumps are too big (80 GB) for my filesystem and I'm looking for a way to compress them on the fly -ie without taking *.dmp to disk first but straight *.dmp.gz Anybody with an idea on how to archive this ? Thanking you, --- CSW -- 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:IMPORTING FROM 8.1.6 to 8.1.5
Hi, if you have a link between the 8.1.5 DB and the 8.1.6 DB you should run exp USER/PASSW@8_1_6_SID from the 8.1.5 environment to create an 8.1.5 compatible export file; then run the Inport as you usually do. Bye Francesco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Francesco Cantisano 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: OT: awk problem
thanks everyone for the help. =)
Sergey V Dolgov wrote:
Hello Maria,
You should look at your file in some hex editor, this symbols might be
symbols with hex code 0A or 0D 0A (it means new line).
So you have to set correct RS value.
Wednesday, May 15, 2002, 3:28:38 PM, you wrote:
MAVdlV
MAVdlV I'm hoping that there are awk gurus also in this list.my problem:
MAVdlV when I use awk, it cuts off a part of the line
MAVdlV $ cat -n *0509*|awk '{if
MAVdlV ((substr($0,34,2) == 1I)(length($0)) == 107) {print $0}}'|sort
MAVdlV 1248 110
MAVdlV 2002050990910931381IF110 201 9091PCORAJ
MAVdlV S1 1
MAVdlV 1.7600MD
MAVdlV 1249 110
MAVdlV 2002050990920931511IF110 201 9092PCORAJ
MAVdlV S1 1
MAVdlV 1.7800MD
MAVdlV 1250 110
MAVdlV 2002050990930931591IF110 201 9093PCORAJ
MAVdlV S2 2
MAVdlV 1.8000MD
MAVdlV i am supposed to get
MAVdlV a longer line
MAVdlV getting line 1248 as
MAVdlV an example:
MAVdlV $ grep 110 2002050990910931381IF110
MAVdlV 201 9091PCOR *0509*
MAVdlV 110 2002050990910931381IF110
MAVdlV 201 9091PCORAJ
MAVdlV S1 1
MAVdlV 1.7600MD C0905L
MAVdlV if I look at the file in windows notepad, this same line has 4box characters
MAVdlV between 1.7600MD and C0905L
MAVdlV I am not sure what those 4 box characters are.
MAVdlV help.
MAVdlV Thanks.
MAVdlV =)
MAVdlV --
MAVdlV Never attribute to malice that which can be adequately explained by
MAVdlV stupidity.
MAVdlV Maria Aurora VT de la Vega OCP
MAVdlV Database Specialist
MAVdlV Philippine Stock Exchange, Inc.
MAVdlV --
MAVdlV Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Best regards,
Sergeymailto:[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sergey V Dolgov
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).
--
Never attribute to malice that which can be adequately explained by stupidity.
Maria Aurora VT de la Vega OCP
Database Specialist
Philippine Stock Exchange, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maria Aurora VT de la Vega
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: Compressing Export Dumps
Sergey, Thanks for the mkfifo idea. I've also come across mknod myfifo p;compess myinfo myinfo.Z I'm looking at the two options, yet to ascertain whether second method works with gzip. Do you know of any known troubles (Block/File corruption) with first method ? Do I have to rm yourinfo or I can re-use the piping file ( as in ascript) ? Thanks once again, Simon -Original Message- Sent: Thursday, May 16, 2002 2:48 PM To: Multiple recipients of list ORACLE-L Hello Simon, You can make it like this: mkfifo yourfifo gzip yourfifo outfile.dmp.gz exp ... file=yourfifo rm yourfifo Thursday, May 16, 2002, 4:38:40 PM, you wrote: SW Hi all, SW My export dumps are too big (80 GB) for my filesystem and I'm looking for a SW way SW to compress them on the fly -ie without taking *.dmp to disk first but SW straight *.dmp.gz SW Anybody with an idea on how to archive this ? SW Thanking you, SW --- SW CSW -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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: Simon Waibale 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: Compressing Export Dumps
Yes, 8.1.7 exp has 2 new parameters: filesize=53687058420# 50gb file=file1.dmp,file2.dmp # 2 files It helps when exporting a 1tb db. Using direct=y, gets it done in 4hours. FWIW, Gene [EMAIL PROTECTED] 05/16/02 09:13AM If you are on Unix, you can pipe the export into a split command and break the file into multiples and compress on the fly. There's a note on metalink about it (note 30528.1) Also, I *think* in 8.1.7 you can specify the size and names of the export files, so that Oracle will automatically break the large file into several smaller ones. Check the Utilities manual |+--- || | || | || [EMAIL PROTECTED]| || o.ug | || | || 05/16/2002 | || 05:38 AM | || Please | || respond to | || ORACLE-L | || | |+--- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Compressing Export Dumps| | Hi all, My export dumps are too big (80 GB) for my filesystem and I'm looking for a way to compress them on the fly -ie without taking *.dmp to disk first but straight *.dmp.gz Anybody with an idea on how to archive this ? Thanking you, --- CSW -- 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: Gene Sais 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: Compressing Export Dumps
[EMAIL PROTECTED] wrote: If you are on Unix, you can pipe the export into a split command and break the file into multiples and compress on the fly. There's a note on metalink about it (note 30528.1) Also, I *think* in 8.1.7 you can specify the size and names of the export files, so that Oracle will automatically break the large file into several smaller ones. i'm doing that with an 8.1.5 instance right now. works seamlessly. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Software is mind work. Having the right frame of mind is essential. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: Dba_tab_modifications question
So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 4:23 AM Prakash, My understanding is that the updation of counts for MONITORed tables is done without using latching, so that normal DML is not held up by some additional latches. This will explain the small difference that you are seeing, i.e. the counting of some INSERTs were missed due to race conditions that could have otherwise been prevented by latches. Am I as clear as mud or what! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: Dba_tab_modifications question Hey Prakash, I never knew about that dictionary table, so I looked it up and found... These views describe tables that have been modified since the last time table statistics were gathered on them. The views are populated only for tables with the MONITORING attribute. They are not populated immediately, but after a time lapse (usually 3 hours). Perhaps that explains the diff. Check it out. Chris -Original Message- Sent: Wednesday, May 15, 2002 1:03 PM To: Multiple recipients of list ORACLE-L Hello, Oracle 8.1.6 on HP-UX 11.0 WFM_ADMIN@VGRAFO select num_rows,last_analyzed,monitoring from user_tables where table_name = 'NOTES_LOG'; NUM_ROWS LAST_ANAL MON --- --- 1585697 14-MAY-02 YES Last night, Informatica inserted rows into this table. 1 select inserts,updates,deletes from dba_tab_modifications 2* where table_name = 'NOTES_LOG' WFM_ADMIN@VGRAFO / INSERTSUPDATESDELETES ---- --- 6509 0 0 WFM_ADMIN@VGRAFO select count(*) from notes_log; COUNT(*) -- 1592488 The difference between yesterday's and today's count is 6791 which does not match the number in dba_tab_modifications. Does this mean that I cannot rely on dba_tab_modifications? TIA Prakash -- -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access /
RE: reset database
Fawzia - Why do you think you need to perform a reset database? Have you performed an incomplete recovery on the database (opened it with RESETLOGS option)? If so, you have created a new incarnation of your database. Therefore, none of your backups are valid because they occurred prior to RESETLOGS. That is the reason for the reset database command. Obviously what you should do as soon as possible after RESETLOGS is perform a full backup. This backup will be valid (assume it is performed correctly). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 3:49 AM To: Multiple recipients of list ORACLE-L Hi, Please can someone explain the consequences of reset database in rman?? Does this mean all the previous backups are lost??Is there a way around ?? Any help/advice would be greatly appreciated... Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- 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: Compressing Export Dumps
If you are on Unix, you can pipe the export into a split command and break the file into multiples and compress on the fly. There's a note on metalink about it (note 30528.1) Easier of you split the zipped result: mknod /tmp/dump p; gzip --fast /tmp/dump | split -b $((1024*1024*1024)) date +dump-%Y%m%d; Gives a set of 1GB files as output. Simpler to manage since they all have the same size (whatever you set -b to) w/ a runt file at the end. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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).
Can a database go down because of too few locks?
Can anyone tell me if an ops database can be brought down because of too few locks being allocated for the database? Thanks, Bryan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SMON reports Dead Transactions - Not seen this before
Murali I have seen them occasionally in our 8.1.7 database, generated during a database recovery. The recoveries in question complete normally so I haven't researched the details. Mike Hand Polaroid Corp -Original Message- Sent: Wednesday, May 15, 2002 9:48 PM To: Multiple recipients of list ORACLE-L Hello list I am for the first time seeing a message like this in the SMON trace file. --- Using 1 slaves for 1 dead transactions Recovered xid: 0x000e.00a.4971 in 20 milliseconds; nchk=0, size=2327 --- I have not seen this message before, any ideas ... Murali Vallath -- 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: Dba_tab_modifications question
Chris, Do you know anything about monitoring and gathering stale statistics on table partitions? I am able to monitor and gather stale statistics on partitioned tables at the table level but don't seem to be doing so at the partition level. I can't figure out how to alter my partitions to put them in monitor mode. Have you ever done that? The documentation and metalink only shows the command for altering the table, there seems to be no partition clause for that command. I've guessed at various syntaxes for the clause and none of them work. Thanks, Cherie Machler Oracle DBA Gelco Information Network Grabowy, Chris To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cgrabowy@fcg. cc: com Subject: RE: Dba_tab_modifications question Sent by: [EMAIL PROTECTED] om 05/16/02 09:23 AM Please respond to ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 4:23 AM Prakash, My understanding is that the updation of counts for MONITORed tables is done without using latching, so that normal DML is not held up by some additional latches. This will explain the small difference that you are seeing, i.e. the counting of some INSERTs were missed due to race conditions that could have otherwise been prevented by latches. Am I as clear as mud or what! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM
RE: Compressing Export Dumps / WinZip
I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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).
offtopic - unix command
Hello, Apologies for the slightly off-topic listing, but I know there are several unix command gurus out there. (Bambi?) Oracle 8.1.6 on Solaris 2.7. I am trying to execute an rsh command against another unix server; the actual command is rsh pnas1 chkpntmk oradata ckpt1 if [ $? != 0 ]; then... pnas1 is name of other server; chkpntmk is command, rest are args. As you can see, I am checking the $? parameter to test for success/failure of the command. However, I think this is the return code for the rsh command, not the chkptmk command which is executed on the other server. When the chkpnt command fails, I still receive a 0 indicating success, because rsh executed OK. Is there a way to check for the success/failure of the actual remote command when using rsh? Thnaks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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: reset database
We've found that when we use RMAN to recover an exact clone of a database with the same name as the original to an alternate host (perhaps as test database or fallback database while upgrading) we have to reset the database in the RMAN catalog if both databases are in the same RMAN catalog. This has happened to us every time we recover to an alternate host using the same database name. We have gotten around this problem by export the RMAN catalog contents for the original database and importing it into a different catalog. Then we don't have the incarnation problem. Cherie Machler Oracle DBA Gelco Information Network DENNIS WILLIAMS DWILLIAMS@LIFE To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] TOUCH.COMcc: Sent by: Subject: RE: reset database [EMAIL PROTECTED] m 05/16/02 09:28 AM Please respond to ORACLE-L Fawzia - Why do you think you need to perform a reset database? Have you performed an incomplete recovery on the database (opened it with RESETLOGS option)? If so, you have created a new incarnation of your database. Therefore, none of your backups are valid because they occurred prior to RESETLOGS. That is the reason for the reset database command. Obviously what you should do as soon as possible after RESETLOGS is perform a full backup. This backup will be valid (assume it is performed correctly). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 3:49 AM To: Multiple recipients of list ORACLE-L Hi, Please can someone explain the consequences of reset database in rman?? Does this mean all the previous backups are lost??Is there a way around ?? Any help/advice would be greatly appreciated... Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
RE: Dba_tab_modifications question
Chris, I remember from Metalink that you cannot use the 'gather stale' option in dbms_stats.gather_schema_stats. There is a bug in 8i and supposed to be fixed in 9i. So they still advise to run a job daily to gather the correct statistics. Thank you Gopalakrishnan, Kirti and John for clearing my doubt! Prakash -Original Message- Sent: Thursday, May 16, 2002 10:23 AM To: Multiple recipients of list ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 4:23 AM Prakash, My understanding is that the updation of counts for MONITORed tables is done without using latching, so that normal DML is not held up by some additional latches. This will explain the small difference that you are seeing, i.e. the counting of some INSERTs were missed due to race conditions that could have otherwise been prevented by latches. Am I as clear as mud or what! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: Dba_tab_modifications question Hey Prakash, I never knew about that dictionary table, so I looked it up and found... These views describe tables that have been modified since the last time table statistics were gathered on them. The views are populated only for tables with the MONITORING attribute. They are not populated immediately, but after a time lapse (usually 3 hours). Perhaps that explains the diff. Check it out. Chris -Original Message- Sent: Wednesday, May 15, 2002 1:03 PM To: Multiple recipients of list ORACLE-L Hello, Oracle 8.1.6 on HP-UX 11.0 WFM_ADMIN@VGRAFO select num_rows,last_analyzed,monitoring from user_tables where table_name = 'NOTES_LOG'; NUM_ROWS LAST_ANAL MON --- --- 1585697 14-MAY-02 YES Last night, Informatica inserted rows into this table. 1 select inserts,updates,deletes from dba_tab_modifications 2* where table_name = 'NOTES_LOG' WFM_ADMIN@VGRAFO / INSERTSUPDATESDELETES ---- --- 6509 0 0 WFM_ADMIN@VGRAFO select count(*) from notes_log; COUNT(*) -- 1592488 The difference between yesterday's and today's count is 6791 which does not match the number in dba_tab_modifications. Does this mean that I cannot rely on dba_tab_modifications? TIA Prakash -- -- 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
ora-04031 and x$ksmlru
Oracle 8.0.5 Solaris 2.6 shared_pool_reserved_min_alloc5K shared_pool_reserved_size 6656000 shared_pool_size 13312 total sga size is 597 megs I'm fighting a particularly difficult ora-04031 error. The error can be reproduced easily with several queries, including this one: select a.agreement fromadvdb.ad a, advdb.pub p where p.adno=a.adno and p.vno=a.vno; ERROR: ORA-04031: unable to allocate 340032 bytes of shared memory (shared pool,unknown object,cursor work he,kllcqc:kllcqslt) In reviewing metalink article 146599.1, it says that I can determine allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation This query (select * from x$ksmlru where ksmlrsiz0) returned this: ADDR INDXINST_ID KSMLRCOM KSMLRSIZ KSMLRNUM -- -- -- -- KSMLRHON KSMLROHV KSMLRSES -- 82B8 0 1 sort area 4152 8 0 A42C4048 82FC 1 1 kafco : qkacol 4292 1032 insert into pub (adno,pubno,... 1730627729 A427B7E0 8340 2 1 kllcqc:kllcqslt 324100 13311 0 A438EC84 8384 3 1 kllcqc:kllcqslt 326124 9590 0 A433A828 83C8 4 1 kllcqc:kllcqslt 376908 6326 0 A438EC84 840C 5 1 kllcqc:kllcqslt 384268 10731 0 A433A828 I notice several references in the column KSMLRCOM to kllcq:kllcqslt, which matches information from the error message. However, I have no idea what that means. Can the information from this column in some way help me figure out what the problem is? Since my request_failures is 41 and my last_failure_size 384,268 and shared_pool_reserved_min_alloc is 5k, I believe I need to increase shared_pool_size. I'm currently begging for permission to bounce the database, but the timing isn't great . . . I've flushed shared_pool several times,to no avail. Anything I can do till I can bounce the database?? Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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: Compressing Export Dumps / WinZip
Patrice - That would be correct. If you run export interactively, the prompt that is provided is compress extents (y/n). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Compressing Export Dumps / WinZip
there is a BIG difference between the COMPRESS=Y parameter on an export and compressing a file! the parameter changes the create table statement placed in the export file so that the initial extent is large enough to hold the entire table. It does NOT affect the size of the export dump file in the least. Compressing a file at the OS level removes waste space and (usually) significantly reduces the file size |+- || | || | || [EMAIL PROTECTED]| || -mpo.gc.ca | || | || 05/16/2002 | || 11:08 AM | || Please respond | || to ORACLE-L| || | |+- | || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: RE: Compressing Export Dumps / | | WinZip | | I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Dba_tab_modifications question
Precisely :) - Kirti -Original Message- Sent: Thursday, May 16, 2002 9:23 AM To: Multiple recipients of list ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA -- 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).
RE: Compressing Export Dumps / WinZip
Doesn't it mean that all rows are compressed into 1 extent? -Original Message- Sent: Thursday, May 16, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Subject:RE: Compressing Export Dumps / WinZip I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Taylor, Shirley 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: Compressing Export Dumps / WinZip
That's right. compress=y is the export default; it causes all extents of an object to be combined into one. --- Boivin, Patrice J [EMAIL PROTECTED] wrote: I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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!? LAUNCH - Your Yahoo! Music Experience http://launch.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 mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dba_tab_modifications question
Thanks Prakash. I was poking around in Metalink and discovered it. Luckily, I'm on 9i, so I will be checking out this feature. -Original Message- Sent: Thursday, May 16, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Chris, I remember from Metalink that you cannot use the 'gather stale' option in dbms_stats.gather_schema_stats. There is a bug in 8i and supposed to be fixed in 9i. So they still advise to run a job daily to gather the correct statistics. Thank you Gopalakrishnan, Kirti and John for clearing my doubt! Prakash -Original Message- Sent: Thursday, May 16, 2002 10:23 AM To: Multiple recipients of list ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 4:23 AM Prakash, My understanding is that the updation of counts for MONITORed tables is done without using latching, so that normal DML is not held up by some additional latches. This will explain the small difference that you are seeing, i.e. the counting of some INSERTs were missed due to race conditions that could have otherwise been prevented by latches. Am I as clear as mud or what! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: Dba_tab_modifications question Hey Prakash, I never knew about that dictionary table, so I looked it up and found... These views describe tables that have been modified since the last time table statistics were gathered on them. The views are populated only for tables with the MONITORING attribute. They are not populated immediately, but after a time lapse (usually 3 hours). Perhaps that explains the diff. Check it out. Chris -Original Message- Sent: Wednesday, May 15, 2002 1:03 PM To: Multiple recipients of list ORACLE-L Hello, Oracle 8.1.6 on HP-UX 11.0 WFM_ADMIN@VGRAFO select num_rows,last_analyzed,monitoring from user_tables where table_name = 'NOTES_LOG'; NUM_ROWS LAST_ANAL MON --- --- 1585697 14-MAY-02 YES Last night, Informatica inserted rows into this table. 1 select inserts,updates,deletes from dba_tab_modifications 2* where table_name = 'NOTES_LOG' WFM_ADMIN@VGRAFO / INSERTSUPDATESDELETES ---- --- 6509 0 0 WFM_ADMIN@VGRAFO select count(*) from notes_log; COUNT(*) -- 1592488 The difference between yesterday's and today's count is 6791 which does not match the number in dba_tab_modifications. Does this mean that I cannot rely on dba_tab_modifications? TIA Prakash -- -- 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
RE: Can a database go down because of too few locks?
Not that I'm aware of are you thinking about the enqueue_resources parameter? It is dynamically adjusted by Oracle as needed. RF -Original Message- Sent: Thursday, May 16, 2002 10:38 AM To: Multiple recipients of list ORACLE-L Can anyone tell me if an ops database can be brought down because of too few locks being allocated for the database? Thanks, Bryan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Waits 8i vs. 9i??
Has anyone noticed that the number of event waits in 9i seems much much higher than in 8i. This is the number, not the time waited mind you, so this doesn't really have performance implications. I'm just wondering if this betrays some internal code changes in the way Oracle is reporting these events. Thoughts RF -- 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).
virus package
perusing the archives of the list i've gotten the impression that virus packages are frowned on; it seems that the endorsed methodology is the restricted-access-purely-this-solution approach. by that, the server *only* runs oracle and is configured to only allow access to that resource. we are running a development box with oracle and mcafee virus shield, excluding the oracle directory from scanning. not knowing what we will need to administer these remotely, applying restrictions, disabling shares and ports might be a step too far. but it's something we may want to consider. what were mentioned as detractions to installing a virus package was: -scanning of oracle files brings the server to it's knees -virus package may erroneously identify an oracle related file as infected, the quarantine or deletion of said file could cripple your solution my question is... does this still hold true? do any of you run oracle w/ a virus package? if so what and how? i'm looking for real world experiences, the development box we have is isolated so i have no real world metrics. for now our decision is to install a virus package and exclude oracle-related--questionable files, like .DBFs and .CTLs... good bad other? TIA, hopefully i'm not beating a dead horse =-=-=-=-=-=-=-=-=-=-= lerone =-=-=-=-=-=-=-=-=-=-= -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Streeter, Lerone A LBX 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:Compressing Export Dumps/WinZip
Patrice, Yes, that's right. On our Tru64 Unix platforms, I was amazed to find that even though I knew the export dump files to be binary files and (what I assumed to be) not only Oracle-extent-compressed, but binary-data-compressed...that in using gzip/gunzip we were achieving compression percentages of up to 50% and even more. Jim Damiano -Original Message- Sent: Thursday, May 16, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano 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: Export multiple targets using OEM
Title: RE: Export multiple targets using OEM I am using OEM 9.0.1 with job scheduler I am trying to export full database for multiple targets using the job scheduler. How do you specify more than one .dmp name and location? I want a .dmp and .log file for each target. Even though I specified 2 targets, it appears as if only one .dmp file was created even though 'Job History' shows both exports completed successfully. Also How can I specify an environment variable such as %ORACLE_SID% in the path for the .dmp file? e.g. D:\ORACLE\ADMIN\%ORACLE_SID%\EXP\FILES\EXP.DMP John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada (604) 697-6476 (Office) (604) 313-6054 (Cell)
Re: Can a database go down because of too few locks?
Depends on what you mean by down, doesn't it? Many define down as unusable, despite the fact that connections can be made and SQL statements can be processed. If you're referring to the parameters that start with the prefix LM_*, then no, the database instance won't crash/halt/abend. If you underconfigure the LM_ parameters then you can expect OPS/RAC to take what it needs for DLM resources from the Shared Pool, resulting in terrible performance and lots of messages to the alert.log. The instances also may not start if you had requested too many resources using the parameters that start with the prefix GC_* (specifically GC_FILES_TO_LOCKS), but I'm not certain of that. I've always used a liberal hand in initially allocating DLM parameters and then monitoring the V$RESOURCE_LIMIT view to consider chopping back (or adding more) as usage patterns become apparent. Clustered systems should not be memory-constrained -- this would be a fine example of penny-wise, pound-foolish, as the cost of RAM is small compared to the overall (direct and indirect) costs of a clustered configuration. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 8:38 AM Can anyone tell me if an ops database can be brought down because of too few locks being allocated for the database? Thanks, Bryan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Compressing Export Dumps / WinZip
The compress=y option doesn't have any effect on how data is stored in the export dump file, only some of the metadata. It directs the EXP program to recalculate the DDL for all of the tables and indexes (instead of just using the settings in the data dictionary) so that all space previously allocated for the object is now contained in a single extent. In other words, if a table being exported had a STORAGE clause like: storage (initial 1M next 1M pctincrease 0) and it currently had 500 extents allocated to it, then the EXP program would store the DDL for the table in the export dump file with a new STORAGE clause: storage (initial 500M next 1M pctincrease 0) There are several flaws with this mechanism: * the table may be completely empty of rows, but 500M will still be allocated for its initial extent. The COMPRESS=Y option doesn't consider the possibility that rows may have been deleted... * the idea that a single extent is better than many extents is complete nonsense (I wrote a paper in spring 2001 on this, posted at www.EvDBT.com/library.htm, entitled Myths About Extents And Performance)... * the idea that a single large extent is better than many extents actually increases the probability and problems associated with tablespace free-space fragmentation instead of reducing it. Think about it: everything may look good immediately after import, but what happens after a few weeks or months? Although COMPRESS=Y is the default, I strongly advise that you use COMPRESS=N to disable this functionality, in most cases. Just my $0.02... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:33 AM Patrice - That would be correct. If you run export interactively, the prompt that is provided is compress extents (y/n). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: 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: Compressing Export Dumps / WinZip
Ok is this a joke?? If not, I think someone needs to crack the Oracle Utilities manual... RF -Original Message- Sent: Thursday, May 16, 2002 11:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: offtopic - unix command
Is there a way to check for the success/failure of the actual remote command when using rsh? $a=$(rsh blah); and parse $a for output for an indication of the blah command succeeding or failing. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: Dba_tab_modifications question
Cherie, Looking at the structure of user_ind_partitions table, I don't see a monitoring column. So I guess you can monitor only at the table level. Prakash -Original Message- Sent: Thursday, May 16, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Chris, Do you know anything about monitoring and gathering stale statistics on table partitions? I am able to monitor and gather stale statistics on partitioned tables at the table level but don't seem to be doing so at the partition level. I can't figure out how to alter my partitions to put them in monitor mode. Have you ever done that? The documentation and metalink only shows the command for altering the table, there seems to be no partition clause for that command. I've guessed at various syntaxes for the clause and none of them work. Thanks, Cherie Machler Oracle DBA Gelco Information Network Grabowy, Chris To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cgrabowy@fcg. cc: com Subject: RE: Dba_tab_modifications question Sent by: [EMAIL PROTECTED] om 05/16/02 09:23 AM Please respond to ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 4:23 AM Prakash, My understanding is that the updation of counts for MONITORed tables is done without using latching, so that normal DML is not held up by some additional latches. This will explain the small difference that you are seeing, i.e. the counting of some INSERTs were missed due to race conditions that could have otherwise been prevented by latches. Am I as clear as mud or what! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: Dba_tab_modifications question Hey Prakash, I never knew about that dictionary table, so I looked it up and found... These views describe tables that have been modified since the last time table statistics were gathered on them. The views are populated only for tables with the MONITORING attribute. They are not populated immediately, but after a time lapse (usually 3 hours). Perhaps that explains the diff. Check it out. Chris -Original Message- Sent: Wednesday, May 15, 2002 1:03 PM To: Multiple recipients of list ORACLE-L Hello, Oracle 8.1.6 on HP-UX 11.0 WFM_ADMIN@VGRAFO select num_rows,last_analyzed,monitoring from user_tables where table_name = 'NOTES_LOG'; NUM_ROWS LAST_ANAL MON --- --- 1585697 14-MAY-02 YES Last night, Informatica inserted rows into this table. 1 select inserts,updates,deletes from
Installing an app on a French configured server
Hello all, There is a requirement to install our application at a site that will have both Oracle (8.1.7) Unix (AIX 5.1) configured in the French language. Can anybody please advise me of anything that I need to be aware of in relation to the differences between an English French configuration?? Any help is greatly appreciated. Best Regards, Barry. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Barry Deevey 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: Dba_tab_modifications question
I found two bugs on Metalink dealing with this. The first, 1890016, can be ignored because the GATHER only fails if you specify an invalid granularity. Well duh. The second, bug 1192012, will only cause the first table in the schema to be skipped. In our case, the first table is first alphabetically (and perhaps the first created in the schema), which happens to contain no rows. So I still use the GATHER_STALE option on 8.1.6. HTH! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Subject: RE: Dba_tab_modifications question Thanks Prakash. I was poking around in Metalink and discovered it. Luckily, I'm on 9i, so I will be checking out this feature. -Original Message- Sent: Thursday, May 16, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Chris, I remember from Metalink that you cannot use the 'gather stale' option in dbms_stats.gather_schema_stats. There is a bug in 8i and supposed to be fixed in 9i. So they still advise to run a job daily to gather the correct statistics. Thank you Gopalakrishnan, Kirti and John for clearing my doubt! Prakash -- 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: SMON reports Dead Transactions - Not seen this before
Murali: The transaction can be considered DEAD for number of reasons. You can see the status of the transaction at anypoint of time using by querying the X$KTUXE. KTUXESTA will give you the transaction status for any given transaction and KTUXEFL will give the transaction flag (DEAD if it is DEAD transaction) and the KTUXESTA will show the STATUS of the transaction (INACTIVE/ACTIVE/COMMITED) To find the transactions which are DEAD in your database you can use this sql. SELECT *from X$KTUXE where KTUXECFL='DEAD'; Transaction STATUS is exposed via V$TRANSACTION and you can query the V$transaction for a known transaction id. There are also few events , like 10013/10015 to trace the instance recovery/rollback segment recovery. I would suggest you set this events to identify the DEAD transactions. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 8:03 PM Murali I have seen them occasionally in our 8.1.7 database, generated during a database recovery. The recoveries in question complete normally so I haven't researched the details. Mike Hand Polaroid Corp -Original Message- Sent: Wednesday, May 15, 2002 9:48 PM To: Multiple recipients of list ORACLE-L Hello list I am for the first time seeing a message like this in the SMON trace file. --- Using 1 slaves for 1 dead transactions Recovered xid: 0x000e.00a.4971 in 20 milliseconds; nchk=0, size=2327 --- I have not seen this message before, any ideas ... Murali Vallath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Compressing Export Dumps / WinZip
Yup... -Original Message- Sent: Thursday, May 16, 2002 11:44 AM To: Multiple recipients of list ORACLE-L Doesn't it mean that all rows are compressed into 1 extent? -Original Message- Sent: Thursday, May 16, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Subject:RE: Compressing Export Dumps / WinZip I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Taylor, Shirley 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).
Need Script to Trigger Archive Log Cleanups
I need a routine which removes archive logs via RMAN tape backups if the archive log destination exceeds half full. I already have the RMAN part which we can kick off manually but I'm looking for something like a basic cron job monitoring script which triggers this based on the half full condition. Before I get started on this, does anyone have a script like this which they'd be willing to share? I figured I'd do a df with awk or Perl... but I'd rather just piggyback on someone else' fine script. :-) Other ideas? Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: trouble importing
Bill - http://www.orafaq.com/faqiexp.htm#SPEED for some tips. Do you have any alternatives to importing? Transportable tablespaces, database cloning, SQL*Net, for example? If your server has multiple CPUs, you can start multiple import sessions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 11:09 AM To: Multiple recipients of list ORACLE-L I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- 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). -- 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: Waits 8i vs. 9i??
Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:13 PM Has anyone noticed that the number of event waits in 9i seems much much higher than in 8i. This is the number, not the time waited mind you, so this doesn't really have performance implications. I'm just wondering if this betrays some internal code changes in the way Oracle is reporting these events. Thoughts RF -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: virus package
Lerone - This was discussed awhile back, so you may want to search the archives. As I recall, the advice was pretty much along the lines you have proposed, to avoid scanning the large Oracle dbf files because you are wasting a lot of your system resource since dbf files aren't executed they won't infect your system, even if a virus was there. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:59 AM To: Multiple recipients of list ORACLE-L perusing the archives of the list i've gotten the impression that virus packages are frowned on; it seems that the endorsed methodology is the restricted-access-purely-this-solution approach. by that, the server *only* runs oracle and is configured to only allow access to that resource. we are running a development box with oracle and mcafee virus shield, excluding the oracle directory from scanning. not knowing what we will need to administer these remotely, applying restrictions, disabling shares and ports might be a step too far. but it's something we may want to consider. what were mentioned as detractions to installing a virus package was: -scanning of oracle files brings the server to it's knees -virus package may erroneously identify an oracle related file as infected, the quarantine or deletion of said file could cripple your solution my question is... does this still hold true? do any of you run oracle w/ a virus package? if so what and how? i'm looking for real world experiences, the development box we have is isolated so i have no real world metrics. for now our decision is to install a virus package and exclude oracle-related--questionable files, like .DBFs and .CTLs... good bad other? TIA, hopefully i'm not beating a dead horse =-=-=-=-=-=-=-=-=-=-= lerone =-=-=-=-=-=-=-=-=-=-= -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Streeter, Lerone A LBX 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: Compressing Export Dumps/WinZip
Large chunks of export files are entirely readable. You can open them in vi and read it because character data is stored as plain text, hence the potentially good compression rates. I agree I wouldn't like to have to decipher a lot of numeric fields. Regards, Mike Hately BTW Robert (Freeman), LMAO! -Original Message- Sent: 16 May 2002 17:14 To: Multiple recipients of list ORACLE-L Patrice, Yes, that's right. On our Tru64 Unix platforms, I was amazed to find that even though I knew the export dump files to be binary files and (what I assumed to be) not only Oracle-extent-compressed, but binary-data-compressed...that in using gzip/gunzip we were achieving compression percentages of up to 50% and even more. Jim Damiano -Original Message- Sent: Thursday, May 16, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: trouble importing
Why have you used commit=n ??? Throw a decent size buffer at it and use commit=y. You could also use indexes=n and rebuild them after with the nologging option HTH Lee -Original Message- Sent: 16 May 2002 17:09 To: Multiple recipients of list ORACLE-L I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- 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). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Loading external binary files(PDF)into BLOB column. Urgent help.
Hi All, I've currently been working on loading many external binary files (PDF) intoBLOB column. After some digging, I learn that the SQL*LOADER can be used to load data from external files into table. I also got help from another forummate mentioning to use PL/SQL procedure to do so. Since I have not done anything like this before. So, my question is what is the simple approach needed to upload PDF files into a table(there is only one table containingBLOB column in my database). In addition, the LOBs can not be query-able, I wanted to list the contents of the LOBs column to make sure that I did successfully upload data into the database. How can I do that?. I do need your help. Please direct me step by step how to do so. Your help is greatly appreciated. Regards, TrangDo You Yahoo!? LAUNCH - Your Yahoo! Music Experience
RE: virus package / UDMA for IDE
With McAfee you can exclude some directories from VirusShield, which speeds things up a bit if you have to use McAfee. since you mentioned McAfee... if you have to run Oracle on IDE drives download DMACheck from Microsoft if you want to try using UDMA. Another question from me... is it a bad thing to use UDMA with Oracle? I think it just lets disk write bypass the NT kernel, not sure if there are buffered writes in there. [don't ask why I am running Oracle with IDE drives, it's a test db. Some people might be running Personal Oracle.] Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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).
cache buffer chains
All, Oracle 8.0.5 Tru64 4.0f I was doing a stats pack analysis and noticed that we had "latch problems". I drilled in a bit further and it would appear that the issue was down to cache buffer chains. The Metalink article (I was flying blind here) states "To identify the heavily accessed buffer chain look at the latch stats for this latch under View:V$Latch_Children" I did this and it came back with over 1 rows Can someone give me a little guidance here ?? Regards Lee The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
Re: Need Script to Trigger Archive Log Cleanups
I need a routine which removes archive logs via RMAN tape backups if the
archive log destination exceeds half full. I already have the RMAN part
which we can kick off manually but I'm looking for something like a basic
cron job monitoring script which triggers this based on the half full
condition. Before I get started on this, does anyone have a script like
this which they'd be willing to share? I figured I'd do a df with awk or
Perl... but I'd rather just piggyback on someone else' fine script. :-)
Other ideas?
Simple enough to do in perl with a regex:
my $mountpoint = /some/dir;
my $cutoff = 50;
my ($used) = qx( df $mountpoint ) =~ /(\d+)%/;
if( $used $cutoff )
{
print $$: Disk use on $mountpoint: $used $cutoff;
# whatever you want down here
}
else
{
print $$: $mountpoint below $cutoff;
}
For multiple mountpoints iterate on df:
my mountz = qw( /foo /bar /bletch );
my $cutoff = 50;
my overz =
map
{
my ($used, $dir) = /(\d+)%\s+(.+)/;
$used $cutoff ? $dir : ()
}
qx( df mountz );
for my $dir ( overz )
{
print $$: Cleaning up $dir...;
# whatever
}
the map combines the extraction with a grep to remove
items that are blow the threshold. overz is syntatic
sugar, since the map could have cleaned everything up
for itself:
my roadkill =
map
{
my( $u, $d ) = /(\d+)%\s+(.+)/;
if( $u $cutoff )
{
# cleanup $d...
eval{ blah blah };
$ ? $ : ()
}
}
qx( df mountz );
print STDERR $$: Bad news, boss, cleanups failed:, roadkill
if( roadkill );
The eval leaves any messages from die in $, which then
get passed up to roadkill. That or pass on a was clean
message and change the array to results or something.
--
Steven Lembark 2930 W. Palmer
Workhorse Computing Chicago, IL 60647
+1 800 762 1582
diskfrie
Description: Binary data
RE: Compressing Export Dumps / WinZip
yes. I just wanted to verify though. One DBA answered that I must be joking... : ) Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 12:43 PM To: Multiple recipients of list ORACLE-L Doesn't it mean that all rows are compressed into 1 extent? -Original Message- Sent: Thursday, May 16, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Subject:RE: Compressing Export Dumps / WinZip I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Taylor, Shirley 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: Boivin, Patrice J 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: Compressing Export Dumps / WinZip
Tim, I may be wrong but I thought that compress=y just adds up the total space allocation from SEG$ rather than calculating them from storage parameters. I know this is a trivial point but I'd appreciate the info if it sets about things differently. regards, Mike Hately -Original Message- Sent: 16 May 2002 17:14 To: Multiple recipients of list ORACLE-L The compress=y option doesn't have any effect on how data is stored in the export dump file, only some of the metadata. It directs the EXP program to recalculate the DDL for all of the tables and indexes (instead of just using the settings in the data dictionary) so that all space previously allocated for the object is now contained in a single extent. In other words, if a table being exported had a STORAGE clause like: storage (initial 1M next 1M pctincrease 0) and it currently had 500 extents allocated to it, then the EXP program would store the DDL for the table in the export dump file with a new STORAGE clause: storage (initial 500M next 1M pctincrease 0) There are several flaws with this mechanism: * the table may be completely empty of rows, but 500M will still be allocated for its initial extent. The COMPRESS=Y option doesn't consider the possibility that rows may have been deleted... * the idea that a single extent is better than many extents is complete nonsense (I wrote a paper in spring 2001 on this, posted at www.EvDBT.com/library.htm, entitled Myths About Extents And Performance)... * the idea that a single large extent is better than many extents actually increases the probability and problems associated with tablespace free-space fragmentation instead of reducing it. Think about it: everything may look good immediately after import, but what happens after a few weeks or months? Although COMPRESS=Y is the default, I strongly advise that you use COMPRESS=N to disable this functionality, in most cases. Just my $0.02... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:33 AM Patrice - That would be correct. If you run export interactively, the prompt that is provided is compress extents (y/n). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: 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: Compressing Export Dumps / WinZip
Using compress=y means only that the value of the initial storage parameter written to the create DDL statement in the .dmp file gets set to the value of select sum(bytes) from dba_extents where owner=:v1 and segment_name=:v2. compress=y is a wretched, awful thing for a number of reasons, not the least of which is that it causes imports to fail if you have a segment that's bigger than your OS-restricted maximum file size. The parameter is poorly named because it causes no space savings whatsoever. To the contrary, using compress=y preserves whatever wasted space has accumulated during the segment's history. Because of the parameter's misleading name, many people attribute temporary performance gains after using exp and then imp to the compress=y setting, but you'll actually get identical performance gains by using compress=n. DBAs should almost always use compress=n instead of allowing the default compress=y to change their extent sizes. Better yet, DBAs should use uniform extent sizes with their locally managed tablespaces and avoid the compress=y|n problem entirely. Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Shirley Sent: Thursday, May 16, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Doesn't it mean that all rows are compressed into 1 extent? -Original Message- Sent: Thursday, May 16, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Subject:RE: Compressing Export Dumps / WinZip I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Taylor, Shirley 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: Cary Millsap 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).
offtopic - unix command
But what if command blah does not output anything? In this case, $a is null, as it is when the command fails. Steven Lembark wrote: Is there a way to check for the success/failure of the actual remote command when using rsh? $a=$(rsh blah); and parse $a for output for an indication of the blah command succeeding or failing. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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: Compressing Export Dumps
Simon, I'm curious as to why you're creating exports that large. Are you doing this as a backup method? Have you ever restored an export that large? The largest export I've ever restored is about 10 gig, and it took far too long. Jared Simon Waibale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/16/2002 02:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Compressing Export Dumps Hi all, My export dumps are too big (80 GB) for my filesystem and I'm looking for a way to compress them on the fly -ie without taking *.dmp to disk first but straight *.dmp.gz Anybody with an idea on how to archive this ? Thanking you, --- CSW -- 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: virus package
thanks, just wanted to double-check. it's scsi disks btw... guess i should have said that... =-=-=-=-=-=-=-=-=-=-= lerone =-=-=-=-=-=-=-=-=-=-= -Original Message- Sent: Thursday, May 16, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Lerone - This was discussed awhile back, so you may want to search the archives. As I recall, the advice was pretty much along the lines you have proposed, to avoid scanning the large Oracle dbf files because you are wasting a lot of your system resource since dbf files aren't executed they won't infect your system, even if a virus was there. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:59 AM To: Multiple recipients of list ORACLE-L perusing the archives of the list i've gotten the impression that virus packages are frowned on; it seems that the endorsed methodology is the restricted-access-purely-this-solution approach. by that, the server *only* runs oracle and is configured to only allow access to that resource. we are running a development box with oracle and mcafee virus shield, excluding the oracle directory from scanning. not knowing what we will need to administer these remotely, applying restrictions, disabling shares and ports might be a step too far. but it's something we may want to consider. what were mentioned as detractions to installing a virus package was: -scanning of oracle files brings the server to it's knees -virus package may erroneously identify an oracle related file as infected, the quarantine or deletion of said file could cripple your solution my question is... does this still hold true? do any of you run oracle w/ a virus package? if so what and how? i'm looking for real world experiences, the development box we have is isolated so i have no real world metrics. for now our decision is to install a virus package and exclude oracle-related--questionable files, like .DBFs and .CTLs... good bad other? TIA, hopefully i'm not beating a dead horse =-=-=-=-=-=-=-=-=-=-= lerone =-=-=-=-=-=-=-=-=-=-= -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Streeter, Lerone A LBX 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: Streeter, Lerone A LBX 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: trouble importing
will try multiple imports - can they go against the same physical dump file? or do I need to copy the dump file for each separate import? will also restart with analyze=n - we're using RBO anyway seem to be two ways with COMMIT param - COMMIT = Y and a large buffer (someone else's post) COMMIT = N and a large RBS (oracle recommendation) any thoughts on this? I would love to use TTS but physically cannot ftp the 70G datafiles from our remote source (a client site) - the compressed dump file is just under 1Gb -Original Message- Sent: Thursday, May 16, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Bill - http://www.orafaq.com/faqiexp.htm#SPEED for some tips. Do you have any alternatives to importing? Transportable tablespaces, database cloning, SQL*Net, for example? If your server has multiple CPUs, you can start multiple import sessions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 11:09 AM To: Multiple recipients of list ORACLE-L I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- 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). -- 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: 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).
RE: offtopic - unix command
I do it all the time with a line like this :
rsh $1 . ${vTARGETPROFILE};mkdir $2;echo \$?
In this case, I am making a directory called $2 at host $1. The unix
command sets the error value so when you can now get that value over on the
calling machine.
You could also do it like this:
rsh ${vTARGETHOST} . ${vTARGETPROFILE};echo \${ORACLE_BASE | read
vTARGETORABASE
In this case, I am getting the value of ORACLE_BASE at the remote host
vTARGETHOST and reading it into the variable vTARGETORABASE.
-Original Message-
Sent: Thursday, May 16, 2002 12:16 PM
To: Multiple recipients of list ORACLE-L
But what if command blah does not output anything? In this
case, $a is null, as it is when the command fails.
Steven Lembark wrote:
Is there a way to check for the success/failure of the actual remote
command when using rsh?
$a=$(rsh blah);
and parse $a for output for an indication of the blah
command succeeding or failing.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Becker
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: Kevin Lange
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: offtopic - unix command
-- Bill Becker [EMAIL PROTECTED] But what if command blah does not output anything? In this case, $a is null, as it is when the command fails. Either: Look for a success message and change the sense of the test. Run the remote command in verbose mode. Wrap the remote command in something that does output a reasonable message (or re-write it to do so if you have any control). If the command has neither verbose mode nor any useful messages by default then give its programmer 30 lashes w/ a wet noodle for designing undecipherable code. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark 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: Compressing Export Dumps / WinZip
It all depends which words you use -- sorry for the ambiguity... As Cary replied earlier, EXP just queries sys.seg$ (i.e. DBA_SEGMENTS) to find the bytes and uses that for the newly-calculated INITIAL. This can be seen in a SQL Trace initiated on the EXP's server process... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 10:48 AM Tim, I may be wrong but I thought that compress=y just adds up the total space allocation from SEG$ rather than calculating them from storage parameters. I know this is a trivial point but I'd appreciate the info if it sets about things differently. regards, Mike Hately -Original Message- Sent: 16 May 2002 17:14 To: Multiple recipients of list ORACLE-L The compress=y option doesn't have any effect on how data is stored in the export dump file, only some of the metadata. It directs the EXP program to recalculate the DDL for all of the tables and indexes (instead of just using the settings in the data dictionary) so that all space previously allocated for the object is now contained in a single extent. In other words, if a table being exported had a STORAGE clause like: storage (initial 1M next 1M pctincrease 0) and it currently had 500 extents allocated to it, then the EXP program would store the DDL for the table in the export dump file with a new STORAGE clause: storage (initial 500M next 1M pctincrease 0) There are several flaws with this mechanism: * the table may be completely empty of rows, but 500M will still be allocated for its initial extent. The COMPRESS=Y option doesn't consider the possibility that rows may have been deleted... * the idea that a single extent is better than many extents is complete nonsense (I wrote a paper in spring 2001 on this, posted at www.EvDBT.com/library.htm, entitled Myths About Extents And Performance)... * the idea that a single large extent is better than many extents actually increases the probability and problems associated with tablespace free-space fragmentation instead of reducing it. Think about it: everything may look good immediately after import, but what happens after a few weeks or months? Although COMPRESS=Y is the default, I strongly advise that you use COMPRESS=N to disable this functionality, in most cases. Just my $0.02... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:33 AM Patrice - That would be correct. If you run export interactively, the prompt that is provided is compress extents (y/n). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:08 AM To: Multiple recipients of list ORACLE-L I don't know whether this is a tangent, but I notice that on the windows platform, compressed exports can still get 85% compression when zipping them with WinZip. Obviously Oracle compressed=y doesn't mean compress the export file, it just means that it places all the segments contiguously in the export file. Right? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Dba_tab_modifications question
You need to check dba_part% views to see information regarding partitioned tables. VIEW_NAME -- DBA_PARTIAL_DROP_TABS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_PART_KEY_COLUMNS DBA_PART_LOBS DBA_PART_TABLES To turn on monitoring on Partions it is the same... ALTER TABLE MSC.MSC_BOMS MONITORING Regards, Kathy -Original Message- Sent: Thursday, May 16, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Chris, Do you know anything about monitoring and gathering stale statistics on table partitions? I am able to monitor and gather stale statistics on partitioned tables at the table level but don't seem to be doing so at the partition level. I can't figure out how to alter my partitions to put them in monitor mode. Have you ever done that? The documentation and metalink only shows the command for altering the table, there seems to be no partition clause for that command. I've guessed at various syntaxes for the clause and none of them work. Thanks, Cherie Machler Oracle DBA Gelco Information Network Grabowy, Chris To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cgrabowy@fcg. cc: com Subject: RE: Dba_tab_modifications question Sent by: [EMAIL PROTECTED] om 05/16/02 09:23 AM Please respond to ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best
RE: Waits 8i vs. 9i??
Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? 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! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA *2 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 corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: ora-04031 and x$ksmlru
Thanks to the wonderful search capabilities that Steve Adams has installed on his website at www.ixora.com.au, the following page has some more information about the X$KSMLRU fixed-table (http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)... I did an advanced search on MetaLink for kllcqc, making sure to check the checkbox for Bug Database -- quite a few bugs appeared (for what they are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the error message looks remarkably like yours even so. Like you, they are using MTS. They indicate that the settings for SORT_AREA_SIZE and HASH_AREA_SIZE are too large for the Shared Pool, hence the ORA-04031. The solution is to reduce SORT_AREA_SIZE and HASH_AREA_SIZE... What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it possible that the users may be using ALTER SESSION SET to set their own custom values for these parameters? I think this statement should appear in the V$SQL or V$SQLAREA if they are using it. This would possibly explain the sudden (and violent) onset of these symptoms... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:33 AM Oracle 8.0.5 Solaris 2.6 shared_pool_reserved_min_alloc5K shared_pool_reserved_size 6656000 shared_pool_size 13312 total sga size is 597 megs I'm fighting a particularly difficult ora-04031 error. The error can be reproduced easily with several queries, including this one: select a.agreement fromadvdb.ad a, advdb.pub p where p.adno=a.adno and p.vno=a.vno; ERROR: ORA-04031: unable to allocate 340032 bytes of shared memory (shared pool,unknown object,cursor work he,kllcqc:kllcqslt) In reviewing metalink article 146599.1, it says that I can determine allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation This query (select * from x$ksmlru where ksmlrsiz0) returned this: ADDR INDXINST_ID KSMLRCOM KSMLRSIZ KSMLRNUM -- -- -- -- KSMLRHON KSMLROHV KSMLRSES -- 82B8 0 1 sort area 4152 8 0 A42C4048 82FC 1 1 kafco : qkacol 4292 1032 insert into pub (adno,pubno,... 1730627729 A427B7E0 8340 2 1 kllcqc:kllcqslt 324100 13311 0 A438EC84 8384 3 1 kllcqc:kllcqslt 326124 9590 0 A433A828 83C8 4 1 kllcqc:kllcqslt 376908 6326 0 A438EC84 840C 5 1 kllcqc:kllcqslt 384268 10731 0 A433A828 I notice several references in the column KSMLRCOM to kllcq:kllcqslt, which matches information from the error message. However, I have no idea what that means. Can the information from this column in some way help me figure out what the problem is? Since my request_failures is 41 and my last_failure_size 384,268 and shared_pool_reserved_min_alloc is 5k, I believe I need to increase shared_pool_size. I'm currently begging for permission to bounce the database, but the timing isn't great . . . I've flushed shared_pool several times,to no avail. Anything I can do till I can bounce the database?? Thanks for any help! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara 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
RE: trouble importing
Bill - You don't say whether your system has multiple CPUs. That will SERIOUSLY affect the advantage from multiple import jobs. You will have to experiment with the number of import jobs that seem to produce the greatest overall performance. You can have multiple import jobs read the same import file. Obviously you must divide the work somehow between them so they don't all try to load the same records, either divide based on user or table. If you divide by table, you end up specifying each table, kind of a hassle. Since import is doing a lot of disk work, you can speed it up by carefully selecting the disk drives for the dump file and the Oracle files so they don't step on each other. This is the real advantage for having multiple copies of your dump file or multiple dump files. A better idea is to go back and do the export again, specifying separate export files for the largest tables. Since export is so much faster than import, this shouldn't delay your overall project by much. I would encourage you to consider alternatives to import. In order of speed, I have found the following to be true: 1. Clone the database. Basically the speed of your tape/disk drives. The only reason you might not want to do this is if the machines are different. 2. Transportable tablespaces. Like cloning, but by tablespace rather than the entire database. 3. Use the SQL*Net COPY command. I've found this to be about twice as fast as import. You can run multiple processes here also. 4. Export/Import 5. SQL*Plus - forget it! Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 12:41 PM To: Multiple recipients of list ORACLE-L will try multiple imports - can they go against the same physical dump file? or do I need to copy the dump file for each separate import? will also restart with analyze=n - we're using RBO anyway seem to be two ways with COMMIT param - COMMIT = Y and a large buffer (someone else's post) COMMIT = N and a large RBS (oracle recommendation) any thoughts on this? I would love to use TTS but physically cannot ftp the 70G datafiles from our remote source (a client site) - the compressed dump file is just under 1Gb -Original Message- Sent: Thursday, May 16, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Bill - http://www.orafaq.com/faqiexp.htm#SPEED for some tips. Do you have any alternatives to importing? Transportable tablespaces, database cloning, SQL*Net, for example? If your server has multiple CPUs, you can start multiple import sessions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 11:09 AM To: Multiple recipients of list ORACLE-L I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- 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). -- 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: 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:
Re: Order rows
Alexander, OK, we're splitting hairs here. :) Of course ROWID's are stored in indexes, the database has to be able to locate the rows. They are an internal mechanism and not part of the user data. And yes they can be used, and safely in certain situations. Updating a row in PL/SQL comes to mind. But they should never be stored in a table for other than temporary utility usage. Jared Alexandre Gorbatchev [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/16/2002 12:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Order rows Jared, Oracle gives you a chance to use it. And it's very common to use. Isn't it? There is no conception of phisical location in realational theory. I'm not saying that pure theory is the best for practical use, though. :) BTW, Oracle stores ROWID in indexes... instead of primary key (which is stored only in case of indexes on IOT). Of course, that's speed up access, but... Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 15, 2002 11:50 PM In fact ROWID is the Oracle implementation and against RDBMS rules. :) ROWID gives information about phisical location of the record. That MUST NOT be in PURE relational database. Nowadays, there is no pure relational database implementation. That doesn't really count, as Oracle does not store that as part of the data. It is generated from datafile and datablock information at runtime. Jared -- 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: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Dba_tab_modifications question
I believe Cherie is looking to turn it on for specific partitions...not always for the whole table. Which is related to why we have partitions in the first place... -Original Message- Sent: Thursday, May 16, 2002 1:48 PM To: Multiple recipients of list ORACLE-L You need to check dba_part% views to see information regarding partitioned tables. VIEW_NAME -- DBA_PARTIAL_DROP_TABS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_PART_KEY_COLUMNS DBA_PART_LOBS DBA_PART_TABLES To turn on monitoring on Partions it is the same... ALTER TABLE MSC.MSC_BOMS MONITORING Regards, Kathy -Original Message- Sent: Thursday, May 16, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Chris, Do you know anything about monitoring and gathering stale statistics on table partitions? I am able to monitor and gather stale statistics on partitioned tables at the table level but don't seem to be doing so at the partition level. I can't figure out how to alter my partitions to put them in monitor mode. Have you ever done that? The documentation and metalink only shows the command for altering the table, there seems to be no partition clause for that command. I've guessed at various syntaxes for the clause and none of them work. Thanks, Cherie Machler Oracle DBA Gelco Information Network Grabowy, Chris To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cgrabowy@fcg. cc: com Subject: RE: Dba_tab_modifications question Sent by: [EMAIL PROTECTED] om 05/16/02 09:23 AM Please respond to ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With these features setup you can basically throw away your nightly analyze everything process and use a more intelligent approach. Very cool. -Original Message- Sent: Wednesday, May 15, 2002 9:28 PM To: Multiple recipients of list ORACLE-L True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept. The updating of these counters is still done without acquiring any other latches (so, John is right :) Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained). Cheers ! - Kirti -Original Message- Sent: Wednesday, May 15, 2002 6:18 PM To: Multiple recipients of list ORACLE-L John: Not being so choosy, MONITORING is subject to latching. There is a latch called 'hash table allocation/modification latch' which keeps the modification in the shared pool and SMON periodically flushes to the disk. Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 4:23 AM Prakash, My understanding is that the updation of counts for MONITORed tables is done without using latching, so that normal DML is not held up by some additional latches. This will explain the small difference that you are seeing, i.e. the counting of some INSERTs were missed due to race conditions that could have otherwise been prevented by latches. Am I as clear as mud or what! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: Dba_tab_modifications question Hey Prakash, I never knew about that dictionary table, so I looked it up and found... These views describe tables that have been modified since the last time table statistics were gathered on them. The views are populated only for tables with the MONITORING attribute. They are not populated immediately, but after a time lapse (usually 3 hours). Perhaps that explains the diff. Check it out. Chris
Re: Dedicated Server versus Shared Server
Tim, If I understood this correctly, you are saying that a DBMS_LOCK.SLEEP(600) call would tie up an MTS shared server for 10 minutes causing other sessions connected to it to hang for 10 minutes? Jared Tim Gorman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/15/2002 09:58 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Dedicated Server versus Shared Server multiplexing architecture like this: long-running SQL operations tying up a shared server, DBMS_LOCK.SLEEP calls tying up a shared server, various types of locks or slowly-serviced operations tying up a shared server. Each of these scenarios deny service to other sessions who might be waiting for it... -- 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: trouble importing
Bill, If the tables already exist, drop all indexes, FK and PK constraints. They will be re-created by the import and this will greatly speed things up. Try setting an obscenely large SORT_AREA_SIZE before running the import to speed up index creation. ( Like 50 - 100 meg ) Don't forget to set it back after the import. This is of course requires bouncing the database twice. Use COMMIT=Y and BUFFER=10485760 Buffers larger than 10 meg have not shown a corresponding increase in speed, at least in my experience. HTH, Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/16/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:trouble importing I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- 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). -- 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).
Remove default value from a column?
Does anyone know how to remove the default value from a column? The following script illustrates: set long 40 drop table i; create table i ( i varchar2(10) null); alter table i modify ( i default null ); select column_name, nullable, data_default from user_tab_columns where table_name = 'I' / drop table i; create table i ( i varchar2(10) null); select column_name, nullable, data_default from dba_tab_columns where table_name = 'I' / The string for the default value is not a constraint. It is stored in sys.col$.default$. There doesn't seem to be any way to remove the default value for a column once it's set. I've peruse TFM quite a bit, did a google search: nothing helpful. Jared -- 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[2]: Dedicated Server versus Shared Server
Jared, Since we have MTS around here for some applications and we also use DBMS_LOCK.SLEEP, Tim is right and wrong. It does tie up a shared server for the time of the sleep, but since a shared server can service one and only one session at a time it should not affect anyone else. Of course if there are no shared servers that are idle during this period the effect is the same. The trick with MTS is always having at least one shared server that is idle. Difficult, but not totally impossible to attain. BTW: I don't depend on the Oracle kernel to start an additional server if none are idle, since it does not always do so. I'm told by OTS that there is a scanning algorithm that is used that works on the idea that if a session waits a short period for a shared server to process it's requests that's OK. The problem is with the word 'short'. It appears to have several definitions. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 5/16/2002 10:05 AM Tim, If I understood this correctly, you are saying that a DBMS_LOCK.SLEEP(600) call would tie up an MTS shared server for 10 minutes causing other sessions connected to it to hang for 10 minutes? Jared Tim Gorman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/15/2002 09:58 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Dedicated Server versus Shared Server multiplexing architecture like this: long-running SQL operations tying up a shared server, DBMS_LOCK.SLEEP calls tying up a shared server, various types of locks or slowly-serviced operations tying up a shared server. Each of these scenarios deny service to other sessions who might be waiting for it... -- 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: ora-04031 and x$ksmlru
Tim, I don't know if it matters, but we faced the same error in 9x, and when we set the hash_area_size to 1M, it went away. The exact error message for us was ORA-04031: unable to allocate 1126656 bytes of shared memory (shared pool,unknown object,hash-join subh,kllcqc:kllcqslt) The 3rd parameter is hash-join subh, so I think it pertains to hash joins. For Barb, the third parameter is cursor work he(ap) so I wonder would it pertain to hash_area_size at all? 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! -Original Message- Sent: Thursday, May 16, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Thanks to the wonderful search capabilities that Steve Adams has installed on his website at www.ixora.com.au, the following page has some more information about the X$KSMLRU fixed-table (http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)... I did an advanced search on MetaLink for kllcqc, making sure to check the checkbox for Bug Database -- quite a few bugs appeared (for what they are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the error message looks remarkably like yours even so. Like you, they are using MTS. They indicate that the settings for SORT_AREA_SIZE and HASH_AREA_SIZE are too large for the Shared Pool, hence the ORA-04031. The solution is to reduce SORT_AREA_SIZE and HASH_AREA_SIZE... What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it possible that the users may be using ALTER SESSION SET to set their own custom values for these parameters? I think this statement should appear in the V$SQL or V$SQLAREA if they are using it. This would possibly explain the sudden (and violent) onset of these symptoms... *2 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 corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Waits 8i vs. 9i??
Not exactly. The granularity of capturing times increased in 9i, but as Gopal implied, there are just a lot more wait events in 9i as compared to the previous releases. Check this link out to see what new events were introduced in 9i : http://www.oraperf.com/reference.html and click on Wait Events by Version. - Kirti -Original Message- Sent: Thursday, May 16, 2002 1:06 PM To: Multiple recipients of list ORACLE-L Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? 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! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA -- 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).
Re: Waits 8i vs. 9i??
Raj: Oracle9i gives timing information in Micro Seconds. Not Nano Seconds though modern CPUs clocks ticks in nano seconds. The older versions (8i and below) give timing info in Centi Seconds (1/100th of a second) . Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 11:35 PM Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? 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! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ora-04031 and x$ksmlru
NAMETYPEVALUE
--- --- --
sort_area_retained_size integer 0
sort_area_size integer 2097152
hash_area_size integer 20971520
The developers might (very possibly) be using alter session set, but not
the regular users -- they're locked into that silly application stuff.
Still trying to devour all the statistics I've accumulated.
Thanks so much!
Barb
--
From: Tim Gorman[SMTP:[EMAIL PROTECTED]]
Reply To: [EMAIL PROTECTED]
Sent: Thursday, May 16, 2002 12:13 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ora-04031 and x$ksmlru
Thanks to the wonderful search capabilities that Steve Adams has installed
on his website at www.ixora.com.au, the following page has some more
information about the X$KSMLRU fixed-table
(http://www.zoftware.org/tuning/tune_shared_pool.html#fixed_table)...
I did an advanced search on MetaLink for kllcqc, making sure to check
the checkbox for Bug Database -- quite a few bugs appeared (for what
they
are worth). One of them (#2324210) is against 9.0.1.3 on Solaris, but the
error message looks remarkably like yours even so. Like you, they are
using
MTS. They indicate that the settings for SORT_AREA_SIZE and
HASH_AREA_SIZE
are too large for the Shared Pool, hence the ORA-04031. The solution is
to
reduce SORT_AREA_SIZE and HASH_AREA_SIZE...
What are the settings for SORT_AREA_SIZE and HASH_AREA_SIZE here? Is it
possible that the users may be using ALTER SESSION SET to set their own
custom values for these parameters? I think this statement should
appear
in the V$SQL or V$SQLAREA if they are using it. This would possibly
explain
the sudden (and violent) onset of these symptoms...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, May 16, 2002 9:33 AM
Oracle 8.0.5
Solaris 2.6
shared_pool_reserved_min_alloc5K
shared_pool_reserved_size 6656000
shared_pool_size 13312
total sga size is 597 megs
I'm fighting a particularly difficult ora-04031 error. The error can
be
reproduced easily with several queries, including this one:
select a.agreement fromadvdb.ad a, advdb.pub p
where p.adno=a.adno and p.vno=a.vno;
ERROR:
ORA-04031: unable to allocate 340032 bytes of shared memory (shared
pool,unknown object,cursor work he,kllcqc:kllcqslt)
In reviewing metalink article 146599.1, it says that I can determine
allocations in the shared pool that cause other objects in the shared
pool to be aged out. This fixed table can be used to identify what is
causing the large allocation
This query (select * from x$ksmlru where ksmlrsiz0) returned this:
ADDR INDXINST_ID KSMLRCOM KSMLRSIZ
KSMLRNUM
-- -- --
--
KSMLRHON KSMLROHV KSMLRSES
--
82B8 0 1 sort area 4152
8
0 A42C4048
82FC 1 1 kafco : qkacol 4292
1032
insert into pub (adno,pubno,... 1730627729 A427B7E0
8340 2 1 kllcqc:kllcqslt 324100
13311
0 A438EC84
8384 3 1 kllcqc:kllcqslt 326124
9590
0 A433A828
83C8 4 1 kllcqc:kllcqslt 376908
6326
0 A438EC84
840C 5 1 kllcqc:kllcqslt 384268
10731
0 A433A828
I notice several references in the column KSMLRCOM to kllcq:kllcqslt,
which matches information from the error message. However, I have no
idea
what that means.
Can the information from this column in some way help me figure out
what
the problem is?
Since my request_failures is 41 and my last_failure_size 384,268 and
shared_pool_reserved_min_alloc is 5k, I believe I need to increase
shared_pool_size. I'm currently begging for permission to bounce the
database, but the timing isn't great . . .
I've flushed shared_pool several times,to no avail.
Anything I can do till I can bounce the database??
Thanks for any help!
Barb
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
RE: Waits 8i vs. 9i??
You might notice more total event completions in 9i because there are about 50% more segments of kernel code that are instrumented in 9i than there were in 8i (~200 events in 8i, ~300 in 9i). Clock granularity is 0.01 in 8i, so events that complete in the same 0.01-sec quantum as they began will register ela=0 in the trace data (WAITED SHORT TIME). But Oracle tallied a completion for every completed event, even if there were several completions of the same event within the same quantum. In 9i, the clock granularity is 0.01, so you'll get more non-zero times in 9i, but the number of event completions reported for a given sequence of events will remain the same. Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Rajendra Sent: Thursday, May 16, 2002 1:06 PM To: Multiple recipients of list ORACLE-L Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? 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! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap 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: Dedicated Server versus Shared Server
Yup! Easy to prove...
In one SQL*Plus session, connect as MTS and verify shared connection...
$ sqlplus perfstat
SQL*Plus: Release 8.0.6.0.0 - Production on Thu May 16 11:20:52 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8 Enterprise Edition Release 8.0.6.2.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.6.2.0 - Production
SQL select server from v$session where audsid =
userenv('SESSIONID');
SERVER
-
SHARED
SQL exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.
SQL
During the 60 seconds when DBMS_LOCK.SLEEP(60) was running, I connected
via INTERNAL in another session and ran the following query...
SQL select s.sid,
2 s.username,
3 c.queue c_queue,
4 c.status c_status,
5 ss.name ss_name,
6 ss.status ss_status
7 from v$shared_server ss,
8 v$circuit c,
9 v$session s
10 where s.username = 'PERFSTAT'
11 andc.saddr = s.saddr
12 andss.paddr (+) = c.server;
SID USERNAME C_QUEUE C_STATUS
-- -- -
---
SS_NA SS_STATUS
-
448 PERFSTAT SERVER NORMAL
S000 EXEC
I kept running this while the DBMS_LOCK.SLEEP was going, and it showed
shared server S000 tied up. When the call completed, the outer-join to
V$SHARED_SERVER showed nothing...
- Original Message -
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, May 16, 2002 10:52 AM
Tim,
If I understood this correctly, you are saying that a DBMS_LOCK.SLEEP(600)
call
would tie up an MTS shared server for 10 minutes causing other sessions
connected to it
to hang for 10 minutes?
Jared
Tim Gorman [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/15/2002 09:58 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Dedicated Server versus Shared Server
multiplexing architecture like this: long-running SQL operations tying up
a
shared server, DBMS_LOCK.SLEEP calls tying up a shared server, various
types
of locks or slowly-serviced operations tying up a shared server. Each of
these scenarios deny service to other sessions who might be waiting for
it...
--
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).
Duplicate from Previous Incarnation
Has anybody duplicated a database from a previous incarnation? Oracle tells me that I should just be able to issue a RESET DATABASE TO inc#. I am a little worried about doing this when connected to my production database and catalog (as required for duplicating). If would like to hear stories from anybody who has done this. Thanks, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- 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: Can a database go down because of too few locks?
Tim, You hit the nail right on the head. Thanks for your answer, Bryan -Original Message- Sent: Thursday, May 16, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Depends on what you mean by down, doesn't it? Many define down as unusable, despite the fact that connections can be made and SQL statements can be processed. If you're referring to the parameters that start with the prefix LM_*, then no, the database instance won't crash/halt/abend. If you underconfigure the LM_ parameters then you can expect OPS/RAC to take what it needs for DLM resources from the Shared Pool, resulting in terrible performance and lots of messages to the alert.log. The instances also may not start if you had requested too many resources using the parameters that start with the prefix GC_* (specifically GC_FILES_TO_LOCKS), but I'm not certain of that. I've always used a liberal hand in initially allocating DLM parameters and then monitoring the V$RESOURCE_LIMIT view to consider chopping back (or adding more) as usage patterns become apparent. Clustered systems should not be memory-constrained -- this would be a fine example of penny-wise, pound-foolish, as the cost of RAM is small compared to the overall (direct and indirect) costs of a clustered configuration. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 8:38 AM Can anyone tell me if an ops database can be brought down because of too few locks being allocated for the database? Thanks, Bryan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Compressing Export Dumps|
I was just about to post a message asking the same thing. Many of us have seen databases produce dumps which at first were much smaller than 2 GB, then we had to pipe them through the native compress utility on UNIX to keep them that way, then we used gzip which does a better job of compression, finally when this wasn't enough we combined gzip with split. For the above, assume the backup system cannot handle files over 2GB. But why export routinely in the first place. They are not a good recovery mechanism. Using an export almost always means loss of data. If a table is lost we can recover, that tablespace, the system tablespace, and the rollback tablespace, to a point-in-time just before the table was lost from our hot backups, and then export the lost table fom this mini database and import it into the one from which it was dropped. If an overzealous user ran some incorrect DML, we can run the undo statement from log miner. One can argue that if for some reason the physical backups are damaged, restoring from an export with some loss of data is better than no recovery at all. It also protects against errors in the physical backup script which may result in incomplete backups. Export can aid in identifying bad table blocks as it selects every row from every non sys table. However, as your database grows larger selecting every row becomes more and more expensive. Even if your exports are inconsistent they have an impact on rollback segments. No, they do not produce any undo, however they do expect that the schemas being exported stay the same. Schema changes while an export is underway can produce snapshot too old errors and stop the export. Although now schema changes usally, always? result in a failure to export just the table involved. The problem with setting optimal for rollback segments is very likely to be exposed by an export. I am curious about the number of people who have stopped using them as part of their daily backup strategy, and if you have. what are you doing to check for block corruption. Assume the database is 24 X 7. Ian MacGregor Stanford Linear Acclerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 16, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Simon, I'm curious as to why you're creating exports that large. Are you doing this as a backup method? Have you ever restored an export that large? The largest export I've ever restored is about 10 gig, and it took far too long. Jared Simon Waibale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/16/2002 02:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Compressing Export Dumps Hi all, My export dumps are too big (80 GB) for my filesystem and I'm looking for a way to compress them on the fly -ie without taking *.dmp to disk first but straight *.dmp.gz Anybody with an idea on how to archive this ? Thanking you, --- CSW -- 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: MacGregor, Ian 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).
RE: Dba_tab_modifications question
Chris, Actually, sometimes I want to be able to just gather statistics for a single stale partition. In my date-based partitioning, usually only the most recent partition has data changes in it. The older partitions do not change at all. It would surely be nice to monitor on a partition-by-partition basis and only have to gather statistics on the partitions that really need it. That would cut down our statistics gathering time dramatically. An 9.0 Oracle manual that I read (either performance tuning or data warehouse management) states that statistics in partitioned tables should be both collected and maintained on both the table as a whole and for each partition as well. Do most places maintain statistics at both the table and partition level for partitioned table? Cherie Machler Oracle DBA Gelco Information Network Grabowy, Chris To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cgrabowy@fcg. cc: com Subject: RE: Dba_tab_modifications question Sent by: [EMAIL PROTECTED] om 05/16/02 01:22 PM Please respond to ORACLE-L I believe Cherie is looking to turn it on for specific partitions...not always for the whole table. Which is related to why we have partitions in the first place... -Original Message- Sent: Thursday, May 16, 2002 1:48 PM To: Multiple recipients of list ORACLE-L You need to check dba_part% views to see information regarding partitioned tables. VIEW_NAME -- DBA_PARTIAL_DROP_TABS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_PART_KEY_COLUMNS DBA_PART_LOBS DBA_PART_TABLES To turn on monitoring on Partions it is the same... ALTER TABLE MSC.MSC_BOMS MONITORING Regards, Kathy -Original Message- Sent: Thursday, May 16, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Chris, Do you know anything about monitoring and gathering stale statistics on table partitions? I am able to monitor and gather stale statistics on partitioned tables at the table level but don't seem to be doing so at the partition level. I can't figure out how to alter my partitions to put them in monitor mode. Have you ever done that? The documentation and metalink only shows the command for altering the table, there seems to be no partition clause for that command. I've guessed at various syntaxes for the clause and none of them work. Thanks, Cherie Machler Oracle DBA Gelco Information Network Grabowy, Chris To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cgrabowy@fcg. cc: com Subject: RE: Dba_tab_modifications question Sent by: [EMAIL PROTECTED] om 05/16/02 09:23 AM Please respond to ORACLE-L So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated. AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed. With
RE: trouble importing
thanks I'll try that . . . bouncing db now -Original Message- Sent: Thursday, May 16, 2002 1:10 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Bill, If the tables already exist, drop all indexes, FK and PK constraints. They will be re-created by the import and this will greatly speed things up. Try setting an obscenely large SORT_AREA_SIZE before running the import to speed up index creation. ( Like 50 - 100 meg ) Don't forget to set it back after the import. This is of course requires bouncing the database twice. Use COMMIT=Y and BUFFER=10485760 Buffers larger than 10 meg have not shown a corresponding increase in speed, at least in my experience. HTH, Jared Magaliff, Bill [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/16/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:trouble importing I have a 17Gb db that I need to import (Sun 880 running Solaris 5.8). the largest tables (of which there are several) are in the 1-3Million row range . . . a few contain longs. so far it's been running about 24 hours and is only half-way done. have 500Mb shared pool and 350Mb db buffer cache - one large rbs (100Mb extents), COMMIT=N set on import. any ideas how to speed this up? thx -- 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). -- 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).
