Oracle Discoverer Report Query

[Viral Amin]

Title: Oracle Discoverer Report Query





Hi All,


I need to create a report in Oracle Discoverer. I would like to know if it is possible to create a report with the following logic. If yes, then how do I go about creating a report.

I have a table TBLREPORT with the following structure from which I have to pick up records.


Table: TBLREPORT
dttime  DATE
interval VARCHAR2(10)  (The field will have values BASE, HOURLY)
rdvalue  number  (aggregate to be applied in business area is AVG)


Data in table:
Dttime    interval    rdvalue
20-AUG-2001 9:00 am   HOURLY   10
20-AUG-2001 10:00 am  HOURLY   20


21-AUG-2001 9:00 am   HOURLY   20
21-AUG-2001 10:00 am  HOURLY   30


22-AUG-2001 9:00 am   HOURLY   10
22-AUG-2001 10:00 am  HOURLY   20


23-AUG-2001 9:00 am   BASE 10
23-AUG-2001 9:15 am   BASE 20
23-AUG-2001 9:30 am   BASE 30
23-AUG-2001 9:45 am   BASE 40


23-AUG-2001 9:00 am   HOURLY   25    (the base data for 23-AUG-2001 from 9:00 am to 9:45 am is rolled up to arrive at HOURLY data e.g. 10+20+30+40 = 100/4 = 25)

23-AUG-2001 10:00 am  BASE 50
23-AUG-2001 10:15 am  BASE 60
23-AUG-2001 10:30 am  BASE 70
23-AUG-2001 10:45 am  BASE 80



23-AUG-2001 10:00 am  HOURLY   65    (the base data for 23-AUG-2001 from 10:00 am to 10:45 am is rolled up to arrive at HOURLY data e.g. 50+60+70+80 = 260/4 = 65)

24-AUG-2001 9:00 am   BASE 10
24-AUG-2001 9:15 am   BASE 20
24-AUG-2001 9:30 am   BASE 30
24-AUG-2001 9:45 am   BASE 40


24-AUG-2001 9:00 am   HOURLY   25    (the base data for 24-AUG-2001 from 9:00 am to 9:45 am is rolled up to arrive at HOURLY data e.g. 10+20+30+40 = 100/4 = 25)

24-AUG-2001 10:00 am  BASE 50
24-AUG-2001 10:15 am  BASE 60
24-AUG-2001 10:30 am  BASE 70
24-AUG-2001 10:45 am  BASE 80


24-AUG-2001 10:00 am  HOURLY   65    (the base data for 24-AUG-2001 from 10:00 am to 10:45 am is rolled up to arrive at HOURLY data e.g. 50+60+70+80 = 260/4 = 65)

From this table I have to pick-up data for week = 20-AUG-2001 to 24-AUG-2001.
(the week start date is entered by the user in the report)
I have to first check if BASE data exists for the week. 
If no base data exists then check for HOURLY data.


The following data exists:


No BASE data will be available for 20-AUG-201, 21-AUG-2001 or 22-AUG-2001, since it would have been deleted. HOURLY data is available for these days.

BASE data would be available for 23-AUG-2001  and 24-AUG-2001. HOURLY is available for these days also.


20-AUG-2001  21-AUG-2001 22-AUG-2001 23-AUG-2001 24-AUG-2001 
---  --- --- --- --- 
No BASE  No BASE No BASE BASE    BASE 
HOURLY   HOURLY  HOURLY  HOURLY  HOURLY 



The input to the report is: 
Week start date = 20-aug-2001 
Hour range = 9:00 am to 10:00 am


Now in order to produce a weekly report, 
1) The HOURLY data for 20-AUG-201, 21-AUG-2001 and 22-AUG-2001 should be viewed for the hour ranges the user enters. 
2) The BASE data for 23-AUG-2001 and 24-AUG-2001 should be rolled-up to arrive at HOURLY data only for the hour ranges entered by the user, using the logic specified for rollup as given in the data. The HOURLY data present for 23-AUG-2001 and 24-AUG-2001 should not be considered but the BASE data should be considered which should be rolled-up to arrive at HOURLY data. 

3) Then the HOURLY data from 20-AUG-201, 21-AUG-2001, 22-AUG-2001, Base data of 23-AUG-2001 and 24-AUG-2001 rolled-up to HOURLY  should be used to generate the weekly report, taking into consideration the hour ranges entered by the user.

Any help will be greatly appreciated.



Thanks in advance



Viral.

Re: Roles and Responsibilties

[Don Granaman]

I distinctly remember at least two.  Pardon me for butchering them...

One was the "overpromoted database guru" whose head exploded when exposed to
actual code after he said something like "You would be fools to ignore the
anti-binary least squares approach!" - evidently during a design meeting.

The other was better, but won't come across well here since I don't remember
the specifics.  Essentially, when starting a new project, Wally and Dilbert
were discussing what they needed - a catchy project name and a database.
When asked why the database, one replied "We like databases".

-Don Granaman
[certifiable OraSaurus]

PS:  My personal favorites from the last month:
http://www.dilbert.com/comics/dilbert/archive/dilbert-20010812.html
http://www.dilbert.com/comics/dilbert/archive/dilbert-20010904.html

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 06, 2001 3:21 PM


Does anyone know if Dilbert has Oracle-related comics, or database admin
related ones?

Just curious.

Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Acting Head
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] 


-Original Message-
Sent: Thursday, September 06, 2001 4:59 PM
To: Multiple recipients of list ORACLE-L


http://www.dilbert.com/comics/dilbert/archive/images/dilbert2001203080906.gi
f

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

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

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

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

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

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

Re: ms acces

[Arul kumar]

Hi agc,

I remember a GUI Wizard from Oracle Enterprise Manager ( of ver.8 !) which
provides easy steps to move data frm MS-Access to Oracle.

hope this may help u.

cheers,
Arul.

agc wrote:

> is there any tool that imports directly form ms acces to oracle?
> with out having to export ms acces files to flat text files and then
> load them in to oracle but some sort  of direct importation? cheers
>
> The information contained in this communication is
> confidential, is intended only for the use of the recipient
> named above, and may be legally privileged. If the reader
> of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or
> copying of this communication is strictly prohibited.
> If you have received this communication in error, please
> re-send this communication to the sender and delete the
> original message or any copy of it from your computer
> system.
> Please do not re-send by any reazon in any way or form any of the
> informatino here contained.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: agc
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arul kumar
  INET: [EMAIL PROTECTED]

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

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

RE: Do fast full index scans do physical disk reads?

[MacGregor, Ian A.]

There is no rule that says an index will be cache.  Yes physical reads are being done.
If the unique index is composed of more than one column look into compressing it.

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]



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



I am confused by the output from tkprof below.   An fast full index
scan is being performed.   However, from the statistics, it looks as
thought 649 physical disk reads are being performed.  Is that actually
the case?   Are physical disk reads being done?

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network


Select SD.KS_OBJECTID as CONCEPTID
>From kbowner.KS_SHORTDESCRIPTION SD
Where SD.KS_DESCRIPTIONTYPE = 'CPTNAME' And
UPPER(SD.KS_DESCRIPTIONTEXT) = ''

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.03   0.03  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.30   0.30649649  4
0
--- --   -- -- -- --
--
total3  0.33   0.33649649  4
0

Rows Row Source Operation
---  ---
  0  INDEX FAST FULL SCAN (object id 5286)


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  0   INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 'SYS_C001069' (UNIQUE)

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

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

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

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

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

Re: Fast Full Index Scan in 8.1.7

[Jared Still]

The alternative is probably a full table scan.

Are you sure you want to turn it off?

I think you need to hint your SQL to do that.

Jared

On Thursday 06 September 2001 13:21, [EMAIL PROTECTED] wrote:
> I had been using the FAST_FULL_SCAN_ENABLED initialization parameter in an
> 8.0.4 database.
>
> Apparently this parameter is now obsolete in 8.1.7.2.   Seems as though I
> am getting the fast full index
> scans automatically now.  I'm not sure if I want them in all cases.   Is
> there a way to turn them off?
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

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

Re: dbms_jobs

[Jared Still]

Any thoughts on how this was scheduled this way?

Schedule a job that takes 10 minutes, set the interval
to run 5 minutes after the first job starts.

Here's the job:

create or replace procedure dummy
is
begin
   -- sleep for 10 minutes
   -- envy the computer
   -- waiting for interruption
   dbms_lock.sleep(10*60);
end;
/

Here's the submission:

declare
   jobno integer;
begin
   dbms_job.submit(
  job => jobno
  , what => 'dummy;'
  -- provide resolution to the second
  --midnighthour ofminute ofunits per day
  -- of current dayday to run  hr to run( 1 second )
  , next_date => trunc(sysdate) + ((21 * (60*60) + ( 60*45)) * ( 1/(60*60*24)))
  , interval => 'trunc(sysdate) + ((21 * (60*60) + ( 60*50)) * ( 1/(60*60*24)))'
   );
   commit;
end;
/

Here's before it ran:
   FAIL
SCHEMA_USE PRIV_USER  LOG_USER  JOB LAST_DATE   LAST_SEC NEXT_DATE 
  NEXT_SEC TOTAL_TIME
B INTERVAL  URES WHAT
-- -- -- -- ---  
---  --
-   
JKSTILLJKSTILLJKSTILL22  09/06/2001 
21:45:00 21:45:00244
N trunc(sysdate) + ((2   dummy;
 
  1 * (60*60) + ( 60*5
 
  0)) * ( 1/(60*60*24)
 
  ))


Here's after it ran:
 
   FAIL
SCHEMA_USE PRIV_USER  LOG_USER  JOB LAST_DATE   LAST_SEC NEXT_DATE 
  NEXT_SEC TOTAL_TIME
B INTERVAL  URES WHAT
-- -- -- -- ---  
---  --
-   
JKSTILLJKSTILLJKSTILL22 09/06/2001 21:45:14 21:45:14 09/06/2001 
21:57:28 21:57:28614
N trunc(sysdate) + ((21 dummy;
 
  1 * (60*60) + ( 60*5
 
  0)) * ( 1/(60*60*24)
 
  ))

Notice the next run time is 00:02:28 after completion of the first job.

Jared


On Thursday 06 September 2001 00:50, [EMAIL PROTECTED] wrote:
> Hi
>
>
> As far as I know the job will be rescheduled after the job completes. So in
> your examples the job will start one hour after the two hour job finishes.
>
>
> Jack
>
>
>
>
> David Turner <[EMAIL PROTECTED]>@fatcity.com on 06-09-2001 05:35:32
>
> Please respond to [EMAIL PROTECTED]
>
> Sent by:  [EMAIL PROTECTED]
>
>
> To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
>
> I was wondering if you schedule a job to run every hour and say
> the job takes 2 hours to run. Will the next run of the job queue up
> or will it run in parallel with the current job? I'll be testing this
> but if anyone knows I would appreciate it?
>
> Also if the second job waits for the first job to finish how can you see
> how many jobs have queued up?
>
> Thanks, Dave
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David Turner
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> =
> De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
> uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
> vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
> derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
> Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
> volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
> voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
> verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
> worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
>
> Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
> vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
> en het origineel en eventuele kopieën te verwijderen en te vernietigen.
>
> Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
> voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
> algemene voorwaarden worden u op verzoek kosteloos toegezonden.
> =
> The information contained in this communication is confidential and is
> intended solely for the use of the individual or entity to whom it is
> addressed. You should not copy, dis

Re: archive query - revisited

[Jared Still]

I've found that large DML operations create a large amount
of redo even if the objects are in a locally managed tablespace.

Ratio is 17:1, at least for me.

Jared

On Wednesday 05 September 2001 10:11, Mohammad Rafiq wrote:
> Grant,
> I am glad that you found reason for lot of archiving.
> Yes, even with nologging, if table/tablespaces are dictionery managed,
> there are redologs generated for extent management.
> Archiving may be minimized by commiting after certain amount of rows like
> rows >= 1 or whaever which your redologs can handle.
>
> For other tables created through procedure if those are created as
> unrecoverable/nologging or not. Make it nologging to avoid redo's specially
> created as select.I cannot open your attachment
> Regards
>
>
> MOHAMMAD RAFIQ
>
>
>
> Reply-To: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: Tue, 04 Sep 2001 19:40:24 -0800
>
> Mohammad and Jaymany thanks for the responses.
>
> Upon running your script Mohammad, I noticed that archive files were being
> written to the archive directory during a large insert operation and
> stopped  being written once the insert had completed.  The insert command
> uses APPEND, the associated table is indexed but created with nologging.
> It is however partitioned.  I'm still confused as to why logging continues
> to occur.could it be that the extents allocated during the insert
> operation are logged?
>
> The same procedure creates a number of worktables to assist with join
> queries (not temp tables rather permanent tables) which are subsequently
> dropped after the query has completed (no indexes - hash joins only).
> There are no deletes or updates.
>
> Any more clues??
>
> Here's a copy of the table structure:
>
> (See attached file: table.txt)
>
> Here's a copy of the insert statement summarized from the procedure:
>
> (See attached file: insert.txt)
>
> Many thanks
> Grant
> << table.txt >>
> << insert.txt >>
>
>
> _
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

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

Re: Alter tablespace add datafile

[Jared Still]

It was still happenind today, so I unsubscribed him

Jared

On Wednesday 05 September 2001 23:18,  wrote:
> is it just me, or is anybody else getting blank replies (exluding the sig)
> from Arich Henneman?
>
> -Original Message-
> [mailto:X]
> Sent: Thursday, 6 September 2001 2:37 PM
> To: X
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Arich Henneman
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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

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

RE: How to change error messages?

[Jacques Kilchoer]

Title: RE: How to change error messages?





> -Original Message-
> From: Terrian, Tom [mailto:[EMAIL PROTECTED]]
> 
> Does anyone know how to change the message that Oracle 
> displays when an error
> occurs.  For example:
> 
> SQL> select * from fjfjfjfjf;
> select * from fjfjfjfjf
>   *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> 
> I am sure that Oracle Corp. will really frown on this, but 
> can I change "table
> or view does not exist" to something else?



If you're bored with seeing the same old error messages, have you tried spicing up your life by installing Oracle in a different language?

SQL> select * from fjfjfjfjf;
select * from fjfjfjfjf
  *
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante



Otherwise, like someone else suggested, you can have a EXCEPTION clause in a PL/SQL block to catch the error and display a different message, but that won't change the "default" error message.

RE: Scripts for Rebuilding Indexes Nightly on Solaris

[Post, Ethan]

I would add that using the MONITORING option is a great way to begin to look
for tables that have indexes that might need rebuilding.  You can query the
DBA_TAB_MODIFICATIONS view to see what tables have a lot of delete/insert
activity.

- Ethan

>-Original Message-
>From: Walthour, Jon (GEAE, Compaq) [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, September 06, 2001 12:25 PM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Scripts for Rebuilding Indexes Nightly on Solaris
>
>
>First off, checking on a regular basis for indexes that need 
>rebuilding is
>fine, but don't rebuild all of them every night. Not only is 
>it unnecessary,
>it can cause lots of problems. Only rebuild the indexes you 
>need to--the
>ones who are significantly browned, have a significant percentage of
>deletes, or whose height is significant.
>
>Second, setting up a cron job is indeed a good idea. And David 
>is right that
>you want to make sure to explicitly set up your environment, 
>but not for the
>reason he suggests. The problem isn't that the job runs as 
>root. If it were
>the case that every cron job on a UNIX box ran as root, then 
>that would be a
>mighty big security hole. The issue is that when running a 
>cron job, the
>account under which it runs does not automatically get its environment
>initialized by running .profile as it does when you log in. Thus, the
>environment must be specifically laid out.
>
>Hope this helps.
>
>Jon Walthour
>
>
>

--
This e-mail is intended for the use of the addressee(s) only and may contain 
privileged, confidential, or proprietary information that is exempt from disclosure 
under law.  If you have received this message in error, please inform us promptly by 
reply e-mail, then delete the e-mail and destroy any printed copy.   Thank you.

==
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: [EMAIL PROTECTED]

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

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

Re: Egregious coding

[Greg Moore]

Title: Egregious coding



>  I would love to hold a
>  valid threat over their 
heads
 
The key word is valid.  Threatening to throw a 
switch and blow up any SQL that contains literals is a threat, but is it a 
valid threat?
 
I mean, what was your previous job, suicide 
bomber?
 
Seriously, providing accurate measurement of how 
much the extra parsing now costs, along with an evaluation of current CPU use 
and an estimate of how much longer they have before the system bottlenecks might 
be a touch more civilized.
 
 

   

IS NULL doesn't always disable index

[Greg Moore]

Title: Egregious coding



Coding "WHERE job IS NULL" is said to prevent you 
from being able to use an index, supposedly because indexes don't store null 
values.
 
Experimenting, I noticed that if a concatenated 
index has one column "A", that's defined as not null, it means every row 
will be in the index -- even if the other column "B" in the index is 
nullable and some rows have nulls.  What's surprising is if your SQL asks 
for rows "WHERE B is null" the index will be used to find these 
rows.
 
The optimizer is now smart enough (8.1.6) to know 
that if one column in a concatenated index is a not null column, then every row 
is guaranteed to be in the index, even if some of the values in the other column 
are null, and apparently the leaf blocks know which rows have nulls, allowing 
the index to be used to resolve the query.
 
Far from preventing the use of an index, if the 
SELECT only asks for columns in the concatenated index, Oracle will resolve the 
query using the index alone, no table access.
 
On a small test table Oracle wanted to full scan 
the table, so an INDEX hint was required to get this result.

  

   

RE: Egregious coding

[Norwood Bradly A]

Title: Egregious coding




Good 
idea.  Cursor_Sharing = force results in the SQL 
statements being converted, substituting the hard-coded values with 
temporary bind variables 
before  they are 
parsed.
It helps where 
the SQL problem is a scarcity 
of bind variables, but that is probably not the only coding problem 
that contributes to your miseries(bet they 
never heard of joins, outer, inner or 
multidimensional??). 
-Original Message-From: John Lewis 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, September 06, 2001 
6:30 PMTo: Multiple recipients of list ORACLE-LSubject: 
RE: Egregious coding 
You 
can put "cursor_sharing=force" in the init file. It forces the reuse of sql even 
without
bind 
variables. I had the same problem with the "javoids".

  -Original Message-From: Hagedorn, Linda 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 06, 2001 
  4:06 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Egregious coding 
  Has anyone thought of a clever way to fail every 
  SQL statement that does not use a bind variable that I could switch on and off 
  as required?   
  I'm looking at hundreds of thousands of queries in 
  the v$sqlarea in production.  I've taken engineering management to task 
  over this, and have gotten empty promises in return.  I would love to 
  hold a valid threat over their heads that I'm going to shut off every 
  statement that is not coded properly.  
  Any ideas are appreciated. 
  Linda 
   
    

Re: ms acces

[Regina Harter]

If you can get an ODBC connection from MS Access to Oracle, Access itself 
will do it.  I have only done it myself once, but I might be able to get 
together with someone and try to remember how it is done...

At 11:58 AM 9/6/01 -0800, you wrote:
>is there any tool that imports directly form ms acces to oracle?
>with out having to export ms acces files to flat text files and then
>load them in to oracle but some sort  of direct importation? cheers
>
>The information contained in this communication is
>confidential, is intended only for the use of the recipient
>named above, and may be legally privileged. If the reader
>of this message is not the intended recipient, you are
>hereby notified that any dissemination, distribution or
>copying of this communication is strictly prohibited.
>If you have received this communication in error, please
>re-send this communication to the sender and delete the
>original message or any copy of it from your computer
>system.
>Please do not re-send by any reazon in any way or form any of the
>informatino here contained.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: agc
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).

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

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

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

RE: Egregious coding

[Wong, Bing]

Title: Egregious coding



I think it is best to escalate this to 
management to deal with bad SQL coding.  May be your shop needs SQL coding 
standard?
 
 

  -Original Message-From: Hagedorn, Linda 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 06, 2001 
  4:06 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Egregious coding 
  Has anyone thought of a clever way to fail every 
  SQL statement that does not use a bind variable that I could switch on and off 
  as required?   
  I'm looking at hundreds of thousands of queries in 
  the v$sqlarea in production.  I've taken engineering management to task 
  over this, and have gotten empty promises in return.  I would love to 
  hold a valid threat over their heads that I'm going to shut off every 
  statement that is not coded properly.  
  Any ideas are appreciated. 
  Linda 
   
    

RE: Egregious coding

[Wong, Bing]

Title: Egregious coding



Please 
be aware of the following note when set to FORCE...
 

Note: Forcing cursor sharing among similar (but not 
identical) statements can have unexpected results in some DSS applications and 
if your applications use stored outlines. 
 

  -Original Message-From: John Lewis 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 06, 2001 
  4:30 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Egregious coding 
  You 
  can put "cursor_sharing=force" in the init file. It forces the reuse of sql 
  even without
  bind 
  variables. I had the same problem with the "javoids".
  
-Original Message-From: Hagedorn, Linda 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, September 06, 2001 
4:06 PMTo: Multiple recipients of list 
ORACLE-LSubject: Egregious coding 
Has anyone thought of a clever way to fail every 
SQL statement that does not use a bind variable that I could switch on and 
off as required?   
I'm looking at hundreds of thousands of queries 
in the v$sqlarea in production.  I've taken engineering management to 
task over this, and have gotten empty promises in return.  I would love 
to hold a valid threat over their heads that I'm going to shut off every 
statement that is not coded properly.  
Any ideas are appreciated. 
Linda 
 
  

Re: Destination address unreachable

[Scott Shafer]

> Not sure if this applies all over, but the Marriot Residence Inn in St
Louis
> used to tap a keg (or two) for happy hour each night.  Free beer and
> horse-de-ovaries for guests - ah that was good livin'...
>
> --Scott Shafer
>   San Antonio, TX
>
>
> - Original Message -
> From: "Farnsworth, Dave" <[EMAIL PROTECTED]>
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Thursday, September 06, 2001 11:01 AM
> Subject: RE: OT: Oracle DBA With Internet Experience Needed in Harrisburg,
>
>
> Back in the fun days of college you could find on any weeknight a bar that
> had a special of $2 for all you could drink(beer) from 6pm to 10pm.  I got
> my money's worth!! ;O)
>
> Dave



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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

Re: How to change error messages?

[Scott Shafer]

Trying to remember here, but isn't there a file called "oraus.mesg" or
something similiar somewhere under your $oracle_home that contains all the
messages and events?  Edit the message you want.  Do not tell Oracle Corp.
you did this.  I have not done and definitely do not recommend doing it
unless its on a "play" system.  This has fun possibilities when I think
about certain duhvelopers...  Hmmm...

Scott Shafer
San Antonio, TX


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 06, 2001 4:18 PM


> I don't know about SQLPLUS, but you sure can do it in pl/sql. You can
> manipulate it in EXCEPTION clause.
>
> HTH
> Raj



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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

RE: Egregious coding

[John Lewis]

Title: Egregious coding



You 
can put "cursor_sharing=force" in the init file. It forces the reuse of sql even 
without
bind 
variables. I had the same problem with the "javoids".

  -Original Message-From: Hagedorn, Linda 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 06, 2001 
  4:06 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Egregious coding 
  Has anyone thought of a clever way to fail every 
  SQL statement that does not use a bind variable that I could switch on and off 
  as required?   
  I'm looking at hundreds of thousands of queries in 
  the v$sqlarea in production.  I've taken engineering management to task 
  over this, and have gotten empty promises in return.  I would love to 
  hold a valid threat over their heads that I'm going to shut off every 
  statement that is not coded properly.  
  Any ideas are appreciated. 
  Linda 
   
    

Egregious coding

[Hagedorn, Linda]

Title: Egregious coding 






Has anyone thought of a clever way to fail every SQL statement that does not use a bind variable that I could switch on and off as required?   

I'm looking at hundreds of thousands of queries in the v$sqlarea in production.  I've taken engineering management to task over this, and have gotten empty promises in return.  I would love to hold a valid threat over their heads that I'm going to shut off every statement that is not coded properly.  

Any ideas are appreciated. 


Linda 


 




 

RE: collections / records / index-by, etc - long, sorry

[Bala, Prakash]

Lisa,
 
How about this:
 
declare
  EmpRec Emp%ROWTYPE;
  type EmpTable is table of EmpRec%type index by binary_integer;
  emp EmpTable;
begin
  emp(1).ename := 'xx';
  emp(1).ssn := 896767097;
  emp(2).ename := 'yy';
end;
 
 
Prakash
 

-Original Message-
Sent: Thursday, September 06, 2001 10:36 AM
To: Multiple recipients of list ORACLE-L



Good morning everyone, 

well I finally have something to work on.  Not being one to whip out shoddy
code, I want to write my load scripts utilizing pl/sql tables and caching as
much as I can, along with utilizing FORALL and BULK COLLECT. 

The last time I did this, I was creating table rows in pl/sql INDEX-BY
tables.  I had one pl/sql table for each column in the target table (that I
was going to insert modified rows to) and it worked fine, very fast in fact.
However, it was an awful mess because I ended up maintaining many many
INDEX-BY tables with one index to refer to each record.  

What I'm talking about is this 

table in the db is emp : enum number, ename varchar 

To represent this table in memory and assemble the records I created the
following index-by tables at the module (package) level

mtab_ename 
mtab_enum 

and inserted values like so 

mtab_enum(idx) := var1; 
mtab_ename(idx) := var2; 

and when it came time to insert, this is what I did 

FORALL i IN mtab_enum.FIRST..mtab_enum.LAST 
  INSERT INTO emp (enum, ename) VALUES mtab_enum(i), mtab_ename(i); 


My question is, is there a way I can have one object that represents the
structure of the entire emp table?  I tried this

TYPE emptabtype IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; 

mtab_emp emptabtype; 

But this doesn't seem to work.  I can't pull the values out (var :=
mtab_emp.ename(i)).   I also don't want to use varrays just because I have
to explicitly set the size.  

I also want to be able to use BULK COLLECT and FORALL.  Otherwise this kind
of stuff is a waste of time.  I then read in the documentation that
"Collections can have only one dimension and must be indexed by integers".
It sounds like what I want to do isn't possible.  

Any suggestions or comments are appreciated.  Thanks 

Lisa Koivu 
Oracle Database Administrator 
Fairfield Resorts, Inc. 
954-935-4117 






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

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

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

Enterprise backup product

[Kim_Thompson]

 Just wondering what product(s) organizations are using for enterprise 
 backup, and how easy or difficult it was to set up the Oracle 
 component to interface with RMAN and perform hot backups.
 
 We're implementing Tivoli Storage Manager/Data Protection for Oracle 
 and our systems people want to ensure they've chosen a widely utilized 
 solution.
 
 Thanks for your input.
 
 Kim Thompson
 City and County of San Francisco 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

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

Re: tool to dump out space info

[Don Granaman]

I would like to see it also.  A few months ago, I wrote a whole space management reporting 
thing ("spaceman"), but it didn't (doesn't?) yet accomodate 
partitioning well.
 
-Don Granaman
[certifiable OraSaurus]
([EMAIL PROTECTED])

  - Original Message - 
  From: 
  JOE 
  TESTA 
  To: Multiple recipients of list ORACLE-L 
  Sent: Thursday, September 06, 2001 11:50 
  AM
  Subject: tool to dump out space 
info
  
  Amazingly, something i wrote a year ago is coming in handy now.
   
  Here is what it does:
   
  1.  grab the total allocated, calculate free space for each 
  datafile.
  2.  grab each table and index(both partitioned and not) and 
  calculate the space allocated AND the space free within the extents.
  3.  dump all that info to CSV, so damagers can put it into 
  spreadsheet and do graphs(oh joy).
   
  anyways if anyone wants it, feel free to ask and the whole thing is 
  yours.
   
  its unix oriented so if you run on windoze, be sure to change / forward 
  slash to \ backward slash.
   
  Unix forward thinking
  windoze backward thinking, 
   
  coincidence, i think not.
   
  joe
   

Rollback segment usage

[Jamadagni, Rajendra]

One of our user is planning to update a single column in a 5 million row
table. Max size of a single RBS is 1GB. When trying to find optimal number
of rows to commit after, should we consider the whole row size? Or we can
just think about the single column size (this sounds very unlikely to me)?

Also, If I have a table which contains a date column. I need to change this
column to a number(14) for business reasons. What would be the best
approach? 

current plan is as follows ...
1. drop index that use this column,
2. null the column
3. redefine the column
4. populate the column committing n rows at a time
5. rebuild indexes

is there a better and efficient way?

TIA
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art !


*2

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

*2

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

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

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

RE: OT: Interesting News..

[Jesse, Rich]

H...

Compaq service does suck at all levels.  However, I have to defend HP
service.  We aren't a huge company (<800 employees), but we do have Gold
Service with HP for our little 6-way K570.  And I have to say that it's been
outstanding.  I've never seen another service company stay onsite for 24+
hours on their weekend to help with our OS upgrade.

Or it could just be our local guys'n'gals that do an outstanding job...  :)

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

-Original Message-
Sent: Thursday, September 06, 2001 15:22
To: Multiple recipients of list ORACLE-L


According to our hardware resalescritter: especially for anything 
below large enterprise level, both HP and Compaq services suck.

so, I guess the objective is to create a new company who services 
suck even more than either individually?

Synergy!!!



> Guy Hammond wrote:
> >
> > On the contrary, I'd say this is a godsend for Sun. Compaq made a mess
> > of truly epic proportions when they tried to integrate DEC and Tandem
> > into their operations. HP did just as badly with Apollo. 

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

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

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

RE: How to change error messages?

[Jamadagni, Rajendra]

I don't know about SQLPLUS, but you sure can do it in pl/sql. You can
manipulate it in EXCEPTION clause.

HTH
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art !

*1

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

*1

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

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

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

Re: OT: insulated coffee mug lid tab failure - tech support reque

[Eric D. Pierce]

R-

Never backup while drinking first cup, but strictly iced in summer 
(like the ultra strong french iced coffee they serve in vietnamese 
restaraunts), lukewarm in winter.

per domestic relationship configuration, due to limitations related 
to inappropriateness of placement of reccomended hardware retrofit in 
kitchen area, workaround is to use a butter knife to open up the 
mutha f* using lateral torsional vector algorithm




[ORACLE-L Digest -- Volume 2001, Number 249]


> 
> --
> 
>  From: "Rachel Carmichael" <[EMAIL PROTECTED]>
>  Date: Thu, 06 Sep 2001 01:48:07 +
>  Subject: Re: OT: insulated coffee mug lid tab failure - tech support
>  reque

...

> On another note -- Eric, do you have a "cold" or "hot" backup of the
> contents of the mug?
> 
> >From: Paul Drake <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: Re: OT: insulated coffee mug lid tab failure - tech support
> >reque Date: Wed, 05 Sep 2001 16:10:23 -0800
> >
> >Kirti,
> >
> >Well, I think that a 15" pry bar might be able to get the coffee mug
> >open, but only for removing the contents of the cup. Most likely, the
> >vessel integrity would be compromised, and you should export the database
> >and rebuild immediately. :)


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

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

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

OT Note of interest to published leaders.

[Ron Rogers]

List,
  How ironic it is? or how ironic is it? . I just read the article about Ellen Hancock 
CEO Exodus Communications and the story about managing your company and outsourcing in 
the Sept/Oct issue of the ORACLE magazine. I went to the Computerworld website to read 
about the changes in the IT industry and future trends. I discovered an article about 
Ellen Hancock CEO Exodus Communications being replaced by a member of the board.
  I guess it may not be the best thing to be published in the Oracle magazine.
 Food for thought...

Is your glass half full, half empty, or do you have to much glass?
ROR mª¿ªm

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

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

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

RE: Last time an Index was used..

[Veronica Levin]

How about 8.1.7 ? Any chance I could trace index usage?

Saludos, 
Veronica Levin Enriquez
Administrador AIX
Compañía Cervecera de Nicaragua


-Mensaje original-
De: Charlie Mengler [mailto:[EMAIL PROTECTED]]
Enviado el: Jueves, 06 de Septiembre de 2001 02:22 p.m.
Para: Multiple recipients of list ORACLE-L
Asunto: Re: Last time an Index was used..


Under 9i

MONITORING USAGE | NOMONITORING USAGE 

Use this clause to begin or end the collection of statistics on index
usage. This clause is useful in determining whether an index is being
used. 

Specify MONITORING USAGE to begin statistics collection. Oracle first
clears existing statistics on index and then begins to collect
statistics on index usage.
Statistics collection continues until a subsequent ALTER INDEX ...
NOMONITORING USAGE statement is executed. 

To terminate collection of statistics on index, specify NOMONITORING
USAGE. 

Connor McDonald wrote:
> 
> Not really - but you could move it into its own
> tablespace and then monitor reads/write on this tspace
> using v$filestat.
> 
> hth
> connor
> 
>  --- Veronica Levin <[EMAIL PROTECTED]> wrote: >
> Hi listers,
> > Is there a chance I could find somehow the last time
> > (date) an index was
> > used
> > Any help will be appreciated!
> >
> > Saludos,
> > Veronica Levin Enriquez
> > Administrador AIX
> > Compañía Cervecera de Nicaragua
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Veronica Levin
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX:
> > (858) 538-5051
> > San Diego, California-- Public Internet
> > access / Mailing Lists
> >
> 
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
> 
> "Some days you're the pigeon, some days you're the statue"
> 
> 
> Do You Yahoo!?
> Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
> or your free @yahoo.ie address at http://mail.yahoo.ie
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Charlie Mengler  Maintenance Warehouse  
[EMAIL PROTECTED] 10641 Scripps Summit Ct.
858-831-2229 San Diego, CA 92131
Data-free analysis results in a success-free history.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: [EMAIL PROTECTED]

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

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

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

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

Oracle DBA with SAP Needed in St. Louis..

[OraStaff]

Join an excellent West St. Louis county based organization,
offering challenging projects and an excellent work environment. 
As a key Oracle DBA resource you will play an intregal role in both in-house
and SAP based projects.

*Responsibilitites
In this environment, you will have a variety of responsibilities including
logical 
and physical design, performance tuning, security, full cycle scratch
development projects 
as well as support of SAP and other packages.

This is a full time staff position so no sub-contractors or third parties
please.

Please do not call or send a resume if you are not in the U.S. and/or need 
sponsorship.


* Requirements:
- 3+ Years Oracle DBA experience 
- SAP-R/3 experience is strongly preferred
- Any Data Warehouse/Data Mining experience is a plus
- U.S. citizenship or Permanent residency is also required

These positions offer:
   * Stability 
   * The opportunity to become a key member of the team.
   * Base salary-D.O.E. up to 82K to start.
   
PLEASE do not send your resume if you are not in the United States.

For  immediate consideration, please send your resume as a Word attachment to:
OraStaff, Inc.
Email: [EMAIL PROTECTED]
ph: 1-800 -549-8502
Please use job code One/St. Louis/DBA SAP/Fred

All Submissions are handled in confidence.

*We pay referral fees.
So please contact me if you know of anyone who would be qualified/interested
in the posiition described above- if it is not a match for your skills.
Thanks,
Bill Law







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

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

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

RE: question about nologging

[Christopher Spence]

Oracle will always generate redo, but nologging will limit the redo
generate, as long as it meets the criteria for nologging.


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

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

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 



-Original Message-
Sent: Thursday, September 06, 2001 4:11 PM
To: Multiple recipients of list ORACLE-L


Hi,

I'm running the following command:

insert into owner1.table1 nologging (select * from owner2.table2);

There is no indices or constraints on table
owner1.table1, yet oracle is generating redo logs for
this transaction. Does anyone know why?

thanks

=


__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gene Gurevich
  INET: [EMAIL PROTECTED]

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

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

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

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

Do fast full index scans do physical disk reads?

[Cherie_Machler]

I am confused by the output from tkprof below.   An fast full index
scan is being performed.   However, from the statistics, it looks as
thought 649 physical disk reads are being performed.  Is that actually
the case?   Are physical disk reads being done?

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network


Select SD.KS_OBJECTID as CONCEPTID
>From kbowner.KS_SHORTDESCRIPTION SD
Where SD.KS_DESCRIPTIONTYPE = 'CPTNAME' And
UPPER(SD.KS_DESCRIPTIONTEXT) = ''

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.03   0.03  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.30   0.30649649  4
0
--- --   -- -- -- --
--
total3  0.33   0.33649649  4
0

Rows Row Source Operation
---  ---
  0  INDEX FAST FULL SCAN (object id 5286)


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  0   INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 'SYS_C001069' (UNIQUE)

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

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

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

How to change error messages?

[Terrian, Tom]

Does anyone know how to change the message that Oracle displays when an error
occurs.  For example:

SQL> select * from fjfjfjfjf;
select * from fjfjfjfjf
  *
ERROR at line 1:
ORA-00942: table or view does not exist

I am sure that Oracle Corp. will really frown on this, but can I change "table
or view does not exist" to something else?


Tom Terrian
Oracle DBA
WPAFB - DAASC
[EMAIL PROTECTED]
937-656-3844 

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

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

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

RE: Interesting News..

[Ken Janusz]

And then Computer Associates can buy the new company and it will suck X 3.

-Original Message-
Sent: Thursday, September 06, 2001 3:22 PM
To: Multiple recipients of list ORACLE-L


According to our hardware resalescritter: especially for anything 
below large enterprise level, both HP and Compaq services suck.

so, I guess the objective is to create a new company who services 
suck even more than either individually?

Synergy!!!



> Guy Hammond wrote:
> >
> > On the contrary, I'd say this is a godsend for Sun. Compaq made a mess
> > of truly epic proportions when they tried to integrate DEC and Tandem
> > into their operations. HP did just as badly with Apollo. 

...



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

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

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

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

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

RE: collections/PLSQL tables

[Koivu, Lisa]

Title: RE: collections/PLSQL tables





Really?  The documentation says that when you use limit, you won't have to keep track of records you have already used and haven't already seen.  But then again the documentation has been wrong before.  I'll have to give it a try.

Thanks for your comments Raj.  I especially liked fuum.  :)


Signing off for now, have a great weekend all.  
Lisa Koivu
"The DBA with the hoarse voice from a football game, of all things"
Fairfield Resorts, Inc. 
Ft. Lauderdale, FL USA




-Original Message-
From:   Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, September 06, 2001 4:11 PM
To: Multiple recipients of list ORACLE-L
Subject:    RE: collections/PLSQL tables


Lisa,


you are right ... on both counts ... but look at it this way ... if you use
the LIMIT clause, when you go for fetch next set of data, you have to figure
out which rows to skip, so there you have more logic to work with. 


As for the amount of memory, you are right too, but then again it matters a
lot how you design the pl/sql table. Also if one is worried about space
utilization, look at PRAGMA SEREALLY_REUSABLE. This pragma can be
incorporated into package spec, so once package execution is complete,  the
data space is released to SGA immediately (more details are in the manual),
it is reallocated when package is invoked. Previously pl/sql table allocated
memory was wither help or if you specify FUUM
(DBMS_SESSION.FREE_UNUSED_USER_MEMORY) the memory was released BUT only for
other pl/sql table use until your session disconnects.


Raj
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.


QOTD: Any clod can have facts, but having an opinion is an art ! 


*1


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

*1


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


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

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

When optimizer reevaluate SQL statement

[Hillman, Alex]

Let's assume that SQL statement was parsed by user X. If this or another
user reexecute this same statement what are the conditions that this SQL
statement will be reparsed? Let's assume that privileges are not changed and
tables and/or views are not dropped and views are not changed. And optimizer
parameters are not changed. First come to mind is dropping index. What about
reanalizing one of the object - theoretically should also reparse. Anything
else?

Also is there possibility to force reparsing of SQL statement if let say
index was added - short of flashing shared pool?

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

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

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

OT/213879487 - Re: ORACLE-L Digest -- Volume 2001, Number 249

[Eric D. Pierce]

got one, but the wife refuses to let me keep it in the kitchen, and 
I'm way to dangerous going into the garage before I have my first cup 
of coffee.


> 
> --
> 
>  From: Jeremiah Wilton <[EMAIL PROTECTED]>
>  Date: Wed, 5 Sep 2001 14:43:55 -0700 (PDT)
>  Subject: Re: OT: insulated coffee mug lid tab failure - tech support
>  request
> 
> There is an aftermarket mug lid retrofit kit available from Amazon.com for
> this known failure mode.
> 
> It is available on Amazon.com at the following URL:
> 
> http://www.amazon.com/exec/obidos/asin/B2N5VN
> 
> Please refer to technical support request number 213879487 in future
> communications on this subject.
> 
> --
> Jeremiah Wilton
> http://www.speakeasy.net/~jwilton
> 
> On Wed, 5 Sep 2001, Eric D. Pierce wrote:
> 
> > I LOOOVE YO MAAAN!
> >
> > About a year ago I got a free car-cupholder-sized insulated coffee
> > mug from Amazon.com. It has a quote from Albert Einstein on it:
> >
> >"If at first the idea is not absurd,
> > then there is no hope for it."
> >
> > The lid fit way too tight, and eventually as a result, the little
> > plastic tab that you use to pop the lid off busted off.
> >
> > Now the lid, which was originally really annoying to try to get off, is
> > even harder.
> >
> > "fyi".


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

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

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

Re: question about nologging

[JOE TESTA]

standard answer for redo(dont think yours is covered):
 
. SQL Statements That Can Use No-Logging Mode   
    The following operations can make use of no-logging 
mode:     direct load 
(SQL*Loader)     direct-load INSERT 
    CREATE TABLE ... AS SELECT 
    CREATE INDEX 
    ALTER TABLE ... MOVE PARTITION 
    ALTER TABLE ... SPLIT PARTITION 
    ALTER INDEX ... SPLIT PARTITION 
    ALTER INDEX ... REBUILD 
    ALTER INDEX ... REBUILD PARTITION 
    INSERT, UPDATE, and DELETE on 
LOBs in NOCACHE NOLOGGING mode stored out of line>>> 
[EMAIL PROTECTED] 09/06/01 04:10PM >>>Hi,I'm 
running the following command:insert into owner1.table1 nologging 
(select * fromowner2.table2);There is no indices or constraints on 
tableowner1.table1, yet oracle is generating redo logs forthis 
transaction. Does anyone know 
why?thanks=__Do 
You Yahoo!?Get email alerts & NEW webcam video instant messaging with 
Yahoo! Messengerhttp://im.yahoo.com-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Gene 
Gurevich  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- (858) 538-5051  FAX: (858) 538-5051San 
Diego, California    -- Public Internet 
access / Mailing 
ListsTo 
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: index in data tablespace ???

[Connor McDonald]

Just to create some controversy, the separation of
data and indexes I believe now to be a mistake.  More
correctly, you want to separate different *types* of
IO.  In the ol' days, this meant data and indices
because the whole world consisted of index scan, table
access by rowid, table full scan - now its hash join,
merge join, fast scan, fast full scan, max/min scan
blah blah blah...so the old argument is a lot more
complex nowadays.

Cheers
Connor

 --- Janet Linsy <[EMAIL PROTECTED]> wrote: > Hi
all,
> 
> I got bunch of indice reside in data tablespace. 
> What's an easy, safy way to move them into index
> tablespace?
> 
> Thank you.
> 
> Janet
> 
> __
> Do You Yahoo!?
> Get email alerts & NEW webcam video instant
> messaging with Yahoo! Messenger
> http://im.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Janet Linsy
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

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

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


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

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

Re: Last time an Index was used..

[Charlie Mengler]

Under 9i

MONITORING USAGE | NOMONITORING USAGE 

Use this clause to begin or end the collection of statistics on index
usage. This clause is useful in determining whether an index is being
used. 

Specify MONITORING USAGE to begin statistics collection. Oracle first
clears existing statistics on index and then begins to collect
statistics on index usage.
Statistics collection continues until a subsequent ALTER INDEX ...
NOMONITORING USAGE statement is executed. 

To terminate collection of statistics on index, specify NOMONITORING
USAGE. 

Connor McDonald wrote:
> 
> Not really - but you could move it into its own
> tablespace and then monitor reads/write on this tspace
> using v$filestat.
> 
> hth
> connor
> 
>  --- Veronica Levin <[EMAIL PROTECTED]> wrote: >
> Hi listers,
> > Is there a chance I could find somehow the last time
> > (date) an index was
> > used
> > Any help will be appreciated!
> >
> > Saludos,
> > Veronica Levin Enriquez
> > Administrador AIX
> > Compañía Cervecera de Nicaragua
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Veronica Levin
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX:
> > (858) 538-5051
> > San Diego, California-- Public Internet
> > access / Mailing Lists
> >
> 
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
> 
> "Some days you're the pigeon, some days you're the statue"
> 
> 
> Do You Yahoo!?
> Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
> or your free @yahoo.ie address at http://mail.yahoo.ie
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Charlie Mengler  Maintenance Warehouse  
[EMAIL PROTECTED] 10641 Scripps Summit Ct.
858-831-2229 San Diego, CA 92131
Data-free analysis results in a success-free history.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: [EMAIL PROTECTED]

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

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

RE: Roles and Responsibilties

[Boivin, Patrice J]

Does anyone know if Dilbert has Oracle-related comics, or database admin
related ones?

Just curious.

Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Acting Head
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]  


-Original Message-
From:   Eric D. Pierce [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, September 06, 2001 4:59 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Roles and Responsibilties


http://www.dilbert.com/comics/dilbert/archive/images/dilbert2001203080906.gi
f

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

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

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

RE: Interesting News..

[Eric D. Pierce]

According to our hardware resalescritter: especially for anything 
below large enterprise level, both HP and Compaq services suck.

so, I guess the objective is to create a new company who services 
suck even more than either individually?

Synergy!!!



> Guy Hammond wrote:
> >
> > On the contrary, I'd say this is a godsend for Sun. Compaq made a mess
> > of truly epic proportions when they tried to integrate DEC and Tandem
> > into their operations. HP did just as badly with Apollo. 

...



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

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

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

RE: collections/PLSQL tables

[Jamadagni, Rajendra]

Lisa,

you are right ... on both counts ... but look at it this way ... if you use
the LIMIT clause, when you go for fetch next set of data, you have to figure
out which rows to skip, so there you have more logic to work with. 

As for the amount of memory, you are right too, but then again it matters a
lot how you design the pl/sql table. Also if one is worried about space
utilization, look at PRAGMA SEREALLY_REUSABLE. This pragma can be
incorporated into package spec, so once package execution is complete,  the
data space is released to SGA immediately (more details are in the manual),
it is reallocated when package is invoked. Previously pl/sql table allocated
memory was wither help or if you specify FUUM
(DBMS_SESSION.FREE_UNUSED_USER_MEMORY) the memory was released BUT only for
other pl/sql table use until your session disconnects.

Raj
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art ! 

*1

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

*1

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

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

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

RE: ms acces

[Cale, Rick T (Richard)]

Supposely Oracle Migration Workbench will do this. You can get it from OTN.
See http://technet.oracle.com/software/tech/migration/workbench/content.html

There is also a tool with sql server client DTS that does a great job of
this.

Rick


-Original Message-
Sent: Thursday, September 06, 2001 3:59 PM
To: Multiple recipients of list ORACLE-L


is there any tool that imports directly form ms acces to oracle? 
with out having to export ms acces files to flat text files and then 
load them in to oracle but some sort  of direct importation? cheers

The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
Please do not re-send by any reazon in any way or form any of the 
informatino here contained. 

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

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

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

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

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

Fast Full Index Scan in 8.1.7

[Cherie_Machler]

I had been using the FAST_FULL_SCAN_ENABLED initialization parameter in an
8.0.4 database.

Apparently this parameter is now obsolete in 8.1.7.2.   Seems as though I
am getting the fast full index
scans automatically now.  I'm not sure if I want them in all cases.   Is
there a way to turn them off?

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network

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

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

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

question about nologging

[Gene Gurevich]

Hi,

I'm running the following command:

insert into owner1.table1 nologging (select * from
owner2.table2);

There is no indices or constraints on table
owner1.table1, yet oracle is generating redo logs for
this transaction. Does anyone know why?

thanks

=


__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gene Gurevich
  INET: [EMAIL PROTECTED]

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

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

RE: Last time an Index was used..

[Weaver, Walt]

You mean the last time the index was accessed by a query or the last time
the index was modified?

You probably mean the first one, but just in case you mean the second one
here's a query I use for tables. It can be modified for indexes by changing
type# to 1, I believe.

--Walt Weaver
  Bozeman, Montana, USA

==

set lines 150
 
select  substr(o.NAME,1,20) "Table Name",
substr(u.name,1,12) "Owner",
to_char(o.CTIME,'DD-MON- HH:MI:SS') "Created",
to_char(o.MTIME,'DD-MON- HH:MI:SS') "Modified"
from sys.obj$ o, sys.user$ u
where o.name = upper('&table_name')
and o.type# = 2
and o.owner# = u.user#
and u.name = upper('&table_owner')
/

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


Hi listers, 
Is there a chance I could find somehow the last time (date) an index was
used
Any help will be appreciated!

Saludos, 
Veronica Levin Enriquez
Administrador AIX
Compañía Cervecera de Nicaragua

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

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

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

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

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

Re: reason behind Oracle and HP love affair

[Jonathan Lewis]

An interesting feature of HP which may make 
them less desirable to Oracle -Unlike other
machines, the HP chips have no atomic
'compare and swap' call, and this could
make a big difference to efficiency in Oracle 9,
especially in the area of shared latches

Look out for contention on the CAS Latch,
used to emulate this function in software
on the HP port.


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

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.



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

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

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

ms acces

[agc]

is there any tool that imports directly form ms acces to oracle? 
with out having to export ms acces files to flat text files and then 
load them in to oracle but some sort  of direct importation? cheers

The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
Please do not re-send by any reazon in any way or form any of the 
informatino here contained. 

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

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

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

RE: Scripts for Rebuilding Indexes Nightly on Solaris

[Christopher Spence]

One thing to remember is b-tree indexes settle at 75% efficiency very
quickly and sit there.  You can never keep a b-tree index at 100%
efficiency.

But if your db is small enough to rebuild indexes nightly, well I guess you
can.

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

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

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 



-Original Message-
Sent: Thursday, September 06, 2001 3:25 PM
To: Multiple recipients of list ORACLE-L


First off, checking on a regular basis for indexes that need rebuilding is
fine, but don't rebuild all of them every night. Not only is it unnecessary,
it can cause lots of problems. Only rebuild the indexes you need to--the
ones who are significantly browned, have a significant percentage of
deletes, or whose height is significant.

Second, setting up a cron job is indeed a good idea. And David is right that
you want to make sure to explicitly set up your environment, but not for the
reason he suggests. The problem isn't that the job runs as root. If it were
the case that every cron job on a UNIX box ran as root, then that would be a
mighty big security hole. The issue is that when running a cron job, the
account under which it runs does not automatically get its environment
initialized by running .profile as it does when you log in. Thus, the
environment must be specifically laid out.

Hope this helps.

Jon Walthour


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



David -

Use cron.  Nightly might be excessive.  Kick off a shell script (remember
that cron executes as root, so you need to set your environment) and create
your own script dynamically (following code is use for backups also) in a
manner like:

rebuild_index.sh

#!/bin/ksh

export PATH=$PATH:/u001/app/oracle/product/8.1.7/bin
export ORACLE_SID=ifas
export PATH=$PATH:/$ORACLE_HOME/bin
export ORACLE_HOME=/u001/app/oracle/product/8.1.7
export ORACLE_BASE=/u001/app/oracle

sqlplus < >  @/usr/local/bin/rebuild_index

exit

rebuild_index.sql

spool rebuild_index.sql
Select 'Alter index ' || index_name || ' rebuild;' from dba_indexes; #
add selection caveats if desired
spool off
!chmod 777 /u014/oradata/ifastrn/rebuild_index.sql
@/u014/oradata/ifastrn/rebuild_index

exit

Lots of variations possible.




David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002


 

Kimberly Smith

<[EMAIL PROTECTED]   To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>  
jitsu.com>   cc:

Sent by: Subject: RE: Scripts
for Rebuilding Indexes Nightly on Solaris   
[EMAIL PROTECTED]

 

 

09/06/2001 11:57 AM

Please respond to

ORACLE-L

 

 





Nightly?  That is a lot.  Are you really entering that much data on a daily
basis?
 -Original Message-
 From: David Wagoner [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, September 06, 2001 8:57 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Scripts for Rebuilding Indexes Nightly on Solaris

 Does anyone have any good scripts for rebuilding indexes nightly on
 Solaris UNIX that they'd be willing to share?  Also, in your
 experience is it better to run this through UNIX cron jobs than using
 the Oracle OEM job scheduler?  I suspect the cron job will be the
 favorable answer.


 Thanks in advance,


 David Wagoner
 Oracle DBA



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

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

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

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

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

RE: Roles and Responsibilties

[Eric D. Pierce]

http://www.dilbert.com/comics/dilbert/archive/images/dilbert2001203080906.gif

> --
> 
>  From: "Rachel Carmichael" <[EMAIL PROTECTED]>
>  Date: Wed, 05 Sep 2001 12:27:53 +
>  Subject: RE: Roles and Responsibilties

[via ORACLE-L Digest -- Volume 2001, Number 249]

> 
> join the club. I've figured out that I either have NO boss, or else
> everyone is my boss.

...

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

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

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

Re: Last time an Index was used..

[Connor McDonald]

Not really - but you could move it into its own
tablespace and then monitor reads/write on this tspace
using v$filestat.

hth
connor

 --- Veronica Levin <[EMAIL PROTECTED]> wrote: >
Hi listers, 
> Is there a chance I could find somehow the last time
> (date) an index was
> used
> Any help will be appreciated!
> 
> Saludos, 
> Veronica Levin Enriquez
> Administrador AIX
> Compañía Cervecera de Nicaragua
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Veronica Levin
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

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

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


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

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

RE: collections/PLSQL tables

[Koivu, Lisa]

Title: RE: collections/PLSQL tables





Raj, 


Well, you can limit the number of rows BULK COLLECT will return with the LIMIT keyword.  Wouldn't that take care of the performance problem you refer to? And isn't it affected by the amount of free memory on the host?  For insteance,  there's very little "free" memory and you bulk collect 1000 rows and the host starts paging.  

Any comments?  


Lisa Koivu
I LOVE MY JOB I LOVE MY JOB I LOVE MY JOB
Fairfield Resorts, Inc.
954-935-4117



-Original Message-
From:   Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, September 06, 2001 12:43 PM
To: Multiple recipients of list ORACLE-L
Subject:    RE: collections/PLSQL tables


Bill,


If you are or will be retrieving more than say 1 rows, I'd say do not
use BULK COLLECT (performance goes down)... instead use a pl/sql table of
records. But I'd say even with the inconvenience of specifying a separate
collection for each column, use for BULK BINDS for INSERT/UPDATE/DELETE is
worth the effort, as it really speeds up the processing. 


Oracle recommends that for faster performance, instead of using a TABLE of
RECORDS, use a RECORD of TABLES. The speed benefit is due to the way data
elements storage technique implemented for these two types.


According to rumors, 9i is supposed to offer more, but I haven't got time
right now to test it out.


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.


QOTD: Any clod can have facts, but having an opinion is an art !


*2


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

*2


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


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

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

Re: tool to dump out space info

[Rachel Carmichael]

me please... anything I can automate that I don't have to write is a GOOD 
thing :)


>From: "JOE TESTA" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: tool to dump out space info
>Date: Thu, 06 Sep 2001 08:50:54 -0800
>
>Amazingly, something i wrote a year ago is coming in handy now.
>
>Here is what it does:
>
>1.  grab the total allocated, calculate free space for each datafile.
>2.  grab each table and index(both partitioned and not) and calculate the 
>space allocated AND the space free within the extents.
>3.  dump all that info to CSV, so damagers can put it into spreadsheet and 
>do graphs(oh joy).
>
>anyways if anyone wants it, feel free to ask and the whole thing is yours.
>
>its unix oriented so if you run on windoze, be sure to change / forward 
>slash to \ backward slash.
>
>Unix forward thinking
>windoze backward thinking,
>
>coincidence, i think not.
>
>joe


_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

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

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

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

RE: Scripts for Rebuilding Indexes Nightly on Solaris

[Walthour, Jon (GEAE, Compaq)]

First off, checking on a regular basis for indexes that need rebuilding is
fine, but don't rebuild all of them every night. Not only is it unnecessary,
it can cause lots of problems. Only rebuild the indexes you need to--the
ones who are significantly browned, have a significant percentage of
deletes, or whose height is significant.

Second, setting up a cron job is indeed a good idea. And David is right that
you want to make sure to explicitly set up your environment, but not for the
reason he suggests. The problem isn't that the job runs as root. If it were
the case that every cron job on a UNIX box ran as root, then that would be a
mighty big security hole. The issue is that when running a cron job, the
account under which it runs does not automatically get its environment
initialized by running .profile as it does when you log in. Thus, the
environment must be specifically laid out.

Hope this helps.

Jon Walthour


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



David -

Use cron.  Nightly might be excessive.  Kick off a shell script (remember
that cron executes as root, so you need to set your environment) and create
your own script dynamically (following code is use for backups also) in a
manner like:

rebuild_index.sh

#!/bin/ksh

export PATH=$PATH:/u001/app/oracle/product/8.1.7/bin
export ORACLE_SID=ifas
export PATH=$PATH:/$ORACLE_HOME/bin
export ORACLE_HOME=/u001/app/oracle/product/8.1.7
export ORACLE_BASE=/u001/app/oracle

sqlplus < >  @/usr/local/bin/rebuild_index

exit

rebuild_index.sql

spool rebuild_index.sql
Select 'Alter index ' || index_name || ' rebuild;' from dba_indexes; #
add selection caveats if desired
spool off
!chmod 777 /u014/oradata/ifastrn/rebuild_index.sql
@/u014/oradata/ifastrn/rebuild_index

exit

Lots of variations possible.




David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002


 

Kimberly Smith

<[EMAIL PROTECTED]   To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>  
jitsu.com>   cc:

Sent by: Subject: RE: Scripts
for Rebuilding Indexes Nightly on Solaris   
[EMAIL PROTECTED]

 

 

09/06/2001 11:57 AM

Please respond to

ORACLE-L

 

 





Nightly?  That is a lot.  Are you really entering that much data on a daily
basis?
 -Original Message-
 From: David Wagoner [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, September 06, 2001 8:57 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Scripts for Rebuilding Indexes Nightly on Solaris

 Does anyone have any good scripts for rebuilding indexes nightly on
 Solaris UNIX that they'd be willing to share?  Also, in your
 experience is it better to run this through UNIX cron jobs than using
 the Oracle OEM job scheduler?  I suspect the cron job will be the
 favorable answer.


 Thanks in advance,


 David Wagoner
 Oracle DBA



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

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

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

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

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

Re: reason behind Oracle and HP love affair

[Brian McGraw]

Actually, I've heard it was because HP's Carly was throwing free machines at
Oracle (and Amazon, BTW) to get them to switch.

Brian

"Boivin, Patrice J" wrote:

> I think I now know why...
>
> http://www.boston.com/dailyglobe2/247/nation/Hewlett_Packard_to_buy_Compaq+.
> shtml
>  .shtml>
>
> 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] 
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Boivin, Patrice J
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

--
--
| Brian McGraw -- Oracle DBA |
| Central Alabama Oracle Users Group |
||
| mailto:[EMAIL PROTECTED]  |
| http://bmcgraw.home.mindspring.com |
--


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

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

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

RE: Kind of cool feature! DBMS_RANDOM

[Christopher Spence]

Title: Message



Yeah, 
on the production side I am as well.  But I am building a rather large demo 
enviroment with millions of rows in many different tables, and use this to 
start building some cool scripts for testing.
"Do not criticize someone until you walked a 
mile in their shoes, that way when you criticize them, you are a mile a way and 
have their shoes."
Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:    (707) 885-2275 
Fuelspot 73 Princeton Street North, Chelmsford 01863   

  
  -Original Message-From: Anjan Thakuria 
  [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 06, 2001 
  12:36 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: Kind of cool feature! DBMS_RANDOMJack, Chris, 
  Thanks. Don't get play with these a whole lot being on the production side. 
  Both of your experiences with this sounds very exciting. 
  Need to do some research. 
  Thanks again. 
  Anjan 
  "Jack C. Applewhite" wrote: 
   Anjan,Look in Oracle_Home/RDBMS/admin 
for the dbmsrand.sql file.  The package is better documented there, at 
least for the 8.1.6 release.I used it last year to generate 
a Session ID for a Web-accessible application to maintain state across a 
User's multiple page hits.Jack Jack C. 
Applewhite Database Administrator/Developer 
OCP Oracle8 DBA iNetProfit, 
Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 

  -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of 
  Anjan Thakuria Sent: Wednesday, September 05, 2001 5:26 PM 
  To: Multiple recipients of list 
  ORACLE-L Subject: 
  Re: Kind of cool feature! DBMS_RANDOM  Hi 
  Chris, 
  Pl excuse me for writing directly. I looked up this package and there 
  is no mention of the STRING option at all. Metalink search returned no 
  hits. Could you please send me the location where you got  the 
  information from. 
  Thanks in advance 
  Anjan 
  Christopher Spence wrote: 
    
In 8.1.6 Oracle added a new feature 
which I don't believe is very well documented, it is 
great. 
DBMS_RANDOM.STRING([OPT], 
[LEN]); 
This will create a random string with 
a length of up to 60 characters.  Great for force populating 
tables. The opt is for 
things like L, U, M (Lower, Upper, Mixed case), there are a few 
different options. 
One thing I did to force populate a 
table is:   DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(5, 
10)); 
Which creates random string with 
random length between 5 and 10 characters.  Run 10,000,000 times, I 
got a table with 30 columns and 10,000,000 random rows in no 
time. 
"Do not criticize someone until you 
walked a mile in their shoes, that way when you criticize them, you are 
a mile a way and have their shoes." 
Christopher R. Spence 
Oracle DBA 
Phone: (978) 
322-5744 Fax:    (707) 885-2275 
Fuelspot 
73 Princeton 
Street North, 
Chelmsford 01863   
 

Last time an Index was used..

[Veronica Levin]

Hi listers, 
Is there a chance I could find somehow the last time (date) an index was
used
Any help will be appreciated!

Saludos, 
Veronica Levin Enriquez
Administrador AIX
Compañía Cervecera de Nicaragua

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

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

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

Re: index in data tablespace ???

[Scott Shafer]

alter index  rebuild tablespace ;

--Scott


Janet Linsy wrote:
> 
> Hi all,
> 
> I got bunch of indice reside in data tablespace.
> What's an easy, safy way to move them into index
> tablespace?
> 
> Thank you.
> 
> Janet

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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

Re: index in data tablespace ???

[Bill Gentry]

Just do an 'alter index rebuild ...' to the proper index tablespace.
---Bill
Bill Gentry
DBA
Allina Health System
Minneapolis, MN 55403
612-775-1190
[EMAIL PROTECTED]
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 06, 2001 1:36 PM


> Hi all,
>
> I got bunch of indice reside in data tablespace.
> What's an easy, safy way to move them into index
> tablespace?
>
> Thank you.
>
> Janet
>
> __
> Do You Yahoo!?
> Get email alerts & NEW webcam video instant messaging with Yahoo!
Messenger
> http://im.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Janet Linsy
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

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

RE: Scripts for Rebuilding Indexes Nightly on Solaris

[David Wagoner]

Thanks for the tips and scripts!


David Wagoner
Oracle DBA




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


David -

Use cron.  Nightly might be excessive.  Kick off a shell script (remember
that cron executes as root, so you need to set your environment) and create
your own script dynamically (following code is use for backups also) in a
manner like:

rebuild_index.sh

#!/bin/ksh

export PATH=$PATH:/u001/app/oracle/product/8.1.7/bin
export ORACLE_SID=ifas
export PATH=$PATH:/$ORACLE_HOME/bin
export ORACLE_HOME=/u001/app/oracle/product/8.1.7
export ORACLE_BASE=/u001/app/oracle

sqlplus < >  @/usr/local/bin/rebuild_index

exit

rebuild_index.sql

spool rebuild_index.sql
Select 'Alter index ' || index_name || ' rebuild;' from dba_indexes; #
add selection caveats if desired
spool off
!chmod 777 /u014/oradata/ifastrn/rebuild_index.sql
@/u014/oradata/ifastrn/rebuild_index

exit

Lots of variations possible.




David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002


 

Kimberly Smith

<[EMAIL PROTECTED]   To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]> 
jitsu.com>   cc:

Sent by: Subject: RE: Scripts
for Rebuilding Indexes Nightly on Solaris  
[EMAIL PROTECTED]

 

 

09/06/2001 11:57 AM

Please respond to

ORACLE-L

 

 





Nightly?  That is a lot.  Are you really entering that much data on a daily
basis?
 -Original Message-
 From: David Wagoner [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, September 06, 2001 8:57 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Scripts for Rebuilding Indexes Nightly on Solaris

 Does anyone have any good scripts for rebuilding indexes nightly on
 Solaris UNIX that they'd be willing to share?  Also, in your
 experience is it better to run this through UNIX cron jobs than using
 the Oracle OEM job scheduler?  I suspect the cron job will be the
 favorable answer.


 Thanks in advance,


 David Wagoner
 Oracle DBA



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

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

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

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

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

RE: index in data tablespace ???

[Gary Weber]

Drop tablespace. 

No, seriously, "alter index 'bad index' rebuild index_tbs;"

Gary Weber
Senior DBA
Charles Jones, LLC
609-530-1144, ext 5529

-Original Message-
Sent: Thursday, September 06, 2001 2:37 PM
To: Multiple recipients of list ORACLE-L


Hi all,

I got bunch of indice reside in data tablespace. 
What's an easy, safy way to move them into index
tablespace?

Thank you.

Janet

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Janet Linsy
  INET: [EMAIL PROTECTED]

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

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

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

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

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

Re: ORACLE-L Digest -- Volume 2001, Number 248

[michael emilfarb]

We are currently running Oracle release 8.1.6.0 on AIX. We want to
upgrade to 8.1.7.2.1 .  Do we have to apply 8.1.7.1 first or it can be
skipped?


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

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

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

RE: outer join problem

[Koivu, Lisa]

Title: RE: outer join problem





Try inline views.   They will solve your problem.


Lisa Koivu
"I can't believe I work here."
Fairfield Resorts, Inc.
954-935-4117



-Original Message-
From:   Harvinder Singh [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, September 06, 2001 1:27 PM
To: Multiple recipients of list ORACLE-L
Subject:    outer join problem


Hi,


In one of our queries a table is participating in 3 outer joins ..but oracle
only allows one outer join per table
how can we achieve to have more than 1 outer join for a particular table :


for example like :


t_recur.id_prop = t_pl_map.id_pi_template(+) and
  t_discount.id_prop = t_pl_map.id_pi_template(+) and
  t_aggregate.id_prop = t_pl_map.id_pi_template(+) and



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


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

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

Re: index in data tablespace ???

[Kevin Kostyszyn]

alter index  rebuild tablespace .  I believe
that's the right code, someone will correct me if I am wrong.
KK
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, September 06, 2001 2:36 PM


> Hi all,
>
> I got bunch of indice reside in data tablespace.
> What's an easy, safy way to move them into index
> tablespace?
>
> Thank you.
>
> Janet
>
> __
> Do You Yahoo!?
> Get email alerts & NEW webcam video instant messaging with Yahoo!
Messenger
> http://im.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Janet Linsy
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

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

RE: index in data tablespace ???

[Cale, Rick T (Richard)]

You can use   ALTER INDEX  REBUILD TABLESPACE ;

You can create these statements for you such as

SPOOL rebuild_index.sql
SELECT ' ALTER INDEX ' ||index_name || ' REBUILD TABLESPACE ;'
FROM DBA_INDEXES
WHERE tablespace_name = 'indexes_in_data_tablespace_to_move';
spool off

substitute the  with destination tablespace
Rick

-Original Message-
Sent: Thursday, September 06, 2001 2:37 PM
To: Multiple recipients of list ORACLE-L


Hi all,

I got bunch of indice reside in data tablespace. 
What's an easy, safy way to move them into index
tablespace?

Thank you.

Janet

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Janet Linsy
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: what is wrong in query

[Harvinder Singh]

if  need to support both sqlserver and oracle this syntax is daily
routine...

enjoy
-H 

-Original Message-
Sent: Thursday, September 06, 2001 2:16 PM
To: Multiple recipients of list ORACLE-L


Well, I don't believe this:
{oj t_po LEFT OUTER JOIN t_ep_po on t_ep_po.id_prop = t_po.id_po}

is valid in Oracle, unless there has been some drastic change I wasn't 
informed of.

At 09:46 AM 9/6/01 -0800, you wrote:
>Hi,
>
>I am trying to run following query thru ODBC and getting error:
>
>09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
>Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
>relational operator
>Which relational operator it is referring?.
>
> select DISTINCT(t_po.id_po), t_po.id_eff_date,
>t_po.id_avail,  t_po.b_user_subscribe, t_po.b_user_unsubscribe,
> t_base_props.n_name,
>t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
>t_base_props.nm_desc,
> t_base_props.nm_display_name,te.n_begintype as
>te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
> as te_n_beginoffset,te.n_endtype as
>te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
> ta.n_begintype as ta_n_begintype,
>ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
> ta.n_endtype as ta_n_endtype, ta.dt_end as
>ta_dt_end, ta.n_endoffset as ta_n_endoffset,
> decode(sign((select count(id_pi_type) from
>t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
> tb.n_kind = 20 and t_po.id_po =
>t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
> ,t_ep_po.c_ExternalInformationURL
>t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
>t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
>t_ep__c_InternalInformationURL
> from t_av_internal tav,t_effectivedate
>te,t_effectivedate ta,t_base_props,t_pricelist,
> t_base_props
>template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
>dual) cdate,
> t_pl_map,t_recur,t_discount,t_aggregate
> where
> {oj t_po LEFT OUTER JOIN t_ep_po on
>t_ep_po.id_prop = t_po.id_po},
> t_recur.id_prop =
t_pl_map.id_pi_template(+)
>and
> t_discount.id_prop =
t_pl_map.id_pi_template
>and
> t_aggregate.id_prop =
>t_pl_map.id_pi_template and
> t_pl_map.id_po = t_po.id_po AND
>t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
> tav.id_acc =  136 AND
> t_pricelist.id_pricelist =
>t_pl_map.id_pricelist   AND tav.c_currency = t_pricelist.nm_currency_code
>AND
> te.id_eff_date = t_po.id_eff_date AND
> ta.id_eff_date = t_po.id_avail AND
> t_base_props.id_prop = t_po.id_po AND
> template_base.id_prop =
>t_pl_map.id_pi_template AND
> t_po.id_po not in
> (select id_po from t_sub,t_effectivedate
>tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
> (tesub.dt_end is NULL AND tesub.dt_start
<=
>cdate.now))
> AND
> ((ta.dt_start <= cdate.now or ta.dt_start
is
>null) AND (cdate.now <= ta.dt_end or ta.dt_end is null)) AND
> t_acc_usage_cycle.id_acc = 136 AND
> t_usage_cycle.id_usage_cycle =
>t_acc_usage_cycle.id_usage_cycle AND
> (t_recur.id_cycle_type is null or
>t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> (t_discount.id_cycle_type is null or
>t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> (t_aggregate.id_cycle_type is null or
>t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> te.n_begintype <> 0 AND ta.n_begintype <>
0
>
>Thanks
>-Harvinder
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a li

index in data tablespace ???

[Janet Linsy]

Hi all,

I got bunch of indice reside in data tablespace. 
What's an easy, safy way to move them into index
tablespace?

Thank you.

Janet

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Janet Linsy
  INET: [EMAIL PROTECTED]

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

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

RE: what is wrong in query

[Harvinder Singh]

we can use this kind of left outer join syntax thru odbc...our other queries
works well with this syntax..


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


I haven't seen "LEFT OUTER JOIN" as a valid oracle operator  maybe the
tool you are using isn't aware of Oracle ?? Also the outer join condition is
in curly braces ... I don't think that is legal in oracle as well.

Maybe the stuff in curly braces if left as an exercise for the developer by
the toll you are using. The easiest way to find exact location of error is
run it in SQLPLUS ... it will show you where the error is.

Good luck
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art !

*1

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

*1

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

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

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

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

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

RE: converting case in sqlserver to oracle

[schmoldt]

I believe Oracle 9i supports the CASE syntax.  Otherwise, you're probably
looking at using some sort of DECODE conbination.

Or, I've found a lot of SQL Server queries are overly complex because SQL
Server didn't support user-defined functions.  I was able to simplify a lot
of queries when moving them to Oracle by defining a few functions to replace
parts of the query.

Dave

> -Original Message-
> From: Harvinder Singh [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 04, 2001 12:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: converting case in sqlserver to oracle
> 
> 
> Hi,
> 
> I have a procedure in sql server containg following case 
> statement...(part
> of select clause)
> how to convert this statement is oracle format:
> select au.id,
> viewID = case when au.id_pi_instance is NULL then id_view else 
>   (select viewID = case when pi_props.n_kind = 15 AND
> child_kind.nm_productview = ed.nm_enum_data then
>   -(au.id_pi_instance + 0x4000)
>   else
>   -au.id_pi_instance 
>   end)
>   end,au.instance,
> 
> 
> Thanks
> -Harvinder
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

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

RE: Scripts for Rebuilding Indexes Nightly on Solaris

[DBarbour]

David -

Use cron.  Nightly might be excessive.  Kick off a shell script (remember
that cron executes as root, so you need to set your environment) and create
your own script dynamically (following code is use for backups also) in a
manner like:

rebuild_index.sh

#!/bin/ksh

export PATH=$PATH:/u001/app/oracle/product/8.1.7/bin
export ORACLE_SID=ifas
export PATH=$PATH:/$ORACLE_HOME/bin
export ORACLE_HOME=/u001/app/oracle/product/8.1.7
export ORACLE_BASE=/u001/app/oracle

sqlplus < >  @/usr/local/bin/rebuild_index

exit

rebuild_index.sql

spool rebuild_index.sql
Select 'Alter index ' || index_name || ' rebuild;' from dba_indexes; #
add selection caveats if desired
spool off
!chmod 777 /u014/oradata/ifastrn/rebuild_index.sql
@/u014/oradata/ifastrn/rebuild_index

exit

Lots of variations possible.




David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002


   
   
Kimberly Smith 
   
<[EMAIL PROTECTED]   To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
jitsu.com>   cc:   
   
Sent by: Subject: RE: Scripts for 
Rebuilding Indexes Nightly on Solaris   
[EMAIL PROTECTED]   
   
   
   
   
   
09/06/2001 11:57 AM
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   




Nightly?  That is a lot.  Are you really entering that much data on a daily
basis?
 -Original Message-
 From: David Wagoner [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, September 06, 2001 8:57 AM
 To: Multiple recipients of list ORACLE-L
 Subject: Scripts for Rebuilding Indexes Nightly on Solaris

 Does anyone have any good scripts for rebuilding indexes nightly on
 Solaris UNIX that they'd be willing to share?  Also, in your
 experience is it better to run this through UNIX cron jobs than using
 the Oracle OEM job scheduler?  I suspect the cron job will be the
 favorable answer.


 Thanks in advance,


 David Wagoner
 Oracle DBA



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

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

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

RE: outer join problem

[Mercadante, Thomas F]

Harvinder,

in this particular case, I don't see the need to perform multiple outer
joins.

why not change it to:

t_recur.id_prop = t_pl_map.id_pi_template(+) and
t_discount.id_prop = t_recur.id_prop and
t_aggregate.id_prop = t_recur.id_prop and

This allows the outer join to the t_pl_map table, but hard-joins the other
tables together.

Would this work?


Tom Mercadante
Oracle Certified Professional


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


Hi,

In one of our queries a table is participating in 3 outer joins ..but oracle
only allows one outer join per table
how can we achieve to have more than 1 outer join for a particular table :

for example like :

t_recur.id_prop = t_pl_map.id_pi_template(+) and
  t_discount.id_prop = t_pl_map.id_pi_template(+) and
  t_aggregate.id_prop = t_pl_map.id_pi_template(+) and


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

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

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

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

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

Re: what is wrong in query

[Regina Harter]

Well, I don't believe this:
{oj t_po LEFT OUTER JOIN t_ep_po on t_ep_po.id_prop = t_po.id_po}

is valid in Oracle, unless there has been some drastic change I wasn't 
informed of.

At 09:46 AM 9/6/01 -0800, you wrote:
>Hi,
>
>I am trying to run following query thru ODBC and getting error:
>
>09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
>Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
>relational operator
>Which relational operator it is referring?.
>
> select DISTINCT(t_po.id_po), t_po.id_eff_date,
>t_po.id_avail,  t_po.b_user_subscribe, t_po.b_user_unsubscribe,
> t_base_props.n_name,
>t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
>t_base_props.nm_desc,
> t_base_props.nm_display_name,te.n_begintype as
>te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
> as te_n_beginoffset,te.n_endtype as
>te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
> ta.n_begintype as ta_n_begintype,
>ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
> ta.n_endtype as ta_n_endtype, ta.dt_end as
>ta_dt_end, ta.n_endoffset as ta_n_endoffset,
> decode(sign((select count(id_pi_type) from
>t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
> tb.n_kind = 20 and t_po.id_po =
>t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
> ,t_ep_po.c_ExternalInformationURL
>t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
>t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
>t_ep__c_InternalInformationURL
> from t_av_internal tav,t_effectivedate
>te,t_effectivedate ta,t_base_props,t_pricelist,
> t_base_props
>template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
>dual) cdate,
> t_pl_map,t_recur,t_discount,t_aggregate
> where
> {oj t_po LEFT OUTER JOIN t_ep_po on
>t_ep_po.id_prop = t_po.id_po},
> t_recur.id_prop = t_pl_map.id_pi_template(+)
>and
> t_discount.id_prop = t_pl_map.id_pi_template
>and
> t_aggregate.id_prop =
>t_pl_map.id_pi_template and
> t_pl_map.id_po = t_po.id_po AND
>t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
> tav.id_acc =  136 AND
> t_pricelist.id_pricelist =
>t_pl_map.id_pricelist   AND tav.c_currency = t_pricelist.nm_currency_code
>AND
> te.id_eff_date = t_po.id_eff_date AND
> ta.id_eff_date = t_po.id_avail AND
> t_base_props.id_prop = t_po.id_po AND
> template_base.id_prop =
>t_pl_map.id_pi_template AND
> t_po.id_po not in
> (select id_po from t_sub,t_effectivedate
>tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
> (tesub.dt_end is NULL AND tesub.dt_start <=
>cdate.now))
> AND
> ((ta.dt_start <= cdate.now or ta.dt_start is
>null) AND (cdate.now <= ta.dt_end or ta.dt_end is null)) AND
> t_acc_usage_cycle.id_acc = 136 AND
> t_usage_cycle.id_usage_cycle =
>t_acc_usage_cycle.id_usage_cycle AND
> (t_recur.id_cycle_type is null or
>t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> (t_discount.id_cycle_type is null or
>t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> (t_aggregate.id_cycle_type is null or
>t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
> te.n_begintype <> 0 AND ta.n_begintype <> 0
>
>Thanks
>-Harvinder
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L 

internal error 729

[Jeffrey Beckstrom]

COmpiling invalid objects in 11.5.3.  Getting following - used to 
work!!!  In the trace file al I see is the following over and 
over
 
Exception in getMemberoracle.aurora.classverifier.VerifierException: 
Class is invalid
 
  at 
oracle.aurora.vm.ByteCodeOptimizer.lookupBCO(ByteCodeOptimizer.java)
 
  at 
oracle.aurora.vm.ByteCodeOptimizer.getMember(ByteCodeOptimizer.java)
 
  at 
oracle.aurora.vm.ByteCodeOptimizer.optimizeInvokeVirtual(ByteCodeOptimizer.java)
 
  at 
oracle.aurora.vm.ByteCodeOptimizer.optimize(ByteCodeOptimizer.java)
 
  at 
oracle.aurora.rdbms.ResolutionDriver$ResolutionState.verificationStatus(ResolutionDriver.java)
 
  at 
oracle.aurora.rdbms.ResolutionDriver.verificationStatus(ResolutionDriver.java)
 
  at 
oracle.aurora.rdbms.Compiler.verificationStatus(Compiler.java)
 
Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 
781-4204

Re: what is wrong in query

[Jan Pruner]

Oh my god, man!?!
Did you write this chaos yourself??

Try this:
Oracle's JDBC drivers do not support outer join syntax: {oj outer-join}. The 
workaround is to use Oracle outer join syntax: 

Instead of:
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
 ("SELECT ename, dname 
   FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno} 
   ORDER BY ename");

 

Use Oracle SQL syntax:
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
 ("SELECT ename, dname 
   FROM emp a, dept b WHERE a.deptno = b.deptno(+)
   ORDER BY ename");




Jan Pruner  


Dne ?t  6. zá?í 2001 19:46 jste napsal(a):
> Hi,
>
> I am trying to run following query thru ODBC and getting error:
>
> 09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
> Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
> relational operator
> Which relational operator it is referring?.
>
>   select DISTINCT(t_po.id_po), t_po.id_eff_date,
> t_po.id_avail,t_po.b_user_subscribe, t_po.b_user_unsubscribe,
>   t_base_props.n_name,
> t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
> t_base_props.nm_desc,
>   t_base_props.nm_display_name,te.n_begintype as
> te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset
>   as te_n_beginoffset,te.n_endtype as
> te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
>   ta.n_begintype as ta_n_begintype,
> ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
>   ta.n_endtype as ta_n_endtype, ta.dt_end as
> ta_dt_end, ta.n_endoffset as ta_n_endoffset,
>   decode(sign((select count(id_pi_type) from
> t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND
>   tb.n_kind = 20 and t_po.id_po =
> t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
>   ,t_ep_po.c_ExternalInformationURL
> t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
> t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
> t_ep__c_InternalInformationURL
>   from t_av_internal tav,t_effectivedate
> te,t_effectivedate ta,t_base_props,t_pricelist,
>   t_base_props
> template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
> dual) cdate,
>   t_pl_map,t_recur,t_discount,t_aggregate
>   where
>   {oj t_po LEFT OUTER JOIN t_ep_po on
> t_ep_po.id_prop = t_po.id_po},
>   t_recur.id_prop = t_pl_map.id_pi_template(+)
> and
>   t_discount.id_prop = t_pl_map.id_pi_template
> and
>   t_aggregate.id_prop =
> t_pl_map.id_pi_template and
>   t_pl_map.id_po = t_po.id_po AND
> t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
>   tav.id_acc =  136 AND
>   t_pricelist.id_pricelist =
> t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code
> AND
>   te.id_eff_date = t_po.id_eff_date AND
>   ta.id_eff_date = t_po.id_avail AND
>   t_base_props.id_prop = t_po.id_po AND
>   template_base.id_prop =
> t_pl_map.id_pi_template AND
>   t_po.id_po not in
>   (select id_po from t_sub,t_effectivedate
> tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
>   (tesub.dt_end is NULL AND tesub.dt_start <=
> cdate.now))
>   AND
>   ((ta.dt_start <= cdate.now or ta.dt_start is
> null) AND (cdate.now <= ta.dt_end or ta.dt_end is null)) AND
>   t_acc_usage_cycle.id_acc = 136 AND
>   t_usage_cycle.id_usage_cycle =
> t_acc_usage_cycle.id_usage_cycle AND
>   (t_recur.id_cycle_type is null or
> t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
>   (t_discount.id_cycle_type is null or
> t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
>   (t_aggregate.id_cycle_type is null or
> t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
>   te.n_begintype <> 0 AND ta.n_begintype <> 0
>
> Thanks
> -Harvinder
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jan Pruner
  INET: [EMAIL PROTECTED]

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

RE: OT: Oracle DBA With Internet Experience Needed in Harrisburg,

[Norrell, Brian]

Don't remember the name of the place, but I think it went out of business
(go figure).

The other interesting one was a place that had "Bladder Buster Wednesday".
Doors opened at 6 pm.  $1 to get in.  Free beer until the someone opened the
restroom door.

Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600


-Original Message-
Sent: Thursday, September 06, 2001 11:36 AM
To: Multiple recipients of list ORACLE-L
Harrisburg,


Actually an nickel is 5 cents! Where did you go to get Nickel beers? The
cheapest I got them in college was a dime!

-Original Message-
Sent: Thursday, September 06, 2001 12:02 PM
To: Multiple recipients of list ORACLE-L
Harrisburg,


you must be kidding !! A nickel is 10 cents isn't it ?? I think the next
time I am in the States I need to look out for some of these things.

Lee


-Original Message-
Sent: 06 September 2001 15:56
To: Multiple recipients of list ORACLE-L
Harrisburg,


Absolutely, but what happened to Nickel Beer Night that I harken back to
fondly?

Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600


-Original Message-
Sent: Thursday, September 06, 2001 7:36 AM
To: Multiple recipients of list ORACLE-L
Harrisburg,


Considering that Bozeman is a college town, a quarter beer probably means
$0.25 drafts during happy hour on a particular day.  Quarter meaning quarter
dollar.

Rodd Holman 

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 9/6/01 6:35 AM
Harrisburg,

Maybe "a quart of beer", not a ¼ of beer.

It's that mediaeval measurement system that only one country in the
world
still uses...

: )

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]  



-Original Message-
From:   Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, September 06, 2001 5:40 AM
To: Multiple recipients of list ORACLE-L
Subject:RE: OT: Oracle DBA With Internet Experience
Needed
in Harrisburg,

Forgive me but what is a "quarter beer". Surely its better to
have a
whole
one !! :-)


-Original Message-
Sent: 05 September 2001 14:47
To: Multiple recipients of list ORACLE-L
Harrisburg, PA


Fishing, Hunting, skiing, no traffic and quarter beers
Sounds
too good
to be true.

Scott

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
Scott
> Sent: Tuesday, September 04, 2001 7:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: OT: Oracle DBA With Internet Experience Needed in
> Harrisburg, PA
>
>
> Walt and all may like Bozeman but beware, Bozeman is a
> beautiful city but if you are a big city person
> Bozeman can get boring. If you like Fishing, the
> Gallatin River has some of the best trout fishing
> anywhere. If you like skiing you have Bridger just up
> the road from Bozeman and Bigsky is about 45 miles
> away. I have also found some homes in Bozeman very
> expensive for that area. I looked at 3 houses that
> where all over 500,000 kinda expensive for my blood.
> If you like sub sandwiches you can always eat at the
> pickle barrel they have some of the best sub
> sandwiches around. I spent most of time in the Bars in
> Bozeman. I spent many a Friday in the Rockin-R
> drinking quarter beers. If you like the outdoors,
> peace and quite and no traffic and you like to drink
> Bozeman is great place.
>
> Scott
>
>
> --- "Weaver, Walt" <[EMAIL PROTECTED]> wrote:
> > That's not unusual if you were on Forest Service
> > land. Did you cross a
> > cattle guard some time before meeting the cows?
> >
> > --Walt Weaver
> >   Bozeman, Montana
> >
> > -Original Message-
> > Sent: Tuesday, September 04, 2001 3:01 PM
> > To: Multiple recipients of list ORACLE-L
> > PA
> >
> >
> > Actually, this reminds me of something.  Where I
> > come from
> > I have never seen a cow not behind a fence but I was
> > driving up
> > to Mt. Adams one day (well actually it was the trip
> > back) and
> > I found myself stopping for cows in the road.  They
> > don't bother
> > fencing them.  Strangest thing I had seen since
> > moving out
> > he

RE: ADMIN: It has been decided...

[schmoldt]

We're trying to keep this easy for the list admins.  No subscriptions, no
invoices, etc.

Really, we're talking about pocket change here.  Just eat the cost.  Or
create your own official-looking invoice in a word processor.  :-)

Dave

> -Original Message-
> From: Gene Sais [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 31, 2001 2:07 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ADMIN: It has been decided...
> 
> 
> Bruce - Can you send an invoice to the list that one can 
> submit to their business?  Nothing fancy, word document would 
> be fine.  The invoice amount can be blank, where we can fill 
> in the amount.  Again, paying the invoice is totally 
> voluntary.  This will make it easier for many people on the 
> list to submit it as an expense.  Companies regularly pay for 
> training, books, oug fees, etc.  A technical list service fee 
> would not be a problem at all.  Heck you may even earn a 
> profit :).  This list is far too valuable to not be 
> supported!  Thank you for hosting this list.
> 
> Gene
> 
> >>> [EMAIL PROTECTED] 08/31/01 01:18PM >>>
> Due to the overwhelming generosity of the ORACLE-L list 
> members, I have
> decided to take the plunge and move up to the big leagues. 
> :-)  The response
> really was amazing, and again I thank each and every one of 
> you, whether you
> offered to donate funds or merely gave a word of 
> encouragement.  Everything
> was appreciated.
> 
> I've tried to make it easy for everyone to contribute.  I've 
> set up a web
> page where you can go to find out your options for sending 
> money.  I suspect
> the majority of you will use PayPal, so I've set up a direct 
> link to PayPal
> on that page.  If you are making a one-time donation, just 
> click on the
> leftmost PayPal logo.  If you've said you'll donate for 
> several months,
> enter the amount you want to donate each month and click on 
> the rightmost
> PayPal logo.  International members can use PayPal also (I 
> think there are
> like 35 countries that PayPal supports).  If you aren't a 
> PayPal member,
> I've also included a link where you can sign up (it's free).  
> The web page
> includes options for postal mailing, anonymous donations, etc.  Go to:
> 
> http://www.fatcity.com/ListGuru/Donate 
> 
> If you have any questions, problems or concerns, please contact me at
> [EMAIL PROTECTED] 
> 
> Again, I'll be getting back to all of you as I can.  If 
> you've asked me a
> specific question, I will respond shortly.
> 
> Thanks everyone!
> bruce
> [EMAIL PROTECTED] 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> -- 
> Author: Bruce Bergman
>   INET: [EMAIL PROTECTED] 
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Gene Sais
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

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

Re: outer join problem

[Regina Harter]

Why are you trying to join three different tables to values which may not 
exists?  Are those three tables related to each other outside of what you 
show here?  If not, and this statement worked, you would end up with a 
matrix join between those three tables.  Is that what you are looking for?

At 09:26 AM 9/6/01 -0800, you wrote:
>Hi,
>
>In one of our queries a table is participating in 3 outer joins ..but oracle
>only allows one outer join per table
>how can we achieve to have more than 1 outer join for a particular table :
>
>for example like :
>
>t_recur.id_prop = t_pl_map.id_pi_template(+) and
>   t_discount.id_prop = t_pl_map.id_pi_template(+) and
>   t_aggregate.id_prop = t_pl_map.id_pi_template(+) and
>
>
>Thanks
>-Harvinder
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).

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

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

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

RE: what is wrong in query

[Jamadagni, Rajendra]

I haven't seen "LEFT OUTER JOIN" as a valid oracle operator  maybe the
tool you are using isn't aware of Oracle ?? Also the outer join condition is
in curly braces ... I don't think that is legal in oracle as well.

Maybe the stuff in curly braces if left as an exercise for the developer by
the toll you are using. The easiest way to find exact location of error is
run it in SQLPLUS ... it will show you where the error is.

Good luck
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art !

*1

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

*1

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

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

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

RE: Please tell me how to know whether or not a table is locked

[Mohammad Rafiq]

Try this script also:

column object_name justify c heading "Object|Name"  format a32
column usernamejustify c heading "User|Name"format a7
column osuser  justify c heading "OS|User"  format a7
column pid justify c heading "Ora|Proc|ID"  format 999
column serial# justify c heading "Ora|Serial|#" format 99
column sid justify c heading "Holding|Session"  format 999
column spidjustify c heading "Unix|Proc"format a5
column object_id   justify c heading "Obj|ID"   format 9
column lockwaitjustify c heading "Lock|Wait"
column typejustify c heading "Lock|Type"format a4
column lmode   justify c heading "Mode" format 

set pagesize 60 linesize 100

select lck.sid, ses.serial#, pro.pid, pro.spid, obj.object_name,
   obj.object_id, ses.username, ses.osuser,
   lck.type, lck.lmode
from dba_blockers blk, dba_objects obj, v$lock lck,
 v$session ses, v$process pro
-- where blk.holding_session = ses.sid
where blk.holding_session = ses.sid
and lck.id1   = obj.object_id
-- and   lck.sid   = ses.sid
-- and   ses.paddr = pro.addr
and   ses.username is not NULL
and ses.paddr = pro.addr
and   lck.sid   = ses.sid
and blk.holding_session = ses.sid
order by obj.object_name, ses.lockwait desc, lck.sid;
clear columns

Regards


MOHAMMAD RAFIQ



Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 06 Sep 2001 07:01:13 -0800

Hi,
select dbo.owner||'.'||dbo.object_name object,
 dbo.Object_type
from v$locked_object lo,
  dba_objects  dbo
where lo.object_id = dbo.object_id
/


   - Original Message -
   From: Ha Duy Thien
   To: Multiple recipients of list ORACLE-L
   Sent: Thursday, September 06, 2001 5:00 AM
   Subject: Please tell me how to know whether or not a table is locked


   Hi Gurus

   Please let me know which view can show me a table that is being locked.



   Thanks a lot


_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

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

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

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

Re: patch question

[JOE TESTA]

Joan, check out the release notes for aix patches, if i remember 
correctly:
 
8.1.7.0 -> 8.1.7.1
8.1.7.0 -> 8.1.7.2
 
8.1.7.1 -> 8.1.7.0 -> 8.1.7.2
 
to go from 8171 to 8172, i think you have to reinstall 8.1.7.0, run all 
scripts, then apply patch and run scripts again.
 
joe
>>> [EMAIL PROTECTED] 09/06/01 01:32PM 
>>>Hi Listers,I am asking a simple question today. We have 
8.1.6 production databaseand want to upgrade to 8.1.7.2 on AIX 
R/6000.1. I installed 8.1.7 on production already. Can I install 8.1.7.2 
patchdirect on top of 8.1.7 or have to go 8.1.7.1 first?2. Installed 
patch first, then upgrade database later or upgradedatabase from 8.1.6 to 
8.1.7 then installed patch later?Thanks,Joan-- Please 
see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Joan 
Hsieh  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- (858) 538-5051  FAX: (858) 538-5051San 
Diego, California    -- Public Internet 
access / Mailing 
ListsTo 
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:OT - un-tar patchset

[dgoulet]

Barb,

On HP-UX it's:

$ tar -xvf 8172_solaris_32_patchset.tar

It should create the 8.1.7.2 directory for you.

Reply Separator
Author: "Baker; Barbara" <[EMAIL PROTECTED]>
Date:   9/6/2001 8:50 AM

Sorry -- I'm new to the unix world . . .
I'm attempting to untar the 8.1.7.2 patchset.  I do not have space to place
the patchset and the resultant files on the same mountpoint.  I'm trying to
place the files in the patches/8.1.7.2 directory.  

I ftp'd the zip file (p1882450_8172_SOLARIS.zip) which I unzipped, which
gave me a tarfile. I've attempted this:

  $ tar xvf 8172_solaris_32_patchset.tar -C
/oracle/app/oracle/product/8.1.7/patches/8.1.7.2

which does nothing.  (comes back to the $ prompt in a few seconds, doesn't
give me squat)

Any help for a poor, wayward non-unixer??
Thanks for any help!

Barb
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: object_type = 'UNDEFINED' ??

[Yadav, Shailesh]

Jerry,

  We also encountered the UNDEFINED objects. On investigation it was found
to be summary object created when you create snapshots. This according to
the metablink was a problem in 8.1.6 on solaris 2.6 and thats what we have
here.
  The quick check for this was querying sys.obj$ for the object_id of one of
these object and if type# = 42 then its the aforementioned problem. 

HTH
Shailesh

-Original Message-
Sent: Wednesday, September 05, 2001 12:52 PM
To: Multiple recipients of list ORACLE-L


Hi there.

What the heck is an object_type of undefined? When I select * from
dba_objects where status = 'INVALID' , I get a bunch of objects with an
object_type of UNDEFINED.

Thanks for any insight!

- Jerry


_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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

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

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

Re: outer join problem

[Jan Pruner]

May be???:

select xyz from t1 a, t1 b, t1 c, t2 d, t3 e, t4 f where
d.id_prop = a.id_pi_template(+) and
  e.id_prop = b.id_pi_template(+) and
   f.id_prop = c.id_pi_template(+) and
  a.rowid = b.rowid and a.row_id = c.rowid and ...

Jan Pruner

Dne ?t  6. zá?í 2001 19:26 jste napsal(a):
> Hi,
>
> In one of our queries a table is participating in 3 outer joins ..but
> oracle only allows one outer join per table
> how can we achieve to have more than 1 outer join for a particular table :
>
> for example like :
>
> t_recur.id_prop = t_pl_map.id_pi_template(+) and
>   t_discount.id_prop = t_pl_map.id_pi_template(+) and
>   t_aggregate.id_prop = t_pl_map.id_pi_template(+) and
>
>
> Thanks
> -Harvinder
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jan Pruner
  INET: [EMAIL PROTECTED]

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

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

what is wrong in query

[Harvinder Singh]

Hi,

I am trying to run following query thru ODBC and getting error:

09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error
Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid
relational operator
Which relational operator it is referring?.

select DISTINCT(t_po.id_po), t_po.id_eff_date,
t_po.id_avail,  t_po.b_user_subscribe, t_po.b_user_unsubscribe,
t_base_props.n_name,
t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name,
t_base_props.nm_desc,
t_base_props.nm_display_name,te.n_begintype as
te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset 
as te_n_beginoffset,te.n_endtype as
te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset,
ta.n_begintype as ta_n_begintype,
ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset,
ta.n_endtype as ta_n_endtype, ta.dt_end as
ta_dt_end, ta.n_endoffset as ta_n_endoffset,
decode(sign((select count(id_pi_type) from
t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND 
tb.n_kind = 20 and t_po.id_po =
t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge
,t_ep_po.c_ExternalInformationURL
t_ep__c_ExternalInformationURL,t_ep_po.c_glcode
t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL
t_ep__c_InternalInformationURL
from t_av_internal tav,t_effectivedate
te,t_effectivedate ta,t_base_props,t_pricelist,
t_base_props
template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from
dual) cdate,
t_pl_map,t_recur,t_discount,t_aggregate
where
{oj t_po LEFT OUTER JOIN t_ep_po on
t_ep_po.id_prop = t_po.id_po},
t_recur.id_prop = t_pl_map.id_pi_template(+)
and
t_discount.id_prop = t_pl_map.id_pi_template
and
t_aggregate.id_prop =
t_pl_map.id_pi_template and
t_pl_map.id_po = t_po.id_po AND
t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND
tav.id_acc =  136 AND
t_pricelist.id_pricelist =
t_pl_map.id_pricelist   AND tav.c_currency = t_pricelist.nm_currency_code
AND
te.id_eff_date = t_po.id_eff_date AND
ta.id_eff_date = t_po.id_avail AND
t_base_props.id_prop = t_po.id_po AND
template_base.id_prop =
t_pl_map.id_pi_template AND
t_po.id_po not in 
(select id_po from t_sub,t_effectivedate
tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND
(tesub.dt_end is NULL AND tesub.dt_start <=
cdate.now))
AND
((ta.dt_start <= cdate.now or ta.dt_start is
null) AND (cdate.now <= ta.dt_end or ta.dt_end is null)) AND
t_acc_usage_cycle.id_acc = 136 AND
t_usage_cycle.id_usage_cycle =
t_acc_usage_cycle.id_usage_cycle AND
(t_recur.id_cycle_type is null or
t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_discount.id_cycle_type is null or
t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND
(t_aggregate.id_cycle_type is null or
t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND
te.n_begintype <> 0 AND ta.n_begintype <> 0

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

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

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

objects in a wrong place ???

[Andrea Oracle]

Hi all, 

I did a full database import.  After the import, the
tables went to the correct schema owner, but
procedures went to owner SYSTEM !!! How does this
happen, and how to move these to the correct schema?

Thank you!

Andrea

__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrea Oracle
  INET: [EMAIL PROTECTED]

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

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

Re: Advice required : Executing application PL/SQL when database is s

[Charlie Mengler]

Invoke a startup trigger on 8i & above

John Dunn wrote:
> 
> We have a requirement to run some of the functionality of our application
> automatically when the database starts up. Basically to run some PL/SQL.
> 
> What is the best approach to this?
> 
> T.I.A.
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: John Dunn
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Charlie Mengler  Maintenance Warehouse  
[EMAIL PROTECTED] 10641 Scripps Summit Ct.
858-831-2229 San Diego, CA 92131
Data-free analysis results in a success-free history.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: [EMAIL PROTECTED]

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

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

Re:

[tday6]

SET DIGEST-L ORACLE?


   

Scott Shafer   

   <[EMAIL PROTECTED]>

Sent by: cc:   

root@fatcity.Subject: Re:  

com

   

   

09/06/2001 

11:01 AM   

Please 

respond to 

ORACLE-L   

   

   





NO.

[EMAIL PROTECTED] wrote:
>
> SET ORACLE-L DIGEST

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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


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

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

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

Re: OT - un-tar patchset

[Scott Shafer]

'man tar' at the unix command line should help.
try putting the tar file on a different mount point and using:

'tar -xvf 8172_solaris_32_patchset.tar
/oracle/app/oracle/product/8.1.7/patches/8.1.7.2'

(without the quotes, of course).

HTH,

Scott Shafer
San Antonio, TX


"Baker, Barbara" wrote:
> 
> Sorry -- I'm new to the unix world . . .
> I'm attempting to untar the 8.1.7.2 patchset.  I do not have space to place
> the patchset and the resultant files on the same mountpoint.  I'm trying to
> place the files in the patches/8.1.7.2 directory.
> 
> I ftp'd the zip file (p1882450_8172_SOLARIS.zip) which I unzipped, which
> gave me a tarfile. I've attempted this:
> 
>   $ tar xvf 8172_solaris_32_patchset.tar -C
> /oracle/app/oracle/product/8.1.7/patches/8.1.7.2
> 
> which does nothing.  (comes back to the $ prompt in a few seconds, doesn't
> give me squat)
> 
> Any help for a poor, wayward non-unixer??
> Thanks for any help!
> 
> Barb

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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

Re: Kind of cool feature! DBMS_RANDOM

[Ron Rogers]

List,
There is a short article about DBMS_RANDOM in the Sept/Oct issue of ORACLE magazine. 
It  is a quick explaination on the usage and a method to get the text usage. 
www.oracle.com/oramag/ for the article.
Basically as SQL> select text from all_source where name = "DBMS_RANDOM" and type = 
"PACKAGE" order by line;
HTH
ROR mª¿ªm

>>> [EMAIL PROTECTED] 09/06/01 12:35PM >>>
Jack, Chris,

Thanks. Don't get play with these a whole lot being on the production
side. Both of your experiences with this sounds very exciting.

Need to do some research.

Thanks again.

Anjan

"Jack C. Applewhite" wrote:

>  Anjan,Look in Oracle_Home/RDBMS/admin for the dbmsrand.sql file.  The
> package is better documented there, at least for the 8.1.6 release.I
> used it last year to generate a Session ID for a Web-accessible
> application to maintain state across a User's multiple page
> hits.Jack
> Jack C. Applewhite
> Database Administrator/Developer
> OCP Oracle8 DBA
> iNetProfit, Inc.
> Austin, Texas
> www.iNetProfit.com 
> [EMAIL PROTECTED] 
> (512)327-9068
>
>  -Original Message-
>  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
>  Anjan Thakuria
>  Sent: Wednesday, September 05, 2001 5:26 PM
>  To: Multiple recipients of list ORACLE-L
>  Subject: Re: Kind of cool feature! DBMS_RANDOM
>
>  Hi Chris,
>
>  Pl excuse me for writing directly. I looked up this package
>  and there is no mention of the STRING option at all.
>  Metalink search returned no hits. Could you please send me
>  the location where you got  the information from.
>
>  Thanks in advance
>
>  Anjan
>
>  Christopher Spence wrote:
>
> >
> >
> > In 8.1.6 Oracle added a new feature which I don't believe
> > is very well documented, it is great.
> >
> > DBMS_RANDOM.STRING([OPT], [LEN]);
> >
> > This will create a random string with a length of up to 60
> > characters.  Great for force populating tables.
> > The opt is for things like L, U, M (Lower, Upper, Mixed
> > case), there are a few different options.
> >
> > One thing I did to force populate a table is:
> > DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(5, 10));
> >
> > Which creates random string with random length between 5
> > and 10 characters.  Run 10,000,000 times, I got a table
> > with 30 columns and 10,000,000 random rows in no time.
> >
> > "Do not criticize someone until you walked a mile in their
> > shoes, that way when you criticize them, you are a mile a
> > way and have their shoes."
> >
> > Christopher R. Spence
> > Oracle DBA
> > Phone: (978) 322-5744
> > Fax:(707) 885-2275
> >
> > Fuelspot
> > 73 Princeton Street
> > North, Chelmsford 01863
> >
> >
>

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

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

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

patch question

[Joan Hsieh]

Hi Listers,

I am asking a simple question today. We have 8.1.6 production database
and want to upgrade to 8.1.7.2 on AIX R/6000.

1. I installed 8.1.7 on production already. Can I install 8.1.7.2 patch
direct on top of 8.1.7 or have to go 8.1.7.1 first?

2. Installed patch first, then upgrade database later or upgrade
database from 8.1.6 to 8.1.7 then installed patch later?

Thanks,

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

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

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

Re:

[Scott Shafer]

OK, but only for you.


[EMAIL PROTECTED] wrote:
> 
> SET DIGEST-L ORACLE?
> 
> 
Scott Shafer  
> NO.
> 
> [EMAIL PROTECTED] wrote:
> >
> > SET ORACLE-L DIGEST

_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

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

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

RE: Scripts for Rebuilding Indexes Nightly on Solaris

[Sherman, Edward]

Try the script at the bottom of the page at:
 
http://www.revealnet.com/newsletter-v2/rebuild.htm
 
 
It will find indexes that need rebuilding and generate a script containing
the 
ALTER INDEX REBUILD statements for those indexes that need rebuilding.
 
You can read the article while waiting for the script to complete ;-)
 
HTH
Ed

-Original Message-
Sent: Thursday, September 06, 2001 12:57 PM
To: Multiple recipients of list ORACLE-L


Nightly?  That is a lot.  Are you really entering that much data on a daily
basis?  

-Original Message-
Sent: Thursday, September 06, 2001 8:57 AM
To: Multiple recipients of list ORACLE-L



Does anyone have any good scripts for rebuilding indexes nightly on Solaris
UNIX that they'd be willing to share?  Also, in your experience is it better
to run this through UNIX cron jobs than using the Oracle OEM job scheduler?
I suspect the cron job will be the favorable answer.

 

 

Thanks in advance,

 

 

David Wagoner

Oracle DBA



* * * * * Freedom of Information Act Notice * * * * * 
The information in this email is subject to the record protection mandated
by 5 United States Code 552(b)(4) and relevant judicial opinions. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sherman, Edward
  INET: [EMAIL PROTECTED]

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

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

RE: Test for compatibility with Standard Engine

[Vadim Gorbounov]

Jeram,
What kind of problem with Java on SE? 

Thanx
Vadim

-Original Message-
Sent: Thursday, September 06, 2001 5:21 AM
To: Multiple recipients of list ORACLE-L


If you are using just PL/SQL it is full supported in Standard Engine, but if
u are using Java programming you will get some problems.

Rgds/Jeram
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thursday, September 06, 2001 3:02 PM


>We develop using 8.1.7 Enterprise Edition on AIX, but a customer has only
>8.1.7 Standard Engine.
>
>Is there anyway I can test that my development code(PL/SQL mostly) is
>compatible with Standard Engine?. From the list of functions missing from
>Standard I think I am Ok...but would like to be sure!
>
>John
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: John Dunn
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).

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

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

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

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

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

outer join problem

[Harvinder Singh]

Hi,

In one of our queries a table is participating in 3 outer joins ..but oracle
only allows one outer join per table
how can we achieve to have more than 1 outer join for a particular table :

for example like :

t_recur.id_prop = t_pl_map.id_pi_template(+) and
  t_discount.id_prop = t_pl_map.id_pi_template(+) and
  t_aggregate.id_prop = t_pl_map.id_pi_template(+) and


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

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

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

RE: Kind of cool feature! DBMS_RANDOM

[Jamadagni, Rajendra]

There is an example in Oracle Magazine Sept/Oct 2001, page 40. More info
with example at 
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:123545815
6173

HTH
Raj
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art ! 

*2

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

*2

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

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

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

tool to dump out space info

[JOE TESTA]

Amazingly, something i wrote a year ago is coming in handy now.
 
Here is what it does:
 
1.  grab the total allocated, calculate free space for each 
datafile.
2.  grab each table and index(both partitioned and not) and calculate 
the space allocated AND the space free within the extents.
3.  dump all that info to CSV, so damagers can put it into spreadsheet 
and do graphs(oh joy).
 
anyways if anyone wants it, feel free to ask and the whole thing is 
yours.
 
its unix oriented so if you run on windoze, be sure to change / forward 
slash to \ backward slash.
 
Unix forward thinking
windoze backward thinking, 
 
coincidence, i think not.
 
joe
 

Re: Scripts for Rebuilding Indexes Nightly on Solaris

[Ruth Gramolini]

Just spool the output of this query and run it everynite.

select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes;

HTH,
Ruth

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, September 06, 2001 11:56 AM


> Does anyone have any good scripts for rebuilding indexes nightly on
Solaris
> UNIX that they'd be willing to share?  Also, in your experience is it
better
> to run this through UNIX cron jobs than using the Oracle OEM job
scheduler?
> I suspect the cron job will be the favorable answer.
>
>
> Thanks in advance,
>
>
> David Wagoner
> Oracle DBA
>

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

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

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

RE: Max IO size - File System vs Raw

[Connor McDonald]

Yeh - thats what I suspected - but no-one at Sun seems
to wanna tell me that.  Basically they're telling me,
"we can read up to maxphys - if you're not getting
that, then it must be something non-Sun related"

Sounds like BS to me.

 --- Christopher Spence <[EMAIL PROTECTED]> wrote:
> A lot of file system's have a max io of much lower
> than raw.  In the earlier
> Solaris's it was 64K, but as you see, it is now
> seems to be 256k.  Under NT
> is 128k without using raw.
> 
> To truly take advantage of MULTIBLOCK_READ_COUNT you
> need to use RAW file
> system.  HP_UX I believe can do as high as 1Mb for
> this.
> 
> "Do not criticize someone until you walked a mile in
> their shoes, that way
> when you criticize them, you are a mile a way and
> have their shoes."
> 
> Christopher R. Spence 
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
> 
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>  
> 
> 
> 
> -Original Message-
> Sent: Thursday, September 06, 2001 9:11 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Anyone know of any restrictions on the max physical
> IO
> (under Solaris8) that is imposed by file systems? 
> To illustrate -
> 
> Using a tablespace on a raw device under veritas vol
> mgr
> - maxphys is 512k
> - vxio:vol_maxio is 512k
> - db block size is 8k
> - db multi read is 64
> and as expected, a level 8, 10046 trace shows me
> that
> I can get 512k max io...so far, so good
> 
> If I repeat the exercise after putting a file system
> (vxfs or ufs) on this device, then the best I can
> get
> is 256k.
> 
> Any Solaris internal people out there ?
> 
> Cheers
> Connor
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at 
> http://www.oradba.freeserve.co.uk)
> 
> "Some days you're the pigeon, some days you're the
> statue"
> 
>

> Do You Yahoo!?
> Get your free @yahoo.co.uk address at
> http://mail.yahoo.co.uk or your free
> @yahoo.ie address at http://mail.yahoo.ie
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

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

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

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


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

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

Advice required : Executing application PL/SQL when database is s

[John Dunn]

We have a requirement to run some of the functionality of our application
automatically when the database starts up. Basically to run some PL/SQL.

What is the best approach to this?

T.I.A.


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

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

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