from:"Jack C. Applewhite"


David,

Select (DateTime1 - DateTime2) * 24 * 60 * 60
From   Dual
;

Oracle date arithmetic results are in fractional days, so the above gets you
the number of seconds between two Date datatype arguments.  Of course you
can go ahead and multiply 24*60*60 to get Seconds/Day, but I prefer to leave
it that way so it's more obvious what my intent is.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
David M
Sent: Monday, April 01, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L


Is it possible to calculate seconds using SQL?

For example, I'd like to subtract   these two time to get difference in
seconds:

10:20:32  -   10:25:29

Thanks,
David



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: LAST REBUILD INDEX


Seema,

Probably Last_DDL_Time in DBA_Objects, though that just tells you the last
time any DDL was executed on the object.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Monday, April 01, 2002 9:58 AM
To: Multiple recipients of list ORACLE-L


Hi
WHich view show when was the last rebuild of indexes?
Thx
-Seema



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Rebuild Oracle Listener Service


Ron,

The easiest way to do that is use the Oracle Net8 Configuration Assistant.
It's under Programs/Oracle - OraHomeYour8iHome/Network Administration and
a snap to use.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
L.
Sent: Monday, April 01, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L


My oracle listener service is not working on NT.  Can someone send me a
script to rebuild the listener service?
Thanks!



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 calculate time using SQL


David,

SysDate is a function and can be referenced in any SQL statement.  So you
could do the following if you want seconds between the two, or leave off
some or all of the trailing multiplicands to get fractional minutes, hours
or days.

Select ( SysDate - Next_Date ) * 24 * 60 * 60
From   User_Refresh
;

You need to do a little reading in the Oracle SQL Reference under Datatypes
to find out details of the Date datatype and Date arithmetic.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
David M
Sent: Monday, April 01, 2002 11:42 AM
To: Multiple recipients of list ORACLE-L


Jack,

How about substracting a system date with a database replication date to get
difference of time?  Can you please give me a specific command?


SQL select to_char(sysdate, 'Dy Mon Dd HH24:MI:SS ') from dual;

TO_CHAR(SYSDATE,'DYMONDD

Mon Apr 01 11:08:00 2002== System Time.


SQL select next_date from user_refresh;

NEXT_DATE

Mon Apr 01 11:11:01 2002  == Replication time.

Thanks,
David




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: LAST REBUILD INDEX


Seema,

What version are you on?  For 8.1.7.3.0 under Win2k Last_DDL_Time in
DBA_Objects *does* show when an Index last had an Alter Index xxx
Rebuild executed against it.  I just now tested to verify that.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Monday, April 01, 2002 11:42 AM
To: Multiple recipients of list ORACLE-L


Jack
No!
thx
-Seema


From: Jack C. Applewhite [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: LAST REBUILD INDEX
Date: Mon, 01 Apr 2002 08:53:33 -0800

Seema,

Probably Last_DDL_Time in DBA_Objects, though that just tells you the last
time any DDL was executed on the object.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Monday, April 01, 2002 9:58 AM
To: Multiple recipients of list ORACLE-L


Hi
WHich view show when was the last rebuild of indexes?
Thx
-Seema




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Do programmers tune SQL?


Dennis,

I've had to keep after the Java developers here to use bind variables
instead of literals.  Apparently, it's much easier for them to construct
literal SQL.  They now understand the importance and use bind variable
pretty much all the time.

A few months ago they started spawning multiple threads of some Java
processes and used a thingey called Connection Pooling to mediate
connections to Oracle.  They fell back to using literal SQL, until they
figured out how to use bind variables with Connection Pooling.  Apparently,
it's even harder to use bind variables with Connection Pooling.  If you make
the Java developers aware of these issues up front, maybe they'll go ahead
and learn how to code it right initially.

Sorry I can't give details, 'cause all I know about Java is how to spell it!
;-)

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
WILLIAMS
Sent: Monday, April 01, 2002 11:20 AM
To: Multiple recipients of list ORACLE-L


Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far.
Very close to my situation.

Given my situation - i.e., we haven't had many SQL statement problems, but
expecting to receive more with Java, I'm wondering how I can get ahead of
the game. I have worked on a set of SQL statement recommendations, simple
stuff like make sure screen queries use and index. I am considering creating
a checklist form for SQL statements for the developers to use. Like include
the listing from EXPLAIN PLAN. Does anyone have any thoughts on this
approach? I'm not sure if the developers/management would go for it, but I
thought it would be worth asking as a starting point. Everybody is new to
Java and a little nervous, so they are probably more open to suggestions.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Long-running SQL

2002-03-27 Thread Jack C. Applewhite


Cherie,

At least the leading column in your index doesn't have an index-killing
function on it in the Where clause - the NVL function on the other columns
makes Oracle not use them for hitting the index.

Since you're only interested in returning a single value, perhaps a stored
function that returns that value and takes :b1 - :b9 as input arguments
could be tuned to be faster.  It could be used in the SQL Select.  Check out
the docs for the use of stored functions in SQL statements.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Wednesday, March 27, 2002 8:33 AM
To: Multiple recipients of list ORACLE-L



We have a statement that I feel takes too long to run in a nightly data
load.
The table it runs against has 386,000 records.   It runs for about 10
seconds
on average.  We're only loading about 50,000 records a night but this
statement
is running during the majority of the 9-hour load time.   This is causing
the
load to run longer than our allowable window and causing me untold
headaches.
If anyone has any suggestions to make this run faster, I'd be greatly
appreciative.

The columns in the where statement are all part of an index.   However,
the functions on the columns add additional execution time and complexity.

This is an 8.0.4 database so I can not make this a function-based index.

I put this in a couple of SQL tuning tools and came up with no valid
alternatives.
I can't help thinking that the statement could be rewritten into a couple
of statements
so that it would be more efficient.   However, I'm not skilled enough with
SQL to
do it.   Perhaps someone else is.   Here's the code.

SELECT /*+ INDEX(EXP_COST_CENTER_DIM EXP_COST_CENTER_DIM_IDX1) + */
EXP_COST_CENTER_KEY
 FROM EXP_COST_CENTER_DIM

 WHERE ACCOUNT_NUMBER = :b1 AND
  NVL(ORG_LEVEL_1_VALUE,'NONE') = NVL(:b2,'NONE') AND
  NVL(ORG_LEVEL_2_VALUE,'NONE') = NVL(:b3,'NONE') AND
  NVL(ORG_LEVEL_3_VALUE,'NONE') = NVL(:b4,'NONE') AND
  NVL(ORG_LEVEL_4_VALUE,'NONE') = NVL(:b5,'NONE') AND
  NVL(ORG_LEVEL_5_VALUE,'NONE') = NVL(:b6,'NONE') AND
  NVL(ORG_LEVEL_6_VALUE,'NONE') = NVL(:b7,'NONE') AND
  NVL(ORG_LEVEL_7_VALUE,'NONE') = NVL(:b8,'NONE') AND
  NVL(ORG_LEVEL_8_VALUE,'NONE') = NVL(:b9,'NONE') AND
ROWNUM = 1


SQL desc exp_cost_center_dim
 NameNull?Type
 ---  
 EXP_COST_CENTER_KEY NOT NULL NUMBER(7)
 ACCOUNT_NUMBER  NOT NULL NUMBER(9)
 BATCH_WINDOW_DATE_KEY   NOT NULL NUMBER(5)
 ORG_LEVEL_1_VALUEVARCHAR2(20)
 ORG_LEVEL_2_VALUEVARCHAR2(20)
 ORG_LEVEL_3_VALUEVARCHAR2(20)
 ORG_LEVEL_4_VALUEVARCHAR2(20)
 ORG_LEVEL_5_VALUEVARCHAR2(20)
 ORG_LEVEL_6_VALUEVARCHAR2(20)
 ORG_LEVEL_7_VALUEVARCHAR2(20)
 ORG_LEVEL_8_VALUEVARCHAR2(20)
 DATA_SOURCE_MOD_DATETIMENOT NULL DATE
 DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE
 DATA_MART_MOD_DATETIME  NOT NULL DATE


SQL select column_name from dba_ind_columns where index_name
='EXP_COST_CENTER_D
IM_IDX1';

COLUMN_NAME


ACCOUNT_NUMBER
ORG_LEVEL_1_VALUE
ORG_LEVEL_2_VALUE
ORG_LEVEL_3_VALUE
ORG_LEVEL_4_VALUE
ORG_LEVEL_5_VALUE
ORG_LEVEL_6_VALUE
ORG_LEVEL_7_VALUE
ORG_LEVEL_8_VALUE


SQL select column_name from dba_ind_columns where index_name
='EXP_COST_CENTER_D
IM_PK';

COLUMN_NAME


EXP_COST_CENTER_KEY




Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Check Constraint

2002-03-26 Thread Jack C. Applewhite


Jay,

Two possibilities:

1. Before you add the constraint, issue a Select query with the Where clause
being the NOT version of your Check Constraint.  This is the least hassle.

2. Use the Exceptions Into clause when you create the Check Constraint.
This will populate an Exceptions table with the RowIDs of the rows that
violate the Check.  See the Constraint_Clause section of the SQL Reference
for details.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Tuesday, March 26, 2002 3:15 PM
To: Multiple recipients of list ORACLE-L


When a check constraint is added to a table, is there an option in Oracle
that will display or list the rows in the table that violate the constraint?
TIA.

Jay



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Automatic shutdown on NT does a shutdown abort

2002-03-25 Thread Jack C. Applewhite


Peter,

You don't even get a line in your Alert log like the following, which is
what I always get immediately after I issue a Shutdown Immediate?

ALTER DATABASE CLOSE NORMAL

Perhaps 30sec. isn't enough time for your DB to do a Shutdown Immediate
before the Oracle Service is stopped, which is equivalent to a Shutdown
Abort?

Try increasing ORA_sid_SHUTDOWN_TIMEOUT and see what happens.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Peter
Sent: Monday, March 25, 2002 10:23 AM
To: Multiple recipients of list ORACLE-L


Environment:

Oracle 8.1.7
NT 4.0 sp 6

Registry parameters:

ORA_sid_SHUTDOWN = TRUE
ORA_sid_SHUTDOWN_TYPE=i
ORA_sid_SHUTDOWN_TIMEOUT=30

When I stop the OracleServicesid, the database appears to do a
shutdown abort.  No entries are made in the alert log
to indicate that a clean shutdown occurred.

When I restart the service, the alert log contains messages like
Beginning crash recovery of 1 threads.

Any ideas what I have missed?

Thanks,

Peter Schauss
Northrop Grumman Corporation
516-346-3148
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Automatic shutdown on NT does a shutdown abort

2002-03-25 Thread Jack C. Applewhite


Jared,

Thanks for the links.  However, we should be wary since the document at the
end of the first link states (near the bottom) 1000 microseconds make one
second!

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Monday, March 25, 2002 2:14 PM
To: Multiple recipients of list ORACLE-L


Found the links for setting up Oracle to autostart/shutdown on NT.

Jared

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=NOTp_id=136214.1

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=FORp_id=82122.996

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=FORp_id=70988.996




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Windows NT and Virtual Memory

2002-03-22 Thread Jack C. Applewhite


Raj,

Oracle can only use 2GB of RAM for all its processes under NT or Win2k
unless you're using NT Enterprise Edition or Win2k Advanced Server and you
set an NT / Win2k parameter to allow it to use up to 3GB or (yet another OS
setup) 4GB and more.

Check out the Windows-specific docs for info. and references to papers on
the MS site.  I went through that investigation a while back, but the exact
details have left my brain cell.   ;-)

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Friday, March 22, 2002 9:59 AM
To: Multiple recipients of list ORACLE-L


Operating System: Windows NT 4.0 with SP 6a
Oracle Version : 8.1.7

We recently migrated an Oracle database from one Windoze NT box to another.
The NT administrators claim that the NT boxes are indentical in all
respects, except that the old one had Service Pack 4. We have recreated the
database on the new box with exactly indentical initialization paremeters,
file sizes, extent sizes, et all.

The database gleefully accepts all connections upto about 220 users
(V$license, v$session). When the 221st user tries to logon, the TNS-12500:
Unable to start a dedicated server process is thrown. After all the
troubleshooting (Connect timeouts, Disable otracing, tracing client), we
discovered that as soon as the Virtual memory comes approaches 1.7Gb, this
problem starts happening. I read articles on Metalink which suggested that
this is a limitation with Windows NT, and suggests some recommendation to
delay this problem.

So, we recommended the same to business. But now they wish to know why
these errors never occured in the old NT server. And to further worsen
matters, the old server has been cleaned up. So, I have no way of going
back and Checking.

Has anyone else on this list encountered the same problem? Possible
workarounds, if any?

Thanks
Raj



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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[2]: Fav. Urban Legend...Mem vs Disk

2002-03-21 Thread Jack C. Applewhite


A most enjoyable book Venus on the Half Shell, written by Philip Jose
Farmer under the pseudonym Kilgore Trout, who was a character in many
novels - particularly Breakfast of Champions - by Kurt Vonnegut, who was a
good friend of Mr. Farmer - also the author of the Riverworld trilogy
(quadrilogy) and others.  Oh, the connections!

But the REAL purpose of this post (to keep it On Topic) is to report that my
problem with excessive SNPx memory use turned out to be an artifact of
upgrading from 8.1.6.0.0 - in which the interMedia Text indexing functions
were handled by ExtProc - to 8.1.7.2.5 - in which the iM Text functions are
incorporated into the RDBMS kernel.

In 8.1.6 the 200MB used during index resyncs was released by ExtProc when
the process finished.  In 8.1.7 the SNP job runs the resync process and the
200MB used is not released.  Periodically stopping and restarting the SNPx
processes releases that memory and is an OK workaround.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Eskridge
Sent: Wednesday, March 20, 2002 9:33 PM
To: Multiple recipients of list ORACLE-L

...

Hmmph.  More kowtowing to Douglas Adam's cheap rip off on Kilgore
Trout's epic, Venus on the Half Shell.  Check the name of the FTL
drive in the latter and compare it to The Question.

Curious though, how the answer is just one more than the maximum ITL
slots with 2k blocks...  (he says in a desperate attempt to get back
on topic)




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Standby Database Problem

2002-03-19 Thread Jack C. Applewhite

Title: Standby Database Problem

Hussain,

As long as none of your datafiles were corrupted by
what I assume was the crash of your Standby Instance, you can reinstall 8i and
bring up the Standby just as you describe. The datafiles don't care at all
where Oracle Home is.

Irecently had to reconfigure our Production and
Standby database servers to add a global hot spare drive to each. Because
of that I had to reinstall 8i and moved Oracle Homeon both of them.
However, 90% of the datafiles stayed just like they were - a few had to move to
a new drive. Both DBs came up just fine and I'm now back in Managed
Standby mode.

Of course your Listener.ora will have to reference the
new Oracle Home, but since you're reinstalling 8i, I'm guessing you'll create a
Listener from scratch anyway.

Jack
Jack C.
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit,
Inc.Austin,
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Hussain Ahmed
QadriSent: Tuesday, March 19, 2002 12:13 AMTo: Multiple
recipients of list ORACLE-LSubject: Standby Database
Problem
Hi
While trying to install developer6 on our standby
database test server (OS NT4, Oracle 8.1.7), I have some how corrupted the 8i,
and hence the standby database is not working. Its actually the Oracle Suite
tools which are not working. I am going to configure it again, that is install
the Oracle8i again. But what I want to know is that do I have to copy the
datafiles from the main server again and recreate the control file. Or just
reinstall the oracle database, copy the archive redologs, which are missing,
apply them and then mount the standby database in recover managed mode with
the existing Datafile copies? The reason I am asking this is that the
configuration of database is not changed nor the database has been brought
from standby to active mode, just registry entries have been changed/deleted
becuase of selecting a different home for Developer6 (Thats another problem
that after installing Oracle 8i, when u want to install Dev6,it doesn't allow
installation in the same Oracle home and neither in a separate home, but if
you try it again in a separate home, like I did, it removes the entries of
first Oracle home, so no Oracle 8i! ) So in such case WHAT happens to the
STAND BY Database???
Looking forward to your replies
Regards,
Hussain Ahmed Qadri Database Administrator Shaukat
Khanum Memorial Cancer Hospital Research Centre [EMAIL PROTECTED] www.shaukatkhanum.org.pk

RE: How to register an package in oracle Database!!

2002-03-19 Thread Jack C. Applewhite


Ayyappan,

You can use the procedures in DBMS_APPLICATION_INFO to publish and
interrogate the states and actions of modules.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, March 19, 2002 3:43 AM
To: Multiple recipients of list ORACLE-L


Hi

I am having one package which I have created.
If I an executing that package on one session and in another session
I want to know the Process of the package is running  with the package name.
How to Know that?.

Regards
Ayyappan.S



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: what pl/sql construct can return multiple rows?

2002-03-19 Thread Jack C. Applewhite


Bill,

A PL/SQL Table of MyTable%RowType can be returned from a function or as an
Out Argument from a procedure.

A Ref Cursor (Cursor Variable) can also be returned as an Out Argument from
a procedure.

See the PL/SQL Users Guide and Reference for info. on these.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Bill
Sent: Tuesday, March 19, 2002 7:58 AM
To: Multiple recipients of list ORACLE-L


Hi,

Is there a way to write a procedure to return multiple rows?  I have some
nasty SQL that I'd like to convert to run server-side, but how do you spit
out multiple rows from PL/SQL?

thx


Bill Magaliff
Framework, Inc.
914-631-2322



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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

2002-03-18 Thread Jack C. Applewhite


Antonio,

We use LMTs for all tablespaces - except for System, of course.  No issues,
problems, etc.  We're happy.

8.1.7.2.5 on Win2k; ~340GB in 29 tablespaces.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Monday, March 18, 2002 7:28 AM
To: Multiple recipients of list ORACLE-L



Hi,

Anyone using LMT for rollback segments ? Any issues , suggestions , ... ?

TIA,
Antonio Belloni



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: calling program

2002-03-15 Thread Jack C. Applewhite




Big 
Planet,

Whoa, 
now there's a can of worms!

In a 
word - no. Not unless you pass that info. in as an 
argument.

Think 
about what you're asking. Since PL/SQL procedures can be called from, not 
only other PL/SQL procedures and functions, but the SQL*Plus  command line, ODBC 
calls, Java programs, etc. etc., what would be the identification 
mechanism?

Also, 
since PL/SQL functions can be embedded in SQL statements, there's yet another 
problem - how does a SQL statementknow/report what program is using 
it?

The 
overhead to give you what you're asking would, IMHO, be toohighif 
provided by the PL/SQL engine.

Jack
Jack C. 
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, 
Inc.Austin, 
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Big PlanetSent: 
  Friday, March 15, 2002 2:18 PMTo: Multiple recipients of list 
  ORACLE-LSubject: calling program
  Hi LIst ,
  Is there a way a pl/sql procedureor function 
  can know the calling procedure or calling program .
  -ak

RE: calling program

2002-03-15 Thread Jack C. Applewhite


Ouch!  Looks like I need to RTFM, not only before asking a question, but
before answering one - shoot from the hip = shot in the foot.   8-(


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Rajendra
Sent: Friday, March 15, 2002 3:39 PM
To: Multiple recipients of list ORACLE-L


Actually you can, dbms_utility.format_call_stack, but you'll have to parse
the information. See http://osi.oracle.com/~tkyte/who_called_me/index.html

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!



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Identification of tables NOT being used in the System.

2002-03-15 Thread Jack C. Applewhite


Dharminder,

Instead of the overhead of auditing, how about periodic queries of
V$SQL_Text for SQL statements that reference those tables.  It should be
pretty easy to come up with occurrences and counts for each table, though
statements using bind variables could cause low counts for the tables
referenced by them.

Heck, you might flush the Shared Pool occasionally and requery V$SQL_Text
just to see which tables are the hottest - most frequently referenced.
That may not be a good idea on a Production DB, though - depends on your
application load.

If you bounce your database regularly (for cold backups, for instance), do
it right before shutdown.

It's just a quick thought...which I probably absorbed from someone else's
posting on this very valuable list.  ;-)   If so, then my thanks to the
originator.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Dharminder
Sent: Friday, March 15, 2002 3:08 PM
To: Multiple recipients of list ORACLE-L


In our production database environment, I have a list of about 1000 tables
,for which we want to find if these tables are being used by anyone. How it
can be done. One of the ideas is that we start database auditing on these
tables for a considerable period of time say one month. Then for those
tables for which there is nothing in database audit, we assume that tables
are not being used. For this option I would like to know if we put auditing
on these 1000 tables, how much extra burden it is add onto the system (CPU,
Memory etc). We are using Oracle 8.1.6 on HP-UX 11.00.
If there are some other alternatives, please let me know.
Thanks.




Dharminder Kumar



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 ts

2002-03-08 Thread Jack C. Applewhite


Ayyapps,

Use the DBMS_SPACE_ADMIN supplied PL/SQL package.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Friday, March 08, 2002 4:03 AM
To: Multiple recipients of list ORACLE-L



Hi all 
Can we able to change the dictionary managed tablespace to locally managed
tablespace. if so how? 
Ayyapps 



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Oracle Indexing

2002-03-08 Thread Jack C. Applewhite


Sinardy,

5 DB blocks is the default for INITIAL and NEXT extents, if you don't
specify them, not necessarily the recommended size.

The extent size of any segment depends more on the size of the segment, but
should always be an integer multiple of db_file_multiblock_read_count.  The
best recommendation is to have one or a few standard extent sizes in
locally-managed tablespaces.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Friday, March 08, 2002 1:48 AM
To: Multiple recipients of list ORACLE-L


Hi all,


Oracle said:
The best extent size of an index to minimize fragmetation is 5 times of db
block size.


My question is why 5 times is the recommended size, why not 4 times or 6
times or perhaps 0.5 of your db block size.



Thanks


Sinardy



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Primary keys

2002-03-07 Thread Jack C. Applewhite


Harvinder,

Every table needs a PK.  Otherwise, you have no way of identifying a row
uniquely.

You also need an index on each FK, to prevent locking problems when
updating/deleting the parent table.  You can kill two birds with one stone
by making your history tables' PKs (enforced by unique indexes) be a
concatenation of the FK column(s) - make it(them) the leading column(s) -
with at least one more column that produces a unique ID for each row.

Then you've got both a PK and an indexed FK and everyone is happy! ...except
maybe your duhvelopers, who are obviously not all that knowledgeable, so who
cares if they're happy?!?!  ;-)

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Singh
Sent: Thursday, March 07, 2002 11:13 AM
To: Multiple recipients of list ORACLE-L


HI,

We have some history tables that are pointing to parent tables.
Parent tables has primary key.
Our developers are saying that we have foreign key from history table to
parent table and we don't need primary key on history table.
Does there are any benefits if we have primary key(concatenated) on history
table. OR
do we should leave history tables without primary key

Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
  INET: [EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Restrictng tables during export/import

2002-03-07 Thread Jack C. Applewhite


Harvinder,

IMHO, you are using the wrong approach.  FGAC is meant for row-level
security.  In fact, DBMS_RLS (RLS = Row-Level Security) is the package that
supports FGAC.

It's a lot easier to use the TABLES parameter in your export and import
parameter files to control which tables are exported/imported.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Singh
Sent: Thursday, March 07, 2002 10:18 AM
To: Multiple recipients of list ORACLE-L


We are testing  Fine-Grained Access Control to export the complete schema
excluding some tables.
This works well for export but during import it try to import all tables.
Is there any way we can restrict import of some tables using Fine-Grained
Access Control
.We are using function as specified in metalink as follows:
CREATE or REPLACE FUNCTION exclude_table
 (obj_schema VARCHAR2, obj_name VARCHAR2) RETURN VARCHAR2 IS
d_predicate VARCHAR2(2000);
BEGIN
if sys_context ('USERENV', 'SESSION_USER') = 'EXP_DB'
THEN d_predicate := '1=2'; else d_predicate := ''; end if;
RETURN d_predicate;
END exclude_table;

Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Hundreds of schemas in one instance?

2002-03-06 Thread Jack C. Applewhite


Ben,

Have you investigated Oracle8i's Virtual Private Database feature - A.K.A.
Fine-Grained Access Control (FGAC) or Row-Level Security (DBMS_RLS).  It's
available in 8i EE.

It keeps users logically separate, even if you only use one schema.  I
believe it would be much easier to maintain than hundreds of schemas -
adding/deleting rows is much easier than adding/dropping schemas and their
objects.

I implemented FGAC in an internationally-accessed Web-based application in
which each customer's data was kept separate from every others, but in a
single schema.  Works very well.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Wednesday, March 06, 2002 1:29 PM
To: Multiple recipients of list ORACLE-L


Hi

Our university wants to set up a server that will provide groups
on campus with a standard set of services for web hosting, data
collection or whatever they want to do. As much as possible each
user should have their own isolated chunk of the server.
An Oracle database will sit in the background to provide whatever
database services they need. My thought is to go with one instance
with a unique schema, including separate tablespaces/datafiles, for each
user.

Some of the pros for this are:
- easy set up for new users
- easy software upgrades
- simplified tuning, backups, monitoring, auditing
- user isolation, especially disk space usage
- multiple instances would use far more memory

Some cons are:
- everyone must use the same release of the software
- database down time affects everyone
- might run into system maximums, for example max. number of datafiles
- an enormous SYSTEM tablespace

Has anyone had to this kind of thing? Any comments or suggestions?

TIA,

Ben




==
 Ben Poels - Senior Technical Analyst - Queen's University at Kingston
 Phone: 613.533.2449  Fax: 613.533.2168  Email: [EMAIL PROTECTED]
==



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: EXTENTS?

2002-03-04 Thread Jack C. Applewhite


Yes, this one will get a lot of traffic, I'll bet.

I just looked in the 8.1.7 docs (SQL Reference - Storage Clause) and it says
that the minimum INITIAL is 2 DB blocks for non-bitmapped segments, 3 for
bitmapped segments.  The minimum for NEXT is 1 DB block.

Unlesss it's a new 9i feature, a DB block can belong to one and only one
Extent.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
[EMAIL PROTECTED]
Sent: Monday, March 04, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L



I am confused. Are you talking of multiple extents in a block? Throws my
fundamentals topsyturvy.

Raj




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Which FM???

2002-03-01 Thread Jack C. Applewhite




Rodd,

Oracle8i 
Reference
Jack
Jack C. 
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, 
Inc.Austin, 
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Rodd HolmanSent: 
  Friday, March 01, 2002 12:53 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Which FM???I'm looking for 
  descriptions of the more cryptic columns in some of the V$'s. Anyone 
  know which FM those are in? TIA Rodd

RE: SNP0...SNPx Memory Hogs

2002-02-28 Thread Jack C. Applewhite


Ruth,

I'm sure they're Oracle Job Queue processes because the lines below show up
in my Alert.Log when I stop and start them by issuing the commands in the
first two lines.  That's when up to 500MB of memory is freed by the Oracle
Process.

BTW, SNMP stands for Simple Network Management Protocol (I'm pretty sure, at
least).  It's a TCP-related thingey - a technical term  ;-).  That's totally
different (again, I'm pretty sure) from the Oracle SNP Job Queue processes.

From Alert.Log:
--
...
ALTER SYSTEM SET job_queue_processes=0;
ALTER SYSTEM SET job_queue_processes=4;
Thu Feb 28 07:01:19 2002
Restarting dead background process SNP0
SNP0 started with pid=8
Thu Feb 28 07:01:19 2002
Restarting dead background process SNP1
SNP1 started with pid=9
Thu Feb 28 07:01:19 2002
Restarting dead background process SNP2
SNP2 started with pid=10
Thu Feb 28 07:01:20 2002
Restarting dead background process SNP3
SNP3 started with pid=11
...
---

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Gramolini
Sent: Thursday, February 28, 2002 8:53 AM
To: Multiple recipients of list ORACLE-L


At least in 8.0.x the oracle intelligent agent process are dbsnmp processes.
Are you sure that these are oracle processes and not OS related.

Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, February 27, 2002 4:20 PM


 8.1.7.2.5 under Win2k Server

 I just discovered that my four SNP processes were sitting around, doing no
 work, taking up about 500MB of RAM.  Is this normal?
...



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: poor query performance

2002-02-28 Thread Jack C. Applewhite


Bill,

Try this:

Delete
From  T1
Where F1 In
(
 Select F1
 From   T1
 Minus
 Select PK
 From   T2
);

This will delete T1 rows for which there's no matching PK in T2 and will
most likely be quicker than the Not Exists query, though you never can say
for sure!   ;-)

Oh, and the Cost that Explain Plan reports is purely relative and can't be
used for comparison across different queries.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Bill
Sent: Thursday, February 28, 2002 3:24 PM
To: Multiple recipients of list ORACLE-L


I have a query that deletes rows from a table with 57K rows, as follows

delete from T1 where not exists
(select T2.PK from T2
where T2.PK = T1.F1);

T2.PK is the Primary Key on Table T2
T1.F1 is an indexed field on table T1

Explain plan shows a low cost (80) but the delete takes about 10+ minutes.
T1 has about 57K rows
T2 has about 29500 rows

other queries with costs in the 700 range are pretty quick - this one's a
dog

any ideas?

thanks

Bill Magaliff
Framework, Inc.
914-631-2322



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

SNP0...SNPx Memory Hogs

2002-02-27 Thread Jack C. Applewhite


8.1.7.2.5 under Win2k Server

I just discovered that my four SNP processes were sitting around, doing no
work, taking up about 500MB of RAM.  Is this normal?

In Task Manager, the Oracle process was showing to be using about 1.4GB.
After I issued Alter System Set Job_Queue_Processes=0 it shrank to about
900MB.  I then issued Alter System Set Job_Queue_Processes=4, but the Mem
Usage stayed the same.

None of those SNP processes had done any work (i.e., run any jobs) for about
10 hours.  I would have expected the SNP processes to release memory when
their jobs finish.

I've been trying to figure out what processes were chewing up RAM, ramping
up over the course of 3 or 4 days.  Some Java processes that we run were
found to be hogs, but after I got the developers to disconnect/reconnect
occasionally, those sessions were OK.

I finally found the culprits when I was checking sessions' session pga
memory.  A couple of sessions were using about 200MB each.  I checked to
see what SQL they'd been running and found it to be dbms_ijob calls, which
lead me to suspect the SNP processes - alas, Win2k doesn't let you look at
individual Oracle processes like UNIX does.

Anyway, I can't find any mention anywhere that dormant SNP processes can be
memory hogs.  Am I missing something?  For now I'll set Job_Queue_Processes
to 0 and back to 4 right after the heavy work each night.

Any init parameters I'm missing? ...other suggestions?

Thanks.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Global variable in Pl/SQL

2002-02-26 Thread Jack C. Applewhite


Andrey,

You can't make a PL/SQL variable value visible to all sessions.

You'll have to put the value in a row in a table and commit it.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Tuesday, February 26, 2002 2:44 PM
To: Multiple recipients of list ORACLE-L


Dear list !
How can i make a package variable visible to all sessions , please ?
I.e. i have a package PPP that has a package variable VVV.
There is a stored proc in the package , PPP.SP1
In this procedure i set the package variable VVV to some value.
Now , i want all the sessions connected to the instance to be able to see
this value of PPP.VVV .

Thanks a lot in advance !



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: datafile compression


Keith,

I've been using PKZip (version 4.00) under Win2k to zip and unzip Oracle
export dump files for months without a hitch.  In fact, I just now zipped
and unzipped a 400MB 8i datafile with no errors.

Are you sure there wasn't some other factor that affected the test you did?
Do you have the latest version of PKZip?

Command line PKZip would be my first choice.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Worley
Sent: Thursday, February 07, 2002 5:23 PM
To: Multiple recipients of list ORACLE-L


Hello everyone. Let me say first to all who are on the list group THANK YOU.
From reading the emails I receive I have learned a LOT the past few weeks.

My question here is. I am trying to find a way to compress the datafiles
when I copy them to a backup folder on NT. Does anyone have a suggestion as
to what product they use to compress datafiles? Has anyone ever used the
COMPRESS option on a folder in NT to compress datafiles? If so, was there
ever a problem. I did try pkzip and when the files unzipped it gave errors
abour the crc checks. I also researched this and didn't find much about
compressing datafiles BUT i'm not that good with the oracle doc's yet
either.

Thanks in advance for any responses to this email and everyone have a good
day.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Where does a DBA go from here?


Rich,

The best Oracle training I've ever had by far is playing around with a
small test DB on a laptop or PC.  Stress it, hurt it, recover it, tune it,
etc., etc.  When done in conjunction with one of the fine books mentioned by
others on this list - even better.

I've only had one official Oracle training course - in the spring of 1989.
It was 4-1/2 days of RDBMS v5, SQL*Forms and RPT/RPF.  Since then I've
attended some of the mini courses at IOUG, ODTUG, Open World, etc. - nice
little hits of the latest technologies.  I studied for my Oracle8 OCP
exams by working through Jason Couchman's book and beating up an 8.0.5 DB on
my PC - learned a hell of a lot.

My on-going study plan for Oracle skills boils down to Beating, Browsing
and Books.
 - Beat up a test database
 - Browse fine Web sites like Steve Adams' IXORA (and beat up the DB)
 - Buy and read good books (and beat up the DB)

I think I've learned about as much as if I'd sat in a bunch of classes - and
saved a pile of money.   ;-)

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068

-Original Message-
Sent: 07 February 2002 21:07
To: Multiple recipients of list ORACLE-L


So, there I am.  I've taken the main Oracle courses -- Intro to SQL, DBA
(Oracle 7!), Backup  Recovery, Network Admin, and Perf Tuning.  Now where
do I go for more Oracle training?

This is sparked by a recent perceived lag in one of our new databases.
We've tracked it down to a possible hot block or two, but I never used X$BH
or V$LATCH_CHILDREN in any of my Oracle classes.  And I *know* I'm far from
being ready for an Internals class.  So how do I get from here to there?

education.oracle.com doesn't seem to have a whole lot other than Internals.
Or is that where I'm at now?

Confused and no beer.

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: capacity planning??




Shibu,

Go 
to
http://www.orapub.com

Craig 
Shallahamer is THE Guru on the subject. I took his course a couple years 
ago - excellent!

Jack
Jack C. 
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, 
Inc.Austin, 
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of ShibuSent: Friday, 
  February 08, 2002 12:13 AMTo: Multiple recipients of list 
  ORACLE-LSubject: capacity planning??
  Hi all
  
  Can anyone send me a doc or white 
  paper on capcity planning??
  
  
  regards,
  shibu

RE: Removing data form a table


Lance,

Truncate Table YourTableHere ;

This deletes all the rows in the table without the cost of rollback.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Friday, February 08, 2002 3:25 PM
To: Multiple recipients of list ORACLE-L


I want to create a procedure the drops all the data from a table.

I was trying to drop table then create this proved to be extremely
complicated in Oracle.  I think the above route is much better.

Does anyone know how to do this?

Lance



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Oracle and Document Management: any real stories?

2002-02-07 Thread Jack C. Applewhite


To add to what Marin suggests...

Oracle's interMedia Text option supports full text indexing and a powerful
set of full text query operators.  We use Oracle8i's iM Text to index and
query about 15 million plain text and HTML documents.

However, to get the docs in and out and create a user-friendly interface
for the query operators, you need N programmers (and DBAs) writing M lines
of code - in our case Cold Fusion, Java, and PL/SQL.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Dimitrov
Sent: Thursday, February 07, 2002 12:07 PM
To: Multiple recipients of list ORACLE-L




- Original Message -

 TOPIC: Oracle and Document Management: any real stories or information?

 Does anyone have any experience with Oracle and Document Management that
 they're willing to share? [I'm cross-posting to Oracle Apps and Oracle
 Server.]


check Oracle Internet File System - it provides check-in/check-out  and
versioning functionality, access control, several interfaces to access the
content

http://technet.oracle.com/products/ifs/content.html


hth,

Marin



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: 8.1.7.2.5 interMedia Text Slow Sync - SOLVED

2002-01-31 Thread Jack C. Applewhite


For what seems to be the few of you who use interMedia Text, I found the
problem - it was ME. :-(

I had set (in CTX_Parameters) Max_Index_Memory to 200MB (from the default of
12.5MB) and used 200MB when I created indexes, but had overlooked
Default_Index_Memory (default of 12.5MB), which is used by
CTX_DDL.Sync_Index.

After I set Default_Index_Memory to 200MB, re-syncs jumped from about 750
CLOBs per minute to over 2,500 per minute.  Oracle was able to sort an
entire batch of 157,000 CLOBs in memory, instead of only about 8,000 at a
time - surprise, surprise with 16 times the memory available.

BTW, it didn't hurt that I spread the datafiles of the tablespaces that hold
the DR$$X segments across 2 drives, instead of just 1.  That boosted
re-syncs from 600 CLOBs per minute to 750.  I/O distribution never hurts.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Applewhite
Sent: Thursday, January 24, 2002 9:35 AM
To: Multiple recipients of list ORACLE-L


We are experiencing very slow interMedia Text resyncs ( CTX_DDL.Sync_Index )
under 8.1.7.2.5 on Win2k Server.

We upgraded from 8.1.6.0.0 to 8.1.7.2.5 earlier this month (both under Win2k
Server) on essentially the same hardware.  We used to see about 700-2000
CLOB documents per second indexed under 8.1.6, but now see only about
400-800.  The low-high numbers relate to the size of the index being
sync'd - low just before we roll out a monthly partition (total 2 million
CLOBs), high just after (total 1 million CLOBs).

Query performance is better under 8.1.7 - partly because I spread the
DR$$I table across 3 drives.  By the looks of the I/O pattern, I should
spread DR$$X (the index on DR$$I table) across 2 or 3 drives as well.

Anybody have any words of wisdom as to what I can do to speed things up?

BTW, I'm curious at how 8.1.7 balances I/O.  While it's writing to the
DR$$X segment, it doesn't write to the online redo logs - it waits until
it's reading from DR$$X segment before it writes to the redo logs.  I
wonder why it doesn't do the writes to redo in parallel with the writes to
DR$$X.

Thanks.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Where to store BLOB's

2002-01-29 Thread Jack C. Applewhite


Yechiel Adar,

We use option #2 (LOB segments in another tablespace).  It allows faster
queries of the non-LOB data in the main table and gives us flexibility as
to storage parameters for the LOB segment(s).  We also use interMedia Text
to index the LOB (CLOB, in our case) column and those index segments are in
yet another tablespace.

BTW, we have about 15 million CLOB documents, so we're not talking a tiny
amount of data here.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Tuesday, January 29, 2002 10:56 AM
To: Multiple recipients of list ORACLE-L


Hello all

We are designing a new application.
This application stores files that were FTP to the clients
and keep then for resubmitting (if the user ask for them).
There are two formats:
1) Standard records whose internal format is known.
These files lets the user ask for a subset of the records,
i.e. all the records for branch 
that were submitted in the last week, etc. .
2) Strange files that are moved as a file.
Here the application is not aware of the internal format,
i.e. excel files, and these files are going to be stored as Blob's
and the user can ask only for the whole file again.

I know that you can store Blob's in three ways: (already rtfm a little)
1) In the record (up to 4k), more goes to another tablespace.
2) In Oracle but in another tablespace (always).
3) As external files.

I would like to learn from your experience what is the best,
easiest to implement, easiest to admin, less demanding on Oracle
and all the good stuff.

TIA

Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

8.1.7.2.5 interMedia Text Slow Sync

2002-01-24 Thread Jack C. Applewhite


We are experiencing very slow interMedia Text resyncs ( CTX_DDL.Sync_Index )
under 8.1.7.2.5 on Win2k Server.

We upgraded from 8.1.6.0.0 to 8.1.7.2.5 earlier this month (both under Win2k
Server) on essentially the same hardware.  We used to see about 700-2000
CLOB documents per second indexed under 8.1.6, but now see only about
400-800.  The low-high numbers relate to the size of the index being
sync'd - low just before we roll out a monthly partition (total 2 million
CLOBs), high just after (total 1 million CLOBs).

Query performance is better under 8.1.7 - partly because I spread the
DR$$I table across 3 drives.  By the looks of the I/O pattern, I should
spread DR$$X (the index on DR$$I table) across 2 or 3 drives as well.

Anybody have any words of wisdom as to what I can do to speed things up?

BTW, I'm curious at how 8.1.7 balances I/O.  While it's writing to the
DR$$X segment, it doesn't write to the online redo logs - it waits until
it's reading from DR$$X segment before it writes to the redo logs.  I
wonder why it doesn't do the writes to redo in parallel with the writes to
DR$$X.

Thanks.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Searching Inside CLOBs

2002-01-21 Thread Jack C. Applewhite


Sundeep,

You couldn't have made a better case for using interMedia Text index(es) on
your CLOB column(s) if you'd had help from Oracle Marketing!   ;-)

interMedia Text is very powerful and efficient.  We use it to index and
search almost 15 million CLOB documents, many in HTML format.  interMedia
even supports section searching, so you can search only between selected
tags within a CLOB.

Check out the interMedia Text docs.  It's even available in Standard
Edition.  You (and your users) will love it.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
maini
Sent: Monday, January 21, 2002 3:06 PM
To: Multiple recipients of list ORACLE-L


Our developers store XML docuements inside CLOBS and
want to search the CLOB contents based on a search
string. I have lot of concern about such queries but
could benefit from someone on the list who has already
solved the performnce issues with such searches.
Typical existing queries I have come across are:
SELECT columns
 FROM list of tables
WHERE join conditions
  AND UPPER(tab1.topic) LIKE '%:in_string%'
   OR UPPER(tab2.subtopic) LIKE '%:in_string%'
   OR .
   OR dbms_lob.instr(bodytext,:in_string,1,1)  0;
Note: in_string is always upper case but the column
contents are not.

Concerns:
1. Poor index selection due to UPPER
2. CLOB searches are wrong as the CLOB contents are
case sensitive. One way to remedy this would be to
build a user defined function which will read the CLOB
col. in chunks and look for string matches inside
individual chunks till a success or end of CLOB.
Either way that is a pretty intense way to search for
a string among table data when a user is waiting for a
quick response.

Keyword searches are supported differently. This
mechanism is only for random string searches. Any cues
or suggestions? Especially for case sensitive searches
inside CLOBS?

TIA
Sundeep


=

Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: multiple extents are OK, dagnabbit!

2002-01-17 Thread Jack C. Applewhite

Title: Message



Jerry,

If 
they want to pay you to reduce their extents, then let 'em! 
;-) "A fool and his money are soon 
parted."

If 
they employ youand want you to work weekends on this, then it's worth the 
effort to educate them. I'm surprised an official Oracle white paper 
didn't convince them. You may just be out of luck - adamant, entrenched 
misinformation is sometimes difficult to dislodge.

If my 
anecdotal situation could be of any help, here it is.

We 
just moved our production 8.1.6.0.0 database to 8.1.7.2.5 on a new, but almost 
identical server.
Old 
server's OS was Windows 2000 Server with Service Pack 2 - new server, the 
same.
Old 
server had dual 550MHz Xeon CPUs - new server, the same.
Old 
server had 2GB RAM - new server has 4GB RAM (of which Oracle can only use 2GB 
anyway).
Old 
server had eighteen 36GB drives - new server has twenty 36GB drives. In 
both cases configured as JBOD (Just a Bunch Of Drives - no RAID, no mirroring, 
no striping of any kind).

Our 6 
documents tableseach had (andhas) its own drive 
andeachhad (and has) about 2 million rows. The out-of-line 
CLOB documents take up about 20-30GB for each table. Each of those 
segments had between 20,000 and 30,000 1MB extents. For the year we 
operated that way, we never had a problem with performance, even with a full 
interMedia Text index on the CLOB column.

When 
we moved theDBto 8.1.7.2.5, I pre-created those tables with 100MB 
extents for the CLOB segments before I imported the documents. So, now 
we're down to a few hundred extents per segment, instead of tens of 
thousands. It hasn't made any noticeable difference on performance. 
If numbers of extents really mattered, a 100 to 1 reduction would have made an 
impact - it didn't.

Whatdid make a difference was spreading the main 
token table (DR$...$I) of the interMedia Text index across 3 drives, instead of 
one. Distributing I/O has significant impact. Number of extents per 
segment has close to zero impact. The Oracle white paper is dead-on 
accurate.

Hope 
my experience helps convince your boneheaded clients. 
;-)

Jack
Jack C. 
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, 
Inc.Austin, 
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, 
  GeraldSent: Thursday, January 17, 2002 3:46 PMTo: 
  Multiple recipients of list ORACLE-LSubject: multiple extents are 
  OK, dagnabbit!
  Hi there - 
  
  
  I'm trying to 
  convince a client that multiple extents for a table will not hurt their 
  performance. It's a PeopleSoft app, and PeopleSoft is telling them that they 
  need to reorg any object with greater than 10 extents (even indexes). This 
  Oracle 8.1.6.
  
  I've referenced 
  the "How to Stop Defragmenting and Start Living: The Definitive Word on 
  Fragmentation" white paper by Bhaskar Himatsingka and Juan Loaiza of Oracle. 
  That didn't convince them. I tried to explain that Oracle reads BUFFERS and 
  not extents, etc., but that didn't work.
  
  I'm about to open 
  a vein.
  
  Does anybody have 
  any references that they can point me to? (Something from PeopleSoft would be 
  ideal, though I would be suprised if it existed.) I read a rant on somebody's 
  web site a while back that was really good, but alas I cannot remember his 
  name or URL. (I blame my kids for my failing memory).
  
  
  Thanks!
  
  - 
  Jerry

RE: Partitions

2002-01-16 Thread Jack C. Applewhite


The latter.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Wednesday, January 16, 2002 3:07 PM
To: Multiple recipients of list ORACLE-L


List,
If we use partitioning for a big tables after finish development, do we have
to change the code for accessing diffrent partition or this will handle by
oracle itself.
Thanks



Hamid Alavi
Office 818 737-0526
Cell818 402-1987




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Partitions

2002-01-16 Thread Jack C. Applewhite


You asked which of two outcomes will occur if you partiton tables after
development:
1. ...do we have to change the code for accessing diffrent partition...
 or
2. ...this will handle by oracle itself.

Of your two proposed outcomes, 1. is the former and 2. is the latter.
Oracle will handle access to table partitions automatically - your code does
not have to be modified.

I was attempting to answer your question in a succinct manner - sorry for
not being clear enough.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Wednesday, January 16, 2002 4:25 PM
To: Multiple recipients of list ORACLE-L


what do you mean the latter Jack

-Original Message-
Sent: Wednesday, January 16, 2002 2:00 PM
To: Multiple recipients of list ORACLE-L


The latter.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Wednesday, January 16, 2002 3:07 PM
To: Multiple recipients of list ORACLE-L


List,
If we use partitioning for a big tables after finish development, do we have
to change the code for accessing diffrent partition or this will handle by
oracle itself.
Thanks



Hamid Alavi
Office 818 737-0526
Cell818 402-1987



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Recover Primary DB from Standby DB

2002-01-07 Thread Jack C. Applewhite


Jeremiah,

Thanks for the info.  It's nice not to be an idiot!  ;-)

...and so much easier to copy up-to-date datafiles from the Standby than to
restore from tape backup in the event of a disk failure on the Primary.
We'll use our Standby only in the event of a failure more catastrophic than
a disk or two.

BTW, I finally found mention in the docs that Oracle sanctions using
datafiles from the Standby to recover the Primary database.  It's the very
last bullet point in the section Additions to Compatibility and Operational
Requirements in chapter 12 (Standby Database) of Oracle8i Documentation
Addendum Release 3 (8.1.7).  It's not mentioned in the Compatibility and
Operational Requirements in chapter 1 (Standby Database Concepts) in the
8.1.6 Standby docs.
---
You can use standby database datafiles to recover a primary database only
on Oracle release 8.0.4 or higher.
---

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Wilton
Sent: Friday, January 04, 2002 2:10 PM
To: Multiple recipients of list ORACLE-L


There is no problem with using the standby as a source of good files
in the event a file on the primary becomes media corrupt.  Once
stopped, you can use a standby just like a good backup of the
database.  The primary will not reject the file.  This is particularly
useful for single tablespace complete recoveries.

It is not advisable to use the standby as the *only* backup of a
database.  If you discover that for whatever reason you need to
recover to a point in time prior to the standby, you will have to
restore from a prior physical backup and roll forward.

On another topic:

On Fri, 4 Jan 2002, Grabowy, Chris wrote:

 Errr...what's the point in having a standby database then?  If you
 have a media failure then you fail over to your standby database,
 and your up.  Once the standby database comes up then you will have
 to rebuild the old primary database from the old standby anyway,
 since you now have all these new transactions hitting the old
 standby server.

If you perform graceful failover, A.K.A. role reversal, you can have
the former primary pick up as the standby immediately after failover
without recopying.  I cover this topic in my Openworld paper on my
site.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Oracle 8.1.7 on MS 2000

2002-01-07 Thread Jack C. Applewhite


Yes, under Windows 2000 Server.

We installed 8.1.7.0.0, then applied the 8.1.7.2.1 patch, then applied the
8.1.7.2.2 patch, and finally applied the 8.1.7.2.5 patch.  Then I built the
database and imported our old 8.1.6.0.0 data last weekend.  Looks OK so
far...less memory leakage...DBMS_Stats works nicely - it was a dog (in my
limited trials) on 8.1.6.

Our DB is spread across about 340GB of datafiles on 20 disks, so there's a
significant amount of data.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Monday, January 07, 2002 2:11 PM
To: Multiple recipients of list ORACLE-L


Yes. Install 8.1.7, upgrade to 8.1.7.2.1, only then create databases. One of
the interim patches between the two levels knocked out SNP processing in
existing databases - no job would run. Unfortunately, testing jobs was not a
part of the overall QA process so I don't know exactly which patch was bad.
Oracle could not fix the problem and after battling it for a week I ended up
rebuilding databases. There were other issues, specifically, Netassist
wouldn't work with Names Server in 8.1.7.0, it would just crash, as well as
some other setback I can't recall.

Having said that, the patched up 8.1.7.2.1 on Win2k seems stable and solid.
After running it for 6 months we are planning on moving into production
shortly. If you're going Windows, this is the way to do it.

Gary Weber
Senior DBA
Charles Jones, LLC||Superior Information Services, LLC
609-530-1144, ext 5529

-Original Message-
L.
Sent: Monday, January 07, 2002 2:51 PM
To: Multiple recipients of list ORACLE-L



Any experience with Oracle 8.1.7 on MS2000?

Ron Smith



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Recover Primary DB from Standby DB

2002-01-04 Thread Jack C. Applewhite


Am I an Idiot?

I've maintained a Managed Standby database (8.1.6 under Win2k) for a year
now.  We've now moved to new servers (8.1.7.2.5 under Win2k) and will again
have both Primary (Production) and Managed Standby databases.

In planning a new backup stragegy, it occurs to me (why not ever before?)
that I should be able to use the datafiles of my Standby DB (along with the
Primary's archived and online redo logs) to recover my Primary DB in the
event of media failure.

In the event of a drive failure on the Primary server, shouldn't I be able
to shutdown the Standby DB and copy it's relevant datafiles to the Primary,
then use the Primary's archived and online redo logs to recover those
datafiles to the point of drive failure?

It makes sense to me, but nowhere in the docs have I found mention that this
is an appropriate action.  Am I missing something?

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Error in Package

2001-12-17 Thread Jack C. Applewhite




Ramon,

You've 
declared the Procedure SELECCIONAR differently in the package spec and 
body. Oracle must think you're creating an overloaded procedure with 
different arguments for each version. The spec and body declarations must 
match.

Jack
Jack C. 
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, 
Inc.Austin, 
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Ramon EstevezSent: 
  Monday, December 17, 2001 9:21 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Error in Package
  Hi 
  List,
  
  What is wrong with this 
  package, I compared it to one example in a book and
  looks equal, but still 
  get an error.
  
  CREATE OR 
  REPLACE PACKAGE MANTENIMIENTO_COMPROBANTES 
  AS TYPE COMPROBANTES_RECORD IS 
  RECORD ( 
   
  GRUPO 
  COMPROBANTES.GRUPO%TYPE, 
  COMPANIA 
  COMPROBANTES.AGENCIA%TYPE, 
  TIPO_COMPROBANTE 
  COMPROBANTES.TIPO_COMPROBANTE%TYPE, 
  COMPROBANTE 
  COMPROBANTES.COMPROBANTE%TYPE, 
  FECHA 
  COMPROBANTES.FECHA%TYPE, 
  AG_CUENTA 
  COMPROBANTES.AG_CUENTA%TYPE, 
  CUENTA 
  COMPROBANTES.CUENTA%TYPE, 
  DETALLE 
  COMPROBANTES.DETALLE%TYPE, 
  CONCEPTO 
  COMPROBANTES.CONCEPTO%TYPE, 
  ESTATUS 
  COMPROBANTES.ESTATUS%TYPE, 
  ESTATUS_IMPRESION 
  COMPROBANTES.ESTATUS_IMPRESION%TYPE, 
  DESC_AGENCIA 
  AGENCIAS.DESCRIPCION%TYPE, 
  CUENTA_CONTABLE 
  DATOS_BALANCES.CUENTA_CONTABLE%TYPE, 
  DESC_CUENTA 
  CUENTAS.DESCRIPCION%TYPE, 
  DESC_CLIENTE 
  CLIENTES.NOMBRE_COMERCIAL%TYPE  
  ); Para Seleccionar Registros de la Tabla-- 
   TYPE COMPROBANTES_REFCURSOR IS REF 
  CURSOR RETURN 
  COMPROBANTES_RECORD; Para Instrucciones 
  DML-- TYPE 
  TABLA_COMPROBANTES IS TABLE OF 
  COMPROBANTES_RECORD INDEX BY 
  BINARY_INTEGER; Procedimiento para Seleccionar registros de la 
  tabla-- PROCEDURE SELECCIONAR 
  (PCOMPROBANTESQRY IN OUT 
   
  COMPROBANTES_REFCURSOR, 
  PGRUPO IN 
   
  COMPROBANTES.GRUPO%TYPE, 
  PCOMPANIA IN 
   
  COMPROBANTES.COMPANIA%TYPE, 
  PTIPO_COMPROBANTE IN 
   
  COMPROBANTES.TIPO_COMPROBANTE%TYPE, 
  PCOMPROBANTE IN 
   
  COMPROBANTES.COMPROBANTE%TYPE 
  );
  
  END 
  MANTENIMIENTO_COMPROBANTES;
  
  The 
  package header creates fine.
  
  This is the package body
  
  
   1 
  -- 2 -- Desarrollo del Paquete para Mantenimiento Tabla de 
  Comprobantes 3 -- 4 CREATE OR REPLACE PACKAGE 
  BODY MANTENIMIENTO_COMPROBANTES AS 5 -- 6 -- 
  Procedimiento para Seleccionar registros de la tabla 7 
  -- 8 PROCEDURE SELECCIONAR 
  (PCOMPROBANTESQRY IN OUT COMPROBANTES_REFCURSOR, 
  9 
  PGRUPO IN 
  NUMBER,10 
  PCOMPANIA IN 
  NUMBER,11 
  PTIPO_COMPROBANTE IN 
  NUMBER,12 
  PCOMPROBANTE IN NUMBER) 
  IS13 
  BEGIN14 OPEN 
  PCOMPROBANTESQRY15 
  FOR16 SELECT 
  C.GRUPO, 
  C.COMPANIA, 
  C.TIPO_COMPROBANTE,17 
  C.COMPROBANTE, 
  C.FECHA, 
  C.AG_CUENTA,18 
  C.CUENTA, 
  C.DETALLE, 
  C.CONCEPTO,19 
  C.ESTATUS, 
  C.ESTATUS_IMPRESION, 
  A.DESCRIPCION20 
  DESC_AGENCIA, D.CUENTA_CONTABLE, 
  CT.DESCRIPCION21 
  DESC_CUENTA, CTE.NOMBRE_COMERCIAL 
  DESC_CLIENTE22 
  FROM23 
  COMPROBANTES C, AGENCIAS A, DATOS_BALANCES 
  D,24 
  CUENTAS CT, CLIENTES 
  CTE25 
  WHERE26 
  C.GRUPO 
  = 
  PGRUPO 
  AND27 
  C.COMPANIA = 
  PCOMPANIA 
  AND28 
  C.TIPO_COMPROBANTE = PTIPO_COMPROBANTE 
  AND29 
  C.COMPROBANTE = 
  PCOMPROBANTE 
  AND30 
  A.AGENCIA 
  = C.AGENCIA 
  AND31 
  D.GRUPO(+) = 
  C.GRUPO 
  AND32 
  D.COMPANIA(+) = 
  C.COMPANIA 
  AND33 
  D.AGENCIA(+) = 
  C.AG_CUENTA 
  AND34 
  D.CUENTA(+) = 
  C.CUENTA 
  AND35 
  CT.GRUPO(+) = 
  D.GRUPO 
  AND36 
  CT.COMPANIA(+) = 
  D.COMPANIA 
  AND37 
  CT.CUENTA(+) = 
  D.CUENTA_CONTABLE 
  AND38 
  CTE.CLIENTE = 
  D.CLIENTE;39 END 
  SELECCIONAR;40* END MANTENIMIENTO_COMPROBANTES;41 
  /
  
  Warning: Package 
  Body created with compilation errors.
  
  SQL SHOW 
  ERRORS PACKAGE BODY MANTENIMIENTO_COMPROBANTES;Errors for PACKAGE BODY 
  MANTENIMIENTO_COMPROBANTES:
  
  LINE/COL 
  ERROR 
  -35/16 
  PLS-00323: subprogram or cursor 'SELECCIONAR' is declared in 
  a package specification 
  and must be defined in the package body
  Any help, suggestions 
  would be appreciated !!!
  
  
  Ramon E. 
  Estevez
  [EMAIL PROTECTED]
  Dominican Republic
  809-565-3121

RE: DB SIZE ?

2001-12-12 Thread Jack C. Applewhite


Seema,

Several possible solutions - you pick.

- Delete Data
- Drop Tables
- Drop Indexes
- Drop Tablespaces

That ought to get you started.  ;-)

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Wednesday, December 12, 2001 12:20 PM
To: Multiple recipients of list ORACLE-L



hi
Just a thought.How do we reduce Database size?
Thanks
-Seema



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 Weakest Link

2001-12-07 Thread Jack C. Applewhite


Paul,

It's very thoughful of you to pre-plan an activity during your party, but,
with all due respect, YOU'VE GOT TO BE KIDDING!  Sound like fun?
NOO!!  DBA trivia at a Chrismas party?!?!?  Come on, think of
something EVERYONE (significant others included) can enjoy!  DBAs have a bad
enough reputation for not having a life - you don't need to reinforce it.

IMHO, shop talk should be strictly banned at parties.  Think of something
a bit more fun - movie trivia, music lyrics, the best jokes I've ever
heard.  There should be plenty of internet sites at which you could find
lots of info. for topics such as these.

Or - my favorite - have anyone who knows how to dance teach the others a
step or two of Swing, Salsa, etc.  Get those normally sedentary DBAs off
their duffs and on their feet!  Have a dance contest!

Jokes could have the party-goers rolling on the floor - DBA trivia
absolutely WON'T.

Respectfully,
Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Friday, December 07, 2001 8:20 AM
To: Multiple recipients of list ORACLE-L


Hello DBAs,

I'm having a company holiday party today, and as you all may know my company
employs mostly DBAs. (Pythian is an Oracle DBA Outsourcing shop.)

I'm thinking of having a DBA Weakest Link game for fun at some point of
the evening. Yes, I know, the partners/husbands/wives will think it's a bit
of a bore, but imagine the fun for us DBAs! :-)

So what I'm trying to do is get many, many weakest link-style questions
(with answers) ready. I want them mostly to be easy, but with the odd hard
one snuck in for unfairness. Please help out by submitting your questions,
and I'll summarize and post the complete list back to the list when I'm
done!

Sound like fun? I'll get us started with a format.

...

Paul



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: RAID system max throughput

2001-12-07 Thread Jack C. Applewhite


Dennis,

I'm no RAID guru, but I can sure imagine disk heads thrashing around, trying
to satisfy a mix of sequential and random reads and writes, causing the DB
to wait, but not getting anywhere near the rated throughput for the RAID
controller or channel.

Could that possibly be the case?

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
WILLIAMS
Sent: Friday, December 07, 2001 10:40 AM
To: Multiple recipients of list ORACLE-L


Whenever I discuss disk waits with my system administrator, I always get the
reply that the RAID system isn't anywhere near its rated throughput. Maybe
I'm wrong, but I don't see any of the tuning books mentioning that as a
relevant performance characteristic. However, I've never been able to move
the discussion beyond this point. Can anyone straighten me out on this point
or point me to a resource that might be applicable.

Our system is Oracle 8.1.6, Compaq Tru64. We use hardware RAID-5 with a
battery-backed RAM cache, and have about 3 RAID sets (plus some extra disks
for redo logs, etc.), and performance is fine, but I'm always looking as to
how we can improve Oracle performance. The application is our corporate ERP
system.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 to find duplicate rows...


Jim,

Oops!  Right after I sent this, I saw that you asked to find, not delete,
duplicates.

Sorry, I've spent the last two days de-duping a bunch of tables, so I've got
deletion on the brain.

How about:

Select SomeColumn
From   MyTable
Where  MyColumn In
(
 Select MyColumn
 From   My Table
 Group By MyColumn
 Having Count(*)  1
)
;

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
Sent: Thursday, December 06, 2001 3:34 PM
To: [EMAIL PROTECTED]


Jim,

An option that handles multiple duplicates is:

Delete
From  MyTable
Where (MyColumn,RowID) In
(
 Select MyColumn,RowID
 From   MyTable
 Minus
 Select MyColumn,Min(RowID)
 From   MyTable
 Group By MyColumn
);

You could use Max(RowID) as well, depending on your needs.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
JAMES W [IT/1000]
Sent: Thursday, December 06, 2001 2:26 PM
To: Multiple recipients of list ORACLE-L


Hello all,

I know this has come across many times now, and I thought I had it saved
somewhere - you know how that goes...

I need to query a table to find all duplicate rows based on a given column.
Don't feel like re-inventing the wheel...

Thanks in advance,
Jim




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 to find duplicate rows...


Jim,

An option that handles multiple duplicates is:

Delete
From  MyTable
Where (MyColumn,RowID) In
(
 Select MyColumn,RowID
 From   MyTable
 Minus
 Select MyColumn,Min(RowID)
 From   MyTable
 Group By MyColumn
);

You could use Max(RowID) as well, depending on your needs.

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
JAMES W [IT/1000]
Sent: Thursday, December 06, 2001 2:26 PM
To: Multiple recipients of list ORACLE-L


Hello all,

I know this has come across many times now, and I thought I had it saved
somewhere - you know how that goes...

I need to query a table to find all duplicate rows based on a given column.
Don't feel like re-inventing the wheel...

Thanks in advance,
Jim




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Duplexing Archived Log Files


Dennis,

We use

log_archive_dest_1
log_archive_dest_2

sending a copy of each archived redo log to our Standby database
(log_archive_dest_2).

log_archive_dest_2 is used with the OPTIONAL parameter, since we don't want
to stop archiving if the Standby is down or there's some problem with the
LAN.

We also have
log_archive_min_succeed_dest = 1
to make sure that the archiving succeeds at least to the local target drive
(log_archive_dest_1).

It's worked great for us (8.1.6.0.0 EE under Win2k).

BTW, we use RMan for hot backups and like it very much.  We can also issue
the Alter Tablespace Begin Backup command and get instantaneous results -
which I had to do recently to restore some datafiles to the Standby after
one of its disks failed.  I wonder why you get such slow response?

Jack


Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068


-Original Message-
WILLIAMS
Sent: Thursday, December 06, 2001 12:29 PM
To: Multiple recipients of list ORACLE-L


Is anyone using the LOG_ARCHIVE_DUPLEX_DEST parameter? Any problems with it?

We are on Oracle 8.1.6 on Compaq Tru64. On our ERP 150-gig. database, we are
currently doing a cold backup weekly. This means that we are very dependent
on archive logs.
   - Recovery time isn't a big concern. In the event of a failure, recovery
time isn't that critical. Loss of a week of work would be.
   - We have tried hot backups successfully on smaller databases, but we
found that once we issue ALTER TABLESPACE BEGIN BACKUP there was often a
delay of hours for that command to complete. So we gave up on hot backups.
   - We are starting to work with RMAN and feel in the long run this will be
an ideal solution. Thanks to everyone for answering my newby RMAN questions.
   - Recently Oracle has added a feature to duplex the archive log files.
This sounds like it would lessen our archive log vulnerability. However, it
causes my sys admin more work, so I would appreciate some feedback before I
add this burden to his work.
Thanks for any thoughts you care to share.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: V7 online backups