RE: Speed up Truncate tables
Check the extent sizes... truncate can take a VERY long time if there are a very large number of extents. At one point we had a large table with approximately 60,000 extents (accidentally created with INITIAL/NEXT 80k MAXEXTENTS UNLIMITED), which took about 2.5 hours to truncate. After recreating it sane extent sizes, it now takes only a few seconds. -Original Message- From: chuan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 15, 2001 3:10 AM To: ORACLE-L Cc: chuan Subject: Speed up Truncate tables Hi All, Is there any way to speed up the truncating a big table with 12 million rows? Basically, I implemented truncating that big table on Production, but it affected the performance much, so I had to stop it in the middle of way. All the rows were truncated but the HWM was not shrunk at all. I want to do it again to get the space back. Is there any way to speed up this process? Platform: Oracle EE8.0.6 and Solaris 2.7 Thanks a lot in advance. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chuan Zhang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: import size problem ???
Create the objects prior to the import, using sane values for INITIAL, NEXT, and MINEXTENTS. -Original Message- From: leslie.y.lu [mailto:[EMAIL PROTECTED]] Sent: Monday, August 06, 2001 11:07 AM To: ORACLE-L Cc: leslie.y.lu Subject: import size problem ??? Hi all, I have an export file of 6M. The import file only has DDL. I imported the file into a db of about 1G. After import, the db became 6G !!! There is one table with 71 columns, but NO DATA, takes 1.8G !!! How does this happen, and how to make the objects claim the space. I issued Alter Table ... Deallocate Unused, but doesn't help. Also how to prevent this from happening. Both the export and import database are 815 on Sun5.6. The export db has 8K block size, while import db has 2K. Here is the par file: USERID=arbor/arbor123 FROMUSER=ARBOR TOUSER=ARBOR COMMIT=TRUE IGNORE=TRUE FILE=arbor_full_20010726.dmp LOG=Afull.log Thank you! Leslie __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Unusable indexes : why ?
Move any tables via ALTER TABLE ... MOVE recently? -Original Message- From: stephane.paquette [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 11, 2001 11:27 AM To: ORACLE-L; stephane.paquette Subject: Unusable indexes : why ? Hi, Oracle 816, I just had 13 unusable indexes (btree and bitmap). I have no partition and no sql*loader load so how come those 13 indexes are unusable ? = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: Prompt in Korn shell
You can embed an actual newline in the assignment, as long as it's
between quotes. For example:
PS1='part1
part2'
-Original Message-
From: Alex.Hillman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 10:26 AM
To: ORACLE-L; Alex.Hillman
Subject: RE: Prompt in Korn shell
Now I have another problem.
export PS1='$LOGNAME@$HOST:$PWD:${ORACLE_SID:-ORUNDEF}\n!-'
- result is:
oracle8i@coin-app:/u01/app/psoft/PSTEST/appserv:PSTESTn133-
instead of
oracle8i@coin-app:/u01/app/psoft/PSTEST/appserv:PSTEST
133-
Looks like it does not understand \n as a new line character.
Any ideas please.
Alex Hillman
-Original Message-
Sent: Wednesday, June 20, 2001 4:26 PM
To: Multiple recipients of list ORACLE-L
Anybody knows how to show current working directory and in
general shell
variable in Korn shell that prompt change when variable changes - for
current working directory and ORACLE_SID for example. I know
how to do it in
bash but cannot do it right in Korn.
Alex Hillman
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Hillman, Alex
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Prompt in Korn shell
On a related note, once the PWD's been crammed in there is it possible to substitute ~ for the home directory (like bash does)? Something like: mehoo@somewhere[~]$ instead of mehoo@somewhere[/foo/bar/mehoo]$ It looks like ksh93 has some string replacement features which might work... unfortunately I seem to be stuck with ksh88. -Original Message- From: Alex.Hillman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 20, 2001 3:26 PM To: ORACLE-L Subject: Prompt in Korn shell Anybody knows how to show current working directory and in general shell variable in Korn shell that prompt change when variable changes - for current working directory and ORACLE_SID for example. I know how to do it in bash but cannot do it right in Korn. Alex Hillman
fine-grained export/import access
We need to allow a user to export/import a different user's objects. We want to limit access to the designated schema, so we're trying to avoid granting EXPORT/IMPORT_FULL_DATABASE. We don't want passwords embedded within scripts either, for obvious reasons. Is there any way to do this? -- My employers like me, but not enough to let me speak for them. Greg Norris Sprint LTD Database Administration
RE: What stands for i in Oracle 8i
I thought it was intensive... as in labor. :-) -Original Message- Sent: Wednesday, June 06, 2001 2:32 PM To: ORACLE-L; Ed.Haskins I'm pretty sure it means...incredibly expensive!! Ed Haskins Oracle DBA Verizon Wireless -Original Message- Sent: Wednesday, June 06, 2001 12:56 PM To: Multiple recipients of list ORACLE-L Hi, I am just curious about to know for what that i stands for in Oracle 8i. Can any one shed some light? Thanks, Muths Do not go where the path may lead, go instead where there is no path and leave a trail.
RE: Script examples for NT. (yuk!)
My suggestion would be to install cygwin (http://sources.redhat.com/cygwin/). -Original Message- From: GLoughmiller [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 8:25 AM To: ORACLE-L Cc: GLoughmiller Subject: Script examples for NT. (yuk!) Quick question.. ALL of my experience has been on UNIX platforms... Recently, I have been asked to work on an Oracle DB that runs on NT.. My scripting capability in the NT world is weak at best. So I am trying to compare ksh type activities with the NT world. SO I was wondering if any of you would mind sharing a couple of NT type scripts with me.. For example-setting variables to the return of a SQL statement to be used in the shell,etc... Any help would be appreciated.. TIA Greg Loughmiller mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Loughmiller, Greg INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SQLLDR Question
Since those are the last two fields in the data file, I think you can use TERMINATED BY WHITESPACE on the field definition. Something like: trans_date date(14) mm/dd/yy,hh:mi terminated by whitespace -Original Message- From: srcdco [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 23, 2001 4:41 PM To: ORACLE-L Cc: srcdco Subject: SQLLDR Question I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Listener.log....writing to renamed file
A coworker of mine discovered this a few weeks ago on a Sun box. No difference there! -Original Message- From: Gsais [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 24, 2001 11:36 AM To: ORACLE-L Cc: Gsais Subject: Re: Listener.logwriting to renamed file I think it is only safe to restart the listener when using dedicated not mts. I know on OpenVMS, you have to restart the db when restarting the listener using mts. Never tested on unix. I prefer dedicated, never use mts, too many problems. Just buy more RAM :).
Re: DBSNMP user?
It doesn't seem to have DBA here (mostly 8.1.6 databases)... perhaps you were generalizing? It does have RESOURCE tho, which is plenty bad since it pulls in UNLIMITED TABLESPACE behind the scenes. -Original Message- From: lvordos [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 13, 2001 1:25 AM To: ORACLE-L Cc: lvordos Subject: Re: DBSNMP user? And has DBA privs and well-known default passwd, which I alter to a random string (not using OEM here...) Holman, Rodney wrote: Ron, That's the good old Oracle Intelligent Agent for Enterprise Manager. It is the ID that talks to the OEM console for events reporting. Rodd Holman Original Message On 5/11/01, 2:45:31 PM, Smith, Ron L. [EMAIL PROTECTED] wrote regarding DBSNMP user?: I have a user that keeps showing up in my NT database called DBSNMP. The OS user name is SYSTEM. Does anyone know where this user might be coming from? Ron Smith Database Administration [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Holman, Rodney INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SuzyV INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Locally Managed Tablespaces
I'm using LMTs with the UNIFORM EXTENT option on 8.1.6[.2], with great results. Two caveats I've noticed: 1) If you want to use LMTs for rollback segments, you MUST create at least one rollback segment in a dictionary managed tablespace first (in addition to the ever-present "system" RBS). This is bug#1176609, which you can lookup on Metalink... assuming that the palm pilot they're using as a webserver is up, of course. (-: 2) If you want to make your TEMP tablespace locally managed (i.e. created using TEMPFILE instead of DATAFILE), it will be created sparse on Unix systems. This means that (for example) you *won't* get an error if you inadvertently attempt to create a file larger than it's host filesystem... not up front, anyway. If that non-existent tempfile space is ever needed, you'll certainly get errors at that point (especially nasty if one of your control files is in the same filesystem). So make absolutely certain that the file size is entered correctly. According to Oracle Support, #2 is a documentation error which will be corrected in 9i. I've been meaning to submit an enhancement request, asking that file filesystem be treated as an error, but haven't actually done it yet. Cheers! -Original Message- From: pashe [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 17, 2001 6:36 PM To: ORACLE-L Cc: pashe Subject: Locally Managed Tablespaces I am interested in some statistics on Oracle locally managed tablespaces. I have been looking for any bugs or negative info about them. Are they in use at alot of sites? Seems like all the information I have come across is positive. Which is great! But maybe they aren't being used at alot of sites. Can I hear about experiences from others on this list? how many sites are actually using them? I have several databases that I am getting ready to go production soon and would like to create the tablespaces as locally managed, but need more statistics.
table/index space usage
I need to determine how much space some tables/indexes are really using, as opposed to how much is allocated to them. Anyone have a script you'd be willing to share? Cheers! -- My employers like me, but not enough to let me speak for them. Greg Norris Sprint LTD Database Administration Phone: 913.345.6628 Internet: [EMAIL PROTECTED] OpenMail: Norris, Gregory T.
RE: sql*loader default value
Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF. Unfortunately, it looks like both of those do basically the opposite of what I'm trying to accomplish. Essentially, I want to prevent sql*loader from ever inserting NULL in a column (which may not be numeric), substituting whatever value is appropriate for the given application instead. Something like using "nvl(:field,'unknown')" in the field specification of the control file for conventional path loads. As far as I can see, there's no way to do this within sql*loader itself. So the choices would be to run a cleanup just after the load (which may or may not negate the speed benefits of using direct path), or to somehow pre-process the data file to fill in the missing values (perhaps an awk or perl script). Of course, if I'm missing something obvious please let me know... the sql*loader documentation seems designed to confuse. (-: Cheers!
