Re: script

[zhu chao]

RE: script

[Stephen Lee]

For something a little different, play with the attached free.sql script.

-Original Message-

where can i find a script to find segments which can't extend due to low of
space in tablespace .

-ak



free.sql
Description: Binary data

Re: script

[Joan Hsieh]

AK, this is a very old script. 

set pagesize 999



spool extent.lst
ttitle 'Database Objects that will have Trouble Throwing Extents'

column owner format a10;
column segment_name format a22;
column segment_type format a10;
column tablespace_name format a14;
column next_extent format 999,999,999;

SELECT seg.owner, seg.segment_name,
   seg.segment_type, seg.tablespace_name,
   t.next_extent
   FROM sys.dba_segments seg,
sys.dba_tables   t
WHERE  (seg.segment_type = 'TABLE'
  ANDseg.segment_name = t.table_name
  ANDseg.owner= t.owner
  ANDNOT EXISTS
(select tablespace_name
   from dba_free_space free
  where free.tablespace_name =  t.tablespace_name
and bytes   >=  t.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
   seg.segment_type, seg.tablespace_name,
   DECODE (seg.segment_type,
   'CLUSTER',  c.next_extent)
   FROM sys.dba_segments seg,
sys.dba_clusters c
WHERE   (seg.segment_type = 'CLUSTER'
  ANDseg.segment_name = c.cluster_name
  ANDseg.owner= c.owner
  ANDNOT EXISTS
(select tablespace_name
   from dba_free_space free
  where free.tablespace_name =  c.tablespace_name
and bytes   >=  c.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
   seg.segment_type, seg.tablespace_name,
   DECODE (seg.segment_type,
   'INDEX',i.next_extent )
   FROM sys.dba_segments seg,
sys.dba_indexes  i
WHERE  (seg.segment_type = 'INDEX'
  ANDseg.segment_name = i.index_name
  ANDseg.owner= i.owner
  ANDNOT EXISTS
(select tablespace_name
   from dba_free_space free
  where free.tablespace_name =  i.tablespace_name
and bytes   >=  i.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
   seg.segment_type, seg.tablespace_name,
   DECODE (seg.segment_type,
   'ROLLBACK', r.next_extent)
   FROM sys.dba_segments seg,
sys.dba_rollback_segs r
where  (seg.segment_type = 'ROLLBACK'
  ANDseg.segment_name = r.segment_name
  ANDseg.owner= r.owner
  ANDNOT EXISTS
(select tablespace_name
   from dba_free_space free
  where free.tablespace_name =  r.tablespace_name
and bytes   >=  r.next_extent ))
/
ttitle 'Segments that Are Sitting on the Maximum Extents Allowable '

select  e.owner, e.segment_name, e.segment_type, count(*),
avg(max_extents)
 from  dba_extents e , dba_segments s
where  e.segment_name = s.segment_name
  and  e.owner= s.owner
group by  e.owner, e.segment_name, e.segment_type
having count(*) = avg(max_extents)
/

spool off

> AK wrote:
> 
> where can i find a script to find segments which can't extend due
> to low of space in tablespace .
> 
> -ak
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: Script to check filename continuously

[Pete Finnigan]

Hi Sami

Try something like this in korn shell, i have typed it in from memory so
please check the syntax...:-)

#!/bin/ksh
location="/tmp"
filename="YOURFILENAME"
done=false
while [[ $done = false ]]; do
if [[ ! -a $filename ]]; then
echo "do something"
cp $filename $location
done=true
fi
done

kind regards

Pete
-- 
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pete Finnigan
  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: Script to check filename continuously

[Pardee, Roy E]

Script would be good for this kind of thing, I think.  Dig how similar this
windows scripting host script is to your pseudocode:

' 
Dim FSO
Const WatchForFile = "c:\pretend.txt"
Const CopyFileTo = "c:\copied.txt"

   Set FSO = CreateObject("Scripting.FileSystemObject")
   Do While True
  If FSO.FileExists(WatchForFile) Then
 WScript.Echo "Found " & WatchForFile & "!  Copying..."
 Call FSO.CopyFile(WatchForFile, CopyFileTo)
 Exit Do
  Else
 WScript.Echo "Still no " & WatchForFile & "--going to sleep for 10
seconds..."
 WScript.Sleep 1
  End If
   Loop
   Set FSO = Nothing   
   WScript.Echo "Finished running " & WScript.ScriptFullName   
' 

Save that off to a text file w/extension .vbs & execute it at the command
line by typing
   cscript <>.vbs.

There's documentation for the windows scripting host at
http://www.microsoft.com/scripting.

HTH,

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487

-Original Message-
Sent: Thursday, July 03, 2003 3:31 AM
To: Multiple recipients of list ORACLE-L






Hi All

Does anyone have script(DOS batch file or Unix Shell) to do the following
stuff?

loop
if exist FILE_NAME
  begin
do something;
copy files to remote machine,etc...
EXIT;
  end;
end loop;

Thanks
Sami

-- 
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: Pardee, Roy E
  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: Script to check filename continuously

[Brian_P_MacLean]

Here is a start for you.  You can get the sleep command at any of these
places

  http://www.weihenstephan.de/~syring/win32/UnxUtils.html
  http://www.cruzio.com/~jeffl/mrtg/docs/sleep.exe
  http://www.nextgeneration.dk/gnu/index.shtml


@ECHO OFF
set BASE_DIR=d:\oracle\oradata\sid\archive
set BASE_FILE=*.arc
set RMT_DIR=x:\rmt_path
:LOOP
for /F  %%T In ('dir %BASE_FILE% /B /O:-N') Do (
  copy %%T %RMT_DIR%\%%T
)
sleep 5
GOTO LOOP





   
  
  Saminathan_Seeran
  
  [EMAIL PROTECTED]   To:   Multiple recipients of 
list ORACLE-L <[EMAIL PROTECTED]> 
  Sent by: cc: 
  
  [EMAIL PROTECTED]Subject:  Script to check filename 
continuously   
  .com 
  
   
  
   
  
  07/03/2003 03:30 
  
  AM   
  
  Please respond to
  
  ORACLE-L 
  
   
  
   
  








Hi All

Does anyone have script(DOS batch file or Unix Shell) to do the following
stuff?

loop
if exist FILE_NAME
  begin
do something;
copy files to remote machine,etc...
EXIT;
  end;
end loop;

Thanks
Sami

--
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: 
  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: Script to check for errors

[Barbara Baker]

Andy:
This is a very cool script.
Thanks very much!!

Barb

--- Andy Rivenes <[EMAIL PROTECTED]> wrote:
> You can try a korn shell script called chkdberr.ksh
> at 
> http://www.appsdba.com/scripts/chkdberr.ksh, it's
> already got the email 
> built in.
> 
> 
> At 10:39 AM 3/21/2003 -0800, you wrote:
> >Anyone have a "simple" script to scan an alert
> >log for errors and email a report if found?
> >
> >Customer wants something to run "often", but only
> >email when an error is found. I've got something
> >that does a bit of that and runs twice a day, but
> >always sends out.
> >
> >Thanks.
> >
> >Maks.
> >
> 
> 
> Andy Rivenes
> Email: [EMAIL PROTECTED]
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Andy Rivenes
>   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).
> 


__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Barbara Baker
  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: Script to check for errors

[david hill]

Here a fast little script I wrote that can
be ran every minute

Emails only when it finds a new message

And pages if it finds a new message
between 6pm and 5am

 

FPATH=/prod/dba/scr

autoload f_xmail

 

logfile=/prod/dba/logs/`cat
/prod/dba/sys_data/newpath`/misc_logs/chk_alerts.log

 

echo "$(date +%y-%m-%d_%H:%M)
 BOK - $$" >> $logfile

for i in DB1 DB2; do

   typeset -l sub=$(echo $i |cut
-c1)

   alert_log="/prod/dump/$sub/alert_$i.log"

   if [ -f $alert_log ]; then  
#if you don't find an alert log its ok just exit

    line_file="/prod/dump/$sub/ora_line"

    if [ ! -f $line_file ];
then 

    echo 0 > $line_file

    fi

    last_line=$(cat $line_file)

    new_line=$(cat $alert_log |wc
-l)

    if [ $new_line -lt $last_line
]; then    #then the alter log must have been deleted and recreated

  last_line=0

    fi

 

    if [ $(tail +$((last_line +
1)) $alert_log |grep ORA- |wc -l) -gt 0 ]; then

    echo "$(date
+%y-%m-%d_%H:%M) NEW ORA Messages Found" >> $logfile

    (echo "From: chk_alerts.ksh"

 echo
"Subject: ORA messages found for $i \n"

 cat $alert_log )
|mail dba@lechateau.ca #mail
the entire log when new messages are found

    if [[ $(date +%H%M)
-lt 0600 || $(date +%H%M) -gt 1800 || $(date +%u) -gt 5 ]]; then #nobody is
around page also

    echo
"$(date +%y-%m-%d_%H:%M) Nobody is at work so paging also" >> $logfile

    f_xmail
pager ORA messages found for $i

    fi

    fi

    echo $new_line > $line_file

   else

  echo "$(date +%y-%m-%d_%H:%M)
NO alert_log found to scan" >> $logfile

   fi

done

 

 

-Original Message-
From: Michael Kline
[mailto:[EMAIL PROTECTED] 
Sent: Friday, March 21, 2003 1:40
PM
To: Multiple recipients of list
ORACLE-L
Subject: Script to check for
errors

 



Anyone have a
"simple" script to scan an alert





log for errors and email a
report if found?





 





Customer wants something to
run "often", but only





email when an error is
found. I've got something





that does a bit of that and
runs twice a day, but





always sends out.





 





Thanks.





 





Maks.





 

Re: Script to check for errors

[Andy Rivenes]

You can try a korn shell script called chkdberr.ksh at 
http://www.appsdba.com/scripts/chkdberr.ksh, it's already got the email 
built in.

At 10:39 AM 3/21/2003 -0800, you wrote:
Anyone have a "simple" script to scan an alert
log for errors and email a report if found?
Customer wants something to run "often", but only
email when an error is found. I've got something
that does a bit of that and runs twice a day, but
always sends out.
Thanks.

Maks.



Andy Rivenes
Email: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Andy Rivenes
 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: Script to check for errors

[Deshpande, Kirti]

Check Tim Gorman's web site, http://www.evdbt.com/tools.htm. He has a script 
(chk_oerr.sh) to monitor alert.log for new ORA- errors and e-mail. 
You can see how it is done and come up with your own version for your own 
requirements. 

- Kirti


-Original Message-
Sent: Friday, March 21, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L


Anyone have a "simple" script to scan an alert
log for errors and email a report if found?

Customer wants something to run "often", but only
email when an error is found. I've got something
that does a bit of that and runs twice a day, but
always sends out.

Thanks.

Maks.

-- 
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: Script to check for errors

[Eberhard, Jeff]

Here's what I use.  Pretty basic (crappy) but may be a starting point for
you.  You'll have to go through and change where you alert files are stored
and to whom to send the email.  I schedule them to run about every 15
minutes. blat is a mail utility you can downloaded from the internet.  
 
 
 
HP-UX:
 
if [ -s "ORAerr.tmp" ]
  then
exit
fi
grep ORA- /u01/app/oracle/admin/orcl/bdump/alert_sid.log > ORAerr.tmp
if [ -s "ORAerr.tmp" ]
  then
  cat crlf.txt /u01/app/oracle/ORAerr.tmp | /usr/sbin/sendmail
-CsendmailORA.cf 
-F"Oracle dbsrv1 alert error" username @company.com
 
fi
 
 
 
WINdoze:
 
find "ORA-" c:\orant\rdbms80\trace\*ALRT.LOG* > e:\dbwork\alerts.txt
if errorlevel 1 goto NEXT
rem net send dba "An ORACLE error has been found in the alert file.  Please
check the alerts.txt file on dbsrv2"
blat e:\dbwork\alerts.txt -t dba @company.com   -s
"Oracle (dbsrv2) alert"
:NEXT
find "ORA-" c:\orant\rdbms80\trace\orcl\*ALRT.LOG* >
e:\dbwork\alertsorcl.txt
if errorlevel 1 goto END
rem net send dba "An ORACLE error has been found in the ORCL alert file.
Please check the alerts.txt file on dbsrv2"
blat e:\dbwork\alertsorcl.txt -t dba @company.com 
-s "Oracle ORCL alert"
erase c:\orant\rdbms80\trace\orcl\orclalrt.tmp
rename c:\orant\rdbms80\trace\orcl\orclalrt.log orclalrt.tmp
:END

 

-Original Message-
Sent: Friday, March 21, 2003 11:40 AM
To: Multiple recipients of list ORACLE-L


Anyone have a "simple" script to scan an alert
log for errors and email a report if found?
 
Customer wants something to run "often", but only
email when an error is found. I've got something
that does a bit of that and runs twice a day, but
always sends out.
 
Thanks.
 
Maks.
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Eberhard, Jeff
  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: Script to Turn Off All Oracle Auditing Options?

[Charlie_Mengler]

I see yet another poor soul still supporting V7.3.4.5 as I am doing on V2.6
Solaris.

You should be warned that on a couple of different occasions (I'm a slow
learner)
when I launched a SQL script which changed AUDITing on hundreds of objects,
Oracle went "bonkers". It appearded as though the memory resident portion
of
the data dictionary got scrambled. For example Oracle started spewing
errors
that it could not find the PART table. Needless to say this caused
considerable
problems for my end users.

So now whenever I need to changing AUDITing, I run the script immediately
before I am/can bounced the database; just to be safe.

HTH & YMMV

HAND!




   
  
  "Sam Bootsma"
  
  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]> 
  .on.ca>  cc: 
  
  Sent by: Subject:  Script to Turn Off All Oracle 
Auditing Options? 
  [EMAIL PROTECTED]
   
   
  
   
  
  03/04/2003 01:50 
  
  PM   
  
  Please respond to
  
  ORACLE-L 
  
   
  
   
  




We are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version
2.3.4.0.0.

I have a database here with about 600 Oracle users defined in dba_users.
However, likely one half or more of the defined users no longer connect to
the database.  I want to purge out the users that do not use the database.
My plan is this:

   1. Turn on session auditing (See below for potential problem)
   2. After a couple of months, determine which users have never logged
  into the database
   3. Lock or drop the accounts of these users

I was about to turn on auditing when I realized there was a potential
problem.  Currently the AUDIT_TRAIL parameter is not defined in the
init.ora file, so there is no auditing being performed.  Potential Problem:
I do not know what auditing options are already defined.  For all I know,
there may be an option that audits all selects and all DML on all tables
for all users (pretty much worst case scenario).  If such an option exists,
it will put a very heavy load on the database, and I will get in trouble
with users and management in the morning.

I want to be able to find out what options are currently set for auditing.
Ideally, I would like scripts that will run through all possible auditing
options and turn them all off.  This way, I can turn on the option(s) I
want turned on, and not worry about pre-existing options.  Can anybody tell
me what data dictionary views hold the auditing options?  Also, our system
has a synonym named DBA_SYS_AUDIT_OPTS, but no corresponding view.  Is
there supposed to be a view for this?

I am new to this company, and new to the database.  Any help you can
provide is appreciated.

Thanks,

Sam Bootsma
[EMAIL PROTECTED]





-- 
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: Script to Turn Off All Oracle Auditing Options?

[Jacques Kilchoer]

In my previous message, I forgot to say:
You could always use the "scorched earth" approach and change everyone's
password. Then see who complains that they can't log in. :)

-Original Message-


We are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version
2.3.4.0.0.  
 
I have a database here with about 600 Oracle users defined in dba_users.
However, likely one half or more of the defined users no longer connect
to the database.  I want to purge out the users that do not use the
database. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  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: Script to Turn Off All Oracle Auditing Options?

[Jacques Kilchoer]

Title: RE: Script to Turn Off All Oracle Auditing Options?





To create the audit trail views, run cataudit.sql.
To delete the audit trail views, run catnoaud.sql.


To find out which are the active options, use this statement:
SELECT * FROM sys.dba_priv_audit_opts;


or for audit options on a specific object:
SELECT * FROM sys.dba_obj_audit_opts WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';


default object audit options:
SELECT * FROM all_def_audit_opts;
 
Use the noaudit command to disable auditing.


Reference: Oracle 7 Server Administrator's Guide - Auditing Database Use
 
 -Original Message-
From: Sam Bootsma [mailto:[EMAIL PROTECTED]]


We are running Oracle 7.3.4.5.0 on an IBM/AIX RISC System/6000: Version 2.3.4.0.0.  
 
I have a database here with about 600 Oracle users defined in dba_users.  However, likely one half or more of the defined users no longer connect to the database.  I want to purge out the users that do not use the database.  My plan is this:

 
Turn on session auditing (See below for potential problem) 
After a couple of months, determine which users have never logged into the database 
Lock or drop the accounts of these users 
 
I was about to turn on auditing when I realized there was a potential problem.  Currently the AUDIT_TRAIL parameter is not defined in the init.ora file, so there is no auditing being performed.  Potential Problem:  I do not know what auditing options are already defined.  For all I know, there may be an option that audits all selects and all DML on all tables for all users (pretty much worst case scenario).  If such an option exists, it will put a very heavy load on the database, and I will get in trouble with users and management in the morning.  

 
I want to be able to find out what options are currently set for auditing.  Ideally, I would like scripts that will run through all possible auditing options and turn them all off.  This way, I can turn on the option(s) I want turned on, and not worry about pre-existing options.  Can anybody tell me what data dictionary views hold the auditing options?  Also, our system has a synonym named DBA_SYS_AUDIT_OPTS, but no corresponding view.  Is there supposed to be a view for this?

 

RE: Script to recreate schema

[Richard Ji]

Yes.  And bvi for binary files.

-Original Message-
Sent: Thursday, December 26, 2002 2:04 AM
To: Multiple recipients of list ORACLE-L



exp userid=system/manager file=schema.dmp rows=n owner=scott
vi schema.dmp

really.

Jared

On Wednesday 25 December 2002 09:53, Andrey Bronfin wrote:
> Dear gurus !
> I'm sure many of you have scripts to recreate an Oracle schema including
> objects (i am interested in tables, indexes , comments, views, sequences,
> triggers, stored procs/functions etc..)
> Would you please share.
> Many thanks in advance !
>
> Merry X-mas and Happy New Year to you all !


Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: Richard Ji
  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: Script to recreate schema

[Dale]

> > I'm sure many of you have scripts to recreate an Oracle schema including
> > objects (i am interested in tables, indexes , comments, views,
sequences,
> > triggers, stored procs/functions etc..)
> exp userid=system/manager file=schema.dmp rows=n owner=scott
> vi schema.dmp

Instead of "vi schema.dmp" use the freeware DBATool to extract a set of
rebuild scripts from the export file. It is much easier and there are a lot
of other options available too.

DBATool: http://www.DataBee.com/dt_home.htm

Regards
Dale

-- 
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: Script to recreate schema

[Jared Still]

exp userid=system/manager file=schema.dmp rows=n owner=scott
vi schema.dmp

really.

Jared

On Wednesday 25 December 2002 09:53, Andrey Bronfin wrote:
> Dear gurus !
> I'm sure many of you have scripts to recreate an Oracle schema including
> objects (i am interested in tables, indexes , comments, views, sequences,
> triggers, stored procs/functions etc..)
> Would you please share.
> Many thanks in advance !
>
> Merry X-mas and Happy New Year to you all !


Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  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: Script to find free space in an index

[Jared Still]

If that's one of my scripts, RCS says it's from 1997.

Here's a newer one.  It gets *everything* in the database, so 
a little tweaking may be in order.

Jared

===

-- showspace_all.sql
-- use dbms_space to show exact amount
-- of space consumed by tables/indexes/clusters

-- jared still

set verify off
set echo off feed off

prompt
prompt Working...
prompt


declare

object_blocks   number;
unused_blocks   number;

object_bytesnumber;
unused_bytesnumber;

total_blocksnumber default 0;
total_bytes number default 0;

last_used_extent_file_idnumber;
last_used_extent_block_id   number;
last_used_block number;

cursor c_objects
is
select owner, object_name, object_type
from dba_objects
where object_type in ('CLUSTER','INDEX','TABLE');


begin

for objrec in c_objects
loop

dbms_space.unused_space(
objrec.owner,objrec.object_name,objrec.object_type,
object_blocks, object_bytes,
unused_blocks, unused_bytes,
last_used_extent_file_id ,
last_used_extent_block_id,
last_used_block
);
dbms_output.put_line(
'Total space used by ' || objrec.owner || '.' || 
objrec.object_name
);

-- comment out these 2 lines if you get buffer overflow on dbms_output
dbms_output.put_line('  BLOCKS: ' || to_char( object_blocks - 
unused_blocks ));
dbms_output.put_line('  BYTES : ' || to_char( object_bytes - 
unused_bytes ));

total_blocks := total_blocks + ( object_blocks - unused_blocks );
total_bytes := total_bytes + ( object_bytes - unused_bytes );

end loop;

dbms_output.put_line('  '  );
dbms_output.put_line('TOTAL BLOCKS: ' || to_char( total_blocks ));
dbms_output.put_line('TOTAL BYTES : ' || to_char( total_bytes ));


end;
/

set feed on
==-
ù
On Sunday 23 June 2002 19:28, Ferenc Mantfeld wrote:
> Hi All
> Jared notified me that the attachment did not come through, so I am
> attaching again. I am also enclosing it in the body of the email.
>
> Credits go to Jared who wrote the shell of this some time back (98 was it
> Jared ?). I just modified an excellent foundation.
>
>  <>
>
> set linesize 80
> set verify off
> set echo off
> set feed off
>
> undef tab_name;
> undef object_type;
> undef schema_name;
>
> prompt Enter the schema and table name, and you are shown the space that
> the table
> prompt and each associated index uses in the database
> prompt
>
> accept schema_name prompt 'Enter Schema: '
> accept tab_name prompt 'Enter TABLE: '
> prompt
>
> set serverout on size 100
>
> declare
>   ind_namevarchar2(30);
>   total_blocksnumber;
>   unused_blocks   number;
>
>   total_bytes number;
>   unused_bytesnumber;
>
>   last_used_extent_file_idnumber;
>   last_used_extent_block_id   number;
>   last_used_block number;
>   cursor find_ind is
>   select index_name from all_indexes where
> owner=upper('&&schema_name') and
>   table_name=upper('&&tab_name') ;
>
> begin
>
>   dbms_space.unused_space(upper('&&schema_name'),upper('&&tab_name'),
>   'TABLE',total_blocks, total_bytes,unused_blocks,
> unused_bytes,
>
> last_used_extent_file_id,last_used_extent_block_id,last_used_block);
>   dbms_output.put_line('Total space used by TABLE  '||
>   upper('&&schema_name')||'.' ||upper('&&tab_name'));
>
>   dbms_output.put_line('TOTAL BLOCKS  USED_BLOCKS   FREE BLOCKS');
>   dbms_output.put_line('  ===   ===');
>   dbms_output.put_line(to_char(total_blocks)   ||'  '||
>   to_char(total_blocks - unused_blocks)||'  '||
>   to_char(unused_blocks));
>   dbms_output.put_line('  ');
>   open find_ind ;
>loop
>   fetch find_ind into ind_name ;
>   exit when find_ind%NOTFOUND or find_ind%NOTFOUND is null ;
>
>
> dbms_space.unused_space(upper('&&schema_name'),upper(ind_name),'INDEX',
>   total_blocks, total_bytes,
>   unused_blocks, unused_bytes,
>   last_used_extent_file_id ,
>   last_used_extent_block_id,
>   last_used_block );
>
>   dbms_output.put_line('Total space used by INDEX  '||
>   upper('&&schema_name')||'.' ||upper(ind_name)||'
> '||
>   to_char(total_blocks)   ||'
> '||to_char(total_blocks - unused_blocks)||
>   '  '|| to_char(unused_blocks));
>   end loop ;
>   if find_ind%ISOPE

RE: script for comparing two schemas in two databases

[Kevin Lange]

I came to that conclusion as well.  I especially like the fact that it even
creates a sync script after the compare.  

I just used it on two of our DBs.  Worked great.

-Original Message-
Sent: Wednesday, May 29, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L


Kevin,
 You should download the ver 7.3 of TOAD. It has a lot of improvements
that You might find handy.
Ron
ROR mª¿ªm

>>> [EMAIL PROTECTED] 05/29/02 01:24PM >>>
Years ago, I found a script on Metalink that worked decently after a
few bug fixes.  It was written by Ottar Sorland for use with Oracle 7.3.
 I still occasionally use it and can send my patched (but still not
perfect) version if you need it.
 
Lately, I've been using a TOAD feature (version 7.2 with DBA option)
that works much better.  I haven't tried the OEM tool.
 
Kevin Kennedy
First Point Energy

-Original Message-
Sent: Wednesday, May 29, 2002 2:18 AM
To: Multiple recipients of list ORACLE-L



Hi! 

Does anybody out there have a script that allows me to compare two
schemas in two different databases (i.e. development and production
db)?

This is 8.1.7 on Sun Solaris. 

Thanks, 
Helmut 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: script for comparing two schemas in two databases

[Ron Rogers]

Kevin,
 You should download the ver 7.3 of TOAD. It has a lot of improvements
that You might find handy.
Ron
ROR mª¿ªm

>>> [EMAIL PROTECTED] 05/29/02 01:24PM >>>
Years ago, I found a script on Metalink that worked decently after a
few bug fixes.  It was written by Ottar Sorland for use with Oracle 7.3.
 I still occasionally use it and can send my patched (but still not
perfect) version if you need it.
 
Lately, I've been using a TOAD feature (version 7.2 with DBA option)
that works much better.  I haven't tried the OEM tool.
 
Kevin Kennedy
First Point Energy

-Original Message-
Sent: Wednesday, May 29, 2002 2:18 AM
To: Multiple recipients of list ORACLE-L



Hi! 

Does anybody out there have a script that allows me to compare two
schemas in two different databases (i.e. development and production
db)?

This is 8.1.7 on Sun Solaris. 

Thanks, 
Helmut 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  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: script for comparing two schemas in two databases

[Simon . Anderson]

The Oracle Enterprise Manager 'Change Manager' does a good job of schema
comparisons, and has an easy  point-and-click interface that shields you from
having to know what you're doing ;-)

The downside is that it can be a pain to get oem set up and working.  I've never
tried it's more advanced functions to propagate changes between databases, but
capturing baselines for later comparison is nice.

I found the earlier versions buggy, slow and painfull to use, but version 2.2.0
under Oracle 8.1.7 seems to work OK.

Hope this helps.

Simon Anderson.




Please respond to [EMAIL PROTECTED]

To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:(bcc: Simon Anderson/SSplc)




Years ago, I found a script on Metalink that worked decently after a few bug
fixes.  It was written by Ottar Sorland for use with Oracle 7.3.  I still
occasionally use it and can send my patched (but still not perfect) version if
you need it.

Lately, I've been using a TOAD feature (version 7.2 with DBA option) that works
much better.  I haven't tried the OEM tool.

Kevin Kennedy
First Point Energy

-Original Message-
Sent: Wednesday, May 29, 2002 2:18 AM
To: Multiple recipients of list ORACLE-L



Hi!

Does anybody out there have a script that allows me to compare two schemas in
two different databases (i.e. development and production db)?

This is 8.1.7 on Sun Solaris.

Thanks,
Helmut





-- 
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: script for comparing two schemas in two databases

[kkennedy]

Title: script for comparing two schemas in two databases



Years 
ago, I found a script on Metalink that worked decently after a few bug 
fixes.  It was written by Ottar Sorland for use with Oracle 7.3.  
I still occasionally use it and can send my patched (but still not perfect) 
version if you need it.
 
Lately, I've been using a TOAD feature (version 7.2 with DBA option) 
that works much better.  I haven't tried the OEM tool.
 
Kevin 
Kennedy
First 
Point Energy

  -Original Message-From: Daiminger, Helmut 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 29, 
  2002 2:18 AMTo: Multiple recipients of list 
  ORACLE-LSubject: script for comparing two schemas in two 
  databases
  Hi! 
  Does anybody out there have a script that allows me 
  to compare two schemas in two different databases (i.e. development and 
  production db)?
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut 

RE: script for comparing two schemas in two databases

[Michael P Sale]

OEM's Change Management pack does just that. It also allows you to
compare it with previous snapshots and implement them into production in
a scheduled, controlled manner.


Regards,
 
Michael Sale
Co-author: Oracle 9i on Windows 2000 Tips and Techniques
 
-Original Message-
Helmut
Sent: Wednesday, May 29, 2002 3:18 AM
To: Multiple recipients of list ORACLE-L


Hi! 
Does anybody out there have a script that allows me to compare two
schemas in two different databases (i.e. development and production db)?
This is 8.1.7 on Sun Solaris. 
Thanks, 
Helmut 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michael P Sale
  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: script for comparing two schemas in two databases

[Farnsworth, Dave]

Thanks for the script Mark!

Dave

-Original Message-
Sent: Wednesday, May 29, 2002 4:53 AM
To: Multiple recipients of list ORACLE-L


Attached..

HTH

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales & Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput & performance

-Original Message-
Helmut
Sent: 29 May 2002 10:18
To: Multiple recipients of list ORACLE-L


Hi!
Does anybody out there have a script that allows me to compare two schemas
in two different databases (i.e. development and production db)?
This is 8.1.7 on Sun Solaris.
Thanks,
Helmut
--
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: script for comparing two schemas in two databases

[Alexandre Gorbatchev]

Title: script for comparing two schemas in two databases



Hi Helmut,
 
There are some tools for it. For example, Oracle 
Change Manager Pack for OEM.
This is a tough task for script, because too many 
factors are involved.
 
HTH,
Alexandre

  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, May 29, 2002 11:18 
  AM
  Subject: script for comparing two schemas 
  in two databases
  
  Hi! 
  Does anybody out there have a script that allows me 
  to compare two schemas in two different databases (i.e. development and 
  production db)?
  This is 8.1.7 on Sun Solaris. 
  Thanks, Helmut 

RE: script for comparing two schemas in two databases

[Stephane Faroult]

>Hi!
>
>Does anybody out there have a script that allows me
>to compare two schemas
>in two different databases (i.e. development and
>production db)?
>
>This is 8.1.7 on Sun Solaris.
>
>Thanks,
>Helmut
>

There's something in the DBA tool kit at http://www.oriole.com. Forgot the exact name.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  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: script for comparing two schemas in two databases

[Mark Leith]

Attached..

HTH

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales & Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput & performance

-Original Message-
Helmut
Sent: 29 May 2002 10:18
To: Multiple recipients of list ORACLE-L


Hi!
Does anybody out there have a script that allows me to compare two schemas
in two different databases (i.e. development and production db)?
This is 8.1.7 on Sun Solaris.
Thanks,
Helmut



compare_schema.sql
Description: Binary data

Re: script for comparing two schemas in two databases

[G . Plivna]

searching in google for
script compare two schemas oracle

gives as the 6th result
http://www.arrowsent.com/oratip/tip15.htm

that may be sufficient in your case


Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/



   
  
  "Daiminger, Helmut"  
  
  
  hGruppe.de>   cc:
  
  Sent by:  Subject:  script for comparing two 
schemas in two databases  
  [EMAIL PROTECTED] 
  
   
  
   
  
  2002.05.29 12:18 
  
  Please respond to
  
  ORACLE-L 
  
   
  
   
  




Hi!


Does anybody out there have a script that allows me to compare two schemas
in two different databases (i.e. development and production db)?


This is 8.1.7 on Sun Solaris.


Thanks,
Helmut







--
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: Script for deleting old archive logs from NT

[JApplewhite]

Arun,

Here are a couple of files (a .bat and .sql) that let me maintain a
constant number of Archived Redo Logs online.

The first batch file executes SQL*Plus to produce two other batch files to
delete the excess logs and move some others, maintaining, in this case
about 450 logs.  It ran every hour - smooth as silk.

Hope it helps.

Archived_Redo_Logs_MoveDelete_main.bat
---
Set ORACLE_SID=orcl

SQLPlus internal @C:
\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_sub.sql

Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat
Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat


Archived_Redo_Logs_MoveDelete_sub.sql

Set FeedBack Off
Set LineSize 200
Set PageSize   0
Set TrimSpool On

-- Delete backup archived redo logs if there are over 450 total arc`d logs.

Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat

Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) ||
'.LOG'
>From   (
Select To_Char(Sequence#,'0') Seq
From   v$Log_History
Order By Sequence# Desc
   ) v
Where  RowNum < 601
Minus
Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) ||
'.LOG'
>From   (
Select To_Char(Sequence#,'0') Seq
From   v$Log_History
Order By Sequence# Desc
   ) v
Where  RowNum < 451
;

Spool Off

-- Move some arc`d redo logs to backup if there are over 200 in Archive
directory.

Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat

Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T:
\Oracle\OraData\orcl\Archive_Backup'
>From   (
Select To_Char(Sequence#,'0') Seq
From   v$Log_History
Order By Sequence# Desc
   ) v
Where  RowNum < 401
Minus
Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T:
\Oracle\OraData\orcl\Archive_Backup'
>From   (
Select To_Char(Sequence#,'0') Seq
From   v$Log_History
Order By Sequence# Desc
   ) v
Where  RowNum < 201
;

Spool Off

Exit



Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715
[EMAIL PROTECTED]



   
 
Arun   
 
Chakrapani   To: Multiple recipients of list ORACLE-L  
 
   
 
WERS.com>cc:   
 
Sent by: Subject: Script for deleting old archive 
logs  
[EMAIL PROTECTED]from  NT 
 
om 
 
   
 
   
 
05/15/2002 
 
04:43 PM   
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




Does anybody has script to delete old archive logs on NT when the disk
reaches certain percentage.
Please let me know
Thanks

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arun Chakrapani
  INET: [EMAIL PROTECTED]



-- 
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: script to show heirarchical list of object dependencies for a

[Jack Silvey]

Great minds think alike? Either that or we are the
borg and just don't know that we are part of the
collective yet.


--- [EMAIL PROTECTED] wrote:
> 
> 
> How is this essentially different than what you can
> get from utldtree.sql?
> 
> when Kevin Loney and I were writing the Annotated
> Archives a few years ago, I
> tried to write a script that would show the
> dependencies, because part of the
> reason for the book was to provide "home-grown"
> scripts that wre documented. I
> ended up essentially rewriting the cod from
> utldtree.sql and we gave up that
> particular effort.
> 
> 
> 
> 
> |+--->
> ||   |
> ||   |
> ||  jack_silvey@y|
> ||  ahoo.com |
> ||   |
> ||  05/09/2002   |
> ||  07:03 PM |
> ||  Please   |
> ||  respond to   |
> ||  ORACLE-L |
> ||   |
> |+--->
>  
>
>|
>   | 
>   |
>   |   To: [EMAIL PROTECTED]  
>   |
>   |   cc: (bcc: Rachel Carmichael)  
>   |
>   |   Subject: script to show heirarchical
> list|
>   |   of object dependencies for a given object 
>   |
>  
>
>|
> 
> 
> 
> 
> Greetings listers,
> 
> Ever worry about wrinkles, loss of hair, and bad
> breath?
> 
> Well, the following script can't help you with THAT,
> but it CAN show you a heirarchical list of objects
> that depend upon the given object! Just pass in
> &1=owner and &2=object name, and viola! No more
> changing objects without knowing what depends on
> them
> (and might break). (The reason for the temp table is
> that you can't do recursive self-join sql on complex
> views.) This can be a very useful script if you make
> production database changes.
> 
> prompt
> prompt objects depending on &1. &2:
> prompt
> 
> set termout off
> set head off
> set verify off
> 
> drop table depends;
> 
> create table depends as select * from
> sys.dba_dependencies tablespace tools;
> 
> set termout on
> 
> select  '*'||lpad(' ',level+3)||type||'
> '||owner||'.'||name
> from depends
> connect by prior owner = referenced_owner
> and prior name = referenced_name
> and prior type = referenced_type
> start with referenced_owner = upper('&1')
> and referenced_name = upper('&2')
> and owner is not null
> /
> 
> good luck,
> 
> jack silvey
> 
> 
> __
> Do You Yahoo!?
> Yahoo! Shopping - Mother's Day is May 12th!
> http://shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jack Silvey
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 
> 
> 
> 
> -- 
> 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).


__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: script to show heirarchical list of object dependencies for a

[Rachel_Carmichael]

How is this essentially different than what you can get from utldtree.sql?

when Kevin Loney and I were writing the Annotated Archives a few years ago, I
tried to write a script that would show the dependencies, because part of the
reason for the book was to provide "home-grown" scripts that wre documented. I
ended up essentially rewriting the cod from utldtree.sql and we gave up that
particular effort.




|+--->
||   |
||   |
||  jack_silvey@y|
||  ahoo.com |
||   |
||  05/09/2002   |
||  07:03 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: script to show heirarchical list|
  |   of object dependencies for a given object|
  >|




Greetings listers,

Ever worry about wrinkles, loss of hair, and bad
breath?

Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
&1=owner and &2=object name, and viola! No more
changing objects without knowing what depends on them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.

prompt
prompt objects depending on &1. &2:
prompt

set termout off
set head off
set verify off

drop table depends;

create table depends as select * from
sys.dba_dependencies tablespace tools;

set termout on

select  '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('&1')
and referenced_name = upper('&2')
and owner is not null
/

good luck,

jack silvey


__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
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: script to show heirarchical list of object dependencies for a given

[Jack Silvey]

Aw, DANG, I knew I was gonna get that one. Same story,
give someone a script named after an adult
incontenence device, get ready for the teasing!

Don't even ask for the viagra script.


--- [EMAIL PROTECTED] wrote:
> > create table depends as select * from
> > sys.dba_dependencies;
> 
> Depends?  Like the adult diaper?
> 
> Doing a little CYA?
> 
> Jared
> 
> 
> 
> 
> 
> Jack Silvey <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 05/09/2002 04:03 PM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:script to show heirarchical
> list of object dependencies for a given object
> 
> 
> Greetings listers,
> 
> Ever worry about wrinkles, loss of hair, and bad
> breath? 
> 
> Well, the following script can't help you with THAT,
> but it CAN show you a heirarchical list of objects
> that depend upon the given object! Just pass in
> &1=owner and &2=object name, and viola! No more
> changing objects without knowing what depends on
> them
> (and might break). (The reason for the temp table is
> that you can't do recursive self-join sql on complex
> views.) This can be a very useful script if you make
> production database changes.
> 
> prompt
> prompt objects depending on &1. &2:
> prompt
> 
> set termout off
> set head off
> set verify off
> 
> drop table depends;
> 
> create table depends as select * from
> sys.dba_dependencies;
> 
> set termout on
> 
> select  '*'||lpad(' ',level+3)||type||'
> '||owner||'.'||name
> from depends
> connect by prior owner = referenced_owner
> and prior name = referenced_name
> and prior type = referenced_type
> start with referenced_owner = upper('&1')
> and referenced_name = upper('&2')
> and owner is not null
> /
> 
> good luck,
> 
> jack silvey
> 
> 
> __
> Do You Yahoo!?
> Yahoo! Shopping - Mother's Day is May 12th!
> http://shopping.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Jack Silvey
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> 
> 
> 
> -- 
> 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).


__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: script to show heirarchical list of object dependencies for a given

[Jared . Still]

> create table depends as select * from
> sys.dba_dependencies;

Depends?  Like the adult diaper?

Doing a little CYA?

Jared





Jack Silvey <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/09/2002 04:03 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:script to show heirarchical list of object dependencies for a 
given object


Greetings listers,

Ever worry about wrinkles, loss of hair, and bad
breath? 

Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
&1=owner and &2=object name, and viola! No more
changing objects without knowing what depends on them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.

prompt
prompt objects depending on &1. &2:
prompt

set termout off
set head off
set verify off

drop table depends;

create table depends as select * from
sys.dba_dependencies;

set termout on

select  '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('&1')
and referenced_name = upper('&2')
and owner is not null
/

good luck,

jack silvey


__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
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: Script for identifying objects having freelists contention

[Anjo Kolk]

That won't do it 
Anjo.
"Bunyamin K. Karadeniz" wrote:

 
SELECT s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state
FROM dba_segments s , V$session_wait w WHERE s.header_file=w.p1
AND s.header_block=w.p2;
 
 
 
   Bunyamin K. Karadeniz
Oracle DBA / Developer
Civilian IT Department
Havelsan A.S. Eskisehir yolu
7.km Ankara Turkey
Phone: +90 312 2873565 / 1217
Mobile : +90 535 3357729 The degree of normality in a database
is inversely proportional to that of its DBA.

- Original Message -

From:
Aponte, Tony

To: Multiple
recipients of list ORACLE-L

Sent: Thursday, April 18, 2002 8:13
AM

Subject: RE: Script for identifying
objects having freelists contention
 Steve Adams' site is
a good start.  http://www.ixora.com.au/Tony
Aponte

-Original
Message-
From: Anand Prakash [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 17,
2002 5:29 PM
To: Multiple recipients of
list ORACLE-L
Subject: Script for identifying
objects having freelists contention
 
Does anyone have the scripts (or URL) for identifying objects having freelists
contention? Thanks.Anand Prakash

Re: Script for identifying objects having freelists contention

[Bunyamin K. Karadeniz]

 
SELECT 
s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state 

FROM dba_segments s , V$session_wait w WHERE s.header_file=w.p1 AND 
s.header_block=w.p2; 
 
 
 
 
Bunyamin K. 
Karadeniz   Oracle 
DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 
7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 
3357729
 
The degree of normality in a database is inversely proportional to that 
of its DBA.

  - Original Message - 
  From: 
  Aponte, Tony 
  To: Multiple recipients of list ORACLE-L 
  Sent: Thursday, April 18, 2002 8:13 
  AM
  Subject: RE: Script for identifying 
  objects having freelists contention
  
  Steve Adams' site is a good 
  start.  http://www.ixora.com.au/
   
  Tony 
  Aponte
  
-Original Message-From: Anand Prakash [mailto:[EMAIL PROTECTED]]Sent: 
Wednesday, April 17, 2002 5:29 PMTo: Multiple recipients of list 
ORACLE-LSubject: Script for identifying objects having freelists 
contention

Does anyone have the scripts (or URL) for identifying objects having 
freelists contention?
 
Thanks.
Anand Prakash

RE: Script for identifying objects having freelists contention

[Aponte, Tony]

Steve Adams' site is a good 
start.  http://www.ixora.com.au/
 
Tony Aponte

  -Original Message-From: Anand Prakash 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 
  2002 5:29 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Script for identifying objects having freelists 
  contention
  
  Does anyone have the scripts (or URL) for identifying objects having 
  freelists contention?
   
  Thanks.
  Anand Prakash

RE: Script for identifying objects having freelists contention

[Wong, Bing]

I hope it works for 
you.
 
 
 
set Trimspool onset Line 132set Pages 
57set Newpage0set FeedBack offset Verify offset Term 
offTTitle offBtime offcolumn Pct Format 990.99 Heading "% 
Of |Free List Waits"column Instance New_Value 
_Instance NoPrintcolumn Today    New_Value _Date No 
Printselect Global_name Instance, To_char(SysDate, 'FXDay DD,  HH:MI') 
Todayfrom Global_Name;
 
TTitle OnTTitle Left 'Date Run: ' _Date Skip 
1-  Center 'Free list Contention' Skip 1 -  Center 'If 
Percentage is Greater then 1%' Skip 1 -  Center 'Consider increasing 
the number of free lists' Skip 1 -  Center 'Instance Name: ' 
_Instanceselect  ((A.Count / (B.Value + C.Value)) * 100) 
Pctfrom   V$WaitStat A, V$SysStat B, V$SysStat Cwhere A.Class 
= 'free list'  and B.Statistic#  =  (select Statistic# from 
V$StatName  
where Name = 'db block gets')and   C.Statistic# = (select 
Statistic# from 
V$StatName  
where Name = 'consistent gets')/Column Total_Waits  Format 
999,999,999,990 Heading "Buffer Busy Waits"Column 
DB_Get   Format 999,999,999,990 Heading "DB 
Block Gets"Column Con_Get  Format 
999,999,999,990 Heading "Consistent Gets"Column Busy_Rate    
Format 990.999 Heading "Busy 
Rate"TTitle Left 'Date Run: ' _Date Skip 
1-   Center 'Buffer Busy Waits Rate' Skip 
1 -   Center 'If >5% review V$waitStat' 
Skip 1 -   Center 'Instance Name: ' 
_Instance Skip 2select Total_Waits, B.Value DB_Get, C.Value 
Con_Get,   ((A.Total_Waits / (B.Value + 
C.Value)) * 100) Busyfrom   V$System_Event A, V$SysStat B, 
V$SysStat Cwhere A.Event = 'buffer busy waits'and B.Statistic# = (select 
Statistic# from V$StatName where Name = 'db block gets')and C.Statistic# = 
(select Statistic# from V$StatName where Name = 'consistent 
gets')/
 

  -Original Message-From: Anand Prakash 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 
  2002 2:29 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Script for identifying objects having freelists 
  contention
  
  Does anyone have the scripts (or URL) for identifying objects having 
  freelists contention?
   
  Thanks.
  Anand Prakash

Re: script to rename constraints

[Jared Still]

I don't have one, but would love to have one. ( hint, hint )

Jared

On Tuesday 19 February 2002 03:28, John Dunn wrote:
> Anyone got a script that will drop table constraints which have system
> generated names and recreate them with names based upon the table name?
>
> John
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  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: script to populate tables, i just wrote one

[P Jeram]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, February 18, 2002 1:28 PM


> List,
> i just finished writing a script to generate random data to populate
> tables for load testing... the script takes into account the check
> constraint
> of a column and generate the correct/valid values. Also, a DEFAULT value
of
> a column is also considered and generated accordingly. values for ALL the
> columns
> are generated (to keep it simple). I'm sure there are many scripts like
this
> on available on the
> web.. but hey!!! i needed to jog the pl/sql side of my brain ;-))
>
> mail me privately to request the script ...
>
> regards
>
> PS: please do not fill the list with "me too" messages
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rahul
>   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: P Jeram
  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: script to populate tables, i just wrote one

[Abdul Aleem]

Rahul,

This certainly is a good thing to have. Why not upload on a site for easy
access.

Aleem

 -Original Message-
Sent:   Monday, February 18, 2002 11:28 AM
To: Multiple recipients of list ORACLE-L
Subject:script to populate tables, i just wrote one

List, 
i just finished writing a script to generate random data to populate 
tables for load testing... the script takes into account the check
constraint
of a column and generate the correct/valid values. Also, a DEFAULT value of 
a column is also considered and generated accordingly. values for ALL the
columns
are generated (to keep it simple). I'm sure there are many scripts like this
on available on the
web.. but hey!!! i needed to jog the pl/sql side of my brain ;-)) 

mail me privately to request the script ...

regards

PS: please do not fill the list with "me too" messages




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  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: Abdul Aleem
  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: script to kill session

[Oberkofler, Dieter]

Title: RE: script to kill session





if you want to do this interactively you might also want to
have a look at a small shareware win32 tool called osession at
http://www.materialdreams.com/oracle/products.html


D


-Original Message-
From: Ruth Gramolini [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 12, 2002 16:04
To: Multiple recipients of list ORACLE-L
Subject: Re: script to kill session



Here is a shell script I use in UNIX, restrict_and_kill.  I included the sql
command to build the sql with the kill commands, killuser_build.  It
excludes users with restricted session access and puts the database into
restricted session mode.  You can modify these scripts to suit your needs.


HTH,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, February 11, 2002 4:58 PM



> Hi,
>
> We are looking for a script that will grep the sid from v$session and
> kill all the sessions of a particular user. The script will prompt the
> username and kill all the related sessions.
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California    -- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

Re: script to kill session

[Ruth Gramolini]

Here is a shell script I use in UNIX, restrict_and_kill.  I included the sql
command to build the sql with the kill commands, killuser_build.  It
excludes users with restricted session access and puts the database into
restricted session mode.  You can modify these scripts to suit your needs.

HTH,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, February 11, 2002 4:58 PM


> Hi,
>
> We are looking for a script that will grep the sid from v$session and
> kill all the sessions of a particular user. The script will prompt the
> username and kill all the related sessions.
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).



restrict_and_kill
Description: Binary data


killusers_build.sql
Description: Binary data

Re: Script stops running.

[Jared . Still]

You really need to ask this on a Perl list or in one
of the ontopic usenet Perl forums.

Check www.perl.com.

Jared






"Nguyen, David M" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/08/02 08:33 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Script stops running.


Hi all,

I get a perl script to telnet to several machines on network to check
something I want to check.  When it trys to connect a machine which is 
down,
the script will stop running.  How do I tell it to continue to check the
next machine and ignore the one down?.  Below is my script.

Thanks,
David

==
#!/usr/local/bin/perl -w

 
use Net::Telnet;

 
%city = qw(host1 passwd
   host2 passwd

host3 passwd
   host4 passwd );

 
$telnet = Net::Telnet->new

(

Timeout => 10,

Input_log => 'PSXreplication.log',

Prompt  => ('/[%:] $/'),

);

 

foreach $key (sort keys %city) {

 

$telnet->prompt("/$key%/i"); 
$telnet->open("$key");

$telnet->login('ssuser', "$city{$key}");

$telnet->prompt("/$key%/i");  
$telnet->cmd('ls -l'); 
print "=\n\n";

$telnet->close("$key");

 

} 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nguyen, David M
  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: Script to Disable Constraint, Change Value, then Enable Const

[Khedr, Waleed]

You 
have to create the constraints to support the deferred mode. Drop them and  
recreate them DEFERRABLE.
 
Also you 
can't do edit in SQLPLUS and put these three lines in one 
window.
Either put 
them in notepad and then cut and paste in sqlplus directly or create sql script 
that look like:
 
set 
constraints all deferred;
update 
table1 set host_name = 'tigerz' where host_name = 'tiger';
update 
table2 set host_name = 'tigerz' where host_name = 'tiger';
 
Regards,
 
Waleed
 

  -Original Message-From: David Wagoner 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 
  2001 1:15 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, 
  then Enable Const
  
  Rick, 
  thanks for your input.  When I 
  test the deferred constraint in SQL*Plus I get an error.  What am I doing wrong?  I did not find much information in the 
  docs about deferred constraints.
   
   
   
    SQL> set constraints all 
  deferred
      2  update table1 set host_name = 'tigerz' 
  where host_name = 'tiger'
      3* update table2 set host_name = 
  'tigerz' where host_name = 'tiger';
   
  SQL> 
  
  update 
  host set host_name = 'tigerz' where host_name = 
  'tiger'
  *
  ERROR 
  at line 2:
  ORA-00933: 
  SQL command not properly ended
   
   
   
  TIA,
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  -Original 
  Message-From: Cale, Rick 
  T (Richard) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 12:29 
  PMTo: Multiple recipients of 
  list ORACLE-LSubject: RE: 
  Script to Disable Constraint, Change Value, then Enable 
Const
   
  I have 
  had the same type of requirement and decided to use deferrable constraints. 
  
  Works 
  great!
   
  Rick
  -Original 
  Message-From: Igor 
  Neyman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 11:56 
  AMTo: Multiple recipients of 
  list ORACLE-LSubject: Re: 
  Script to Disable Constraint, Change Value, then Enable 
  Const
  Not good approach.
  Instead, use 'deferrable constraints, 
  should work in your situation.
   
  Igor Neyman, OCP DBA[EMAIL PROTECTED]  
  
  
  - Original 
  Message - 
  
  From: David Wagoner 
  
  
  To: Multiple 
  recipients of list ORACLE-L 
  Sent: Wednesday, November 
  28, 2001 11:24 AM
  Subject: RE: Script to 
  Disable Constraint, Change Value, then Enable Const
   
  I can see the 
  confusion here.  The point is not 
  to let someone enter data
  that would 
  violate the referential integrity.  
  Let me explain with an
  example:
   
  
  1.    
  User wants to 
  update a primary key record in parent table
  2.    
  Dependent 
  data exists in a child table so the user gets an error while trying to perform 
  step 1
  3.    
  It is 
  necessary to disable the FK constraint in order to update both 
  tables
  4.    
  Enable the FK 
  constraint successfully
  
   
   
  Does that 
  make sense?  This is a process we 
  have to do routinely and it has
  happened in 
  the past that the FK was mistakenly not re-enabled, which
  allowed 
  "illegal" data to be loaded later.  
  Thus the need for a script.
   
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  
  -Original 
  Message-From: Mark Leith 
  [mailto:[EMAIL PROTEC

RE: Script to Disable Constraint, Change Value, then Enable Const

[David Wagoner]

I know it
looks like a semi-colon error, but it’s not.  Please send me one of your examples that you know works and
I’ll modify and try here.

 

 

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor
Blvd., Suite 110

Durham, NC 27703

Tel. (919)
941-4645

Fax (919)
474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
***  NOTICE  ***

This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, work product or exempt from disclosure
under applicable law.  If you have
received this message in error, or are not the named recipient(s), please
immediately notify the sender at (919) 941-4645 and delete this e-mail message
from your computer.  Thank you.

 

-Original
Message-
From: Cale, Rick T (Richard)
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
2:35 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Const

 

David,

 

You need a semi-colon
after line2 i.e., update table1 ...;

 

Rick

-Original Message-
From: David Wagoner
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
1:16 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Const

Rick, thanks for your input.  When I test the deferred constraint in
SQL*Plus I get an error.  What am I
doing wrong?  I did not find much
information in the docs about deferred constraints.

 

 

 

  SQL>
set constraints all deferred

    2  update table1 set host_name = 'tigerz'
where host_name = 'tiger'

    3* update table2 set
host_name = 'tigerz' where host_name = 'tiger';

 

SQL> 

update host set host_name = 'tigerz' where host_name
= 'tiger'

*

ERROR at line 2:

ORA-00933: SQL command not properly ended

 

 

 

TIA,

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
***  NOTICE  ***

This e-mail message is confidential, intended only for the
named recipient(s) above and may contain information that is privileged, work
product or exempt from disclosure under applicable law.  If you have received this message in
error, or are not the named recipient(s), please immediately notify the sender
at (919) 941-4645 and delete this e-mail message from your computer.  Thank you.

 

-Original
Message-
From: Cale, Rick T (Richard)
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
12:29 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Const

 

I have
had the same type of requirement and decided to use deferrable constraints. 

Works
great!

 

Rick

-Original Message-
From: Igor Neyman
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
11:56 AM
To: Multiple recipients of list
ORACLE-L
Subject: Re: Script to Disable
Constraint, Change Value, then Enable Const

Not good approach.

Instead, use 'deferrable constraints,
should work in your situation.

 

Igor Neyman, OCP DBA
[EMAIL PROTECTED]
  



-
Original Message - 







From: David
Wagoner 



 





To: Multiple
recipients of list ORACLE-L 

Sent: Wednesday, November 28,
2001 11:24 AM

Subject: RE:
Script to Disable Constraint, Change Value, then Enable Const

 

I can see the
confusion here.  The point is not
to let someone enter data

that would violate
the referential integrity.  Let me
explain with an

example:

 





1.    User wants to update a primary key record in parent
table

2.    Dependent data exists in a child table so the user
gets an error while trying to perform step 1

3.    It is necessary to disable the FK constraint in order
to update both tables

4.    Enable the FK constraint successfully





 

 

Does that make
sense?  This is a process we have
to do routinely and it has

happened in the
past that the FK was mistakenly not re-enabled, which

allowed
"illegal" data to be loaded later.  Thus the need for a script.

 

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions
Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
***  NOTICE  ***

This e-mail message is confidential, intended
only for the named recipien

RE: Script to Disable Constraint, Change Value, then Enable Const

[Cale, Rick T (Richard)]

David,
 
You 
need a semi-colon after line2 i.e., update table1 ...;
 
Rick

  -Original Message-From: David Wagoner 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 
  2001 1:16 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, 
  then Enable Const
  
  Rick, 
  thanks for your input.  When I 
  test the deferred constraint in SQL*Plus I get an error.  What am I doing wrong?  I did not find much information in the 
  docs about deferred constraints.
   
   
   
    SQL> set constraints all 
  deferred
      2  update table1 set host_name = 'tigerz' 
  where host_name = 'tiger'
      3* update table2 set host_name = 
  'tigerz' where host_name = 'tiger';
   
  SQL> 
  
  update 
  host set host_name = 'tigerz' where host_name = 
  'tiger'
  *
  ERROR 
  at line 2:
  ORA-00933: 
  SQL command not properly ended
   
   
   
  TIA,
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  -Original 
  Message-From: Cale, Rick 
  T (Richard) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 12:29 
  PMTo: Multiple recipients of 
  list ORACLE-LSubject: RE: 
  Script to Disable Constraint, Change Value, then Enable 
Const
   
  I have 
  had the same type of requirement and decided to use deferrable constraints. 
  
  Works 
  great!
   
  Rick
  -Original 
  Message-From: Igor 
  Neyman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 11:56 
  AMTo: Multiple recipients of 
  list ORACLE-LSubject: Re: 
  Script to Disable Constraint, Change Value, then Enable 
  Const
  Not good approach.
  Instead, use 'deferrable constraints, 
  should work in your situation.
   
  Igor Neyman, OCP DBA[EMAIL PROTECTED]  
  
  
  - Original 
  Message - 
  
  From: David Wagoner 
  
  
  To: Multiple 
  recipients of list ORACLE-L 
  Sent: Wednesday, November 
  28, 2001 11:24 AM
  Subject: RE: Script to 
  Disable Constraint, Change Value, then Enable Const
   
  I can see the 
  confusion here.  The point is not 
  to let someone enter data
  that would 
  violate the referential integrity.  
  Let me explain with an
  example:
   
  
  1.    
  User wants to 
  update a primary key record in parent table
  2.    
  Dependent 
  data exists in a child table so the user gets an error while trying to perform 
  step 1
  3.    
  It is 
  necessary to disable the FK constraint in order to update both 
  tables
  4.    
  Enable the FK 
  constraint successfully
  
   
   
  Does that 
  make sense?  This is a process we 
  have to do routinely and it has
  happened in 
  the past that the FK was mistakenly not re-enabled, which
  allowed 
  "illegal" data to be loaded later.  
  Thus the need for a script.
   
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  
  -Original 
  Message-From: Mark Leith 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 5:20 
  AMTo: Multiple recipients of 
  list ORACLE-LSubject: RE: 
  Script to Disable Constraint, Change Value, then Enable 
  Constrain
   
  How could this be 
  user proof? You are essentially disabling the constraint that WILL enforce 
  data integrity, then letting the user input whatever rubbish he wants to, and 
  are then going to try and enable the constraint afterwards?
   
  Not a good appro

RE: Script to Disable Constraint, Change Value, then Enable Const

[David Wagoner]

Rick,
thanks for your input.  When I test
the deferred constraint in SQL*Plus I get an error.  What am I doing wrong? 
I did not find much information in the docs about deferred constraints.

 

 

 

  SQL> set constraints all deferred

    2  update table1 set host_name = 'tigerz' where host_name =
'tiger'

    3* update table2 set host_name = 'tigerz' where host_name =
'tiger';

 

SQL> 

update
host set host_name = 'tigerz' where host_name = 'tiger'

*

ERROR at
line 2:

ORA-00933:
SQL command not properly ended

 

 

 

TIA,

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor
Blvd., Suite 110

Durham, NC 27703

Tel. (919)
941-4645

Fax (919)
474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
***  NOTICE  ***

This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, work product or exempt from disclosure
under applicable law.  If you have
received this message in error, or are not the named recipient(s), please
immediately notify the sender at (919) 941-4645 and delete this e-mail message
from your computer.  Thank you.

 

-Original
Message-
From: Cale, Rick T (Richard)
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
12:29 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Const

 

I have had the same type
of requirement and decided to use deferrable constraints. 

Works great!

 

Rick

-Original Message-
From: Igor Neyman
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
11:56 AM
To: Multiple recipients of list
ORACLE-L
Subject: Re: Script to Disable
Constraint, Change Value, then Enable Const

Not good approach.

Instead, use 'deferrable constraints,
should work in your situation.

 

Igor Neyman, OCP DBA
[EMAIL PROTECTED]
  



-
Original Message - 



From: David
Wagoner 



To: Multiple
recipients of list ORACLE-L 

Sent: Wednesday, November 28,
2001 11:24 AM

Subject: RE:
Script to Disable Constraint, Change Value, then Enable Const

 

I can see the
confusion here.  The point is not
to let someone enter data

that would
violate the referential integrity. 
Let me explain with an

example:

 





1.    User wants to update a primary key record in parent
table

2.    Dependent data exists in a child table so the user
gets an error while trying to perform step 1

3.    It is necessary to disable the FK constraint in order
to update both tables

4.    Enable the FK constraint successfully





 

 

Does that make
sense?  This is a process we have
to do routinely and it has

happened in the
past that the FK was mistakenly not re-enabled, which

allowed
"illegal" data to be loaded later.  Thus the need for a script.

 

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions
Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
*** 
NOTICE  ***

This e-mail message is confidential, intended
only for the named recipient(s) above and may contain information that is
privileged, work product or exempt from disclosure under applicable law.  If you have received this message in
error, or are not the named recipient(s), please immediately notify the sender
at (919) 941-4645 and delete this e-mail message from your computer.  Thank you.

 





-Original Message-
From: Mark Leith
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
5:20 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Constrain

 

How could this be user proof? You are essentially disabling the
constraint that WILL enforce data integrity, then letting the user input
whatever rubbish he wants to, and are then going to try and enable the
constraint afterwards?

 

Not a good approach.. How can you ensure that the user hasn't put a
duplicate value in (unique constraint) or something else that might break the
constraint rule? The only way you are going to know is when you try and
re-enable the constraint it will fail.. 

 

I struggle to see why you would want to do this - do you have any
more info?





-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of David
Wagoner
Sent: 27 November 2001 21:30
To: Multiple recipients of list
ORACLE-L
Subject: Script to Disable
Constraint, Change Value, then Enable Constrain

Listers,

 

Does anyone have a script
that will do the following:

 





1.  
Ac

RE: Script to Disable Constraint, Change Value, then Enable Const

[Cale, Rick T (Richard)]

I have 
had the same type of requirement and decided to use deferrable constraints. 

Works 
great!
 
Rick

  -Original Message-From: Igor Neyman 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 
  11:56 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: Script to Disable Constraint, Change Value, then Enable 
  Const
  Not good approach.
  Instead, use 'deferrable constraints, should work in your 
  situation.
   
  Igor Neyman, OCP DBA[EMAIL PROTECTED]  
  
  
- Original Message - 
From: 
David Wagoner 
To: Multiple recipients of list ORACLE-L 

Sent: Wednesday, November 28, 2001 
11:24 AM
Subject: RE: Script to Disable 
Constraint, Change Value, then Enable Const


I can see 
the confusion here.  The point 
is not to let someone enter data
that would 
violate the referential integrity.  
Let me explain with an
example:
 
1.    
User wants 
to update a primary key record in parent table
2.    
Dependent 
data exists in a child table so the user gets an error while trying to 
perform step 1
3.    
It is 
necessary to disable the FK constraint in order to update both 
tables
4.    
Enable the 
FK constraint successfully
 
 
Does that 
make sense?  This is a process 
we have to do routinely and it has
happened in 
the past that the FK was mistakenly not re-enabled, which
allowed 
"illegal" data to be loaded later.  
Thus the need for a script.
 
 
David 
B. Wagoner
Database 
Administrator
Arsenal 
Digital Solutions Worldwide Inc.
4815 
Emperor Blvd., Suite 110
Durham, 
NC 27703
Tel. 
(919) 941-4645
Fax 
(919) 474-0735
Email 
mailto:[EMAIL PROTECTED]
Web 
http://www.arsenaldigital.com/
 
  
***  NOTICE  ***
This 
e-mail message is confidential, intended only for the named recipient(s) 
above and may contain information that is privileged, work product or exempt 
from disclosure under applicable law.  
If you have received this message in error, or are not the named 
recipient(s), please immediately notify the sender at (919) 941-4645 and 
delete this e-mail message from your computer.  Thank you.
 
-Original 
Message-From: Mark 
Leith [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 5:20 
AMTo: Multiple recipients 
of list ORACLE-LSubject: 
RE: Script to Disable Constraint, Change Value, then Enable 
Constrain
 
How 
could this be user proof? You are essentially disabling the constraint that 
WILL enforce data integrity, then letting the user input whatever rubbish he 
wants to, and are then going to try and enable the constraint 
afterwards?
 
Not a 
good approach.. How can you ensure that the user hasn't put a duplicate 
value in (unique constraint) or something else that might break the 
constraint rule? The only way you are going to know is when you try and 
re-enable the constraint it will fail.. 
 
I 
struggle to see why you would want to do this - do you have any more 
info?
-Original 
Message-From: 
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of David WagonerSent: 27 November 2001 
21:30To: Multiple 
recipients of list ORACLE-LSubject: Script to Disable Constraint, 
Change Value, then Enable Constrain
Listers,
 
Does 
anyone have a script that will do the 
following:
 
1.   
Accept 
user input for old data value 
2.   
Accept 
user input for new data value 
3.   
Disable 
table constraint 
4.   
Update 
record with new data value 
5.   
Enable 
constraint 

 
A 
script like this would help ensure that constraints are not left "off" after 
updates, allowing "illegal" data into the tables.  Good user-proof script I would 
think.
 
 
TIA,
 
david
 
David 
B. Wagoner
Database 
Administrator
Arsenal 
Digital Solutions Worldwide Inc.
4815 
Emperor Blvd., Suite 110
Durham, 
NC 27703
Tel. 
(919) 941-4645
Fax 
(919) 474-0735
Email 
mailto:[EMAIL PROTECTED]
Web 
http://www.arsenaldigital.com/
 
  
***  NOTICE  ***
This 
e-mail message is confidential, intended only for the named recipient(s) 
above and may contain information that is privileged, work product or exempt 
from disclosure under applicable law.  
If you

Re: Script to Disable Constraint, Change Value, then Enable Const

[Stephane Faroult]

As far as I remember, there is such a script at http://www.oriole.com,
in the X-rated section - as the name implies, not full-heartedly
recommended.
-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:+44  (0) 7050-696-449 
Performance Tools & Free Scripts
--
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Script to Disable Constraint, Change Value, then Enable Const

[tday6]

OK.  Here's what I do in that situation:

Here's the main driver

-- disable foreign keys on 
alter table CHILD1 disable constraint FK1;
alter table CHILD2 disable constraint FK2;
--run the detail_update.sql for each pair of values, ie, the current value
and the
-- value that it will be set to
@ex_detail_update
-- re-enable foreign keys on 
alter table CHILD1 enable constraint FK1;
alter table CHILD2 enable constraint FK2;

Here's the ex_detail_update.sql  - basically it will replace parameter 1
with parameter 2

@detail_update 1012 1013
@detail_update 1011 1012
@detail_update 1010 1011
@detail_update 1009 1010

Here's where the nitty gritty happens -- detail_update.sql

update table CHILD1 set FK_ID = &&2 where FK_ID = &&1;
update table CHILD2 set FK_ID = &&2 where FK_ID = &&1;
update table PARENT set PK_ID = &&2 where PK_ID = &&1;

COMMIT
/

When it's done it falls out to the next set of pairs.  When you run out of
pairs then the top level script will re-enable your constraints.



   

David Wagoner  



al.com>  cc:           

Sent by: rootSubject: RE: Script to Disable 
Constraint,
 Change Value, then Enable Const   

   

11/28/2001 

11:24 AM   

Please 

respond to 

ORACLE-L   

   

   







I can see the confusion here.  The point is not to let someone enter data

that would violate the referential integrity.  Let me explain with an

example:



1.    User wants to update a primary key record in parent table

2.    Dependent data exists in a child table so the user gets an error
while trying to perform step

3.    It is necessary to disable the FK constraint in order to update
both tables

4.    Enable the FK constraint successfully





Does that make sense?  This is a process we have to do routinely and it has

happened in the past that the FK was mistakenly not re-enabled, which

allowed "illegal" data to be loaded later.  Thus the need for a script.





David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/



***  NOTICE  ***

This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged, work
product or exempt from disclosure under applicable law.  If you have
received this message in error, or are not the named recipient(s), please
immediately notify the sender at (919) 941-4645 and delete this e-mail
message from your computer.  Thank you.



-Original Message-
Sent: Wednesday, November 28, 2001 5:20 AM
To: Multiple recipients of list ORACLE-L
Constrain



How could this be user proof? You are essentially disabling the constraint
that WILL enforce data integrity, then letting the user input whatever
rubbish he wants to, and are then going to try and enable the constraint
afterwards?



Not a good approach.. How can you ensure that the user hasn't put a
duplicate value in (unique constraint) or something else that might break
the constraint rule? The only way you are going to know is when you try and
re-enable the constraint it will fail..



I struggle to see why you would want to do this - do you have any more
info?

-Original Message-
ner
Sent: 27 November 2001 21:30
To: Multiple recipients of list ORACLE-L
ain

Listers,



Does anyone have a script that will do the following:



1.   Accept user input for old data value

2.   Accept user input for new data value

3.   Disable table constraint

4.   Update record with new data value

5.   Enable constraint



A script like this would help ensure that constraints are not left "off"
after updates, allowing "illegal" data into the tables.  Good user-proof
script I would think.





TIA,



david



Da

Re: Script to Disable Constraint, Change Value, then Enable Const

[Igor Neyman]

Not good approach.
Instead, use 'deferrable constraints, should work in your 
situation.
 
Igor Neyman, OCP DBA[EMAIL PROTECTED]  


  - Original Message - 
  From: 
  David Wagoner 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, November 28, 2001 11:24 
  AM
  Subject: RE: Script to Disable 
  Constraint, Change Value, then Enable Const
  
  
  I can see the 
  confusion here.  The point is not 
  to let someone enter data
  that would 
  violate the referential integrity.  
  Let me explain with an
  example:
   
  1.    
  User wants to 
  update a primary key record in parent table
  2.    
  Dependent 
  data exists in a child table so the user gets an error while trying to perform 
  step 1
  3.    
  It is 
  necessary to disable the FK constraint in order to update both 
  tables
  4.    
  Enable the FK 
  constraint successfully
   
   
  Does that 
  make sense?  This is a process we 
  have to do routinely and it has
  happened in 
  the past that the FK was mistakenly not re-enabled, which
  allowed 
  "illegal" data to be loaded later.  
  Thus the need for a script.
   
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   
  -Original 
  Message-From: Mark Leith 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 5:20 
  AMTo: Multiple recipients of 
  list ORACLE-LSubject: RE: 
  Script to Disable Constraint, Change Value, then Enable 
  Constrain
   
  How 
  could this be user proof? You are essentially disabling the constraint that 
  WILL enforce data integrity, then letting the user input whatever rubbish he 
  wants to, and are then going to try and enable the constraint 
  afterwards?
   
  Not a 
  good approach.. How can you ensure that the user hasn't put a duplicate value 
  in (unique constraint) or something else that might break the constraint rule? 
  The only way you are going to know is when you try and re-enable the 
  constraint it will fail.. 
   
  I 
  struggle to see why you would want to do this - do you have any more 
  info?
  -Original 
  Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of David WagonerSent: 27 November 2001 21:30To: Multiple recipients of list 
  ORACLE-LSubject: Script to 
  Disable Constraint, Change Value, then Enable Constrain
  Listers,
   
  Does 
  anyone have a script that will do the 
  following:
   
  1.   
  Accept 
  user input for old data value 
  2.   
  Accept 
  user input for new data value 
  3.   
  Disable 
  table constraint 
  4.   
  Update 
  record with new data value 
  5.   
  Enable 
  constraint 
  
   
  A 
  script like this would help ensure that constraints are not left "off" after 
  updates, allowing "illegal" data into the tables.  Good user-proof script I would 
  think.
   
   
  TIA,
   
  david
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   

RE: Script to Disable Constraint, Change Value, then Enable Const

[David Wagoner]

I can see the confusion here.  The point is not to let someone enter
data

that would violate the referential integrity.  Let me explain with an

example:

 

1.   
User wants to
update a primary key record in parent table

2.   
Dependent data
exists in a child table so the user gets an error while trying to perform step
1

3.   
It is necessary
to disable the FK constraint in order to update both tables

4.   
Enable the FK
constraint successfully

 

 

Does that make sense?  This is a process we have to do routinely and it has

happened in the past that the FK was mistakenly not
re-enabled, which

allowed "illegal" data to be loaded later.  Thus the need for a script.

 

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor
Blvd., Suite 110

Durham, NC 27703

Tel. (919)
941-4645

Fax (919)
474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
***  NOTICE  ***

This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, work product or exempt from disclosure
under applicable law.  If you have
received this message in error, or are not the named recipient(s), please
immediately notify the sender at (919) 941-4645 and delete this e-mail message
from your computer.  Thank you.

 

-Original
Message-
From: Mark Leith
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001
5:20 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Constrain

 

How could this be user
proof? You are essentially disabling the constraint that WILL enforce data
integrity, then letting the user input whatever rubbish he wants to, and are
then going to try and enable the constraint afterwards?

 

Not a good approach.. How
can you ensure that the user hasn't put a duplicate value in (unique
constraint) or something else that might break the constraint rule? The only
way you are going to know is when you try and re-enable the constraint it will
fail.. 

 

I struggle to see why you
would want to do this - do you have any more info?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of David
Wagoner
Sent: 27 November 2001 21:30
To: Multiple recipients of list
ORACLE-L
Subject: Script to Disable
Constraint, Change Value, then Enable Constrain

Listers,

 

Does anyone have a script that will do the following:

 

1.   Accept user input for old
data value 

2.   Accept user input for new
data value 

3.   Disable table constraint 

4.   Update record with new data
value 

5.   Enable constraint 

 

A script like this would help ensure that constraints
are not left "off" after updates, allowing "illegal" data
into the tables.  Good user-proof
script I would think.

 

 

TIA,

 

david

 

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email mailto:[EMAIL PROTECTED]

Web http://www.arsenaldigital.com/

 

 
*** 
NOTICE  ***

This e-mail message is confidential, intended only for the
named recipient(s) above and may contain information that is privileged, work
product or exempt from disclosure under applicable law.  If you have received this message in
error, or are not the named recipient(s), please immediately notify the sender
at (919) 941-4645 and delete this e-mail message from your computer.  Thank you.

 

RE: Script to Disable Constraint, Change Value, then Enable Const

[Hallas John]

Well 
said Mark.
David, 
you could also re-able the constraint 'novalidate' if you wanted to ignore the 
validity of any new input. 
If 
that was the case then why have an integrity constraint in the first 
place?
 
John

  -Original Message-From: Mark Leith 
  [mailto:[EMAIL PROTECTED]]Sent: 28 November 2001 
  10:20To: Multiple recipients of list ORACLE-LSubject: 
  RE: Script to Disable Constraint, Change Value, then Enable 
  Constrain
  How 
  could this be user proof? You are essentially disabling the constraint that 
  WILL enforce data integrity, then letting the user input whatever rubbish he 
  wants to, and are then going to try and enable the constraint 
  afterwards?
   
  Not 
  a good approach.. How can you ensure that the user hasn't put a duplicate
  value in (unique constraint) or something else that might break the constraint 
  rule? The only way you are going to know is when you try and re-enable the 
  constraint it will fail.. 
   
  I 
  struggle to see why you would want to do this - do you have any 
  more info?
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]On Behalf Of David WagonerSent: 
27 November 2001 21:30To: Multiple recipients of list 
ORACLE-LSubject: Script to Disable Constraint, Change Value, then 
Enable Constrain

Listers,
 
Does 
anyone have a script that will do the 
following:
 

  Accept 
  user input for old data value 
  Accept 
  user input for new data value 
  Disable 
  table constraint 
  Update 
  record with new data value 
  Enable 
  constraint 
 
A 
script like this would help ensure that constraints are not left “off” after 
updates, allowing “illegal” data into the tables.  Good user-proof script I would 
think.
 
 
TIA,
 
david
 
David 
B. Wagoner
Database 
Administrator
Arsenal 
Digital Solutions Worldwide Inc.
4815 
Emperor Blvd., Suite 110
Durham, 
NC 27703
Tel. 
(919) 941-4645
Fax 
(919) 474-0735
Email 
mailto:[EMAIL PROTECTED]
Web 
http://www.arsenaldigital.com/
 
  
***  NOTICE  ***
This 
e-mail message is confidential, intended only for the named recipient(s) 
above and may contain information that is privileged, work product or exempt 
from disclosure under applicable law.  
If you have received this message in error, or are not the named
recipient(s), please immediately notify the sender at (919) 941-4645 and 
delete this e-mail message from your computer.  Thank you.
 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
This electronic message contains information from the mmO2 plc Group 
which may be privileged or confidential. The information is intended to be 
for the use of the individual(s) or entity named above. If you are not the 
intended recipient be aware that any disclosure, copying, distribution or 
use of the contents of this information is prohibited. If you have received 
this electronic message in error, please notify us by telephone or email 
(to the numbers or address above) immediately.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Re: Script to Disable Constraint, Change Value, then Enable Constrain

[Connor McDonald]

If the old and new data are both intended to be valid
- why do you need to disable the constraint?

If its a referential integrity issue - maybe a
deferrable constraint (8.0+) is what you are after.

hth
connor

 --- David Wagoner <[EMAIL PROTECTED]>
wrote: > Listers,
>  
> Does anyone have a script that will do the
> following:
>  
> 1.Accept user input for old data value
> 2.Accept user input for new data value
> 3.Disable table constraint
> 4.Update record with new data value
> 5.Enable constraint
>  
> A script like this would help ensure that
> constraints are not left "off"
> after updates, allowing "illegal" data into the
> tables.  Good user-proof
> script I would think.
>  
>  
> TIA,
>  
> david
>  
> David B. Wagoner
> Database Administrator
> Arsenal Digital Solutions Worldwide Inc.
> 4815 Emperor Blvd., Suite 110
> Durham, NC 27703
> Tel. (919) 941-4645
> Fax (919) 474-0735
> Email  
> mailto:[EMAIL PROTECTED]
> Web  
> http://www.arsenaldigital.com/
>  
>  
> ***  NOTICE  ***
> This e-mail message is confidential, intended only
> for the named
> recipient(s) above and may contain information that
> is privileged, work
> product or exempt from disclosure under applicable
> law.  If you have
> received this message in error, or are not the named
> recipient(s), please
> immediately notify the sender at (919) 941-4645 and
> delete this e-mail
> message from your computer.  Thank you.
>  
>  

=
Connor McDonald
http://www.oracledba.co.uk (mirrored at 
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"

__
Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music 
Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  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: Script to Disable Constraint, Change Value, then Enable Constrain

[Mark Leith]

How 
could this be user proof? You are essentially disabling the constraint that WILL 
enforce data integrity, then letting the user input whatever rubbish he wants 
to, and are then going to try and enable the constraint 
afterwards?
 
Not a 
good approach.. How can you ensure that the user hasn't put a duplicate value in 
(unique constraint) or something else that might break the constraint rule? The 
only way you are going to know is when you try and re-enable the constraint it 
will fail.. 
 
I 
struggle to see why you would want to do this - do you have any 
more info?

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of David WagonerSent: 27 
  November 2001 21:30To: Multiple recipients of list 
  ORACLE-LSubject: Script to Disable Constraint, Change Value, then 
  Enable Constrain
  
  Listers,
   
  Does 
  anyone have a script that will do the 
  following:
   
  
Accept 
user input for old data value 
Accept 
user input for new data value 
Disable 
table constraint 
Update 
record with new data value 
Enable 
constraint 
   
  A 
  script like this would help ensure that constraints are not left “off” after 
  updates, allowing “illegal” data into the tables.  Good user-proof script I would 
  think.
   
   
  TIA,
   
  david
   
  David 
  B. Wagoner
  Database 
  Administrator
  Arsenal 
  Digital Solutions Worldwide Inc.
  4815 
  Emperor Blvd., Suite 110
  Durham, 
  NC 27703
  Tel. 
  (919) 941-4645
  Fax 
  (919) 474-0735
  Email 
  mailto:[EMAIL PROTECTED]
  Web 
  http://www.arsenaldigital.com/
   
    
  ***  NOTICE  ***
  This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, work product or exempt from 
  disclosure under applicable law.  
  If you have received this message in error, or are not the named 
  recipient(s), please immediately notify the sender at (919) 941-4645 and 
  delete this e-mail message from your computer.  Thank you.
   

RE: Script for next_extent of objects <= free space available

[nlzanen1]

Hi,


I have this Space Bound object script that will list every table that can
not allocate it's next extent. It will not list tables in tablespace that
is set to autoextent.
This is not entirely correct as you may have set an upper limit to the auto
extent (if you feel like adding it in please send back)


SELECT s.owner owner, s.segment_name object, f.name TABLESPACE,
s.segment_type TYPE,
   so.object_id obj#, round((s.blocks * f.blocksize) / (1024 * 1024),2)
objmbytes,
   s.extents extents, s.initial_extent iniexts, s.min_extents minexts,
   s.max_extents maxexts, s.next_extent nextextsize,
   round((s.next_extent  / (1024*1024)),2) nextextmbytes,
   s.pct_increase extpct,
   DECODE (s.max_extents - s.extents,
  0, 'Max Extents Reached',
  DECODE (LEAST (s.next_extent, f.totfreeblocks) - s.next_extent,
 0, 'Fragmented Free Space', 'Insufficient Free Space'
  )
   ) reason
  FROM DBA_SEGMENTS s,
   (  SELECT a.tablespace_name AS name, c.value AS blocksize,
   NVL (MAX (b.blocks*c.value), 0) AS maxfreeblocks,
   NVL (SUM (b.blocks*c.value), 0) AS totfreeblocks,
   NVL (SUM (b.blocks / b.blocks), 0) AS freeextents
  FROM DBA_TABLESPACES a, DBA_FREE_SPACE b ,v$parameter c
 WHERE a.tablespace_name = b.tablespace_name
   AND c.name = 'db_block_size'
 GROUP BY a.tablespace_name, c.value) f,
   DBA_OBJECTS so,
 (  SELECT tablespace_name,max(autoextensible) auto
   FROM dba_data_files GROUP BY tablespace_name) df
 WHERE (
 s.next_extent > f.maxfreeblocks
  OR (s.extents >= s.max_extents
  AND s.max_extents != 0)
   )
   AND s.tablespace_name = f.name
   AND so.owner = s.owner
   AND so.object_name = s.segment_name
   AND df.tablespace_name=s.tablespace_name
   AND df.auto='NO'
/


Hope this is what you can use

Jack

=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
=





--
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: Script for next_extent of objects <= free space available

[CHAN Chor Ling Catherine (CSC)]

Hi Gerardo,

I notice that in your script the free space is also derived from
dba_free_space (same as my script). Although I issued the command alter
database set autoextend on next ??M for the datafile, the table
dba_free_space does not reflect this changes. 

Any other advice ? Thanks.

Regds,
New Bee
-Original Message-
From:   Molina, Gerardo [mailto:[EMAIL PROTECTED]]
Sent:   Wednesday, October 31, 2001 4:20 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Script for next_extent of objects <=
free space available

Try this query to show objects whose next extent is larger
than the largest
free extent in the respective tablespace.

set linesize 120
col owner format a10
col segment_name format a20
col type format a7
col tablespace format a15
select a.tablespace_name tablespace,
segment_type type,owner,segment_name,
a.next_extent/1024 next,big_chunk from dba_segments a,
(select tablespace_name, max(bytes/1024)
as big_chunk from dba_free_space
group by tablespace_name ) b, dba_tablespaces c
where a.segment_type in ('TABLE','INDEX')
and b.tablespace_name = a.tablespace_name
and a.tablespace_name = c.tablespace_name
and c.status != 'READ ONLY'
and a.next_extent/1024 > b.big_chunk;

HTH
Gerardo
-Original Message-
Sent: Tuesday, October 30, 2001 11:10 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

I am looking for a script that shows the list of tables with
next extent >=
the free space available in the tablespace. Does anyone have
the script ?

I write a script displaying the list of table-spaces with
inadequate space
for the next extent of the table

SELECT F.TABLESPACE_NAME,NEXT_EXTENT,BYTES FREE_SPACE
FROM (SELECT TABLESPACE_NAME,MAX(NEXT_EXTENT) NEXT_EXTENT
FROM DBA_TABLES GROUP BY TABLESPACE_NAME) T,
 (SELECT TABLESPACE_NAME,MAX(BYTES) BYTES
  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
AND  F.BYTES <= T.NEXT_EXTENT

Example :

TABLESPACE_NAMENEXT_EXTENT FREE_SPACE

-- --- --

XTRD   83886083112960


Based on the example above, although I alter database to set
the data file
pertaining to the tablespace_name XTRD autoextend on for the
next 10M, 
the bytes in dba_free_space will still reflect as 3112960.
My script will
not work.

Any advise ? Thanks.


SQL> SELECT * FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME='XTRD';

FILE_NAME



  FILE_ID TABLESPACE_NAMEBYTESBLOCKS
STATUS
RELATIVE_FNO AUT  MAXBYTES
- -- - -
-
 --- -
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-  -- ---
/dg7/app/oracle/testdata/xtrd01.dbf
   29 XTRD20971520  2560
AVAILABLE
29 NO  0
00   208896002550


I issued the command to set autoextend on for the datafile
'/dg7/app/oracle/testdata/xtrd01.dbf'.


SQL>  SELECT * FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME='XTRD';

FILE_NAME


---
  FILE_ID TABLESPACE_NAMEBYTESBLOCKS
STATUS
RELATIVE_FNO AUT  MA
- -- - -
-
 --- ---
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-  -- ---
/dg7/app/oracle/testdata/xtrd01.dbf
   29 XTRD20971520  2560
AVAILABLE
  

RE: Script for next_extent of objects <= free space available

[Molina, Gerardo]

Try this query to show objects whose next extent is larger than the largest
free extent in the respective tablespace.

set linesize 120
col owner format a10
col segment_name format a20
col type format a7
col tablespace format a15
select a.tablespace_name tablespace,
segment_type type,owner,segment_name,
a.next_extent/1024 next,big_chunk from dba_segments a,
(select tablespace_name, max(bytes/1024)
as big_chunk from dba_free_space
group by tablespace_name ) b, dba_tablespaces c
where a.segment_type in ('TABLE','INDEX')
and b.tablespace_name = a.tablespace_name
and a.tablespace_name = c.tablespace_name
and c.status != 'READ ONLY'
and a.next_extent/1024 > b.big_chunk;

HTH
Gerardo
-Original Message-
Sent: Tuesday, October 30, 2001 11:10 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

I am looking for a script that shows the list of tables with next extent >=
the free space available in the tablespace. Does anyone have the script ?

I write a script displaying the list of table-spaces with inadequate space
for the next extent of the table

SELECT F.TABLESPACE_NAME,NEXT_EXTENT,BYTES FREE_SPACE
FROM (SELECT TABLESPACE_NAME,MAX(NEXT_EXTENT) NEXT_EXTENT
FROM DBA_TABLES GROUP BY TABLESPACE_NAME) T,
 (SELECT TABLESPACE_NAME,MAX(BYTES) BYTES
  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
AND  F.BYTES <= T.NEXT_EXTENT

Example :

TABLESPACE_NAMENEXT_EXTENT FREE_SPACE

-- --- --

XTRD   83886083112960


Based on the example above, although I alter database to set the data file
pertaining to the tablespace_name XTRD autoextend on for the next 10M, 
the bytes in dba_free_space will still reflect as 3112960. My script will
not work.

Any advise ? Thanks.


SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='XTRD';

FILE_NAME


  FILE_ID TABLESPACE_NAMEBYTESBLOCKS STATUS
RELATIVE_FNO AUT  MAXBYTES
- -- - - -
 --- -
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-  -- ---
/dg7/app/oracle/testdata/xtrd01.dbf
   29 XTRD20971520  2560 AVAILABLE
29 NO  0
00   208896002550


I issued the command to set autoextend on for the datafile
'/dg7/app/oracle/testdata/xtrd01.dbf'.


SQL>  SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='XTRD';

FILE_NAME

---
  FILE_ID TABLESPACE_NAMEBYTESBLOCKS STATUS
RELATIVE_FNO AUT  MA
- -- - - -
 --- ---
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-  -- ---
/dg7/app/oracle/testdata/xtrd01.dbf
   29 XTRD20971520  2560 AVAILABLE
29 YES 3.4
  4194302 2560   208896002550


SQL> SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='XTRD';

TABLESPACE_NAME  FILE_ID  BLOCK_ID BYTESBLOCKS
RELATIVE_FNO
-- - - - -

XTRD  29  2179   3112960   380
29


Regds,
New bee
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  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: Molina, Gerardo
  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: Script for checking temp usage

[Jenkins, Michael]

Thanks to everyone for all the help.

-Original Message-
Sent: Monday, September 10, 2001 1:35 PM
To: Multiple recipients of list ORACLE-L


try this query:

SELECT s.username, s.serial#, s.sid, u.tablespace, u.contents, u.extents,
u.blocks
 FROM v$session s, v$sort_usage u
 WHERE s.saddr=u.session_addr;


Lucy



On Mon, 10 Sep 2001, Jenkins, Michael wrote:

> Does anybody have a script that will tell you which session_ids are using
> temp and how much they are using?  We occasionally get runaway sessions
that
> don't release temp and this would allow us to easily locate the offending
> session.
> 
> I've already checked OraMag and used google with no significant results.
> 
> Thanks in advance.
> 
> --Michael
> 

-- 
Lucy Lin Oracle DBA

condenet.com 212-286-3852

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lucy Lin
  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: Jenkins, Michael
  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: Script for checking temp usage

[Christian Trassens]

Since 8.X joining v$sort_usage and v$session. In 7.X
Metalink had an script that I enclose.

When the query is running, you can issue a query over
v$session_wait. Looking for the event 'db file
sequential read' where p1 includes all the files of
the temp tablespace.

Regards.




--- "Jenkins, Michael" <[EMAIL PROTECTED]>
wrote:
> Does anybody have a script that will tell you which
> session_ids are using
> temp and how much they are using?  We occasionally
> get runaway sessions that
> don't release temp and this would allow us to easily
> locate the offending
> session.
> 
> I've already checked OraMag and used google with no
> significant results.
> 
> Thanks in advance.
> 
> --Michael
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Jenkins, Michael
>   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).


=
Eng. Christian Trassens
Senior DBA
Systems Engineer
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : 541149816062

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
 findtemp.sql

RE: Script for checking temp usage

[Christopher Spence]

Have you looked at v$sort_usage?  (8i only)

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 



-Original Message-
Sent: Monday, September 10, 2001 12:20 PM
To: Multiple recipients of list ORACLE-L


Does anybody have a script that will tell you which session_ids are using
temp and how much they are using?  We occasionally get runaway sessions that
don't release temp and this would allow us to easily locate the offending
session.

I've already checked OraMag and used google with no significant results.

Thanks in advance.

--Michael
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jenkins, Michael
  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: Christopher Spence
  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: Script for checking temp usage

[Seley, Linda]

Michael -

I've had similar problems (although the pain isn't high enough for me to get
in there and fix them yet).  To do this I've set up a cron job to check
every 15 minutes.  The script I run is:

set lines 100 pages 87

column username format a10
column tablespace format a6 heading 'TBSP'
column segfile# format 999 heading 'FILE#'
column extents format 9 heading 'EXT'
column blocks format 999

spool /tmp/sort&1

select s.username,
  su.session_addr,
  su.session_num,
  su.sqladdr,
  su.tablespace,
  su.contents,
  su.segtype,
  su.segfile#,
  sum(su.extents) extents,
  sum(su.blocks) blocks, 
  sum((su.blocks*p.value)/1024/1024) M,
  sa.sql_text
from v$sort_usage su, 
  v$session s, 
  v$sqlarea sa, 
  v$parameter p
where su.session_num = s.serial#
  and s.sql_address=sa.address
  and p.name = 'db_block_size'
  and s.username <> 'SYS'
group by s.username,
  su.session_addr,
  su.session_num,
  su.sqladdr,
  su.tablespace,
  su.contents,
  su.segtype,
  su.segfile#,
  sa.sql_text
/
select *
from v$sort_segment
/

exit;

My ksh script checks to see if there are rows returned and if so sends me
the information.  

HTH!

Linda

-Original Message-
Sent: Monday, September 10, 2001 10:20 AM
To: Multiple recipients of list ORACLE-L


Does anybody have a script that will tell you which session_ids are using
temp and how much they are using?  We occasionally get runaway sessions that
don't release temp and this would allow us to easily locate the offending
session.

I've already checked OraMag and used google with no significant results.

Thanks in advance.

--Michael
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jenkins, Michael
  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: Seley, Linda
  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: Script for checking temp usage

[Lucy Lin]

try this query:

SELECT s.username, s.serial#, s.sid, u.tablespace, u.contents, u.extents,
u.blocks
 FROM v$session s, v$sort_usage u
 WHERE s.saddr=u.session_addr;


Lucy



On Mon, 10 Sep 2001, Jenkins, Michael wrote:

> Does anybody have a script that will tell you which session_ids are using
> temp and how much they are using?  We occasionally get runaway sessions that
> don't release temp and this would allow us to easily locate the offending
> session.
> 
> I've already checked OraMag and used google with no significant results.
> 
> Thanks in advance.
> 
> --Michael
> 

-- 
Lucy Lin Oracle DBA

condenet.com 212-286-3852

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lucy Lin
  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: Script to Generate SQL To Recreate All Tables and Indexes for Sch

[mohammed bhatti]

I've got a couple of PL/SQL scripts that grab the
table and index DDL and dump out to a file.  They'll
do it for a particular schema.  I'm also in middle of
converting them over to perl so I'll send that to you
as well.  Let me know if you have questions.

hth

mkb

--- Sam Bootsma <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> Can anybody help me with a script or tool that will
> generate SQL to recreate
> all tables and all indexes in a schema?  I DO NOT
> want the script to
> generate storage parameters.  
> 
> If anybody has a script that does this, or can
> suggest a free tool, please
> let me know.  I need to recreate all tables and
> indexes for about 20
> schemas.  I need the tables and indexes to inherit
> storage parameters from
> the tablespace.  My plan is as follows:
> 1) Create dump file of each of the existing schema's
> 2) Run the script to generate SQL to create all
> tables and all indexes in
> the schema
> 3) Drop the original schema using cascade option
> 4) Recreate the schema with appropriate privileges
> (schema will be empty at
> this point with no objects)
> 5) Precreate the tables and indexes (with no storage
> parameters) using the
> script generated in step 2)
> 6) Import (specifying ignore=y) data into the
> pre-created tables using the
> dump file created in step 1)
> 
> We are running Oracle 8.1.6 on Windows NT. 
> 
> Thanks!
> 
> Sam Bootsma, OCP
> Technical Support Analyst
> CPAS Systems Inc.
> 416-422-0563 x237
> [EMAIL PROTECTED]
> http://www.cpas.com
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Sam Bootsma
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: mohammed bhatti
  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: Script to Generate SQL To Recreate All Tables and Indexes for

[Christopher Spence]

Take a look at my site, www.vampired.net, you will find a script to do both,
it can be modified pretty easily to remove the storage clauses.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 



-Original Message-
Sent: Friday, September 07, 2001 1:06 PM
To: Multiple recipients of list ORACLE-L


Hello,

Can anybody help me with a script or tool that will generate SQL to recreate
all tables and all indexes in a schema?  I DO NOT want the script to
generate storage parameters.  

If anybody has a script that does this, or can suggest a free tool, please
let me know.  I need to recreate all tables and indexes for about 20
schemas.  I need the tables and indexes to inherit storage parameters from
the tablespace.  My plan is as follows:
1) Create dump file of each of the existing schema's
2) Run the script to generate SQL to create all tables and all indexes in
the schema
3) Drop the original schema using cascade option
4) Recreate the schema with appropriate privileges (schema will be empty at
this point with no objects)
5) Precreate the tables and indexes (with no storage parameters) using the
script generated in step 2)
6) Import (specifying ignore=y) data into the pre-created tables using the
dump file created in step 1)

We are running Oracle 8.1.6 on Windows NT. 

Thanks!

Sam Bootsma, OCP
Technical Support Analyst
CPAS Systems Inc.
416-422-0563 x237
[EMAIL PROTECTED]
http://www.cpas.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sam Bootsma
  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: Christopher Spence
  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: Script to report rman backup

[Ruth Gramolini]

 I am using rman on UNIX so I am not sure how it works for NT.
The rman log will have this information.  You can spool it to disk.

HTH,
Ruth

--- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, July 16, 2001 11:20 AM


>
>
> Hi
>
> Does anyone have any scripts which reports on the backups, time taken
etc??
>
> Any help/pointers would be appreciated
>
> Rgds
>
> Fawzia
> ___
>
> This message is confidential and is intended for the addressee only;
> unless clearly stated that this disclaimer should not apply, this
> e-mail is not intended to create legally binding commitments on
> behalf of any company in the British Interactive Broadcasting
> Holdings Limited group,  nor do its contents reflect the corporate
> views or policies of any such company. Any unauthorised disclosure,
> use or dissemination, either whole or partial, is prohibited. If you
> are not the intended recipient of the message, please notify the
> sender immediately.
> ___
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Malik, Fawzia
>   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: Script to find space bound objects

[Naik, Kevin K]

Title: RE: Script to find space bound objects





This is my two pence


select a.tablespace_name,a.segment_name,a.segment_type,a.su ,b.sf from
    (select tablespace_name,segment_name,segment_type,next_extent/(1024*1024) su from dba_segments) a,
    (select tablespace_name,max(bytes/(1024*1024)) sf from dba_free_space group by tablespace_name) b
    where a.tablespacE_name=b.tablespace_name
    and a.su > b.sf;


Kevin Naik


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 26 June, 2001 16:31
To: Multiple recipients of list ORACLE-L
Subject: Script to find space bound objects



Hi All,



I thought I had a script to check for space bound objects (can not allocate
next extent for whatever reason) but seem to have misplaced it.


Anybody want to save me the time?


TIA



Jack


=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.


Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.


Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.


If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.


In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
=






-- 
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).





__
Disclaimer and 
confidentiality note
Everything in this e-mail and 
any attachments relating to the official business of Standard Bank Investment 
Corporation (Stanbic) is proprietary to the company. It is confidential, legally 
privileged and protected by law. Stanbic does not own and endorse any other 
content. Views and opinions are those of the sender unless clearly stated as 
being that of Stanbic. 
The person addressed in the 
e-mail is the sole authorised recipient. Please notify the sender immediately if 
it has unintentionally reached you and do not read, disclose or use the content 
in any way.

Stanbic can not assure that the integrity of this communication has been 
maintained nor that it is free of errors, virus, interception or 
interference.
 ___

RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Gogala, Mladen]

The other  really cool free tool is "tora" (Tool for ORAcle). It's free and
it works even on
Windoze platforms. Tora can be found through slashdot (go to "slashdot.org"
and
type  "tora" in the "search freshmeat" field. I tested the app on RH 6.2,
Slackware and 
Windoze platforms and it works on all of them.
 

-Original Message-
Sent: Monday, July 02, 2001 3:40 PM
To: Multiple recipients of list ORACLE-L



Bunyamin, 
Download Toad (www.toadsoft.com).  It does it for you.  THe only thing it
doesn't handle (from what I remember) is partitioning. 

HTH 
Lisa Koivu 
Data Bored Administrator 
Ft. Lauderdale, FL, 

-Original Message- 
Sent:   Monday, July 02, 2001 1:46 PM 
To: Multiple recipients of list ORACLE-L 


Hi Gurus, 
I do not have time to write it. 
IF you have a script creating 
   the create table scripts of a schema, I will be so happy . 
Thanks 
Bunyamin 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  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: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Norwood Bradly A]

Robert:
 
You 
might try comparing your script to one of the old reverse engineering scripts on 
www.oramag.com.
-Original Message-From: Yexley Robert D SSgt AFIT/SCA 
[mailto:[EMAIL PROTECTED]]Sent: Monday, July 02, 2001 3:40 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.
I wanted to throw this out there for anyone/everyone, whatever.  
Being the struggling, wannabe DBA that I am, if anyone gets a chance (or would 
be willing, just to help me out) to take a look at this script (the one attached 
to the original message) and provide any feedback/observations as to how it 
could be improved, I would certainly be open and 
appreciative.
 
Direct responses would probably be most appropriate.  Thanks for 
your time.
 
-::YEX::-
<)))><

  -Original Message-From: Yexley Robert D SSgt 
  AFIT/SCA [mailto:[EMAIL PROTECTED]]Sent: Monday, 02 July, 2001 
  14:51 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A 
  SCHEMA.
  
  I just finished this the other day.  It works for individual 
  tables only, but you could pretty easily modify it to work for all of the 
  tables within a schema.  Hope it helps.
   
  -::YEX::-
  <)))><
   
   
  
-Original Message-From: Bunyamin K. Karadeniz 
[mailto:[EMAIL PROTECTED]]Sent: Monday, 02 July, 2001 
13:46 PMTo: Multiple recipients of list 
ORACLE-LSubject: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS 
OF A SCHEMA.
Hi Gurus,
I do not have time to write it.
IF you have a script creating 
   
the create table scripts of a schema, I will be so happy .
Thanks 
Bunyamin

RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Yexley Robert D SSgt AFIT/SCA]

I wanted to throw this out there for anyone/everyone, whatever.  
Being the struggling, wannabe DBA that I am, if anyone gets a chance (or would 
be willing, just to help me out) to take a look at this script (the one attached 
to the original message) and provide any feedback/observations as to how it 
could be improved, I would certainly be open and 
appreciative.
 
Direct responses would probably be most appropriate.  Thanks for 
your time.
 
-::YEX::-
<)))><

  -Original Message-From: Yexley Robert D SSgt 
  AFIT/SCA [mailto:[EMAIL PROTECTED]]Sent: Monday, 02 July, 2001 
  14:51 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A 
  SCHEMA.
  
  I just finished this the other day.  It works for individual 
  tables only, but you could pretty easily modify it to work for all of the 
  tables within a schema.  Hope it helps.
   
  -::YEX::-
  <)))><
   
   
  
-Original Message-From: Bunyamin K. Karadeniz 
[mailto:[EMAIL PROTECTED]]Sent: Monday, 02 July, 2001 
13:46 PMTo: Multiple recipients of list 
ORACLE-LSubject: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS 
OF A SCHEMA.
Hi Gurus,
I do not have time to write it.
IF you have a script creating 
   
the create table scripts of a schema, I will be so happy .
Thanks 
Bunyamin

RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[The Oracle DBA]

And sub-partitioning



---

TheOracleDBA
[EMAIL PROTECTED]



On Mon, 02 Jul 2001 11:40:23  
 Koivu, Lisa wrote:
>Bunyamin, 
>Download Toad (www.toadsoft.com).  It does it for you.  THe only thing it
>doesn't handle (from what I remember) is partitioning. 
>
>HTH
>Lisa Koivu
>Data Bored Administrator
>Ft. Lauderdale, FL, 
>
>> -Original Message-
>> From:Bunyamin K. Karadeniz [SMTP:[EMAIL PROTECTED]]
>> Sent:Monday, July 02, 2001 1:46 PM
>> To:  Multiple recipients of list ORACLE-L
>> Subject: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS  OF A SCHEMA.
>> 
>> Hi Gurus,
>> I do not have time to write it.
>> IF you have a script creating 
>>the create table scripts of a schema, I will be so happy .
>> Thanks 
>> Bunyamin
>


Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: The Oracle DBA
  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: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Bunyamin K. Karadeniz]

Title: RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.



Thank you Lisa  .
Bunyamin 

  - Original Message - 
  From: 
  Koivu, Lisa 
  To: Multiple recipients of list ORACLE-L 
  Sent: Monday, July 02, 2001 10:40 
PM
  Subject: RE: SCRIPT NEEDED TO MAKE THE 
  CREATE TABLE SCRIPTS OF A SCHEMA.
  
  Bunyamin, Download Toad (www.toadsoft.com).  It does it for 
  you.  THe only thing it doesn't handle (from what I remember) is 
  partitioning. 
  HTH Lisa Koivu Data Bored Administrator Ft. Lauderdale, FL, 
  
-Original Message- From:   Bunyamin K. Karadeniz [SMTP:[EMAIL PROTECTED]] 
Sent:   Monday, July 02, 2001 1:46 PM To: Multiple recipients of list ORACLE-L Subject:    SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS  OF A 
SCHEMA. 
Hi Gurus, I do 
not have time to write it. IF you have a 
script creating    the create table scripts of a schema, I will be so happy 
. Thanks Bunyamin 

RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Yexley Robert D SSgt AFIT/SCA]

I just finished this the other day.  It works for individual tables 
only, but you could pretty easily modify it to work for all of the tables within 
a schema.  Hope it helps.
 
-::YEX::-
<)))><
 
 

  -Original Message-From: Bunyamin K. Karadeniz 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, 02 July, 2001 13:46 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A 
  SCHEMA.
  Hi Gurus,
  I do not have time to write it.
  IF you have a script creating 
     
  the create table scripts of a schema, I will be so happy .
  Thanks 
  Bunyamin
 gen_table_ddl.sql

RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Jack C. Applewhite]

Bunyamin,
 
A very easy thing to do is export the schema 
(Rows=No), then copy the Create Table statements from the *.dmp file.  That 
way you don't have to worry about your script getting out of date with features 
of newer versions of the RDBMS.
 
Jack
Jack C. 
ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, 
Inc.Austin, 
Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Bunyamin K. 
  KaradenizSent: Monday, July 02, 2001 12:46 PMTo: 
  Multiple recipients of list ORACLE-LSubject: SCRIPT NEEDED TO MAKE 
  THE CREATE TABLE SCRIPTS OF A SCHEMA.
  Hi Gurus,
  I do not have time to write it.
  IF you have a script creating 
     
  the create table scripts of a schema, I will be so happy .
  Thanks 
  Bunyamin

RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS OF A SCHEMA.

[Koivu, Lisa]

Title: RE: SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS  OF A SCHEMA.





Bunyamin, 
Download Toad (www.toadsoft.com).  It does it for you.  THe only thing it doesn't handle (from what I remember) is partitioning. 

HTH
Lisa Koivu
Data Bored Administrator
Ft. Lauderdale, FL, 


-Original Message-
From:   Bunyamin K. Karadeniz [SMTP:[EMAIL PROTECTED]]
Sent:   Monday, July 02, 2001 1:46 PM
To: Multiple recipients of list ORACLE-L
Subject:    SCRIPT NEEDED TO MAKE THE CREATE TABLE SCRIPTS  OF A SCHEMA.


Hi Gurus,
I do not have time to write it.
IF you have a script creating 
   the create table scripts of a schema, I will be so happy .
Thanks 
Bunyamin

Re: Script to find space bound objects

[Mohammad Rafiq]

Jack,
I think following script will do what you want...
Regards
Rafiq



col substr(seg.owner,1,10) heading OWNER
col substr(seg.segment_name,1,30) heading  "SEGMENT NAME"
col substr(seg.segment_type,1,10)  heading "TYPE"
col substr(seg.tablespace_name,1,20)  heading "TS NAME"
prompt ;
prompt *   THIS SHOWS THE DEFICIT OF FREE SPACE   *;
prompt ;


spool /tmp/small;
  select
substr(seg.owner,1,10),substr(seg.segment_name,1,30),substr(seg.segment_type,1,10),substr(seg.tablespace_name,1,20)
  from sys.dba_segments seg,
   sys.dba_clusters c
  where
  seg.segment_type = 'CLUSTER'
  and seg.segment_name = c.cluster_name
  and seg.owner = c.owner
  and NOT EXISTS (select tablespace_name from dba_free_space
free
  where free.tablespace_name = c.tablespace_name
  and free.bytes >=  c.next_extent);
  select
substr(seg.owner,1,10),substr(seg.segment_name,1,30),substr(seg.segment_type,1,10),substr(seg.tablespace_name,1,20)
  from sys.dba_segments seg,
   sys.dba_indexes i
  where
  seg.segment_type = 'INDEX'
  and seg.segment_name = i.index_name
  and seg.owner = i.owner
  and NOT EXISTS (select tablespace_name from dba_free_space
free
  where free.tablespace_name = i.tablespace_name
  and free.bytes >=  i.next_extent);
  select
substr(seg.owner,1,10),substr(seg.segment_name,1,30),substr(seg.segment_type,1,10),substr(seg.tablespace_name,1,20)
  from sys.dba_segments seg,
   sys.dba_tables t
  where
  seg.segment_type = 'TABLE'
  and seg.segment_name = t.table_name
  and seg.owner = t.owner
  and NOT EXISTS (select tablespace_name from dba_free_space
free
  where free.tablespace_name = t.tablespace_name
  and free.bytes >=  t.next_extent);
spool off;





Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 26 Jun 2001 06:30:45 -0800

Hi All,


I thought I had a script to check for space bound objects (can not allocate
next extent for whatever reason) but seem to have misplaced it.

Anybody want to save me the time?

TIA


Jack

=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
=





--
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: Script to find space bound objects

[Mark Leith]

Jack,

Here's one that we use..

--

CREATE OR REPLACE VIEW SPACE_BOUND_OBJECTS
  ( TS#,
NAME,
BLOCKSIZE,
MAXFREEBLOCKS,
TOTFREEBLOCKS,
FREEEXTENTS ) AS
select  a.ts#,a.name,a.blocksize,
nvl(max(b.blocks),0),
nvl(sum(b.blocks),0),
nvl(sum(b.blocks/b.blocks),0)
from sys.ts$ a, dba_free_space b
where a.name = b.tablespace_name
group by a.ts#,a.name,a.blocksize

--

SELECT u.name OWNER,
o.name OBJECT,
f.name TABLESPACE,
so.object_type TYPE,
so.object_id OBJ#,
   (s.blocks*f.blocksize)/(1024*1024) OBJMBYTES,
s.extents EXTENTS,
s.iniexts INIEXTS,
s.minexts MINEXTS,
s.maxexts MAXEXTS,
s.extsize NEXTEXTSIZE,
(s.extsize*f.blocksize)/(1024*1024) NEXTEXTMBYTES,
s.extpct EXTPCT,
decode (s.maxexts-s.extents, 0, 'Max Extents Reached',
decode(least(s.extsize,f.totfreeblocks)-s.extsize, 0, 'Fragmented
Free Space', 'Insufficient Free Space')) REASON
 FROM   sys.seg$ s, space_bound_objects f, sys.sys_objects so,
sys.obj$ o, sys.user$ u
 WHERE  (s.extsize > f.maxfreeblocks or (s.extents >= s.maxexts and
s.maxexts != 0) ) and
s.ts# = f.ts# and
(s.file#=so.header_file and s.block# = so.header_block and s.type# =
so.segment_type_id) and
so.object_id = o.obj# and
o.owner# = u.user#
--

HTH

Mark

-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, June 26, 2001 03:31
To: Multiple recipients of list ORACLE-L


Hi All,


I thought I had a script to check for space bound objects (can not allocate
next extent for whatever reason) but seem to have misplaced it.

Anybody want to save me the time?

TIA


Jack

=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
=





--
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: Mark Leith
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note 

RE: Script examples for NT. (yuk!)

[O'Neill, Sean]

Hi Greg,

I came to NT world from VMS and was used to DCL.  I find the following book
a useful reference:
Windows NT Shell Scripting  1-57870-047-7

HTH,

Sean :)

Rookie Data Base Administrator
[0%] OCP Oracle8i DBA
[0%] OCP Oracle9i DBA
  
Organon (Ireland) Ltd.
E-mail: [EMAIL PROTECTED]   [subscribed: Digest Mode]

Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA

"Nobody loves me but my mother... and she could be jivin' too."  - BB King

 
--

 From: "Loughmiller, Greg" <[EMAIL PROTECTED]>
 Date: Wed, 30 May 2001 08:26:45 -0400
 Subject: Script examples for NT. (yuk!)

Quick question..

ALL of my experience has been on UNIX platforms... Recently, I have been
asked to work on an Oracle DB that runs on NT.. My scripting capability in
the NT world is "weak" at best.  So I am trying to compare ksh type
activities with the NT world. SO I was wondering if any of you would mind
sharing a couple of NT type scripts with me.. For example-setting variables
to the return of a SQL statement to be used in the shell,etc...

Any help would be appreciated..

TIA

Greg Loughmiller

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: O'Neill, Sean
  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: Script examples for NT. (yuk!)

[Christopher Spence]

You can download CyGWin from Redhat for free that will give you a very nice
cloned shell of linux environment, full compatable with Bash/Sh scripting.

Wish i could find a ksh shell for it, but it has ALOT of the tools normally
available with Unix.  ps, kill, grep, tail, head, piping, shell scripts.

I will generally write scripts in it and move it to unix with no
compatibility problems.

Exact url: http://sources.redhat.com/cygwin/

There are many other unix clones for NT, some are great, some are simily a
directory with clone commands in it.

"Walking on water and developing software from a specification are easy if
both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot 



-Original Message-
Sent: Wednesday, May 30, 2001 9:26 AM
To: Multiple recipients of list ORACLE-L


Quick question..

ALL of my experience has been on UNIX platforms... Recently, I have been
asked to work on an Oracle DB that runs on NT.. My scripting capability in
the NT world is "weak" at best.  So I am trying to compare ksh type
activities with the NT world. SO I was wondering if any of you would mind
sharing a couple of NT type scripts with me.. For example-setting variables
to the return of a SQL statement to be used in the shell,etc...

Any help would be appreciated..

TIA

Greg Loughmiller




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Loughmiller, Greg
  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: Christopher Spence
  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: Script examples for NT. (yuk!)

[gregory . t . norris]

My suggestion would be to install cygwin 
(http://sources.redhat.com/cygwin/).

> -Original Message-
> From: GLoughmiller [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 30, 2001 8:25 AM
> To: ORACLE-L
> Cc: GLoughmiller
> Subject: Script examples for NT. (yuk!)
> 
> 
> Quick question..
> 
> ALL of my experience has been on UNIX platforms... Recently, 
> I have been
> asked to work on an Oracle DB that runs on NT.. My scripting 
> capability in
> the NT world is "weak" at best.  So I am trying to compare ksh type
> activities with the NT world. SO I was wondering if any of 
> you would mind
> sharing a couple of NT type scripts with me.. For 
> example-setting variables
> to the return of a SQL statement to be used in the shell,etc...
> 
> Any help would be appreciated..
> 
> TIA
> 
> Greg Loughmiller
> 
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Loughmiller, Greg
>   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: Script examples for NT. (yuk!)

[Eric D. Pierce]

http://www.ultratech-llc.com/KB/Scripts/?File=Scripting.BAT

(also see bottom of this message for more URLs)

http://www.ultratech-llc.com/KB/?File=!Contents.TXT


--forwarded message follows--
[Date sent: Wed, 30 May 2001 10:32:11 -0400
To: Recipients of WINNT-L digests <[EMAIL PROTECTED]> ]

> -Original Message-
> From: Andrew S. Baker [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, May 29, 2001 8:46 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: RE: Yet another scripting question
>
>
> >>However, if the user double clicks the
> >>script again, it should not map a new
> >>drive, it will only open an Explorer
> >>window showing them the contents of
> >>the already mapped drive.
>
> That will actually require a bit more code than you've used so far,
> since the ENV variables will not retain enough info between runs.
>
> Here's a script using the CD command, which I don't like (for this
> purpose).  Rule #3 in scripting should be to always use the FOR
> command... :)
>
> ~
> @ECHO OFF
>  SETLOCAL
>  SET DRIVELIST=z y x w v u t s r q p o n m l k j i h g f e d
>  SET CHECKDRIVE=FALSE
>  FOR %%D IN (%DRIVELIST%) DO CALL :CheckDrive %%D
>  ECHO %CHECKDRIVE%
>  ENDLOCAL
>  GOTO :EOF
>
> :CheckDrive
>  rem - %1 = Current Drive Letter
>  CD %1:\ 2>NUL
>  IF %ERRORLEVEL% EQU 1 IF /I "%CHOSEN%"=="FALSE" SET CHECKDRIVE=%1:
>  GOTO :EOF
> ~
>
>
> As a replacement for CD, I chose PUSHD, especially since it already
> does the first part of what you want (chosing the highest available
> drive letter).
>
> ~
> @ECHO OFF
>  SETLOCAL
>  SET RESOURCE=\\COMPUTER\SHARE
>  SET CHECKDRIVE=NOT SET
>  PUSHD %RESOURCE%
>  IF %ERRORLEVEL% EQU 0 FOR /F "DELIMS=\" %%D IN ('CD') DO SET
> CHECKDRIVE=%%D
>  ECHO %CHECKDRIVE%
>  POPD
>  ENDLOCAL
> ~
>
>
>
> Now, if you don't want the drive assigned again, you should check 
for
> the existence of the mapping before running the rest of the 
script
>
>
> See the following:
>  http://www.ultratech-llc.com/KB/Scripts/?File=MapDrive.BAT
>
> The full script will be here at some point before tomorrow 
morning...
>
>
>
> ==
>  ASB - http://www.ultratech-llc.com/KB/?File=Troubleshoot.TXT
> ==

--- Forwarded message follows ---
Date sent:  Wed, 30 May 2001 01:38:54 -0700

"Andrew S. Baker" wrote:

> I ended up using PUSHD, since it is native to NT/2000 and does all the
> work of choosing the highest free drive.
>

==begin file c:\CMD\TEST\freedrive.cmd ==
001. @echo off
002. setlocal
003. set tgt=%1
004. if /i "%tgt%" NEQ "HIGHEST" if /i "%tgt%" NEQ "LOWEST" (
005.echo.
006.echo Specify an argument of HIGHEST to return the highest free drive letter
007.echo Specify an argument of LOWEST  to return the lowest  free drive letter
008.endlocal&goto :EOF
009. )
010. set free_drives=
011. for %%a in (
012.   z y x w v u t s r q p o n m l k j i h g f e d c b
013. ) do call :test %%a
014. set free_drives=%free_drives:~1%
015. call :%tgt%
016. endlocal&set %~n0=%free_drive%&goto :EOF
017.
018. :test
019. if exist %1: goto :EOF
020. set free_drives=%free_drives% %1:
021. goto :EOF
022.
023. :lowest
024. for /f "tokens=1 delims=: " %%a in (
025.  "%free_drives%"
026. ) do set free_drive=%%a:
027. goto :EOF
028.
029. :highest
030. call :length %free_drives%
031. set /a pos = length - 3
032. call :exec set free_drive=%%free_drives:~%pos%,2%%%
033. goto :EOF
034.
035. :length
036. set S1=%*
037. set length=0
038. :lloop
039. if not defined S1 goto :EOF
040. set S1=%S1:~1%
041. set /a length+=1
042. goto :lloop
043.
044. :exec
045. %*
046. goto :EOF
==end   file c:\CMD\TEST\freedrive.cmd ==

--- End of forwarded message ---

http://www.ultratech-llc.com/KB/?File=!Contents.TXT

...

SCRIPTING & AUTOMATION

* http://www.ultratech-llc.com/KB/?File=Assoc.TXT
* http://www.ultratech-llc.com/KB/?File=Audit.TXT
* http://www.ultratech-llc.com/KB/?File=AutoLogon.TXT
* http://www.ultratech-llc.com/KB/?File=BatchJob.TXT
* http://www.ultratech-llc.com/KB/?File=Browser.TXT
* http://www.ultratech-llc.com/KB/?File=CopyPerms.TXT
* http://www.ultratech-llc.com/KB/?File=DateTime.TXT
* http://www.ultratech-llc.com/KB/?File=Delegate.TXT
* http://www.ultratech-llc.com/KB/?File=DelOld.TXT
* http://www.ultratech-llc.com/KB/?File=EnvVars.TXT
* http://www.ultratech-llc.com/KB/?File=ERD.TXT
* http://www.ultratech-llc.com/KB/?File=InUse.TXT
* http://www.ultratech-llc.com/KB/?File=License.TXT
* http://www.ultratech-llc.com/KB/?File=Monitoring.TXT
* http://www.ultratech-llc.com/KB/?File=NetShell.TXT
* http://www.ultratech-llc.com/KB/?File=NTRights.TXT
* http://www.ultratech-llc.com/KB/?File=NTVer.TXT
* http://www.ultratech-llc.com/KB/?File=OSType.TXT
* http://www.ultratech-llc.com/KB/?

Re: Script and control file

[nlzanen1]

Hi,


Within your *.sql script you can jump to the OS and give OS commands.
e.g:   host sqlldr.  (start with host).
I have used it with import and it works.


Jack


   
   
Quaglio Andrea 
   
 
agroup.it>cc: (bcc: Jack van 
Zanen/nlzanen1/External/MEY/NL)  
Sent by:  Subject: Script and control file 
   
[EMAIL PROTECTED]   
   
   
   
   
   
29-05-2001 17:15   
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   



First of all, I'm a beginner to SQL/SQL Loader.
Currently,I'm working with Oracle 8.
I would like to know if it's possible invoke SQL Loader inside a script.

Example:
- inside the script I want to create a new table TABLE1
- copy some data inside TABLE1 using SQL loader
- work on TABLE1 using SQL language.

Is it possible do this inside a .sql file ?

Thanks,
Andrea
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Quaglio Andrea
  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).




=
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
=





--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTE

Re: Script and control file

[William Beilstein]

As Jared pointed out (shame on me), there is no host command in PL/SQL, the only place 
where the host command is supported in in sqlplus and forms.

>>> Jared Still <[EMAIL PROTECTED]> 05/29/01 02:10PM >>>
On Tuesday 29 May 2001 09:07, William Beilstein wrote:
> see the host command in pl/sql

Bill, I know you know this: there is no host command in pl/sql.

Assuming you meant sqlplus.  :)

Jared

>
> >>> [EMAIL PROTECTED] 05/29/01 11:15AM >>>
>
> First of all, I'm a beginner to SQL/SQL Loader.
> Currently,I'm working with Oracle 8.
> I would like to know if it's possible invoke SQL Loader inside a script.
>
> Example:
> - inside the script I want to create a new table TABLE1
> - copy some data inside TABLE1 using SQL loader
> - work on TABLE1 using SQL language.
>
> Is it possible do this inside a .sql file ?
>
> Thanks,
> Andrea

--
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: Script and control file

[Rajaram]

Inside Pl/SQL u cannot use the host command.. HOST is Sql*PLUS command not a SQL. So, 
u cannot use with in Pl?SQL

Rajaram


-Original Message-
From:   William Beilstein [SMTP:[EMAIL PROTECTED]]
Sent:   Tuesday, May 29, 2001 12:08 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: Script and control file

see the host command in pl/sql

>>> [EMAIL PROTECTED] 05/29/01 11:15AM >>>
First of all, I'm a beginner to SQL/SQL Loader.
Currently,I'm working with Oracle 8.
I would like to know if it's possible invoke SQL Loader inside a script.

Example:
- inside the script I want to create a new table TABLE1
- copy some data inside TABLE1 using SQL loader
- work on TABLE1 using SQL language.

Is it possible do this inside a .sql file ?

Thanks,
Andrea
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Quaglio Andrea
  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).

NetZero Platinum
No Banner Ads and Unlimited Access
Sign Up Today - Only $9.95 per month!
http://www.netzero.net
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rajaram
  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: Script and control file

[lhoska]

If it is NT you can create a batch file similar to Unix example given below
which will do the same.  The only difference here is you'll have to save
your sqlplus scripts in a text or *.sql format and call them from the batch
file.  After that you can call sqlloader.


-Original Message-
Sent: Tuesday, May 29, 2001 12:12 PM
To: Multiple recipients of list ORACLE-L


Andrea,

Is this UNIX or NT.

In UNIX ( I don't know NT that well), the most simple method would be to use
a shell script.

The following is about as basic as it comes but would need to be embellished
with environment settings and error checking etc..

eg.

Start of Script


sqlplus username/passwd <

sqlplus username/password 

RE: Script and control file

[Robertson Lee - lerobe]

Andrea,

Is this UNIX or NT.

In UNIX ( I don't know NT that well), the most simple method would be to use
a shell script.

The following is about as basic as it comes but would need to be embellished
with environment settings and error checking etc..

eg.

Start of Script


sqlplus username/passwd <

sqlplus username/password 

Re: Script and control file

[William Beilstein]

see the host command in pl/sql

>>> [EMAIL PROTECTED] 05/29/01 11:15AM >>>
First of all, I'm a beginner to SQL/SQL Loader.
Currently,I'm working with Oracle 8.
I would like to know if it's possible invoke SQL Loader inside a script.

Example:
- inside the script I want to create a new table TABLE1
- copy some data inside TABLE1 using SQL loader
- work on TABLE1 using SQL language.

Is it possible do this inside a .sql file ?

Thanks,
Andrea
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Quaglio Andrea
  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: Script to compare two schemas

[Jared Still]

On Thursday 24 May 2001 17:20, Helmut Daiminger wrote:
> Does anybody out there have a script to compare to database schemas and
> list the differences?


I'm forwarding a post I made on this topic a few days ago.

Jared

--  Forwarded Message  --
Date: Sun, 13 May 2001 21:44:53 -0700
To: "Oracle-L List, [EMAIL PROTECTED]" <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED], "Mohan, Ross [EMAIL PROTECTED]" 
<[EMAIL PROTECTED]>


The database compare script didn't come out to well the first
time, so I've just embedded them in this post.

The scripts are:

clears.sql
columns.sql
title.sql
schema_diff.sql

Jared

--- title.sql - copied from title80.sql
-- specify line width when calling
-- eg @title 'report heading' 90

rem TITLE.SQL   - This SQL*Plus script builds a standard report
rem   heading for database reports that are XX columns
rem
column  TODAY   NEW_VALUE   CURRENT_DATENOPRINT
column  TIMENEW_VALUE   CURRENT_TIMENOPRINT
column  DATABASENEW_VALUE   DATA_BASE   NOPRINT
set term off feed off
rem
define COMPANY = "BCBSO"
define HEADING = "&1"
col cPageNumLoc new_value PageNumLoc noprint
select ('&&2' - 10 ) cPageNumLoc from dual;
rem
TTITLE LEFT "Date: " current_date CENTER company col &&PageNumLoc "Page:"
format 999 -
   SQL.PNO SKIP 1 LEFT "Time: " current_time CENTER heading RIGHT -
   format a15 SQL.USER SKIP 1 CENTER format a20 data_base SKIP 2
rem
rem
set heading off
set pagesize 0
rem
column passout new_value dbname noprint
SELECT TO_CHAR(SYSDATE,'MM/DD/YY') TODAY,
   TO_CHAR(SYSDATE,'HH:MI AM') TIME,
   --DATABASE||' Database' DATABASE,
   --rtrim(database) passout
   name||' Database' DATABASE,
   lower(rtrim(name)) passout
FROM   v$database;
set term on feed on
rem
set heading on
set pagesize 58
set line &&2
set newpage 0
define db = '_&dbname'
undef 1 2
---

-- columns.sql

col blocks format 99,999,999 head 'BLOCKS'
col db_link format a30 head 'DB LINK'
col db_link_instance format a30 head 'DB LINK|INSTANCE'
col db_link_username format a10 head 'DB LINK|USERNAME'
col empty_blocks format 99,999,999 head 'EMPTY BLOCKS'
col host format a10 head 'HOST'
col index_name format a30 head 'INDEX NAME'
col initial_extent format 9,999,999,999 head 'INITIAL|EXTENT'
col instance format a4 head 'INST'
col max_blocks format 9,999,999 head 'MAX BLOCKS'
col max_bytes format 99,999,999,999 head 'MAX BYTES'
col max_extents format 999,999 head 'MAX|EXTENTS'
col min_extents format 999 head 'MIN|EXT'
col next_extent format 9,999,999,999 head 'NEXT|EXTENT'
col object_name format a30 head 'OBJECT NAME'
col owner format a10 head 'OWNER'
col pct_free format 999 head 'PCT|FREE'
col pct_increase format 999 head 'PCT|INC'
col pct_used format 999 head 'PCT|USED'
col synonym_name format a30 head 'SYNONYM NAME'
col table_name format a30 head 'TABLE NAME'
col table_owner format a10 head 'TABLE|OWNER'
col tablespace_name format a15 head 'TABLESPACE|NAME'
col timestamp format a19 head 'TIME STAMP'
col username format a10 head 'USERNAME'
col last_ddl_time head 'LAST DDL|TIME'
col created head 'CREATED'
col procedure_name format a30 head 'PROCEDURE NAME'
col function_name format a30 head 'FUNCTION NAME'
col package_name format a30 head 'PACKAGE NAME'
col package_body_name format a30 head 'PACKAGE BODY NAME'
col segment_name format a30 head 'SEGMENT NAME'

--

-- clears.sql
set pause off
set echo off
set trimspool on
set feed on term on echo off verify off
set line 80
set pages 24 head on

clear col
clear break
clear computes

btitle ''
ttitle ''

btitle off
ttitle off

---

-- schema_diff.sql
-- Jared Still
-- [EMAIL PROTECTED]
-- [EMAIL PROTECTED]
--
--
-- 07/14/1999 jkstill - removed table_owner from the index_diff
--  section as this made all indexes appear
--  in the difference report if the schemas
--  had different names
--  -
-- this script will create reports of the
-- differences between 2 schemas
-- the reports created are:

-- table_diff.txt
-- index_diff.txt
-- arg_diff.txt
-- sequence_diff.txt

-- all differences are checked both
-- ways - schema1 is compared to schema2
-- and then schema2 is compared to schema1
-- it does not matter which database the
-- reports are initiated from

-- requirements:
--
-- either global links or database links to
-- the schemas to be compared
--
-- dba privileges
--

-- this report creates temporary tables for it's reports
-- they are left intact for your perusal
-- tables:
--   arg_diff
--   index_diff
--   table_diff
--

-- REPORT SUMMARY

-- table_diff.txt
-- this report compares tables from 2 schemas
-- included are
--tables/columns that appear in a table in
--one schema, and not the other
--
--any differences between columns; includes
--order of column, type and size.


-- index_diff.txt
-- this report co

RE: Script that will remove redundant rows from table

[Rachel Carmichael]

GROAN


>From: "Eric D. Pierce" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Script that will remove redundant rows from table
>Date: Thu, 17 May 2001 10:50:39 -0800
>
>Mr. Rhet Device
>(rhet oracle device, that is)
>
>On 17 May 2001, at 8:21, Alan Aschenbrenner wrote:
>
> > Who was the half person?
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Eric D. Pierce
>   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: Script that will remove redundant rows from table

[Eric D. Pierce]

Mr. Rhet Device
(rhet oracle device, that is)

On 17 May 2001, at 8:21, Alan Aschenbrenner wrote:

> Who was the half person?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  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: Script that will remove redundant rows from table

[Rachel Carmichael]

Deal! :)


>From: "Eric D. Pierce" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: Script that will remove redundant rows from table
>Date: Thu, 17 May 2001 09:31:33 -0800
>
>How about we split the difference?
>
>|  1* select (25.25+27.5)/2 from dual
>|SQL> /
>|
>|(25.25+27.5)/2
>|--
>|26.375
>|
>
>  (no this is not the mean)
>
>
>On 16 May 2001, at 19:36, Rachel Carmichael wrote:
>
> > now now, don't exaggerate it was only 25 & 1/4 people
>...
>
> > >didn't 27 & 1/2 people post the answer to this yesterday???
>
>...
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Eric D. Pierce
>   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: Script that will remove redundant rows from table

[Jared Still]

Are you an idiot?

( oh god, I can't believe I said that.  might as well finish it )

ALL YOUR BASE ARE BELONG TO US

;^{)

On Thursday 17 May 2001 11:07, Mohan, Ross wrote:
> HELP
>
> || -Original Message-
> || From: Eric D. Pierce [mailto:[EMAIL PROTECTED]]
> || Sent: Thursday, May 17, 2001 1:32 PM
> || To: Multiple recipients of list ORACLE-L
> || Subject: Re: Script that will remove redundant rows from table
> ||
> ||
> || How about we split the difference?
> ||
> || |  1* select (25.25+27.5)/2 from dual
> || |SQL> /
> || |
> || |(25.25+27.5)/2
> || |--
> || |26.375
> ||
> ||  (no this is not the mean)
> ||
> || On 16 May 2001, at 19:36, Rachel Carmichael wrote:
> || > now now, don't exaggerate it was only 25 & 1/4 people
> ||
> || ...
> ||
> || > >didn't 27 & 1/2 people post the answer to this yesterday???
> ||
> || ...
> ||
> ||
> || --
> || Please see the official ORACLE-L FAQ: http://www.orafaq.com
> || --
> || Author: Eric D. Pierce
> ||   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: Jared Still
  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: Script that will remove redundant rows from table

[Hillman, Alex]

Are you an idiot? :-)

Alex Hillman

-Original Message-
Sent: Thursday, May 17, 2001 2:07 PM
To: Multiple recipients of list ORACLE-L


HELP









|| -Original Message-
|| From: Eric D. Pierce [mailto:[EMAIL PROTECTED]]
|| Sent: Thursday, May 17, 2001 1:32 PM
|| To: Multiple recipients of list ORACLE-L
|| Subject: Re: Script that will remove redundant rows from table
|| 
|| 
|| How about we split the difference?
|| 
|| |  1* select (25.25+27.5)/2 from dual
|| |SQL> /
|| |
|| |(25.25+27.5)/2
|| |--
|| |26.375
|| |
|| 
||  (no this is not the mean)
|| 
|| 
|| On 16 May 2001, at 19:36, Rachel Carmichael wrote:
|| 
|| > now now, don't exaggerate it was only 25 & 1/4 people
|| ...
|| 
|| > >didn't 27 & 1/2 people post the answer to this yesterday???
|| 
|| ...
|| 
|| 
|| -- 
|| Please see the official ORACLE-L FAQ: http://www.orafaq.com
|| -- 
|| Author: Eric D. Pierce
||   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: Mohan, Ross
  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: Hillman, Alex
  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: Script that will remove redundant rows from table

[Mohan, Ross]

HELP









|| -Original Message-
|| From: Eric D. Pierce [mailto:[EMAIL PROTECTED]]
|| Sent: Thursday, May 17, 2001 1:32 PM
|| To: Multiple recipients of list ORACLE-L
|| Subject: Re: Script that will remove redundant rows from table
|| 
|| 
|| How about we split the difference?
|| 
|| |  1* select (25.25+27.5)/2 from dual
|| |SQL> /
|| |
|| |(25.25+27.5)/2
|| |--
|| |26.375
|| |
|| 
||  (no this is not the mean)
|| 
|| 
|| On 16 May 2001, at 19:36, Rachel Carmichael wrote:
|| 
|| > now now, don't exaggerate it was only 25 & 1/4 people
|| ...
|| 
|| > >didn't 27 & 1/2 people post the answer to this yesterday???
|| 
|| ...
|| 
|| 
|| -- 
|| Please see the official ORACLE-L FAQ: http://www.orafaq.com
|| -- 
|| Author: Eric D. Pierce
||   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: Mohan, Ross
  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: Script that will remove redundant rows from table

[Eric D. Pierce]

How about we split the difference?

|  1* select (25.25+27.5)/2 from dual
|SQL> /
|
|(25.25+27.5)/2
|--
|26.375
|

 (no this is not the mean)


On 16 May 2001, at 19:36, Rachel Carmichael wrote:

> now now, don't exaggerate it was only 25 & 1/4 people
...

> >didn't 27 & 1/2 people post the answer to this yesterday???

...


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  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: Script that will remove redundant rows from table

[Alan Aschenbrenner]

Who was the half person?


-Original Message-
Sent: Wednesday, May 16, 2001 9:37 PM
To: Multiple recipients of list ORACLE-L


now now, don't exaggerate it was only 25 & 1/4 people




>From: "Eric D. Pierce" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: Script that will remove redundant rows from table
>Date: Wed, 16 May 2001 17:45:22 -0800
>
>didn't 27 & 1/2 people post the answer to this yesterday???
>
>On 16 May 2001, at 17:10, Haskins, Ed wrote:
>
> > A few months ago I remember seeing a script that enabled the removal
of
> > redundant rows in a table.
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Eric D. Pierce
>   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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Aschenbrenner
  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: Script that will remove redundant rows from table

[Haskins, Ed]

Eric,

Yes they did...I am not resending this message.  Either the List software is
screwing up, or my Company's email servers suck.

Ed Haskins
Oracle DBA
Verizon Wireless


-Original Message-
Sent: Wednesday, May 16, 2001 9:45 PM
To: Multiple recipients of list ORACLE-L


didn't 27 & 1/2 people post the answer to this yesterday???

On 16 May 2001, at 17:10, Haskins, Ed wrote:

> A few months ago I remember seeing a script that enabled the removal of
> redundant rows in a table.  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  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: Haskins, Ed
  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: Script to remove redundant rows in a table?

[Hatzistavrou Giannis]

there is also the case of trying to enable a unique constraint on the
underlying table using the exeption option and creating an exception
table(utlexcpt.sql). The unique constraint will of caurse fail but inside
the exeption table the duplicate rowids will be written . Using this input
you can delete from table the duplicates.

> -Original Message-
> From: Haskins, Ed [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, May 17, 2001 04:10
> To:   Multiple recipients of list ORACLE-L
> Subject:  Script to remove redundant rows in a table?
> 
> A few months ago I remember seeing a script that enabled the removal of
> redundant rows in a table. Can someone please forward or point me to such
> a
> script. 
> 
> I have an Oracle Names server that stores the database connect descriptors
> in a table within the Region Database. I've loaded these connect
> descriptors
> from many SQLNET.ora files throughout the enterprise. My problem is that
> there exists duplicates from the multiple SQLNET.ora files. I really want
> to
> clean this up so that only one entry per connect descriptor exists. I
> think
> that a generic script for any table will work in this situation. 
> 
> Thanks, 
> Ed Haskins 
> Oracle DBA
> Verizon Wireless
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Haskins, Ed
>   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: Hatzistavrou Giannis
  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: Script that will remove redundant rows from table

[Rachel Carmichael]

now now, don't exaggerate it was only 25 & 1/4 people




>From: "Eric D. Pierce" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: Script that will remove redundant rows from table
>Date: Wed, 16 May 2001 17:45:22 -0800
>
>didn't 27 & 1/2 people post the answer to this yesterday???
>
>On 16 May 2001, at 17:10, Haskins, Ed wrote:
>
> > A few months ago I remember seeing a script that enabled the removal of
> > redundant rows in a table.
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Eric D. Pierce
>   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: Script that will remove redundant rows from table

[Eric D. Pierce]

didn't 27 & 1/2 people post the answer to this yesterday???

On 16 May 2001, at 17:10, Haskins, Ed wrote:

> A few months ago I remember seeing a script that enabled the removal of
> redundant rows in a table.  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  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).