Re: simple question
Why do you have r declared as number(10)? On 01/08/2004 03:24:25 PM, oranew2004 wrote: > CREATE OR REPLACE PROCEDURE labware_admin.truncate_tables > ( > schema_ownerIN VARCHAR2 > ) > AS >r number(10); > BEGIN > FOR r IN (SELECT table_name FROM dba_tables WHERE owner=schema_owner) > LOOP > EXECUTE IMMEDIATE 'truncate table schema_owner.'||r.table_name; > END LOOP; > END; > > > ERROR: Line 14 Column 55 PLS-00364: loop index variable 'R' use is invalid. > > > Any ideas? > > Thank you! > > > > > - > Do you Yahoo!? > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question
I'll add that if you have foreign keys, they will mess you up. Here is an old script I have in my stash that you can modify with info already provided by others. procedure truncate_all(code_word in varchar2 default 'XXX') is cursor c1 is select table_name,constraint_name from user_constraints where constraint_type = 'R'; cursor c2 is select table_name from user_tables; handle integer; rows integer; begin if code_word != 'NUKE' then raise_application_error(-20001, 'WRONG MAGIC WORD, BUCKWHEAT.'); end if; dbms_output.enable(10); handle := dbms_sql.open_cursor; for t in c1 loop dbms_sql.parse(handle,'alter table '||t.table_name||' disable constraint '||t.constraint_name, dbms_sql.native); rows := dbms_sql.execute(handle); end loop; for t in c2 loop dbms_sql.parse(handle,'truncate table '||t.table_name, dbms_sql.native); rows := dbms_sql.execute(handle); end loop; for t in c1 loop dbms_sql.parse(handle,'alter table '||t.table_name||' enable constraint '||t.constraint_name, dbms_sql.native); rows := dbms_sql.execute(handle); end loop; exception when others then dbms_output.put_line(SQLCODE||': '||SQLERRM); end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question
Remove the r number(10); and it should be fine. Tom Mercadante Oracle Certified Professional -Original Message-From: oranew2004 [mailto:[EMAIL PROTECTED]Sent: Thursday, January 08, 2004 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: simple question CREATE OR REPLACE PROCEDURE labware_admin.truncate_tables( schema_owner IN VARCHAR2) AS r number(10); BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner=schema_owner) LOOP EXECUTE IMMEDIATE 'truncate table schema_owner.'||r.table_name; END LOOP; END; ERROR: Line 14 Column 55 PLS-00364: loop index variable 'R' use is invalid. Any ideas? Thank you! Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: simple question
Greg, Try this: create or replace procedure truncate_tables(in_owner in varchar2) is BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner=in_owner) LOOP EXECUTE IMMEDIATE 'truncate table ' || in_owner || '.' || r.table_name; END LOOP;END;/ You will need to grant select on DBA_TABLES to the owner of this procedure. You may also need to grant ALTER ANY TABLE to the same user to allow you to perform the Truncate function. I forget exactly which priv you need to do this - but something extra needs to be granted to the owner of the procedure to perform the trancate function. Good Luck Tom Mercadante Oracle Certified Professional -Original Message-From: Greg Sorrel [mailto:[EMAIL PROTECTED]Sent: Thursday, January 08, 2004 2:55 PMTo: Multiple recipients of list ORACLE-LSubject: simple question Hi! I need to wrap this SQL into SP where I can pass new owner name. I'm planning create db table with all owners I need to pass. BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner='TRAIN1') LOOP EXECUTE IMMEDIATE 'truncate table TRAIN1.'||r.table_name; END LOOP;END;/ Thanks. Greg Do you Yahoo!?Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: simple question
CREATE OR REPLACE PROCEDURE labware_admin.truncate_tables( schema_owner IN VARCHAR2) AS r number(10); BEGIN FOR r IN (SELECT table_name FROM dba_tables WHERE owner=schema_owner) LOOP EXECUTE IMMEDIATE 'truncate table schema_owner.'||r.table_name; END LOOP; END; ERROR: Line 14 Column 55 PLS-00364: loop index variable 'R' use is invalid. Any ideas? Thank you! Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
Re: DDLs -- was Re: simple question on DDL
My point wasn't to pick any particular detail of any particular example . I was merely making the point that whilst the concept of DDL without commits seems to be straightforward, the requirement for designing something that could analyse and handle all the consequent errors that might be a non-trivial problem. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 24 January 2003 16:40 > >Take your first example : >insert into t1 values (1); >drop table t1; > -- how to deal with self-deadlock ? >insert into t1 values (2); >commit; > >Why does Oracle HAVE to commit when the DROP TABLE is issued ? >What if the INSERT had been issued by another session ? Would >the DROP TABLE go through in this session ? >The "self-deadlock" could be handled as an Error -- a Transaction error, >with a message like "cannot drop table when transaction is active in >current session". >A duhveloper who has written a very long-winded .SQL file or procedure >and tries to drop a Table when he has an active transaction should be >caught and "errored" -- he shouldn't be allowed to drop his own table, he >has made a logical error. > >Take the second example : >insert into t1 values (1); >drop table t2; > -- how to deal with lock by other user ? >insert into t1 values (2); >commit; > >If the DROP TABLE may have to wait for another user who has a lock on T2, >why should the INSERT INTO T1 be committed ? Our user hasn't completed >his transaction yet. > >It all depends on what you mean by a "Transaction". The way Oracle has >written DDLs, a "Transaction" ends and is committed when the next DDL >is issued. But that may not be a logical transaction in the real world. > >My point is not that the DROP TABLE should be "roll-backable". The DROP >TABLE itself must always commit it's own statement. --- it's own statement. >That is to ensure that a third user does not see inconsistency when running >a transaction accessing T1. But why should the DROP TABLE explicitly >commit the previous statement ? In your example, you have shown that >an inconsistency may arise when a DROP is issued on a table with an >outstanding transaction. > >But take the case where : >INSERT INTO T10 values ('a'); >-- now I want to, for the heck of it, or bec'ose I'm a bad programmer ... >DROP TABLE XYZ ; >-- oops, my INSERT INTO T10 should be rolled back >-- but the Drop XYZ is independent >ROLLBACK; > >Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT >INTO T10. >I didn't want that ! >It would have been better if this DROP TABLE XYZ was an Autonomous Transaction. >But in the first example, the DROP TABLE T1 should return an error. >So, the Oracle Kernel must have some more complexity to see if there's any >outstanding >transaction in the current session [it already checks for other sessions >because they >hold TM locks on the Table !] are pending against the same table being dropped. > >Hemant > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DDLs -- was Re: simple question on DDL
Agreed. There's a lot of code out there that was developed considering the way Oracle handled DDL and DML specifically. Although I would have liked Oracle to handle DDL as an Autonomous Transaction and provide an error message for the scenario in the first example, I guess it might be too late to change the kernel now. As Arup said, hopefully Oracle 38i will allow us to mix DDL with DML and yet have DDL as Autonomous Transactions. When Oracle didn't have Sequences before Oracle6, people wrote code to SELECT MAX(KEY)+1. There might still be a few developers and few pieces of code doing precisely that instead of using a sequence [of course, development which is RDBMS-independent might not use an Oracle Sequence]. I was just expressing a wish that Oracle could have done DDL and DML better.We are stuck with QWERTY and we are stuck with DDL that does an Auto-Commit. Hemant At 10:49 AM 24-01-03 -0800, you wrote: Tom - I think you've nailed it. Think of the design decisions that some of Oracle's competitors made in the early days and how silly they seem in retrospect. Anyone remember the row-locking vs. block-locking wars? The other aspect that many people don't think of if they have never worked in a vendor's development staff is inertia. You as a developer are assigned a list of tasks which is usually larger than you can accomplish. Marketing bases those tasks on 1) which features will get more customers or 2) what customers are howling about -- where is the ROI? Changing how the kernel works is a high-risk business, so I think you are probably stuck with the way it works, even if you feel it is inconsistent. If Oracle did go back and "fix" stuff in this area, there may be customer scripts that rely on this behavior, even if we feel it is inconsistent. -Original Message- Sent: Friday, January 24, 2003 11:35 AM To: Multiple recipients of list ORACLE-L Hemant, My guess is that Oracle, at some point in time long ago, decided that DDL's and DML's should not be mixed together. Because they could not (or did not want to) deal with the issue, they decided to perform an implicit commit before any DDL statement was issued. Case closed. This is the way it was done. Things have changed in the (more than??) 10 years that this code was written. We are all smarter and have much more experience in what we would like to see happen, rather than how it is currently coded. Could Oracle change this code? Absolutely. Will they? Only if enough people ask for it to be changed. Would I like to see it changed? No. I really don't see the need. I think DDL and DML are two different things and should not be mixed together. If they are mixed together, then developers need to learn how it works and deal with it. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 24, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Take your first example : insert into t1 values (1); drop table t1; -- how to deal with self-deadlock ? insert into t1 values (2); commit; Why does Oracle HAVE to commit when the DROP TABLE is issued ? What if the INSERT had been issued by another session ? Would the DROP TABLE go through in this session ? The "self-deadlock" could be handled as an Error -- a Transaction error, with a message like "cannot drop table when transaction is active in current session". A duhveloper who has written a very long-winded .SQL file or procedure and tries to drop a Table when he has an active transaction should be caught and "errored" -- he shouldn't be allowed to drop his own table, he has made a logical error. Take the second example : insert into t1 values (1); drop table t2; -- how to deal with lock by other user ? insert into t1 values (2); commit; If the DROP TABLE may have to wait for another user who has a lock on T2, why should the INSERT INTO T1 be committed ? Our user hasn't completed his transaction yet. It all depends on what you mean by a "Transaction". The way Oracle has written DDLs, a "Transaction" ends and is committed when the next DDL is issued. But that may not be a logical transaction in the real world. My point is not that the DROP TABLE should be "roll-backable". The DROP TABLE itself must always commit it's own statement. --- it's own statement. That is to ensure that a third user does not see inconsistency when running a transaction accessing T1. But why should the DROP TABLE explicitly commit the previous statement ? In your example, you have shown that an inconsistency may arise when a DROP is issued on a table with an outstanding transaction. But take the case where : INSERT INTO T10 values ('a'); -- now I want to, for the heck of it, or bec'ose I'm a bad programmer ... DROP TABLE XYZ ; -- oops, my INSERT INTO T10 should be rolled back -- but the Drop XYZ is independent ROLLBACK; Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT INTO T10. I didn't w
RE: DDLs -- was Re: simple question on DDL
> I think DDL and DML are two different things and should not be mixed > together. If they are mixed together, then developers need to learn how it > works and deal with it. And there you have it. If you need DDL in your code, isolate it in an anonymous transaction, or just be sure that you are at a safe commit point. jared "Mercadante, Thomas F" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/24/2003 09:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: DDLs -- was Re: simple question on DDL Hemant, My guess is that Oracle, at some point in time long ago, decided that DDL's and DML's should not be mixed together. Because they could not (or did not want to) deal with the issue, they decided to perform an implicit commit before any DDL statement was issued. Case closed. This is the way it was done. Things have changed in the (more than??) 10 years that this code was written. We are all smarter and have much more experience in what we would like to see happen, rather than how it is currently coded. Could Oracle change this code? Absolutely. Will they? Only if enough people ask for it to be changed. Would I like to see it changed? No. I really don't see the need. I think DDL and DML are two different things and should not be mixed together. If they are mixed together, then developers need to learn how it works and deal with it. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DDLs -- was Re: simple question on DDL
Tom - I think you've nailed it. Think of the design decisions that some of Oracle's competitors made in the early days and how silly they seem in retrospect. Anyone remember the row-locking vs. block-locking wars? The other aspect that many people don't think of if they have never worked in a vendor's development staff is inertia. You as a developer are assigned a list of tasks which is usually larger than you can accomplish. Marketing bases those tasks on 1) which features will get more customers or 2) what customers are howling about -- where is the ROI? Changing how the kernel works is a high-risk business, so I think you are probably stuck with the way it works, even if you feel it is inconsistent. If Oracle did go back and "fix" stuff in this area, there may be customer scripts that rely on this behavior, even if we feel it is inconsistent. -Original Message- Sent: Friday, January 24, 2003 11:35 AM To: Multiple recipients of list ORACLE-L Hemant, My guess is that Oracle, at some point in time long ago, decided that DDL's and DML's should not be mixed together. Because they could not (or did not want to) deal with the issue, they decided to perform an implicit commit before any DDL statement was issued. Case closed. This is the way it was done. Things have changed in the (more than??) 10 years that this code was written. We are all smarter and have much more experience in what we would like to see happen, rather than how it is currently coded. Could Oracle change this code? Absolutely. Will they? Only if enough people ask for it to be changed. Would I like to see it changed? No. I really don't see the need. I think DDL and DML are two different things and should not be mixed together. If they are mixed together, then developers need to learn how it works and deal with it. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 24, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Take your first example : insert into t1 values (1); drop table t1; -- how to deal with self-deadlock ? insert into t1 values (2); commit; Why does Oracle HAVE to commit when the DROP TABLE is issued ? What if the INSERT had been issued by another session ? Would the DROP TABLE go through in this session ? The "self-deadlock" could be handled as an Error -- a Transaction error, with a message like "cannot drop table when transaction is active in current session". A duhveloper who has written a very long-winded .SQL file or procedure and tries to drop a Table when he has an active transaction should be caught and "errored" -- he shouldn't be allowed to drop his own table, he has made a logical error. Take the second example : insert into t1 values (1); drop table t2; -- how to deal with lock by other user ? insert into t1 values (2); commit; If the DROP TABLE may have to wait for another user who has a lock on T2, why should the INSERT INTO T1 be committed ? Our user hasn't completed his transaction yet. It all depends on what you mean by a "Transaction". The way Oracle has written DDLs, a "Transaction" ends and is committed when the next DDL is issued. But that may not be a logical transaction in the real world. My point is not that the DROP TABLE should be "roll-backable". The DROP TABLE itself must always commit it's own statement. --- it's own statement. That is to ensure that a third user does not see inconsistency when running a transaction accessing T1. But why should the DROP TABLE explicitly commit the previous statement ? In your example, you have shown that an inconsistency may arise when a DROP is issued on a table with an outstanding transaction. But take the case where : INSERT INTO T10 values ('a'); -- now I want to, for the heck of it, or bec'ose I'm a bad programmer ... DROP TABLE XYZ ; -- oops, my INSERT INTO T10 should be rolled back -- but the Drop XYZ is independent ROLLBACK; Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT INTO T10. I didn't want that ! It would have been better if this DROP TABLE XYZ was an Autonomous Transaction. But in the first example, the DROP TABLE T1 should return an error. So, the Oracle Kernel must have some more complexity to see if there's any outstanding transaction in the current session [it already checks for other sessions because they hold TM locks on the Table !] are pending against the same table being dropped. Hemant At 09:50 AM 23-01-03 -0800, you wrote: >One question to ask is whether whether all DDL >use the same strategy. Similarly, if you have >multiple code paths for "do a ddl call" how much >more risk of error do you introduce to the kernel. >Finally how do you get a consistent error response >to the end user if the error condition of apparently >identical events can fail in extremely different ways. > >Consider the complexities of finding a consistent >kernel level approach to: > >insert into t1 values
RE: DDLs -- was Re: simple question on DDL
Hemant, My guess is that Oracle, at some point in time long ago, decided that DDL's and DML's should not be mixed together. Because they could not (or did not want to) deal with the issue, they decided to perform an implicit commit before any DDL statement was issued. Case closed. This is the way it was done. Things have changed in the (more than??) 10 years that this code was written. We are all smarter and have much more experience in what we would like to see happen, rather than how it is currently coded. Could Oracle change this code? Absolutely. Will they? Only if enough people ask for it to be changed. Would I like to see it changed? No. I really don't see the need. I think DDL and DML are two different things and should not be mixed together. If they are mixed together, then developers need to learn how it works and deal with it. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, January 24, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Take your first example : insert into t1 values (1); drop table t1; -- how to deal with self-deadlock ? insert into t1 values (2); commit; Why does Oracle HAVE to commit when the DROP TABLE is issued ? What if the INSERT had been issued by another session ? Would the DROP TABLE go through in this session ? The "self-deadlock" could be handled as an Error -- a Transaction error, with a message like "cannot drop table when transaction is active in current session". A duhveloper who has written a very long-winded .SQL file or procedure and tries to drop a Table when he has an active transaction should be caught and "errored" -- he shouldn't be allowed to drop his own table, he has made a logical error. Take the second example : insert into t1 values (1); drop table t2; -- how to deal with lock by other user ? insert into t1 values (2); commit; If the DROP TABLE may have to wait for another user who has a lock on T2, why should the INSERT INTO T1 be committed ? Our user hasn't completed his transaction yet. It all depends on what you mean by a "Transaction". The way Oracle has written DDLs, a "Transaction" ends and is committed when the next DDL is issued. But that may not be a logical transaction in the real world. My point is not that the DROP TABLE should be "roll-backable". The DROP TABLE itself must always commit it's own statement. --- it's own statement. That is to ensure that a third user does not see inconsistency when running a transaction accessing T1. But why should the DROP TABLE explicitly commit the previous statement ? In your example, you have shown that an inconsistency may arise when a DROP is issued on a table with an outstanding transaction. But take the case where : INSERT INTO T10 values ('a'); -- now I want to, for the heck of it, or bec'ose I'm a bad programmer ... DROP TABLE XYZ ; -- oops, my INSERT INTO T10 should be rolled back -- but the Drop XYZ is independent ROLLBACK; Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT INTO T10. I didn't want that ! It would have been better if this DROP TABLE XYZ was an Autonomous Transaction. But in the first example, the DROP TABLE T1 should return an error. So, the Oracle Kernel must have some more complexity to see if there's any outstanding transaction in the current session [it already checks for other sessions because they hold TM locks on the Table !] are pending against the same table being dropped. Hemant At 09:50 AM 23-01-03 -0800, you wrote: >One question to ask is whether whether all DDL >use the same strategy. Similarly, if you have >multiple code paths for "do a ddl call" how much >more risk of error do you introduce to the kernel. >Finally how do you get a consistent error response >to the end user if the error condition of apparently >identical events can fail in extremely different ways. > >Consider the complexities of finding a consistent >kernel level approach to: > >insert into t1 values (1); >drop table t1; > -- how to deal with self-deadlock ? >insert into t1 values (2); >commit; > >insert into t1 values (1); >drop table t2; > -- how to deal with lock by other user ? >insert into t1 values (2); >commit; > > > >Regards > >Jonathan Lewis >http://www.jlcomp.demon.co.uk > >Coming soon a new one-day tutorial: >Cost Based Optimisation >(see http://www.jlcomp.demon.co.uk/tutorial.html ) > >Next Seminar dates: >(see http://www.jlcomp.demon.co.uk/seminar.html ) > >England__January 21/23 >USA_(CA, TX)_August > > >The Co-operative Oracle Users' FAQ >http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > >-Original Message- >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Date: 23 January 2003 16:31 > > > >Arup, > > I see your point and agree that the DDL should be an autonomous tx. > >Perhaps an enhancement request is in order? Since Oracle has the >autonomous > >tx code, integrating into the kernel should be consi
DDLs -- was Re: simple question on DDL
Take your first example : insert into t1 values (1); drop table t1; -- how to deal with self-deadlock ? insert into t1 values (2); commit; Why does Oracle HAVE to commit when the DROP TABLE is issued ? What if the INSERT had been issued by another session ? Would the DROP TABLE go through in this session ? The "self-deadlock" could be handled as an Error -- a Transaction error, with a message like "cannot drop table when transaction is active in current session". A duhveloper who has written a very long-winded .SQL file or procedure and tries to drop a Table when he has an active transaction should be caught and "errored" -- he shouldn't be allowed to drop his own table, he has made a logical error. Take the second example : insert into t1 values (1); drop table t2; -- how to deal with lock by other user ? insert into t1 values (2); commit; If the DROP TABLE may have to wait for another user who has a lock on T2, why should the INSERT INTO T1 be committed ? Our user hasn't completed his transaction yet. It all depends on what you mean by a "Transaction". The way Oracle has written DDLs, a "Transaction" ends and is committed when the next DDL is issued. But that may not be a logical transaction in the real world. My point is not that the DROP TABLE should be "roll-backable". The DROP TABLE itself must always commit it's own statement. --- it's own statement. That is to ensure that a third user does not see inconsistency when running a transaction accessing T1. But why should the DROP TABLE explicitly commit the previous statement ? In your example, you have shown that an inconsistency may arise when a DROP is issued on a table with an outstanding transaction. But take the case where : INSERT INTO T10 values ('a'); -- now I want to, for the heck of it, or bec'ose I'm a bad programmer ... DROP TABLE XYZ ; -- oops, my INSERT INTO T10 should be rolled back -- but the Drop XYZ is independent ROLLBACK; Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT INTO T10. I didn't want that ! It would have been better if this DROP TABLE XYZ was an Autonomous Transaction. But in the first example, the DROP TABLE T1 should return an error. So, the Oracle Kernel must have some more complexity to see if there's any outstanding transaction in the current session [it already checks for other sessions because they hold TM locks on the Table !] are pending against the same table being dropped. Hemant At 09:50 AM 23-01-03 -0800, you wrote: One question to ask is whether whether all DDL use the same strategy. Similarly, if you have multiple code paths for "do a ddl call" how much more risk of error do you introduce to the kernel. Finally how do you get a consistent error response to the end user if the error condition of apparently identical events can fail in extremely different ways. Consider the complexities of finding a consistent kernel level approach to: insert into t1 values (1); drop table t1; -- how to deal with self-deadlock ? insert into t1 values (2); commit; insert into t1 values (1); drop table t2; -- how to deal with lock by other user ? insert into t1 values (2); commit; Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 23 January 2003 16:31 >Arup, > I see your point and agree that the DDL should be an autonomous tx. >Perhaps an enhancement request is in order? Since Oracle has the autonomous >tx code, integrating into the kernel should be considered...perhaps for >Oracle 38i? > In the absence of that change, I must disagree and say that ddl must >issue a commit. If you look at a combination of your example and mine, the >problem of waiting transactions still exists. In order to create a table, >space must be allocated. Again, if fet$ contains only 1 row for the >particular file where the table is to be created, there is a potential for a >serious locking problem. > >Dan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale
Re: simple question on DDL
One question to ask is whether whether all DDL use the same strategy. Similarly, if you have multiple code paths for "do a ddl call" how much more risk of error do you introduce to the kernel. Finally how do you get a consistent error response to the end user if the error condition of apparently identical events can fail in extremely different ways. Consider the complexities of finding a consistent kernel level approach to: insert into t1 values (1); drop table t1; -- how to deal with self-deadlock ? insert into t1 values (2); commit; insert into t1 values (1); drop table t2; -- how to deal with lock by other user ? insert into t1 values (2); commit; Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 23 January 2003 16:31 >Arup, > I see your point and agree that the DDL should be an autonomous tx. >Perhaps an enhancement request is in order? Since Oracle has the autonomous >tx code, integrating into the kernel should be considered...perhaps for >Oracle 38i? > In the absence of that change, I must disagree and say that ddl must >issue a commit. If you look at a combination of your example and mine, the >problem of waiting transactions still exists. In order to create a table, >space must be allocated. Again, if fet$ contains only 1 row for the >particular file where the table is to be created, there is a potential for a >serious locking problem. > >Dan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question on DDL
Title: RE: simple question on DDL Set autotrace on ... and then do explain plan for You'll see. 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-From: Jeremy Pulcifer [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 23, 2003 12:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: simple question on DDL > From: Gogala, Mladen [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 8:45 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: simple question on DDL > > > Actually, DB2 (pardon my French) doesn't issue a commit > after a DDL. I'm not aware of any standards specifying > the presence or absence of a "commit" after a DDL. I know > that, for instance, "EXPLAIN PLAN" can be rolled back. > Huh? Explain plan is DDL? 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: simple question on DDL
Actually, DB2 (pardon my French) doesn't issue a commit after a DDL. I'm not aware of any standards specifying the presence or absence of a "commit" after a DDL. I know that, for instance, "EXPLAIN PLAN" can be rolled back. > -Original Message- > From: Fink, Dan [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 10:05 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: simple question on DDL > > > Arup, > I see your point and agree that the DDL should be an > autonomous tx. > Perhaps an enhancement request is in order? Since Oracle has > the autonomous > tx code, integrating into the kernel should be > considered...perhaps for > Oracle 38i? > In the absence of that change, I must disagree and say > that ddl must > issue a commit. If you look at a combination of your example > and mine, the > problem of waiting transactions still exists. In order to > create a table, > space must be allocated. Again, if fet$ contains only 1 row for the > particular file where the table is to be created, there is a > potential for a > serious locking problem. > > Dan > > -Original Message- > Sent: Wednesday, January 22, 2003 7:14 PM > To: Multiple recipients of list ORACLE-L > > > Dan, > > If I may, essentially you are saying that changes to data > dictionary tables > have to be committed immediately regardless of the outcome of the > transaction. > > For instance in the following code, starting with an empty table t1 > > step 1: insert into table t1 values row1 > step 2: create table t2 > step 3: insert into table t1 values row2 > step 4: rollback > > At this point a select * from t1 will show only row1, since > the ddl create > table t2 has inserted a commit. However, the point is, my > transaction should > > have been from step 1 through step 4, not fromn step 3 > through 4. The DDL > broke my txn at step 2 and another transaction started from > there. The data > dictionary tables were updated and they should be committed; > but that commit > > could have been done via an "autonomous transaction", not in the same > transaction the user issued. > > The more I think about it, I see no point why a DDL should > insert a commit. > This is different from saying that DDL itself may issue a > commit to its > seprate transaction to update the catalog. Any thoughts on that? > > Arup > > >From: "Fink, Dan" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: simple question on DDL > >Date: Wed, 22 Jan 2003 14:18:57 -0800 > >MIME-Version: 1.0 > >Received: from newsfeed.cts.com ([209.68.248.164]) by > >mc1-f3.law16.hotmail.com with Microsoft > SMTPSVC(5.0.2195.5600); Wed, 22 Jan > > >2003 15:13:04 -0800 > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com > >(8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 > 15:11:42 -0800 (PST) > >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via > UUCP id 00537F3B; > >Wed, 22 Jan 2003 14:18:57 -0800 > >Message-ID: <[EMAIL PROTECTED]> > >X-Comment: Oracle RDBMS Community Forum > >X-Sender: "Fink, Dan" <[EMAIL PROTECTED]> > >Sender: [EMAIL PROTECTED] > >Errors-To: [EMAIL PROTECTED] > >Organization: Fat City Network Services, San Diego, California > >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce > A. Bergman > >Precedence: bulk > >Return-Path: [EMAIL PROTECTED] > >X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) > >FILETIME=[D0E4CCE0:01C2C26B] > > > >Don't forget that extent allocation also affects the extent > map for the > >segment and possibly the high water mark. The hwm can be set without > >allocating another extent and allocation of an extent may > not alter the hwm > >(if you manually allocate an extent). If I deallocate space > from an object, > >I will alter the rows in fet$ and uet$ but not update the > hwm. Make sense? > > > >As for the ATOMICITY of the transaction, this is usually > used to describe > >the changes to data of interest. I don't think it is used to > describe any > >underlying data dictionary changes. Thus the answer is Yes > (for 99% of the > >Oracle techies) and No (for the 1% of us who really like to > know exactly > >what is going on under the covers). > > > >Thanks for a great question, it brought up a subject that I had never > >thought about. Yee-Haw! I learned someting tod
RE: simple question on DDL
Title: RE: simple question on DDL > From: Gogala, Mladen [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 8:45 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: simple question on DDL > > > Actually, DB2 (pardon my French) doesn't issue a commit > after a DDL. I'm not aware of any standards specifying > the presence or absence of a "commit" after a DDL. I know > that, for instance, "EXPLAIN PLAN" can be rolled back. > Huh? Explain plan is DDL?
RE: simple question on DDL
I tend to agree with the argument "that's the way they wrote it". If I remember correctly, DDL always commits the current transaction in the session, even back in Oracle5 [and earlier ?] days. Most likely they couldn't write autonomous transactions then. That "autonomous transactions" have been available to us since 8i doesn't mean that autonomous transactions may not have been possible within the kernel earlier. I always think that the SCN mechanism is like a sequence and has been around since before Oracle6 but sequences were available to us only in Oracle6. Hemant At 06:54 AM 23-01-03 -0800, you wrote: Well, then even Tom's vague on this one. "DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions". Well, then even an insert statement makes changes to DD objects. Ain't locks taken then? "DDL Starts by commiting". Why not declare a savepoint, and later, rollback to that savepoint? And theres another DBA friend, who speculates that the reason could be that DDL statements always take an exclusive lock on the underlying object. If that transaction is not ended implicitly, the locks would be waiting forever until the user intervenes. This could have serious consequences on the database, with a potential for deadlocks. Ahem. This by far, seems to be the most convincing answer to me. Ok. Maybe, thats just the way its coded. The point I have understood from this thread, is that a commit is performed to protect the data dictionary. And DML statements are not truly atomic, for while it maybe possible for an DML statement to roll back, there are some changes that are indeed committed. Thanks Raj "Deshpande, Kirti" rizon.com> cc: Sent by: Subject: RE: simple question on DDL [EMAIL PROTECTED] January 22, 2003 10:33 PM Please respond to ORACLE-L Dan addressed this very well in his earlier post... Here is what Tom Kyte says in his book (Expert one-on-one Oracle): "DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions". "DDL locks are held for the duration of the DDL statement, and are released immediately afterwards. This is done, in effect, by always wrapping DDL statements in implicit commits (or commit/rollback pair). It is for this reason that DDL always commits in Oracle.". "So, DDL will always commit, even if it is unsuccessful. DDL starts by committing - be aware of this. It commits first so that if it has to rollback, it will not roll back your transaction. If you execute DDL, it'll make permanent any outstanding work you have performed, even if the DDL is not successful.". Refer to page 119... - Kirti -Original Message- Sent: Wednesday, January 22, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Dan, If I may, essentially you are saying that changes to data dictionary tables have to be committed immediately regardless of the outcome of the transaction. For instance in the following code, starting with an empty table t1 step 1: insert into table t1 values row1 step 2: create table t2 step 3: insert into table t1 values row2 step 4: rollback At this point a select * from t1 will show only row1, since the ddl create table t2 has inserted a commit. However, the point is, my transaction should have been from step 1 through step 4, not fromn step 3 through 4. The DDL broke my txn at step 2 and another transaction started from there. The data dictionary tables were updated and they should be committed; but that commit could have been done via an "autonomous transaction", not in the same transaction the user issued. The more I think about it, I see no point why a DDL should insert a commit. This is different from saying that DDL itself may issue a commit to its seprate transaction to update the catalog. Any thoughts on that? Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the offic
RE: simple question on DDL
Well, then even Tom's vague on this one. "DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions". Well, then even an insert statement makes changes to DD objects. Ain't locks taken then? "DDL Starts by commiting". Why not declare a savepoint, and later, rollback to that savepoint? And theres another DBA friend, who speculates that the reason could be that DDL statements always take an exclusive lock on the underlying object. If that transaction is not ended implicitly, the locks would be waiting forever until the user intervenes. This could have serious consequences on the database, with a potential for deadlocks. Ahem. This by far, seems to be the most convincing answer to me. Ok. Maybe, thats just the way its coded. The point I have understood from this thread, is that a commit is performed to protect the data dictionary. And DML statements are not truly atomic, for while it maybe possible for an DML statement to roll back, there are some changes that are indeed committed. Thanks Raj "Deshpande, Kirti" rizon.com> cc: Sent by: Subject: RE: simple question on DDL [EMAIL PROTECTED] January 22, 2003 10:33 PM Please respond to ORACLE-L Dan addressed this very well in his earlier post... Here is what Tom Kyte says in his book (Expert one-on-one Oracle): "DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions". "DDL locks are held for the duration of the DDL statement, and are released immediately afterwards. This is done, in effect, by always wrapping DDL statements in implicit commits (or commit/rollback pair). It is for this reason that DDL always commits in Oracle.". "So, DDL will always commit, even if it is unsuccessful. DDL starts by committing - be aware of this. It commits first so that if it has to rollback, it will not roll back your transaction. If you execute DDL, it'll make permanent any outstanding work you have performed, even if the DDL is not successful.". Refer to page 119... - Kirti -Original Message- Sent: Wednesday, January 22, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Dan, If I may, essentially you are saying that changes to data dictionary tables have to be committed immediately regardless of the outcome of the transaction. For instance in the following code, starting with an empty table t1 step 1: insert into table t1 values row1 step 2: create table t2 step 3: insert into table t1 values row2 step 4: rollback At this point a select * from t1 will show only row1, since the ddl create table t2 has inserted a commit. However, the point is, my transaction should have been from step 1 through step 4, not fromn step 3 through 4. The DDL broke my txn at step 2 and another transaction started from there. The data dictionary tables were updated and they should be committed; but that commit could have been done via an "autonomous transaction", not in the same transaction the user issued. The more I think about it, I see no point why a DDL should insert a commit. This is different from saying that DDL itself may issue a commit to its seprate transaction to update the catalog. Any thoughts on that? Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
RE: simple question on DDL
Arup, I see your point and agree that the DDL should be an autonomous tx. Perhaps an enhancement request is in order? Since Oracle has the autonomous tx code, integrating into the kernel should be considered...perhaps for Oracle 38i? In the absence of that change, I must disagree and say that ddl must issue a commit. If you look at a combination of your example and mine, the problem of waiting transactions still exists. In order to create a table, space must be allocated. Again, if fet$ contains only 1 row for the particular file where the table is to be created, there is a potential for a serious locking problem. Dan -Original Message- Sent: Wednesday, January 22, 2003 7:14 PM To: Multiple recipients of list ORACLE-L Dan, If I may, essentially you are saying that changes to data dictionary tables have to be committed immediately regardless of the outcome of the transaction. For instance in the following code, starting with an empty table t1 step 1: insert into table t1 values row1 step 2: create table t2 step 3: insert into table t1 values row2 step 4: rollback At this point a select * from t1 will show only row1, since the ddl create table t2 has inserted a commit. However, the point is, my transaction should have been from step 1 through step 4, not fromn step 3 through 4. The DDL broke my txn at step 2 and another transaction started from there. The data dictionary tables were updated and they should be committed; but that commit could have been done via an "autonomous transaction", not in the same transaction the user issued. The more I think about it, I see no point why a DDL should insert a commit. This is different from saying that DDL itself may issue a commit to its seprate transaction to update the catalog. Any thoughts on that? Arup >From: "Fink, Dan" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: simple question on DDL >Date: Wed, 22 Jan 2003 14:18:57 -0800 >MIME-Version: 1.0 >Received: from newsfeed.cts.com ([209.68.248.164]) by >mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, 22 Jan >2003 15:13:04 -0800 >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com >(8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 15:11:42 -0800 (PST) >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00537F3B; >Wed, 22 Jan 2003 14:18:57 -0800 >Message-ID: <[EMAIL PROTECTED]> >X-Comment: Oracle RDBMS Community Forum >X-Sender: "Fink, Dan" <[EMAIL PROTECTED]> >Sender: [EMAIL PROTECTED] >Errors-To: [EMAIL PROTECTED] >Organization: Fat City Network Services, San Diego, California >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman >Precedence: bulk >Return-Path: [EMAIL PROTECTED] >X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) >FILETIME=[D0E4CCE0:01C2C26B] > >Don't forget that extent allocation also affects the extent map for the >segment and possibly the high water mark. The hwm can be set without >allocating another extent and allocation of an extent may not alter the hwm >(if you manually allocate an extent). If I deallocate space from an object, >I will alter the rows in fet$ and uet$ but not update the hwm. Make sense? > >As for the ATOMICITY of the transaction, this is usually used to describe >the changes to data of interest. I don't think it is used to describe any >underlying data dictionary changes. Thus the answer is Yes (for 99% of the >Oracle techies) and No (for the 1% of us who really like to know exactly >what is going on under the covers). > >Thanks for a great question, it brought up a subject that I had never >thought about. Yee-Haw! I learned someting today! > >Cheers, >Dan > >-Original Message- >Sent: Wednesday, January 22, 2003 2:04 PM >To: Multiple recipients of list ORACLE-L > > > >Thanks Dan. The gist of your response was that all changes to the data >dictionary are immediately commited. Seems to make sense to me. Maybe, >thats one reason why one cannot free space below the high water mark. Coz >changes to UET$ has been committed, even though the data was rolled back. > >I sent an email to one of my senior DBA friends, posing the same question, >and he replied with a one liner "To make the transaction as ATOMIC as >possible - They either run completely, or not at all". Now, does that mean >the Insert, update and delete statements are not ATOMIC? For on a rollback, >changes to the data dictionary are commited, whereas the data is rolled >back. > >Thanks >Raj > > > > > > > "Fink, Dan" > > list >ORACLE-L <[EMAIL PROTECTED]> &g
Re: simple question on DDL
It could also have something to do with the status changes of objects that may be affected by the DDL, though I am speculating here. Jared On Thursday 23 January 2003 02:58, you wrote: > remember this "functionality" of committing before a DDL statement has > been around from the beginning. Autonomous transactions have not. It > may simply be a case of Oracle not getting around to adding that change > to the kernel code. > > Or, as Kirti quoted from Tom Kyte, that might just be the way they want > it to work. > > --- Arup Nanda <[EMAIL PROTECTED]> wrote: > > Dan, > > > > If I may, essentially you are saying that changes to data dictionary > > tables > > have to be committed immediately regardless of the outcome of the > > transaction. > > > > For instance in the following code, starting with an empty table t1 > > > > step 1: insert into table t1 values row1 > > step 2: create table t2 > > step 3: insert into table t1 values row2 > > step 4: rollback > > > > At this point a select * from t1 will show only row1, since the ddl > > create > > table t2 has inserted a commit. However, the point is, my transaction > > should > > have been from step 1 through step 4, not fromn step 3 through 4. The > > DDL > > broke my txn at step 2 and another transaction started from there. > > The data > > dictionary tables were updated and they should be committed; but that > > commit > > could have been done via an "autonomous transaction", not in the same > > > > transaction the user issued. > > > > The more I think about it, I see no point why a DDL should insert a > > commit. > > This is different from saying that DDL itself may issue a commit to > > its > > seprate transaction to update the catalog. Any thoughts on that? > > > > Arup > > > > >From: "Fink, Dan" <[EMAIL PROTECTED]> > > >Reply-To: [EMAIL PROTECTED] > > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > >Subject: RE: simple question on DDL > > >Date: Wed, 22 Jan 2003 14:18:57 -0800 > > >MIME-Version: 1.0 > > >Received: from newsfeed.cts.com ([209.68.248.164]) by > > >mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, > > > > 22 Jan > > > > >2003 15:13:04 -0800 > > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com > > >(8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 15:11:42 -0800 > > > > (PST) > > > > >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id > > > > 00537F3B; > > > > >Wed, 22 Jan 2003 14:18:57 -0800 > > >Message-ID: <[EMAIL PROTECTED]> > > >X-Comment: Oracle RDBMS Community Forum > > >X-Sender: "Fink, Dan" <[EMAIL PROTECTED]> > > >Sender: [EMAIL PROTECTED] > > >Errors-To: [EMAIL PROTECTED] > > >Organization: Fat City Network Services, San Diego, California > > >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. > > > > Bergman > > > > >Precedence: bulk > > >Return-Path: [EMAIL PROTECTED] > > >X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) > > >FILETIME=[D0E4CCE0:01C2C26B] > > > > > >Don't forget that extent allocation also affects the extent map for > > > > the > > > > >segment and possibly the high water mark. The hwm can be set without > > >allocating another extent and allocation of an extent may not alter > > > > the hwm > > > > >(if you manually allocate an extent). If I deallocate space from an > > > > object, > > > > >I will alter the rows in fet$ and uet$ but not update the hwm. Make > > > > sense? > > > > >As for the ATOMICITY of the transaction, this is usually used to > > > > describe > > > > >the changes to data of interest. I don't think it is used to > > > > describe any > > > > >underlying data dictionary changes. Thus the answer is Yes (for 99% > > > > of the > > > > >Oracle techies) and No (for the 1% of us who really like to know > > > > exactly > > > > >what is going on under the covers). > > > > > >Thanks for a great question, it brought up a subject that I had > > > > never > > > > >thought about. Yee-Haw! I learned someting today! > > > > > >Cheers, > > >Dan > > > > > >-Original Message- > &g
Re: simple question on DDL
le - They either run completely, or not at all". Now, does that mean the Insert, update and delete statements are not ATOMIC? For on a rollback, changes to the data dictionary are commited, whereas the data is rolled back. Thanks Raj "Fink, Dan" recipients of list ORACLE-L <[EMAIL PROTECTED]> .com>cc: Sent by: Subject: RE: simple question on DDL root@fatcity. com January 22, 2003 02:16 PM Please respond to ORACLE-L Take the case of an insert (we'll call tx1), where space allocation is required. As you insert records, the table allocated additional extents (updating fet$ (free extent table) and uet$ (used extent table) in the data dictionary). These updates to the data dictionary are implicitly committed, even if you issue an explicit rollback for the insert statement. Imagine if the dd changes are not immediately committed. Let's say another tx (we'll call tx2) needs to allocate an extent in the same datafile. If fet$ contains only a single row for the file requested, then tx1 will have an exclusive lock on the row. tx2 needs to also lock the row exclusively in order to update it. Thus, tx2 would wait until tx1 has completed and released the lock. In the meantime, any transaction that needs to allocate a new extent in that file will have to wait...and wait...and wait. A different strategy is to commit the changes to fet$ and uet$ immediately. Then the next tx can access the row and grab space. While this could result in an overallocation of space if the tx is rolled back, it does not block other txs. If space was allocated to an object, and the tx failed, there is a strong probability that this space will be used at some point in the future. It seems that the tradeoff here is that the access to the data dictionary === message truncated === __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
RE: simple question on DDL
remember this "functionality" of committing before a DDL statement has been around from the beginning. Autonomous transactions have not. It may simply be a case of Oracle not getting around to adding that change to the kernel code. Or, as Kirti quoted from Tom Kyte, that might just be the way they want it to work. --- Arup Nanda <[EMAIL PROTECTED]> wrote: > Dan, > > If I may, essentially you are saying that changes to data dictionary > tables > have to be committed immediately regardless of the outcome of the > transaction. > > For instance in the following code, starting with an empty table t1 > > step 1: insert into table t1 values row1 > step 2: create table t2 > step 3: insert into table t1 values row2 > step 4: rollback > > At this point a select * from t1 will show only row1, since the ddl > create > table t2 has inserted a commit. However, the point is, my transaction > should > have been from step 1 through step 4, not fromn step 3 through 4. The > DDL > broke my txn at step 2 and another transaction started from there. > The data > dictionary tables were updated and they should be committed; but that > commit > could have been done via an "autonomous transaction", not in the same > > transaction the user issued. > > The more I think about it, I see no point why a DDL should insert a > commit. > This is different from saying that DDL itself may issue a commit to > its > seprate transaction to update the catalog. Any thoughts on that? > > Arup > > >From: "Fink, Dan" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: simple question on DDL > >Date: Wed, 22 Jan 2003 14:18:57 -0800 > >MIME-Version: 1.0 > >Received: from newsfeed.cts.com ([209.68.248.164]) by > >mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, > 22 Jan > >2003 15:13:04 -0800 > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com > >(8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 15:11:42 -0800 > (PST) > >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id > 00537F3B; > >Wed, 22 Jan 2003 14:18:57 -0800 > >Message-ID: <[EMAIL PROTECTED]> > >X-Comment: Oracle RDBMS Community Forum > >X-Sender: "Fink, Dan" <[EMAIL PROTECTED]> > >Sender: [EMAIL PROTECTED] > >Errors-To: [EMAIL PROTECTED] > >Organization: Fat City Network Services, San Diego, California > >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. > Bergman > >Precedence: bulk > >Return-Path: [EMAIL PROTECTED] > >X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) > >FILETIME=[D0E4CCE0:01C2C26B] > > > >Don't forget that extent allocation also affects the extent map for > the > >segment and possibly the high water mark. The hwm can be set without > >allocating another extent and allocation of an extent may not alter > the hwm > >(if you manually allocate an extent). If I deallocate space from an > object, > >I will alter the rows in fet$ and uet$ but not update the hwm. Make > sense? > > > >As for the ATOMICITY of the transaction, this is usually used to > describe > >the changes to data of interest. I don't think it is used to > describe any > >underlying data dictionary changes. Thus the answer is Yes (for 99% > of the > >Oracle techies) and No (for the 1% of us who really like to know > exactly > >what is going on under the covers). > > > >Thanks for a great question, it brought up a subject that I had > never > >thought about. Yee-Haw! I learned someting today! > > > >Cheers, > >Dan > > > >-Original Message- > >Sent: Wednesday, January 22, 2003 2:04 PM > >To: Multiple recipients of list ORACLE-L > > > > > > > >Thanks Dan. The gist of your response was that all changes to the > data > >dictionary are immediately commited. Seems to make sense to me. > Maybe, > >thats one reason why one cannot free space below the high water > mark. Coz > >changes to UET$ has been committed, even though the data was rolled > back. > > > >I sent an email to one of my senior DBA friends, posing the same > question, > >and he replied with a one liner "To make the transaction as ATOMIC > as > >possible - They either run completely, or not at all". Now, does > that mean > >the Insert, update and delete statements are not ATOMIC? For on a > rollback, > >changes to the data dictionary are commited, whereas the d
Re: simple question on DDL
SORRY - this was the wrong list. the other pertinent list is one about controlling machines via a DDL protocol and a demon called the alike. kr mr >>> [EMAIL PROTECTED] 01/22/03 16:49 PM >>> hi what's your exact question? you mean autocommit like in database applications? what tool are you using to observe or redard to this phenomenon? the erd-demon has to send some info via rs232 to make the amplifier -called booster - work. no info implies no current on the tracks. kind of answwer you want? kr mr >>> [EMAIL PROTECTED] 01/22/03 13:03 PM >>> Hi friends Why DDL statements performs auto commit ? What is the exact reason behind that one? Anyone can share his/her opinions!! Thanks & regards BanarasiBabu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BanarasiBabu Tippa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Markus Reger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Markus Reger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question on DDL
Dan addressed this very well in his earlier post... Here is what Tom Kyte says in his book (Expert one-on-one Oracle): "DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions". "DDL locks are held for the duration of the DDL statement, and are released immediately afterwards. This is done, in effect, by always wrapping DDL statements in implicit commits (or commit/rollback pair). It is for this reason that DDL always commits in Oracle.". "So, DDL will always commit, even if it is unsuccessful. DDL starts by committing - be aware of this. It commits first so that if it has to rollback, it will not roll back your transaction. If you execute DDL, it'll make permanent any outstanding work you have performed, even if the DDL is not successful.". Refer to page 119... - Kirti -Original Message- Sent: Wednesday, January 22, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Dan, If I may, essentially you are saying that changes to data dictionary tables have to be committed immediately regardless of the outcome of the transaction. For instance in the following code, starting with an empty table t1 step 1: insert into table t1 values row1 step 2: create table t2 step 3: insert into table t1 values row2 step 4: rollback At this point a select * from t1 will show only row1, since the ddl create table t2 has inserted a commit. However, the point is, my transaction should have been from step 1 through step 4, not fromn step 3 through 4. The DDL broke my txn at step 2 and another transaction started from there. The data dictionary tables were updated and they should be committed; but that commit could have been done via an "autonomous transaction", not in the same transaction the user issued. The more I think about it, I see no point why a DDL should insert a commit. This is different from saying that DDL itself may issue a commit to its seprate transaction to update the catalog. Any thoughts on that? Arup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question on DDL
Dan, If I may, essentially you are saying that changes to data dictionary tables have to be committed immediately regardless of the outcome of the transaction. For instance in the following code, starting with an empty table t1 step 1: insert into table t1 values row1 step 2: create table t2 step 3: insert into table t1 values row2 step 4: rollback At this point a select * from t1 will show only row1, since the ddl create table t2 has inserted a commit. However, the point is, my transaction should have been from step 1 through step 4, not fromn step 3 through 4. The DDL broke my txn at step 2 and another transaction started from there. The data dictionary tables were updated and they should be committed; but that commit could have been done via an "autonomous transaction", not in the same transaction the user issued. The more I think about it, I see no point why a DDL should insert a commit. This is different from saying that DDL itself may issue a commit to its seprate transaction to update the catalog. Any thoughts on that? Arup From: "Fink, Dan" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: simple question on DDL Date: Wed, 22 Jan 2003 14:18:57 -0800 MIME-Version: 1.0 Received: from newsfeed.cts.com ([209.68.248.164]) by mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, 22 Jan 2003 15:13:04 -0800 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 15:11:42 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00537F3B; Wed, 22 Jan 2003 14:18:57 -0800 Message-ID: <[EMAIL PROTECTED]> X-Comment: Oracle RDBMS Community Forum X-Sender: "Fink, Dan" <[EMAIL PROTECTED]> Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) FILETIME=[D0E4CCE0:01C2C26B] Don't forget that extent allocation also affects the extent map for the segment and possibly the high water mark. The hwm can be set without allocating another extent and allocation of an extent may not alter the hwm (if you manually allocate an extent). If I deallocate space from an object, I will alter the rows in fet$ and uet$ but not update the hwm. Make sense? As for the ATOMICITY of the transaction, this is usually used to describe the changes to data of interest. I don't think it is used to describe any underlying data dictionary changes. Thus the answer is Yes (for 99% of the Oracle techies) and No (for the 1% of us who really like to know exactly what is going on under the covers). Thanks for a great question, it brought up a subject that I had never thought about. Yee-Haw! I learned someting today! Cheers, Dan -Original Message- Sent: Wednesday, January 22, 2003 2:04 PM To: Multiple recipients of list ORACLE-L Thanks Dan. The gist of your response was that all changes to the data dictionary are immediately commited. Seems to make sense to me. Maybe, thats one reason why one cannot free space below the high water mark. Coz changes to UET$ has been committed, even though the data was rolled back. I sent an email to one of my senior DBA friends, posing the same question, and he replied with a one liner "To make the transaction as ATOMIC as possible - They either run completely, or not at all". Now, does that mean the Insert, update and delete statements are not ATOMIC? For on a rollback, changes to the data dictionary are commited, whereas the data is rolled back. Thanks Raj "Fink, Dan" ORACLE-L <[EMAIL PROTECTED]> .com> cc: Sent by: Subject: RE: simple question on DDL root@fatcity. com January 22, 2003 02:16 PM Please respond to ORACLE-L Take the case of an insert (we'll call tx1), where space allocation is required. As you insert records, the table allocated additional extents (updating fet$ (free extent table) and uet$ (used extent table) in the data dictionary). These updates to the data dictionary are implicitly committed, even if you issue an explicit rollback for the insert statement. Imagine if the dd changes are not immediately committed. Let's say another tx (we'll call tx2) needs to allocate an extent in the same datafile. If fet$ contains only a single row for the file requested, then tx1 will have an exclusive lock on the row. tx2 needs to also lock the row exclusively in order to update it. Thus, tx2 would
RE: simple question on DDL
Don't forget that extent allocation also affects the extent map for the segment and possibly the high water mark. The hwm can be set without allocating another extent and allocation of an extent may not alter the hwm (if you manually allocate an extent). If I deallocate space from an object, I will alter the rows in fet$ and uet$ but not update the hwm. Make sense? As for the ATOMICITY of the transaction, this is usually used to describe the changes to data of interest. I don't think it is used to describe any underlying data dictionary changes. Thus the answer is Yes (for 99% of the Oracle techies) and No (for the 1% of us who really like to know exactly what is going on under the covers). Thanks for a great question, it brought up a subject that I had never thought about. Yee-Haw! I learned someting today! Cheers, Dan -Original Message- Sent: Wednesday, January 22, 2003 2:04 PM To: Multiple recipients of list ORACLE-L Thanks Dan. The gist of your response was that all changes to the data dictionary are immediately commited. Seems to make sense to me. Maybe, thats one reason why one cannot free space below the high water mark. Coz changes to UET$ has been committed, even though the data was rolled back. I sent an email to one of my senior DBA friends, posing the same question, and he replied with a one liner "To make the transaction as ATOMIC as possible - They either run completely, or not at all". Now, does that mean the Insert, update and delete statements are not ATOMIC? For on a rollback, changes to the data dictionary are commited, whereas the data is rolled back. Thanks Raj "Fink, Dan" .com>cc: Sent by: Subject: RE: simple question on DDL root@fatcity. com January 22, 2003 02:16 PM Please respond to ORACLE-L Take the case of an insert (we'll call tx1), where space allocation is required. As you insert records, the table allocated additional extents (updating fet$ (free extent table) and uet$ (used extent table) in the data dictionary). These updates to the data dictionary are implicitly committed, even if you issue an explicit rollback for the insert statement. Imagine if the dd changes are not immediately committed. Let's say another tx (we'll call tx2) needs to allocate an extent in the same datafile. If fet$ contains only a single row for the file requested, then tx1 will have an exclusive lock on the row. tx2 needs to also lock the row exclusively in order to update it. Thus, tx2 would wait until tx1 has completed and released the lock. In the meantime, any transaction that needs to allocate a new extent in that file will have to wait...and wait...and wait. A different strategy is to commit the changes to fet$ and uet$ immediately. Then the next tx can access the row and grab space. While this could result in an overallocation of space if the tx is rolled back, it does not block other txs. If space was allocated to an object, and the tx failed, there is a strong probability that this space will be used at some point in the future. It seems that the tradeoff here is that the access to the data dictionary is kept to a minimum duration at the expense of periodic space wastage (initially). As for the other data dictionary tables, it may be a case of read consistency conflicts. If a long running transaction needs to access a table definition, but a previous transaction has updated the table definition, but not committed, which table definition do you use? There are some issues with definitions not being the same at the start of a transaction and at a later point. As I recall, Oracle terminates the transaction if object definitions change while the transaction is running. All in all, it makes sense (at least to me) that changes to the data dictionary are immediately committed. Otherwise, the performance and integrity of the system would be comprimised. Dan Fink -Original Message- Sent: Wednesday, January 22, 2003 10:01 AM To: Multiple recipients of list ORACLE-L That raises another doubt. For an simple insert statement, could also update the UET$ or FET$ tables? So, if the purpose was to preserve all changes to the data dictionary, What's different between OBJ$, COL$ and these space management tables? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail
RE: simple question on DDL
Thanks Dan. The gist of your response was that all changes to the data dictionary are immediately commited. Seems to make sense to me. Maybe, thats one reason why one cannot free space below the high water mark. Coz changes to UET$ has been committed, even though the data was rolled back. I sent an email to one of my senior DBA friends, posing the same question, and he replied with a one liner "To make the transaction as ATOMIC as possible - They either run completely, or not at all". Now, does that mean the Insert, update and delete statements are not ATOMIC? For on a rollback, changes to the data dictionary are commited, whereas the data is rolled back. Thanks Raj "Fink, Dan" .com>cc: Sent by: Subject: RE: simple question on DDL root@fatcity. com January 22, 2003 02:16 PM Please respond to ORACLE-L Take the case of an insert (we'll call tx1), where space allocation is required. As you insert records, the table allocated additional extents (updating fet$ (free extent table) and uet$ (used extent table) in the data dictionary). These updates to the data dictionary are implicitly committed, even if you issue an explicit rollback for the insert statement. Imagine if the dd changes are not immediately committed. Let's say another tx (we'll call tx2) needs to allocate an extent in the same datafile. If fet$ contains only a single row for the file requested, then tx1 will have an exclusive lock on the row. tx2 needs to also lock the row exclusively in order to update it. Thus, tx2 would wait until tx1 has completed and released the lock. In the meantime, any transaction that needs to allocate a new extent in that file will have to wait...and wait...and wait. A different strategy is to commit the changes to fet$ and uet$ immediately. Then the next tx can access the row and grab space. While this could result in an overallocation of space if the tx is rolled back, it does not block other txs. If space was allocated to an object, and the tx failed, there is a strong probability that this space will be used at some point in the future. It seems that the tradeoff here is that the access to the data dictionary is kept to a minimum duration at the expense of periodic space wastage (initially). As for the other data dictionary tables, it may be a case of read consistency conflicts. If a long running transaction needs to access a table definition, but a previous transaction has updated the table definition, but not committed, which table definition do you use? There are some issues with definitions not being the same at the start of a transaction and at a later point. As I recall, Oracle terminates the transaction if object definitions change while the transaction is running. All in all, it makes sense (at least to me) that changes to the data dictionary are immediately committed. Otherwise, the performance and integrity of the system would be comprimised. Dan Fink -Original Message- Sent: Wednesday, January 22, 2003 10:01 AM To: Multiple recipients of list ORACLE-L That raises another doubt. For an simple insert statement, could also update the UET$ or FET$ tables? So, if the purpose was to preserve all changes to the data dictionary, What's different between OBJ$, COL$ and these space management tables? Thanks Raj
Re: simple question on DDL
Very good case described. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 2:16 PM > Take the case of an insert (we'll call tx1), where space allocation is > required. As you insert records, the table allocated additional extents > (updating fet$ (free extent table) and uet$ (used extent table) in the data > dictionary). These updates to the data dictionary are implicitly committed, > even if you issue an explicit rollback for the insert statement. Imagine if > the dd changes are not immediately committed. Let's say another tx (we'll > call tx2) needs to allocate an extent in the same datafile. If fet$ > contains only a single row for the file requested, then tx1 will have an > exclusive lock on the row. tx2 needs to also lock the row exclusively in > order to update it. Thus, tx2 would wait until tx1 has completed and > released the lock. In the meantime, any transaction that needs to allocate a > new extent in that file will have to wait...and wait...and wait. > > A different strategy is to commit the changes to fet$ and uet$ immediately. > Then the next tx can access the row and grab space. While this could result > in an overallocation of space if the tx is rolled back, it does not block > other txs. If space was allocated to an object, and the tx failed, there is > a strong probability that this space will be used at some point in the > future. > > It seems that the tradeoff here is that the access to the data dictionary is > kept to a minimum duration at the expense of periodic space wastage > (initially). > > As for the other data dictionary tables, it may be a case of read > consistency conflicts. If a long running transaction needs to access a table > definition, but a previous transaction has updated the table definition, but > not committed, which table definition do you use? There are some issues with > definitions not being the same at the start of a transaction and at a later > point. As I recall, Oracle terminates the transaction if object definitions > change while the transaction is running. > > All in all, it makes sense (at least to me) that changes to the data > dictionary are immediately committed. Otherwise, the performance and > integrity of the system would be comprimised. > > Dan Fink > > -Original Message- > Sent: Wednesday, January 22, 2003 10:01 AM > To: Multiple recipients of list ORACLE-L > > > > That raises another doubt. For an simple insert statement, could also > update the UET$ or FET$ tables? So, if the purpose was to preserve all > changes to the data dictionary, What's different between OBJ$, COL$ and > these space management tables? > > Thanks > Raj > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Fink, Dan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question on DDL
Take the case of an insert (we'll call tx1), where space allocation is required. As you insert records, the table allocated additional extents (updating fet$ (free extent table) and uet$ (used extent table) in the data dictionary). These updates to the data dictionary are implicitly committed, even if you issue an explicit rollback for the insert statement. Imagine if the dd changes are not immediately committed. Let's say another tx (we'll call tx2) needs to allocate an extent in the same datafile. If fet$ contains only a single row for the file requested, then tx1 will have an exclusive lock on the row. tx2 needs to also lock the row exclusively in order to update it. Thus, tx2 would wait until tx1 has completed and released the lock. In the meantime, any transaction that needs to allocate a new extent in that file will have to wait...and wait...and wait. A different strategy is to commit the changes to fet$ and uet$ immediately. Then the next tx can access the row and grab space. While this could result in an overallocation of space if the tx is rolled back, it does not block other txs. If space was allocated to an object, and the tx failed, there is a strong probability that this space will be used at some point in the future. It seems that the tradeoff here is that the access to the data dictionary is kept to a minimum duration at the expense of periodic space wastage (initially). As for the other data dictionary tables, it may be a case of read consistency conflicts. If a long running transaction needs to access a table definition, but a previous transaction has updated the table definition, but not committed, which table definition do you use? There are some issues with definitions not being the same at the start of a transaction and at a later point. As I recall, Oracle terminates the transaction if object definitions change while the transaction is running. All in all, it makes sense (at least to me) that changes to the data dictionary are immediately committed. Otherwise, the performance and integrity of the system would be comprimised. Dan Fink -Original Message- Sent: Wednesday, January 22, 2003 10:01 AM To: Multiple recipients of list ORACLE-L That raises another doubt. For an simple insert statement, could also update the UET$ or FET$ tables? So, if the purpose was to preserve all changes to the data dictionary, What's different between OBJ$, COL$ and these space management tables? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple question on DDL
Very good point, Raj. I didn't wonder just for the sake it; there was meat to it ;) Now that this has been raised, any ideas, anybody? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 12:01 PM > > That raises another doubt. For an simple insert statement, could also > update the UET$ or FET$ tables? So, if the purpose was to preserve all > changes to the data dictionary, What's different between OBJ$, COL$ and > these space management tables? > > Thanks > Raj > > > > > > "Arup Nanda" > > tmail.com> cc: > Sent by: Subject: Re: simple question on DDL > root@fatcity. > com > > > January 22, > 2003 09:58 AM > Please > respond to > ORACLE-L > > > > > > > My guess will be to preserve the changes to the data dictionary, which are > just Oracle tables anyway. When you create a table, a record goes to TAB$, > SEG$ and so on and so forth. Unless there is a commit these information is > not visible. > > But now that you asked, I wnder why the same objective couldn't have been > done through autonomous transactions. > > Arup > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, January 22, 2003 6:33 AM > > > > Hi friends > > > > Why DDL statements performs auto commit ? What is the exact reason behind > > that one? > > Anyone can share his/her opinions!! > > > > Thanks & regards > > BanarasiBabu > > -- > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple question on DDL
Consistency is the key too imagine what would happen if I dropped a column or changed it's definition, while a SQL statement or PL/SQL package was executing. The data that was updated before the change may well be very different in nature than the data after the change Borrowing from Ghostbusters if I may: Dr. DBA: This database is about to face a disaster of biblical proportions. Hapless Manager: What do you mean, "biblical?" Sr. DBA Lead: We mean real wrath-of-God type stuff. Plagues, darkness-- Another DBA: The dead rising from the grave! Sr. DBA: Forty years of darkness! Earthquakes, volcanoes-- Dr. DBA: Riots in the streets, dogs and cats living together, mass hysteria Manager: So, it's Ok to cut the DBA budget then? RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (it's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Wednesday, January 22, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Well look at it this way, besides creating/modifying/dropping the object that you want your also performing a number of updated/inserts/deletes from the data dictionary. Those data dictionary tables are just that, tables. Now imagine what a mess would be created if you performed a DDL statement and then rolled back the data dictionary updates? These MUST be completed as a single autonomous transaction, so Oracle does you a favor and performs an implicit commit. Same thing with any other DBMS I've been associated with. Dick Goulet Reply Separator Author: BanarasiBabu Tippa <[EMAIL PROTECTED]> Date: 1/22/2003 3:33 AM Hi friends Why DDL statements performs auto commit ? What is the exact reason behind that one? Anyone can share his/her opinions!! Thanks & regards BanarasiBabu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BanarasiBabu Tippa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple question on DDL
That raises another doubt. For an simple insert statement, could also update the UET$ or FET$ tables? So, if the purpose was to preserve all changes to the data dictionary, What's different between OBJ$, COL$ and these space management tables? Thanks Raj "Arup Nanda" tmail.com> cc: Sent by: Subject: Re: simple question on DDL root@fatcity. com January 22, 2003 09:58 AM Please respond to ORACLE-L My guess will be to preserve the changes to the data dictionary, which are just Oracle tables anyway. When you create a table, a record goes to TAB$, SEG$ and so on and so forth. Unless there is a commit these information is not visible. But now that you asked, I wnder why the same objective couldn't have been done through autonomous transactions. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 6:33 AM > Hi friends > > Why DDL statements performs auto commit ? What is the exact reason behind > that one? > Anyone can share his/her opinions!! > > Thanks & regards > BanarasiBabu > -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple question on DDL
My guess will be to preserve the changes to the data dictionary, which are just Oracle tables anyway. When you create a table, a record goes to TAB$, SEG$ and so on and so forth. Unless there is a commit these information is not visible. But now that you asked, I wnder why the same objective couldn't have been done through autonomous transactions. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 6:33 AM > Hi friends > > Why DDL statements performs auto commit ? What is the exact reason behind > that one? > Anyone can share his/her opinions!! > > Thanks & regards > BanarasiBabu > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: BanarasiBabu Tippa > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple question on DDL
hi what's your exact question? you mean autocommit like in database applications? what tool are you using to observe or redard to this phenomenon? the erd-demon has to send some info via rs232 to make the amplifier -called booster - work. no info implies no current on the tracks. kind of answwer you want? kr mr >>> [EMAIL PROTECTED] 01/22/03 13:03 PM >>> Hi friends Why DDL statements performs auto commit ? What is the exact reason behind that one? Anyone can share his/her opinions!! Thanks & regards BanarasiBabu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BanarasiBabu Tippa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Markus Reger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple ? question
Stephane, you are right, after I talked to the developer. She is totally confused by alias name, dbname, schema name. She is referring database name. joan Joan Hsieh wrote: > > Stephane, > > Well spoken- my bad. Our developer threw me this question when I was > just leaving from work. (I copied her email to the list) Now I am at > home and taking a chance to look it over again. I am not sure what she > asked for. My guess is she wants to pass a variable based on @xxx to > access the schema. I will make it clear tomorrow. I have been very busy > lately and we have too many projects going on at once. Please bear with > me if I didn't put my effort into it. > > Many thanks, > Joan > > Stephane Faroult wrote: > > > > Joan Hsieh wrote: > > > > > > Hi Listers, > > > > > > I am trying to find a way to know the schema name. Say, if I logged in > > > as jjin01@ngd. When I run a program, how can I get the schema name > > > which should be "ngd"? If I logged in as bkrasnof@pr, in this case, the > > > schema name will be "pr". > > > > > > Thanks in advance, > > > > > > Joan > > > > Joan, > > > > Beware of vocabulary: what is usually called the 'schema name' is the > > name of the Oracle account which owns the tables. > > > >select global_name from global_name; > > > > will return the name of the database. But beware that the name of the > > database has no link to your @ngd which is just your tns alias (locally > > defined very often). Most people give as tns alias the name of the SID > > which is in turn the name of the database, but this is just a common > > sense rule - nothing mandatory here. It's usually pretty clean on Unix, > > but under NT a lot of databases have 'ORCL' has their SID, are of course > > referred to under different names in TNSNAMES.ora and _may_ not have a > > significant global name (can be changed with 'alter database'). > > > > Great opportunity to set up company standards. > > > > -- > > 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 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Joan Hsieh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple ? question
Stephane, Well spoken- my bad. Our developer threw me this question when I was just leaving from work. (I copied her email to the list) Now I am at home and taking a chance to look it over again. I am not sure what she asked for. My guess is she wants to pass a variable based on @xxx to access the schema. I will make it clear tomorrow. I have been very busy lately and we have too many projects going on at once. Please bear with me if I didn't put my effort into it. Many thanks, Joan Stephane Faroult wrote: > > Joan Hsieh wrote: > > > > Hi Listers, > > > > I am trying to find a way to know the schema name. Say, if I logged in > > as jjin01@ngd. When I run a program, how can I get the schema name > > which should be "ngd"? If I logged in as bkrasnof@pr, in this case, the > > schema name will be "pr". > > > > Thanks in advance, > > > > Joan > > Joan, > > Beware of vocabulary: what is usually called the 'schema name' is the > name of the Oracle account which owns the tables. > >select global_name from global_name; > > will return the name of the database. But beware that the name of the > database has no link to your @ngd which is just your tns alias (locally > defined very often). Most people give as tns alias the name of the SID > which is in turn the name of the database, but this is just a common > sense rule - nothing mandatory here. It's usually pretty clean on Unix, > but under NT a lot of databases have 'ORCL' has their SID, are of course > referred to under different names in TNSNAMES.ora and _may_ not have a > significant global name (can be changed with 'alter database'). > > Great opportunity to set up company standards. > > -- > 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 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple ? question
Stephane, Well spoken- my bad. Our developer threw me this question when I was just leaving from work. ( I copied from her email) Now I am at home to look it over. I am not clear what she asked for. I think she wants to pass a variable based on the @xxx to access this schema. I will make it clear tomorrow. Tomorrow is going to be a long day for me. Too many projects going on at once and we have a very tight schedule. Thanks, Joan Quoting Stephane Faroult <[EMAIL PROTECTED]>: > Joan Hsieh wrote: > > > > Hi Listers, > > > > I am trying to find a way to know the schema name. Say, if I logged in > > as jjin01@ngd. When I run a program, how can I get the schema name > > which should be "ngd"? If I logged in as bkrasnof@pr, in this case, the > > schema name will be "pr". > > > > Thanks in advance, > > > > Joan > > Joan, > > Beware of vocabulary: what is usually called the 'schema name' is the > name of the Oracle account which owns the tables. > >select global_name from global_name; > > will return the name of the database. But beware that the name of the > database has no link to your @ngd which is just your tns alias (locally > defined very often). Most people give as tns alias the name of the SID > which is in turn the name of the database, but this is just a common > sense rule - nothing mandatory here. It's usually pretty clean on Unix, > but under NT a lot of databases have 'ORCL' has their SID, are of course > referred to under different names in TNSNAMES.ora and _may_ not have a > significant global name (can be changed with 'alter database'). > > Great opportunity to set up company standards. > > -- > 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 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple ? question
Joan Hsieh wrote: > > Hi Listers, > > I am trying to find a way to know the schema name. Say, if I logged in > as jjin01@ngd. When I run a program, how can I get the schema name > which should be "ngd"? If I logged in as bkrasnof@pr, in this case, the > schema name will be "pr". > > Thanks in advance, > > Joan Joan, Beware of vocabulary: what is usually called the 'schema name' is the name of the Oracle account which owns the tables. select global_name from global_name; will return the name of the database. But beware that the name of the database has no link to your @ngd which is just your tns alias (locally defined very often). Most people give as tns alias the name of the SID which is in turn the name of the database, but this is just a common sense rule - nothing mandatory here. It's usually pretty clean on Unix, but under NT a lot of databases have 'ORCL' has their SID, are of course referred to under different names in TNSNAMES.ora and _may_ not have a significant global name (can be changed with 'alter database'). Great opportunity to set up company standards. -- 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Simple question on logging..
Title: RE: Simple question on logging.. > -Original Message- > From: Peter R [mailto:[EMAIL PROTECTED]] > > How can I turn off logging for a table in Oracle7.3 database. Recoverable/unrecoverable is not a "persistent" attribute in 7.3.4 The "unrecoverable" option only applies to the create table but not to any subsequent operations. But starting with Oracle 8.0, the "nologging" option (which replaces the deprecated "unrecoverable") on a create table will also apply to subsequent operations like Direct SQL*Loader and direct-load inserts. If you don't specify logging/nologging at table creation time then, as expected, the setting will be taken from the tablespace defaults.
Re: Simple question on logging..
ALTER TABLE yourtable NOLOGGING - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, August 14, 2002 4:33 PM > > > Hi Gurus, > > How can I turn off logging for a table in Oracle7.3 database. Iam planning > to reorg thru ctas and want to use append hint for loading data. > > SQL> alter table tt unrecoverable; > alter table tt unrecoverable >* > ERROR at line 1: > ORA-01735: invalid ALTER TABLE option > > > SQL> alter table tt nologging; > alter table tt nologging >* > ERROR at line 1: > ORA-01735: invalid ALTER TABLE option > > TIA > peter. > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Peter R > 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: Ramon E. Estevez 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: SIMPLE QUESTION
Dave I checked this out and it depends on the oracle version. Editing the listener.log of 8.1.6.3.4 on NT failed as: Another process is using the file. The same for 9.0.1.3 on NT. Editing not problem. Marked the whole text except for the last day and deleted it and then I saved the file. Size down to 9k from about 10,000k. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, May 01, 2002 7:58 PM If you are running Oracle on Windoze, you have to stop the listener service, then rename the listener.log, then start the listener service again. I don't know how on Unix. Dave -Original Message- Sent: Wednesday, May 01, 2002 12:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Farnsworth, Dave 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: Yechiel Adar 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: SIMPLE QUESTION
If this is unix, do this... 1. backup the log if you need it 2. issue this command " > listener.log" (this will initialize the log) -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Feng, Jun 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: Wong, Bing 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: SIMPLE QUESTION - old answer
See my old posting at http://faqchest.dynhost.com/prgm/oracle-l/ora-01/ora-0105/ora-010521/ora01052411_15995.html Brian P. MacLean Oracle DBA, OCP8i Hamid Alavi ovadx.com>cc: Sent by: Subject: SIMPLE QUESTION [EMAIL PROTECTED] m 05/01/02 10:18 AM Please respond to ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: 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: SIMPLE QUESTION
Hamid, Stop the listener, delete the file, then restart it. lsnrctl stop delete the log or rename it lsnrctl start It will take less than a minute. If this isn't a very important server you can do it quickly during the day - but use your judgment on that, don't want to get you fired. HTH Reed -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Sutton, Reed 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: SIMPLE QUESTION (truncating listener.log)
Oops! I forgot about that..RBG - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, May 01, 2002 2:28 PM > Hi, > You can truncate the listener.log without stopping the listener by doing > this: > > cat /dev/null > listener.log > > It won't interrupt the flow of information into the file because it > maintains the same inode value. > > Regards, > Mike Hately > > PS notice how "truncating listener.log" is more meaningful than "SIMPLE > QUESTION". =) > > > > -Original Message- > Sent: Wednesday, May 01, 2002 6:54 PM > To: Multiple recipients of list ORACLE-L > > > Hamid, > > Ignore my e-mail, as I had alert_log in the brain, not listener. As for the > listener, for most versions, you would have to stop the listener, remove it, > then restart it. > > Thank you, > > Paul Sherman > DBAElcom, Inc. > voice - 781-501-4143 (direct #) > fax- 781-278-8341 (secure) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, May 01, 2002 1:18 PM > To: Multiple recipients of list ORACLE-L > > > HI AGAIN, > > JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO > RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY > ORACLE. > HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? > THANKS FOR HELP. > > > > > Hamid Alavi > Office 818 737-0526 > Cell818 402-1987 > > > > > > > > > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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: SIMPLE QUESTION
Title: RE: SIMPLE QUESTION Actually it can be done without restarting the listener lsnrctl set log_file new_log_file_name [re]move the old log file lsnrctl set log_file old_log_file_name Matt Adams - GE Appliances - [EMAIL PROTECTED] Reason is 6/7ths of treason. - The Xtals -Original Message- From: Sherman, Paul R. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 1:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: SIMPLE QUESTION Hamid, Ignore my e-mail, as I had alert_log in the brain, not listener. As for the listener, for most versions, you would have to stop the listener, remove it, then restart it. Thank you, Paul Sherman DBA Elcom, Inc. voice - 781-501-4143 (direct #) fax - 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. Hamid Alavi Office 818 737-0526 Cell 818 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: Sherman, Paul R. 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: SIMPLE QUESTION (truncating listener.log)
Hi, You can truncate the listener.log without stopping the listener by doing this: cat /dev/null > listener.log It won't interrupt the flow of information into the file because it maintains the same inode value. Regards, Mike Hately PS notice how "truncating listener.log" is more meaningful than "SIMPLE QUESTION". =) -Original Message- Sent: Wednesday, May 01, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Hamid, Ignore my e-mail, as I had alert_log in the brain, not listener. As for the listener, for most versions, you would have to stop the listener, remove it, then restart it. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. Hamid Alavi Office 818 737-0526 Cell818 402-1987 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: SIMPLE QUESTION
You have to have the listener down to rename or delete it. Just shut the listener down for a second, rename the file or delete it, and restart the listener. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, May 01, 2002 1:18 PM > HI AGAIN, > > JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO > RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY > ORACLE. > HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? > THANKS FOR HELP. > > > > > 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: Ruth Gramolini 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: SIMPLE QUESTION
Here is what we did. 1. Disable VCS; 2. lsnrctl> set current_listener LISTENERedb; 3. lsnrctl> set log_status off; 4. rename listeneredb.log to listeneredb.log.old; 5. lsnrctl> set log_status on. -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Feng, Jun 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: SIMPLE QUESTION
Switch the logfile location to another directory using 'lsnrctl set log_directory', rename the old log, switch the destination back. -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Seefelt, Beth 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: SIMPLE QUESTION
Hamid, the following command: lsnrctl set log_status off will stop logging connections to the listener.log file. the following command: lsnrctl set log_file listener1.log will create a new file called listener1.log. you could then delete the original listener.log file (I have YET to find a reason for keeping this file around), and issue another : lsnrctl set log_file listener.log to reset the logging back to the original file name. I have started using option #1 above to turn this stupid logging off. hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: SIMPLE QUESTION
stop the listener, then rename or delete it. (It won't matter.) Then restart the listener and you will have a new log. >From: Hamid Alavi <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: SIMPLE QUESTION >Date: Wed, 01 May 2002 09:18:23 -0800 > >HI AGAIN, > >JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO >RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY >ORACLE. >HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? >THANKS FOR HELP. > > > > >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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 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: SIMPLE QUESTION
You'll have to stop listener in order to remove listener.log (otherwise, the file is locked). If you are not interested in the info, which is in listener.log, you can just specify "LOGGING_LISTENER=OFF" in your listener configuration file. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, May 01, 2002 1:48 PM > Hamid, > > Move it to alert_log.yymmdd if you need to save it, else remove it. The file > will be auto-created by Oracle the next time it writes to it. > > Thank you, > > Paul Sherman > DBAElcom, Inc. > voice - 781-501-4143 (direct #) > fax- 781-278-8341 (secure) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, May 01, 2002 1:18 PM > To: Multiple recipients of list ORACLE-L > > > HI AGAIN, > > JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO > RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY > ORACLE. > HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? > THANKS FOR HELP. > > > > > 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: Sherman, Paul R. > 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: Igor Neyman 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: SIMPLE QUESTION
Actually, like most everything else, it depends on platform and version. OpenVMS, for example, will not recreate the file. In this case, you must: 1) lsnrctl set log_file temp_name 2) rename $ORACLE_HOME/network/log/listener.log to something else. 3) lsnrctl set log_file listener (no ".log"!) This should work for Oracle 8.x. Not sure about other versions. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Disclaimer: I'm an idiot, but the difference between me and other idiots is that I know I'm an idiot. > -Original Message- > From: Sherman, Paul R. [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, May 01, 2002 12:49 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SIMPLE QUESTION > > > Hamid, > > Move it to alert_log.yymmdd if you need to save it, else > remove it. The file > will be auto-created by Oracle the next time it writes to it. > > Thank you, > > Paul Sherman > DBAElcom, Inc. > voice - 781-501-4143 (direct #) > fax- 781-278-8341 (secure) > email - [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, May 01, 2002 1:18 PM > To: Multiple recipients of list ORACLE-L > > > HI AGAIN, > > JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING > BIG I TRY TO > RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD > ONE IS USING BY > ORACLE. > HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY > DELETE IT OR NOT? > THANKS FOR HELP. > > > > > Hamid Alavi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: SIMPLE QUESTION
If you are running Oracle on Windoze, you have to stop the listener service, then rename the listener.log, then start the listener service again. I don't know how on Unix. Dave -Original Message- Sent: Wednesday, May 01, 2002 12:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Farnsworth, Dave 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: SIMPLE QUESTION
cat /dev/null > listener.log. -Original Message- Sent: Wednesday, May 01, 2002 12:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Mandal, Ashoke 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: SIMPLE QUESTION
Hamid, Ignore my e-mail, as I had alert_log in the brain, not listener. As for the listener, for most versions, you would have to stop the listener, remove it, then restart it. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Sherman, Paul R. 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: SIMPLE QUESTION
What OS? Oracle version? Oh yeah, stop SHOUTING. We can hear you just fine here on the Good Ship Lollipop. Scott Shafer San Antonio, TX 210-581-6217 > -Original Message- > From: Hamid Alavi [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, May 01, 2002 12:18 PM > To: Multiple recipients of list ORACLE-L > Subject: SIMPLE QUESTION > > HI AGAIN, > > JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO > RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY > ORACLE. > HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? > THANKS FOR HELP. > > Hamid Alavi > Office 818 737-0526 > Cell818 402-1987 > -- 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: SIMPLE QUESTION
For Unix: cp -p listener.log listener.log.old echo "" > listener.log Or you can disable logging completely using logging_listener=off in listener.ora Hamid Alavi wrote: > > HI AGAIN, > > JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO > RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY > ORACLE. > HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? > THANKS FOR HELP. > > 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: Suzy Vordos 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: SIMPLE QUESTION
Hamid, Move it to alert_log.yymmdd if you need to save it, else remove it. The file will be auto-created by Oracle the next time it writes to it. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 01, 2002 1:18 PM To: Multiple recipients of list ORACLE-L HI AGAIN, JUST WANT TO CHECK WITH YOU GUYS, MY LISTENER.LOG IS GETTING BIG I TRY TO RENAME IT THEN CREATE A NEW LISTENER.LOG BUT STILL THE OLD ONE IS USING BY ORACLE. HOW CAN I TRUNCATE THE LISTENER.LOG OR CAN I JUST SIMPLY DELETE IT OR NOT? THANKS FOR HELP. 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: Sherman, Paul R. 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: simple question
alter tablespace add datafile '' size ; >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: simple question >Date: Tue, 29 May 2001 11:52:29 -0800 > >Does any one have an example of 'alter tablespace add datafile' >statement? I am having a little trouble.. >Thank you. > > > > >-- >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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: simple question
alter tablespace tbname adddatafile '/u02/test.dbf' size 100m autoextend on next 8m maxsize 2000m; - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Tuesday, May 29, 2001 3:52 PM > Does any one have an example of 'alter tablespace add datafile' > statement? I am having a little trouble.. > Thank you. > > > > > -- > 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: Mitchell 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: simple question
alter tablespace tbs_name add datafile '/u01/oracle/admin/data/data_02.dbf' size 200m; If the file already exists, add reuse to the last line. Terry [EMAIL PROTECTED] wrote: > Does any one have an example of 'alter tablespace add datafile' > statement? I am having a little trouble.. > Thank you. > > -- > 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: Terry Ball 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: simple question
Problem resolved. Oracle 8 Complete Reference I was using contains an incorrect syntax. They use MAXSIZE instead of SIZE in the syntax. Thank you to all of those who replied. -Original Message- Sent: Tuesday, May 29, 2001 3:52 PM To: Multiple recipients of list ORACLE-L Does any one have an example of 'alter tablespace add datafile' statement? I am having a little trouble.. Thank you. -- 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: 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: simple question
alter tablespace pad add datafile '/u18/oradata/prod/pad02PROD.dbf' size 1750M; >>> [EMAIL PROTECTED] 05/29/01 03:52PM >>> Does any one have an example of 'alter tablespace add datafile' statement? I am having a little trouble.. Thank you. -- 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: William Beilstein 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: simple question
alter tablespace tablespace_name add datafile '/full_path/datafile_name.dbf' size 100M; This is the correct syntax. HTH, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, May 29, 2001 3:52 PM > Does any one have an example of 'alter tablespace add datafile' > statement? I am having a little trouble.. > Thank you. > > > > > -- > 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: Ruth Gramolini 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).