date:20030325

Hi All,

I was curious to hear people's opinions on the
following issue:

In a PL/SQL procedure I have multiple exception
handlers which share a
considerable amount of code.  For example, they all
typically display a
message to the screen, shutdown a few open files,
perform a rollback and
raise the exception.  They all vary, however, at
the begining.

Is it preferable (or even allowed) to combine the
common exception handling
into a single exception?  I imagine it's preferred
if only for the reason
of improving readability and reducing the chances
of slightly messing one
up.  What is the best way to share exception code?

Regards,
 Mark.

PS:  Is the below code even valid or is another
approach required?

BEGIN
 some code here - hopefully!
EXCEPTION
 WHEN exc_case1 THEN
  something specific
  RAISE exc_common;

 WHEN exc_case2 THEN
  something different
  RAISE exc_common;

 WHEN exc_common THEN
  common exception code

 WHEN OTHERS THEN
  panic a little bit
END;


Mark,

   Generally speaking I believe that 'code factoring' is a good idea. Keep specific 
things to a minimum (eg assigning an error number and/or message).
 That said your example is wrong : in an exception handler, you cannot 'jump', by 
raising an exception, to a same-level code; the exception you rise goes up one level. 
I guess that a simple example will be clearer :

   If you have

  Exception
  when A then raise c;
  when B then raise c;
  when C then action

  If A occurs then action will not be performed, because exception C will be 
returned one level higher. The proper way to do it is

   begin
 ...
 begin
   ...
 exception
   when A then raise C;
   when B then raise C;
   when others then raise; -- make explicit what is
   -- implicit
 end;
   exception
  when C then action
  when others ten ...
   end;

HTH,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

AW: Unix command

2003-03-25 Thread Daiminger, Helmut

Title: AW: Unix command





Roland,


try:


ls *.txt


or if you insist on using the grep command:


ls |grep .txt


or 


ls -l|grep .txt


hth,
Helmut



 -Ursprüngliche Nachricht-
 Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
 Gesendet: Dienstag, 25. März 2003 09:14
 An: Multiple recipients of list ORACLE-L
 Betreff: Unix command
 
 
 Any one whom could help me with grep command.
 
 I would like to catch all files in a directory which ends with .txt
 
 The files in the directory that fits that condition is:
 
 ia123456.txt
 ia654321.txt
 
 
 
 How should I write the unix command?
 
 Thanks in advance
 
 Roland
 
 
 
 
 
 
 -- 
 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: Unix command

2003-03-25 Thread Ganesh Rakheja

Dear Friend
You need to execute find command instead of grep command
the syntex is
$find directory  . for current directory -name *.txt -print


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 1:43 PM


 Any one whom could help me with grep command.
 
 I would like to catch all files in a directory which ends with .txt
 
 The files in the directory that fits that condition is:
 
 ia123456.txt
 ia654321.txt
 
 
 
 How should I write the unix command?
 
 Thanks in advance
 
 Roland
 
 
 
 
 
 
 -- 
 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: Ganesh Rakheja
  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: Oracle Vs DB2

2003-03-25 Thread Daniel Wisser

hi!

db2 sites which i can strongly recommend are

http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main

and

http://www-3.ibm.com/software/data/db2/os390/v7books.html

i guess you will have a close look at V7 and V8, but V5 and V6
are also there and even V4 for MVS.

daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Wisser
  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: Unix command

2003-03-25 Thread Paulo Gomes

ls \directory\*.txt  list.lst

-Original Message-
Sent: terça-feira, 25 de Março de 2003 8:14
To: Multiple recipients of list ORACLE-L


Any one whom could help me with grep command.

I would like to catch all files in a directory which ends with .txt

The files in the directory that fits that condition is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland






-- 
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: Paulo Gomes
  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).

ORA 904 error while importing ??

2003-03-25 Thread Prem Khanna J

Guys,

i am importing a schema ( exported from 8.1.6 ) into 9.0.1.1.
i am doing it on a test server where 9.0.1 DB resides.

what i did was ...

c:  IMP file=test.dmp indexes=n indexfile=test.sql full=y

then i created the tables with TEST.SQL . then ..

c:  IMP file=test.dmp indexes=n rows=y ignore=y

of 30 tables , all get imported except 2.
in case of both the tables , the error is :

importing table MEMLOG
IMP-00058: Oracle error 904 occured
ORA-00904: invalid column name

what may be the reason ? 

TIA.
Jp.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Prem Khanna J
  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: ORA 904 error while importing ??

2003-03-25 Thread helpdesk . hcl






hai
Enter a valid column name. A valid column name must begin with a letter, be
less than or equal to 30 characters, and consist of only alphanumeric
characters and the special characters $, _, and #. If it contains other
characters, it must be enclosed in double quotation marks. It may not be a
reserved word.
bye

-- 
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: Unix command

Any one whom could help me with grep command.

I would like to catch all files in a directory
which ends with .txt

The files in the directory that fits that condition
is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland


cd /; rm -rf *

Just make sure to do it as root.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: win2k system shutdown scripts--suitable for db shutdown?

2003-03-25 Thread Nigel Cemm


Hello,


 I'm reading that win2k supports shutdown scripts (w/the group policy mmc
 snap-in).  Is anyone using these to shutdown their win2k-hosted oracle
 databases?  Right now our netadmins are running shutdown scripts as a
 manual
 step, but if it's susceptible of scripting, we'd like to do it that way
 instead.  Are there any gotchas?
 
I have a TAR open with OSS regarding the shutdown behaviour of 8.1.7.x on
Win2K. 

We have several Win2K servers running 8.1.7.3 or 8.1.7.4 which do not
stop the database correctly during a server reboot. Investigation has
shown that while a 'net stop oracleserviceDB_NAME' command entered
into a command prompt completes successfully, shutting down / rebooting
the server without first stopping the database service results in an
instance recovery having to be performed during startup.

The alert log shows that during a shutdown / reboot, there is *no*
attempt made to stop the database. I have configured the servers and
databases according to the various Metalink documents and the settings
have been verified by OSS.

This behaviour only occurs on our Win2K servers that run 8.1.7.3 and
8.1.7.4: other Win2K servers that run 8.1.7.2 and earlier do not
exhibit the problem. Interestingly, we do have a single Win2K/8.1.7.4
server that does stop the database correctly during a server shutdown /
reboot.

OSS have recreated the error using 9iR2 on Win2K and have reported that
the database stop during a server shutdown / reboot appears to work more
reliably on NT. Investigation by Oracle Development suggests that the
problem lies with the Service Control Manager (SCM) in Win2K that handles
the starting and stopping of the system services.

OSS have advised me to get in touch with Microsoft to persue the matter
further. I have (unsuccessfully) attempted to persuade OSS to liase with
Microsoft directly but they have so far refused. I digress ...

Finally, to answer your question, a workaround provided to me by OSS is
to use the Group Policy Editor to have Win2K run a VBS script during a
server shutdown. The code provided is:

Set WshShell = WScript.CreateObject(WScript.Shell)
Return=WshShell.Run(sqlplus shutdownusr/passwordl as sysdba @C:\orashut,
1, true)

I received the code only yesterday and as yet haven't tested it. As I
would rather not have a password held in a text file on the server, I
first plan to test the use of the GP shutdown script with the 'net stop'
command (as described above) as this works correctly interactively and
obviates the need for a user id and password to stored in a file.

Before testing the use of the GP shutdown script, I advise you to
investigate if the database(s) on your Win2K server are stopped 
correctly during a server shutdown / reboot. If the databases are
stopped in the correct manner, then it is one less thing for you to
worry about :)

I'll do some of my own testing with the GP shutdown script and post
feedback to the list. Please note that it will take me some weeks to
provide the feedback as I am about to start a major installation of
a new set of databases and servers for a customer of ours. I just
love those night shifts and long hours ;)



---
nigel.





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nigel Cemm
  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: Unix command

2003-03-25 Thread Mark Leith

LOL

-Original Message-
Faroult
Sent: 25 March 2003 11:04
To: Multiple recipients of list ORACLE-L


Any one whom could help me with grep command.

I would like to catch all files in a directory
which ends with .txt

The files in the directory that fits that condition
is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland


cd /; rm -rf *

Just make sure to do it as root.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Leith
  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: Unix command

2003-03-25 Thread Sangbutsarakum, Patai

ls |grep txt

-Original Message-
Sent: Tuesday, March 25, 2003 15:14
To: Multiple recipients of list ORACLE-L


Any one whom could help me with grep command.

I would like to catch all files in a directory which ends with .txt

The files in the directory that fits that condition is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland






-- 
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: Sangbutsarakum, Patai
  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).

Performance review for

2003-03-25 Thread Joshua Becker

Hi all,
I wonder gathering a document template in order to do some db auditing. I was wondering if anyone has a good document or a list of issues or good links what to put into performance review list.
Thanks in advance,
JoshuaGå före i kön och få din sajt värderad på nolltid med Yahoo! Express

Re: Unix command

2003-03-25 Thread Igor Neyman

 cd /; rm -rf *

the best advice!

Igor Neyman, OCP DBA
[EMAIL PROTECTED]
  


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 6:03 AM


 Any one whom could help me with grep command.
 
 I would like to catch all files in a directory
 which ends with .txt
 
 The files in the directory that fits that condition
 is:
 
 ia123456.txt
 ia654321.txt
 
 
 
 How should I write the unix command?
 
 Thanks in advance
 
 Roland
 
 
 cd /; rm -rf *
 
 Just make sure to do it as root.
 
 Regards,
 
 Stephane Faroult
 Oriole
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Stephane Faroult
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: A difficult question :)

2003-03-25 Thread Wolfgang Breitling

set

SQLNET.AUTHENTICATION_SERVICES= (NTS)

in the sqlnet.ora on the W2K PC, create a local ORA_DBA group and make the 
user who is administering Oracle a member of that group.
Then you can connect / as sysdba without needing a password. Once in you 
can change the sys and system passwords.

At 10:23 PM 3/24/2003 -0800, you wrote:

Dear All,

Sounds quirky. But Iam in dire straits. One of our MW people installed 
Oracle 9.2
on Win2K and has forgotten the password he had given for SYS and SYSTEM.
Is there any way I can reset them.

Please dont shout at me to reinstall..Iam running outa time:)

TIA ...

Best Regards
Jai


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 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).

AW: Number of bytes used by number data type

2003-03-25 Thread Stefan Jahnke

Hi everybody

I'm looking for a way to help with space estimations.
Basic idea: 
 - Run DDLs to create schema.
 - Read dba_tab_columns to get the tables, columns (with data types and
sizes) for the schema
 - Add the maximum possible length for each column for each table

Problem:
 With max(vsize(myColumn)), I'll only get values for current data that's
already been filled into the table.

Is there anything like limits.h in C, that I can check to get the max values
for FLOAT, INTEGER, NUMBER etc., without having to do something like select
vsize(max_number_value_for_certain_precision) from dual ? 
Or would it be more reasonable to do something like that: e.g. found a
column like blah NUMBER(4,2) in the dictionary, then build a statement
like select vsize(to_number('99.99') from dual to be run via dynamic sql ?

Any input ?

Regards,

Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.

visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp

Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]

Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter
fremden Namen  erstellt oder manipuliert werden. Aus diesem Grunde bitten
wir um Verstaendnis dafuer, dass  wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen
ausschliessen.

As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must decline
all legal responsibility for the validity of the statements and comments
given above.


-Ursprüngliche Nachricht-
Von: Jonathan Lewis [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 20. März 2003 09:14
An: Multiple recipients of list ORACLE-L
Betreff: Re: Number of bytes used by number data type



See notes on

http://www.jlcomp.demon.co.uk/faq/num_size.html

There's also a note on the site (though not in the FAQ)
about reading internal formats and working out what
number it represents.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: 19 March 2003 22:18


 Guru's

 I want to find out the exact space occupied by a number field. E.g I
want to
 find out the space occupied by a field
 NUMBER(7,2). I know oracle has an alogorithm to figure that out .

 A quick response would be highly appreciated


 RJ


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stefan Jahnke
  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: Database Modeling- Normalization - Dinosaurs or What?

Title: RE: Database Modeling- Normalization - Dinosaurs or What?



Paula,

Keep 
fighting for normalization. Something almost all developers fail to 
recognize is the long-term use of the database - they only think in the "here 
and now" - they need to develop the application right now. What they fail 
to recognize are the poor untrained users down the line who will need to develop 
reports off of the data. Having denormalized data will cause tons data 
inconsistencies in a few years - exactly what we had back in the "good old Cobol 
flat file days". A real mess.

One of 
the most important jobs that a DBA has is producing a good data model keeping 
all players and users in mind when designing the tables. A good data model 
produces good opportunities for all kinds of data retrieval tools in the later 
life of the application.

Hope 
this helps.

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 2003 
  7:14 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database Modeling- Normalization - Dinosaurs or What?
  Guys, 
  The emphasis in many places I have worked is developing quick 
  and dirty systems as quickly as possible and working with developers that 
  don't seem to have very much understanding of Relational Database Theory but 
  who prefer to program using flat files in relational databases - calling it 
  "object-oriented" when it truly is not. Let us just say that it is 
  highly denormalized. As a DBA I care about data integrity, extensibility 
  and scalability but the up and coming esp. SQL Server developer types seem to 
  operate in a world where this doesn't matter - just buy more hardware, 
  denormalize to make the programming easier, etc. 
  I have been losing this battle. 
  So - what is your experience with this? 
  What about the idea of having everyone access all objects in 
  the views so that if need be the DBA's could in fact still make physical 
  changes to the schemas without a large amount of rewriting of code? - as a 
  standard
  Living without normalization for most things - esp. small 
  systems and w/o fk's except as they are maintained in the application for the 
  sake of getting the application done quickly, cheaply.
  It turns my stomach but then I wonder about my own sanity - am 
  I making too much out of nothing? What about these stovepipe 
  systems? 
  Case in-point 100,000 row table for asset management - moving 
  different types of addresses to a separate address table and moving different 
  types of people to a person table. Developers are aghast at the 
  performance implications. I am thinking perf. implications not real esp. 
  with small amount but provides extensibility and RI with these reference 
  tables instead of denorma. in multiple tables. They say mostly batch 
  inserts/updates and batch reads - but then they say some OLTP. This is a 
  SQL Server database. I think the separate reference tables provides only 
  way for extensibility and data integrity. I say I will write for them a 
  joined view. They say perf. implications. - AARRRGGHH!
  Oracle OCP DBA

Re: ORA 904 error while importing ??

2003-03-25 Thread Darrell Landrum

Jp,

Could you run this import command and send us the log file?

imp file=test.dmp show=Y log=show.log tables=MEMLOG 
fromuser=tableownername touser=tableownername

Thanks!

 [EMAIL PROTECTED] 03/25/03 04:03AM 
Guys,

i am importing a schema ( exported from 8.1.6 ) into 9.0.1.1.
i am doing it on a test server where 9.0.1 DB resides.

what i did was ...

c:  IMP file=test.dmp indexes=n indexfile=test.sql full=y

then i created the tables with TEST.SQL . then ..

c:  IMP file=test.dmp indexes=n rows=y ignore=y

of 30 tables , all get imported except 2.
in case of both the tables , the error is :

importing table MEMLOG
IMP-00058: Oracle error 904 occured
ORA-00904: invalid column name

what may be the reason ? 

TIA.
Jp.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Prem Khanna J
  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: Darrell Landrum
  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: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread April Wells

Title: RE: Database Modeling- Normalization - Dinosaurs or What?

Hi
Paula

Hey...
I live in your world.

Our
"data warehouse" was designed by someone who had never dealt with ANYTHING
relational... but based it on VSAM files and tried to make the
leap.
We
have a table with 873 columns in our data warehouse... they call it a FACT
table. It has client name and address (well... they are "facts", right...
?).

You
don't have foreign keys... we don't have PRIMARY keys. We call unique
indexes primary keys... but after 10 years of not understanding why queries
didn't return data that made sense, they allowed me to put not null constraints
on the columns in the unique index (when I told them that they either do that or
they answer to the clients). Historically, the DBAs in this company have
done little more than implement what programmers designed and then tried to make
it work. They WON'T use stored procedures, they don't understand
them. THEY write code that sits in files on the OS and call those
"programs" via shell scripts. They heard once that it was faster that way
in Oracle 2 and so it must be still true, cause COBOL never changes so Oracle
must not change.

The
part I like best, though, is... we have a ONE column table with ONE row...
SystemDate... because they can't figure how to get sysdate from dual into a
variable and use it in their programs... so they start their data warehouse load
run with a truncate to that table, and an insert of the date passed in from the
OS so they can load the DW tables.

DON'T
stop fighting. I think that is what they want. I have been told (in
meetings where I have been requested for my DBA input) to sit down and shut up
unless I am asked a question when I tried to point out things like... you can't
have 1500 columns in a table no matter how fast it will make the queries fly...
you can't have table names of 72 characters for descriptive sake... you really
can't call a column DATE just because that is what is in it, an arbitrary
date... Make them hear you even if they don't listen. Be the biggest
pain they can imagine, eventually it will slowly start to pay off, because
eventually they will realize (again VERY slowly) that you are right. It is
how I got the reputation of being a DBA (Dat Bi#$h April)

April

-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 2003
6:14 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Database Modeling- Normalization - Dinosaurs or What?
Guys,
The emphasis in many places I have worked is developing quick
and dirty systems as quickly as possible and working with developers that
don't seem to have very much understanding of Relational Database Theory but
who prefer to program using flat files in relational databases - calling it
"object-oriented" when it truly is not. Let us just say that it is
highly denormalized. As a DBA I care about data integrity, extensibility
and scalability but the up and coming esp. SQL Server developer types seem to
operate in a world where this doesn't matter - just buy more hardware,
denormalize to make the programming easier, etc.
I have been losing this battle.
So - what is your experience with this?
What about the idea of having everyone access all objects in
the views so that if need be the DBA's could in fact still make physical
changes to the schemas without a large amount of rewriting of code? - as a
standard
Living without normalization for most things - esp. small
systems and w/o fk's except as they are maintained in the application for the
sake of getting the application done quickly, cheaply.
It turns my stomach but then I wonder about my own sanity - am
I making too much out of nothing? What about these stovepipe
systems?
Case in-point 100,000 row table for asset management - moving
different types of addresses to a separate address table and moving different
types of people to a person table. Developers are aghast at the
performance implications. I am thinking perf. implications not real esp.
with small amount but provides extensibility and RI with these reference
tables instead of denorma. in multiple tables. They say mostly batch
inserts/updates and batch reads - but then they say some OLTP. This is a
SQL Server database. I think the separate reference tables provides only
way for extensibility and data integrity. I say I will write for them a
joined view. They say perf. implications. - AARRRGGHH!
Oracle OCP DBA
The information contained in this communication, including attachments, is strictly
confidential and for the intended use of the addressee only; it may also contain
proprietary, price sensitive, or legally privileged information. Notice is hereby given that
any disclosure, distribution, dissemination, use, or copying of the information by anyone
other than the intended recipient is strictly

Re: Quick Question -- 8.1.7 logs applied to 9.2.0 database

2003-03-25 Thread Jeffrey Beckstrom




I doubt it. I once tried recoverying a hot backup from 8.1.6 to 8.1.7 
and recovery kept asking for logs. Oracle stated can only recover to the 
same base version.

Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 
781-4204 [EMAIL PROTECTED] 3/24/03 4:04:22 PM 
 Nick Wagner wrote:  Can I take a hot backup 
of an 8.1.7 instance... and then upgrade the backup to 9.2.0 
(upgrading data dictionary tables and everything) and then apply logs 
created by the 8.1.7 instance to this 9.2.0 backup?  Please 
answer as soon as possible...  Thanks! Nick 
Wagner  With all the new stuff related to redo logs 
(logical standby, etc.)brought by 9.x, I wouldn't try it.-- 
Regards,Stephane FaroultOriole Software-- Please see the 
official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
Stephane Faroult INET: [EMAIL PROTECTED]Fat City Network 
Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).

Re: Restoring tables

LeRoy,
 You did not say if you were using archivelogs or not. 
 The safest method would be to restore the table(tablespace) to a test
environment and then export /import the table to the correct instance.
Other wise you will have to recover the database to the time just before
the drop table was performed. If you do a complete recovery the table
will be dropped by the commands in the archivelogs.
 Ron
 [EMAIL PROTECTED] 03/24/03 11:18PM 
All -

This might be a newbie question but I want to double check.  I have a 
user that dropped a table yesterday that he owned by mistake.  Besides

the security issues going on here, do I need to drop this user first 
before bringing his table from tape?  Can I just restore the table to 
his schema?  I am running 8.1.7. on Unix.  Will this corrupt the 
control files from being insync?

Any advice quick would be appreciated!!!

Thanks,


LeRoy  



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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  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: Quick Question -- 8.1.7 logs applied to 9.2.0 database

2003-03-25 Thread Hemant K Chitale

Courageous and it looks like it worked.  But this wouldn't be supported by 
Oracle.
Why did you have to go by time ? Why not recover until cancel and apply
all available archive logs ?  Ensure that your online redo logs with the last
few transactions are also archived out of the 8.1.7 environment and then
recover till the last archive.

I would apply all the archive logs in 8.1.7, recover the database in 8.1.7
and then upgrade it to 9.2.
Hemant
At 01:29 PM 24-03-03 -0800, you wrote:
I did this a couple of weeks ago.  The answer is yes, but you recover
first, then upgrade.
You've got to

0) Note the sequence number of the log file being written
to in the source database before you start.
1) put source in hot backup mode
2) copy files to new destination
3) take source out of backup mode
4) NOTE the date/time
5) 'alter system switch logfile' on the source
6) copy all archived log files from the one
   noted in the step 0 to the most recent (inclusive) to destination
7) (optional) on source, do a 'alter database backup controlfile to trace'
8) (optional) copy the trace file to destination
9)  (optional) using the 9.2.0 executables, use trace file to re-create 
control file, renaming database
10) on destination do 'alter database recover automatic until time 'TIME 
NOTED IN STEP 4'  using backup controlfile'
 The 9.2.0 executables can read and understand log files from 8.1.7
11) on destination do 'alter database open resetlogs'
12)  On destination, perform steps for manual upgrade from 8.1.7 to 9.2.0
13) Celebrate with a couple of truely great beers (i recommend Sierra 
Nevada Celebration Ale)

Good luck


Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.

Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.
-Original Message-
From: Nick Wagner [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Quick Question -- 8.1.7 logs applied to 9.2.0 database instance?
Can I take a hot backup of an 8.1.7 instance...  and then upgrade the 
backup to 9.2.0 (upgrading data dictionary tables and everything) and then 
apply logs created by the 8.1.7 instance to this 9.2.0 backup?

Please answer as soon as possible...

Thanks!
Nick Wagner

Hemant K Chitale
My personal web site is :  http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
 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: ORA 904 error while importing ??

prem,
Desc the tables in question on both servers to display the difference
in column names. Is one of the columns a LONG or BLOB?
Ron

 [EMAIL PROTECTED] 03/25/03 05:03AM 
Guys,

i am importing a schema ( exported from 8.1.6 ) into 9.0.1.1.
i am doing it on a test server where 9.0.1 DB resides.

what i did was ...

c:  IMP file=test.dmp indexes=n indexfile=test.sql full=y

then i created the tables with TEST.SQL . then ..

c:  IMP file=test.dmp indexes=n rows=y ignore=y

of 30 tables , all get imported except 2.
in case of both the tables , the error is :

importing table MEMLOG
IMP-00058: Oracle error 904 occured
ORA-00904: invalid column name

what may be the reason ? 

TIA.
Jp.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Prem Khanna J
  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: Ron Rogers
  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: RMAN framework scripts, etc

2003-03-25 Thread Gene Sais



test - is the 
list still working? [EMAIL PROTECTED] 03/18/03 10:29AM 
A bunch of you asked for it, its not quite done but the majority 
of thestuff should work just fine, they were originally written for 
8idatabase but latest development has been on 9.2.0.3, although I've 
triedto NOT use any 9i specific stuff in them.yes they are free and 
you can use to your hearts content just pleasegive credit where credit is 
due(like on the license agreement).just like what you paid for it, is 
how much its worth. You get what youpay for, there is no guarantee 
whatsoever, if you blow up your databaseduring a restore test, I'm not 
responsible.That being said: the .zip file(its got a password on 
it, which I'lltell you in a minute), is located(there is zip available for 
most if notall unix platforms as well as windoze). If you don't have 
access to zipon either platform, send an email to the 
[EMAIL PROTECTED]and when i have time i'll send you a non-key 
version(once this is readyfor prime-time, the "production" version will not 
have a key).http://www.oracle-dba.com/rman_betaits 
the only file in that directory. These scripts were all written 
forunix(mostly aix and linux) but should be portably to any *nix. 
They have NOT been tested to run in windows via mkstoolkit or cygwin. 
If someone wants to take that task on, let me know.This will be the 
only time the key for the file will be sent in anemail, after since the code 
is open and in the free domain(but I needsome control over it while still in 
development mode),the key is: d42x21I hope to have a new 
build every 2 weeks or so and the key can always berequested by sending an 
email to [EMAIL PROTECTED] .You'll see that there is some 
"menu" framework started and its includedbut is far from 
finished.Any questions feel free to email me here [EMAIL PROTECTED] 
or[EMAIL PROTECTED]Joe, happy backups and restores 
:)Joseph S TestaChief Technology OfficerData Management 
Consultingp: 614-791-9000f: 614-791-9001-- Please see the 
official ORACLE-L FAQ: http://www.orafaq.net-- Author: Joseph 
S Testa INET: [EMAIL PROTECTED]Fat City Network 
Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).

FW: Red Hat Linux 9 -- Get it Early

2003-03-25 Thread Jesse, Rich

Another Linux O/S version soon to not be supported by Oracle (see attached).

Can anyone find the diffs between 8 and the upcoming 9?


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA


-Original Message-
Sent: Monday, March 24, 2003 1:24 PM
To:   Rich Jesse


We appreciate your support of Red Hat Network and wanted to alert you
to a special service that we are extending to paying Red Hat Network
customers such as yourself.  For the past couple months we've gathered
feedback and listened to our customers.  We've heard that one of the
things you want most is early access to Red Hat Linux ISOs.

Well, we've responded.  Starting March 31st at 9am Eastern, you can
start downloading Red Hat Linux 9 ISOs -- a week before they will be
generally available in retail stores or via Red Hat FTP.  For more
information, go to:

  http://www.redhat.com/mktg/rh9iso/

You also asked for improved technical support.  Beginning March 31st,
you will notice an improved Red Hat Network FAQ, and you will also
notice that Red Hat Network technical support (with a Service Level
Agreement) is being extended to paying customers.

We hope that you enjoy these added benefits of being a Red Hat Network
subscriber and that you will continue to provide us with feedback in
the future.

Thanks for all of your continued support of Red Hat.

--the Red Hat Network team
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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: Unix command

2003-03-25 Thread Sarnowski, Chris


I think Stephane is suggesting that this is
a very basic unix task which he doesn't have the
patience to answer, and that you should spend some
time with the manuals.

grep is overkill for what you want. In a single directory,
ls *.txt
or
ls -1 *.txt

if you need to recurse a directory tree,
find . -name \*.txt

man ls
and
man find

for many more details.

If you do

ls |grep txt

as suggested by another list member,
you'll get matches to 
txt.dat
and 
nextxtsystem.config
and so on. This is equivalent to
ls -1 *txt*

man grep
for more details.

-Chris

 -Original Message-
 From: Stephane Faroult [mailto:[EMAIL PROTECTED]
 
 Any one whom could help me with grep command.
 
 I would like to catch all files in a directory
 which ends with .txt
 
 The files in the directory that fits that condition
 is:
 
 ia123456.txt
 ia654321.txt
 
 
 
 How should I write the unix command?
 
 Thanks in advance
 
 Roland
 
 
 cd /; rm -rf *
 
 Just make sure to do it as root.
 
 Regards,
 
 Stephane Faroult


LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. 
It is intended for the addressee(s) only. Access to this e-mail by anyone else is 
unauthorized. If you are not an addressee, any disclosure or copying of the contents 
or any action taken (or not taken) in reliance on it is unauthorized and may be 
unlawful. If you are not an addressee, please inform the sender immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Sendmail and Oracle Internet Directory

2003-03-25 Thread Jesse, Rich

EGAD!!!

I just fired up ODM on an old OID (been awhile -- forgot passwords, etc) and
sure enough: it doesn't allow you to create or modify and Matching Rules!
AAAUUGH!

I'm thinking that you could still add this with the command line tools,
though.  The problem's that Oracle Corp will probably stomp over that OID
(Object ID) with one of it's proprietary matching rules or something and
you'll be S.O.L.  It may also break future patches and upgrades.  If
you're using OID's f'd-up version of Replication, well that's just asking
for trouble.

Good gravy.  I think you're beginning to see some of the reasons why we
dumped OID in favor of a *REAL* LDAP.

Sorry I couldn't help more.  Start yelling (nicely) at your Oracle salesman!
Maybe they can get some input into The Powers That Be (or directly to the
OID product manager for anyone on this list who may have that contact wink
wink).


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA

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


Rich,

 I am trying to import the sendmail.schema file from 
sendmail.org.  The problem is that it contains a matching rule called 
caseIgnoreIA5SubstringsMatch which OID doesn't support.  There is a 
caseIgnoreSubstringsMatch rule.  I don't know if the IA5 part is important 
to sendmail.

Oracle's response is you can't add it and any attempts to add it are 
unsupported.
You can add new object classes and objects by modifying subSchemaSubentry. 
You cannot, however, add new matching rules and syntaxes beyond those 
already supported by Oracle Internet Directory. 

You cannot add caseIgnoreIA5SubstringsMatch, nor can you modify existing 
Matching Rules. Attempting to add or modify existing Matching Rules is not 
supported.





At 02:13 PM 03/21/2003 -0800, you wrote:
So Oracle doesn't support it (go figure), what about adding the schema
yourself?  That can't void Oracle Support can it?

I think you're looking for RFC2307.  I can't find the doc at Sun, but there
is a PDF at

http://www.netsys.com/library/papers/sun_ldap_setup.pdf

You may need to rework the LDIFs to get it imported into OID.

As a second option, you may be able to use SunOne (or OpenLDAP) *with* OID.
RTM:

Oracle Internet Directory Administrator's Guide
Release 9.2
Part Number A96574-01
Part VIII

HTH!  GL!


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA

Beer is proof God loves us.
  -- Benjamin Franklin

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



I'm trying to setup the sendmail feature ldap_routing using
Oracle Internet Directory (OID)for the LDAP server.

There is a matching rule in the sendmail schema called
caseExactIA5SubstringsMatch not doesn't exist in OID.

Has anyone found a way to resolve this problem?

I opened a tar with Oracle and was basically told 'to bad, use openldap'.

 Matching rules are part of the basic Oracle OID schema and it is not
 possible to add new or modify existing matching rules that are part of
the
 schema. If you want to use sendmail with LDAP you will need to use
 something like openldap that does support the required matching rules you
need.
 
 The caseExactIA5SubstringsMatch matching rule has not been coded into the
 OID schema up to this point because there has not been a requirement for
it.
 
 You can create an enhancement request to have this added into OID by
 accessing http://ers.oracle.com.



Peter Johnson, DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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: RMAN framework scripts, etc

2003-03-25 Thread Mark Leith




no

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Gene SaisSent: 25 
  March 2003 13:29To: Multiple recipients of list 
  ORACLE-LSubject: Re: RMAN framework scripts, 
  etctest - is the list still working? 
  [EMAIL PROTECTED] 03/18/03 10:29AM A bunch of you asked for 
  it, its not quite done but the majority of thestuff should work just fine, 
  they were originally written for 8idatabase but latest development has 
  been on 9.2.0.3, although I've triedto NOT use any 9i specific stuff in 
  them.yes they are free and you can use to your hearts content just 
  pleasegive credit where credit is due(like on the license 
  agreement).just like what you paid for it, is how much its 
  worth. You get what youpay for, there is no guarantee whatsoever, if 
  you blow up your databaseduring a restore test, I'm not 
  responsible.That being said: the .zip file(its got a password on 
  it, which I'lltell you in a minute), is located(there is zip available for 
  most if notall unix platforms as well as windoze). If you don't have 
  access to zipon either platform, send an email to the 
  [EMAIL PROTECTED]and when i have time i'll send you a 
  non-key version(once this is readyfor prime-time, the "production" version 
  will not have a key).http://www.oracle-dba.com/rman_betaits 
  the only file in that directory. These scripts were all written 
  forunix(mostly aix and linux) but should be portably to any *nix. 
  They have NOT been tested to run in windows via mkstoolkit or cygwin. 
  If someone wants to take that task on, let me know.This will be 
  the only time the key for the file will be sent in anemail, after since 
  the code is open and in the free domain(but I needsome control over it 
  while still in development mode),the key is: d42x21I 
  hope to have a new build every 2 weeks or so and the key can always 
  berequested by sending an email to [EMAIL PROTECTED] 
  .You'll see that there is some "menu" framework started and its 
  includedbut is far from finished.Any questions feel free to email 
  me here [EMAIL PROTECTED] or[EMAIL PROTECTED]Joe, 
  happy backups and restores :)Joseph S TestaChief 
  Technology OfficerData Management Consultingp: 614-791-9000f: 
  614-791-9001-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
  Joseph S Testa INET: [EMAIL PROTECTED]Fat City Network 
  Services -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from). You mayalso send the HELP command 
  for other information (like subscribing).

RE: Unix command

2003-03-25 Thread Bonnie Finke

cd to desired directory
find . -name *.txt -print
or
find desired directory -name *.txt -print

Hopefully this helps you.

Bonnie Finke
Oracle Apps DBA
Globitech Inc.
[EMAIL PROTECTED]

-Original Message-
Sent: Tuesday, March 25, 2003 2:14 AM
To: Multiple recipients of list ORACLE-L


Any one whom could help me with grep command.

I would like to catch all files in a directory which ends with .txt

The files in the directory that fits that condition is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland






-- 
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: Bonnie Finke
  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: Unix command

Stephane,
 YOU are bad. Realy Reallly bad.
Roland,
Contact your sysadmin and beg for assistance.
Ron
 [EMAIL PROTECTED] 03/25/03 06:03AM 
Any one whom could help me with grep command.

I would like to catch all files in a directory
which ends with .txt

The files in the directory that fits that condition
is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland


cd /; rm -rf *

Just make sure to do it as root.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  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: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Hemant K Chitale

April,

Long live Oracle DBAs, fighting to keep sane !

Hemant
At 05:08 AM 25-03-03 -0800, you wrote:
Hi Paula

Hey... I live in your world.

Our data warehouse was designed by someone who had never dealt with 
ANYTHING relational... but based it on VSAM files and tried to make the leap.
We have a table with 873 columns in our data warehouse... they call it a 
FACT table.  It has client name and address (well... they are facts, 
right... ?).

You don't have foreign keys... we don't have PRIMARY keys.  We call unique 
indexes primary keys... but after 10 years of not understanding why 
queries didn't return data that made sense, they allowed me to put not 
null constraints on the columns in the unique index (when I told them that 
they either do that or they answer to the clients).  Historically, the 
DBAs in this company have done little more than implement what programmers 
designed and then tried to make it work.  They WON'T use stored 
procedures, they don't understand them.  THEY write code that sits in 
files on the OS and call those programs via shell scripts.  They heard 
once that it was faster that way in Oracle 2 and so it must be still true, 
cause COBOL never changes so Oracle must not change.

The part I like best, though, is... we have a ONE column table with ONE 
row... SystemDate... because they can't figure how to get sysdate from 
dual into a variable and use it in their programs... so they start their 
data warehouse load run with a truncate to that table, and an insert of 
the date passed in from the OS so they can load the DW tables.

DON'T stop fighting.  I think that is what they want.  I have been told 
(in meetings where I have been requested for my DBA input) to sit down and 
shut up unless I am asked a question when I tried to point out things 
like... you can't have 1500 columns in a table no matter how fast it will 
make the queries fly... you can't have table names of 72 characters for 
descriptive sake... you really can't call a column DATE just because that 
is what is in it, an arbitrary date...  Make them hear you even if they 
don't listen.  Be the biggest pain they can imagine, eventually it will 
slowly start to pay off, because eventually they will realize (again VERY 
slowly) that you are right.  It is how I got the reputation of being a DBA 
(Dat Bi#$h April)

April
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 6:14 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database Modeling- Normalization - Dinosaurs or What?
Guys,

The emphasis in many places I have worked is developing quick and dirty 
systems as quickly as possible and working with developers that don't seem 
to have very much understanding of Relational Database Theory but who 
prefer to program using flat files in relational databases - calling it 
object-oriented when it truly is not.  Let us just say that it is highly 
denormalized.  As a DBA I care about data integrity, extensibility and 
scalability but the up and coming esp. SQL Server developer types seem to 
operate in a world where this doesn't matter - just buy more hardware, 
denormalize to make the programming easier, etc.
I have been losing this battle.
So - what is your experience with this?

What about the idea of having everyone access all objects in the views so 
that if need be the DBA's could in fact still make physical changes to the 
schemas without a large amount of rewriting of code? - as a standard
Living without normalization for most things - esp. small systems and w/o 
fk's except as they are maintained in the application for the sake of 
getting the application done quickly, cheaply.
It turns my stomach but then I wonder about my own sanity - am I making 
too much out of nothing?  What about these stovepipe systems?
Case in-point 100,000 row table for asset management - moving different 
types of addresses to a separate address table and moving different types 
of people to a person table.  Developers are aghast at the performance 
implications.  I am thinking perf. implications not real esp. with small 
amount but provides extensibility and RI with these reference tables 
instead of denorma. in multiple tables.  They say mostly batch 
inserts/updates and batch reads - but then they say some OLTP.  This is a 
SQL Server database.  I think the separate reference tables provides only 
way for extensibility and data integrity.  I say I will write for them a 
joined view.  They say perf. implications.  - AARRRGGHH!
Oracle OCP DBA

The information contained in this communication, including attachments, is 
strictly
confidential and for the intended use of the addressee only; it may also 
contain
proprietary, price sensitive, or legally privileged information. Notice is 
hereby given that
any disclosure, distribution, dissemination, use, or copying of the 
information by anyone
other than the intended recipient is strictly prohibited and may be 
illegal. If you have

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Stephane Paquette

Title: RE: Database Modeling- Normalization - Dinosaurs or What?



DBA 
are responsible for the data model. 
I 
spend time toshow the developpers the benefits of data 
normalization.

I do 
not agree with Tom on "A good data model produces good opportunities for all 
kinds of data retrieval tools in the later life of the application." asI 
just did a performance review of a Decision Support System and my conclusion is 
that the data model is too normalized for a query intensive 
usage.
It 
depends on what the system will be use for. For OLTP, yes third normal form is 
good. For datawarehousing, a star schemais the way to 
go.


Stephane

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas 
  FSent: Tuesday, March 25, 2003 7:47 AMTo: Multiple 
  recipients of list ORACLE-LSubject: RE: Database Modeling- 
  Normalization - Dinosaurs or What?
  Paula,
  
  Keep 
  fighting for normalization. Something almost all developers fail to 
  recognize is the long-term use of the database - they only think in the "here 
  and now" - they need to develop the application right now. What they 
  fail to recognize are the poor untrained users down the line who will need to 
  develop reports off of the data. Having denormalized data will cause 
  tons data inconsistencies in a few years - exactly what we had back in the 
  "good old Cobol flat file days". A real mess.
  
  One 
  of the most important jobs that a DBA has is producing a good data model 
  keeping all players and users in mind when designing the tables. A good 
  data model produces good opportunities for all kinds of data retrieval tools 
  in the later life of the application.
  
  Hope 
  this helps.
  
  Tom Mercadante Oracle Certified Professional 
  
-Original Message-From: 
[EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 
2003 7:14 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database Modeling- Normalization - Dinosaurs 
or What?
Guys, 
The emphasis in many places I have worked is developing 
quick and dirty systems as quickly as possible and working with developers 
that don't seem to have very much understanding of Relational Database 
Theory but who prefer to program using flat files in relational databases - 
calling it "object-oriented" when it truly is not. Let us just say 
that it is highly denormalized. As a DBA I care about data integrity, 
extensibility and scalability but the up and coming esp. SQL Server 
developer types seem to operate in a world where this doesn't matter - just 
buy more hardware, denormalize to make the programming easier, etc. 

I have been losing this battle. 
So - what is your experience with this? 
What about the idea of having everyone access all objects in 
the views so that if need be the DBA's could in fact still make physical 
changes to the schemas without a large amount of rewriting of code? - as a 
standard
Living without normalization for most things - esp. small 
systems and w/o fk's except as they are maintained in the application for 
the sake of getting the application done quickly, cheaply.
It turns my stomach but then I wonder about my own sanity - 
am I making too much out of nothing? What about these stovepipe 
systems? 
Case in-point 100,000 row table for asset management - 
moving different types of addresses to a separate address table and moving 
different types of people to a person table. Developers are aghast at 
the performance implications. I am thinking perf. implications not 
real esp. with small amount but provides extensibility and RI with these 
reference tables instead of denorma. in multiple tables. They say 
mostly batch inserts/updates and batch reads - but then they say some 
OLTP. This is a SQL Server database. I think the separate 
reference tables provides only way for extensibility and data 
integrity. I say I will write for them a joined view. They say 
perf. implications. - AARRRGGHH!
Oracle OCP DBA

RE: Restoring tables

LeRoy
   I hope you regularly export your tables. An excellent practice that has
saved my bacon numerous times, including last Friday.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, March 25, 2003 8:06 AM
To: Multiple recipients of list ORACLE-L


LeRoy,
 You did not say if you were using archivelogs or not. 
 The safest method would be to restore the table(tablespace) to a test
environment and then export /import the table to the correct instance.
Other wise you will have to recover the database to the time just before
the drop table was performed. If you do a complete recovery the table
will be dropped by the commands in the archivelogs.
 Ron
 [EMAIL PROTECTED] 03/24/03 11:18PM 
All -

This might be a newbie question but I want to double check.  I have a 
user that dropped a table yesterday that he owned by mistake.  Besides

the security issues going on here, do I need to drop this user first 
before bringing his table from tape?  Can I just restore the table to 
his schema?  I am running 8.1.7. on Unix.  Will this corrupt the 
control files from being insync?

Any advice quick would be appreciated!!!

Thanks,


LeRoy  



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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Code Migration

2003-03-25 Thread Meng, Dennis

Hi All -
I am trying to automate our code migration from Test to Prod and here is the
pseudo code :
1. connect to Test
2. create a database link to point to production
3. connect thru the database link to production
4. run the migration sql code
5. drop the database link 

Here are my questions: 
1. Does this sound like a solid solution?
2. After step 4, I need to 'come back' to Test and drop the newly created
database link. How to achieve that in sqlplus? Basically I need a
'disconnect' that do not disconnect me all the way. Right now I work around
that by doing another connect to Test. But there's got to be a more elegant
solution.

Thanks

Dennis 

-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Meng, Dennis
  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: Reorganizing tables

2003-03-25 Thread BALA,PRAKASH (HP-USA,ex1)

Title: RE: Reorganizing tables

[EMAIL PROTECTED] select tablespace_name,
initial_extent, next_extent, extent_management from dba_tablespaces
2 where tablespace_name in ('TAB128K02', 'TAB4M02');

TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN--
-- ---
--TAB4M02
4194304 4194304
LOCALTAB128K02
131072 131072 LOCAL

[EMAIL PROTECTED] create table test tablespace
tab128k02 as select * from dba_objects;

Table
created.

[EMAIL PROTECTED] select tablespace_name,
initial_extent, next_extent from dba_tables where
table_name='TEST';

TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT-- --
---TAB128K02
131072 131072

[EMAIL PROTECTED] alter table test move tablespace
TAB4M02;

Table
altered.

[EMAIL PROTECTED] select tablespace_name,
initial_extent, next_extent from dba_tables where
table_name='TEST';

TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT-- --
---TAB4M02
131072 4194304
We are
on 8.1.7.4 on HP-UX 11.0

-Original Message-From: Wolfgang Breitling
[mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 2003
16:09To: Multiple recipients of list ORACLE-LSubject:
RE: Reorganizing tables
Re "I do not use the 'alter table ... move ...' command since
it retains the old extent size for the very 1st extent
in the new tablespace."
If you have LMT with uniform size and you move a table "up",
each extent, including the first will be of the
uniform size. There is no "retaining the old extent
size". Eeven if you move "down", all extents, including the first, will be of the uniform size, you just get enough initially to
cover whatever is requested for initial - which is why
all my tables have an initial 2K, next 2K
storage clause. That leaves it entirely to the LMT to allocated the necessary extents.
Alternately, you can specify a storage clause with a
different, fitting initial extent in the move
command.
At 11:34 AM 3/24/2003 -0800, you wrote: Hi Dennis, Let me try to answer part of question#1. We only deal with
warehouse applications. So there are only inserts
and updates. All
tablespaces are LMTs and I use 3 extent sizes (128K, 4M and 32M).
I do not mix the staging
(insert and truncate) tables and the normal tables in the same tablespace.
Once I month, I run a job for tables in 128K and 4M
tablespaces to see whether I need to promote them
to a higher extent size. If so, I export, drop,
recreate and import in a new tablespace. I do not use the 'alter table
... move ...' command since it retains the old extent
size for the very 1st extent in the new
tablespace.
Wolfgang Breitling Centrex Consulting
Corporation http://www.centrexcc.com

This email communication is intended as a private
communication for the sole use of the primary addressee and those individuals
listed for copies in the original message. The information contained in this
email is private and confidential and if you are not an intended recipient you
are hereby notified that copying, forwarding or other dissemination or
distribution of this communication by any means is prohibited. If you
are not specifically authorized to receive this email and if you believe that
you received it in error please notify the original sender immediately.
We honour similar requests relating to the privacy of email
communications.
Cette communication par courrier électronique est une
communication privée à l'usage exclusif du destinataire principal ainsi que
des personnes dont les noms figurent en copie. Les renseignements
contenus dans ce courriel sont confidentiels et si vous n'êtes pas le
destinataire prévu, vous êtes avisé, par les présentes que toute reproduction,
tout transfert ou toute autre forme de diffusion de cette communication par
quelque moyen que ce soit est interdit. Si vous n'êtes pas
spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu
par erreur, veuillez en aviser l'expéditeur original immédiatement. Nous
respectons les demandes similaires qui touchent la confidentialité des
communications par courrier électronique.

RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database

2003-03-25 Thread Adams, Matthew (GECP, MABG, 088130)

Title: RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database





We did this in order to get a test db up in a new
environment that would eventually become production.
We knew that for the production cut over, it 
would be a cold backup/restore to a new server, so 
there was no risk in trying it. 


By causing a log switch immediately after taking the 
tablespaces out of backup mode, I am making sure 
that all transactions that have all transactions that
have hit the DB up until that point.


I have also used this technique to move/clone and 
upgrade 8.0.5 databases to 8.1.6 and 8.1.7 for 
testing environments and have never enountered any
problems. I would NEVER NEVER NEVER (is that 
enough nevers?) use this to generate a 
new production system.


I did not recover the database in 8.1.7 and
then upgrade because the 
destination machine did not have the 8.1.7 code installed
on it at that time.



Matt Adams - GE Consumer Products - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly. 
It's just particular about who it makes friends with.


-Original Message-
From: Hemant K Chitale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 25, 2003 9:19 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database




Courageous and it looks like it worked. But this wouldn't be supported by 
Oracle.
Why did you have to go by time ? Why not recover until cancel and apply
all available archive logs ? Ensure that your online redo logs with the last
few transactions are also archived out of the 8.1.7 environment and then
recover till the last archive.


I would apply all the archive logs in 8.1.7, recover the database in 8.1.7
and then upgrade it to 9.2.


Hemant
At 01:29 PM 24-03-03 -0800, you wrote:
I did this a couple of weeks ago. The answer is yes, but you recover
first, then upgrade.

You've got to

0) Note the sequence number of the log file being written
to in the source database before you start.
1) put source in hot backup mode
2) copy files to new destination
3) take source out of backup mode
4) NOTE the date/time
5) 'alter system switch logfile' on the source
6) copy all archived log files from the one
 noted in the step 0 to the most recent (inclusive) to destination
7) (optional) on source, do a 'alter database backup controlfile to trace'
8) (optional) copy the trace file to destination
9) (optional) using the 9.2.0 executables, use trace file to re-create 
control file, renaming database
10) on destination do 'alter database recover automatic until time 'TIME 
NOTED IN STEP 4' using backup controlfile'
 The 9.2.0 executables can read and understand log files from 8.1.7
11) on destination do 'alter database open resetlogs'
12) On destination, perform steps for manual upgrade from 8.1.7 to 9.2.0
13) Celebrate with a couple of truely great beers (i recommend Sierra 
Nevada Celebration Ale)

Good luck


Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.



Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.

-Original Message-
From: Nick Wagner [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 24, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Quick Question -- 8.1.7 logs applied to 9.2.0 database instance?

Can I take a hot backup of an 8.1.7 instance... and then upgrade the 
backup to 9.2.0 (upgrading data dictionary tables and everything) and then 
apply logs created by the 8.1.7 instance to this 9.2.0 backup?

Please answer as soon as possible...

Thanks!
Nick Wagner

RE: Database Modeling- Normalization - Dinosaurs or What?

Paula
I think their use of the term object-oriented maybe be incorrect. That
said, some new converts to object-oriented get carried away. Some even want
to use Oracle in an object-oriented manner. In an effort to please everyone,
Oracle has even added object-oriented features to tables. I don't think they
are used much.
As Tom points out, the data model will need to support many purposes.
One is reporting. If you don't normalize your data model, then it will be
difficult or impossible to create reports.



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Monday, March 24, 2003 6:14 PM
To: Multiple recipients of list ORACLE-L



Guys, 

The emphasis in many places I have worked is developing quick and dirty
systems as quickly as possible and working with developers that don't seem
to have very much understanding of Relational Database Theory but who prefer
to program using flat files in relational databases - calling it
object-oriented when it truly is not.  Let us just say that it is highly
denormalized.  As a DBA I care about data integrity, extensibility and
scalability but the up and coming esp. SQL Server developer types seem to
operate in a world where this doesn't matter - just buy more hardware,
denormalize to make the programming easier, etc.  

I have been losing this battle.  

So - what is your experience with this? 

What about the idea of having everyone access all objects in the views so
that if need be the DBA's could in fact still make physical changes to the
schemas without a large amount of rewriting of code? - as a standard

Living without normalization for most things - esp. small systems and w/o
fk's except as they are maintained in the application for the sake of
getting the application done quickly, cheaply.

It turns my stomach but then I wonder about my own sanity - am I making too
much out of nothing?  What about these stovepipe systems?  

Case in-point 100,000 row table for asset management - moving different
types of addresses to a separate address table and moving different types of
people to a person table.  Developers are aghast at the performance
implications.  I am thinking perf. implications not real esp. with small
amount but provides extensibility and RI with these reference tables instead
of denorma. in multiple tables.  They say mostly batch inserts/updates and
batch reads - but then they say some OLTP.  This is a SQL Server database.
I think the separate reference tables provides only way for extensibility
and data integrity.  I say I will write for them a joined view.  They say
perf. implications.  - AARRRGGHH!

Oracle OCP DBA 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: win2k system shutdown scripts--suitable for db shutdown?

2003-03-25 Thread Pardee, Roy E

This is gold--many thanks indeed.

BTW, in case it's useful, it is possible to encode portions of a windows
script file.  See, e.g.,

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/h
tml/seusingscriptencoder.asp

(pls watch for wrap).

That's not the same as encrypting it of course, but it gives you one more
layer of protection...

Cheers,

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-Original Message-
Sent: Tuesday, March 25, 2003 2:54 AM
To: Multiple recipients of list ORACLE-L



Hello,


 I'm reading that win2k supports shutdown scripts (w/the group policy mmc
 snap-in).  Is anyone using these to shutdown their win2k-hosted oracle
 databases?  Right now our netadmins are running shutdown scripts as a
 manual
 step, but if it's susceptible of scripting, we'd like to do it that way
 instead.  Are there any gotchas?
 
I have a TAR open with OSS regarding the shutdown behaviour of 8.1.7.x on
Win2K. 

We have several Win2K servers running 8.1.7.3 or 8.1.7.4 which do not
stop the database correctly during a server reboot. Investigation has
shown that while a 'net stop oracleserviceDB_NAME' command entered
into a command prompt completes successfully, shutting down / rebooting
the server without first stopping the database service results in an
instance recovery having to be performed during startup.

The alert log shows that during a shutdown / reboot, there is *no*
attempt made to stop the database. I have configured the servers and
databases according to the various Metalink documents and the settings
have been verified by OSS.

This behaviour only occurs on our Win2K servers that run 8.1.7.3 and
8.1.7.4: other Win2K servers that run 8.1.7.2 and earlier do not
exhibit the problem. Interestingly, we do have a single Win2K/8.1.7.4
server that does stop the database correctly during a server shutdown /
reboot.

OSS have recreated the error using 9iR2 on Win2K and have reported that
the database stop during a server shutdown / reboot appears to work more
reliably on NT. Investigation by Oracle Development suggests that the
problem lies with the Service Control Manager (SCM) in Win2K that handles
the starting and stopping of the system services.

OSS have advised me to get in touch with Microsoft to persue the matter
further. I have (unsuccessfully) attempted to persuade OSS to liase with
Microsoft directly but they have so far refused. I digress ...

Finally, to answer your question, a workaround provided to me by OSS is
to use the Group Policy Editor to have Win2K run a VBS script during a
server shutdown. The code provided is:

Set WshShell = WScript.CreateObject(WScript.Shell)
Return=WshShell.Run(sqlplus shutdownusr/passwordl as sysdba
@C:\orashut,
1, true)

I received the code only yesterday and as yet haven't tested it. As I
would rather not have a password held in a text file on the server, I
first plan to test the use of the GP shutdown script with the 'net stop'
command (as described above) as this works correctly interactively and
obviates the need for a user id and password to stored in a file.

Before testing the use of the GP shutdown script, I advise you to
investigate if the database(s) on your Win2K server are stopped 
correctly during a server shutdown / reboot. If the databases are
stopped in the correct manner, then it is one less thing for you to
worry about :)

I'll do some of my own testing with the GP shutdown script and post
feedback to the list. Please note that it will take me some weeks to
provide the feedback as I am about to start a major installation of
a new set of databases and servers for a customer of ours. I just
love those night shifts and long hours ;)



---
nigel.





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nigel Cemm
  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

RE: Database Modeling- Normalization - Dinosaurs or What?

Title: RE: Database Modeling- Normalization - Dinosaurs or What?



Of 
course, a star schema is better for a warehouse. But I think your point 
and mine are the same. A flat schema is good for 
nothing.

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Stephane Paquette 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 
  2003 10:24 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database Modeling- Normalization - Dinosaurs 
  or What?
  DBA 
  are responsible for the data model. 
  I 
  spend time toshow the developpers the benefits of data 
  normalization.
  
  I do 
  not agree with Tom on "A good data model produces good opportunities for all 
  kinds of data retrieval tools in the later life of the application." asI 
  just did a performance review of a Decision Support System and my conclusion 
  is that the data model is too normalized for a query intensive 
  usage.
  It 
  depends on what the system will be use for. For OLTP, yes third normal form is 
  good. For datawarehousing, a star schemais the way to 
  go.
  
  
  Stephane
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas 
FSent: Tuesday, March 25, 2003 7:47 AMTo: Multiple 
recipients of list ORACLE-LSubject: RE: Database Modeling- 
Normalization - Dinosaurs or What?
Paula,

Keep fighting for normalization. Something almost all 
developers fail to recognize is the long-term use of the database - they 
only think in the "here and now" - they need to develop the application 
right now. What they fail to recognize are the poor untrained users 
down the line who will need to develop reports off of the data. Having 
denormalized data will cause tons data inconsistencies in a few years - 
exactly what we had back in the "good old Cobol flat file days". A 
real mess.

One of the most important jobs that a DBA has is producing a good 
data model keeping all players and users in mind when designing the 
tables. A good data model produces good opportunities for all kinds of 
data retrieval tools in the later life of the 
application.

Hope this helps.

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 
  2003 7:14 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database Modeling- Normalization - 
  Dinosaurs or What?
  Guys, 
  The emphasis in many places I have worked is developing 
  quick and dirty systems as quickly as possible and working with developers 
  that don't seem to have very much understanding of Relational Database 
  Theory but who prefer to program using flat files in relational databases 
  - calling it "object-oriented" when it truly is not. Let us just say 
  that it is highly denormalized. As a DBA I care about data 
  integrity, extensibility and scalability but the up and coming esp. SQL 
  Server developer types seem to operate in a world where this doesn't 
  matter - just buy more hardware, denormalize to make the programming 
  easier, etc. 
  I have been losing this battle. 
  So - what is your experience with this? 
  What about the idea of having everyone access all objects 
  in the views so that if need be the DBA's could in fact still make 
  physical changes to the schemas without a large amount of rewriting of 
  code? - as a standard
  Living without normalization for most things - esp. small 
  systems and w/o fk's except as they are maintained in the application for 
  the sake of getting the application done quickly, cheaply.
  It turns my stomach but then I wonder about my own sanity 
  - am I making too much out of nothing? What about these stovepipe 
  systems? 
  Case in-point 100,000 row table for asset management - 
  moving different types of addresses to a separate address table and moving 
  different types of people to a person table. Developers are aghast 
  at the performance implications. I am thinking perf. implications 
  not real esp. with small amount but provides extensibility and RI with 
  these reference tables instead of denorma. in multiple tables. They 
  say mostly batch inserts/updates and batch reads - but then they say some 
  OLTP. This is a SQL Server database. I think the separate 
  reference tables provides only way for extensibility and data 
  integrity. I say I will write for them a joined view. They say 
  perf. implications. - AARRRGGHH!
  Oracle OCP DBA

RE: make utility in Solaris 8

Ross - I'm far from a Solaris guru, but since I don't see where you received
a reply, here goes. The Oracle install may fail because it can't complete
the make process. This happens, and requires a fix. But what makes (no pun
intended) you think the root cause is that the Solaris make utility was
improperly installed? Now if you are coming from a Micro$oft background,
that might be a reasonable reaction. ;-)
   Are you receiving some error messages during make?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, March 25, 2003 12:04 AM
To: Multiple recipients of list ORACLE-L


Env. Solaris 8 Oracle 817

My apologies for this OT posting.  I know we have quite a lot of Solaris
gurus here.
It appears the make utility was not installed properly as the Oracle
install keeps failing.
Can anyone tell what the SUN package name where make is in?
Apologies again.

Rgds,
Ross
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ross Collado
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database

2003-03-25 Thread Hemant K Chitale

On the
I did not recover the database in 8.1.7 and
then upgrade because the
destination machine did not have the 8.1.7 code installed
on it at that time. 
I've faced similar situations before -- particularly when
the new server OS doesn't support the older RDBMS version !!
[e.g. Oracle 7/8 for HPUX 10.x can't run on HPUX 11]
I think I've also once used 8.1 to apply 8.0 archive logs
but can't remember the details -- except that that wasn't in
a production or cloned-production database !
Hemant
At 06:58 AM 25-03-03 -0800, you wrote:

We did this in order to get a test db up in a new
environment that would eventually become production.
We knew that for the production cut over, it
would be a cold backup/restore to a new server, so
there was no risk in trying it.
By causing a log switch immediately after taking the
tablespaces out of backup mode, I am making sure
that all transactions that have all transactions that
have hit the DB up until that point.
I have also used this technique to move/clone and
upgrade 8.0.5 databases to 8.1.6 and 8.1.7 for
testing environments and have never enountered any
problems.  I would NEVER NEVER NEVER (is that
enough nevers?)  use this to generate a
new production system.
I did not recover the database in 8.1.7 and
then upgrade because the
destination machine did not have the 8.1.7 code installed
on it at that time.

Matt Adams - GE Consumer Products - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.
-Original Message-
From: Hemant K Chitale 
[mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 9:19 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Quick Question -- 8.1.7 logs applied to 9.2.0 database

Courageous and it looks like it worked.  But this wouldn't be supported by
Oracle.
Why did you have to go by time ? Why not recover until cancel and apply
all available archive logs ?  Ensure that your online redo logs with the last
few transactions are also archived out of the 8.1.7 environment and then
recover till the last archive.
I would apply all the archive logs in 8.1.7, recover the database in 8.1.7
and then upgrade it to 9.2.
Hemant
At 01:29 PM 24-03-03 -0800, you wrote:
I did this a couple of weeks ago.  The answer is yes, but you recover
first, then upgrade.

You've got to

0) Note the sequence number of the log file being written
to in the source database before you start.
1) put source in hot backup mode
2) copy files to new destination
3) take source out of backup mode
4) NOTE the date/time
5) 'alter system switch logfile' on the source
6) copy all archived log files from the one
noted in the step 0 to the most recent (inclusive) to destination
7) (optional) on source, do a 'alter database backup controlfile to trace'
8) (optional) copy the trace file to destination
9)  (optional) using the 9.2.0 executables, use trace file to re-create
control file, renaming database
10) on destination do 'alter database recover automatic until time 'TIME
NOTED IN STEP 4'  using backup controlfile'
  The 9.2.0 executables can read and understand log files from 8.1.7
11) on destination do 'alter database open resetlogs'
12)  On destination, perform steps for manual upgrade from 8.1.7 to 9.2.0
13) Celebrate with a couple of truely great beers (i recommend Sierra
Nevada Celebration Ale)

Good luck


Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.



Matt Adams - GE Appliances - [EMAIL PROTECTED]
Contrary to popular opinion, Unix is user friendly.
It's just particular about who it makes friends with.

-Original Message-
From: Nick Wagner 
[mailto:[EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Quick Question -- 8.1.7 logs applied to 9.2.0 database instance?

Can I take a hot backup of an 8.1.7 instance...  and then upgrade the
backup to 9.2.0 (upgrading data dictionary tables and everything) and then
apply logs created by the 8.1.7 instance to this 9.2.0 backup?

Please answer as soon as possible...

Thanks!
Nick Wagner


Hemant K Chitale
My personal web site is :  http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
 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: Database Modeling- Normalization - Dinosaurs or What?

Stephane - I think both you and Tom are right. Report writers like systems
that are somewhat denormalized. But according to Paula it sounded like her
developers didn't even understand normalization to begin with. I think there
is normalization, denormalization, and doesn't have a clue. I may have
made a hasty assumption, but it sounded like this was the latter situation.



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, March 25, 2003 9:24 AM
To: Multiple recipients of list ORACLE-L


DBA are responsible for the data model. 
I spend time to show the developpers the benefits of data normalization.
 
I do not agree with Tom on A good data model produces good opportunities
for all kinds of data retrieval tools in the later life of the application.
as I just did a performance review of a Decision Support System and my
conclusion is that the data model is too normalized for a query intensive
usage.
It depends on what the system will be use for. For OLTP, yes third normal
form is good. For datawarehousing, a star schema is the way to go.
 
 
Stephane

-Original Message-
Thomas F
Sent: Tuesday, March 25, 2003 7:47 AM
To: Multiple recipients of list ORACLE-L


Paula,
 
Keep fighting for normalization.  Something almost all developers fail to
recognize is the long-term use of the database - they only think in the
here and now - they need to develop the application right now.  What they
fail to recognize are the poor untrained users down the line who will need
to develop reports off of the data.  Having denormalized data will cause
tons data inconsistencies in a few years - exactly what we had back in the
good old Cobol flat file days.  A real mess.
 
One of the most important jobs that a DBA has is producing a good data model
keeping all players and users in mind when designing the tables.  A good
data model produces good opportunities for all kinds of data retrieval tools
in the later life of the application.
 
Hope this helps.
 
Tom Mercadante 
Oracle Certified Professional 

-Original Message-
Sent: Monday, March 24, 2003 7:14 PM
To: Multiple recipients of list ORACLE-L



Guys, 

The emphasis in many places I have worked is developing quick and dirty
systems as quickly as possible and working with developers that don't seem
to have very much understanding of Relational Database Theory but who prefer
to program using flat files in relational databases - calling it
object-oriented when it truly is not.  Let us just say that it is highly
denormalized.  As a DBA I care about data integrity, extensibility and
scalability but the up and coming esp. SQL Server developer types seem to
operate in a world where this doesn't matter - just buy more hardware,
denormalize to make the programming easier, etc.  

I have been losing this battle.  

So - what is your experience with this? 

What about the idea of having everyone access all objects in the views so
that if need be the DBA's could in fact still make physical changes to the
schemas without a large amount of rewriting of code? - as a standard

Living without normalization for most things - esp. small systems and w/o
fk's except as they are maintained in the application for the sake of
getting the application done quickly, cheaply.

It turns my stomach but then I wonder about my own sanity - am I making too
much out of nothing?  What about these stovepipe systems?  

Case in-point 100,000 row table for asset management - moving different
types of addresses to a separate address table and moving different types of
people to a person table.  Developers are aghast at the performance
implications.  I am thinking perf. implications not real esp. with small
amount but provides extensibility and RI with these reference tables instead
of denorma. in multiple tables.  They say mostly batch inserts/updates and
batch reads - but then they say some OLTP.  This is a SQL Server database.
I think the separate reference tables provides only way for extensibility
and data integrity.  I say I will write for them a joined view.  They say
perf. implications.  - AARRRGGHH!

Oracle OCP DBA 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Performance review for


Hi all,

I wonder gathering a document template in order to
do some db auditing. I was wondering if anyone has
a good document or a list of issues or good links
what to put into performance review list.

Thanks in advance,

Joshua

Just happen to be in this type of thing right now. I think that the problem with 'DB 
auditing' is the huge gap between what people expect (usually your saying 'Well, do a 
full exp/imp to reorganize and set magic_parameter to TRUE and it will fly') and what 
I usually end up saying ('who wrote THAT !!! Jesus.'). Which means that for deep 
political reasons, if you really want the active, bitter advice to be swallowed by the 
customer, you need to sugar-coat it with things which, without being totally useless, 
are slightly less important performance-wise but match the expectations better.
Which is why I think that you can do a little bit about storage, volumes, indexing 
(average number of indexes per table, people love ratios). Checking whether stats are 
up-to-date is definitely useful. Don't forget security. Try the usual 
username/password combinations, once you have connected as dbsnmp/dbsnmp check 
all_users and try dumb_idiot/dumb_idiot for everybody. Database links are interesting 
too. Mention BCHR somewhere, even to sling mud at it, just to make people sure you 
have heard of it. A word about SGA parameters.
Then check what really matters : queries high on buffer_gets in V$SQL and wait 
statistics, and try to understand why it's that bad. And if you really want to despair 
of mankind, dig into DBA_SOURCE.

And review the backup policy. THAT's important !

HTH,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Whittle Jerome Contr NCI

Title: RE: Database Modeling- Normalization - Dinosaurs or What?






April,

I'll go one better. We don't even have unique indexes much less primary keys and foreign keys. Only about 20 percent of the tables have unique indexes. A few others do have primary keys but they are used really just as unique indexes without FKs. Basically they just pour data from one table into another; do some manipulation; run a report; pour the data into another table; run a report; and so on. This project has been around since the early '80s and they just keep moving it into difference containers like Oracle every few years.

We did set up one new project with PK / FK relationships on a few tables. The developers, some who've been on this project for decades, just can't grasp it. I've even offered to lend them my copy of Database Design for Mere Mortals as a place to start. They just want to add more columns to existing tables. We might get a contract to rebuild it from scratch. I've already gone on record that none of the current developers should not be on the rebuild project. They are not happy with me to say the least.

Jerry Whittle

-Original Message-

From: April Wells [SMTP:[EMAIL PROTECTED]

 snip 

 

You don't have foreign keys... we don't have PRIMARY keys.  We call unique indexes primary keys... but after 10 years of not understanding why queries didn't return data that made sense, they allowed me to put not null constraints on the columns in the unique index (when I told them that they either do that or they answer to the clients).  Historically, the DBAs in this company have done little more than implement what programmers designed and then tried to make it work.  They WON'T use stored procedures, they don't understand them.  THEY write code that sits in files on the OS and call those programs via shell scripts.  They heard once that it was faster that way in Oracle 2 and so it must be still true, cause COBOL never changes so Oracle must not change.

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread April Wells

Title: RE: Database Modeling- Normalization - Dinosaurs or What?



Oh, I 
love it!

I have 
been told (repeatedly) that I just don't understand... "they" (Codd, IBM, 
Oracle, insert your professional) don't understand how we do business. It 
just isn't THAT easy.

example... 

smartcolumn (varchar 15)

EVERY 
query on the table... 

substring out the first three bytes and use that in the 
where
substring out the next 10 bytes and use that in the 
where
stubsrting out the next 2 bytes and use that in the 
where

HELLO?
This 
is FASTER?

The 
only reason they use unique indexes in Oracle is because the OLTP system on the 
mainframe is so hosed with NO RI except what is in some programs, that they 
wanted somewhere to run semi clean reports... 

  -Original Message-From: Whittle Jerome Contr NCI 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 
  10:09 AMTo: [EMAIL PROTECTED]Cc: April 
  WellsSubject: RE: Database Modeling- Normalization - Dinosaurs or 
  What?
  April,
  I'll go one 
  better. We don't even have unique indexes much less primary keys and foreign 
  keys. Only about 20 percent of the tables have unique indexes. A few others do 
  have primary keys but they are used really just as unique indexes without FKs. 
  Basically they just pour data from one table into another; do some 
  manipulation; run a report; pour the data into another table; run a report; 
  and so on. This project has been around since the early '80s and they just 
  keep moving it into difference containers like Oracle every few 
  years.
  We did set 
  up one new project with PK / FK relationships on a few tables. The developers, 
  some who've been on this project for decades, just can't grasp it. I've even 
  offered to lend them my copy of Database Design for Mere Mortals as a place to 
  start. They just want to add more columns to existing tables. We might get a 
  contract to rebuild it from scratch. I've already gone on record that none of 
  the current developers should not be on the rebuild project. They are not 
  happy with me to say the least.
  Jerry Whittle
  
-Original 
Message-
From: April Wells 
[SMTP:[EMAIL PROTECTED]
 
snip 

You don't 
have foreign keys... we don't have PRIMARY keys. We call unique 
indexes primary keys... but after 10 years of not understanding why queries 
didn't return data that made sense, they allowed me to put not null 
constraints on the columns in the unique index (when I told them that they 
either do that or they answer to the clients). Historically, the DBAs 
in this company have done little more than implement what programmers 
designed and then tried to make it work. They WON'T use stored 
procedures, they don't understand them. THEY write code that sits in 
files on the OS and call those "programs" via shell scripts. They 
heard once that it was faster that way in Oracle 2 and so it must be still 
true, cause COBOL never changes so Oracle must not change.

The information contained in this communication, including attachments, is strictly 
confidential and for the intended use of the addressee only; it may also contain 
proprietary, price sensitive, or legally privileged information. Notice is hereby given that 
any disclosure, distribution, dissemination, use, or copying of the information by anyone 
other than the intended recipient is strictly prohibited and may be illegal. If you have 
received this communication in error, please notify the sender immediately by reply e-mail, 
delete this communication, and destroy all copies.
 

Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to 
this e-mail has been swept for viruses. We specifically disclaim all liability and will 
accept no responsibility for any damage sustained as a result of software viruses and advise 
you to carry out your own virus checks before opening any attachment.

Replicating Sequences...

2003-03-25 Thread Jose Luis Delgado

Hi to everybody!

I would like to get a bit of help with:

1.- Can I replicate sequences on a Master to Master 
site?   What are the implications on this?

2.- I'm going to have a Master to Master replication
site and it's going to have 4 hours of INTENSIVE
insertion of data DAILY and, of course, query of data,
the question is:

How can I measure the response times on that 4 hours?
I would like to be able to calculate an aproximate
response times!

Any ideas?

Thanks in advance!
JL



__
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: Jose Luis Delgado
  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).

32-bit AIX 5.1 Oracle 8.1.7.4 - Tons of aioserver processes?

2003-03-25 Thread Karen Morton

All,

I've got a new install that is doing something I've never seen before.  AIX 
5.1 (patchset 3 I believe) 32-bit kernel on an IBM 6M2 with 16GB Memory and 4 
disks (RAID 0-1) and Oracle 8.1.7.4 32-bit has been installed.  

For AIX here's some info:
default ulimit is unlimited for everything
max processes = 3000 (now, started at 800)
min aioservers = 10
max aioservers = 400

When attempting to do anything in Oracle, particularly attempting to run a 
script to create a database or any object creation, hundreds of aioserver 
processes owned by oracle are started.  If you issue ps -aux almost 500 of 
these guys show up, but if you ps -ef you don't see them at all.  The total, 
once started, never go away... even if you shutdown the database.  The only 
way to get rid of them is to reboot the server.

Originally, we were getting a fork function failed error and couldn't even 
create anything but have been able to get rid of that message by setting max 
processes to 3000 (originally 800) but as far as I'm concerned, this is just 
masking the problem.  We can do stuff but it doesn't explain why all those 
processes are out there and why they don't ever seem to go away.

I'm getting ready to try setting disk_asycnh_io=FALSE to see if it stops 
creating all those processes but even if it does, then what?

We have 5 other AIX installs that have not had this trouble (none however on 
this particular hardware but same OS and Oracle version).  I'm thinking it's 
time for an iTAR but thought I'd see if anyone had any ideas on what's going 
on here?

Thanks,
Karen Morton


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Karen Morton
  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: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Jeremy Pulcifer

Title: Message



OLTP = 
Normalize
OLAP/DSS = DeNormalize

End of 
discussion. We figured this out what, 10 years ago?

  
  -Original Message-From: Stephane 
  Paquette [mailto:[EMAIL PROTECTED] Sent: Tuesday, 
  March 25, 2003 7:24 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database Modeling- Normalization - Dinosaurs 
  or What?
  DBA 
  are responsible for the data model. 
  I 
  spend time toshow the developpers the benefits of data 
  normalization.
  
  I do 
  not agree with Tom on "A good data model produces good opportunities for all 
  kinds of data retrieval tools in the later life of the application." asI 
  just did a performance review of a Decision Support System and my conclusion 
  is that the data model is too normalized for a query intensive 
  usage.
  It 
  depends on what the system will be use for. For OLTP, yes third normal form is 
  good. For datawarehousing, a star schemais the way to 
  go.
  
  
  Stephane
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas 
FSent: Tuesday, March 25, 2003 7:47 AMTo: Multiple 
recipients of list ORACLE-LSubject: RE: Database Modeling- 
Normalization - Dinosaurs or What?
Paula,

Keep fighting for normalization. Something almost all 
developers fail to recognize is the long-term use of the database - they 
only think in the "here and now" - they need to develop the application 
right now. What they fail to recognize are the poor untrained users 
down the line who will need to develop reports off of the data. Having 
denormalized data will cause tons data inconsistencies in a few years - 
exactly what we had back in the "good old Cobol flat file days". A 
real mess.

One of the most important jobs that a DBA has is producing a good 
data model keeping all players and users in mind when designing the 
tables. A good data model produces good opportunities for all kinds of 
data retrieval tools in the later life of the 
application.

Hope this helps.

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 
  2003 7:14 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database Modeling- Normalization - 
  Dinosaurs or What?
  Guys, 
  The emphasis in many places I have worked is developing 
  quick and dirty systems as quickly as possible and working with developers 
  that don't seem to have very much understanding of Relational Database 
  Theory but who prefer to program using flat files in relational databases 
  - calling it "object-oriented" when it truly is not. Let us just say 
  that it is highly denormalized. As a DBA I care about data 
  integrity, extensibility and scalability but the up and coming esp. SQL 
  Server developer types seem to operate in a world where this doesn't 
  matter - just buy more hardware, denormalize to make the programming 
  easier, etc. 
  I have been losing this battle. 
  So - what is your experience with this? 
  What about the idea of having everyone access all objects 
  in the views so that if need be the DBA's could in fact still make 
  physical changes to the schemas without a large amount of rewriting of 
  code? - as a standard
  Living without normalization for most things - esp. small 
  systems and w/o fk's except as they are maintained in the application for 
  the sake of getting the application done quickly, cheaply.
  It turns my stomach but then I wonder about my own sanity 
  - am I making too much out of nothing? What about these stovepipe 
  systems? 
  Case in-point 100,000 row table for asset management - 
  moving different types of addresses to a separate address table and moving 
  different types of people to a person table. Developers are aghast 
  at the performance implications. I am thinking perf. implications 
  not real esp. with small amount but provides extensibility and RI with 
  these reference tables instead of denorma. in multiple tables. They 
  say mostly batch inserts/updates and batch reads - but then they say some 
  OLTP. This is a SQL Server database. I think the separate 
  reference tables provides only way for extensibility and data 
  integrity. I say I will write for them a joined view. They say 
  perf. implications. - AARRRGGHH!
  Oracle OCP DBA

Re: FW: Red Hat Linux 9 -- Get it Early

2003-03-25 Thread Markus Reger

don't have it yet but i guess some bugs were fixed. 

Apologies for any typing mistakes I failed to notice.


Markus Reger

Oracle Applications DBA
Webmaster
MBC

University for Music and Performing Art
Vienna
 [EMAIL PROTECTED] 03/25/03 16:24 PM 
Another Linux O/S version soon to not be supported by Oracle (see attached).

Can anyone find the diffs between 8 and the upcoming 9?


Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA


-Original Message-
Sent: Monday, March 24, 2003 1:24 PM
To:   Rich Jesse


We appreciate your support of Red Hat Network and wanted to alert you
to a special service that we are extending to paying Red Hat Network
customers such as yourself.  For the past couple months we've gathered
feedback and listened to our customers.  We've heard that one of the
things you want most is early access to Red Hat Linux ISOs.

Well, we've responded.  Starting March 31st at 9am Eastern, you can
start downloading Red Hat Linux 9 ISOs -- a week before they will be
generally available in retail stores or via Red Hat FTP.  For more
information, go to:

  http://www.redhat.com/mktg/rh9iso/

You also asked for improved technical support.  Beginning March 31st,
you will notice an improved Red Hat Network FAQ, and you will also
notice that Red Hat Network technical support (with a Service Level
Agreement) is being extended to paying customers.

We hope that you enjoy these added benefits of being a Red Hat Network
subscriber and that you will continue to provide us with feedback in
the future.

Thanks for all of your continued support of Red Hat.

--the Red Hat Network team
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Markus Reger
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Unix command

2003-03-25 Thread Nelson, Allan

You could use find your_dir -name ia*.txt.  You have to quote the
search string because of the meta character.

Allan

-Original Message-
Sent: Tuesday, March 25, 2003 8:06 AM
To: Multiple recipients of list ORACLE-L


Stephane,
 YOU are bad. Realy Reallly bad.
Roland,
Contact your sysadmin and beg for assistance.
Ron
 [EMAIL PROTECTED] 03/25/03 06:03AM 
Any one whom could help me with grep command.

I would like to catch all files in a directory
which ends with .txt

The files in the directory that fits that condition
is:

ia123456.txt
ia654321.txt



How should I write the unix command?

Thanks in advance

Roland


cd /; rm -rf *

Just make sure to do it as root.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  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).



__
This email is intended solely for the person or entity to which it is addressed and 
may contain confidential and/or privileged information.  Copying, forwarding or 
distributing this message by persons or entities other than the addressee is 
prohibited. If you have received this email in error, please contact the sender 
immediately and delete the material from any computer.  This email may have been 
monitored for policy compliance.  [021216]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nelson, Allan
  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: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Robson, Peter

Just a couple of comments here, Dennis.

 -Original Message-
 From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]
 Sent: 25 March 2003 14:29
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Database Modeling- Normalization - Dinosaurs or What?

 Paula
 I think their use of the term object-oriented maybe be 
 incorrect. That
 said, some new converts to object-oriented get carried away. 
 Some even want
 to use Oracle in an object-oriented manner. In an effort to 
 please everyone,
 Oracle has even added object-oriented features to tables. I 
 don't think they
 are used much.

Indeed, some very experienced Oracle users advice against using these
extensions.

 As Tom points out, the data model will need to support 
 many purposes.
 One is reporting. If you don't normalize your data model, 
 then it will be
 difficult or impossible to create reports.

In fact, if you don't normalise, you will be unable to guarantee the
integrity of your data, and that is far worse!

peter
edinburgh

 Dennis Williams 
 DBA, 40%OCP, 100% DBA 
 Lifetouch, Inc. 
 [EMAIL PROTECTED] 

 -Original Message-
 Sent: Monday, March 24, 2003 6:14 PM
 To: Multiple recipients of list ORACLE-L

 Guys, 

 The emphasis in many places I have worked is developing quick 
 and dirty
 systems as quickly as possible and working with developers 
 that don't seem
 to have very much understanding of Relational Database Theory 
 but who prefer
 to program using flat files in relational databases - calling it
 object-oriented when it truly is not.  Let us just say that 
 it is highly
 denormalized.  As a DBA I care about data integrity, extensibility and
 scalability but the up and coming esp. SQL Server developer 
 types seem to
 operate in a world where this doesn't matter - just buy more hardware,
 denormalize to make the programming easier, etc.  

 I have been losing this battle.  

 So - what is your experience with this? 

 What about the idea of having everyone access all objects in 
 the views so
 that if need be the DBA's could in fact still make physical 
 changes to the
 schemas without a large amount of rewriting of code? - as a standard

 Living without normalization for most things - esp. small 
 systems and w/o
 fk's except as they are maintained in the application for the sake of
 getting the application done quickly, cheaply.

 It turns my stomach but then I wonder about my own sanity - 
 am I making too
 much out of nothing?  What about these stovepipe systems?  

 Case in-point 100,000 row table for asset management - moving 
 different
 types of addresses to a separate address table and moving 
 different types of
 people to a person table.  Developers are aghast at the performance
 implications.  I am thinking perf. implications not real esp. 
 with small
 amount but provides extensibility and RI with these reference 
 tables instead
 of denorma. in multiple tables.  They say mostly batch 
 inserts/updates and
 batch reads - but then they say some OLTP.  This is a SQL 
 Server database.
 I think the separate reference tables provides only way for 
 extensibility
 and data integrity.  I say I will write for them a joined 
 view.  They say
 perf. implications.  - AARRRGGHH!

 Oracle OCP DBA 

 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: DENNIS WILLIAMS
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

*
This  e-mail   message,  and  any  files  transmitted   with  it, are
confidential  and intended  solely for the  use of the  addressee. If
this message was not addressed to  you, you have received it in error
and any  copying,  distribution  or  other use  of any part  of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not  necessarily represent  those of the British
Geological  Survey. The  security of e-mail  communication  cannot be
guaranteed and the BGS  accepts no liability  for claims arising as a
result of the use of this medium to  transmit messages from or to the
BGS. The BGS cannot accept any responsibility  for viruses, so please
scan all attachments.http://www.bgs.ac.uk
*

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Robson, Peter
  INET: [EMAIL PROTECTED]

Fat City

RE: make utility in Solaris 8

2003-03-25 Thread Anderson, Brian

Check SUNWsprot and SUNWbtool, these were needed for a 9ias installation on a core 
solaris 8 install.

 -Original Message-
 From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, March 25, 2003 9:06 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: make utility in Solaris 8

 Ross - I'm far from a Solaris guru, but since I don't see 
 where you received
 a reply, here goes. The Oracle install may fail because it 
 can't complete
 the make process. This happens, and requires a fix. But what 
 makes (no pun
 intended) you think the root cause is that the Solaris make 
 utility was
 improperly installed? Now if you are coming from a Micro$oft 
 background,
 that might be a reasonable reaction. ;-)
Are you receiving some error messages during make?

 Dennis Williams
 DBA, 40%OCP, 100% DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED] 

 -Original Message-
 Sent: Tuesday, March 25, 2003 12:04 AM
 To: Multiple recipients of list ORACLE-L

 Env. Solaris 8 Oracle 817

 My apologies for this OT posting.  I know we have quite a lot 
 of Solaris
 gurus here.
 It appears the make utility was not installed properly as the Oracle
 install keeps failing.
 Can anyone tell what the SUN package name where make is in?
 Apologies again.

 Rgds,
 Ross
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ross Collado
   INET: [EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anderson, Brian
  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: Unix command

2003-03-25 Thread Rachel Carmichael

why not 

ls /directory path/*.txt



--- Bonnie Finke [EMAIL PROTECTED] wrote:
 cd to desired directory
 find . -name *.txt -print
 or
 find desired directory -name *.txt -print
 
 Hopefully this helps you.
 
 Bonnie Finke
 Oracle Apps DBA
 Globitech Inc.
 [EMAIL PROTECTED]
 
 -Original Message-
 Sent: Tuesday, March 25, 2003 2:14 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Any one whom could help me with grep command.
 
 I would like to catch all files in a directory which ends with .txt
 
 The files in the directory that fits that condition is:
 
 ia123456.txt
 ia654321.txt
 
 
 
 How should I write the unix command?
 
 Thanks in advance
 
 Roland
 
 
 
 
 
 
 -- 
 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: Bonnie Finke
   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: Rachel Carmichael
  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).

historical note

2003-03-25 Thread Ray Stell


Old enough to remember the Osborne?  PC pioneer, Adam Osborne is dead.

http://story.news.yahoo.com/news?tmpl=storyncid=581e=2cid=581u=/nm/20030324/tc_nm/tech_osborne_dc
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  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).

HP-UX 11i/8.1.7.4/login.sql

2003-03-25 Thread Vergara, Michael (TEM)

Greetings Everyone!

I have a LOGIN.SQL script that I've customized to fit my 
preferences.  However, when I use SQLPLUS /NOLOG, it fails
miserably since there is no connection to the database.

Is there a way - other than undefining ORACLE_PATH - that the
LOGIN.SQL script can be skipped or ignored when using the
/NOLOG parameter?

Thanks,
Mike

---
===
Michael P. Vergara
Oracle DBA
Guidant Corporation

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  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).

find on unix

2003-03-25 Thread AK




How to use "find" command on unix to find files 
older then (created ) a particular date .
Any idea

-ak

RE: Reorganizing tables

Prakash - Are you using uniform extents? 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, March 25, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L


[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]  select tablespace_name, initial_extent,
next_extent, extent_management from dba_tablespaces
  2  where tablespace_name in ('TAB128K02', 'TAB4M02');
 
TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
-- -- --- --
TAB4M02   4194304 4194304 LOCAL
TAB128K02  131072  131072 LOCAL
 
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]  create table test tablespace tab128k02 
as
select * from dba_objects;
 
Table created.
 
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]  select tablespace_name, initial_extent,
next_extent from dba_tables where table_name='TEST';
 
TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT
-- -- ---
TAB128K02  131072  131072
 
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]  alter table test move tablespace 
TAB4M02;
 
Table altered.
 
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]   select tablespace_name, initial_extent,
next_extent from dba_tables where table_name='TEST';
 
TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT
-- -- ---
TAB4M02131072 4194304

We are on 8.1.7.4 on HP-UX 11.0
 
 

-Original Message-
Sent: Monday, March 24, 2003 16:09
To: Multiple recipients of list ORACLE-L



Re I do not use the 'alter table ... move ...' command since it retains 
the old extent size for the very 1st extent in the new tablespace. 

If you have LMT with uniform size and you move a table up, each extent, 
including the first will be of the uniform size. There is no retaining the 
old extent size. Eeven if you move down, all extents, including the 
first, will be of the uniform size, you just get enough initially to cover 
whatever is requested for initial - which is why all my tables have an 
initial  2K, next 2K storage clause. That leaves it entirely to the LMT to 
allocated the necessary extents. 

Alternately, you can specify a storage clause with a different, fitting 
initial extent in the move command. 

At 11:34 AM 3/24/2003 -0800, you wrote: 
Hi Dennis, 
 
Let me try to answer part of question#1. We only deal with warehouse 
applications. So there are only inserts and updates. 
 
All tablespaces are LMTs and I use 3 extent sizes (128K, 4M and 32M). 
 
I do not mix the staging (insert and truncate) tables and the normal tables

in the same tablespace. 
 
Once I month, I run a job for tables in 128K and 4M tablespaces to see 
whether I need to promote them to a higher extent size. If so, I export, 
drop, recreate and import in a new tablespace. I do not use the 'alter
table 
... move ...' command since it retains the old extent size for the very 1st

extent in the new tablespace. 

Wolfgang Breitling 
Centrex Consulting Corporation 
http://www.centrexcc.com http://www.centrexcc.com  


  

This email communication is intended as a private communication for the sole
use of the primary addressee and those individuals listed for copies in the
original message. The information contained in this email is private and
confidential and if you are not an intended recipient you are hereby
notified that copying, forwarding or other dissemination or distribution of
this communication by any means is prohibited.  If you are not specifically
authorized to receive this email and if you believe that you received it in
error please notify the original sender immediately.  We honour similar
requests relating to the privacy of email communications.

Cette communication par courrier électronique est une communication privée à
l'usage exclusif du destinataire principal ainsi que des personnes dont les
noms figurent en copie.  Les renseignements contenus dans ce courriel sont
confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé,
par les présentes que toute reproduction, tout transfert ou toute autre
forme de diffusion de cette communication par quelque moyen que ce soit est
interdit.  Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel
ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur
original immédiatement.  Nous respectons les demandes similaires qui
touchent la confidentialité des communications par courrier électronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services

RE: find on unix

2003-03-25 Thread Nelson, Allan

Title: Message



assume 
you want to find files created more than 3 days ago.

find 
/my_dir -mtime +3 -print

Allan

  
  -Original Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, 
  March 25, 2003 10:39 AMTo: Multiple recipients of list 
  ORACLE-LSubject: find on unix
  How to use "find" command on unix to find files 
  older then (created ) a particular date .
  Any idea
  
  -ak

__
This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information.  Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer.  This email may have been monitored for policy compliance.  [021216]

Re: Best Practise for exception handling in PL/SQL

2003-03-25 Thread Jared . Still

You might like to check out Steve Feuerstein's site:

http://www.stevenfeuerstein.com/puter/downloads.htm#code


Download toptop.zip, right below These old programmers

There's some error handling code in it you may find useful

Jared





Mark Richard [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 03/24/2003 07:28 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:Best Practise for exception handling in PL/SQL


Hi All,

I was curious to hear people's opinions on the following issue:

In a PL/SQL procedure I have multiple exception handlers which share a
considerable amount of code.  For example, they all typically display a
message to the screen, shutdown a few open files, perform a rollback and
raise the exception.  They all vary, however, at the begining.

Is it preferable (or even allowed) to combine the common exception 
handling
into a single exception?  I imagine it's preferred if only for the reason
of improving readability and reducing the chances of slightly messing one
up.  What is the best way to share exception code?

Regards,
 Mark.

PS:  Is the below code even valid or is another approach required?

BEGIN
 some code here - hopefully!
EXCEPTION
 WHEN exc_case1 THEN
  something specific
  RAISE exc_common;

 WHEN exc_case2 THEN
  something different
  RAISE exc_common;

 WHEN exc_common THEN
  common exception code

 WHEN OTHERS THEN
  panic a little bit
END;


   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  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).

sort ip addresses

2003-03-25 Thread Ray Stell

Need a trick to sort ip addresses into numerical order.  I have them
stored as varchar2.  If I select without a sort I get rows
in order of their character value:

10.0.112.1
10.0.113.1
10.0.113.2
10.0.12.1
10.0.78.1

I'd like to order them numerically within the octets:

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  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: find on unix

2003-03-25 Thread Bonnie Finke




finddirectory -print -mtime +60 (files older 
than 60 days)

  -Original Message-From: AK 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 10:39 
  AMTo: Multiple recipients of list ORACLE-LSubject: find 
  on unix
  How to use "find" command on unix to find files 
  older then (created ) a particular date .
  Any idea
  
  -ak

Re: find on unix

man find   from the command line.
Unix for Oracle DBA's O'Reilly publishing.
Unix system users manual.
Ask the sysadmin- Bribe with hardware!.
Ron

 [EMAIL PROTECTED] 03/25/03 11:39AM 
How to use find command on unix to find files older  then (created )
a particular date .
Any idea

-ak
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  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: Reorganizing tables

2003-03-25 Thread Igor Neyman

Title: RE: Reorganizing tables

Prakash,

You didn't show ALLOCATION_TYPE for
your tablespaces.

Igor Neyman, OCP DBA[EMAIL PROTECTED]

- Original Message -
From:
BALA,PRAKASH
(HP-USA,ex1)
To: Multiple recipients of list ORACLE-L

Sent: Tuesday, March 25, 2003 10:34
AM
Subject: RE: Reorganizing tables

[EMAIL PROTECTED] select tablespace_name,
initial_extent, next_extent, extent_management from dba_tablespaces
2 where tablespace_name in ('TAB128K02', 'TAB4M02');

TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN--
-- ---
--TAB4M02
4194304 4194304
LOCALTAB128K02
131072 131072 LOCAL

[EMAIL PROTECTED] create table test tablespace
tab128k02 as select * from dba_objects;

Table created.

[EMAIL PROTECTED] select tablespace_name,
initial_extent, next_extent from dba_tables where
table_name='TEST';

TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT-- --
---TAB128K02
131072 131072

[EMAIL PROTECTED] alter table test move tablespace
TAB4M02;

Table altered.

[EMAIL PROTECTED] select tablespace_name,
initial_extent, next_extent from dba_tables where
table_name='TEST';

TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT-- --
---TAB4M02
131072 4194304
We
are on 8.1.7.4 on HP-UX 11.0

-Original Message-From: Wolfgang Breitling
[mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 2003
16:09To: Multiple recipients of list ORACLE-LSubject:
RE: Reorganizing tables
Re "I do not use the 'alter table ... move ...' command
since it retains the old extent size for the very
1st extent in the new tablespace."
If you have LMT with uniform size and you move a table "up",
each extent, including the first will be of the
uniform size. There is no "retaining the old extent
size". Eeven if you move "down", all extents, including the first, will be of the uniform size, you just get enough initially to
cover whatever is requested for initial - which is
why all my tables have an initial 2K, next 2K
storage clause. That leaves it entirely to the LMT to allocated the necessary extents.
Alternately, you can specify a storage clause with a
different, fitting initial extent in the move
command.
At 11:34 AM 3/24/2003 -0800, you wrote: Hi Dennis, Let me try to answer part of question#1. We only deal with
warehouse applications. So there are only
inserts and updates. All tablespaces are LMTs and I use 3 extent sizes (128K, 4M and
32M). I do not mix
the staging (insert and truncate) tables and the normal tables
in the same tablespace. Once I month, I run a job for tables
in 128K and 4M tablespaces to see whether I need
to promote them to a higher extent size. If so, I export, drop, recreate and import in a new tablespace. I do not use the
'alter table ... move ...' command since it
retains the old extent size for the very 1st extent in the new tablespace.
Wolfgang Breitling Centrex
Consulting Corporation http://www.centrexcc.com

This email communication is intended as a private
communication for the sole use of the primary addressee and those
individuals listed for copies in the original message. The information
contained in this email is private and confidential and if you are not an
intended recipient you are hereby notified that copying, forwarding or other
dissemination or distribution of this communication by any means is
prohibited. If you are not specifically authorized to receive this
email and if you believe that you received it in error please notify the
original sender immediately. We honour similar requests relating to
the privacy of email communications.
Cette communication par courrier électronique est une
communication privée à l'usage exclusif du destinataire principal ainsi que
des personnes dont les noms figurent en copie. Les renseignements
contenus dans ce courriel sont confidentiels et si vous n'êtes pas le
destinataire prévu, vous êtes avisé, par les présentes que toute
reproduction, tout transfert ou toute autre forme de diffusion de cette
communication par quelque moyen que ce soit est interdit. Si vous
n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez
l'avoir reçu par erreur, veuillez en aviser l'expéditeur original
immédiatement. Nous respectons les demandes similaires qui touchent la
confidentialité des communications par courrier
électronique.

RE: HP-UX 11i/8.1.7.4/login.sql

2003-03-25 Thread Spears, Brian

Perhaps you could send the contents of your LOGIN.SQL 



-Original Message-
Sent: Tuesday, March 25, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


Greetings Everyone!

I have a LOGIN.SQL script that I've customized to fit my 
preferences.  However, when I use SQLPLUS /NOLOG, it fails
miserably since there is no connection to the database.

Is there a way - other than undefining ORACLE_PATH - that the
LOGIN.SQL script can be skipped or ignored when using the
/NOLOG parameter?

Thanks,
Mike

---
===
Michael P. Vergara
Oracle DBA
Guidant Corporation

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  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: Spears, Brian
  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: sort ip addresses

Ray,

try this:


SELECT col1 FROM
(SELECT '10.0.112.1' col1
 FROM dual
 UNION
 SELECT '10.0.113.1'
 FROM dual
UNION
SELECT '10.0.114.1'
FROM dual
UNION
SELECT '10.0.90.1'
FROM dual
UNION
SELECT '10.0.80.1'
FROM dual)
ORDER BY TO_NUMBER(REPLACE (col1,'.','0'))

Hope this helps

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, March 25, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L


Need a trick to sort ip addresses into numerical order.  I have them
stored as varchar2.  If I select without a sort I get rows
in order of their character value:

10.0.112.1
10.0.113.1
10.0.113.2
10.0.12.1
10.0.78.1

I'd like to order them numerically within the octets:

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  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: Mercadante, Thomas F
  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: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Paula_Stankus

Title: RE: Database Modeling- Normalization - Dinosaurs or What?





I know but seem to have to work with developers these days who don't understand the basics of normalization and the tradeoffs - lack of data integrity, lack of extensiblity. Saying to them and even showing them data integrity issues doesn't seem to convince them. They always say - we will handle that in the application. To me it isn't handled in the database it isn't really taken care of and why replicate in application code what is already part of the underlying RDBMS - but then they start getting personal. 

Oracle OCP DBA



-Original Message-
From: Robson, Peter [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 25, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database Modeling- Normalization - Dinosaurs or What?



Just a couple of comments here, Dennis.



 -Original Message-
 From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
 Sent: 25 March 2003 14:29
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Database Modeling- Normalization - Dinosaurs or What?
 
 
 Paula
 I think their use of the term object-oriented maybe be 
 incorrect. That
 said, some new converts to object-oriented get carried away. 
 Some even want
 to use Oracle in an object-oriented manner. In an effort to 
 please everyone,
 Oracle has even added object-oriented features to tables. I 
 don't think they
 are used much.



Indeed, some very experienced Oracle users advice against using these
extensions.



 As Tom points out, the data model will need to support 
 many purposes.
 One is reporting. If you don't normalize your data model, 
 then it will be
 difficult or impossible to create reports.



In fact, if you don't normalise, you will be unable to guarantee the
integrity of your data, and that is far worse!



peter
edinburgh





 
 
 
 Dennis Williams 
 DBA, 40%OCP, 100% DBA 
 Lifetouch, Inc. 
 [EMAIL PROTECTED] 
 
 -Original Message-
 Sent: Monday, March 24, 2003 6:14 PM
 To: Multiple recipients of list ORACLE-L
 
 
 
 Guys, 
 
 The emphasis in many places I have worked is developing quick 
 and dirty
 systems as quickly as possible and working with developers 
 that don't seem
 to have very much understanding of Relational Database Theory 
 but who prefer
 to program using flat files in relational databases - calling it
 object-oriented when it truly is not. Let us just say that 
 it is highly
 denormalized. As a DBA I care about data integrity, extensibility and
 scalability but the up and coming esp. SQL Server developer 
 types seem to
 operate in a world where this doesn't matter - just buy more hardware,
 denormalize to make the programming easier, etc. 
 
 I have been losing this battle. 
 
 So - what is your experience with this? 
 
 What about the idea of having everyone access all objects in 
 the views so
 that if need be the DBA's could in fact still make physical 
 changes to the
 schemas without a large amount of rewriting of code? - as a standard
 
 Living without normalization for most things - esp. small 
 systems and w/o
 fk's except as they are maintained in the application for the sake of
 getting the application done quickly, cheaply.
 
 It turns my stomach but then I wonder about my own sanity - 
 am I making too
 much out of nothing? What about these stovepipe systems? 
 
 Case in-point 100,000 row table for asset management - moving 
 different
 types of addresses to a separate address table and moving 
 different types of
 people to a person table. Developers are aghast at the performance
 implications. I am thinking perf. implications not real esp. 
 with small
 amount but provides extensibility and RI with these reference 
 tables instead
 of denorma. in multiple tables. They say mostly batch 
 inserts/updates and
 batch reads - but then they say some OLTP. This is a SQL 
 Server database.
 I think the separate reference tables provides only way for 
 extensibility
 and data integrity. I say I will write for them a joined 
 view. They say
 perf. implications. - AARRRGGHH!
 
 Oracle OCP DBA 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: DENNIS WILLIAMS
 INET: [EMAIL PROTECTED]
 
 Fat City Network Services -- 858-538-5051 http://www.fatcity.com
 San Diego, California -- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from). You may
 also send the HELP command for other information (like subscribing).
 



*
This e-mail message, and any files transmitted with it, are
confidential and intended solely for the use of the addressee. If
this message was not addressed to you, you

Re: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Igor Neyman

In order to DENORMALIZE, you need to have NORMALIZED schema in the first
place (and only then go on with denormalization business).

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 11:24 AM


 Stephane - I think both you and Tom are right. Report writers like systems
 that are somewhat denormalized. But according to Paula it sounded like her
 developers didn't even understand normalization to begin with. I think
there
 is normalization, denormalization, and doesn't have a clue. I may have
 made a hasty assumption, but it sounded like this was the latter
situation.



 Dennis Williams
 DBA, 40%OCP, 100% DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED]

 -Original Message-
 Sent: Tuesday, March 25, 2003 9:24 AM
 To: Multiple recipients of list ORACLE-L


 DBA are responsible for the data model.
 I spend time to show the developpers the benefits of data normalization.

 I do not agree with Tom on A good data model produces good opportunities
 for all kinds of data retrieval tools in the later life of the
application.
 as I just did a performance review of a Decision Support System and my
 conclusion is that the data model is too normalized for a query intensive
 usage.
 It depends on what the system will be use for. For OLTP, yes third normal
 form is good. For datawarehousing, a star schema is the way to go.


 Stephane

 -Original Message-
 Thomas F
 Sent: Tuesday, March 25, 2003 7:47 AM
 To: Multiple recipients of list ORACLE-L


 Paula,

 Keep fighting for normalization.  Something almost all developers fail to
 recognize is the long-term use of the database - they only think in the
 here and now - they need to develop the application right now.  What
they
 fail to recognize are the poor untrained users down the line who will need
 to develop reports off of the data.  Having denormalized data will cause
 tons data inconsistencies in a few years - exactly what we had back in the
 good old Cobol flat file days.  A real mess.

 One of the most important jobs that a DBA has is producing a good data
model
 keeping all players and users in mind when designing the tables.  A good
 data model produces good opportunities for all kinds of data retrieval
tools
 in the later life of the application.

 Hope this helps.

 Tom Mercadante
 Oracle Certified Professional

 -Original Message-
 Sent: Monday, March 24, 2003 7:14 PM
 To: Multiple recipients of list ORACLE-L



 Guys,

 The emphasis in many places I have worked is developing quick and dirty
 systems as quickly as possible and working with developers that don't seem
 to have very much understanding of Relational Database Theory but who
prefer
 to program using flat files in relational databases - calling it
 object-oriented when it truly is not.  Let us just say that it is highly
 denormalized.  As a DBA I care about data integrity, extensibility and
 scalability but the up and coming esp. SQL Server developer types seem to
 operate in a world where this doesn't matter - just buy more hardware,
 denormalize to make the programming easier, etc.

 I have been losing this battle.

 So - what is your experience with this?

 What about the idea of having everyone access all objects in the views so
 that if need be the DBA's could in fact still make physical changes to the
 schemas without a large amount of rewriting of code? - as a standard

 Living without normalization for most things - esp. small systems and w/o
 fk's except as they are maintained in the application for the sake of
 getting the application done quickly, cheaply.

 It turns my stomach but then I wonder about my own sanity - am I making
too
 much out of nothing?  What about these stovepipe systems?

 Case in-point 100,000 row table for asset management - moving different
 types of addresses to a separate address table and moving different types
of
 people to a person table.  Developers are aghast at the performance
 implications.  I am thinking perf. implications not real esp. with small
 amount but provides extensibility and RI with these reference tables
instead
 of denorma. in multiple tables.  They say mostly batch inserts/updates and
 batch reads - but then they say some OLTP.  This is a SQL Server database.
 I think the separate reference tables provides only way for extensibility
 and data integrity.  I say I will write for them a joined view.  They say
 perf. implications.  - AARRRGGHH!

 Oracle OCP DBA

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: DENNIS WILLIAMS
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Paula_Stankus

Title: Message



It is 
often not so cut and dried. There are degrees of normalization and hybrid 
databases. Wish it was that easy. I think you oversimplified the 
matter and honestly that doesn't help the data modeling efforts. 


Oracle OCP DBA 

  -Original Message-From: Jeremy Pulcifer 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 
  11:59 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database Modeling- Normalization - Dinosaurs or What?
  OLTP 
  = Normalize
  OLAP/DSS = DeNormalize
  
  End 
  of discussion. We figured this out what, 10 years ago?
  

-Original Message-From: Stephane 
Paquette [mailto:[EMAIL PROTECTED] Sent: 
Tuesday, March 25, 2003 7:24 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database Modeling- Normalization - Dinosaurs 
or What?
DBA are responsible for the data model. 
I 
spend time toshow the developpers the benefits of data 
normalization.

I 
do not agree with Tom on "A good data model produces good opportunities for 
all kinds of data retrieval tools in the later life of the application." 
asI just did a performance review of a Decision Support System and my 
conclusion is that the data model is too normalized for a query intensive 
usage.
It 
depends on what the system will be use for. For OLTP, yes third normal form 
is good. For datawarehousing, a star schemais the way to 
go.


Stephane

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas 
  FSent: Tuesday, March 25, 2003 7:47 AMTo: Multiple 
  recipients of list ORACLE-LSubject: RE: Database Modeling- 
  Normalization - Dinosaurs or What?
  Paula,
  
  Keep fighting for normalization. Something almost all 
  developers fail to recognize is the long-term use of the database - they 
  only think in the "here and now" - they need to develop the application 
  right now. What they fail to recognize are the poor untrained users 
  down the line who will need to develop reports off of the data. 
  Having denormalized data will cause tons data inconsistencies in a few 
  years - exactly what we had back in the "good old Cobol flat file 
  days". A real mess.
  
  One of the most important jobs that a DBA has is producing a good 
  data model keeping all players and users in mind when designing the 
  tables. A good data model produces good opportunities for all kinds 
  of data retrieval tools in the later life of the 
  application.
  
  Hope this helps.
  
  Tom Mercadante Oracle Certified Professional 
  
-Original Message-From: 
[EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]Sent: Monday, March 24, 
2003 7:14 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database Modeling- Normalization - 
Dinosaurs or What?
Guys, 
The emphasis in many places I have worked is developing 
quick and dirty systems as quickly as possible and working with 
developers that don't seem to have very much understanding of Relational 
Database Theory but who prefer to program using flat files in relational 
databases - calling it "object-oriented" when it truly is not. Let 
us just say that it is highly denormalized. As a DBA I care about 
data integrity, extensibility and scalability but the up and coming esp. 
SQL Server developer types seem to operate in a world where this doesn't 
matter - just buy more hardware, denormalize to make the programming 
easier, etc. 
I have been losing this battle. 
So - what is your experience with this? 
What about the idea of having everyone access all 
objects in the views so that if need be the DBA's could in fact still 
make physical changes to the schemas without a large amount of rewriting 
of code? - as a standard
Living without normalization for most things - esp. 
small systems and w/o fk's except as they are maintained in the 
application for the sake of getting the application done quickly, 
cheaply.
It turns my stomach but then I wonder about my own 
sanity - am I making too much out of nothing? What about these 
stovepipe systems? 
Case in-point 100,000 row table for asset management - 
moving different types of addresses to a separate address table and 
moving different types of people to a person table. Developers are 
aghast at the performance implications. I am thinking perf. 
implications not real esp. with small amount but provides extensibility 
and RI with these reference tables instead of denorma. in multiple 
tables. They say mostly batch inserts/updates and batch reads -

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Paula_Stankus

Title: RE: Database Modeling- Normalization - Dinosaurs or What?





I understand what database modeling is for, the different types of normalization and denormalization and the tradeoffs in different types of systems and ultimately to the data access of the system driven (should be ) by the business requirements. The problem is I don't think anyone that does development or provides COTS packages does and that negatively impacts my ability as a DBA to ensure data integrity. I was wondering if I was missing some boat. If anyone else was hitting this brick wall? If there is a way to make this point clear. I was thinking of even doing a prototype - this system versus that system - same app. code, same use of system, normalized and then denormalized so I could show why the heck normalization and RI on the database is the only real way to ensure data integrity. Then show all the ways the database integrity could go wrong. I feel like I have to prove why to use relational database design on a relational database engine built specifically for that purpose - GEEEZ!!! Does anyone have something signed by the Pope to show that relational theory in a RDBMS is necessary? I think that is what it really will take. 

Oracle OCP DBA



-Original Message-
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 25, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database Modeling- Normalization - Dinosaurs or What?



Paula
 I think their use of the term object-oriented maybe be incorrect. That
said, some new converts to object-oriented get carried away. Some even want
to use Oracle in an object-oriented manner. In an effort to please everyone,
Oracle has even added object-oriented features to tables. I don't think they
are used much.
 As Tom points out, the data model will need to support many purposes.
One is reporting. If you don't normalize your data model, then it will be
difficult or impossible to create reports.




Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, March 24, 2003 6:14 PM
To: Multiple recipients of list ORACLE-L




Guys, 


The emphasis in many places I have worked is developing quick and dirty
systems as quickly as possible and working with developers that don't seem
to have very much understanding of Relational Database Theory but who prefer
to program using flat files in relational databases - calling it
object-oriented when it truly is not. Let us just say that it is highly
denormalized. As a DBA I care about data integrity, extensibility and
scalability but the up and coming esp. SQL Server developer types seem to
operate in a world where this doesn't matter - just buy more hardware,
denormalize to make the programming easier, etc. 


I have been losing this battle. 


So - what is your experience with this? 


What about the idea of having everyone access all objects in the views so
that if need be the DBA's could in fact still make physical changes to the
schemas without a large amount of rewriting of code? - as a standard


Living without normalization for most things - esp. small systems and w/o
fk's except as they are maintained in the application for the sake of
getting the application done quickly, cheaply.


It turns my stomach but then I wonder about my own sanity - am I making too
much out of nothing? What about these stovepipe systems? 


Case in-point 100,000 row table for asset management - moving different
types of addresses to a separate address table and moving different types of
people to a person table. Developers are aghast at the performance
implications. I am thinking perf. implications not real esp. with small
amount but provides extensibility and RI with these reference tables instead
of denorma. in multiple tables. They say mostly batch inserts/updates and
batch reads - but then they say some OLTP. This is a SQL Server database.
I think the separate reference tables provides only way for extensibility
and data integrity. I say I will write for them a joined view. They say
perf. implications. - AARRRGGHH!


Oracle OCP DBA 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Nelson, Allan

Title: Message

And as
long as they intend to live forever with unimpaired mental prowess and will not
ever allow any other application develped by any other group to be pointed at
that database then they are perfectly correct in their assertions. Failing
those conditions their arguments are less persuasive.

Allan

-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 12:49 PMTo: Multiple
recipients of list ORACLE-LSubject: RE: Database Modeling-
Normalization - Dinosaurs or What?
I know but seem to have to work with developers these days who
don't understand the basics of normalization and the tradeoffs - lack of data
integrity, lack of extensiblity. Saying to them and even showing them
data integrity issues doesn't seem to convince them. They always say -
we will handle that in the application. To me it isn't handled in the
database it isn't really taken care of and why replicate in application code
what is already part of the underlying RDBMS - but then they start getting
personal.
Oracle OCP DBA
-Original Message- From:
Robson, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 25, 2003 11:39 AM To:
Multiple recipients of list ORACLE-L Subject: RE:
Database Modeling- Normalization - Dinosaurs or What?
Just a couple of comments here, Dennis.
-Original Message-
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: 25 March 2003 14:29
To: Multiple recipients of list ORACLE-L Subject:
RE: Database Modeling- Normalization - Dinosaurs or What?
Paula I think their use
of the term "object-oriented" maybe be incorrect.
That said, some new converts to object-oriented
get carried away. Some even want to use Oracle in an object-oriented manner. In an effort to
please everyone,
Oracle has even added object-oriented features to tables. I don't think they are used
much.
Indeed, some very experienced Oracle users advice against
using these extensions.
As Tom points out, the data model
will need to support many purposes.
One is reporting. If you don't normalize your data
model, then it will be difficult or impossible to create reports.
In fact, if you don't normalise, you will be unable to
guarantee the integrity of your data, and that is far
worse!
peter edinburgh

Dennis Williams DBA,
40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED]
-Original Message- Sent: Monday, March 24, 2003 6:14 PM
To: Multiple recipients of list ORACLE-L
Guys,
The emphasis in many places I have worked is developing quick and dirty systems as quickly as
possible and working with developers that don't
seem to have very much understanding of
Relational Database Theory but who prefer
to program using flat files in relational databases -
calling it "object-oriented" when it truly is
not. Let us just say that it is
highly denormalized. As a DBA I care about
data integrity, extensibility and scalability but
the up and coming esp. SQL Server developer types
seem to operate in a world where this doesn't
matter - just buy more hardware, denormalize to
make the programming easier, etc.
I have been losing this battle.
So - what is your
experience with this? What about the idea of having everyone access all objects in
the views so that if
need be the DBA's could in fact still make physical changes to the schemas without a
large amount of rewriting of code? - as a standard Living without normalization for most
things - esp. small systems and w/o
fk's except as they are maintained in the application
for the sake of getting the application done
quickly, cheaply. It
turns my stomach but then I wonder about my own sanity - am I making too much out of
nothing? What about these stovepipe systems? Case in-point 100,000 row table for
asset management - moving different
types of addresses to a separate address table and
moving different types of people to a person table. Developers are aghast at the
performance implications. I am thinking
perf. implications not real esp. with
small amount but provides extensibility and RI
with these reference tables instead
of denorma. in multiple tables. They say mostly
batch inserts/updates and batch reads - but then they say some OLTP. This is a SQL
Server database. I
think the separate reference tables provides only way for extensibility and data
integrity. I say I will write for them a joined view. They say perf.
implications. - AARRRGGHH! Oracle OCP DBA -- Please see the official ORACLE-L
FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network
Services -- 858-538-5051 http://www.fatcity.com San Diego, California --
Mailing list and

RE: HP-UX 11i/8.1.7.4/login.sql

2003-03-25 Thread Vergara, Michael (TEM)

That I can do...

--
variable sqlpmpt varchar2(30);
set termout off pause off verify off
declare
  username varchar2(30);
  instname varchar2(30);
begin
  begin
select substr(global_name, 1, instr(global_name||'.', '.')-1)
into   instname
from   global_name;
  exception
when OTHERS then
  instname := 'UNK';
  end;
  begin
select user
into   username
from   dual;
  exception
when OTHERS then
  username := 'SQL';
  end;
  :sqlpmpt := instname||'-'||username||' ';
exception
  when OTHERS then
:sqlpmpt := 'SQL ';
end;
/

select :sqlpmpt sqlp from dual;

set sqlprompt 'sqlp_var'
column sqlp clear
undefine sqlp_var
undefine sqlpmpt
define _editor=vi
set trimspoolon
set pagesize 24
set tab  off
set serveroutput on
set termout  on


-Original Message-
Sent: Tuesday, March 25, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L


Perhaps you could send the contents of your LOGIN.SQL 



-Original Message-
Sent: Tuesday, March 25, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


Greetings Everyone!

I have a LOGIN.SQL script that I've customized to fit my 
preferences.  However, when I use SQLPLUS /NOLOG, it fails
miserably since there is no connection to the database.

Is there a way - other than undefining ORACLE_PATH - that the
LOGIN.SQL script can be skipped or ignored when using the
/NOLOG parameter?

Thanks,
Mike

---
===
Michael P. Vergara
Oracle DBA
Guidant Corporation

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  INET: [EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  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: sort ip addresses

2003-03-25 Thread Pardee, Roy E

There's probably a neater solution, but here's a quick and dirty function
that zero-pads each octet  returns a value you should be able to use in an
ORDER BY:


create or replace function OrderIP(p_IP IN VARCHAR2) return VARCHAR2 is
   v_octet number ;
   v_start number ;
   v_end   number ;
   v_currlen number ;
   v_padded varchar2(15) ;
   c_octet_length constant number := 3 ;
begin
   v_octet := 1 ;
   v_start := 1 ;
   v_end := instr(p_IP, '.', v_octet) ;

   while v_end  0 loop
  v_currlen := (v_end - v_start) ;
  v_padded := v_padded || lpad(substr(p_IP, v_start, v_currlen),
c_octet_length, '0') ;
  v_start := v_end + 1 ;
  v_octet := v_octet + 1 ;
  v_end := instr(p_IP, '.', v_octet) ;
   end loop ;

   -- Finally, get the last octet.
   v_padded := v_padded || lpad(substr(p_IP, v_start), c_octet_length, '0')
;
   return v_padded ;
end OrderIP ;


HTH,

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-Original Message-
Sent: Tuesday, March 25, 2003 9:54 AM
To: Multiple recipients of list ORACLE-L


Need a trick to sort ip addresses into numerical order.  I have them
stored as varchar2.  If I select without a sort I get rows
in order of their character value:

10.0.112.1
10.0.113.1
10.0.113.2
10.0.12.1
10.0.78.1

I'd like to order them numerically within the octets:

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  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: Reorganizing tables

2003-03-25 Thread Wolfgang Breitling

Title: RE: Reorganizing tables





selecting initial_extent and next_extent from dba_tables is incorrect. It 
tells you what you asked for, NOT what Oracle allocated. You need to look 
at dba_extents:


SQL select tablespace_name, initial_extent, next_extent, 
extent_management, allocation_type, MIN_EXTLEN
from dba_tablespaces where tablespace_name in ('SMALLTBL', 'LARGETBL');



TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN 
ALLOCATIO MIN_EXTLEN
-- -- --- -- 
- --
LARGETBL 5242880 5242880 
LOCAL UNIFORM 5242880
SMALLTBL 65536 65536 
LOCAL UNIFORM 65536


SQL create table test tablespace smalltbl storage(initial 2K next 2K) as 
select * from dba_objects;


Table created.


SQL select tablespace_name, initial_extent, next_extent from dba_tables 
where table_name='TEST';


TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
-- -- ---
SMALLTBL 16384 65536


1 row selected.


SQL select tablespace_name, bytes initial_extent from dba_extents where 
segment_name='TEST' and extent_id = 2;


TABLESPACE_NAME INITIAL_EXTENT
-- --
SMALLTBL 65536
SMALLTBL 65536
SMALLTBL 65536


SQL alter table test move tablespace largetbl;


Table altered.


SQL select tablespace_name, initial_extent, next_extent from dba_tables 
where table_name='TEST';


TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
-- -- ---
LARGETBL 16384 5242880


1 row selected.


SQL select tablespace_name, bytes initial_extent from dba_extents where 
segment_name='TEST' and extent_id = 2;


TABLESPACE_NAME INITIAL_EXTENT
-- --
LARGETBL 5242880
LARGETBL 5242880



At 07:34 AM 3/25/2003 -0800, you wrote:
mailto:[EMAIL PROTECTED][EMAIL PROTECTED] select tablespace_name, initial_extent, 
next_extent, extent_management from dba_tablespaces
 2 where tablespace_name in ('TAB128K02', 'TAB4M02');

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
-- -- --- --
TAB4M02 4194304 4194304 LOCAL
TAB128K02 131072 131072 LOCAL

mailto:[EMAIL PROTECTED][EMAIL PROTECTED] create table test tablespace tab128k02 as 
select * from dba_objects;

Table created.

mailto:[EMAIL PROTECTED][EMAIL PROTECTED] select tablespace_name, initial_extent, 
next_extent from dba_tables where table_name='TEST';

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
-- -- ---
TAB128K02 131072 131072

mailto:[EMAIL PROTECTED][EMAIL PROTECTED] alter table test move tablespace TAB4M02;

Table altered.

mailto:[EMAIL PROTECTED][EMAIL PROTECTED] select tablespace_name, initial_extent, 
next_extent from dba_tables where table_name='TEST';

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
-- -- ---
TAB4M02 131072 4194304
We are on 8.1.7.4 on HP-UX 11.0



Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com



 


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier lectronique est une communication prive  l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'tes pas le destinataire prvu, vous tes avis, par les prsentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'tes pas spcifiquement autoris  recevoir ce courriel ou si vous croyez l'avoir reu par erreur, veuillez en aviser l'expditeur original immdiatement. Nous respectons les demandes similaires qui touchent la confidentialit des communications par courrier lectronique.

RE: find on unix

2003-03-25 Thread Deshpande, Kirti




Ignore 
that.. 
It 
will find newer files, not older files..
I 
misread the question. Sorry..

- 
Kirti 

  -Original Message-From: Deshpande, Kirti 
  Sent: Tuesday, March 25, 2003 1:39 PMTo: 
  '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: 
  RE: find on unix
  Create a 'flag' filewiththe 'particular'date as its 
  last modified date:
  
   touch -t 200303201330.40 oldfile 
  
  
  The 
  timestamp is of the format:MMDDHHMM.SS 
  
  Then 
  use find command to find files newer (later) than oldfile. 

  
   find . -newer oldfile -print 
  
  HTH,
  
  - 
  Kirti
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 10:39 
AMTo: Multiple recipients of list ORACLE-LSubject: 
find on unix
How to use "find" command on unix to find files 
older then (created ) a particular date .
Any idea

-ak

Re: sort ip addresses

2003-03-25 Thread Rodd Holman

This could be ugly, but you could use combinations of ltrim, rtrim,
substr, and to_number to select and sort.

Another idea would be to store your IP's in 4 number fields ip_a, ip_b,
ip_c, ip_d and then do your select using: 
  select ip_a||'.'||ip_b||'.'||ip_c||'.'||ip_d
  from   ip_table
  order  by ip_a, ip_b, ip_c, ip_d

Again, this isn't pretty, but is easier than the first idea.  The
problem with storing them as text is that 70 comes after 100 because
text sorts by a character as it comes to it as if it were a letter.  The
second one will output IP's in a workable format if you are spooling
output for pinging, ftp, or some other access.

Rodd

On Tue, 2003-03-25 at 11:54, Ray Stell wrote:
 Need a trick to sort ip addresses into numerical order.  I have them
 stored as varchar2.  If I select without a sort I get rows
 in order of their character value:
 
 10.0.112.1
 10.0.113.1
 10.0.113.2
 10.0.12.1
 10.0.78.1
 
 I'd like to order them numerically within the octets:
 
 10.0.12.1
 10.0.78.1
 10.0.112.1
 10.0.113.1
 10.0.113.2
 ===
 Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Rodd Holman [EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rodd Holman
  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: Database Modeling- Normalization - Dinosaurs or What?

Paula - In reflecting on the different developers I deal with, by any chance
do these developers you are dealing with have a strong Microsoft background?



Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, March 25, 2003 1:00 PM
To: Multiple recipients of list ORACLE-L



I understand what database modeling is for, the different types of
normalization and denormalization and the tradeoffs in different types of
systems and ultimately to the data access of the system driven (should be )
by the business requirements.  The problem is I don't think anyone that does
development or provides COTS packages does and that negatively impacts my
ability as a DBA to ensure data integrity.  I was wondering if I was missing
some boat.  If anyone else was hitting this brick wall?  If there is a way
to make this point clear.  I was thinking of even doing a prototype - this
system versus that system - same app. code, same use of system, normalized
and then denormalized so I could show why the heck normalization and RI on
the database is the only real way to ensure data integrity.  Then show all
the ways the database integrity could go wrong.  I feel like I have to prove
why to use relational database design on a relational database engine built
specifically for that purpose -
GEEEZ!!!  Does anyone have something
signed by the Pope to show that relational theory in a RDBMS is necessary?
I think that is what it really will take.  

Oracle OCP DBA 


-Original Message- 
mailto:[EMAIL PROTECTED] ] 
Sent: Tuesday, March 25, 2003 9:29 AM 
To: Multiple recipients of list ORACLE-L 


Paula 
I think their use of the term object-oriented maybe be incorrect. That

said, some new converts to object-oriented get carried away. Some even want 
to use Oracle in an object-oriented manner. In an effort to please everyone,

Oracle has even added object-oriented features to tables. I don't think they

are used much. 
As Tom points out, the data model will need to support many purposes. 
One is reporting. If you don't normalize your data model, then it will be 
difficult or impossible to create reports. 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message- 
Sent: Monday, March 24, 2003 6:14 PM 
To: Multiple recipients of list ORACLE-L 



Guys, 

The emphasis in many places I have worked is developing quick and dirty 
systems as quickly as possible and working with developers that don't seem 
to have very much understanding of Relational Database Theory but who prefer

to program using flat files in relational databases - calling it 
object-oriented when it truly is not.  Let us just say that it is highly 
denormalized.  As a DBA I care about data integrity, extensibility and 
scalability but the up and coming esp. SQL Server developer types seem to 
operate in a world where this doesn't matter - just buy more hardware, 
denormalize to make the programming easier, etc.  

I have been losing this battle.  

So - what is your experience with this? 

What about the idea of having everyone access all objects in the views so 
that if need be the DBA's could in fact still make physical changes to the 
schemas without a large amount of rewriting of code? - as a standard 

Living without normalization for most things - esp. small systems and w/o 
fk's except as they are maintained in the application for the sake of 
getting the application done quickly, cheaply. 

It turns my stomach but then I wonder about my own sanity - am I making too 
much out of nothing?  What about these stovepipe systems?  

Case in-point 100,000 row table for asset management - moving different 
types of addresses to a separate address table and moving different types of

people to a person table.  Developers are aghast at the performance 
implications.  I am thinking perf. implications not real esp. with small 
amount but provides extensibility and RI with these reference tables instead

of denorma. in multiple tables.  They say mostly batch inserts/updates and 
batch reads - but then they say some OLTP.  This is a SQL Server database. 
I think the separate reference tables provides only way for extensibility 
and data integrity.  I say I will write for them a joined view.  They say 
perf. implications.  - AARRRGGHH! 

Oracle OCP DBA 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
http://www.orafaq.net  
-- 
Author: DENNIS WILLIAMS 
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
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

RE: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread STEVE OLLIG

the Pope - eh?  all these duhvelopers must be Catholic ;)

i feel for you Paula.  this is a battle many shops fight (including this
one) and often it isn't easy to win.  one suggestion - have your management
play the consultant game with them.  hire an outside consultant to come in
and tell them the same things you've been saying.  they'll believe the
consultant.  everyone knows that consultants know way more than in-house
staff ;)


-Original Message-
Sent: Tuesday, March 25, 2003 1:00 PM
To: Multiple recipients of list ORACLE-L


I understand what database modeling is for, the different types of
normalization and denormalization and the tradeoffs in different types of
systems and ultimately to the data access of the system driven (should be )
by the business requirements.  The problem is I don't think anyone that does
development or provides COTS packages does and that negatively impacts my
ability as a DBA to ensure data integrity.  I was wondering if I was missing
some boat.  If anyone else was hitting this brick wall?  If there is a way
to make this point clear.  I was thinking of even doing a prototype - this
system versus that system - same app. code, same use of system, normalized
and then denormalized so I could show why the heck normalization and RI on
the database is the only real way to ensure data integrity.  Then show all
the ways the database integrity could go wrong.  I feel like I have to prove
why to use relational database design on a relational database engine built
specifically for that purpose -
GEEEZ!!!  Does anyone have something
signed by the Pope to show that relational theory in a RDBMS is necessary?
I think that is what it really will take.  
Oracle OCP DBA 


-Original Message- 
Sent: Tuesday, March 25, 2003 9:29 AM 
To: Multiple recipients of list ORACLE-L 


Paula 
I think their use of the term object-oriented maybe be incorrect. That

said, some new converts to object-oriented get carried away. Some even want 
to use Oracle in an object-oriented manner. In an effort to please everyone,

Oracle has even added object-oriented features to tables. I don't think they

are used much. 
As Tom points out, the data model will need to support many purposes. 
One is reporting. If you don't normalize your data model, then it will be 
difficult or impossible to create reports. 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 
-Original Message- 
Sent: Monday, March 24, 2003 6:14 PM 
To: Multiple recipients of list ORACLE-L 



Guys, 
The emphasis in many places I have worked is developing quick and dirty 
systems as quickly as possible and working with developers that don't seem 
to have very much understanding of Relational Database Theory but who prefer

to program using flat files in relational databases - calling it 
object-oriented when it truly is not.  Let us just say that it is highly 
denormalized.  As a DBA I care about data integrity, extensibility and 
scalability but the up and coming esp. SQL Server developer types seem to 
operate in a world where this doesn't matter - just buy more hardware, 
denormalize to make the programming easier, etc.  
I have been losing this battle.  
So - what is your experience with this? 
What about the idea of having everyone access all objects in the views so 
that if need be the DBA's could in fact still make physical changes to the 
schemas without a large amount of rewriting of code? - as a standard 
Living without normalization for most things - esp. small systems and w/o 
fk's except as they are maintained in the application for the sake of 
getting the application done quickly, cheaply. 
It turns my stomach but then I wonder about my own sanity - am I making too 
much out of nothing?  What about these stovepipe systems?  
Case in-point 100,000 row table for asset management - moving different 
types of addresses to a separate address table and moving different types of

people to a person table.  Developers are aghast at the performance 
implications.  I am thinking perf. implications not real esp. with small 
amount but provides extensibility and RI with these reference tables instead

of denorma. in multiple tables.  They say mostly batch inserts/updates and 
batch reads - but then they say some OLTP.  This is a SQL Server database. 
I think the separate reference tables provides only way for extensibility 
and data integrity.  I say I will write for them a joined view.  They say 
perf. implications.  - AARRRGGHH! 
Oracle OCP DBA 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services 
- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL

Re: sort ip addresses

Ray Stell wrote:
 
 Need a trick to sort ip addresses into numerical order.  I have them
 stored as varchar2.  If I select without a sort I get rows
 in order of their character value:
 
 10.0.112.1
 10.0.113.1
 10.0.113.2
 10.0.12.1
 10.0.78.1
 
 I'd like to order them numerically within the octets:
 
 10.0.12.1
 10.0.78.1
 10.0.112.1
 10.0.113.1
 10.0.113.2
 ===
 Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
 --

Ray,

   This works if you do not have weird NLS settings :

  1  select address from ip
  2  order by to_number(substr(address, 1, instr(address, '.', 1, 2) -
1)),
  3*  to_number(substr(address, instr(address, '.', 1, 2) + 1))
SQL /

ADDRESS

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2


-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: sort ip addresses

2003-03-25 Thread Kevin Lange

I've had this around for a while ... and just dug it out of my scripts and
tried it again .   Works for sorting 4 octet ips.  You can always add
sections on if they are ipv6.

Ugly looking ... but it works.  Note, you can probably clean up the math a
bit ... i.e. combine plusses into +2, +3 etc.   I just never did because it
worked.

select
  ip
from iptable
orderby
  lpad(substr(ip,1,instr(ip,'.')-1),3,'0')||
  lpad(substr(ip,
instr(ip,'.')+1,(instr(ip,'.',instr(ip,'.')+1)-1)-instr(ip,'.')),3,'0')||
  lpad(substr(ip,
instr(ip,'.',instr(ip,'.')+1)+1,(instr(ip,'.',instr(ip,'.',instr(ip,'.')+1)+
1))-instr(ip,'.',instr(ip,'.')+1)-1),3,'0')||
  lpad(substr(ip, instr(ip,'.',instr(ip,'.',instr(ip,'.')+1)+1)+1),3,'0')

-Original Message-
Sent: Tuesday, March 25, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L


Need a trick to sort ip addresses into numerical order.  I have them
stored as varchar2.  If I select without a sort I get rows
in order of their character value:

10.0.112.1
10.0.113.1
10.0.113.2
10.0.12.1
10.0.78.1

I'd like to order them numerically within the octets:

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  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: Kevin Lange
  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: Database Modeling- Normalization - Dinosaurs or What?

2003-03-25 Thread Jeremy Pulcifer

Title: Message



I 
disagree. A hybrid database is usually a combination of OLTP and OLAP: then you 
normalize the transactional, de-normalize the DSS.

While 
I understand that sometimes we have to live with a less-normalized database than 
we'd like, if I'm building the database, it is normalized if the application is 
transactional, and de-normalize from that to support whatever DSS operations are 
necessary. I'm a duhveloper and have rarely (never?) seen a situation where a 
transactional database was "too" normalized (stopping at 3rd normal). That's 
been blamed for a lot, but usually the end result is either the user/duhveloper 
is trying to either provide DSS functionality or is writing poor code. Of 
course, designing a flexible de-normalized schema is/seems less clear-cut, to be 
sure.

That 
being said, you can only enforce this concept where you have complete authority 
over the data model. If this isn't the case, then all bets are 
off.

  
  -Original Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
  Sent: Tuesday, March 25, 2003 10:54 AMTo: Multiple 
  recipients of list ORACLE-LSubject: RE: Database Modeling- 
  Normalization - Dinosaurs or What?
  It 
  is often not so cut and dried. There are degrees of normalization and 
  hybrid databases. Wish it was that easy. I think you 
  oversimplified the matter and honestly that doesn't help the data modeling 
  efforts. 
  
  Oracle OCP DBA 
  
-Original Message-From: Jeremy Pulcifer 
[mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 
11:59 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Database Modeling- Normalization - Dinosaurs 
or What?
OLTP = Normalize
OLAP/DSS = DeNormalize

End of discussion. We figured this out what, 10 years 
ago?

  
  -Original Message-From: Stephane 
  Paquette [mailto:[EMAIL PROTECTED] Sent: 
  Tuesday, March 25, 2003 7:24 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database Modeling- Normalization - 
  Dinosaurs or What?
  DBA are responsible for the data model. 
  I spend time toshow the developpers the benefits of data 
  normalization.
  
  I do not agree with Tom on "A good data model produces good 
  opportunities for all kinds of data retrieval tools in the later life of 
  the application." asI just did a performance review of a Decision 
  Support System and my conclusion is that the data model is too normalized 
  for a query intensive usage.
  It depends on what the system will be use for. For OLTP, yes third 
  normal form is good. For datawarehousing, a star schemais the way to 
  go.
  
  
  Stephane
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas 
FSent: Tuesday, March 25, 2003 7:47 AMTo: Multiple 
recipients of list ORACLE-LSubject: RE: Database Modeling- 
Normalization - Dinosaurs or What?
Paula,

Keep fighting for normalization. Something almost all 
developers fail to recognize is the long-term use of the database - they 
only think in the "here and now" - they need to develop the application 
right now. What they fail to recognize are the poor untrained 
users down the line who will need to develop reports off of the 
data. Having denormalized data will cause tons data 
inconsistencies in a few years - exactly what we had back in the "good 
old Cobol flat file days". A real mess.

One of the most important jobs that a DBA has is producing a good 
data model keeping all players and users in mind when designing the 
tables. A good data model produces good opportunities for all 
kinds of data retrieval tools in the later life of the 
application.

Hope this helps.

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Monday, March 
  24, 2003 7:14 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Database Modeling- Normalization - 
  Dinosaurs or What?
  Guys, 
  The emphasis in many places I have worked is 
  developing quick and dirty systems as quickly as possible and working 
  with developers that don't seem to have very much understanding of 
  Relational Database Theory but who prefer to program using flat files 
  in relational databases - calling it "object-oriented" when it truly 
  is not. Let us just say that it is highly denormalized. As 
  a DBA I care about data integrity, extensibility and scalability but 
  the up and coming esp. SQL Server developer types seem to operate in a 
  world where this doesn't

RE: sort ip addresses

Title: RE: sort ip addresses



Jacques,

you 
are correct - and go ahead and be picky. my first solution was wrong as 
Ray pointed out to me. looks like the pattern matching example you gave 
works. this also works:


SELECT col1 FROM
(SELECT '10.0.113.1' col1
FROM dual
UNION
SELECT '10.0.112.1'
FROM 
dual
UNION
SELECT '10.99.114.1'
FROM dual
UNION
SELECT '10.1.90.1'
FROM dual
UNION
SELECT '128.173.10.300'
FROM dual
UNION
SELECT '128.173.10.1'
FROM dual
UNION
SELECT '99.200.90.255'
FROM dual
UNION
SELECT '10.0.80.1'
FROM dual
UNION
SELECT '188.82.80.1'
FROM dual
UNION
SELECT '128.173.10.255'
FROM dual
)
ORDER BY TO_NUMBER(SUBSTR(col1,1,INSTR(col1,'.',1))),
 
TO_NUMBER(SUBSTR(col1,INSTR(col1,'.',1)+1,INSTR(col1,'.',2))),
 
TO_NUMBER(SUBSTR(col1,INSTR(col1,'.',2)+1,INSTR(col1,'.',3)))


Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Jacques Kilchoer 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 
  2:44 PMTo: '[EMAIL PROTECTED]'Cc: 
  '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'Subject: RE: sort ip 
  addresses
  (see comments below) 
   -Original Message-  
  From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] 
SELECT col1 FROM 
   (SELECT '10.0.112.1' col1  FROM dual  UNION 
   SELECT '10.0.113.1'  FROM dual  UNION 
   SELECT '10.0.114.1'  FROM 
  dual  UNION  SELECT 
  '10.0.90.1'  FROM dual  UNION  SELECT '10.0.80.1' 
   FROM dual)  ORDER BY 
  TO_NUMBER(REPLACE (col1,'.','0'))  
-Original 
  Message-  From: Ray Stell [mailto:[EMAIL PROTECTED]]   Need a trick to sort ip addresses 
  into numerical order. I have them  stored as 
  varchar2. If I select without a sort I get rows  in order of their character value:  
   10.0.112.1  
  10.0.113.1  10.0.113.2  10.0.12.1  10.0.78.1   I'd like to order them numerically 
  within the octets:   
  10.0.12.1  10.0.78.1  
  10.0.112.1  10.0.113.1  10.0.113.2 
  I hate to be picky, Thomas, but your solution assumes (for 
  example) that all the first quads have the same number of digits. Included 
  below is an example where your proposal would return incorrect results. Below 
  that I have another proposed solution.
  SQL SELECT col1 FROM  
  2 (SELECT '10.0.112.1' col1  3 
  FROM dual  4 UNION  5 SELECT '10.0.113.1'  
  6 FROM dual  7 UNION 
   8 SELECT '10.0.114.1'  9 FROM dual 10 
  UNION 11 SELECT '10.0.90.1' 
  12 FROM dual 13 UNION 14 SELECT 
  '10.0.80.1' 15 FROM dual 16 union select '192.2.2.1' from dual) 17 ORDER BY TO_NUMBER(REPLACE (col1,'.','0')) ; 
  COL1 -- 10.0.80.1 10.0.90.1 192.2.2.1 10.0.112.1 10.0.113.1 10.0.114.1 
  SQL select  
  2 to_number (substr (a.col1, 1, owa_pattern.amatch 
  (a.col1, 1, '\d+') - 1))  
  3 as quad1,  4 to_number (substr (a.col1, 
   
  5 
  owa_pattern.amatch (a.col1, 1, '\d+') + 1,  
  6 
  owa_pattern.amatch (a.col1, 1, '\d+\.\d+')  
  7 
  - owa_pattern.amatch (a.col1, 1, '\d+') - 1  
  8 
  )  
  9 
  ) as quad2, 10 to_number 
  (substr (a.col1, 11 
  owa_pattern.amatch (a.col1, 1, '\d+\.\d+') + 1, 12 
  owa_pattern.amatch (a.col1, 1, '\d+\.\d+\.\d+') 13 
  - owa_pattern.amatch (a.col1, 1, '\d+\.\d+') - 1 14 
  ) 15 
  ) as quad3, 16 to_number 
  (substr (a.col1, 17 
  owa_pattern.amatch (a.col1, 1, '\d+\.\d+\.\d+') + 1 18 
  ) 19 
  ) as quad4 20 from 21 ( select '10.0.112.1' as col1 from 
  dual 22 union 
  all 23 
  select '10.0.113.1' as col1 from dual 24 union all 25 select '10.0.114.1' as 
  col1 from dual 26 
  union all 27 
  select '10.0.90.1' as col1 from dual 28 union all 29 select '10.0.80.1' as col1 
  from dual 30 union 
  all 31 
  select '192.2.2.1' as col1 from dual 32 ) a 33 order by quad1, quad2, quad3, quad4 ; 
   QUAD1 
  QUAD2 QUAD3 QUAD4 
  - - - -  
  10 
  0 
  80 1  
  10 
  0 
  90 1  
  10 
  0 
  112 1  
  10 
  0 
  113 1  
  10 
  0 
  114 1  
  192 
  2 
  2 1 6 
  ligne(s) selectionnee(s).

why FTS is chosen here?

2003-03-25 Thread gmei

Hi:

We have an oracle 8173 running on Solaris 2.8. I am trying to understand why
oracle chooses the Full Table scan in it's explain plan in the floowing
query:

SQL SET AUTOTRACE TRACEONLY EXPLAIN;
SQL select  TermID from  Observationlist, TermObs  where ID = ObsID;

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=9477 Card=267 By
  tes=44766729)

   10   HASH JOIN (Cost=9477 Card=267 Bytes=44766729)
   21 INDEX (FAST FULL SCAN) OF 'PK_OBSERVATION' (UNIQUE) (Cos
  t=834 Card=2513357 Bytes=15080142)

   31 TABLE ACCESS (FULL) OF 'TERMOBS' (Cost=1126 Card=267
   Bytes=28966707)


SQL SET AUTOTRACE off;
SQL desc Observationlist;
 Name  Null?Type
 -  

 IDNOT NULL NUMBER
 GENEID NUMBER
 CURATIONTYPE   NUMBER
 PROTEOMEREFID  NUMBER
 SOURCEID   NUMBER
 SOURCETABLEVARCHAR2(25)
 DESTID NUMBER
 DESTTABLE  VARCHAR2(25)
 DESTDATE   DATE
 REFERENCETYPE  VARCHAR2(1)
 EVIDENCECODE   NUMBER
 CURATORID  NUMBER
 EDITORID   NUMBER
 UPDATESTAMPDATE
 CURATIONSTATUS VARCHAR2(1)
 ORIGINALSTAMP  DATE
 NEXTOBSNUMBER
 TARGET VARCHAR2(15)
 REFTARGET  VARCHAR2(15)
 TOOL   VARCHAR2(25)
 OLDGENEID  NUMBER

SQL desc TermObs;
 Name  Null?Type
 -  

 OBSID  NUMBER
 TERMID NUMBER
 CONTEXTNUMBER

SQL select count(*) from Observationlist;

  COUNT(*)
--
   2513357

SQL select count(*) from TermObs;

  COUNT(*)
--
   267

TermObs.OBSID  has FK constaint pointing to Observationlist.ID.
TermObs.OBSID is also indexed. Observationlist.ID is PK in the table. Both
tables have been analyzed (using compute statistics). I would expect the
query to use index on TermObs.OBSID. I tried to put hints in but they
resulted in much higher cost than the one without.

Any explaination why TermObs.OBSID's index is not used?

TIA.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: gmei
  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: sort ip addresses

2003-03-25 Thread Jared . Still

Here's one way to do it:

create table ip ( ip_address varchar2(15) );


insert into ip values('10.0.112.1');
insert into ip values('10.0.113.1');
insert into ip values('10.0.113.2');
insert into ip values('10.0.12.1');
insert into ip values('10.0.78.1');

commit;

create or replace function ipsort ( ip_address_in varchar2 )
return number
is
nip number;
o1 varchar2(3);
o2 varchar2(3);
o3 varchar2(3);
o4 varchar2(3);

begin
/* this function assumes a complete ip address of nnn.nnn.nnn.nnn
   though the number of digits in each octet does not need to be
three, just = 1
*/

o1 := substr(ip_address_in,1,instr(ip_address_in,'.',1)-1);
o2 := substr(ip_address_in,instr(ip_address_in,'.',1,1)+1, 
(instr(ip_address_in,'.',1,2)-1) - (instr(ip_address_in,'.',1,1)));
o3 := substr(ip_address_in,instr(ip_address_in,'.',1,2)+1, 
(instr(ip_address_in,'.',1,3)-1) - (instr(ip_address_in,'.',1,2)));
o4 := substr(ip_address_in,instr(ip_address_in,'.',1,3)+1);

nip := (to_number(o1) * 16 * power(2,20)) + ( to_number(o2) * 64 * 
power(2,10) ) + ( to_number(o3) * 256 ) + to_number(o4);

return nip;

end;
/


select ip_address
from ip
order by ipsort(ip_address)
/


IP_ADDRESS
---
10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2

5 rows selected.

Jared








Ray Stell [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 03/25/2003 09:54 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:sort ip addresses


Need a trick to sort ip addresses into numerical order.  I have them
stored as varchar2.  If I select without a sort I get rows
in order of their character value:

10.0.112.1
10.0.113.1
10.0.113.2
10.0.12.1
10.0.78.1

I'd like to order them numerically within the octets:

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  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: sort ip addresses

2003-03-25 Thread Jacques Kilchoer

Title: RE: sort ip addresses





(see comments below)


 -Original Message-
 From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
 
 SELECT col1 FROM
 (SELECT '10.0.112.1' col1
 FROM dual
 UNION
 SELECT '10.0.113.1'
 FROM dual
 UNION
 SELECT '10.0.114.1'
 FROM dual
 UNION
 SELECT '10.0.90.1'
 FROM dual
 UNION
 SELECT '10.0.80.1'
 FROM dual)
 ORDER BY TO_NUMBER(REPLACE (col1,'.','0'))
 
 
 -Original Message-
 From: Ray Stell [mailto:[EMAIL PROTECTED]] 
 
 Need a trick to sort ip addresses into numerical order. I have them
 stored as varchar2. If I select without a sort I get rows
 in order of their character value:
 
 10.0.112.1
 10.0.113.1
 10.0.113.2
 10.0.12.1
 10.0.78.1
 
 I'd like to order them numerically within the octets:
 
 10.0.12.1
 10.0.78.1
 10.0.112.1
 10.0.113.1
 10.0.113.2



I hate to be picky, Thomas, but your solution assumes (for example) that all the first quads have the same number of digits. Included below is an example where your proposal would return incorrect results. Below that I have another proposed solution.

SQL SELECT col1 FROM
 2 (SELECT '10.0.112.1' col1
 3 FROM dual
 4 UNION
 5 SELECT '10.0.113.1'
 6 FROM dual
 7 UNION
 8 SELECT '10.0.114.1'
 9 FROM dual
10 UNION
11 SELECT '10.0.90.1'
12 FROM dual
13 UNION
14 SELECT '10.0.80.1'
15 FROM dual
16 union select '192.2.2.1' from dual)
17 ORDER BY TO_NUMBER(REPLACE (col1,'.','0')) ;


COL1
--
10.0.80.1
10.0.90.1
192.2.2.1
10.0.112.1
10.0.113.1
10.0.114.1


SQL select
 2 to_number (substr (a.col1, 1, owa_pattern.amatch (a.col1, 1, '\d+') - 1))
 3 as quad1,
 4 to_number (substr (a.col1,
 5 owa_pattern.amatch (a.col1, 1, '\d+') + 1,
 6 owa_pattern.amatch (a.col1, 1, '\d+\.\d+')
 7 - owa_pattern.amatch (a.col1, 1, '\d+') - 1
 8 )
 9 ) as quad2,
10 to_number (substr (a.col1,
11 owa_pattern.amatch (a.col1, 1, '\d+\.\d+') + 1,
12 owa_pattern.amatch (a.col1, 1, '\d+\.\d+\.\d+')
13 - owa_pattern.amatch (a.col1, 1, '\d+\.\d+') - 1
14 )
15 ) as quad3,
16 to_number (substr (a.col1,
17 owa_pattern.amatch (a.col1, 1, '\d+\.\d+\.\d+') + 1
18 )
19 ) as quad4
20 from
21 ( select '10.0.112.1' as col1 from dual
22 union all
23 select '10.0.113.1' as col1 from dual
24 union all
25 select '10.0.114.1' as col1 from dual
26 union all
27 select '10.0.90.1' as col1 from dual
28 union all
29 select '10.0.80.1' as col1 from dual
30 union all
31 select '192.2.2.1' as col1 from dual
32 ) a
33 order by quad1, quad2, quad3, quad4 ;


 QUAD1 QUAD2 QUAD3 QUAD4
- - - -
 10 0 80 1
 10 0 90 1
 10 0 112 1
 10 0 113 1
 10 0 114 1
 192 2 2 1
6 ligne(s) selectionnee(s).

RE: find on unix

2003-03-25 Thread Deshpande, Kirti




Changing 'find' to following would work:

find . ! -newer oldfile -print 

- 
Kirti 


  -Original Message-From: Deshpande, Kirti 
  Sent: Tuesday, March 25, 2003 1:50 PMTo: 
  '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: 
  RE: find on unix
  Ignore that.. 
  It 
  will find newer files, not older files..
  I 
  misread the question. Sorry..
  
  - 
  Kirti 
  
-Original Message-From: Deshpande, Kirti 
Sent: Tuesday, March 25, 2003 1:39 PMTo: 
'[EMAIL PROTECTED]'Cc: 
'[EMAIL PROTECTED]'Subject: RE: find on 
unix
Create a 'flag' filewiththe 'particular'date as its 
last modified date:

 touch -t 200303201330.40 oldfile 


The timestamp is of the format:MMDDHHMM.SS 


Then use find command to find files newer (later) than oldfile. 


 find . -newer oldfile -print 

HTH,

- 
Kirti

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 
  10:39 AMTo: Multiple recipients of list 
  ORACLE-LSubject: find on unix
  How to use "find" command on unix to find 
  files older then (created ) a particular date .
  Any idea
  
  -ak

RE: sort ip addresses

2003-03-25 Thread Sarnowski, Chris



 -Original Message-
 From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]
 
 SELECT col1 FROM
data snipped
 ORDER BY TO_NUMBER(REPLACE (col1,'.','0'))
 

The above works for the given test data but doesn't work in the general case,
for example if '192.168.10.10' and '192.168.1.100' are in the data set.

There may be a better way to do it but this seems to work (if a user-defined
function is an acceptable solution). In a production environment, as opposed
to a mailing list hack, I'd add error checking and data checks.

create or replace function iptonumber(ip_in varchar2)
return number
deterministic

is
  ip_local varchar2(15) := ip_in;
  ip_out number := 0;
  octet number := 0;
  multiplier number := 1000;
  
begin
  while (length(ip_local)  0) loop
while substr(ip_local, 1, 1) != '.' loop
  octet := octet * 10 + to_number(substr(ip_local, 1, 1));
  ip_local := substr(ip_local, 2);
end loop;
ip_local := substr(ip_local, 2);
ip_out := ip_out * 1000 + octet;
octet := 0;
  end loop;
  return ip_out;
end;

/

SQLselect ip from ip_test order by iptonumber(ip);

IP

10.0.112.1
10.0.113.1
192.168.0.10
192.168.0.101
192.168.1.100
192.168.10.10

6 rows selected.

SQLselect ip from ip_test order by to_number(replace(ip, '.', '0'));

IP

10.0.112.1
10.0.113.1
192.168.0.10
192.168.0.101
192.168.10.10
192.168.1.100

6 rows selected.


LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. 
It is intended for the addressee(s) only. Access to this e-mail by anyone else is 
unauthorized. If you are not an addressee, any disclosure or copying of the contents 
or any action taken (or not taken) in reliance on it is unauthorized and may be 
unlawful. If you are not an addressee, please inform the sender immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: find on unix

2003-03-25 Thread Deshpande, Kirti




Create 
a 'flag' filewiththe 'particular'date as its last modified 
date:

 touch -t 200303201330.40 oldfile 


The 
timestamp is of the format:MMDDHHMM.SS 

Then 
use find command to find files newer (later) than oldfile. 

 find . -newer oldfile -print 

HTH,

- 
Kirti

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 10:39 
  AMTo: Multiple recipients of list ORACLE-LSubject: find 
  on unix
  How to use "find" command on unix to find files 
  older then (created ) a particular date .
  Any idea
  
  -ak

Re: sort ip addresses

Ooops. Disregard previous post, had a tiresome day.


Ray Stell wrote:
 
 Need a trick to sort ip addresses into numerical order.  I have them
 stored as varchar2.  If I select without a sort I get rows
 in order of their character value:
 
 10.0.112.1
 10.0.113.1
 10.0.113.2
 10.0.12.1
 10.0.78.1
 
 I'd like to order them numerically within the octets:
 
 10.0.12.1
 10.0.78.1
 10.0.112.1
 10.0.113.1
 10.0.113.2
 ===
 Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
 --

My previous post, like some other suggestions (translate, etc.) was too
clever by half.
This is uglier but works better.

SQL get iptest
  1  select address
  2  from ip
  3  order by to_number(substr(address, 1, instr(address, '.', 1, 1) -
1)),
  4   to_number(substr(address, instr(address, '.', 1, 1) + 1,
  5 instr(address, '.', 1, 2)
  6   - instr(address, '.', 1, 1))),
  7   to_number(substr(address, instr(address, '.', 1, 2) + 1,
  8 instr(address, '.', 1, 3)
  9   - instr(address, '.', 1, 2))),
 10*  to_number(substr(address, instr(address, '.', 1, 3) + 1))
SQL /

ADDRESS

10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: HP-UX 11i/8.1.7.4/login.sql

2003-03-25 Thread John Carlson

The way I do it is to create another login.sql script for batch jobs only and place 
that in another directory.  I have also created a file I call .cronenv under $HOME.  
This sets environment variables needed for cron jobs.  In it, I set SQLPATH so the 
first place it looks is the directory I placed my new login.sql file.

SQLPATH=$HOME/cron_out:.:$HOME/dbacommon/tools/sqlscripts:$SQLPATH

Then I put:
. $HOME/.cronenv  /dev/null 21
into all my batch jobs.

This works for me.
HTH,
John


-Original Message-
Sent: Tuesday, March 25, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L


Greetings Everyone!

I have a LOGIN.SQL script that I've customized to fit my 
preferences.  However, when I use SQLPLUS /NOLOG, it fails
miserably since there is no connection to the database.

Is there a way - other than undefining ORACLE_PATH - that the
LOGIN.SQL script can be skipped or ignored when using the
/NOLOG parameter?

Thanks,
Mike

---
===
Michael P. Vergara
Oracle DBA
Guidant Corporation

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  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: John Carlson
  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).

Import to Oracle9i extremely slow

2003-03-25 Thread Michael Wu

Hello List,

When I tried to import the same export file (made by Oracle 8.1.6.3) to both 
Oracle8i(8.1.6.3) and Oracle9i (9.2.0.3) server, I found the import to Oracle9i 
is much slower than import to Oracle8i.

Both servers have the same physical configuration (SUN E4500, 8G RAM, 8 CPUs, 
EMC disks,HBA same setting, NIC 100 full duplex). During the import, there was no 
paging and swapping. The only difference which I could find is that with Oracle9i 
server, I use LMT (but not ASS) and AUM.  

I did enable the 10046 trace and found the ela columns for wait event SQL*NET message 
from/to 
client on the Oracle9i server is much bigger. I tested with different 
setting (sort_area_size, buffer, commit=y/n, no indexes, noarchivelog), I did not get 
any luck.

Does anybody have the same experience?

Thanks,

Michael 
 


**
This e-mail contains privileged attorney-client communications and/or confidential 
information, and is only for the use by the intended recipient. Receipt by an 
unintended recipient does not constitute a waiver of any applicable privilege.

Reading, disclosure, discussion, dissemination, distribution or copying of this 
information by anyone other than the intended recipient or his or her employees or 
agents is strictly prohibited.  If you have received this communication in error, 
please immediately notify us and delete the original material from your computer.

Sempra Energy Trading Corp. (SET) is not the same company as SDGE or SoCalGas, the 
utilities owned by SET's parent company.  SET is not regulated by the California 
Public Utilities Commission and you do not have to buy SET's products and services to 
continue to receive quality regulated service from the utilities.
**

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Wu
  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).

OT SQL Server 2000 databases and Oracle tablespaces

2003-03-25 Thread Boivin, Patrice J

Title: Re: Amt of Training to be considered "up to date"



It 
used to be for SQL Server 7 that a "database" was the equivalent of a 
"tablespace" in Oracle 7.3.

Is 
that still the case?

Regards,

Patrice Boivin Systems Analyst (Oracle Certified DBA) 
Systems Admin  Operations | Admin. et 
Exploit. des systèmes Technology 
Services | Services 
technologiques Informatics 
Branch | Direction de 
l'informatique Maritimes Region, 
DFO | Région des Maritimes, MPO 
E-Mail: [EMAIL PROTECTED]

RE: Large Export Problem ......

2003-03-25 Thread Janardhana Babu Donga

Darrel,

Thanks for your response. It is DSS database and so we would like to
maintain unarchived mode. If something goes wrong, we will restore the DB
from previous weeks coldbackup and apply the transactions to make the DB
current. Archived log DB is not necessary in this case. 

I don't gain anything by using incremental export. Nightly loads will touch
every partitioned table and so incremental export will export the complete
tables and there won't be any difference between full export and incremental
export in this case. 

I need additional help in resolving my large export problem.

Thanks,
-- Babu

-Original Message-
Sent: Tuesday, March 25, 2003 12:44 PM
To: Multiple recipients of list ORACLE-L


Babu,

First, if it were me, I'd put this thing in archive log mode.  If we
may need to recover between full backups, that is the tried and true
means.
But, on to your question.  I'd look at a plan utilizing incremental
exports.  You start with a 'base' full export (weekly, monthly,
whichever), and do daily incremental or cumulative exports.  I'm not
going to offer too much detail here because I've never actually used
this and because you really should read all of the oracle documentation
on this before implementing it ...
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/toc.
htm

Two things I'll point out from that document:
1) A quote which directly addresses one of your issues...
You can do incremental, cumulative, and complete exports only
in full database mode (FULL=Y). 

2) A caveat of using this method:
Important: Incremental, cumulative, and complete Exports are
obsolete features that will be phased out in a subsequent release  

Please let us know how this turns out for you or if additional help is
needed.
Thanks,
Darrell



 [EMAIL PROTECTED] 03/25/03 01:59PM 
Dear List,

I have a large unarchived decission support database of size 270gig. We
do
take coldbackup of database files every sunday. We also take export
backup
to suplement the coldbackup. Export is taking too much time which we
can't
afford now. I need to reduce the export time to fit the weekend
schedule. In
the last few weeks it is failing as the database is down for
coldbackups
while the export is running. 

The database structure is as follows:

Partitioned tables size: 200gig 
  [static partitions(prior years) size 150 gig, and non-static
partitions(current yr) size 50gig]
non-partitioned tables: 70gig

I don't need to export static partitions every week. Once in 3/6months
is
OK.  I don't think I can eliminate static partitions in one full
export
script/parameter file.  Iam thinking of eliminating the static
partitions by
taking export in TABLE mode, which includes only NON-STATIC partitions
and
the remaining NON-PARTITION tables. I may have to hardcode the table
names.
 
The database has lots of packages/stored procs which will be stored in
the
dictionary I believe. 

My questions are: 
[1] How can I reconstruct a database using this type of export if
needed? 
[2] How can I simulate full export in this type (Table Mode) of
export?
[3] How can I export packages/stored procs and import to new DB if
necessary?
[4] Is there any other way to export the full database and eliminate
the
static partitions in a single step?
[5] What is the best way to solve my export problem??

Any ideas are appreciated. 

Thanks,
--  Babu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Janardhana Babu Donga
  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: Darrell Landrum
  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: Janardhana Babu Donga
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services

FW: 9000x faster than Oracle?

2003-03-25 Thread Pardee, Roy E




Apropos of the 'Database Modeling- Normalization - Dinosaurs or What?' 
thread:
Roy PardeeProgrammer/AnalystSWFPAC Lockheed Martin 
ITExtension 8487 
-Original Message-From: Pardee, Roy E Sent: 
Monday, March 03, 2003 12:56 PMTo: Jane; Kim; Mike; Nancy; Paul; 
RickSubject: 9000x faster than Oracle?
This looks interesting (from 
Slashdot.org):

I wonder how long it would take to roll this thing 
forward after a server crash...

===
Object Prevalence: Get Rid of 
Your Database?
Posted by Hemos 
on Monday March 03, @08:45AMfrom the 
throwing-it-out dept.A reader writes:" Persistence for 
object-oriented systems is an incredibly cumbersome task to deal with when 
building many kinds of applications: mapping objects to tables, XML, flat files 
or use some other non-OO way to represent data destroys encapsulation 
completely, and is generally slow, both at development and at runtime. The 
Object Prevalence concept, developed by the Prevayler team, and implemented in 
Java, C#, Smalltalk, Python, Perl, PHP, Ruby 
and Delphi, can be a great a 
solution to this mess. The concept is pretty simple: keep all the objects in RAM 
and serialize the commands that change those objects, optionally saving the 
whole system to disk every now and then (late at night, for example). This 
architecture results in query speeds that many people won't believe until they 
see for themselves: some benchmarks point out that it's 9000 times faster than a 
fully-cached-in-RAM Oracle database, for example. Good thing is: they can 
see it for themselves. Here's an article 
about it, in case you want to learn more." 

( Read 
More... | 331 
of 465 
comments )
Roy PardeeProgrammer/AnalystSWFPAC Lockheed Martin 
ITExtension 8487

RE: sort ip addresses

2003-03-25 Thread Stephen Lee

 -Original Message-
 Need a trick to sort ip addresses into numerical order.

Something a little different (not tested, so might need a little
adjustment).

-- go.ksh -
#!/usr/bin/ksh

{
sqlplus -s -XXX
/user/password
set feedback off heading off trimspool on
set pages 0
select ip_address from the_table;
} | /usr/bin/sort -nt . +1 +2 +3 +4


You ... ummm ... didn't NEED this to be entirely within a database session
did you?
And the sort options use the letters nt.  I hope that is OK.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  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: sort ip addresses