RE: bind variables

Hi Nicoll, The importance of bind variables, to use in OLTP application is such that if we use, we survive else one or the other day we would feel uncomfortable over the performance issues and would repent over the scalability of the application. Yes, if we use bind variables, the parser does no

Re: how to find no. of transactions/sec ?

Check the 'user commits' statistic in v$sysstat hth connor --- oraora oraora <[EMAIL PROTECTED]> wrote: > Guys, > > my DB is on 8.1.6/Win2K. > how do i find the number of transactions per second > in my DB ? > > TIA. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com >

RE: bind variables

Yes. -Original Message- Sent: Friday, September 06, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_nam

test - please ignore

Just testing, I'm not getting any messages lately.   Igor Neyman, OCP DBA[EMAIL PROTECTED] 

RE: connect to the databases without using tnsnames.ora?

Correct. And since I use ONames, a TNSNAMES.ORA file is not supposed to be required on the client. QCO, however, still requires it, but there are workarounds. Tbink about it. What's the point of centralizing your instance names in ONames if you still require that same info to be somehow magica

RE: test - please ignore

Can you hear me now   Good   ;o) -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Monday, September 09, 2002 9:38 AMTo: Multiple recipients of list ORACLE-LSubject: test - please ignore Just testing, I'm not getting any messages lately.

RE: Correlated subquery performance in 8i & 9i

Hello Larry, The better performance of correlated subqueries is accomplished in 9i without manipulating any parameters. It's automatic! As far as I remember, Oracle uses a 'sort-merge' approach and uses the temp file for this. You can reach Gaja @ [EMAIL PROTECTED] Prakash -Original Mes

RE: Recreate database script

If you export with rows=n, you get a text file with all the ddl to recreate the exportable objects. However, packages/procedures are formatted and not at all easy to use to recreate and the storage clauses are all in bytes. Not a pretty method, but certainly one that can be used. -Original Me

RE: Recreate database script

if you are not using locally managed tablespaces, you can find scripts to do this in the SQL and PL/SQL Annotated Archives --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > If you export with rows=n, you get a text file with all the ddl to > recreate > the exportable objects. However, packages/p

RE: Recreate database script

DBATool will fix this problem for you, as well as a whole host of added features for database creation scripts, such as "rule" based storage attribute changes for example - e.g. change ALL indexes in a schema to be stored in an INDEXES tablespace. You can also use it to create HTML based DDL docum

Re: Recreate database script

Thanks. I wonder if you can treat an export file as a *.sql script and run it off sqlplus with the @@script.sql command? I shall give this a try. ltiu Fink, Dan wrote: >If you export with rows=n, you get a text file with all the ddl to recreate >the exportable objects. However, packages/proc

multiple 8.1.7 db Migration Question

Dear List, i have to migrate around 25 databases to oracle 8.1.7 from 7.3.4. is there an automated procdure where i can run on each server. like a shell script where i can copy the 8.1.7 directory and run the migration process without using the OUI. thanks Sarath ___

RE: Recreate database script

Forgot to mention that this will only build the db create script. Nothing more than that. - Kirti -Original Message- Sent: Monday, September 09, 2002 10:07 AM To: '[EMAIL PROTECTED]' Here is one that I used to use some time ago (for Oracle 6 and 7). This does not support all the *n

RE: Recreate database script

Here is one that I used to use some time ago (for Oracle 6 and 7). This does not support all the *new* stuff available since Oracle8, but it should not be difficult to change it.. if you do, I would appreciate a copy :) - Kirti #!/bin/ksh #--

Re: Recreate database script

Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http:/

Re: Recreate database script

> Could anyone here suggest a software package that can create an ER > diagram by simply connecting to an Oracle database, reverse engineer > it to see the schema in an ER diagram - instead of in a file with > ddl/dml statements. I haven't tested it, but Microsoft Visio 2000 claims to be able to

RE: Recreate database script

Itiu - ERWin Examiner (Computer Associates) is supposed to have that capability. I have never used that capability. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Hello, Could

Re: Recreate database script

ErWin is an excellent tool.. Gabriel --- ltiu <[EMAIL PROTECTED]> wrote: > Hello, > > Could anyone here suggest a software package that > can create an ER > diagram by simply connecting to an Oracle database, > reverse engineer it > to see the schema in an ER diagram - instead of in a > file

* Oracle DBA Needed For Long Term Contract in Upstate New

Position: Oracle DBA- 18 month 1099 contract position Location: Albany, New York Rate: Submit your 1099 rate with your resume PLEASE DO NOT send your resume for this position Unless you are qualified for this position based on the requirements outlined below. No H-1B candidates please. *Requi

Re: Recreate database script

Most CASE tools have reverse engineering feature. We are using PowerDesigner (from SYBASE). Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 09, 2002 12:23 PM > Hello, > > Could anyo

snapshot of a remote snapshot without pk

short question, log demo: Since I can't make a snapshot of a remote partition (this is really what I wanted in the first place), I make a snapshot on the remote machine first and then snapshot the remote snapshot. This works if there is a PK on the remote original table, but not if I try to use

Re: Calling report

sorry dear but this is a dba forum . Do you think it's a dba question ?   bp - Original Message - From: sultan To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 8:03 AM Subject: Calling report   Hi friends   I am usi

Re: Recreate database script

ERwin, Visia Architect, Oracle Designer all can do it. There may be other packages that I do not know about. I have all three of them and they work. Shakir --- ltiu <[EMAIL PROTECTED]> wrote: > Hello, > > Could anyone here suggest a software package that can create an ER > diagram by simply c

RE: Recreate database script

I have used Visio to do that . It links the tables together if they have foreign/primary keys. If you have no relations like that then it will at least list all the tables you pick. -Original Message- Sent: Monday, September 09, 2002 12:09 PM To: Multiple recipients of list ORACLE-L

RE: Can't create simple MV with FAST refresh

Maybe the IN clause is throwing it? Try it with - where trans_type = 'A' or trans_type = 'B' Just a WAG... Beth -Original Message- Sent: Monday, September 09, 2002 2:38 PM To: Multiple recipients of list ORACLE-L Hi all, Using 8.1.7.4.0 on HP/UX with COMPATIBILE set to "8.1.7", I'

RE: No space left on device - but I have lots left.

Did I shoot from the hip too quickly? I was in a bad mood caused by some other issues. I've received a few emails telling me how grumpy I appeared to be, some of them through an anonymous remailer. There were also some interesting assumptions about my place on the tree of evolution as well as the

Can't create simple MV with FAST refresh

Hi all, Using 8.1.7.4.0 on HP/UX with COMPATIBILE set to "8.1.7", I'm not able to create the following simple MV: CREATE MATERIALIZED VIEW qt_gl_trans_ab_mv TABLESPACE qt_mat_view BUILD IMMEDIATE USING INDEX TABLESPACE qt_mat_view PCTFREE 15 INITRANS 2 R

Re: connect to the databases without using tnsnames.ora?

Thanks all for the answers. We do use Oracle Names here. Cc Harvest. --- [EMAIL PROTECTED] wrote: > Neither sqlnet.ora or tnsnames.ora are required. > > You can connect to a database by specifying the full > connect string. > > This will work from sqlplus: > > connect > system/manager@(des

NLS_DATE_FORMAT

On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- (858

RE: ALTER TABLE MOVE command causes table to grow

How did you perform the move? Was the operation done in parallel? -Original Message- Sent: Friday, September 06, 2002 4:13 PM To: Multiple recipients of list ORACLE-L No LOBS. Here's the definition: REQUEST_IDNOT NULL NUMBER PROFILE_ID

RE: Recreate database script

Oracle Designer Viso2000 I use Oracle Designer. -Original Message- Sent: Monday, September 09, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse

RE: Recreate database script

Yeah, you can use Visio. I used it when I need to quickly generate an ER diagram from a schema. -Original Message- Sent: Monday, September 09, 2002 1:09 PM To: Multiple recipients of list ORACLE-L > Could anyone here suggest a software package that can create an ER > diagram by simply

RE: Recreate database script

Now called AllFusion Erwin Data Modeler. It will create ER diagram from existing database. Rick DENNIS WILLIAMS

Oracle and Cache Hit Ratio based training

FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracle is in the process of revamping all their performance classes to be based on the wait interface. Looks like Cache Hit Ratios may finally go away !!! -- Please see the official ORACLE-L

OT: Here we go again!!

Will Bill Gates never learn! Can you say Anti-trust lawsuit #2? Dick Goulet -- MICROSOFT CLAIMS .NET REMOVES NEED FOR APPLICATION SERVER | cw360 Microsoft says. NET Server is all you need. Defying the industry trend for vendors to

RE: NLS_DATE_FORMAT

Set it in the init.ora. Dave -Original Message- Sent: Monday, September 09, 2002 2:18 PM To: Multiple recipients of list ORACLE-L On NT/2000, how can you set the NLS date format at the session level? Isn't there a file that you can set it in? What do you write in the file? -- Please

Re:RE: No space left on device - but I have lots left.

Mladen, Welcome to the club for those who hit send without fully engaging brain. Dick Goulet, president & founder Reply Separator Author: "Gogala; Mladen" <[EMAIL PROTECTED]> Date: 9/9/2002 10:48 AM Did I shoot from the hip too quickly? I was i

RE: Oracle and Cache Hit Ratio based training

I can see it now... DBA 102:Buffer Cache Wait Hit Ratio Interface Tuning... -Original Message- Sent: Monday, September 09, 2002 12:13 PM To: Multiple recipients of list ORACLE-L FWIW ... One of our DBAs just got back from a Oracle 9i course and it was stated by the instuctor that Oracl

RE: Can't create simple MV with FAST refresh

Thanks, but as I had mentioned, I've already tried that. One part I didn't include is that the table does not have a primary key and cannot be made to have one, so I'm using ROWID. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Interna

RE: Oracle and Cache Hit Ratio based training

Hi all, I have a large data load to run into a partitioned table. With indexes the load takes over 24 hours, without on the order of 3 hours, obviously, I don't want to mess with the indexes until after the job completes. So I try to drop to local indexes and get ORA-14076. Fair enough. I g

Re: OT: Here we go again!!

-- [EMAIL PROTECTED] on 09/09/02 11:58:25 -0800 > http://www.cw360.com/article&rd=&i=&ard=115584&fv=1 As a long time *NIX user I can only hope they never do. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647

Re: Calling report

No, this is an Oracle forum. The question do not need to be DBA related. It just happens that many of the questions are DBA questions. Jared "BigP" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/09/2002 11:23 AM Please respond to ORACLE-L To: Multiple recipients of list

RE: Can't create simple MV with FAST refresh

OK, so the MetaLink article seems to be really incomplete, as I found this hidden in the docs: "The WHERE clause can contain only joins and they must be equi-joins (inner or outer) and all join predicates must be connected with ANDs. No selection predicates on individual tables are allowed." OK,

RE: ALTER TABLE MOVE command causes table to grow

alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent: Monday, September 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L How did you perform the move? Was the operation done in parallel? -Or

Solaris 2.9 -- Portal

We're getting prepared to begin implementation of the Oracle Web Portal. Does anyone know if Oracle 9ias will run successfully on Solaris 2.9? It looks like it is officially in pre-certified status, but I wondered if anyone has any real-world experiences with it (+ or -). thanks! -Bob [EMAIL PR

Re: Can't create simple MV with FAST refresh

Jesse, don't have much experience with mv, but shouldn't "rowid" be included in "select" list? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 09, 2002 4:18 PM > Thanks, but as I had m

Re: Calling report

.. and many members are DBAs. Jared, you are really generous today :-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 09, 2002 4:38 PM > No, this is an Oracle forum. The question > d

Disabling local partitioned indexes for load

Sorry...sent with the wrong heading.. -Original Message- Sent: Monday, September 09, 2002 4:18 PM To: Multiple recipients of list ORACLE-L Hi all, I have a large data load to run into a partitioned table. With indexes the load takes over 24 hours, without on the order of 3 hours,

Re: No space left on device - but I have lots left.

Did I, I might have, if that's the case; you waltz divinely, almost Clintonesque ;) On Mon, Sep 09, 2002 at 10:48:21AM -0800, Gogala, Mladen wrote: > Did I shoot from the hip too quickly? I was in a bad mood > caused by some other issues. I've received a few emails telling > me how grumpy I

RE: Disabling local partitioned indexes for load

Hi, I believe there is the caveat that it doesn't apply to pk indexes, because they are required to enforce the integrity constaint, although I can't find that in the documentation at the moment. Beth -Original Message- Sent: Monday, September 09, 2002 5:03 PM To: Multiple recipients o

using obfuscation

Hi all, I have downloaded the Metalink Notes on implementing dbms_obfuscation. I am using multiple front ends on the database, so the way I plan to implement the de-encryption is with a de-encrypt function in a view. Create View my_data AS Select de_encrypt(sensitive_data) AS sensitive_data ,o

RE: foreign key indexes and parent-table locking

Jared: I've now run through a basic test (on 8.1.7.2.1) and lo and behold I get the exact same results - index or no index! For each dml I show the results of this query for the session performing the DML: select type, id1, id2, lmode, request, block from v$lock h

Recreating database from hot backup but only 1 tablespace - Urgen

We need to recover a table that had been truncated. Unfortunately at this time we don't have a server large enough to copy our complete backup (I know, I've been yelling about this for over a year). Can we copy just the tablespaces holding the SYS objects and the table we need, modify our create

Re: using obfuscation

Randy, Do you have an index on SENSITIVE_DATA? Also, you didn't include the long running query. Does it look something like select * from original_table where decrypt(sensitive_data) = 'CLEAR TEXT' ? That requires a full table scan, unless you build a functional index with the clear

dbwr high count of threads

Hi all, I noticed the dbwr process on Solaris has a very high number of threads (258). To me this is not a problem since I am seeing this on my small development box too. But we recently had some server crash and the consultant is saying Oracle is consuming a lots of resource, citing the high num

RE: Recreating database from hot backup but only 1 tablespace - U

Jay You should also bring the tablespace holding your ROLLBACK segments. Archive and redo logs, of course. Other than that, I think you are off to a promising start. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 09, 2002 5:04 PM To:

RE: Recreating database from hot backup but only 1 tablespace - U

Thanks Dennis, The rollback segments occurred to me already, I'm composing the email to our SAs to restore the files (I'm going to have to wipe out another test box but it's one that can easily be recreated). Thanks! Jay Miller -Original Message- Sent: Monday, September 09, 2002 5:14 PM

RE: No space left on device - but I have lots left.

Wait until you see me debating the finer points of the word "is". > -Original Message- > From: Ray Stell [mailto:[EMAIL PROTECTED]] > Sent: Monday, September 09, 2002 5:25 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: No space left on device - but I have lots left. > > >

RE: Recreating database from hot backup but only 1 tablespace - U

Jay - Here is a link to the Oracle documentation for what you are attempting. You'll have to patch the link back together. I don't know what your Oracle version is, but the main change with the newer version is that you can use transportable tablespaces to move the resulting tablespace back to pro

RE: Changing sysdate

Title: RE: Constraints problem I remember a Y2K testing parameter for setting the database date.  It still might work.  Search for FIXED_DATE to see what NLS date format to use.   HTH Tony Aponte -Original Message-From: Karthikeyan S [mailto:[EMAIL PROTECTED]]Sent: Wednesday, S

RE: Disabling local partitioned indexes for load

Why do not you use sql*loader in the direct mode? Waleed -Original Message- Sent: Monday, September 09, 2002 5:03 PM To: Multiple recipients of list ORACLE-L Sorry...sent with the wrong heading.. -Original Message- Sent: Monday, September 09, 2002 4:18 PM To: Multiple rec

Sort Area Size?

Hello, For an SGA of about 512MB and a 2GB database (total of the *.dbf) files. Is 1MB sort area size enough? Thank you for any tips. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051

Privileges for other's table

Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL> select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view do

RE: ALTER TABLE MOVE command causes table to grow

What is the DEGREE setting on the table? -Original Message- Sent: Monday, September 09, 2002 4:48 PM To: Multiple recipients of list ORACLE-L alter table email_request_queue move tablespace ts_email_request storage (initial 25m next 25m pctincrease 0); -Original Message- Sent:

RE: Sort Area Size?

I suggest you check the amount of disk sort activity (v$sysstat, v$sesstat). If there are numerous disk sorts taking place, you may want to consider raising Sort_Area_Size and Sort_Area_Retained_Size. Also, if the sorting (to disk) is taking place mainly in the batch processes, consider changing t

RE: Oracle and Cache Hit Ratio based training

Performance Tuning 102 dear, Performance Tuning 102 --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > I can see it now... > DBA 102:Buffer Cache Wait Hit Ratio Interface Tuning... > > -Original Message- > Sent: Monday, September 09, 2002 12:13 PM > To: Multiple recipients of list ORACLE-L >

Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based train

Title: Nasty bug in 9.2.0.1 (was: Oracle and Cache Hit Ratio based training) FWIW There is a document (id 283852) on Metalink updated on 03-SEP-2002 created by "CENTER OF EXPERTISE" named THE COE PERFORMANCE METHOD A PERFORMANCE METHODOLOGY FOR ENTERPRISE-WIDE INFORMATION SYSTEMS which

Re: Privileges for other's table

Hand, Michael T, hi, what you tested is not the normal behavior of oracle, neither the document said like that nor in my database behavior like that.Maybe something is wrong with your database:) Look: SQL> conn internal Connected. SQL> create user t identified by t; User

Re: multiple 8.1.7 db Migration Question

At 09:58 AM 9/9/2002, sarath kumar wrote: >Dear List, > >i have to migrate around 25 databases to oracle 8.1.7 >from 7.3.4. is there an automated procdure where i can >run on each server. like a shell script where i can >copy the 8.1.7 directory and run the migration process >without using the OUI

Aout plan stabilty matching.

Hi, All, From Oracle Doc: "if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch." How could I ensure the incoming SQL text exa

RE: Calling report

Hi syed...   srw.run_report ('d:\test.rdf destype=file desname=try.out desformat=dflt batch=yes');   The above will work fine. You should not mention 'report' keyword in the command line. This is the mistake in reports help.   Nirmal. Prior Deveoper, Current OCP DBA.   -Original Mes

Re: NLS_DATE_FORMAT

Eric Richmond, hi, try to set it in regedit, or click my computer->property->advanced->enviroment variable->add nls_date_format. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 11:18:00 ,you wrote£º=== >On NT/2000, how