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
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
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
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
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
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
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
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
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
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
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
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
-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
recipients of
list
ORACLE-L [EMAIL PROTECTED]
.com cc:
Sent by: Subject: RE: simple
question
on
DDL
root@fatcity.
com
]
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
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
:
Sent by: Subject: RE: simple question on DDL
[EMAIL PROTECTED
recipients of list ORACLE-L [EMAIL PROTECTED]
rizon.com cc:
Sent by: Subject: RE: simple
question on DDL
[EMAIL PROTECTED]
January 22,
2003
10:33
PM
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
]]
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
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
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
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
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
]
tmail.com cc:
Sent by: Subject: Re: simple question on DDL
root@fatcity
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
arupnanda@hoTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
tmail.com cc:
Sent by: Subject: Re: simple question
on DDL
root@fatcity.
com
January 22
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,
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
: RE: simple question on DDL
root@fatcity.
com
]
.comcc:
Sent by: Subject: RE: simple question on
DDL
root@fatcity.
com
January 22,
2003 02:16 PM
Please
cc:
Sent by: Subject: RE: simple question
on
DDL
root@fatcity.
com
January 22,
2003 02:16 PM
Please
respond
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
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
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,
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
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
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
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
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
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]
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
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
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 -
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
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
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
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
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
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
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
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:
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,
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
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
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
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
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
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
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
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
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
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'
alter tablespace tablespacename
add datafile 'fullly qualified datafile name' size some 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
64 matches
Mail list logo
