Oracle Indexing

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:

Re: ORA-01406

Denham Eva wrote: Hi List, Please can anyone give me some pointers on this issue. We have third party app called Maximo with reports that run from within it. We are receiving an error on one of these reports. The Oracle error that comes out of it is ORA-01406 fetched column value was

Re: DB2

Title: Message - Original Message - From: Cunningham, Gerald Does anybody know if there's a list such as this one for DB2? Or, a link to DB2 documentation (maybe something like the Oracle Concepts Guide)? try these: DB2 links from SearchDatabase.com -

Locally managed ts

Hi all Can we able to change the dictionary managed tablespace to locally managed tablespace. if so how? Ayyapps This communication contains information, which is confidential and may also be privileged. It is for

alter table enable table lock hangs..

list, i was playing around with the disable table lock command and disabled the locks on a temp table.. but i'm not able to ENABLE the table lock again !!! the command just hangs... queried from v$session_wait.. .the command is waiting for library cache handle eternally..!!! is there any way

Strangeness

Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records processed in 1

Recall: Strangeness

Robertson Lee - lerobe would like to recall the message, Strangeness. The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are

Strangeness with PL/SQL and ProC

Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the detail of it we have a Pro C program that is taking ages to run updates and selects (2 hours to do 1 records). The program was changed to PL/SQL and we suddenly were seeing 5 million records

Recall: Strangeness

Robertson Lee - lerobe would like to recall the message, Strangeness. The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are

EMC file systems and backups

Where I can find more info concerning EMC disks, setting up the filesystem and Oracle tuning + backups with EMC. Thanks in advance, sepi _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see

Re: Strangeness

If you can re-run both programs, I'd check the amount of: undo redo redo synch writes. and of course the v$session_event/wait, and there's always the rows_processed column from v$sql. All quick ways of checking for symptoms, which may give you a clue about cause. It is possible

Re: Now: IOUG : Was: PocketDBA

Rachel, I don't seem to have a note Marlene's email address, and would like to drop her a note. Could you forward this to her please and ask her to get in touch. Thanks. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th

Re: Now: IOUG : Was: PocketDBA

the conspiracy continues to unfold. joe PS: moved to the OT list, since now its a conspiracy Rachel Carmichael wrote: not once we get through with them (Kirti, you distract them, I'll take the batteries) --- Joseph S Testa [EMAIL PROTECTED] wrote: But they're laptops with working

RE: Locally managed ts

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]

Re: Cost vs Rule

Bill, There are some good notes on Metalink about why CBO avoids using an index when one is available. I'll see if I can find a note number but you might try searching on index and optimizer. Sometimes you need to modify the code in order to get better performance under CBO. Can you test

RE: Oracle Indexing

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

Re: Cost vs Rule

This shouldn't be a problem, hints just look like comments to other db's. John [EMAIL PROTECTED] wrote: not much - desire is to keep sql ANSI compliant due to cross-platform issues(want to be able to run the app on multiple db's)-Original Message-Sent: Thu, March 07, 2002 2:44

Re:Strangeness with PL/SQL and ProC

Lee, Stop a minute and take a look at what your doing. I assume that when the process was pure PRO*C there must have been a pile of communication between the database and the program. This communication, even if done by IPC takes time. Now when you re-code it in PL/SQL there is no reason

RE: Strangeness

Thanks for the input Jonathan, I will pass the relevant parts on to the development team responsible. Regards Lee -Original Message- Sent: 08 March 2002 11:33 To: Multiple recipients of list ORACLE-L If you can re-run both programs, I'd check the amount of: undo redo

Re: Number of Active Users inside the Database

SELECT s.client_info client,s.username,s.osuser,s.PROGRAM,p.pid,p.spid, s.sid,s.serial#, to_char(S.LOGON_TIME,'MONDD HH24:MI') LOGON TIME from v$session s, v$process p where s.status='ACTIVE' and s.type != 'BACKGROUND' and p.addr=s.paddr / Joan Abul Fazal wrote: Hello List, Just a stupid

RE: [oracle-l-OT] Re: Now: IOUG : Was: PocketDBA

Careful! Kirti has the power of Oradebug in his pocket. erso to speak. -Original Message- To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 3/8/2002 6:55 AM the conspiracy continues to unfold. joe PS: moved to the OT list, since now its a conspiracy Rachel Carmichael wrote:

Oracle client 8.0.5 to 8.1.7

Hi everybody, we've recently upgraded our Oracle client from 8.0.5 to 8.1.7 SQL Worksheet does not seem to have any line numbers - So, if you run any code, it'll give you the line number of the error line, but the code above does not have any line numbers displayed, making it very difficult to

RE: Number of Transaction Slots

Yes, the man is a X$ marvel What that I could remember all of the things that he seems to have at the tip of his emails. RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience

Re: PL/SQL

Possibly :-) but it did reinforce the point to developers out there who insist that using %TYPE for parameters ensures that they cannot pass strings that exceed the length of the corresponding column. Cheers Connor --- Freeman, Robert [EMAIL PROTECTED] wrote: So, do you think I'm making a

Re: OT-Genesis of a DBA Universe

Hilarious Jim. A bit irreverent, but I'm laughing my head off. Thanks for sharing that. Jonathan Gennick --- Brighten the corner where you are mailto:[EMAIL PROTECTED] http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com -- Please see the official ORACLE-L FAQ:

tkprof plan missing rows

Oracle 7.3.4, OpenVMS 7.1 My tkprof report is missing the row count in the execution plan. Does anyone know why what I might be missing? If I autotrace the same query in the same database, I do get cardinality. The tables have been analyzed. If I tkprof another database using the same

RE: Sun Cluster and VCS failover

We have several 2-way VCS clusters with 16 and 10 CPU's per server. We found that the failover time was proportionate with the number of file systems that need to be mounted by the take-over node. We consolidated the file systems containing the datafiles down to 1 and each database takes

Re: Number of Active Users inside the Database

The problem is what is meant by active. If you query for v$session(status) = 'ACTIVE' you will only get the sessions that are currently in the middle of having a statement processed. I doubt that is what he is looking for. If Abul wants everyone who is connected but hasn't been idle for an

Almost OT...

It seems this t-shirt would fit most DBAs here. :) http://www.thinkgeek.com/images/products/zoom/no-clue.jpg Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ:

RE: Strangeness

sql_trace and tkprof should tell you where the time was spent and what the code is doing! Waleed -Original Message- Sent: Friday, March 08, 2002 4:28 AM To: Multiple recipients of list ORACLE-L Oracle 8.0.5.0.0 Tru64 4.0f We have a process running here and without going into the

Re: tkprof plan missing rows

It usually means the cursor for that query was not closed before the end of file (e.g. SQL in pl/sql and you didn't do an exit to get out of sql*plus) so Oracle never got around to dumping the STAT lines. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th

Networker and Legato Question

We are setting up the Legato Networker Module for Oracle but we are encountering problems. When we schedule a backup script to run through the utility, it fails. I don't know if the problem is because we run it as root or what. Even though, we can submit the same backups as our oracle user, using

Partitioning

Hello All, We have an 8.1.7. database and have a partitioned table in it. Table has about 80 million rows and growing fast. Recent changes have forced us to think about sub-partitioning it further. Also we have to upgrade the database to 9i shortly. My question is is there any advantage of

Re:RE: Strangeness with PL/SQL and ProC

Lee, Over the years I've developed a very strong appreciation for TCP/IP as a fast, error free communication protocol. At the same time I've also developer a VERY strong appreciation for how slow SQL*Net is. Anything you can do to minimize that part of an application helps 10 fold.

Re: Number of Active Users inside the Database

Use this for active users. Remove status clause and see all logged on users... set linesize 132 set pagesize 24 set feedback on select SADDR, SID, SERIAL#, PADDR, substr(USERNAME,1,8) USER, STATUS , SCHEMA#, OSUSER, PROCESS, LOGON_TIME, last_call_et from v$session where status = 'ACTIVE' and

USER DEFINED FUNCTIONS

Hi, We are evaluating the usefullness/drawbacks of using UDF's. Is there any case study on any site which shows some scenarios of using UDF's in Queries. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat

VMS, large database

I have a large 7.4 db running under VMS... Database has limited objects, 125,148 meg, with 1,131 tables and 1,022 indexes. (Total objects = 3,445) It runs around 256 I/O per sec and 7,011 logical I/O per sec. Fetch vs. Scan is 5%.. They run about 87 db waits per minute and get 176 I/O per

RE: USER DEFINED FUNCTIONS

select to_char(1) from dual; to_char is a user defined function (already built for you) by oracle. I am yet to find someone who says UDF is a bad thing ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN

Re: Strangeness

Lee, I've had similar experiences. The problem is not PRO*C, but how the program is designed. Is it by any chance written in C++? I once had the 'privilege' of administering an the databases for an application written in C++. The software featured and award winning design, literaly. The

Re: Networker and Legato Question

Your root user should su to oracle and then connect / as sysdba. [EMAIL PROTECTED] 03/08/02 12:48PM We are setting up the Legato Networker Module for Oracle but we are encountering problems. When we schedule a backup script to run through the utility, it fails. I don't know if the problem is

Re: free buffer waits

Sounds like the write latency on your storage is high or you have an abusive. 'free buffer waits' is the db writer failing to flush it's cache fast enough to disk. George On Thursday, March 7, 2002, at 06:39 PM, Manytrees wrote: Hello all,   Does anybody know what parameters I should be

Re: Do you use RMAN? DB clone problem

Does anyone have any suggestions on DB cloning with RMAN.Netbackup, I keep getting a file not found error: RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve sequential file, handle=EML_L0-EML- 455497207-2852-1, parms= I'm missing something somewhere??? Origin DB EML

Re: free buffer waits

The puzzle has been solved, a developer was deleted 4 million rows from a 8 million rows table one row at a time! Joel. - Original Message - From: Manytrees To: Multiple recipients of list ORACLE-L Sent: Thursday, March 07, 2002 6:39 PM Subject: free buffer

cursor not closing

Hi, We have a Application that user JDBC thin client connecting to Oracle database. It seems like java code is opening the cursor , running some sql but not closing the cusrsor. Now the number of open_cursor reached 3568 . Is there any way we can close cursor from sqlplus. Thanks --Harvinder --

RE: cursor not closing

Java surely allows you to close the statement, ask your developers to do that. I told mine, and they are happy with it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is

RE: cursor not closing

common developers problem. they forget to close it in powerbuilder, too. -Original Message- Sent: Friday, March 08, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Java surely allows you to close the statement, ask your developers to do that. I told mine, and they are happy with

Cannot run job in OEM ???

Hi all, I'm using 9i on Win2000. When I submit job in OEM (9.0.1), I always got VNI-2015 : Authentication error. I created a user and put that user in administrator group. That user has Log on as a batch job and Log on locally rights. (No deny ... rights were set) In OEM's preferred

Re[2]: Strangeness

Jared, Yeah, they don't want to hear about PL/SQL because then they can't scale it up on the middle tier where they can have dozens of machines with the same poorly written app simultaneously pounding the database thousands of times more intensely than the task requires Hmmph. Topics like

archivelog mode

Hi All While enabling automatic archiving on our 8.1.7 database I get the following error :- ORA-00439: feature not enabled: Managed Standby ie the database is in archivelog mode and I edit the parameter file to enable automatic archiving .. I get the error when trying to startup the database

Re: Re[2]: Strangeness

Hmmph. Topics like this on a Friday make me want to dig deeper into my toolbox (the malted compartment of course). I'm having similar feelings. Time to break out the Lagavulin tonight. And Rachel, yes, I know it reminds you of burning peat. Maybe I *like* burning peat. :) Re the rewrite

RE: Cost vs Rule

Bill, In addition to the many excellent suggestions, may I also suggest generating adequate number of histograms and using them by using literals instead of bind variables (horrors!). You may also want to look at 9i - the CBO therein looks at the value of the bind variables prior to parsing and

RE: archivelog mode

Try using: LOG_ARCHIVE_DEST instead of LOG_ARCHIVE_DEST_1 If you would like using LOG_ARCHIVE_DEST_1 you have to read the in Oracle Doc the syntax for it since more keywords are needed like 'location'. Waleed -Original Message- Sent: Friday, March 08, 2002 6:09 PM To: Multiple

RE: VMS, large database

Mike, I have a large 7.4 db running under VMS... Database has I am assuming 7.3.4 here (unless VMS has 7.4 :) It runs around 256 I/O per sec and 7,011 logical I/O per sec. Fetch vs. Scan is 5%.. They run about 87 db waits per minute and get 176 I/O per wait... I assume you calculated

FW: VMS, large database

Sorry - premature send of the last one! This note finished properly. Mike, I have a large 7.4 db running under VMS... Database has I am assuming 7.3.4 here (unless VMS has 7.4 :) It runs around 256 I/O per sec and 7,011 logical I/O per sec. Fetch vs. Scan is 5%.. They run about 87 db

Re: Re[2]: Strangeness

Jared, yes it reminds me of burning peat. But since I am not the one drinking it, I don't care what it tastes like. Me, I'm debating the merits of a Brooklyn Pennant '55 Pale ale or a nice large glass of Glenmorangie.. hm maybe tonight is a Macallan's night? Been that sort of week. Rachel

RE: cursor not closing

Identify all the codes where ResultSet, Statement are used and make sure they are closed after it's done. -Original Message- Sent: Friday, March 08, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Hi, We have a Application that user JDBC thin client connecting to Oracle

UPGRADE TO 8173

DEAR LIST, Simple question for upgrading, Is it ok if i install 817 separatley then patch 8173 and finally import the database from 816 to 8173, isn't it faster in this way the database is a small database and no need to link and all other things, any idea???in this case how can i use the same

RE: cursor not closing

In the for what it is worth department, I had similiar issues. After the developers knocked themselves out looking for code that was not closing the cursor, I opened a tar: In a nutshell: yet another feature that cursors even though closed by the session that opened them remain available

RE: archivelog mode

You are looking for ...dest_n = location=arch path I would recommend that you utilize the new enumerated archive destination locations, due to the inherent benefits... one example: If you use two locations, enabling the first(1) and defer the second(2), then if dest_1 fills up, you can enable

Re: Do you use RMAN? DB clone problem

I use this technique only as a failover routine, if the production machine were to crash: Cloning I would use standards methods as it is much faster: But once u restored the database you can rename it. But the steps that I take are 1.Install executables on other machine as same owner and group

Re: USER DEFINED FUNCTIONS

Jamadagni, Rajendra wrote: select to_char(1) from dual; to_char is a user defined function (already built for you) by oracle. I am yet to find someone who says UDF is a bad thing ... Raj __ Rajendra Jamadagni MIS, ESPN