Line No in Oracle Error
BDY.RTF Description: RTF file
RE: Complex Integrity Checking
First of all I want to thank you all for your answers. Let's take'em one by one: Attn: Mercadante, Thomas F [[EMAIL PROTECTED]] - I cannont use instead of trigger because of this error: ORA-25002: cannot create INSTEAD OF triggers on tables Cause: Only BEFORE or AFTER triggers can be created on a table. Action: Change the trigger type to BEFORE or AFTER. I have an Oracle database version 9.0.1.1.1 Attn: Stephane Faroult [[EMAIL PROTECTED]] - for insert your approach works (although I have to change a bit the select in exists condirion) but what about the update statements. - moreover i think this will not keep my integrity rule consistent, if someone try to simply use typical insertupdate statements. Attn: Khedr, Waleed [[EMAIL PROTECTED]] - Can you give me an example for your unique function based index, I mean how can you assign an unique number for various intervals. - anyway if this can be done I assume that would be a very nice, clean solution Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] - this really doesn't suit my needs, create 2 tables instead of one Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony [[EMAIL PROTECTED]] - I did make a function: FUNCTION check_for_overlapped_intervals ( p_start_time IN NUMBER, p_end_time IN NUMBER) RETURN NUMBER IS n NUMBER; BEGIN -- when this select have records to count -- means that the new interval overlap an existing one -- and still is not corectly implement for update stament -- where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time p_end_time AND end_time p_start_time; RETURN(n); END; and use it in the trigger: CREATE OR REPLACE TRIGGER bi_interval BEFORE INSERT OR UPDATE ON intervals REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF check_for_overlapped_intervals(:new.start_time, :new.end_time) 0 THEN raise_application_error(-20100, 'Overlapped intervals'); END IF; END; but still got the same mutating table error. Am I wrong someplace. Thanks again. I try to test all of your solution and above are my answers. Can you still help me. I simplify my problem using a table INTERVALS with 2 columns START_TIME, END_TIME of NUMBER type. CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Please try to insert some data and implement an integrity system like I wanted Regards iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Recovery of CTAS with NOLOGGING Data
Did a Database Recovery was Able to Successfully Recover ALL Data of a Table Created with the CTAS NOLOGGING Option Oracle 8.1.7 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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).
Post - Recovery Identification of Indexes Created with the NOLogging Option
After Completion of Recovery , an Index which had been Created wirth CREATE INDEX NOLOGGING Command , when Attempting to use in a SQL Query Gave the Error ORA-1578 ORACLE data block corrupted (file # %s, block # %s) The respective Block file IDs we were able to Identify the Corrupt Index by using the Query select segment_name from sys.dba_extens where file_id = File Number Given with ORA-1578 Error and Block Number Given with ORA-1578 Error between (block_id and block_id+blocks -1); Qs Is there any Other Way to find Out Such a List of Corrupt Indexes which Had been Created with the NOLOGGING Option after Completion of recovery ? NOTE STATUS = VALID in sys.dba_indexes for the respective index Post-Recovery -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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: Production Database Open Fails after Mount
Vivek, It depends on how many files were already open and how many files this 'small dummy' database had. As John K. mentioned, using 'sar -v' at the time of opening the production database would tell you more about the number of file opened at that time. Both your NFILE and MAX_FILES number look too small. What's set for MAXUSERS? HTH. - K i r t i ;) -Original Message- Sent: Tuesday, June 04, 2002 11:48 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Kirthi NOTE Point 3) (Bottom) We were able to OPEN a Small Dummy Database on the Failing Production Server . If the system wide limit for the number of simultaneously open files is exceeded Would the Dummy Database OOEN Either ? NOTE nfile = 200 max_files = 200 Approx 75 User processes All inclusive would exist on the machine Thanks for responding -Original Message- Sent: Wednesday, June 05, 2002 5:58 AM To: Multiple recipients of list ORACLE-L Vivek, You are right, this is an OS related issue, but a DBA must be aware of why it happens ;) Error 23 means 'File Table Overflow' and it is generated when the system wide limit for the number of simultaneously open files is exceeded. It is controlled by a kernel parameter 'nfile'. which defaults to a value arrived at by a formula that uses 'maxusers' (and a couple of other) kernel parameters. You can check the values set for 'maxusers' and 'nfile' on these servers, and get your SA to increase those on the server where you had a problem starting the database. Use '/usr/sbin/kmtune -q parameter' command to check currently set value for 'nfile' and 'maxusers'. Read more about 'nfile' at http://docs.hp.com/hpux/onlinedocs/os/KCparam.Nfile.html. HTH, - Kirti -Original Message- Sent: Wednesday, June 05, 2002 3:11 AM To: Multiple recipients of list ORACLE-L Solved Database OPENed Successfully on another HP-UX Box Without any ORA-1092 The Same Oracle OS Versions Existed on Both HP-UX Boxes . NOTE Though ORA-1092 was often succeeded by the message :- Error Num 23 NO Idea what Error Num 23 Stands for ? Thus This seems to be Some OS /Hardware Issue with the Previous Production Database on which the Database would NOT Open after OS RE_Installation . For Problem Details Go Below . For Complete Details Either E-mail me Or See Tar Nums - 2263888.995 , 9505435.7 (If accessible) Thanks to All List Vivek -Original Message- Sent: Monday, June 03, 2002 11:57 PM To: LazyDBA.com Discussion Hi Gopal,List What are those UNdocumented Events ? Thanks again Vivek -Original Message- Sent: Monday, June 03, 2002 4:51 PM To: LazyDBA.com Discussion Vivek: I guess SMON runs the command to get the details for regular cleanup. You can use few undocumented events to get the things done depending on the seriousness of the database. These events just asks the SMON to skip few things during recovery and pretty harmless. Best Regards, K Gopalakrishnan - Original Message - From: VIVEK_SHARMA [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Sent: Monday, June 03, 2002 4:29 PM Subject: RE: Production Database Open Fails after Mount Problem Still Existing . ora_3263.trc file :- ORA-01092: ORACLE instance terminated. Disconnection forced Current SQL statement for this session: select line#, sql_text from bootstrap$ where obj# != :1 bootstrap$ seems to tbe the CAUSE . Any Advice ? 1) STATUS in V$LOG shows 2 Groups as INACTIVE the 3rd as CURRENT NOTE - Log Switches are Happening even in Mount State due to some internal Database Activity at the rate of about 5 Switched in 12 Hours . Size of Redo Logfile = 5M 2) RECOVER DATABASE UNTIL CANCEL Succeeds , But ALTER DATABASE OPEN RESETLOGS Also Fails with ORA-1092 like ALTER DATABASE OPEN 3) We Created a SMALL Dummy Database on the Same machine using the Same ORACLE_HOME which we were able to open eith the Same SGA as the Production Database . Thus Prima-facie the O.S. Oracle S/w seem OK . We relinked the network rdbms Components of Oracle 7.3.4.0 too though 4) Due to Root Disk Crash OS was RE-Installed , But Oracle Software Existed Existed on another Hard Disk was Simply Mounted back without any Change after the OS RE-Installation Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about
Re: help
On Tue, Jun 04, 2002 at 06:58:46PM -0800, Sergey V Dolgov wrote: Hello Ferenc, OK, just try this: log in as root rm -f /etc/* reboot And all will be fine :-))) I tried this and now my oracle doesn't work. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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*Loader question
By default all records are loaded is only true if there are no errors. The default ERRORS is 50 which means that the load is automatically aborted when 50 records are rejected due to errors. To assure that all records can be loaded you need to set ERRORS to a higher number than the total number of records in the load. My $0.02 worth, Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 11:28 PM The parameters LOAD and SKIP determine how many records to load and skip. By default ALL records are loaded. HTH. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia * +61-412-232-056 * use mobile please Please note 17 hour time difference between Melbourne and CA -Original Message- Sent: Tuesday, 4 June 2002 8:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ferenc Mantfeld 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: KENNETH JANUSZ 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: Complex Integrity Checking
no matter what you do, if you access table A inside a trigger on table A, oracle will give you mutating table error. What you could (and I really mean you have to consider your business logic here) is go ahead and insert the rows with a temp flag. As soon as you commit, fire up a procedure that will do the scan on the table and delete appropriate rows which have the temp status. BTW how big is this table? What is the frequency of inserts and updates? 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! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: help
Are you serious? Uh-oh! -Original Message- Sent: 05 June 2002 14:08 To: Multiple recipients of list ORACLE-L On Tue, Jun 04, 2002 at 06:58:46PM -0800, Sergey V Dolgov wrote: Hello Ferenc, OK, just try this: log in as root rm -f /etc/* reboot And all will be fine :-))) I tried this and now my oracle doesn't work. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: Mark Leith 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: Post - Recovery Identification of Indexes Created with the NO
Hi, The problem with this is that the blocks couldn't be recovered because you used the NOLOGGING option. They are therefore flagged as corrupt. You may have found that blocks flagged as corrupt will have been logged in you alert log during the recovery operation. All of the objects that were created using NOLOGGING will be in the same state and it may be easier to tackle the problem from this angle rather than examining each block. If you're not sure which objects were created in this fashion then dbv will be able to identify corrupted blocks which you can then analyse in the manner you've described. The indexes will still show as VALID because in a logical sense nothing has happened to invalidate them. The fact that the blocks comprising the indexes are corrupt is (in a data dictionary sense) immaterial. Regards, Mike Hately, Oracle DBA -Original Message- Sent: 05 June 2002 13:33 To: Multiple recipients of list ORACLE-L NOLogging Option After Completion of Recovery , an Index which had been Created wirth CREATE INDEX NOLOGGING Command , when Attempting to use in a SQL Query Gave the Error ORA-1578 ORACLE data block corrupted (file # %s, block # %s) The respective Block file IDs we were able to Identify the Corrupt Index by using the Query select segment_name from sys.dba_extens where file_id = File Number Given with ORA-1578 Error and Block Number Given with ORA-1578 Error between (block_id and block_id+blocks -1); Qs Is there any Other Way to find Out Such a List of Corrupt Indexes which Had been Created with the NOLOGGING Option after Completion of recovery ? NOTE STATUS = VALID in sys.dba_indexes for the respective index Post-Recovery -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike 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: Line No in Oracle Error
Short answer: No Long answer: No Oracle has been dragging its tail in providing this functionality which incidentally is available to you if you don't do exception handling. Then dbms_utility.format_error_stack will return the relevant information. 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! -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 3:58 AM To: Multiple recipients of list ORACLE-L Hi, Is there any way to get line no where error occured in Oracle PL/SQL stored subprograms. Thanks Mnaoj *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
How to model DBA_SEGMENTS for trend analysis?
Title: How to model DBA_SEGMENTS for trend analysis? This is rather a simplistic question, but how would you model the DBA_SEGMENTS table for trend analysis purposes? Simply duplicate the table and add a TIMESTAMP (which is what I wanted to do.) Even though it's *my* project, my boss, insists on being the DA, is adamant that all trend tables include 'natural, complete' business keys. If you follow this logic with respect to DBA_SEGMENTS, you then run into a problem with a PK including PART_NAME which is going to be null for most records in the table, but is necessary to enforce uniqueness. So, not liking this, he then gets the idea that partitions are nothing more then 'sub-segments' with a a 'parent segment' and now wants to see a segments trend table with a self-referencing relationship, PARENT_SEGMENT_NAME, and so forth. I've argued up and down and all around, and of course, my boss, being the typical, stubborn, old fart Sr DBA, refuses to listen. So, I'm just wondering how you trap DBA_SEGMENTS info at your site. Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson multimedia Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
RE: Complex Integrity Checking
two questions: How many records do you insert into that table before a commit ? Is the whole issue simply mutating table error when running some business logic in an insert/update trigger for the intervals table? Regards, Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/5/02 4:33 AM First of all I want to thank you all for your answers. Let's take'em one by one: Attn: Mercadante, Thomas F [[EMAIL PROTECTED]] - I cannont use instead of trigger because of this error: ORA-25002: cannot create INSTEAD OF triggers on tables Cause: Only BEFORE or AFTER triggers can be created on a table. Action: Change the trigger type to BEFORE or AFTER. I have an Oracle database version 9.0.1.1.1 Attn: Stephane Faroult [[EMAIL PROTECTED]] - for insert your approach works (although I have to change a bit the select in exists condirion) but what about the update statements. - moreover i think this will not keep my integrity rule consistent, if someone try to simply use typical insertupdate statements. Attn: Khedr, Waleed [[EMAIL PROTECTED]] - Can you give me an example for your unique function based index, I mean how can you assign an unique number for various intervals. - anyway if this can be done I assume that would be a very nice, clean solution Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] - this really doesn't suit my needs, create 2 tables instead of one Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony [[EMAIL PROTECTED]] - I did make a function: FUNCTION check_for_overlapped_intervals ( p_start_time IN NUMBER, p_end_time IN NUMBER) RETURN NUMBER IS n NUMBER; BEGIN -- when this select have records to count -- means that the new interval overlap an existing one -- and still is not corectly implement for update stament -- where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time p_end_time AND end_time p_start_time; RETURN(n); END; and use it in the trigger: CREATE OR REPLACE TRIGGER bi_interval BEFORE INSERT OR UPDATE ON intervals REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF check_for_overlapped_intervals(:new.start_time, :new.end_time) 0 THEN raise_application_error(-20100, 'Overlapped intervals'); END IF; END; but still got the same mutating table error. Am I wrong someplace. Thanks again. I try to test all of your solution and above are my answers. Can you still help me. I simplify my problem using a table INTERVALS with 2 columns START_TIME, END_TIME of NUMBER type. CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Please try to insert some data and implement an integrity system like I wanted Regards iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania SA is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
unable to split a partition in parallel
Listers,
We have a table range partitioned on a date column.
Last night I tried to split the earliest partition
into itself and an earlier (empty) partition in
parallel. The partition is ~ 25 gigs. This ran
overnight and did not finish.
Here is the statement:
alter table dwcorp.t_claim_alv
split partition p_200107
at (TO_DATE(' 2001-07-01 00:00:00', 'S-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
into (partition p_200106, partition p_200107)
parallel(Degree 12)
/
A quick check of waits showed that the processes were
active and not waiting. A check of the tablespace
showed no temp segments being written there by the
parallel processes.
I removed the parallelism clause and the statement
immediately started writing temp segs that were
growing. This ran for 5 hours and was less than 1/2
done, so we need the PQ to work since the split will
break indexes and remove stats on the split partitions
and we can't have THAT in the middle of the day.
Tried these things to rectify:
1) created empty table with same structure and indexes
in a temp schema, parallel partition split worked.
2) altered the partition to nologging
3) altered the pq of the partition to match that of
the statement
4) ran as both DBA and schema owner
5) removed the leading space from the partition clause
(this is a historical design flaw, might be time to
rectify)
Since the empty table worked, this is probably either
a problem unique to this table or related to the data.
My first thought is that the pq process co-ordinator
is unable to resolve the partition key adequately and
so is unable to properly handoff the required
information to the child pq procs, so they are active
but cannot proceed. This would explain why they write
no temp segs but are active. I would expect to see pq
enqueue waits of some sort, however.
Otherwise, perhaps the pq procs cannot write, but have
never had problems with our PQ procs writing before
and have done many parallel CTAS to this tablespace.
thanks,
jack silvey
__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
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: Complex Integrity Checking
-Original Message- Sent: Wednesday, June 05, 2002 4:53 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** two questions: How many records do you insert into that table before a commit ? Is the whole issue simply mutating table error when running some business logic in an insert/update trigger for the intervals table? Regards, Waleed I'm sorry bu I can't answer to your questions because I don't see the point. Here's a test table: CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Here are some statemens: INSERT INTO intervals (START_TIME,END_TIME) VALUES (3,5) / INSERT INTO intervals (START_TIME,END_TIME) VALUES (2,3) / INSERT INTO intervals (START_TIME,END_TIME) VALUES (7,8) What I want is that the integrity rule (no overlapped intervals) be operational even if i insert a new record or more or update one or more. Think of it the same way an unique key works. This is a simplified table for example purpose. In fact my application is a resource scheduler, so I want a resource not to be assigned for more than 1 client at the same time. Here the start_time and end_time are of number type just for testing, but of course it'll be of date type. I'm starting to think that what I want, can be done in a simple, clean manner but using complex workarounds, isn't it? Thanks! iulian -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/5/02 4:33 AM First of all I want to thank you all for your answers. Let's take'em one by one: Attn: Mercadante, Thomas F [[EMAIL PROTECTED]] - I cannont use instead of trigger because of this error: ORA-25002: cannot create INSTEAD OF triggers on tables Cause: Only BEFORE or AFTER triggers can be created on a table. Action: Change the trigger type to BEFORE or AFTER. I have an Oracle database version 9.0.1.1.1 Attn: Stephane Faroult [[EMAIL PROTECTED]] - for insert your approach works (although I have to change a bit the select in exists condirion) but what about the update statements. - moreover i think this will not keep my integrity rule consistent, if someone try to simply use typical insertupdate statements. Attn: Khedr, Waleed [[EMAIL PROTECTED]] - Can you give me an example for your unique function based index, I mean how can you assign an unique number for various intervals. - anyway if this can be done I assume that would be a very nice, clean solution Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] - this really doesn't suit my needs, create 2 tables instead of one Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony [[EMAIL PROTECTED]] - I did make a function: FUNCTION check_for_overlapped_intervals ( p_start_time IN NUMBER, p_end_time IN NUMBER) RETURN NUMBER IS n NUMBER; BEGIN -- when this select have records to count -- means that the new interval overlap an existing one -- and still is not corectly implement for update stament -- where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time p_end_time AND end_time p_start_time; RETURN(n); END; and use it in the trigger: CREATE OR REPLACE TRIGGER bi_interval BEFORE INSERT OR UPDATE ON intervals REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF check_for_overlapped_intervals(:new.start_time, :new.end_time) 0 THEN raise_application_error(-20100, 'Overlapped intervals'); END IF; END; but still got the same mutating table error. Am I wrong someplace. Thanks again. I try to test all of your solution and above are my answers. Can you still help me. I simplify my problem using a table INTERVALS with 2 columns START_TIME, END_TIME of NUMBER type. CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Please try to insert some data and implement an integrity system like I wanted Regards iulian ** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient
Re: Re:RE: Oracle and Tru64
I had seen Ultrix only at the institute where I was learning computers. I agree -- nobody should have bought Ultrix. But to put Tru64 on Alpha in the same class as Ultrix is *big* mistake. Tru64 and Alpha is a winning combination -- on par with HPUX on PA-RISC, it not better. The problem was that the Alpha processor was priced so high, few people would buy it and, therefore, Digital could never achieve economies of scale. Hemant K Chitale - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, 05 June, 2002 2:09 AM Peter, Please allow me to disagree. I came from the USAF with a very deep love for DEC hardware and software (VAX/VMS), only to be VERY disappointed by them when I was presented with DEC Ultrix. Their straying into the Unix world was a real nightmare. First off their sales folks over sold the capabilities of the 5000/240 workstation. A database server it was not. Ultrix was a failure right out of the gate. That monster combination was a guarantee that I would get a page every night that it was here and the server a re-boot. It finally took me three years to get a Ultrix tech to admit that they had not implemented a TCP_KEEP_ALIVE capability into Ultrix and that I would never see it. At that time the only path was to upgrade to an Alpha with OSF-1 which was crashing on a daily basis. Oracle back then recommended a cold backup of the database twice a day. One of the soccer dads whose's daughter was on the same team as mine was a DEC/Compaq employee. His recommendation was to stay away from OSF at all costs. He was one of the lucky ones when Compaq sold off the CASE tools operation. Oh how I would have loved moving back onto a VAX, but DEC was not interested in that platform any more and HP's 9000 platform was not only cheaper to acquire and support, but faster and more capable as well. We benchmarked a DEC Ultrix box specifically tailored by DEC to database work against an HP9000 that 'just happen to between owners'. The DEC was a multi processor unit, stuffed with every bite of RAM it could hold, multi scsi ports with load balancing on their (at that time) best disk system and a custom Oracle install with a highly tuned (by DEC engineers) Ultrix kernel. We passed then a dmp file with 1 million rows of data for two tables and 4 SQL scripts to run against the data. Took them all day to get the results. Did the same test with the HP that had minimal RAM, one scsi port and only the internal drives and a default Oracle install and only that tweaking of the HP kernel in Oracle's install manual. Same test ran in 4.5 hours hands down. I left DEC behind at that time, never to return. As of today, I love the HP's I have to work with. I do not believe them to be outclassed anywhere and that they do outclass all in terms of reliability and dependability. I must admit to really enjoying a server platform that does it's job day in and day out for months or years without so much as a burp. I'm sure that part of that are three very good SA's, but the hardware/OS speaks for itself as well. I did not shed one tear when DEC fell to Compaq, and will not now that Compaq is falling to HP. I am sure that the good of DEC/Compaq will find it's way into HP-UX as well as the HP9000 series. So we've only good things to look forward to. Dick Goulet Reply Separator Author: Peter Barnett [EMAIL PROTECTED] Date: 6/4/2002 8:08 AM It's a shame that Digital had such good computer scientists and such lousy marketing. Digital Unix and the AlphaServer were the most stable Unix boxes in the world. Compaq never did understand the gem it purchased and HP will never admit that their current generation of hardware was outclassed by Digital 10 years ago. All good runs must come to an end. It is just too bad that the end is an execution by technical nitwits. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Stephane, Hemant Below is the official word that I pulled off HP's website. It is straight PR material, so read between the words as you choose. As I recall, Compaq had already decided not to build the next-generation Alpha chip before the merger arose. If you are interested, I would suggest that you attend the HP roadshow when it comes to a city near you and ask them the hard questions yourself. Having worked for a computer manufacturer in the past, I can assure you that the manufacturer would appreciate it if you bought the last system to come off the production line, then never called them for support. My company plans to continue operating our Tru64 systems for several years to come, and they have provided wonderful service. But we're purchasing new Sun systems. In this session, an HP Executive will highlight the important current and future role HP's business critical and high
* Oracle DBA Needed in Minnesota..
Position: Oracle DBA Location: Rochester, Minnesota Salary Range: 65-75K- maybe more Description: This client company-a leader in its' field, needs an Oracle DBA to provide database support. This position includes support of all aspects of the Oracle relational database product (ASE) on primarily SUN Unix platforms. The key is depth of production database experience (as opposed to development), knowledge of core Oracle infrastructure and the pieces that make up the instances. Tasks will include, but are not limited to: capacity planning, installation, upgrading, monitoring, performance and tuning, responding to trouble calls, writing administrative scripts in Unix shell languages and SQL/PL-SQL, backup and recovery, and maintaining security in the institution's Oracle environments. Sharing a 24x7 on-call rotation with the other members of the team is also expected. This is an interesting position with a solid organization with terrific benefits. *They need a Production DBA not an Development side DBA. This is a full time staff position so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. * Please DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes cannot be considered. If you are employed by a consulting company you must have a long term project history. Requirements: *Bachelor's degree in computer science, related field or equivalent experience. *Three or more years of supporting a multi-server Oracle environment. *Strong analytical and problem solving skills. *Demonstrated proficiency with the HP or SUN Unix operating system. *Advanced Replication experience is a plus. * Must be a U.S. citizen or perm. resident with excellent English. For immediate consideration, please email your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Phone: 1-800-549-8502. (*Please do not call if you need sponsorship) Please Use Job Code: One/DBA/Rochester/Diane I pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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).
Rollback segment shrinks
Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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).
char vs. varchar in a data warehouse
Hi - We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB. The developers have defined most of their small character fields as varchar2 - even the ones of size 1! They tell me that if the column does not have a value, they do not want to have to deal with a 'space' when manipulating that value. Does this make sense? Should I be pursuing this further or shouldn't I care as the DBA? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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).
Partitions
All, Oracle 8.0.5 Tru64 4.0f We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions. My take on it after RTFM ing was to 1) Unload the data 2) Create my new tablespaces for the new partitions 3) Split the last partition 4) Repeat point 3 until I have my relevant number of smaller partitions 5) Create my local indexes for new partitions 6) Reload the data This "looked" OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you drop the last partition in a table (therefore leaving the second from last without a MAXVALUE). Regards (and confused) Lee 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 hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
Re: char vs. varchar in a data warehouse
I say make all char fields varchar2 no mater how long they are. It sure makes it easier for debugging later on, when the duhvelopers tell you that their code doesn't work and its b/c of a data type mismatch. Standards are paramount. Gene [EMAIL PROTECTED] 06/05/02 11:53AM Hi - We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB. The developers have defined most of their small character fields as varchar2 - even the ones of size 1! They tell me that if the column does not have a value, they do not want to have to deal with a 'space' when manipulating that value. Does this make sense? Should I be pursuing this further or shouldn't I care as the DBA? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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: Gene Sais 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).
session being killed
hi, i have a web based Forms application. When a user enters a search criteria in one particular field,order_no, then executes query, after a couple of seconds his session his killed. This only happens when user searches for certain records, for others the search performs fine. There is index on this column but it isnt the PK. any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf 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 DBA Needed in Minnesota..
This position includes support of all aspects of the Oracle relational database product (ASE) on primarily SUN Unix platforms. ASE is the Sybase adaptive server database product, not Oracle. Are you quite clear on what you are seeking? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 05, 2002 8:59 AM Position: Oracle DBA Location: Rochester, Minnesota Salary Range: 65-75K- maybe more Description: This client company-a leader in its' field, needs an Oracle DBA to provide database support. This position includes support of all aspects of the Oracle relational database product (ASE) on primarily SUN Unix platforms. The key is depth of production database experience (as opposed to development), knowledge of core Oracle infrastructure and the pieces that make up the instances. Tasks will include, but are not limited to: capacity planning, installation, upgrading, monitoring, performance and tuning, responding to trouble calls, writing administrative scripts in Unix shell languages and SQL/PL-SQL, backup and recovery, and maintaining security in the institution's Oracle environments. Sharing a 24x7 on-call rotation with the other members of the team is also expected. This is an interesting position with a solid organization with terrific benefits. *They need a Production DBA not an Development side DBA. This is a full time staff position so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. * Please DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes cannot be considered. If you are employed by a consulting company you must have a long term project history. Requirements: *Bachelor's degree in computer science, related field or equivalent experience. *Three or more years of supporting a multi-server Oracle environment. *Strong analytical and problem solving skills. *Demonstrated proficiency with the HP or SUN Unix operating system. *Advanced Replication experience is a plus. * Must be a U.S. citizen or perm. resident with excellent English. For immediate consideration, please email your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Phone: 1-800-549-8502. (*Please do not call if you need sponsorship) Please Use Job Code: One/DBA/Rochester/Diane I pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Tim Gorman 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
Lee, I've been doing a lot of work with reorging partitioned tables and splitting them. I'm on Oracle version 8.0.4 and 2.6 of Sun Solaris. I don't believe that you need to unload your data. You should be able to create your new tablespaces to the correct size. I believe you plan on creating on tablespace per partition, right. Just create those, including the new tablespace for the last, largest partition. Then split the partitions off one-by-one, specifying the new storage parameters and new tablespace name for each new partition in the split command. Your data will automatically be moved into the new tablespace. This is a nice little way to reorg your table.When you are finished splitting off all of your partitions, do an alter table move partition and move the remaining, large partition into it's new, smaller tablespace with the appropriate storage parameters. Then, drop your old tablespace once you have confirmed that it is empty. This should work fine if you have enough space to have both the new and old tablespaces around at the same time. There are some things to watch out for. Any global index will become invalidated. Any local indexes may have the closest partition become invalidated. You may want to drop and rebuild your partitioned indexes into separate smaller tablespaces as well. You can also do an alter move on the indexes instead of dropping them. I know there are some open bugs on alter move of partitioned indexes, so check for those. I don't remember the specifics. It's worth your time to get the sizes of the new tablespaces and initial and next extents right from the start. Also, you want to make sure you can get the move and the index rebuild done in the time available. Been there, done that. Cherie Machler Oracle DBA Gelco Information Network Robertson Lee - lerobe To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lerobe@acxiom cc: .co.uk Subject: Partitions Sent by: [EMAIL PROTECTED] om 06/05/02 11:03 AM Please respond to ORACLE-L All, Oracle 8.0.5 Tru64 4.0f We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions. My take on it after RTFM ing was to 1) Unload the data 2) Create my new tablespaces for the new partitions 3) Split the last partition 4) Repeat point 3 until I have my relevant number of smaller partitions 5) Create my local indexes for new partitions 6) Reload the data This looked OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you drop the last partition in a table (therefore leaving the second from last without a MAXVALUE). Regards (and confused) Lee 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 hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
RE: char vs. varchar in a data warehouse
Lisa, let it be varchar2 ... but the developers are not entirely right too. oracle still puts in a character to indicate that this column has a NULL value. So, unless your storage is at premium, leave it as varchar2, because the moment you make it CHAR, the comparison semantics change when you compare that with varchar2 field and that would be an added cost for your SQLS. 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! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Rollback segment shrinks
Terry, Can you see if there are any session(s) (runaway or not) that might be causing your rollback segments to fill up? Until you can figure out what sessions are filling up the rollbacks you will be stuck adding more space until those session(s) end. Bryan -Original Message- Sent: Wednesday, June 05, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Rodrigues, Bryan 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: char vs. varchar in a data warehouse
Title: RE: char vs. varchar in a data warehouse Makes no sense at all. The 'developers' don't want to deal with the space when manipulating that value? If it ain't there, they do nothing. All of the space management will be up to you, not the developers. Jon -Original Message- From: YTTRI Lisa [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Subject: char vs. varchar in a data warehouse Hi - We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB. The developers have defined most of their small character fields as varchar2 - even the ones of size 1! They tell me that if the column does not have a value, they do not want to have to deal with a 'space' when manipulating that value. Does this make sense? Should I be pursuing this further or shouldn't I care as the DBA? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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 connection
Title: RE: oracle connection I've recently encountered 3113 error e-o-f on com channel, and I followed it through, using trial and error, to the setting of ORA_NLS33. Oracle 8.1.6 installed on Solaris and Dev 1.6.1 installed separately. We started getting the errors and need to specify ORA_NLS33=/hmis11/oracle/1.6.1/ocommon/nls/admin/data explicitly for our server side scripts. After speaking to Oracle support on this, it seems they had the vague suggestion: set it to what works ! - Mike. Database Administrator -Original Message-From: Richard Huntley [mailto:[EMAIL PROTECTED]]Sent: 04 June 2002 20:14To: Multiple recipients of list ORACLE-LSubject: RE: oracle connection Are you using MTS or dedicated connection? If MTS, first do a client trace, if that turns up nothing, just for kicks try adding (SERVER=dedicated) to your client tnsnames.ora file and see if allows you to log in by bypassing MTS. -Original Message- From: Danisment Gazi Unal (ubTools) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 04, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: oracle connection Hi, It's another bad error to diagnose. But, there should be a good note for ora-3113 at metalink. Also, If Oracle can not know that a process is being terminated, there will be no trace for this error. Because Oracle will not have a chance to dump trace file. regards... Alexandre Gorbatchev wrote: "End of communication channel" is common message when server process is terminated. You may take a look at alert.log on server-side to see what could cause it to terminate. It's often there. Also trace file for that session on the server. -- Alexandre - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 2:08 PM hai all. my database connection has a problem. when user start to establish connection... an error returns "End of communication channel". So no user can log in. I've already checked the status of listener , reinstall net8 component, trace file. Seems those are oke. what should i do ? thanks rgds fico -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Softhome - Fico 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: Alexandre Gorbatchev 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). -- Danisment Gazi Unal http://www.ubTools.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (ubTools) 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: Rollback segment shrinks
Terry, What do you have optimal set to? I believe shrink only shrinks to the optimal size. Jim Ball, Terry [EMAIL PROTECTED] wrote: Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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). -- _ Jim Hawkins Oracle Database Administrator [EMAIL PROTECTED] St. Louis, MO USA __ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jim Hawkins 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:Rollback segment shrinks
Terry, Take a look to see if there is some long running process that is inactive and attached to a client that you know is not connected to the database. I've had this happen when someone lets the query from hell loose and then shuts off their PC or worse yet, does a 'set pause on' in their SQL session and minimizes SQL*Plus. Your rollback usage will grow logarithmically since the query is forcing it to be retained. Dick Goulet Reply Separator Author: Ball; Terry [EMAIL PROTECTED] Date: 6/5/2002 7:53 AM Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: char vs. varchar in a data warehouse
Even a stopped clock is right twice a day. They have a silly reason for wanting to use varchar2 but I believe that we've had this discussion before and the reasons for using char on fields of length 1 were not terribly compelling. On the other hand, not want to have to deal with a 'space' makes me shudder. You do have reason to believe that these developers know what they're doing, right? YTTRI Lisa lisa.yttri To: Multiple recipients of list ORACLE-L @cnh.com[EMAIL PROTECTED] Sent by: rootcc: Subject: char vs. varchar in a data warehouse 06/05/2002 11:53 AM Please respond to ORACLE-L Hi - We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB. The developers have defined most of their small character fields as varchar2 - even the ones of size 1! They tell me that if the column does not have a value, they do not want to have to deal with a 'space' when manipulating that value. Does this make sense? Should I be pursuing this further or shouldn't I care as the DBA? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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: Thomas Day 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).
817 intelligent agent resolves to wrong dns entry
config: OEM 2.2, Win2K management server running 81721 one of the Win2K nodes I'm trying to discover has 2 dns entries, one for the machine name and one for the name of the Oracle Names server that resides on it (i have a separate dns entry for the names servers because I periodically need to move them and I put the names of the servers in the SQLNET.ORA, rather than the IP addresses - this works fine). issue is when I discover the node with the names server on it (fwfsdb06, listed as the entered name), the node name by which it gets discovered is oraclenames2. i have another names server on a different machine that resolves correctly (i.e., to the machine name, not to oraclenames1) - just this one that seems to be problematic. I've tried bouncing the agent, deleting the agent config files and bouncing it, even tried putting a second ip address on that server and have oraclenames2 resolve to the second IP address (and bouncing the agent) - now when I try to discover fwfsdb06, it fails completely that the agent is not running, and the node name still shows up as oraclenames2 any ideas? thanks bill -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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 DBA Needed in Minnesota..
Hey look... It's the Mayo Clinic job again... I never knew Bill was into recycling... Oracle Data Base Administer Job Posting Number: 01-0004509 Openings: 1 Section: MIS/Managed Care Job Class: 6115 Job Summary: This position includes support of all aspects of the Oracle relational database product (ASE) on primarily SUN Unix platforms. Tasks will include, but are not limited to: capacity planning, installation, upgrading, monitoring, performance and tuning, responding to trouble calls, writing administrative scripts in Unix shell languages and SQL/PL-SQL, backup and recovery, and maintaining security in the institution`s Oracle environments. Sharing a 24x7 on-call rotation with the other members of the team is also expected. Qualifications: Bachelor`s degree in computer science, or related field. Three or more years of supporting a multi-server Oracle environment. Strong analytical and problem solving skills. Demonstrated proficiency with the HP or SUN Unix operating system. Benefit Eligibility: Benefit Eligible Posting Begin Date: 10/31/2001 Posting End Date: Until Filled Employment Type: Exempt Hours/2-Weeks: 80 Percent Full Time Employment: 100% Schedule: Mon-Fri Shift: Day Weekends: 0 Out of 0 Location: Ozmun Center Floor: 1 Approx Min Hourly Salary: Salaried * Staffing Contact: Wilson, Cheryl A *Education, experience, and tenure are considered along with internal equity when job offers are extended. -Original Message- Sent: Wednesday, June 05, 2002 10:59 AM To: Multiple recipients of list ORACLE-L Position: Oracle DBA Location: Rochester, Minnesota Salary Range: 65-75K- maybe more Description: This client company-a leader in its' field, needs an Oracle DBA to provide database support. This position includes support of all aspects of the Oracle relational database product (ASE) on primarily SUN Unix platforms. The key is depth of production database experience (as opposed to development), knowledge of core Oracle infrastructure and the pieces that make up the instances. Tasks will include, but are not limited to: capacity planning, installation, upgrading, monitoring, performance and tuning, responding to trouble calls, writing administrative scripts in Unix shell languages and SQL/PL-SQL, backup and recovery, and maintaining security in the institution's Oracle environments. Sharing a 24x7 on-call rotation with the other members of the team is also expected. This is an interesting position with a solid organization with terrific benefits. *They need a Production DBA not an Development side DBA. This is a full time staff position so no sub-contractors or third parties please. Please do not call or send a resume if you are not in the U.S. and/or need sponsorship. * Please DO NOT send your resume unless you have a stable work history. Candidates whose work history includes frequent job changes cannot be considered. If you are employed by a consulting company you must have a long term project history. Requirements: *Bachelor's degree in computer science, related field or equivalent experience. *Three or more years of supporting a multi-server Oracle environment. *Strong analytical and problem solving skills. *Demonstrated proficiency with the HP or SUN Unix operating system. *Advanced Replication experience is a plus. * Must be a U.S. citizen or perm. resident with excellent English. For immediate consideration, please email your resume as an attachment to: OraStaff, Inc. Email: [EMAIL PROTECTED] Phone: 1-800-549-8502. (*Please do not call if you need sponsorship) Please Use Job Code: One/DBA/Rochester/Diane I pay referral fees. So please contact me if you know of anyone who would be qualified/interested in the posiition described above- if it is not a match for your skills. Thanks, Bill Law -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: OraStaff 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: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
Re: Rollback segment shrinks
The rollback segment will not shrink if there are active transactions in the rollback segment i.e xacts 0 in v$rollstat for that rollback segment. Check out which transaction is using the rollback segments using v$transaction where XIDUSN = usn from v$rollname. The ses_addr in v$transaction should point you to the session which is running this transaction. Do the honours for that session. Raj Ball, Terry TBall@birch.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] com cc: Sent by: Subject: Rollback segment shrinks root@fatcity. com June 05, 2002 11:53 AM Please respond to ORACLE-L Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitions
Thanks for that but we don't really have the spare space to set up the new tablespaces and the old one. Thats why I initially thought about unloading the data. Thanks again Lee -Original Message- Sent: 05 June 2002 16:31 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Lee, I've been doing a lot of work with reorging partitioned tables and splitting them. I'm on Oracle version 8.0.4 and 2.6 of Sun Solaris. I don't believe that you need to unload your data. You should be able to create your new tablespaces to the correct size. I believe you plan on creating on tablespace per partition, right. Just create those, including the new tablespace for the last, largest partition. Then split the partitions off one-by-one, specifying the new storage parameters and new tablespace name for each new partition in the split command. Your data will automatically be moved into the new tablespace. This is a nice little way to reorg your table.When you are finished splitting off all of your partitions, do an alter table move partition and move the remaining, large partition into it's new, smaller tablespace with the appropriate storage parameters. Then, drop your old tablespace once you have confirmed that it is empty. This should work fine if you have enough space to have both the new and old tablespaces around at the same time. There are some things to watch out for. Any global index will become invalidated. Any local indexes may have the closest partition become invalidated. You may want to drop and rebuild your partitioned indexes into separate smaller tablespaces as well. You can also do an alter move on the indexes instead of dropping them. I know there are some open bugs on alter move of partitioned indexes, so check for those. I don't remember the specifics. It's worth your time to get the sizes of the new tablespaces and initial and next extents right from the start. Also, you want to make sure you can get the move and the index rebuild done in the time available. Been there, done that. Cherie Machler Oracle DBA Gelco Information Network Robertson Lee - lerobe To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lerobe@acxiom cc: .co.uk Subject: Partitions Sent by: [EMAIL PROTECTED] om 06/05/02 11:03 AM Please respond to ORACLE-L All, Oracle 8.0.5 Tru64 4.0f We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions. My take on it after RTFM ing was to 1) Unload the data 2) Create my new tablespaces for the new partitions 3) Split the last partition 4) Repeat point 3 until I have my relevant number of smaller partitions 5) Create my local indexes for new partitions 6) Reload the data This looked OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you drop the last partition in a table (therefore leaving the second from last without a MAXVALUE). Regards (and confused) Lee 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 hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. All, Oracle 8.0.5 Tru64 4.0f We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions. My take on it after RTFM ing was to 1) Unload the data 2) Create my new tablespaces for the new partitions 3) Split the last partition 4) Repeat point 3 until I have my relevant number of smaller partitions 5) Create my local indexes for new partitions 6) Reload the data This looked OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you drop the last partition in a table (therefore leaving the second from last without a MAXVALUE). Regards (and confused) Lee 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 hereby notified that any
RE: Complex Integrity Checking
Title: RE: Complex Integrity Checking
Iulian, this is what you want, NO? (except this works for date fields not number fields as you've put in
your latest posts)... This is done using two triggers.
SQL insert into interval
values('01-JAN-2002','01-MAR-2002');
2
1 row created.
SQL insert into interval
values('03-MAR-2002','26-MAR-2002');
2
1 row created.
SQL insert into interval
values('03-FEB-2002','14-MAR-2002');
2 insert into interval
*
ERROR at line 1:
ORA-2: date overlap 03-FEB-02 14-MAR-02
ORA-06512: at RHUNTLEY.SINTERVAL, line 23
ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'
SQL insert into interval
values('01-DEC-1999','01-JAN-2002');
2 insert into interval
*
ERROR at line 1:
ORA-2: date overlap 03-FEB-02 14-MAR-02
ORA-06512: at RHUNTLEY.SINTERVAL, line 23
ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'
SQL insert into interval
values('05-JAN-2002','01-FEB-2002');
2 insert into interval
*
ERROR at line 1:
ORA-2: date overlap 03-FEB-02 14-MAR-02
ORA-06512: at RHUNTLEY.SINTERVAL, line 23
ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 05, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Complex Integrity Checking
-Original Message-
Sent: Wednesday, June 05, 2002 4:53 PM
To: Multiple recipients of list ORACLE-L
**
This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.
**
two questions: How many records do you insert into that table before a
commit ?
Is the whole issue simply mutating table error when running some business
logic in an insert/update trigger for the intervals table?
Regards,
Waleed
I'm sorry bu I can't answer to your questions because I don't see the point.
Here's a test table:
CREATE TABLE intervals (
start_time NUMBER NOT NULL,
end_time NUMBER NOT NULL
)
Here are some statemens:
INSERT INTO intervals
(START_TIME,END_TIME)
VALUES
(3,5)
/
INSERT INTO intervals
(START_TIME,END_TIME)
VALUES
(2,3)
/
INSERT INTO intervals
(START_TIME,END_TIME)
VALUES
(7,8)
What I want is that the integrity rule (no overlapped intervals) be
operational even if i insert a new record or more or update one or more.
Think of it the same way an unique key works.
This is a simplified table for example purpose. In fact my application is a
resource scheduler, so I want a resource not to be assigned for more than 1
client at the same time.
Here the start_time and end_time are of number type just for testing, but of
course it'll be of date type.
I'm starting to think that what I want, can be done in a simple, clean
manner but using complex workarounds, isn't it?
Thanks!
iulian
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 6/5/02 4:33 AM
First of all I want to thank you all for your answers.
Let's take'em one by one:
Attn: Mercadante, Thomas F [[EMAIL PROTECTED]]
- I cannont use instead of trigger because of this error:
ORA-25002: cannot create INSTEAD OF triggers on tables
Cause: Only BEFORE or AFTER triggers can be created on a table.
Action: Change the trigger type to BEFORE or AFTER.
I have an Oracle database version 9.0.1.1.1
Attn: Stephane Faroult [[EMAIL PROTECTED]]
- for insert your approach works (although I have to change a bit the
select
in exists condirion) but what about the update statements.
- moreover i think this will not keep my integrity rule consistent, if
someone try to simply use typical insertupdate statements.
Attn: Khedr, Waleed [[EMAIL PROTECTED]]
- Can you give me an example for your unique function based index, I
mean
how can you assign an unique number for various intervals.
- anyway if this can be done I assume that would be a very nice, clean
solution
Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen
[[EMAIL PROTECTED]]
- this really doesn't suit my needs, create 2 tables instead of one
Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony
[[EMAIL PROTECTED]]
- I did make a function:
FUNCTION check_for_overlapped_intervals (
p_start_time IN NUMBER,
p_end_time IN NUMBER)
RETURN NUMBER
IS
n NUMBER;
BEGIN
-- when this select have records to count
-- means that the new interval overlap an existing one
-- and still is not corectly implement for update stament
-- where it should not consider the current record
Re: Partitions
Not having a partition with MAXVALUE in partitioned table is not a problem (as long, as you know, that there will be no values outside of the existing partitions range). Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Robertson Lee - lerobe To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 05, 2002 12:03 PM Subject: Partitions All, Oracle 8.0.5 Tru64 4.0f We have a partitioned table here that has been left for sometime now and we need to split up the last partition into at least 6 to 7 resized partitions. My take on it after RTFM ing was to 1) Unload the data 2) Create my new tablespaces for the new partitions 3) Split the last partition 4) Repeat point 3 until I have my relevant number of smaller partitions 5) Create my local indexes for new partitions 6) Reload the data This "looked" OK but now I am rethinking this. The problem is that the last partition tablespace will still be massive, so I really need to drop the last partition, and its tablespace and recreate. Is this feasible ?? Can you drop the last partition in a table (therefore leaving the second from last without a MAXVALUE). Regards (and confused) Lee The information contained in this communication isconfidential, is intended only for the use of the recipientnamed above, and may be legally privileged. If the reader of this message is not the intended recipient, you arehereby notified that any dissemination, distribution orcopying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computersystem.
RE: Rollback segment shrinks
Title: RE: Rollback segment shrinks Terry, This query will tell you which process is using which rollback segment, maybe that'll help you backtrack to find out what's going on. column Oracle UserName FORMAT a15 column RBS Name format a15 select r.name RBS Name, p.spid, l.sid ORACLE PID, s.username Oracle UserName from v$lock l, v$process p, v$rollname r, v$session s where s.sid = l.sid and l.sid = p.pid(+) and r.usn = trunc(l.id1(+)/65536) and l.type(+) = 'TX' and l.lmode(+) = 6 order by r.name / HTH, Rich -Original Message- From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 12:23 PM To: Multiple recipients of list ORACLE-L Subject: RE: Rollback segment shrinks Terry, Can you see if there are any session(s) (runaway or not) that might be causing your rollback segments to fill up? Until you can figure out what sessions are filling up the rollbacks you will be stuck adding more space until those session(s) end. Bryan -Original Message- Sent: Wednesday, June 05, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Rodrigues, Bryan 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).
Archiver process in 7.3.4
Anybody knows how to get status of archiver process programmatically in Oracle 7.3.4? This information is available from v$instance in Oracle 8+. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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: char vs. varchar in a data warehouse
If the column is defined as not null, then varchar2(1) and char(1) would make no difference. Also, if the columns can accept null values, and no values are entered, there's no difference either. The developers seem to be under the misunderstanding that when a column is defined as char, and no values are entered, it still occupies space equal to the length of the column. Show them the use of the dump function. Select col1, dump(col1) from tablename; What the developer would be more concerned about would be with character datatypes defined with lengths more than 1, being blank padded with space when the data length does not match the field length. The comparison semantics change. Hence, I like to see all my columns with varchar2 datatypes. Also, since we are talking in bytes here, there's always a 1 byte overhead for each field. The only exception is null values at the end of the row. Hence, generally columns likely to contain null values are grouped at the end of the table. Raj YTTRI Lisa lisa.yttri@cTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] nh.com cc: Sent by: Subject: char vs. varchar in a data warehouse root@fatcity. com June 05, 2002 11:53 AM Please respond to ORACLE-L Hi - We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB. The developers have defined most of their small character fields as varchar2 - even the ones of size 1! They tell me that if the column does not have a value, they do not want to have to deal with a 'space' when manipulating that value. Does this make sense? Should I be pursuing this further or shouldn't I care as the DBA? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: char vs. varchar in a data warehouse
Lisa, The only reason for a space in a char(1) would be if it were inserted explicitly. Inserting a NULL into a char column does not result in any padding. That said, I don't see any problem with varchar2(1), other than the extra 1 or 2 bytes ( can't remember which ) that are used in the header. Jared YTTRI Lisa [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/05/2002 08:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:char vs. varchar in a data warehouse Hi - We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB. The developers have defined most of their small character fields as varchar2 - even the ones of size 1! They tell me that if the column does not have a value, they do not want to have to deal with a 'space' when manipulating that value. Does this make sense? Should I be pursuing this further or shouldn't I care as the DBA? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rollback segment shrinks
Alternately run this querry to check which rollback segments are in use... set linesize 120 select substr(a.os_user_name,1,8) OS User , substr(b.object_name,1,30) Object Name , substr(b.object_type,1,8) Type , substr(c.segment_name,1,10) RBS , e.process PROCESS , substr(d.used_urec,1,8) # of Records from v$locked_object a , dba_objects b , dba_rollback_segs c , v$transaction d , v$session e where a.object_id = b.object_id and a.xidusn = c.segment_id and a.xidusn = d.xidusn and a.xidslot = d.xidslot and d.addr = e.taddr / For shrink use alter rollback segment rollback_segment_name shrink to 10M; (or whatever size is allowable in your situation (but it will not be less then extent_size*min extents) Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 05 Jun 2002 09:13:32 -0800 The rollback segment will not shrink if there are active transactions in the rollback segment i.e xacts 0 in v$rollstat for that rollback segment. Check out which transaction is using the rollback segments using v$transaction where XIDUSN = usn from v$rollname. The ses_addr in v$transaction should point you to the session which is running this transaction. Do the honours for that session. Raj Ball, Terry TBall@birch.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] com cc: Sent by: Subject: Rollback segment shrinks root@fatcity. com June 05, 2002 11:53 AM Please respond to ORACLE-L Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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).
Two Listener
I am running two listener on one server, How can I stop logging for specific listener, Itry to do this but dosn't work: set log_status listener2 off, also I have tried with set log_status listener2 0, but non of them are working. Any Idea? Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Recovery of CTAS with NOLOGGING Data
A Sample Test :- 1) Database Cold Backup taken 2) CTAS ...NOLOGGING Option used to create a table 3) Crashed the Database Lost ALL the Datafiles Containing the Table 4) Extracted the Col Back Did Recovery RESULT Was Able to Successfully Recover ALL Data of the Table which had been created with the CTAS.. NOLOGGING Option Qs. Is the Above Behaviour in accordance with what the Manual States ? Oracle 8.1.7 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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: Recovery of CTAS with NOLOGGING Data
On Wed, 5 Jun 2002, VIVEK_SHARMA wrote: Did a Database Recovery was Able to Successfully Recover ALL Data of a Table Created with the CTAS NOLOGGING Option Did you recover from a backup taken before or after the CTAS? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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 on NT
1. Does the Oracle service on Win2K run in the security domain of its login user (LocalSys)? A: It runs as SYSTEM by default. 2. If so, would that explain why RMAN can't write to a network drive that's mapped by a different user who is logged in to the domain rather than the local machine? A: Services running as the SYSTEM user cannot access network drives. Side question: Am I correct in my belief that the Oracle service must be running before an Oracle instance can start, and that it keeps running even when the instance is shut down? A: The service must be running to start Oracle. The service is actually the VOS portion of Oracle. ( Virtual Operating System - see James Morle's book ) When the instance is shutdown, the service may be stopped, but that is not required. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rollback segment shrinks
Title: RE: Rollback segment shrinks Terry, This query will tell you which process is using which rollback segment, maybe that'll help you backtrack to find out what's going on. column Oracle UserName FORMAT a15 column RBS Name format a15 select r.name RBS Name, p.spid, l.sid ORACLE PID, s.username Oracle UserName from v$lock l, v$process p, v$rollname r, v$session s where s.sid = l.sid and l.sid = p.pid(+) and r.usn = trunc(l.id1(+)/65536) and l.type(+) = 'TX' and l.lmode(+) = 6 order by r.name / Then go into Instance Mananger and take a look at what sql is being executed and when you find the one that's causing the problem, kill it. HTH, Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Subject: Re:Rollback segment shrinks Terry, Take a look to see if there is some long running process that is inactive and attached to a client that you know is not connected to the database. I've had this happen when someone lets the query from hell loose and then shuts off their PC or worse yet, does a 'set pause on' in their SQL session and minimizes SQL*Plus. Your rollback usage will grow logarithmically since the query is forcing it to be retained. Dick Goulet Reply Separator Author: Ball; Terry [EMAIL PROTECTED] Date: 6/5/2002 7:53 AM Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: char vs. varchar in a data warehouse
Raj, What do you mean by : 'oracle still puts in a character to indicate that this column has a NULL value' ? Oracle does not insert anything into a nullable column when the insert value is an empty string or a NULL. This is true of both char and varchar2. Or are you saying that oracle somehow tracks that a NULL was inserted by use of a special character? ( Guess I could dump a block and find out, but it's easier to ask you. :) Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/05/2002 09:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: char vs. varchar in a data warehouse Lisa, let it be varchar2 ... but the developers are not entirely right too. oracle still puts in a character to indicate that this column has a NULL value. So, unless your storage is at premium, leave it as varchar2, because the moment you make it CHAR, the comparison semantics change when you compare that with varchar2 field and that would be an added cost for your SQLS. 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! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Archiver process in 7.3.4
select value from v$parameter where name = 'log_archive_start'; I know that this is set in 8.0.5. Try it for 7.3.4. Kevin -Original Message- Sent: Wednesday, June 05, 2002 12:13 PM To: Multiple recipients of list ORACLE-L Anybody knows how to get status of archiver process programmatically in Oracle 7.3.4? This information is available from v$instance in Oracle 8+. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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: Kevin Lange 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: Archiver process in 7.3.4
How about the ARCHIVE LOG LIST command in svrmgrl ? -Original Message- Sent: Wednesday, June 05, 2002 12:13 PM To: Multiple recipients of list ORACLE-L Anybody knows how to get status of archiver process programmatically in Oracle 7.3.4? This information is available from v$instance in Oracle 8+. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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: Kevin Lange 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).
INDEX move
Hi I have few of primary key and unique indexes on main data tablespace.I am thinking that if I moved those indexes into diffrent tablespace then we woudl have some performance gain.If I am not correct let me know please?Is any impact if I move primary key and unique indexes to INDEX tablespace? Thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema 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).
RE: Archiver process in 7.3.4
Title: RE: Archiver process in 7.3.4 how about examining v$parameter for the value of the log_archive_start parameter? Matt Adams - GE Appliances - [EMAIL PROTECTED] Contrary to popular opinion, Unix is user friendly. It's just particular about who it makes friends with. -Original Message- From: Alex Hillman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 1:13 PM To: Multiple recipients of list ORACLE-L Subject: Archiver process in 7.3.4 Anybody knows how to get status of archiver process programmatically in Oracle 7.3.4? This information is available from v$instance in Oracle 8+. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman 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: help
Perhaps we should let Ferenc know that when a 'help' message shows up on the list, all heck breaks loose. ( take that, you ultra paranoid firewalls ) Ferenc, you need to send the 'help' to [EMAIL PROTECTED] Jared Ferenc Mantfeld [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/04/2002 06:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:help help -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ferenc Mantfeld INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Remove Duplicates
Here is an interesting script I found on Metalink (Note:1019920.6) for removing duplicates, but I have not tried it yet: == Title: == Script to Eliminate Non-unique Rows === Disclaimer: === This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text editors, email packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. = Abstract: = This script removes all but one row (all but the row with the highest rowid) from owner.table in each group of rows having identical values in column(s). Multiple columns must be separated with commas (without spaces). Script TFSUNIQU is intended primarily for use in deleting rows that prevent the creation of a unique index on the columns in column(s). It will happily delete rows that are not identical, as long as the rows are identical with respect to the values of the columns in column(s). = Requirements: = You must have DELETE privileges on the selected table. === Script: === --- cut -- cut -- cut -- SET ECHO off REM NAME: TFSUNIQU.SQL REM USAGE:@path/tfsuniqu schema_name table_name column_name(s) REM REM REQUIREMENTS: REM DELETE on selected table REM REM AUTHOR: REM Grant Franjione, Phil Joel, and Cary Millsap REM (c)1994 Oracle Corporation REM REM PURPOSE: REM Removes all but one row (all but the row with the highest rowid) REM from owner.table in each group of rows having identical values REM in colum(s). Multiple columns must be seperated with commas REM (without spaces). REM REM TFSUNIQU is intended primarily for use in deleting rows that REM prevent the creation of a unique index on the columns in REM column(s). It will happily delete rows that are not identical, REM as long as the rows are identical with respect to the values of REM the columns in column(s). REM REM EXAMPLE: REM N/A REM REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM REM Main text of script follows: def owner = 1 def table = 2 def uukey = 3 delete from owner..table where rowid in ( select rowid from owner..table minus select min(rowid) from owner..table group by uukey ) / undef owner undef table undef uukey --- cut -- cut -- cut -- David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Office (919) 466-6723 Pager [EMAIL PROTECTED] Fax (919) 466-6783 http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- From: Terrian, Tom [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 04, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Subject: Remove Duplicates I know I have seen this posted before... We have a large range partitioned table that has duplicates in it. What is the fastest way to remove the dups.? I have the following scripts which do it but may be fast or slow. What do you guys use? DELETE FROM tablename WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM tablename GROUP BY fieldnames); Or alter table table_name add constraint duplicate_cons unique key (column_name) exceptions into exception table; How to find duplicates: select column_name, count(column_name) from table_name group by column_name having count(column_name) 1; Tom
Re: INDEX move
If your index tablespace is on the same physical device than your table tablespace , you will have no gain. Is your bottleneck an IO one ? --- Seema Singh [EMAIL PROTECTED] a écrit : Hi I have few of primary key and unique indexes on main data tablespace.I am thinking that if I moved those indexes into diffrent tablespace then we woudl have some performance gain.If I am not correct let me know please?Is any impact if I move primary key and unique indexes to INDEX tablespace? Thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Two Listener
Hamid, try: lsnrctl set current_listener listener2 lsnrctl set log_status off lsnrctl save_config listener.ora Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L I am running two listener on one server, How can I stop logging for specific listener, Itry to do this but dosn't work: set log_status listener2 off, also I have tried with set log_status listener2 0, but non of them are working. Any Idea? Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Mercadante, Thomas F 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: char vs. varchar in a data warehouse
I am pretty sure I read that oracle puts a character in the field, (it could be on this list or it could be something in oracle 7) But in a blockdump I just did, for a null row (tow columns both null) there is nothing in the dump, for second row(first col null, second has value) the first column value is shown as '*NULL*' I believe the discussion was something related to how null and empty string is handled. But here again in 816 the empty strings are not shown once again. Oracle 8161 data_block_dump === tsiz: 0x1fb8 hsiz: 0x1a pbl: 0x095c2c44 bdba: 0x0140337a flag=--- ntab=1 nrow=4 frre=-1 fsbo=0x1a fseo=0x1fa7 avsp=0x1f7a tosp=0x1f7a 0xe:pti[0] nrow=4 offs=0 0x12:pri[0] offs=0x1fb5 0x14:pri[1] offs=0x1faf 0x16:pri[2] offs=0x1faa 0x18:pri[3] offs=0x1fa7 block_row_dump: tab 0, row 0, 0x1fb5 tl: 3 fb: --H-FL-- lb: 0x0 cc: 0 tab 0, row 1, 0x1faf tl: 6 fb: --H-FL-- lb: 0x0 cc: 2 col 0: *NULL* col 1: [ 1] 41 tab 0, row 2, 0x1faa tl: 5 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [ 1] 41 tab 0, row 3, 0x1fa7 tl: 3 fb: --H-FL-- lb: 0x1 cc: 0 end_of_block_dump This proves that I was wrong... but I am pretty sure I remember reading about it ... 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! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Archiver process in 7.3.4
This might help: select b.name, s.sid, s.serial#, s.status from v$session s, v$bgprocess b where s.paddr = b.paddr order by b.name, sid / If the archiver is started, there will be an 'ARCH' process. Jared Alex Hillman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/05/2002 10:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Archiver process in 7.3.4 Anybody knows how to get status of archiver process programmatically in Oracle 7.3.4? This information is available from v$instance in Oracle 8+. Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alex Hillman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Complex Integrity Checking
Here is a working example: drop table test_intervals; CREATE TABLE test_intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL,primary key (start_time,end_time) ); create or replace package test_mut as type start_time_tab_type is table of number index by binary_integer; start_time_tab start_time_tab_type; end_time_tab start_time_tab_type; end; / create or replace trigger testupd before update or insert on test_intervals for each row declare m_cnt number := 0; begin test_mut.start_time_tab(test_mut.start_time_tab.count + 1) := :new.start_time; test_mut.end_time_tab (test_mut.end_time_tab.count + 1) := :new.end_time; end; / create or replace trigger testupd1 after update or insert on test_intervals declare m_cnt number := 0; begin for i in 1..test_mut.start_time_tab.count loop dbms_output.put_line(i); select count(*) into m_cnt from test_intervals where (test_mut.start_time_tab(i) between start_time and end_time or start_time between test_mut.start_time_tab(i) and test_mut.end_time_tab(i)) and not(start_time = test_mut.start_time_tab(i) and end_time = test_mut.end_time_tab(i)); if m_cnt 0 then test_mut.start_time_tab.delete; test_mut.end_time_tab.delete; raise_application_error (-20001,' overlap error '); end if; end loop; test_mut.start_time_tab.delete; test_mut.end_time_tab.delete; end; / Try different inserts/updates. Regards, Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: INDEX move
1. Data and Index segments have different storage and sizing requirements. That's the main reason for them to be put up in different tablespaces. 2. If all indexes are in a seperate tablespace of their own, one could avoid backing up this tablespace if time and space are a constraint. 3. The I/O should be evenly distributed across the disks and controllers. It will not help you, if you create a new Index tablespace, and put it on a disk, that is currently experiencing heavy I/O. 4. Having data and Index on seperate disks will benefit you, depending on the data access patterns. Sequential access might benefit from it, but would not make any difference to random access. As always, I could stand corrected. Raj Seema Singh oracledbam@hoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tmail.comcc: Sent by: Subject: INDEX move [EMAIL PROTECTED] om June 05, 2002 01:55 PM Please respond to ORACLE-L Hi I have few of primary key and unique indexes on main data tablespace.I am thinking that if I moved those indexes into diffrent tablespace then we woudl have some performance gain.If I am not correct let me know please?Is any impact if I move primary key and unique indexes to INDEX tablespace? Thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema 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: 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 refresh of delta data for materialized views
Ferenc Mantfeld wrote: Hi All I have a very large data set for a DW and I created a materialized view on it to give me a quick access to group by and summarization results, I want some way to be able to update the MV with just the delta after I perform an incremental load to the base fact or dimension tables on which the MV is built. From what I have played with so far, I don't see a ready solution. Fast refreshes don't work when MV is on complex view. We are talking about dimension tables with possibly close to a billion rows, and fact tables of similar proportions, so the time taken to refresh the MV becomes a factor. Any useful suggestions, write-up's, white papers in this regard would be extremely helpful and welcome. Ta muchly in advance ! Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia Ferenc, This has just crossed my mind and perhaps that after I think harder I'll find it a foolish idea, but would it be possible to have snapshot logs (or their home-made equivalents) on all the tables in the view and building a 'delta view' on top of those logs ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: INDEX move
In a multi-user system, you do not get any performance gain by separating indexes from their associated tables. However I like to place them in different tablespaces for other reasons: tables and their corresponding indexes grow at different rates and I prefer to have objects of different types separated. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 05, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Hi I have few of primary key and unique indexes on main data tablespace.I am thinking that if I moved those indexes into diffrent tablespace then we woudl have some performance gain.If I am not correct let me know please?Is any impact if I move primary key and unique indexes to INDEX tablespace? Thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema 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: MacGregor, Ian A. 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).
Permissions on user trace files
Hi all, User Trace files are currently created as -rw-r- Is there an easy way to change the permissions when they are created to -rw-r--r-- The developers would like to be able to run Sql Trace on queries on the development box and then run tkprof on the resulting file. I'm perfectly happy giving them permission to do so, since it means I won't need to run it for them several times a day. I'm on Solaris 2.6, Oracle 8.1.7.2 TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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 model DBA_SEGMENTS for trend analysis?
Title: How to model DBA_SEGMENTS for trend analysis? I cheat. My segment history table does not have a PK. Instead, I use a UK which allows the partition name to be null. Your boss probably wouldn't like me very much 8-) Kevin KennedyFirst Point Energy Corporation -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 7:08 AMTo: Multiple recipients of list ORACLE-LSubject: How to model DBA_SEGMENTS for trend analysis? This is rather a simplistic question, but how would you model the DBA_SEGMENTS table for trend analysis purposes? Simply duplicate the table and add a TIMESTAMP (which is what I wanted to do.) Even though it's *my* project, my boss, insists on being the DA, is adamant that all trend tables include 'natural, complete' business keys. If you follow this logic with respect to DBA_SEGMENTS, you then run into a problem with a PK including PART_NAME which is going to be null for most records in the table, but is necessary to enforce uniqueness. So, not liking this, he then gets the idea that partitions are nothing more then 'sub-segments' with a a 'parent segment' and now wants to see a segments trend table with a self-referencing relationship, PARENT_SEGMENT_NAME, and so forth. I've argued up and down and all around, and of course, my boss, being the typical, stubborn, old fart Sr DBA, refuses to listen. So, I'm just wondering how you trap DBA_SEGMENTS info at your site. Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson multimedia Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
RE: why so much slower
Set sort_area_size to very large as 20Gb (obscene) amount of space available. Doing 2 large table outer joins returns results in .341 seconds - both partitioned on same criteria added one small codetable equijoin with one of the larger tables. There is a foreign key to codetable and index that is unique. Used hash join hint Used nested loop hint Basically saw two large joins sort merged hash join then nested join to smaller table - much much smaller codetable. NO matter what it seems query is much much slower - Any ideas?
RE: Permissions on user trace files
set _trace_files_public =true in init.ora. 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! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: custom DD views to allow users to see source without needing
We have the same problem with SQL Navigator. Any suggestions would be great. Jay Miller -Original Message- Sent: Tuesday, June 04, 2002 7:08 PM To: Multiple recipients of list ORACLE-L needing exe rights Hi, Jack list, We had the same problem here and we finally resolved it in a similar way. We created 3 views as sys : all_objects_xx, all_arguments_xx and all_source_xx, synonyms for both views and granted select permissions to user. But, we still have a problem. User needs to debug packages (step by step) and it seems that when you use dbms_debug the views are not enough. The only way to achieve this is by granting create any procedure to user. We want to avoid grant such permission. Do you have faced the same problem ? If yes, how do you resolve it ? Best regards, Mario. Por favor, responda a [EMAIL PROTECTED] Enviado por: [EMAIL PROTECTED] Destinatarios: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] CC: Asunto: custom DD views to allow users to see source without needing exe rights Clasificación: Good afternoon co-listers, Recently we had a problem with TOAD and I thought I would share our solution. TOAD looks at the views ALL_ARGUMENTS and ALL_OBJECTS to see procedural code. Unless a user has the ability to execute a package/procedure/function, they cannot see the source code through these views, and can't see the source in TOAD. This limitiation is hard-coded in the view structure. Upon reflection, it occured to me that I could recreate these views in the users' schema, customized to remove the necessity of having execute priv to see the code, and since Oracle looks local first during object name resolution, it would probably use these views instead of the data dictionary views. This worked. The two views that I customized are below - feel free to use. jack silvey ALL_ARGUMENTS: select u.name owner, /* OWNER */ nvl(a.procedure$,o.name) object_name, /* OBJECT_NAME */ decode(a.procedure$,null,null, o.name) package_name, /*PACKAGE_NAME */ o.obj# object_id, /* OBJECT_ID */ decode(a.overload#,0,null,a.overload#) overload, /*OVERLOAD */ a.argument argument_name, /* ARGUMENT_NAME */ a.position# position, /* POSITION */ a.sequence# sequence, /* SEQUENCE */ a.level# data_level, /* DATA_LEVEL */ decode(a.type#, /* DATA_TYPE */ 0, null, 1, decode(a.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(a.scale, -127, 'FLOAT', 'NUMBER'), 3, 'NATIVE INTEGER', 8, 'LONG', 9, decode(a.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 11, 'ROWID', 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 29, 'BINARY_INTEGER', 69, 'ROWID', 96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'), 102, 'REF CURSOR', 104, 'UROWID', 105, 'MLSLABEL', 106, 'MLSLABEL', 110, 'REF', 111, 'REF', 112, decode(a.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 121, 'OBJECT', 122, 'TABLE', 123, 'VARRAY', 178, 'TIME', 179, 'TIME WITH TIME ZONE', 180, 'TIMESTAMP', 181, 'TIMESTAMP WITH TIME ZONE', 231, 'TIMESTAMP WITH LOCAL TIME ZONE', 182, 'INTERVAL YEAR TO MONTH', 183, 'INTERVAL DAY TO SECOND', 250, 'PL/SQL RECORD', 251, 'PL/SQL TABLE', 252, 'PL/SQL BOOLEAN', 'UNDEFINED') data_type, default$ default_value, /* DEFAULT_VALUE */ deflength default_length, /* DEFAULT_LENGTH */ decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefi ned') in_out, /* IN_OUT */ length data_length, /* DATA_LENGTH */ precision# data_precision, /* DATA_PRECISION */ scale data_scale, /* DATA_SCALE */ radix radix, /* RADIX */ decode(a.charsetform, 1, 'CHAR_CS', /* CHARACTER_SET_NAME */ 2, 'NCHAR_CS', 3, NLS_CHARSET_NAME(a.charsetid), 4, 'ARG:'||a.charsetid) char_cs, a.type_owner type_owner, /* TYPE_OWNER */ a.type_name type_name, /* TYPE_NAME */ a.type_subname type_subname, /* TYPE_SUBNAME */ a.type_linkname type_link, /* TYPE_LINK */ a.pls_type pls_type /* PLS_TYPE */ from sys.obj$ o,sys.argument$ a,sys.user$ u where o.obj# = a.obj# and o.owner# = u.user# ALL_OBJECTS: select u.name owner, o.name object_name, o.subname subobject_name, o.obj# object_id, o.dataobj# data_object_id, decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNDEFINED') object_type, o.ctime created, o.mtime last_ddl_time, to_char(o.stime, '-MM-DD:HH24:MI:SS') timestamp, decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID') status, decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N') temporary, decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N')
RE: Permissions on user trace files
Title: RE: Permissions on user trace files Jay, Use the _trace_files_public=true in the init.ora file. FYI This opens the door for dumps of data that is sensitive. -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 3:55 PM To: Multiple recipients of list ORACLE-L Subject: Permissions on user trace files Hi all, User Trace files are currently created as -rw-r- Is there an easy way to change the permissions when they are created to -rw-r--r-- The developers would like to be able to run Sql Trace on queries on the development box and then run tkprof on the resulting file. I'm perfectly happy giving them permission to do so, since it means I won't need to run it for them several times a day. I'm on Solaris 2.6, Oracle 8.1.7.2 TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: why so much slower
more info. It seemed just when I went from two to three tables in a join there was a very substantial increase in elapsed time. I did join with one large table and small codetable alone and performed like a champ. H. Any ideas? -Original Message-From: Stankus, Paula G Sent: Wednesday, June 05, 2002 3:03 PMTo: '[EMAIL PROTECTED]'Subject: RE: why so much slower Set sort_area_size to very large as 20Gb (obscene) amount of space available. Doing 2 large table outer joins returns results in .341 seconds - both partitioned on same criteria added one small codetable equijoin with one of the larger tables. There is a foreign key to codetable and index that is unique. Used hash join hint Used nested loop hint Basically saw two large joins sort merged hash join then nested join to smaller table - much much smaller codetable. NO matter what it seems query is much much slower - Any ideas?
RE: custom DD views to allow users to see source without needing
We have granted view on dba_source, dba_arguments and dba_objects to developers. As for debugging, we create a copy of production db as of 5AM and make it available to developers (after scrambling some data). It is their playground to fix and diagnose the problems. In few days their complete access to production will go away. although we make production changes only on a weekly basis, I generate a schema description (basically a bunch of web pages) that has all objects info, all source code dumped to web pages with links to navigate easily between content to a file system that only developers have access. So far they seem to be happy with it. 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! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Jr.DBA, Mid level DBA, Sr.DBA
The Jr knows the passwords and where to find the databases. (Data) The Mid knows the answers to the exam questions. (Information) The Sr knows how the systems integrate and where to find things out. (Knowledge) The Guru knows the engine internals but also knows to test for 'features'. (Wisdom) -Original Message- Sent: Friday, May 31, 2002 1:11 PM To: Multiple recipients of list ORACLE-L The jr thinks that she knows. The mid knows that she knows. The sr knows that she knows not. Awareness of ignorance is the mark of true knowledge. I like cake. jack silvey --- Fink, Dan [EMAIL PROTECTED] wrote: I agree, the Jr. DBA must focus on learning. Mid DBA...is still learning. Many Mid still view tuning/troubleshooting as an art (with a little magic thrown in) Sr. DBA...is still learning. Realizes that database management is a science, requiring research, expirementation and a very healthy dose of skepticism. The best Sr. DBAs that I know are the first ones to say 'I don't know'. That is the only true path to learning. No one can know everything. Often times the Jr. DBA will be a great source of knowledge since they don't know what NOT to ask. Reaction to reading Books/Documentation Junior - I did not know that Mid - I know that Senior - Perhaps...let's prove it When a developer/user asks for a change to the database Junior - I'll look it up and change it Mid - I have a script to do that, I'll let you know when I am done Senior - Why are you needing this change? Did you realize that x will cause y? Let's figure out the best way to accomplish the result. When faced with an undocumented condition/unknown error Junior - Log a TAR, get frustrated with 'We need a trace file. We need more information. We have no clue...'. Calls more senior help. Mid - Remembers a passage in a book, tries out the command. Fixes the symptom. Senior - Knows that x can cause y, if z is present. Tracks condition from symptom through to actual problem. Attends sessions at IOUG Junior - Assumes that all speakers know exactly what they are talking about and all vendor tools work as advertised. Mid - Listens to and believes Tim, Cary, Craig, Rich, Rachel, Gaja and all other High Holy Oracle Gurus preach Senior - Listens to, questions and tests (on non production systems) what Tim, Cary, Craig, Rich, Rachel, Gaja and all other High Holy Oracle Gurus preach Knowledge level Junior - Has no clue what they know and don't know Mid - Knows what they know Senior - Knows what they don't know Every Senior DBA is a mix of Jr. and Mid. They may know a great deal about one subsystem of Oracle, but lack knowledge in another area. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456 -Original Message- Sent: Friday, May 31, 2002 2:43 AM To: Multiple recipients of list ORACLE-L Junior DBA's job is a learning. Mid DBA's job is a science. Sr. DBA's job is the Art. Srs feel database, users, developers and everything else. They feel what, where, how, when and why should by done. Their intuition is of high degree. ... and everybody is sure - the Sr DBA knows everything. (so one of the priority of Sr DBA is to make this impression) -- Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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). i -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California--
RE: Complex Integrity Checking
Title: RE: Complex Integrity Checking In my suggestion you example for check_for_overlapped_intervals would have the autonomous transaction pragma, thereby avoiding the mutating table error. Tony Aponte -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Complex Integrity Checking -Original Message- Sent: Wednesday, June 05, 2002 4:53 PM To: Multiple recipients of list ORACLE-L ** This email has been tested for viruses by F-Secure Antivirus administered by IT Network Department. ** two questions: How many records do you insert into that table before a commit ? Is the whole issue simply mutating table error when running some business logic in an insert/update trigger for the intervals table? Regards, Waleed I'm sorry bu I can't answer to your questions because I don't see the point. Here's a test table: CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time NUMBER NOT NULL ) Here are some statemens: INSERT INTO intervals (START_TIME,END_TIME) VALUES (3,5) / INSERT INTO intervals (START_TIME,END_TIME) VALUES (2,3) / INSERT INTO intervals (START_TIME,END_TIME) VALUES (7,8) What I want is that the integrity rule (no overlapped intervals) be operational even if i insert a new record or more or update one or more. Think of it the same way an unique key works. This is a simplified table for example purpose. In fact my application is a resource scheduler, so I want a resource not to be assigned for more than 1 client at the same time. Here the start_time and end_time are of number type just for testing, but of course it'll be of date type. I'm starting to think that what I want, can be done in a simple, clean manner but using complex workarounds, isn't it? Thanks! iulian -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/5/02 4:33 AM First of all I want to thank you all for your answers. Let's take'em one by one: Attn: Mercadante, Thomas F [[EMAIL PROTECTED]] - I cannont use instead of trigger because of this error: ORA-25002: cannot create INSTEAD OF triggers on tables Cause: Only BEFORE or AFTER triggers can be created on a table. Action: Change the trigger type to BEFORE or AFTER. I have an Oracle database version 9.0.1.1.1 Attn: Stephane Faroult [[EMAIL PROTECTED]] - for insert your approach works (although I have to change a bit the select in exists condirion) but what about the update statements. - moreover i think this will not keep my integrity rule consistent, if someone try to simply use typical insertupdate statements. Attn: Khedr, Waleed [[EMAIL PROTECTED]] - Can you give me an example for your unique function based index, I mean how can you assign an unique number for various intervals. - anyway if this can be done I assume that would be a very nice, clean solution Attn: Richard Huntley [[EMAIL PROTECTED]], Gogala, Mladen [[EMAIL PROTECTED]] - this really doesn't suit my needs, create 2 tables instead of one Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony [[EMAIL PROTECTED]] - I did make a function: FUNCTION check_for_overlapped_intervals ( p_start_time IN NUMBER, p_end_time IN NUMBER) RETURN NUMBER IS n NUMBER; BEGIN -- when this select have records to count -- means that the new interval overlap an existing one -- and still is not corectly implement for update stament -- where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time p_end_time AND end_time p_start_time; RETURN(n); END; and use it in the trigger: CREATE OR REPLACE TRIGGER bi_interval BEFORE INSERT OR UPDATE ON intervals REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF check_for_overlapped_intervals(:new.start_time, :new.end_time) 0 THEN raise_application_error(-20100, 'Overlapped intervals'); END IF; END; but still got the same mutating table error. Am I wrong someplace. Thanks again. I try to test all of your solution and above are my answers. Can you still help me. I simplify my problem using a table INTERVALS with 2 columns START_TIME, END_TIME of NUMBER type. CREATE TABLE intervals ( start_time NUMBER NOT NULL, end_time
Patch a Patch
I was just wondering if I'm allowed to apply a new patch after I have already installed patch. Say from 8.1.7.3 to 8.1.7.4. Do I have to uninstall the .3 patch and then install the .4 or what? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: david hill 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: Complex Integrity Checking
Title: RE: Complex Integrity Checking With the introduction of Autonomous Transactions this is no longer entirely true. If you call an autonomous transaction procedure, it is executed in a separate transaction context. This gives you the ability to probe the mutating table without inducing the error. A good explanation can be found in Tom Kyte's Export One-on-one Oracle book in the chapter on Autonomous Transactions. HTH Tony Aponte -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: Complex Integrity Checking no matter what you do, if you access table A inside a trigger on table A, oracle will give you mutating table error. What you could (and I really mean you have to consider your business logic here) is go ahead and insert the rows with a temp flag. As soon as you commit, fire up a procedure that will do the scan on the table and delete appropriate rows which have the temp status. BTW how big is this table? What is the frequency of inserts and updates? 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!
RE: session being killed
Look in your alert log. Something similar to this was happening to me yesterday. Turned out that a piece of code was producing an ORA-7445 error and killing the session. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Wednesday, June 05, 2002 9:03 AM To: Multiple recipients of list ORACLE-L hi, i have a web based Forms application. When a user enters a search criteria in one particular field,order_no, then executes query, after a couple of seconds his session his killed. This only happens when user searches for certain records, for others the search performs fine. There is index on this column but it isnt the PK. any ideas? __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf 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: kkennedy 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: char vs. varchar in a data warehouse
Hi, I am sure u probably know this already a leading null value column will take up a byte and like in the second case where the first column is null and the second column has a value the av row length is increased by 1 as the null takes up a byte. If the first col had a value and the second column had a null, then only the first one would have been stored. So its a good practice to put the columns with frequnet nulls at the end of the table so that it will decrease the ave row length and increase the data density. Regards, Madhavan But in a blockdump I just did, for a null row (tow columns both null) there is nothing in the dump, for second row(first col null, second has value) the first column value is shown as '*NULL*' I believe the discussion was something related to how null and empty string is handled. But here again in 816 the empty strings are not shown once again. Oracle 8161 -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - You've just been FastMailed! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur 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: why so much slower
Why don't you try using a leading or ordered hint to get oracle to use the smaller table first? From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: why so much slower Date: Wed, 05 Jun 2002 12:18:45 -0800 more info. It seemed just when I went from two to three tables in a join there was a very substantial increase in elapsed time. I did join with one large table and small codetable alone and performed like a champ. H. Any ideas? -Original Message- Sent: Wednesday, June 05, 2002 3:03 PM To: '[EMAIL PROTECTED]' Set sort_area_size to very large as 20Gb (obscene) amount of space available. Doing 2 large table outer joins returns results in .341 seconds - both partitioned on same criteria added one small codetable equijoin with one of the larger tables. There is a foreign key to codetable and index that is unique. Used hash join hint Used nested loop hint Basically saw two large joins sort merged hash join then nested join to smaller table - much much smaller codetable. NO matter what it seems query is much much slower - Any ideas? _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough 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: Patch a Patch
On Wed, Jun 05, 2002 at 02:18:31PM -0800, david hill wrote: I was just wondering if I'm allowed to apply a new patch after I have already installed patch. Say from 8.1.7.3 to 8.1.7.4. yes Do I have to uninstall the .3 patch and then install the .4 or what? Oracle database patchsets for UNIX are cumulative. Applying the latest patchset will include all of the fixes in previous patchsets for the same baseline version. (i.e. The 8.1.7.4.0 will include the fixes in 8.1.7.3). Unless otherwise noted, you can apply the latest patchset -- with out applying lower patchsets first. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: Patch a Patch
No. First of all, you cannot uninstall patch 8.1.7.3 because oracle hasn't provided uninstall in any of the recent patch sets. You should just install over the existing 8.1.7.4. The ONLY uninstall is the full, cold backup. If you decide that the patch set is not for you after, let's say, a week, you are stuck. Your only option is to ask oracle for one off patch for your particular bug. That is, generally, why I do not recommend plunging, head first, into the new patchset as soon as it appears. While waiting for two or three months, I monitor RDBMS forums on the metablink and released one off patches. Only when I am reasonably sure that everything is going to be hunky dory will I request my boss to authorize the upgrade. On 2002.06.05 18:18 david hill wrote: I was just wondering if I'm allowed to apply a new patch after I have already installed patch. Say from 8.1.7.3 to 8.1.7.4. Do I have to uninstall the .3 patch and then install the .4 or what? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: david hill 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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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: Patch a Patch
No need to uninstall, just apply the next patch. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Wednesday, June 05, 2002 3:19 PM To: Multiple recipients of list ORACLE-L I was just wondering if I'm allowed to apply a new patch after I have already installed patch. Say from 8.1.7.3 to 8.1.7.4. Do I have to uninstall the .3 patch and then install the .4 or what? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: david hill 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: kkennedy 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: Patch a Patch
patches are supposed to be cumulative, so 8.1.7.4 should include all fixed/patches since the 8.1.7 baseline. joe david hill wrote: I was just wondering if I'm allowed to apply a new patch after I have already installed patch. Say from 8.1.7.3 to 8.1.7.4. Do I have to uninstall the .3 patch and then install the .4 or what? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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: Complex Integrity Checking
Title: RE: Complex Integrity Checking The problem with this solution is the Autonomous Transactions will not be able to see any changes done within the current transaction only the committed one. So no way to enforce business logic during the context of the transaction. This is why I asked before how frequently commit happens. Regards, Waleed -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 05, 2002 6:33 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Complex Integrity Checking With the introduction of Autonomous Transactions this is no longer entirely true. If you call an autonomous transaction procedure, it is executed in a separate transaction context. This gives you the ability to probe the mutating table without inducing the error. A good explanation can be found in Tom Kyte's Export One-on-one Oracle book in the chapter on Autonomous Transactions. HTH Tony Aponte -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 05, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: Complex Integrity Checking no matter what you do, if you access table A inside a trigger on table A, oracle will give you mutating table error. What you could (and I really mean you have to consider your business logic here) is go ahead and insert the rows with a temp flag. As soon as you commit, fire up a procedure that will do the scan on the table and delete appropriate rows which have the temp status. BTW how big is this table? What is the frequency of inserts and updates? 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!
Maintaining Simulated Standby DB on 7.1.6.0
Hi, I've got an old (test db) Oracle 7.1.6.0 db on HP-Ux. I've created a simulated standby db on another server of same spec. I say simulated because there is no real standby mechanism in this old version. Anyway, the standby database is mounted and on regular intervals (via cron), is doing recover database using backup controlfile until cancel. So far this arrangement is working perfectly. Now this is where it starts to become hairy. The tablespace USERS has 2 datafiles. USERS needs to be resized and the datafiles relocated. What I've done so far are the ff: 1. exported everything in USERS 2. dropped USERS t/space on primary then recreate with new datafile size and location. 3. recreated all tables in it. 4. imported all data back. While this is happening, the primary is happily generating all these archive logs which in turn are also being applied to the standby. The standby's alert.log indicates it has applied all logs and reports no errors. I then tried to open the standby db using alter databse open resetlogs. I didn't expect this to work and sure enough I got the error: SQLDBA alter database open resetlogs; ORA-01177: data file does not match dictionary - probably old incarnation ORA-01110: data file 5: '/u9/db/DEV/users01.dbf' users01.dbf is the old filename. My questions are: 1. Why is there no error reported? I was expecting it to complain about an unknown datafile. 2. If the standby is applying the logs where is it applying the transactions to? 3. Since there is no '... offline drop' in this version, is there any other way to get around this apart from completely recreating the standby? Any input much appreciated. Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ross Collado 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).
