RE: SQL and PL/SQL tuning template document required urgently
Dennis, Excellent recommendation, Guy Harrison's book (2nd Edition) is excellent the best I've seen on SQL tuning. I've used it for a number of years. I had the 1st edition and then bought the 2nd when it came out. Cheers, Chris -Original Message- Sent: 29 October 2003 15:49 To: Multiple recipients of list ORACLE-L Ranganath Since you mentioned proactive and reactive query tuning, I think the philosophy with which one approaches the tuning exercise means everything. Wrong philosophy and you spend your time spinning your wheels. All of us have only a limited amount of time to devote, so the best approach will make the best use of that time. Get Optimizing Oracle Performance by Cary Millsap. It doesn't take long to read the important parts. Implement Cary's approach to locate the queries where you will get the most bang for the buck. Then use books like Guy Harrison's (Ryan's suggestion) for pointers on making those queries perform better. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 29, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi there, Does any body have a template for proactive and reactive query tuning which can be used as a guideline/report while tuning simple, medium complex and complex SQL queries and PL/SQL stored procedures? If so, can you please forward the same to me please? If not, can anybody suggest as to how to go about doing one? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: OCP 9i New Features for DBAs
Jared, I didn't make a detailed list but where I clearly noticed the inaccuracies was in the sample exam questions at the back of the book e.g. In which version of Oracle was hash partitioning introduced? A) 7 B) 8 C) 8i D) 9i Answer D. The real answer as we know is C. Which statement is true about the TIMESTAMP WITH TIME ZONE datatype? A) It represents absolute time. B) In addition to the date and time, you can store the time zone displacement (offset), which requires additional bytes of storage. C) In addition to the date and time, you can store the time zone displacement (offset), without consuming additional bytes of storage. D) You can use the NLS_TIMESTAMP_TZ_FORMAT initialisation parameter to specify the default timestamp format for retrieval. Answer C. The real answer is B. What this means is that you need to checkout the answers when marking yourself just to be on the safe side. I must point out that on a couple of occaisions the book was right when I initially thought it was wrong. Cheers, Chris -Original Message- Sent: 20 November 2003 18:35 To: Multiple recipients of list ORACLE-L Chris, Care to share details on the inaccuracies? Jared Dunscombe, Chris [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/20/2003 02:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: OCP 9i New Features for DBAs Ryan, I took my exam yesterday and passed!! I used the Oracle Press - OCP Oracle 9i Database: New Features for Administrators Exam Guide book. Even though there are a number of inaccuracies it was good preparation especially the sample exams it provides. Regarding 9.2 vs 9.0 content in the exam it all seemed to be 9.0. Hope all goes well when you take your exam. Cheers, Chris -Original Message- Sent: 12 November 2003 18:25 To: Multiple recipients of list ORACLE-L im going to take it soon. I was going to just read howard rogers guide then the otn one. you think that is enough? I just want to pass it and get my piece of paper. I already know the 9i stuff that is useful to me. From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/11/12 Wed PM 12:19:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OCP 9i New Features for DBAs Chris I'm betting on 9.0. For it to cover 9.2 would have meant that Oracle would have had to go back and recreate the test. And Oracle would have felt compelled to change the name of the test. However, I think it possible that any question whose answer would be true for 9.0 but false for 9.2 might be removed. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 12, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Hi, I'm currently studying for this exam but can't find info to say whether the exam covers 9.2 or just 9.0. Anyone any clues Thanks, Chris Dunscombe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: RE: OCP 9i New Features for DBAs
Ryan, I took my exam yesterday and passed!! I used the Oracle Press - OCP Oracle 9i Database: New Features for Administrators Exam Guide book. Even though there are a number of inaccuracies it was good preparation especially the sample exams it provides. Regarding 9.2 vs 9.0 content in the exam it all seemed to be 9.0. Hope all goes well when you take your exam. Cheers, Chris -Original Message- Sent: 12 November 2003 18:25 To: Multiple recipients of list ORACLE-L im going to take it soon. I was going to just read howard rogers guide then the otn one. you think that is enough? I just want to pass it and get my piece of paper. I already know the 9i stuff that is useful to me. From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/11/12 Wed PM 12:19:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: OCP 9i New Features for DBAs Chris I'm betting on 9.0. For it to cover 9.2 would have meant that Oracle would have had to go back and recreate the test. And Oracle would have felt compelled to change the name of the test. However, I think it possible that any question whose answer would be true for 9.0 but false for 9.2 might be removed. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 12, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Hi, I'm currently studying for this exam but can't find info to say whether the exam covers 9.2 or just 9.0. Anyone any clues Thanks, Chris Dunscombe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: (looong) PCTFREE, PCTUSED and ASSM
Tanel, Maybe a PQ FTS needs to resolve migrated rows immediately as it's possible that the migrated row is located in a block that's allocated to a different PQ slave. Chris -Original Message- Sent: 12 November 2003 15:49 To: Multiple recipients of list ORACLE-L As a strange thing, from 10046 trace I saw that normal table scanning was done using direct reads, this was expected behaviour, but the lookups of migrated rows were reflected as 'db file sequential reads'. And even more, there were 3 subsequent sequential read waits for the same datablock in a row, it seems that a PX slave isn't even able to cache one datablock in it's PGA, in case of finding migrated rows... (or a wait event is registered for reading from cache...) I was just wondering, why a PQ FTS requires resolving migrated rows immediately, instead of reading them when scan hits their location. Could it be some concurrency issue, that if a row migrates to another location during the scan, then results could get inconsistent? It is not a direct read issue, because I experimented using _serial_direct_read parameter, and for regular FTS, no migrated rows were resolved ahead. There's lot to learn... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:34 PM I believe it's direct read from files in parallel execution, nothing gets read from cache. Cached blocks for the table get flushed to files before the direct read. Regards, Waleed -Original Message- Sent: Wednesday, November 12, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Yep, the situation can get bad for parallel execution, especially if blocks read aren't cached... But for serial FTS I haven't seen such a problem, I did even a test to verify it on 9.2.0.4, and did see behaviour as I expected - all blocks were scanned using multiblock reads and rows were returned in order the contents of them were found, instead of pointers. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:14 AM Actually row migration is a big problem for FTS also(whether serially or using PQ). You end up waiting for too many db file sequential read single block reads instead of MBRC in (direct path read, db file scattered read) Regards, Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
OCP 9i New Features for DBAs
Hi, I'm currently studying for this exam but can't find info to say whether the exam covers 9.2 or just 9.0. Anyone any clues Thanks, Chris Dunscombe -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: wait/notify syntax for unix help please
There's no problem with waiting after the process has already finished,
you'll just get a non-zero return code the wait but evrything will still
work fine.
Chris
-Original Message-
Sent: 27 October 2003 18:54
To: Multiple recipients of list ORACLE-L
if you attemp to wait after the process is complete, will it cause a
problem? say the PID no longer exists when you issue wait?
From: Dunscombe, Chris [EMAIL PROTECTED]
Date: 2003/10/27 Mon AM 11:39:34 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: wait/notify syntax for unix help please
I don't know about Solaris but on HP-UX and AIX you can do:
run_sql_1
run_sql_2
wait
This will wait until both have finished.
Re a specific PID $! will return you PID of the last child process and
then
you can wait on that PID. Looks something like:
run_sql_1
run_sql_2
PID_WAIT=$!
wait ${PID_WAIT}
HTH
Chris Dunscombe
-Original Message-
Sent: 27 October 2003 16:09
To: Multiple recipients of list ORACLE-L
I need to parallelize some sql operations and Im running them from unix
scripts.
I want to spawn off a few in the background from a master script, then
have
the master script 'wait' for them to finish. Ive done this in Java and
with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: wait/notify syntax for unix help please
I don't know about Solaris but on HP-UX and AIX you can do:
run_sql_1
run_sql_2
wait
This will wait until both have finished.
Re a specific PID $! will return you PID of the last child process and then
you can wait on that PID. Looks something like:
run_sql_1
run_sql_2
PID_WAIT=$!
wait ${PID_WAIT}
HTH
Chris Dunscombe
-Original Message-
Sent: 27 October 2003 16:09
To: Multiple recipients of list ORACLE-L
I need to parallelize some sql operations and Im running them from unix
scripts.
I want to spawn off a few in the background from a master script, then have
the master script 'wait' for them to finish. Ive done this in Java and with
dbms_alert, but I cant dig up the syntax to do this with korn shell on
solaris.
Also, if I want to wait for a specific PID, how do I get the PID of the
thread I want to wait for?
so I have
nohup run_sql
wait(on previous nohup)
then to use notify, I just use 'notify()' inside the script right?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Dunscombe, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Opinions sought on possible TOAD replacement
Paul, I've used PL/SQL Developer by Allround Automations to develop PL/SQL procs, packages and it's fine including a well featured de-bugger. I believe that a site licence costs $3,000. As to it being a DBA tool I'd have to say it's not in the same league as TOAD Xpert with DBA module. Chris Dunscombe Accenture Worthing Unit Internal: 71-3558 External: 01903-283558 Email: [EMAIL PROTECTED] -Original Message- Sent: 15 October 2003 16:59 To: Multiple recipients of list ORACLE-L Management have been grumbling about the cost of TOAD Professional licenses, and have been recommended a cheaper product called PL/SQL Developer by Allround Automations (available from Inthink Corporation at $150 a pop). Now, I've been to the product website, and read up on all its features, and it basically looks like a nice enough product, but aimed squarely at PL/SQL developers, rather than including all the DBA-oriented goodies we find in TOAD. My first reaction is to respond by saying fine, give it to the developers to replace their copies of TOAD, if they find it adequate for development, but it's not a DBA tool, so I'll keep my TOAD Xpert with DBA module, thanks very much!. But if anyone on the list has tried both products, I'd be interested to know what you think. Is it as usable as TOAD Professional for developers? Does it have hidden charms which would make it a suitable replacement for DBA use? How responsive are the product developers to requests for enhancements? Any input is very welcome! Paul Vincent DBA University of Central England -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: LMT and Fragmentation
Niall, I played around with autoallocate on 8.1.7 a while back and came to the same conclusions as yourself. Chris -Original Message- Sent: 13 October 2003 21:54 To: Multiple recipients of list ORACLE-L A week or so ago Jesse (I think) suggested a test to see whether auto-allocate LMTs were susceptible to fragmentation, or whether the fact that under the hood every allocation unit was 64k made this irrelevant. The test below shows that under 9.2 creating 32 tables, extending them until each has a next extent of 64k. Then we drop half the tables. Can Oracle allocate a new extent for a table. Looks like it can't and the old fun of fragmentation might remain. I'll be sticking with ULMTs but flames/corrections welcomed. SQL set echo on SQL select banner from v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production PL/SQL Release 9.2.0.3.0 - Production CORE9.2.0.3.0 Production TNS for 32-bit Windows: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production SQL SQL create tablespace auto_alloc_test 2 datafile 'c:\oracle\oradata\nl9iwk\auto_alloc.dbf' size 32832k 3 extent management local; Tablespace created. SQL SQL /* DOCcreate the tables DOC*/ SQL SQL begin 2 for i in 1..32 loop 3 execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test'; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL SQL select sum(bytes)/1024 free_k from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_K -- 30720 SQL SQL begin 2 for i in 1..15 loop 3 for j in 1..32 loop 4 execute immediate 'alter table table'||j||' allocate extent'; 5 end loop; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL SQL select sum(bytes)/1024/1024 free_M from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_M -- SQL SQL begin 2 for i in 1..32 loop 3 if i mod 2 = 0 then 4 execute immediate 'drop table table'||i; 5 end if; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL SQL select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_MB -- 16 SQL SQL alter table table1 allocate extent; alter table table1 allocate extent * ERROR at line 1: ORA-01653: unable to extend table NIALL.TABLE1 by 64 in tablespace AUTO_ALLOC_TEST -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: 64 bit Oracle (8.1.7) on a 32 bit AIX kernel (AIX 4.3.3)
Peter, I know that this looks odd but as the hardware is 64 bit the AIX O/S has some sort of translation layer that allows it to run Oracle 64 bit even though the kernel is in 32 bit mode. I discovered this when investigating running Oracle 9.2 and Oracle 8.1.7 on AIX 5L. After raising a TAR with Oracle I received the response which basically stated: - Oracle 8.1.7 can only be run in 32 bit mode on AIX 5L. - To run Oracle 8.1.7 and Oracle 9.2 concurrently on AIX 5L you must run AIX in 32 bit mode on 64 bit hardware with 32 bit Oracle 8.1.7 and Oracle 9.2 64 bit (it only exists in 64 bit mode). Confusing I know but somehow true. As an aside I don't think this applies to the likes of HP-UX. I'm fairly sure that running 32bit HP-UX 11 on 64 bit hardware will not allow 64 bit Oracle to run. HTH Chris Dunscombe -Original Message- Sent: 11 September 2003 21:39 To: Multiple recipients of list ORACLE-L O/S is AIX 4.3.3 /usr/sbin/bootinfo -p returns chrp meaning that the hardware is capable of either 32 or 64 bit operation. /usr/sbin/bootinfo -K returns 32 meaning that the kernel is running in 32 bit mode. When I run sqlplus the server says: Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production How can I be running the 64 bit server on a system with a 32 bit kernel? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: 64 bit Oracle (8.1.7) on a 32 bit AIX kernel (AIX 4.3.3)
Peter, I'll answer the 2nd question first as it's easier. - You will need to change the word size of your database from 64 bit to 32 bit. This involves running a few scripts and bouncing the database were required. The exact process is described in a Metalink note, sorry but I don't have the number to hand. - In answer to the 1st question I believe you can only run the Oracle 8.1.7 software in 32 bit mode on AIX 5L so Oracle 8.1.7 will be running as 32 bit software even after you apply the patch. See the end of the Metalink note: Oracle Releases for AIX 4.3.3 and AIX 5L: - The 64-bit ABI in AIX 4.3.3 is not supported under AIX 5L. Moving existing 64-bit applications from AIX 4.3.x to AIX 5L requires recompiling the application source code, and possibly changing application source code to ensure that data types are used correctly and consistently. Due to this restriction, starting from 9.2.0 version, Oracle ships different CDs for AIX 433 and AIX 5L. Customers should be careful not to use the wrong CDs, since these two releases are not compatible. This is also the reason why the 64-bit versions 9.0.1 and 8.1.7/64 do not run on AIX 5L. Cheers, Chris -Original Message- Sent: 12 September 2003 15:30 To: Multiple recipients of list ORACLE-L Thanks for the response. I have Metalink note 231901.1 which explains the options for installing 8.1.7 on AIX 5L, so I understand what I have to do (after reading it three times). Additional questions: - If we run the 64 bit kernel and put on the required patch for 8.1.7 will I be running Oracle in 64 bit or 32 bit mode? - If Oracle is running in 32 bit mode, what will happen if I restore a cold backup of my AIX 4.3.3 (64 bit) database onto the AIX 5L system? Thanks, Peter Schauss -Original Message- Sent: Friday, September 12, 2003 5:24 AM To: Multiple recipients of list ORACLE-L Peter, I know that this looks odd but as the hardware is 64 bit the AIX O/S has some sort of translation layer that allows it to run Oracle 64 bit even though the kernel is in 32 bit mode. I discovered this when investigating running Oracle 9.2 and Oracle 8.1.7 on AIX 5L. After raising a TAR with Oracle I received the response which basically stated: - Oracle 8.1.7 can only be run in 32 bit mode on AIX 5L. - To run Oracle 8.1.7 and Oracle 9.2 concurrently on AIX 5L you must run AIX in 32 bit mode on 64 bit hardware with 32 bit Oracle 8.1.7 and Oracle 9.2 64 bit (it only exists in 64 bit mode). Confusing I know but somehow true. As an aside I don't think this applies to the likes of HP-UX. I'm fairly sure that running 32bit HP-UX 11 on 64 bit hardware will not allow 64 bit Oracle to run. HTH Chris Dunscombe -Original Message- Sent: 11 September 2003 21:39 To: Multiple recipients of list ORACLE-L O/S is AIX 4.3.3 /usr/sbin/bootinfo -p returns chrp meaning that the hardware is capable of either 32 or 64 bit operation. /usr/sbin/bootinfo -K returns 32 meaning that the kernel is running in 32 bit mode. When I run sqlplus the server says: Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production How can I be running the 64 bit server on a system with a 32 bit kernel? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
