RE: Starting Database from OEM 9.2 gives ORA-12500 Failed to star
-Original Message- Igor Neyman OracleServiceSID starts the database automatically, because by default the registry key ORA_SID_AUTOSTART IN HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 is set to TRUE. If you don't want your OracleServiceSID to start the database automatically, change the value to FALSE. This way service will be still running, but you should be able to startup the database from OEM. Igor Neyman, OCP DBA [EMAIL PROTECTED] Are you sure about that? This is the way I thought it worked with Oracle 8.1 and 9.2 (I just tried it again today using Oracle 8.1.7 on Windows 2000) If in the services control panel the database service has Startup Type Automatic, then the registry entry you mention will have ORA_sid_AUTOSTART TRUE, and when the machine is rebooted, the service will start up AND the instance will be started up. If you want to startup the instance manually on system reboot, you should set the Startup Type for the service to Manual (either in the Services control panel or the registry). Then once the machine is rebooted, you can 1) go to the Services control panel and start the Service, which will start the service and the instance OR 2) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% which will start the service and the instance OR 3) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then oradim -startup -sid %ORACLE_SID% -starttype inst which will start the instance OR 4) At a command prompt type in set ORACLE_SID=instance_name oradim -startup -sid %ORACLE_SID% -starttype srvc which will start the service, then sqlplus /nolog connect sys/password as sysdba startup which will start the instance I personally use method 4. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Bye...
It's the end of an era. How long was the list hosted here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: !!Please Read - Oracle-L is moving!!
It's working for me, but slowly. I tried to do it through the webpage and got the first confirmation e-mail back (containing a code to enter on the webpage.) Then I subscribed to the new list, got a second e-mail back to confirm my subscription, and replied to that. I'm sure more things will show up shortly. One caveat though: the first response was caught by my work's spam filter and flagged as spam. -Original Message- Ron Thomas The list members must be really hammering their servers now. I've tried to sign up using both the web and email methods and have yet to receive a conformation/response. I can see the headlines now, oracle-l slashdots freelists.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: kill session privilage
create procedure kill_your_session (in_sid in sys.v_$session.sid%type, in_serial# in sys.v_$session.serial#%type) as row_count pls_integer ; begin select count (*) into row_count from v$session where username = user and sid = in_sid and serial# = in_serial# ; if row_count 0 then execute immediate 'alter system kill session ''' || to_char (in_sid) || ', ' || to_char (in_serial#) || ; end if ; end ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
why would enable constraint cause a delete to wait on library cache lock?
I am only an egg, so I hope someone else can explain this to me. Oracle Enterprise Edition 8.1.7.4.1 on Windows 2000 I was experimenting to see if an alter table enable validate constraint would cause DML statements to wait. I thought it wouldn't. But in real life I see something different. I have a table X (object_id 429995) with about 100 million rows. In one session (sid 15) I enable a check constraint, and in another session (sid 14) I simultaneously delete a few rows from the table. The delete waits on the enable constraint to complete, and it's waiting on a library cache lock. Why would that be? At the end of this e-mail you can see the contents of DBA_LOCKS, V$LOCKED_OBJECT, V$SESSION_WAIT. I read the system state dump and I still don't understand why there would be a library cache lock. Session ID 15 --- sid-15-SQL1 alter table hes_a_keeper.many_rows add (constraint ck1 check (dummy_column 'B') disable) ; --- sid-15-SQL2 (simultaneous with sid-14-SQL1) alter table hes_a_keeper.many_rows enable validate constraint ck1 ; Session ID 14 --- sid-14-SQL1 (simultaneous with sid-15-SQL2) delete from hes_a_keeper.many_rows where rownum 10 ; Session ID 10 --- statements issued while sid-14-SQL1 and sid-15-SQL2 are running alter session set events 'immediate trace name systemstate level 10' ; select * from dba_locks where session_id in (14,15) ; select * from v$locked_object where object_id = 429995 ; select * from v$session_wait where sid in (14,15) ; SQL select * from dba_locks where session_id in (14,15) ; SESSION_ID LOCK_TYPE MODE_HELD -- -- MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS 15 DMLShare None 429995 0 10 Not Blocking SQL select * from v$locked_object where object_id = 429995 ; XIDUSN XIDSLOTXIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME - - - - -- -- OS_USER_NAME PROCESS LOCKED_MODE -- - --- 0 0 0429995 15 JRK jkilchoe 1540:2604 SQL select * from v$session_wait where sid in (14,15) ; SID SEQ# EVENT - - P1TEXT P1 P1RAW - P2TEXT P2 P2RAW - P3TEXT P3 P3RAW WAIT_TIME - - SECONDS_IN_WAIT STATE --- --- 15 5005 db file scattered read file# 13 000D block# 19516 4C3C blocks 8 0008 0 0 WAITING 14 142 library cache lock handle address47483828 02D48BB4 lock address 40114204 0264181C 10*mode+namespace 21 0015 0 9 WAITING SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
finding the partition for a newly inserted row, from inside a trigger on the table
This is probably old hat for some of you and not very useful to most of you, but maybe
there's that one person who is struggling with this question who will be happy to read
the post.
You have a partitioned table and you want to find out in which partition a newly
inserted or updated row will be placed, because for example you want to prevent
changes in that partition for certain users or whatever.
Here's one way to do it. If there's a better way I'd be glad to hear about it.
(I realize that for list or range partitions one could compare the relevant columns to
the partitioning values but you would have to modify the trigger every time you
add/remove partitions.)
create table t (n number, d date)
partition by hash (n)
(partition tp1, partition tp2) ;
create trigger t_afi
after insert on t
for each row
declare
rid_type number ;
objid number ;
rfno number ;
bno number ;
rno number ;
objname sys.obj$.subname%type ;
begin
dbms_rowid.rowid_info (rowid_in = :new.rowid,
rowid_type = rid_type,
object_number = objid,
relative_fno = rfno,
block_number = bno,
row_number = rno) ;
select subname
into objname
from sys.obj$
where dataobj# = objid ;
dbms_output.put_line ('Row was placed in partition ' || objname) ;
end ;
/
Proof of concept:
SQL set serveroutput on
SQL insert into t (n, d) values (1, sysdate) ;
Row was placed in partition TP2
1 ligne créée.
SQL insert into t (n, d) values (2, sysdate) ;
Row was placed in partition TP1
1 ligne créée.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Delete vs. truncate to free up spaces.
This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete vs. truncate to free up spaces.
Then you should have the partitioning option. Partition your table if you can. -Original Message- Nguyen, David M It's Oracle8i Enterprise Edition. -Original Message- Jacques Kilchoer This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a 96524/c12p arti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
better delete statement to remove duplicate rows from exception table?
In the situation below, is there a better way to write the delete statement that eliminates duplicates? (assuming duplicate rows form at most 5 % of the table rows) Notice that the exceptions table is not analyzed. If I analyze the exceptions table, is there then another better way to write it? create table my_exceptions (row_id urowid, owner varchar2 (30), table_name varchar2 (30), constraint varchar2 (30) ); create table orders (order_id number (8) not null, order_date date, constraint orders_uq1 unique (order_id) disable ) ; /* -- load table orders with millions of rows */ create index orders_idx1 on orders (order_id) ; analyze table orders estimate statistics sample 10 percent ; alter table orders enable constraint orders_uq1 exceptions into my_exceptions ; delete from orders a where a.rowid in (select d.delete_row_id from (select min (b.row_id) over (partition by c.order_id) as keep_row_id, b.row_id as delete_row_id from my_exceptions b, orders c where c.rowid = b.row_id group by c.order_id, b.row_id ) d where d.delete_row_id != d.keep_row_id ) ; commit ; alter table orders enable constraint orders_uq1 ; truncate table my_exceptions ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
wintercorp survey
Can this be right? When I look for Category: Peak Workload Platform: All Usage: OLTP I see 6 SQL server databases and no Oracle databases. -Original Message- MacGregor, Ian A. Subject: http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopTenWinners.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ** find whether table or index being accessed
I was too lazy to look for it on asktom.oracle.com, but here's what I read at the site a while ago (if you search on index usage or something like that you should find Mr. Kyte's answer). Tom Kyte has the following suggestions: a) In Oracle 8.0 and earlier - put an index all by itself in a tablespace, and check reads and writes on the tablespace. If reads are close to writes - index not being used (only read for updates.) If reads much larger than writes - indexes being used. b) In Oracle 9.0 and later - use alter index ... monitoring and check v$object_usage c) In Oracle 8.1 (your case): See Chapter 11 of his book expert one-on-one Oracle - use stored outlines. Use an ON LOGON trigger to enable automatic outline generation (and disable it after a while) - look in user_outline_hints to see if the index is being used. Finally, even though an index is used, that doesn't mean it's necessary. e.g. if you have index IDX1 on MYTABLE (N1, N2) and index IDX2 on MYTABLE (N1, N2, N3) IDX1 may be used by some queries but might not be necessary because the query could use IDX2. -Original Message- I had sent this some time back but got no answer for version 8.1.7. For table I understand auditing is an option. What about for index? Thank You A Joshi [EMAIL PROTECTED] wrote: Is there an easy way to find out if a table or an index is being used. I mean short of going thru all code or keeping looking at v$sqlarea. I mean even if code is covered there are always ad hoc SQL queries etc. Same for other objects like views etc. Is there a place where oracle stores objects accessed and any other related info. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: granting SELECT privilege on SYS.X$ TABLES
Yet another case of the right hand not knowing what the left hand is doing. Now I'm curious to find out which team is creating those x_$ views. -Original Message- Mark Leith Check out Jacques e-mail address - he *works* for Quest ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: granting SELECT privilege on SYS.X$ TABLES
Boy do I feel stupid! Thank you Mr. Khedr and Mr. Breitling. I know the views are there, but at first I thought that maybe there were being created as part of the database creation process in some cases. In the first database I looked at, the X_$ views had the same creation date as the database creation date, but then one of the programmers here might have used a Quest tool on the database on the same day it was created. Now I see that in the other databases that have those views, the views were created later. I'll have to find out which product does that, most of the quest products create objects staring with QUEST (e.g. QUEST_XXX_X$KTFBUE) - actually AFAIK it's the rule for any object created in the database - but it does seem like there's one that doesn't use that naming convention. -Original Message- Wolfgang Breitling Someone must have created sys.x_$ views on some of the sys.x$ tables. Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of the x$ tables as well. I routinely do it for all x$ tables in my test databases and grant select to the select_catalog_role. Then I can access the x$tables without having to log on as sys. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a
user-written PL/SQL function because I wanted to compare timings for both. I created a
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my)
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false,
so all this was just an intellectual exercise.
With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause,
the times were slightly longer but still the same: 14.71 vs. 14.32
VB query:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (SELECT DISTINCT element FROM (
SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
, INSTR(:list, ',', 1, ROWNUM) c
FROM gab
WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
)
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
GROUP BY
usr
, cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
from
(select distinct
b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
) a
where
val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
and cnt = 4
group by
usr, cnt
having
count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
declare
insert_cnt constant pls_integer := 20 ;
commit_cnt constant pls_integer := 2000 ;
i pls_integer ;
j pls_integer ;
k pls_integer ;
l pls_integer ;
n pls_integer ;
usr gab.usr%type ;
type usrt is table of gab.usr%type index by binary_integer ;
usra usrt ;
type valt is table of gab.val%type index by binary_integer ;
vala valt ;
begin
dbms_random.initialize (dbms_utility.get_time) ;
i := 1 ;
while i = insert_cnt
loop
usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
n := mod (abs (dbms_random.random), 5) + 1 ;
j := mod (i - 1, commit_cnt) + 1 ;
k := least (commit_cnt, j + n - 1) ;
for l in j..k
loop
usra (l) := usr ;
vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
end loop ;
i := i + k - j + 1 ;
if k = commit_cnt or i = insert_cnt
then
forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
commit ;
end if ;
end loop ;
commit ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: truncate privilege
Yes. Instead of using a procedure, create a function or a package that does the truncate. Grant execute on the function or package to the user doing the truncate. Without creating a procedure, a function or a package: no. -Original Message- [EMAIL PROTECTED] Is there a way to give truncate privilege on particular table to a particular user? (Without using procedure) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: modeling year and month for summary data
-Original Message- Steiner, Randy I have a table that will contain sales monthly summary. I am not sure which is the better way to handle the date of the data. I can create a year field and a month field or I can create a date field and force in the day e.g. 2003 November's data can be 01-Nov-03 . Is there a censuses on which way to handle the date? A consensus? I think probably not. :) I like the idea that the date datatype performs validation on the values entered. I personally would create a date field with a before insert/update trigger that sets :new.date := trunc (:new.date, 'MM') ; If you need an index on year then you can use a function based index on trunc (date, '') -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: RE: Re: Stop using SYS, SYSTEM?
-Original Message- Nuno Pinto do Souto I don't want to know that SYSTEM or SOUTON with a subset of its rights stuffed up my database or exported my main accounts and clients tables. What I want to know is WHY, WHEN, HOW and by WHOM. What I was saying is that having a different username for each DBA helps you identify the WHOM. Of course a hacker could always cut knock the DBA unconscious and prop up his head to fool an eye retina scan, à la James Bond, but by that argument any username or IP address or whatever else you use is meaningless. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Code compilation and library cache lock issue
-Original Message- As we know if procedure A is currently being executed, one cannot re-compile procedure A. And when one tries to do so, the compilation appears to be _hanging_. Though it time-outs after some time. My question is , is there a way to detect this when I am compiling a procedure? Can this timeout be changed? If so, how? DB is 9202. -- A: Did you know about this ixora QA? How can I tell if a procedure/package is running? http://www.ixora.com.au/q+a/0110/30141015.htm -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
Yes, your query was much better. I keep on forgetting about those analytic functions. Shame on me. I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) -Original Message- Vladimir Begun ... PL/SQL is not needed to solve this task as SQL task. There reason when it would be wise to rewrite it is out of scope of this topic (but the reason is obvious). I'm just thinking that the query proposed by you is a bit expensive. So, I've re-scribbled mine: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (1, 5, 7) AND cnt = 3 GROUP BY usr , cnt HAVING COUNT(*) = cnt / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
-Original Message- Bellow, Bambi Why not do it like this... select usr from gab where val=1 intersect select usr from gab where val=5 intersect select usr from gab where val=7; Because that way you would get the wrong answer. With the sample data as kindly provided by Mr. Begun the correct query would return one row, but your query returns two rows. SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP5 GAP7 PAG1 PAG7 PAG2 JKL1 JKL5 JKL5 GPA1 GPA5 GPA7 GPA8 14 ligne(s) sélectionnée(s). SQL select usr from gab 2 where val=1 3 intersect 4 select usr from gab 5 where val=5 6 intersect 7 select usr from gab 8 where val=7; USR -- GAP GPA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
granting SELECT privilege on SYS.X$ TABLES
I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: granting SELECT privilege on SYS.X$ TABLES
P.S. I forgot to mention that in all the databases (including the 8.1.7 databases) in which I tried this, init parameter O7_DICTIONARY_ACCESSIBILITY was set to FALSE. I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Looking for help - sql*loader and truncate
In that case you could create a procedure owned by the data owner that does the truncate, grant execute on the procedure to the data loader, and use SQL*Plus to call the truncate procedure before the SQL*load starts. -Original Message- Stefan Jahnke .. and there is another scenario to use replace. As you mention it, that's what we do, too ;). The package owner (who owns the transformation packages) also does the load, so we use replace here, since I didn't feel like granting DROP ... to the package user or use the data owner to do the load. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: data modelling question - job vs. job history table
-Original Message- Mercadante, Thomas F Why make it sooo complicated? I like the third table - Job_History that shows what job ran and when. Much clearer now, and in a year from now when you (or someone else) goes and reviews what you did. Because I can? Because when I make something complicated people think I'm smart? :) My first idea was to have a job_history table, but when I was looking at my tables I noticed that the job_schedule table and the job_history table had pretty much identical columns, and I said to myself why have two tables when all the columns are the same? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: data modelling question - job vs. job history table
-Original Message- [EMAIL PROTECTED] What you are doing is not exactly data modeling: you are designing a database. I stand corrected. I am actually looking at a denormalized set of tables we have here (there's a table with 52 columns: OBSERVATION1, OBSERVATION2, ... OBSERVATION52) and playing aroung with a different design. But perhaps I need to step back and look at it from the data model point of view as you suggest. (Don't tell me I have to go in my modelling tool Your suggestions sounded good to me. I didn't think of a CONFIG subtype. I have a tendency to go straight to the physical model anyway. I guess I should look at the Conceptual Data Model option in my design tool. :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: data modelling question - job vs. job history table
Merci beaucoup Monsieur. I agree with your point in the second paragraph. -Original Message- Stephane Faroult My personal preference is with solution 2 - moving the current information to JOB. The scheduler can quietly insert into JOB_HISTORY when it is done with a job, and update the current line (do it through triggers if you like). Solution 3 violates the beloved KISS principle ... Moreover, when you want to do some maintenance operation over the history table (purge, archival, whatever) you are going to interfere with the process which presumably polls the table. My EUR 0.02 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: data modelling question - job vs. job history table
-Original Message- Yechiel Adar I usually use a switch in the scheduled job tables to indicate active status. After the job run jus NULL or put another value in it. If you put bitmap index on this the search will be a snap. Except would you put a bitmap index on a column in a table that gets updated frequently? There is no reason to hold this pointer in the jobs table. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Silly SQL Question
of elements in the list / Depending on the existence of the constraint, here gab$uq, you can either use inline view of run it against original table. Gabriel Aragon wrote: I have a table with like this: Usr val -- GAP 1 GAP 5 GAP 7 JKL 8 JKL 5 I need a query that returns the user (GAP o JKL) that has ALL the values in a list. Example: Having the list: 1,5,7 the result will be GAP, but with the values 1,5 or 1,5,7,8 there will be no result. select distinct usr from xxx where val = All (1,3,5) I was trying the ALL operator but it works with part of the list, I need the user that has (exactly) all the values in the list. Any idea? Maybe it's a simple solution, but after several hours I feel blocked. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
data modelling question - job vs. job history table
Warning - this is a little long. Thank you to those who take the time to read it. I have a data modelling question (the target database will be an Oracle database.) I am keeping track of scheduled jobs run by a job agent. Table 1: JOB with columns JOBNO (primary key), JOBNAME Table 2: JOB_SCHED with columns JOB_SCHED_ID (primary key), JOBNO (foreign key to JOB), JOB_INTERVAL, JOB_START_DATE, JOB_END_DATE, JOB_RESULT Table JOB_SCHED can have: completed jobs: JOB_START_DATE not null and JOB_END_DATE not null scheduled jobs: JOB_START_DATE not null and JOB_END_DATE null unscheduled jobs: JOB_START_DATE null and JOB_END_DATE null The job can be scheduled to run only once: JOB_INTERVAL null or scheduled to run periodically: JOB_INTERVAL not null A user can save an unscheduled job and then schedule it later. As currently designed JOB_SCHED contains job history for past jobs. My background scheduler often looks up jobs to see which jobs should run now. If JOB_SCHED contains the history of all jobs run then I will have to scan through many rows to find out those jobs which should run now. I could do this in several ways: Option 1: put completed jobs in a different table called JOB_HISTORY, and then JOBNO would be UNIQUE in JOB_SCHED, or I could combine the columns in JOB and JOB_SCHED Option 2: select * from JOB a, JOB_SCHED b where a.JOBNO = b.JOBNO and b.JOB_START_DATE is not null and b.JOB_END_DATE is null But I propose option 3: Add to JOBNO a column called CURRENT_JOB_SCHED_ID (foreign key to JOB_SCHED) This should make it faster to find the current schedule for the job. The tables have reciprocal foreign key relationships: JOB_SCHED.JOBNO foreign key references JOB.JOBNO - FK_JOBNO JOB.CURRENT_JOB_SCHED_ID foreign key references JOB_SCHED.JOB_SCHED_ID - FK_JOB_SCHED FK_JOBNO characteristics: ON DELETE CASCADE FK_JOB_SCHED characteristics: DEFERRABLE INITIALLY DEFERRED (you insert into JOB before you insert into JOB_SCHED) On JOB I have a BEFORE INSERT TRIGGER that generates JOBNO and CURRENT_JOB_SCHED_ID based on a sequence On JOB_SCHED I have a BEFORE INSERT TRIGGER that generates JOB_SCHED_ID based on a sequence if JOB_SCHED_ID is null To create a new job: insert into JOB returning the new JOBNO and CURRENT_JOB_SCHED_ID set by trigger -- the insert into JOB will succeed because the FK relationship to JOB_SCHED is a DEFERRABLE FK constraint insert into JOB_SCHED using the schedule ID returned by the above insert commit When a periodic job has completed: update JOB_SCHED set JOB_END_DATE = SYSDATE insert into JOB_SCHED returning the new JOB_SCHED_ID set by trigger, START_DATE = previous START_DATE + INTERVAL update JOB set CURRENT_JOB_SCHED_ID to the schedule ID returned by the above insert commit When a run-once job has completed: update JOB_SCHED set JOB_END_DATE = SYSDATE update JOB set CURRENT_JOB_SCHED_ID to null commit Is there any reason why option 3 should be avoided? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Re: Stop using SYS, SYSTEM?
-Original Message- Nuno Pinto do Souto Fact is: an admin user MUST have access to an admin privileged account. Call it whatever you want, root or role, who cares. In my case I also enforce the don't sign on as SYS/SYSTEM rule. The reasons I do that: - The default tablespace for SYS is SYSTEM, and I don't like to change that. There are probably reasons why you wouldn't want to change that. But when I sign on to do my DBA work to try something I don't want to have to specify a tablespace name every time I create a test object like CREATE TABLE TEST (X NUMBER) STORAGE (INITIAL 1000M) - If each DBA has a named account, it's easy to tell who's logged in to the database by saying SELECT USERNAME FROM V$SESSION ; otherwise I would have to figure out who could be logged on as SYSTEM to call them and ask them if it's OK to shutdown the database. Telling all the DBAs sign on as SYSTEM would be (IMHO) like telling all the programmers You can all sign on as user 'coder' and all users you can all sign on in the database as user 'data_entry_person'. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Plus errors... how to hide?
Catch the error in an exception clause and ignore it.
SQL set serveroutput on
SQL run
1 declare
2 x number ;
3 begin
4 x := to_number ('123^') ;
5 exception
6 when value_error
7 then
8dbms_output.put_line ('Bad Number') ;
9 when others
10 then
11raise ;
12* end ;
Bad Number
Procedura PL/SQL completata correttamente.
-Original Message-
Saira Somani-Mendelin
I have a shell script that executes a sql*plus script (which
executes a
procedure) based on user input.
But what if the user inputs an invalid datatype? The exception section
handles the error and displays a user-friendly message but I still get
an error stack. I want to hide this from the user. How can I
do this? I
have set feedback and echo options off. See output below:
PO Reconciliation Batch Release
Enter batch number to be released:
yrugis
You have entered an invalid number! Exiting program...
**[I want to suppress the errors below]**
BEGIN RELEASE_PO_B_H('yrugis'); END;
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at TRAIN65D.RELEASE_PO_B_H, line 16
ORA-06512: at line 1
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Table partitioning Oracle 9.2
Is the table hash-partitioned, range-partitioned, or list-partitioned? -Original Message- Vikas S RDBMS Version: 9.2.0.1.0 Operating System and Version: Solaris 8 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0 Table partitioning I've a query reg. space usage in context of partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i delete some huge amount of data from P_1 (after archiving it) will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? Data is partitioned by date. So, my query is whether Oracle will put future data (which belongs to partition P_7 etc.) in space earlier used by P_1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Help sorting out SQL statement
Forgive me if you've already considered this option and discarded it, but wouldn't a
group by combined with a max() aggregate function give you what you want?
Example:
SQL select
2 a.owner || '.' || a.table_name || '' as tbl,
3 max (b.partition_name) as last_partition
4 from
5 dba_tables a, dba_tab_partitions b
6 where
7 a.owner = 'SPC_TBL_OWNER'
8 and a.table_name in ('COUNTRY', 'CUST_CATEGORY_TOTAL')
9 and a.owner = b.table_owner
10 and a.table_name = b.table_name
11 group by
12 a.owner, a.table_name ;
TBL LAST_PARTITION
--- --
SPC_TBL_OWNER.COUNTRY COUNTRY_PZ
SPC_TBL_OWNER.CUST_CATEGORY_TOTAL CUST_CAT_2001
SQL
-Original Message-
Gamini Karunaratne
I need help in sorting out a tricky sql statement as follows:
Statement:
SELECT /*+RULE */ distinct rfx.rfx_id , rfx.type_id , supp.supplier_id ,
decode ( nvl(rrfx.state_id, 0) , 1, 'Responded', 3, 'Responded', 6, 'Responded', 7,
'Responded', supp.read_state_id) as full_read_state,
rfx.subject , TO_CHAR(LOG.date_changed, 'DD/MM/YY') , TO_CHAR(LOG.date_changed,
'HH24:MI') ,
rfx.buyer_document_id , rfx.buyer_org_guid , rfx.buyer_org_unit_display_name , NULL ,
supp.supplier_guid , supp.supplier_id ,
(SELECT COUNT(rrfx.rrfx_id) FROM tx_rrfx_document rrfx WHERE rrfx.rfx_id = rfx.rfx_id
AND rrfx.state_id = 3 and rrfx.state_id 999),
STATE.ORDER_ID , LOG.date_changed , (SELECT COUNT(*) FROM TX_RFX_ATTACHMENT WHERE
RFX_ID = rfx.RFX_ID)
FROM tx_rfx_document rfx , tx_rfx_supplier supp , tx_rfx_state_log log ,
TX_RFX_VIEWER_STATES STATE, tx_rrfx_document rrfx
WHERE rfx.rfx_id = supp.rfx_id
AND supp.READ_STATE_ID = STATE.READ_STATE_ID
AND LOG.rfx_id = rfx.rfx_id
AND supp.supplier_id = rrfx.supplier_id (+)
AND log.log_id =(select MAX(log3.log_id) from tx_rfx_state_log log3 where log3.rfx_id
= rfx.rfx_id and LOG3.to_state_id IN (SELECT MAX(log2.to_state_id) FROM
tx_rfx_state_log log2 WHERE log2.rfx_id = rfx.rfx_id AND (log2.to_state_id = 9 OR
log2.to_state_id = 12)))
AND supp.read_state_id 'Deleted'
AND nvl(rrfx.state_id, 0) 999
and rfx.rfx_id = 12619
ORDER BY STATE.ORDER_ID, full_read_state;
The output I get is as follows:
RFX_ID TYPE_ID SUPPLIER_ID FULL_READ_STATE SUBJECT etc etc
12619 11207 Full
homing
12619 1 1205 Normal
homing
12619 1 1209 Normal
hourly
How is if possible for me to change the script so that my output is a unique rfx_id
and only the maximum supplier_id is output (ie supplier_id 1209)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ** database configuration assistant scripts
If you're interested I can send you the sample batch files and scripts I use to create databases on Windows. I have sample scripts for 8.1.7 and 9.2 -Original Message- A Joshi I am using database configuration assistant to create a NT database. Is there a way I can get the scripts used in the creation? I clicked on the template but do not know where the template goes. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Boolean dates...
-Original Message- DENNIS WILLIAMS I think this is very perceptive - Julian vs. Boolean. I just want to mention that what Oracle calls a Julian date is the number of days since Jan 1, 4712 BC. As far as I know, that is exclusive to Oracle. Other systems define Julian differently. I looked it up on Wikipedia and they say that Oracle's definition is the usual definition. ?!? Which surprises me also. If I had an Encyclopedia Britannica account then I could see what their definition is. http://en.wikipedia.org/wiki/Julian_date Julian day (Redirected from Julian date) The term Julian day has different meanings. It is sometimes confused with Julian date, which also has more than one meaning. Just as the Gregorian date is a date in the Gregorian calendar, a Julian date is a date in the Julian calendar. Some people use the term Julian date as synonymous with Julian Day or Julian Day Number. Such use makes it ambigous, for which reason is better to reserve the term Julian date to refer to a date in the Julian calendar. The Julian Day (JD) or Julian Day Number is the time that has elapsed since noon January 1, 4713 BC (according to the proleptic Julian calendar; or November 24, 4714 BC according to the proleptic Gregorian calendar), expressed in days and fractions of a day. The Julian day system was intended to provide a single system of dates that could be used when working with different calendars and to unify different historical chronologies. Given that the Julian Day Number (and modifications of it) has been widely used by astronomers, it is also called Astronomical Julian Day (AJD). The most well known version of the Julian Day is perhaps the Chronological Julian Day (CJD), a modification of the Astronomical Julian Day, in which the starting point is set at midnight January 1, 4713 BC (Julian calendar) rather than noon. Chronographers found the Julian Day concept useful, but they didn't like noon as starting time. So CJD = AJD + 0.5. Note that AJD uses Coordinated Universal Time (UTC), and so it is the same for all time zones and is independent of Daylight-Saving Times (DST). On the other hand, CJD is not, so it changes with different time zones and takes into account the different local DSTs. Because the starting point is so long ago, numbers in the Julian day can be quite large and cumbersome. To make numbers more convenient, a more recent starting point is sometimes used, for instance by dropping the leading digits. For example, the Lilian Day number (LD) counts from October 14, 1582 C.E. in the Gregorian Calendar, which is the date before the day on which the Gregorian calendar was adopted. Where CJD is the Chronological Julian day number: LD = CJD - 2,299,160 = AJD - 2,299,159.5 The Modified Julian Day, introduced by space scientists in the 1950s, is defined in terms of the Julian Day as follows: MJD = AJD - 240.5 The offset of 0.5 means that MJDs start midnight of November 17th, 1858 CE. Modified Julian Days are always based on the Universal Time system, not local time. The Truncated Julian Day (TJD) is obtained by subtracting 2,440,000.5 from the AJD. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Can I concatenate several rows without a procedure?
Yes. -Original Message- Jake Johnson I am trying to concatenate several records with simple sql. Is this possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01401: inserted value too large for column
That's funny. When I tried it on Oracle 9.0.1.1.0 on SunOS there was no error.
When I tried it on Oracle 9.2.0.3.0 on SunOS the value assigned to po_out was an X
followed by two (2) spaces.
-Original Message-
Post, Ethan
Seeing this on AIX 5.1 64 bit Oracle 9.2.0.1.0 with char
datatypes. Anyone
aware of this issue?
CREATE TABLE CHAR_TEST
(
CHAR1 CHAR(1)
)
--
--
--
CREATE OR REPLACE PROCEDURE TEST_CHAR (
po_out OUT CHAR_TEST.CHAR1%TYPE -- this column is defined
as CHAR(1)
)
IS
BEGIN
po_out := 'X';
-- The value assigned to po_out (in Oracle 9i instance) is
an 'X' followed
by 199 spaces.
-- The value assigned to po_out (in Oracle 8i instance) is
an 'X', no
spaces.
dbms_output.put_line('[BEGIN]--' || po_out || '--[END]');
-- In Oracle 9i, the following will cause the exception: ORA-01401:
inserted value too large for column
INSERT INTO CHAR_TEST (CHAR1)
VALUES (po_out);
EXCEPTION WHEN OTHERS
THEN
dbms_output.put_line('Error: ' || SQLERRM);
END;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Analyze - For All option
NO 0 COL:OBJECT_TYPENO 0 COL:LAST_DDL_TIME NO 0 SQL -- +++ SQL -- table, index, indexed columns size 80 SQL analyze table X 2compute statistics 3for table for all indexes 4for all indexed columns size 80 ; SQL @@show_analyze SQL set echo off OBJECT ANALYZED LAST_ANALYZEDHISTOGRAMS -- -- TBL:X YES 2003/10/16 17:42:18 IDX:XI1YES 2003/10/16 17:42:18 IDX:XI2YES 2003/10/16 17:42:18 COL:OBJECT_ID YES 2003/10/16 17:42:18 80 COL:OWNER YES 2003/10/16 17:42:18 6 COL:OBJECT_NAMEYES 2003/10/16 17:42:18 80 COL:OBJECT_TYPENO 0 COL:LAST_DDL_TIME NO 0 SQL set echo off ### ANALYZE_OPTIONS.SQL ### set verify off set feedback off drop table X ; define table_name = X set echo on create table X as select object_id, owner, object_name, object_type, last_ddl_time from all_objects ; create unique index XI1 on X (object_id) ; create index XI2 on X (owner, object_name) ; -- +++ -- default options @@show_analyze analyze table X compute statistics ; @@show_analyze analyze table X delete statistics ; @@show_analyze -- +++ -- table, index, all columns size 1 analyze table X compute statistics for table for all indexes for all columns size 1; @@show_analyze analyze table X delete statistics ; @@show_analyze -- +++ -- table only analyze table X compute statistics for table ; @@show_analyze analyze table X delete statistics ; @@show_analyze -- +++ -- table, index, indexed columns analyze table X compute statistics for table for all indexes for all indexed columns ; @@show_analyze analyze table X delete statistics ; @@show_analyze -- +++ -- table, index, indexed columns size 75 (default) analyze table X compute statistics for table for all indexes for all indexed columns size 75 ; @@show_analyze analyze table X delete statistics ; @@show_analyze -- +++ -- table, index, indexed columns size 80 analyze table X compute statistics for table for all indexes for all indexed columns size 80 ; @@show_analyze set echo off ### SHOW_ANALYZE.SQL ### set echo off column sort_id noprint column analyzed format a8 select '1' as sort_id, 'TBL:' || table_name as object, case when last_analyzed is null then 'NO' else 'YES' end as analyzed, last_analyzed, null as histograms from user_tables where table_name = 'table_name' union select '2' || index_name as sort_id, 'IDX:' || index_name as object, case when last_analyzed is null then 'NO' else 'YES' end as analyzed, last_analyzed, null as histograms from user_indexes where table_owner = user and table_name = 'table_name' union select '3' || to_char (a.column_id) as sort_id, 'COL:' || a.column_name as object, case when a.last_analyzed is null then 'NO' else 'YES' end as analyzed, a.last_analyzed, count (b.endpoint_number) as histograms from user_tab_columns a, user_tab_histograms b where a.table_name = 'table_name' and a.table_name = b.table_name (+) and a.column_name = b.column_name (+) group by a.column_id, a.column_name, a.last_analyzed order by 1 ; clear columns set echo on -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Table Size
How true. My previous query would be incorrect also. ANALYZE the table COMPUTE STATISTICS (or ESTIMATE STATISTICS if it's a very large table) and then select num_rows * avg_row_len from dba_tables where owner = 'table_owner' and table_name = 'table_name' ; -Original Message- Goulet, Dick That gives you the size of the segments/extents that are the table, but not the actual amount of space being used. -Original Message- How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Table Size
The query proposed by Anthony Hsu assumes that the tablespace has an 8K block size.
Instead use the query below. Of course the result will be in bytes, not gigabytes, but
any good DBA can instantly convert bytes to gigabytes or terabytes at a glance. :)
P.S. I include LOB indexes in the table total. Some people might argue that those
should be in the index total, not the table total, but I figure that without the LOB
column those indexes wouldn't be there so they should properly be counted in the table
total.
select sum (x.bytes)
from
(select a.bytes
from dba_segments a
where a.owner = 'table_owner'
and a.segment_name = 'table_name'
and a.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
union all
select d.bytes
from
dba_tab_columns b, dba_lobs c, dba_segments d
where
b.owner = 'table_owner'
and b.table_name = 'table_name'
and b.column_name = c.column_name
and d.owner = c.owner
and d.segment_name = c.segment_name
and d.segment_type in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
union all
select g.bytes
from
dba_tab_columns e, dba_lobs f, dba_segments g
where
e.owner = 'table_owner'
and e.table_name = 'table_name'
and e.column_name = f.column_name
and g.owner = f.owner
and g.segment_name = f.index_name
and g.segment_type in ('LOBINDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
) x ;
-Original Message-
Hsu, Anthony C., ,CPMS
Try:
SELECT segment_type, segment_name,BLOCKS*8192/1024 Kb
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('owner') AND SEGMENT_NAME = UPPER('table_name');
-Original Message-
Could somebody help me in finding the actual size of an oracle table in GB.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Query question
asktom.oracle.com
http://asktom.oracle.com/pls/ask/f?p=4950:8:2542717627406446060::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:124812348063,
or
http://tinyurl.com/r3lk
pivot a result set
-Original Message-
Teresita Castro
I have the next query:
SELECT COMPANY, ITEM,
OEBASE.CUR_PRICE_01
FROM OEBASE
WHERE OEBASE.COMPANY IN (2000,2001,2002) AND OEBASE.BASE_NAME IN ('BASE-OCJ',
'BASE-OSI','BASE-OCR')
This returns for each item three lines
2000 0010041 12.34
2001 0010041 12.74
2002 0010041 11.99
I want one row per item, how can I do that?
0010041 12.3412.74 11.99
Put the price of each company on three different fields
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SUPPRESS SQL STATEMENTS
-Original Message- Johan Muller A script dumps out table info. (sqlplus on aix 4.3.3 and oracle 8.1.7). I cannot suppress the PROMPT@path/scriptname and PROMPT spool off statements from the report output. The script contains both set heading off and set feedback off as part of the formatting. Posssible solutions? I'm not sure if this is answers your question, but is this the output you're looking for? # cat x.ksh sqlplus /nolog @x.sql # cat x.sql set echo off connect username/password spool x.lst select dummy from dual ; spool off exit # x.ksh SQL*Plus: Release 9.2.0.3.0 - Production on Tue Oct 14 11:51:35 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. D - X Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production # cat x.lst D - X # -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: alter session
Well my blood pressure has been raised by alter session set current_schema = ... today. Here are a couple of interesting things I have found about that feature: Case A) If you sign on as userA, set current_schema to userB, then use dbms_sql to 'create table t': in 7.3.4 and 8.0 - table will be created belonging to userA in 8.1 and higher - table will be created belonging to userB Case B) In 8.1.7 If you set current_schema to userA and try to add a constraint to a table belonging to userB, it works if you are signed in as a DBA user, but you get ORA-01031 if you are signed on as SYSDBA. How does that make sense?!?! Case A) sample script connect userA/userA alter session set current_schema = userB ; declare ignore pls_integer ; c_dynsql pls_integer ; begin c_dynsql := dbms_sql.open_cursor ; dbms_sql.parse (c_dynsql, 'create table my_test_table (n number)', dbms_sql.native) ; ignore := dbms_sql.execute (c_dynsql) ; dbms_sql.close_cursor (c_dynsql) ; end ; / select owner from dba_tables where table_name = 'MY_TEST_TABLE' ; -- + -- in 7.3 and 8.0: table owner will be userA -- in 8.1 and higher: table owner will be userB Case B) sample script -- + -- jrk, a, b are all users with DBA role -- + connect jrk/password create table a.t (n1 number, n2 number) ; alter table a.t add (constraint t_uq1 unique (n1)) ; alter session set current_schema = b ; -- statement below: no error when signed on as JRK alter table a.t add (constraint t_uq2 unique (n2)) ; drop table a.t ; -- + connect jrk/password as sysdba create table a.t (n1 number, n2 number) ; alter table a.t add (constraint t_uq1 unique (n1)) ; alter session set current_schema = b ; -- statement below: fails when signed on as SYSDBA alter table a.t add (constraint t_uq2 unique (n2)) ; drop table a.t ; results: SQL -- + SQL -- jrk, a, b are all users with DBA role SQL -- + SQL connect jrk/password Connecté. SQL create table a.t (n1 number, n2 number) ; Table créée. SQL alter table a.t add (constraint t_uq1 unique (n1)) ; Table modifiée. SQL alter session set current_schema = b ; Session modifiée. SQL -- statement below: no error when signed on as JRK SQL alter table a.t add (constraint t_uq2 unique (n2)) ; Table modifiée. SQL drop table a.t ; Table supprimée. SQL -- + SQL connect jrk/password as sysdba Connecté. SQL create table a.t (n1 number, n2 number) ; Table créée. SQL alter table a.t add (constraint t_uq1 unique (n1)) ; Table modifiée. SQL alter session set current_schema = b ; Session modifiée. SQL -- statement below: fails when signed on as SYSDBA SQL alter table a.t add (constraint t_uq2 unique (n2)) ; alter table a.t add (constraint t_uq2 unique (n2)) * ERREUR à la ligne 1 : ORA-01031: privilèges insuffisants SQL drop table a.t ; Table supprimée. -Original Message- Paul Drake it raises the DBA's blood pressure by 50 mm Hg. if found, it prevents the user from having an unlocked account. if found, it is possible that it gets the user a termination notice. you're in the sys schema for what purpose? testing recovery from dictionary corruption? [EMAIL PROTECTED] wrote: List, what does the following do ? alter session set current_schema=sys; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sid/serial# vs. audsid: why both?
Thank you for your answer. My question was more along the lines why doesn't kill session use audsid? Mr. Kangaraj provided the answer that audsid is 0 for some sessions, or, in older databases, when AUDIT_TRAIL is not TRUE. My question would then be: why doesn't auditing use SID/SERIAL# instead of creating a new session identifier? But presumably only an Oracle developer could answer that question. -Original Message- Paul Drake Sent: mardi, 7. octobre 2003 00:54 --- Paul Drake [EMAIL PROTECTED] wrote: --- Jacques Kilchoer [EMAIL PROTECTED] wrote: In what cases does the SERIAL# need to be used? orakill. sqlnet.expire_timeout did not work on NT for 8.1.7. garbage middle-tier apps that don't close connections require the use of orakill. Pd oops. orakill takes the ORACLE_SID and v$session.sid as arguments. alter system kill session takes the sid and serial number as arguments. I'd only use alter system to kill a session if I could not access the server console. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sid/serial# vs. audsid: why both?
From the Fine Manual: Oracle9i Database Performance Tuning Guide and Reference, Release 2 (9.2) Part Number A96533-02 Chapter 24 Dynamic Performance Views for Tuning http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqlviews.htm#32598 V$SESSION . SID: Session identifier, used to join to other columns . SERIAL#: Counter, which is incremented each time a SID is reused by another session (when a session ends and another session starts and uses the same SID) . AUDSID: Auditing session ID uniquely identifies a session over the life of a database. It is also useful when finding the parallel query slaves for a query coordinator (during the PQ execution they have the same AUDSID) Oracle9i Database Reference Release 2 (9.2) Part Number A96536-02 V$SESSION http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3171.htm SERIAL# ... Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. In what cases does the SERIAL# need to be used? Can someone give an example where a session-level command would be applied to an incorrect session object if SERIAL# were not available? Why not use AUDSID all the time? Is there a reason why the database keeps track of two session identifying numbers? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what's the size of a date field?
The size is 7.
avg_row_len includes the row header.
dba_tab_columns.data_length has the correct result 7.
The length function applies to varchar2 fields, so the date is converted to a
character using your default date format, and the length of the string is returned.
length ('06-OCT-03') = 9
-Original Message-
elain he
I'm trying to figure out the size (in bytes) of a DATE
column. Executing the
three queries below returned me three different values. Is
there any way of
finding the size of a date column in the database?
select * from testing;
DOB
-
06-OCT-03
desc testing
Name
Null?Type
--
--
-
DOB
DATE
select avg_row_len from user_tables where table_name='TESTING';
AVG_ROW_LEN
---
11
select data_length from dba_Tab_columns where table_name='TESTING';
DATA_LENGTH
---
7
select length (timestamp) from abc;
LENGTH(TIMESTAMP)
-
9
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: data loading
If you choose to implement this method, make sure that all the tables can be in the exchange partition command. The exchange partition command has certain requirements to be able to complete the exchange (similarity of indexes and constraints), and the table has to be suitable for building a copy as a partitioned table. For example, the method would not work with this table (because of the two unique keys): create table t (a number unique, b number unique) ; If your database version is 8.0 there are even more restrictions on what kind of table you can use in an exchange partition command (IIRC any FK constraint will prevent you from using the table in an exchange partition.) -Original Message- Stephane Paquette You can use the partitionning option. Load into a 1 partitionned table then do a partition exchange with the target table. It allows you to have the current data live while loading into the partitionned table. Once the load is loaded just exchange the partition with the table, it is fast as it is just an update in the data dictionnary. No data is physically moved. -Original Message- Nancy Hu We have an Oracle database that is a kind of data warehouse. We load data from mainframe into the database every day. The following are the steps how we load data currently: 1. get the data file from Datacom 2. ftp the data file from mainframe to the Sun machine where the Oracle database resides 3. truncate all tables in Oracle database 4. load the data into Oracle with SQL LOADER Most tables in the Oracle database don't have primary key. Tables are not available during the loading that gives us problem for our 24x7 availability. Therefore, we would like to change our loading method. Any ideas would be highly appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sid/serial# vs. audsid: why both?
Thank you John. I guess I can revise my question, then, to say Why not use SID/SERIAL# all the time? :) I suppose there are reasons but it seems unnecessary to have two sets of values that uniquely identify a session. Though of course, from the documentation, AUDSID is unique over the lifetime of the database (generated from sequence SYS.AUDSES$) and SID/SERIAL# would not be unique over the lifetime of the database. -Original Message- John Kanagaraj Sent: lundi, 6. octobre 2003 15:59 In what cases does the SERIAL# need to be used? Can someone give an example where a session-level command would be applied to an incorrect session object if SERIAL# were not available? For backward compatibility reasons :) Looks like AUDSID wasn't generated 7.2 and prior unless AUDIT_TRAIL was TRUE (even if you don't use auditing). A lot of scripts use SERIAL# and the ALTER SYSTEM KILL SESSION uses the serial#, which should always be available. Why not use AUDSID all the time? Is there a reason why the database keeps track of two session identifying numbers? AUDSID is 0 if connecting as internal. Notes 123128.1 and 122230.1 may help! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
job announcement for Oracle DBA having experience with RAC (posted with approval of list owner)
Job announcement for Oracle DBA having experience with RAC (posted with approval of list owner) Oracle specialist - Clustering and grid technologies Location: TBD Job Description: Quest Software is currently seeking an Oracle professional with extensive experience implementing, configuring and tuning Oracle Real Application Clusters (RAC). The individual concerned would be involved in providing technical advice to Quest development groups regarding the implementation of RAC capabilities in existing Quest tools, designing software solutions that assist Oracle professionals in the administration of RAC and developing strategies to support new Oracle technologies relating to RAC, including grid and database management technologies introduced in Oracle 10g. The position would initially report to the CTO of the Application Performance Management BU. Job Responsibilities: . Implement RAC environments within Quest for research and development purposes. . Assist in developing strategies to support RAC and 10g technologies. . Provide competitive analysis with regard to RAC and 10g technologies in competing tools . Provide input into requirement and design documents for existing and new Quest Oracle tools. . Design expert systems rules and diagnostic capabilities to support RAC. . Assist in ensuring total product quality . Assist with product documentation Job Requirements: . 1-2 years experience in the implementation of production RAC environments. . Sound understanding of RAC internals, administration and performance management. . At least 5 years experience in managing Oracle RDBMS technology. . (Desirable) Exposure to Oracle 10g technology (participation in the beta) and sound understanding of Oracle 10g concepts. . Good interpersonal skills with the ability to work well in a team environment . Good organizational and communication skills To apply, send a resume (MS Word compatible format) to [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OFA myths was Re: BAARF
-Original Message- Paul Baumgartel Loney didn't write OFA, and methinks he was taking liberties with it. Perhaps. However I notice that DBCA in Oracle 9.2 creates a tablespace called INDX. http://download-west.oracle.com/docs/html/A97297_01/appg_ofa.htm#sthref807 Oracle9i Administrator's Reference Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris Part No. A97297-01 Appendix G Optimal Flexible Architecture ... Separate Segments With Different Requirements Separate groups of segments with different lifespans, I/O request demands, and backup frequencies across different tablespaces. Table G-5 describes the special tablespaces that the Database Configuration Assistant creates for each Oracle database. ... Table G-5 Special Tablespaces ... INDX - Index associated with data in the USERS tablespace USERS - Miscellaneous user segments ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
locally managed autoallocate (was: Separate Indexes and Data)
Ive read the book. PCTINCREASE is basically set to 100% so the extent sizes double. Thats 'basically' how it works. I have seen some posts on dejanews saying it doesnt necessarily work this way and some people are finding large extent sizes with just a few extents and when tables are dropped this is leading to fragmentation. It hasnt happened to me, but the posts on dejanews were from some pretty good posters. So Im playing conservative. We also had one of the contributors here mention issues. I think Jonathan Lewis has explained the algorithm before, but it's also something that we have investigated here. The algorithm (ignoring some details) is: There will be 4 extent sizes used, 64K, 1M, 8M, 64M As long as object allocation is 1M or less, 64K extent sizes are used, When object allocation is between 1M and 64M, 1M extent sizes are used. When object allocation is between 64M and 1G, 8M extent sizes are used. When object allocation is more than 1G, 64M extent sizes are used. However, when you initially create the object, the extents are determined by figuring out the space allocated to the newly created object taking into account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters. So the object might start off with 1M extents instead of starting off with 64K extents. The algorithm is similar to the one outlined above but it is more complicated. The NEXT and PCTINCREASE seem to be ignored after the object is created. e.g. create table ... tablespace locally_managed_autoallocate storage (initial 1M next 512K minextents 15 pctincrease 0) ... ; Initial allocation will be 1M + (15 - 1) * 512K = 8M When you create the table, you will see eight extents, each of one megabyte. There are additional wrinkles, but I don't think the algorithm has bugs. I don't think that there really is fragmentation in the sense that an unused extent will remain unused forever. All extents will be in one of the 4 sizes mentioned above, and all are subject to reuse at some point. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OFA myths was Re: BAARF
Personally I never use DBCA. The manual installation scripts that I've carried over from my 8.0 days for Windows and HP-UX still work (with minor modifications) for 9.2. Well, I lie. When I first install a new Oracle version I use DBCA to create a database just to see what some of the new options are. -Original Message- Mercadante, Thomas F and the first thing that I do is to delete the INDX tablespace!!! As well as dropping the ORD* users, SCOTT, Tim, Tammy-Fae, Jim Bob and all the other crappy stuff that auytomatically gets installed. I try and get it back to the original 8.0 install!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OFA myths was Re: BAARF
Not commenting on the accuracy of the information, but Kevin Loney, in the Oracle8 DBA Handbook (1998), says the following (Chapter 3 Logical Database Layouts), in a section entitled The Optimal Flexible Architecture (OFA) Index segments should not be stored in the same tablespace as their associated tables, since they have a great deal of concurreint I/O during both manipulation and queries. Index segments are also subject to fragmentation due to improper sizing or unpredicted table growth. Isolating the application indexes to a separate tablespace greatly reduces the administrative efforts involved in defragmenting either the DATA or the INDEXES tablespace. From reading his book, I always thought that OFA implied the separation of tables and indexes. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Steve Rospo Sent: jeudi, 25. septembre 2003 15:10 I'd like to get rid of the myth that OFA really states all that much about what goes in what tablespace etc. I've got a copy of the Cary's OFA paper entitled The OFA Standard - Oracle7 for Open Systems dated Sept 24, 1995. (Happy belated birthday OFA!) At the end of paper there's a summary of the requirements and the recommendations that make up OFA. The CLOSEST the OFA comes to specifying table/index separation are #7 Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. -or maybe- #11 *IF* [emphasis mine] you can afford enough hardware that: 1) You can guarantee that each disk drive will contain database files from exactly one application and 2) You can dedicate sufficiently many drives to each database to ensure that there will be no I/O bottleneck. The document itself says, The OFA Standard is a set of configuration guidelines that will give you faster, more reliable Oracle database that require less work to maintain. So every time I read that someone is putting redo here, index tablespaces here, and temp tablespaces there in order to be OFA compliant I kinda shrug. Obviously it's all a good idea to separate this stuff but it's not absolutely required for OFA-ness. Essentially, OFA is just a very good way of separating Oracle code from Oracle data to make administration *much* easier. I'm sure before OFA there were plenty of places that had everything under $ORACLE_HOME/dbs and no naming standard for datafiles. Ugh! Now if we could only find this Cary V. Millsap, Oracle Corporation character so he could explain himself. ;-) '95 was a loong time ago. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tuning help required
This probably doesn't apply to the original question, but I would like to point out
that
MEPAI.MPAI_AS_OF_DATE between to_date('03/01/2003','MM/DD/') and
to_date('03/31/2003','MM/DD/')
is not the same as
MEPAI.MPAI_AS_OF_DATE in (to_date('03/01/2003','MM/DD/'), ...,
to_date('03/31/2003','MM/DD/'))
unless all the dates have 0:0:0 for the time portion, for example because of a before
insert or update trigger that sets :new.mpai_as_of_date := trunc (:new.mpai_as_of_date)
Try this and see the difference.
create table orders (order_id number (4), order_date date) ;
begin
for i in 1..24
loop
for j in 1..30
loop
insert into orders (order_id, order_date)
values (j + 30 * (i - 1),
to_date ('200309' || to_char (j, 'FM09') || to_char (i - 1, 'FM09'),
'MMDDHH24')
) ;
end loop ;
end loop ;
commit ;
end ;
/
select count (*) from orders
where order_date between to_date ('20030901', 'MMDD')
and to_date ('20030930', 'MMDD') ;
select count (*) from orders
where order_date in
(to_date ('20030901', 'MMDD'),
to_date ('20030902', 'MMDD'),
to_date ('20030903', 'MMDD'),
to_date ('20030904', 'MMDD'),
to_date ('20030905', 'MMDD'),
to_date ('20030906', 'MMDD'),
to_date ('20030907', 'MMDD'),
to_date ('20030908', 'MMDD'),
to_date ('20030909', 'MMDD'),
to_date ('20030910', 'MMDD'),
to_date ('20030911', 'MMDD'),
to_date ('20030912', 'MMDD'),
to_date ('20030913', 'MMDD'),
to_date ('20030914', 'MMDD'),
to_date ('20030915', 'MMDD'),
to_date ('20030916', 'MMDD'),
to_date ('20030917', 'MMDD'),
to_date ('20030918', 'MMDD'),
to_date ('20030919', 'MMDD'),
to_date ('20030920', 'MMDD'),
to_date ('20030921', 'MMDD'),
to_date ('20030922', 'MMDD'),
to_date ('20030923', 'MMDD'),
to_date ('20030924', 'MMDD'),
to_date ('20030925', 'MMDD'),
to_date ('20030926', 'MMDD'),
to_date ('20030927', 'MMDD'),
to_date ('20030928', 'MMDD'),
to_date ('20030929', 'MMDD'),
to_date ('20030930', 'MMDD')
) ;
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
[EMAIL PROTECTED]
Sent: mercredi, 24. septembre 2003 02:20
To: Multiple recipients of list ORACLE-L
Subject: Re: Tuning help required
Well, I'd start by writing the date part as :
MEPAI.MPAI_AS_OF_DATE between
to_date('03/01/2003','MM/DD/') and
to_date('03/31/2003','MM/DD/')
It will at the very least make the query easier to read and
understand
(also for the optimizer : it will know it's filtering on a
range instead
of distinct values).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: possible to have a primary key with a bitmap indx?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] is it possible to have a primary key that is enforced with a bitmap index? I don't think so. Here's an example from a 9.2.0.3 database: SQL create table t (v varchar2 (10), d date) ; Table créée. SQL create bitmap index bit1 on t (v) ; Index créé. SQL alter table t add (constraint t_pk primary key (v)) ; alter table t add (constraint t_pk primary key (v)) * ERREUR à la ligne 1 : ORA-01408: such column list already indexed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Isnumeric question
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Teresita Castro I want to made something like this ina query select decode( isnumeric(line_comment), to_number(line_comment),0) how can I do this in Oracle? First you need to define what isnumeric means to you. Does it mean a) this string only contains ASCII characters 0 through 9 b) this string only contains ASCII characters 0 through 9 with maybe one decimal point (as determined by my NLS settings)' c) this string only contains ASCII characters 0 through 9 with maybe one decimal point and group separators (as determined by my NLS settings) d) 'this string contains a valid ORACLE number constant' e.g. -2.3e+10 is a valid number? e) something else (minus signs, etc.) If the answer is d), then (from an old idea by Jared Still) create your own function create function is_number (str in varchar2) return number is x number ; begin x := to_number (str) ; return 1 ; exception when value_error then return 0 ; when others then raise ; end ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Package Body created with compilation errors-
The way SQL*Plus works: if you run a script with set echo off, but the script has a multi-line comment beginning with /* and ending with */, every comment line after the first one will show in the SQL*Plus output with DOC in the line prefix instead of SQL. The show errors will not show errors if the package you are creating belongs to someone else. Look for an uncommented create package body in your script, then look in dba_errors for that package body. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mercadante, Thomas F Why do you have a */ as the last line? And why is the script prefaced with DOC ? It looks like you have either an unbalanced comment indicator (*/) or the whole thing is a mess. Further, the error indicates that you have errors in the package body but you are showing us the package. If you could post the whole thing (package and package body) we could help you better. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Oracle DBA Could you please help to see the errors in the package compilation? Please see below SQL @xyz_pkg.sql DOCCREATE OR REPLACE PACKAGE XYZ_PKG DOCAS DOC-- DOCPROCEDURE UPDATE_TEST(COL1 IN DROP_ME.C1%TYPE); DOCEND XYZ_PKG; DOC DOC*/ Warning: Package Body created with compilation errors. SQL show err No errors. SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anyone have a copy of DUL ??
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tanel Poder A lot easier would be to write an online dul, which queries data dictionary for location of extents and object definitions for example and then scans only relevant datablocks. I think that in most Oracle databases, along with the software is included an online DUL. If I remember right it's called something like SQL. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Anyone have a copy of DUL ??
I realize that Oracle's DUL tool works for datafiles from a database that cannot be started. Far from me to argue with Mr. Poder, but I thought he meant by online DUL a DUL that would work when the instance is running. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Ji And DUL doesn't need a running instance to do it's job. All it needs are as much data files as you got. -Original Message- Good point, but there are few issues. Unloading data with SQL is slower than doing it directly. Yes, direct export is very fast, but it doesn't have the flexibility DUL does - DUL is built do survive any corruptions in data, while your export just may fail or server process crashes, DUL just keeps going tries to extract as much as possible. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pct_direct_access for a secondary index on an iot
I was just reading about that recently. Here's what I think happens (though a correction would be welcome): In a secondary b-tree index on an IOT, the b-tree index contains, for each key, a guess as to which block contains the row. In case of a block split, the guess can become incorrect. Hence the pct_direct_access statistic. In a secondary bitmap index on an IOT, the bitmap points to a row in the mapping table, and the mapping table contains a logical rowid pointing to the location of the row in the IOT. The mapping table is always updated with each DML on the IOT so the bitmap index would always find the right row without guessing. Therefore the statistic would have no meaning for a bitmap secondary index. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Hello list, I am unable to get a value in dba_indexes(pct_direct_access) for a bitmap index I created on an iot. I am using Oracle 9.2.0.1.0 enterprise edition on windows . Can someone help me by telling me what I need to do. I have done the following : --First create the iot with a mapping table. sql create table countries ( country_id char(2) , country_name varchar2 (40) , currency_name varchar2(25), constraint country_c_id_pk primary key (country_id ) ) organization index including country_name pctthreshold 20 tablespace users overflow tablespace indx mapping table; Table created. SQL insert into HR.countries values ( 'C', 'A', NULL ) ; 1 row created. SQL insert into HR.countries values ( 'D', 'A', NULL ) ; 1 row created. SQL insert into HR.countries values ( 'E', 'A', NULL ) ; 1 row created. SQL COMMIT ; Commit complete. --Now create a bmp index on it. SQL create bitmap index myindex on countries (country_name ) ; Index created. --This command should analyze the iot and its mapping table. SQL analyze table countries compute statistics; Table analyzed. --Might as well analyze the bmp index ( this does no harm right ? ) SQL analyze index myindex compute statistics; Index analyzed. SQL select index_name, pct_direct_access from DBA_indexes where index _name = 'MYINDEX' and owner=user; INDEX_NAME PCT_DIRECT_ACCESS -- - MYINDEX --As you can see I get a null value in the pct_dircect_access column. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL help needed
I know you said that this was Informix, but I'll post the article below anyway for the benefit of other listers. Oracle Technology Network Oracle Magazine September/October 2002 Turning On Pivot Tables By Jonathan Gennick http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html Describes a method for Oracle 9i where you don't have to worry that the table you are using to generate sequential numbers does not have enough rows for your query. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kirtikumar Deshpande I need some SQL help. Not sure if this is possible in plain SQL or not, but here it is: I have a table with two columns: EMPNBR LINENBR -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tuning a massive delete
Perhaps this small example can make it clear? I have two tables, orders and order_to_delete. I want to find orders to KEEP - i.e. the order_id is not in table order_to_delete, or it's in table order_to_delete with a status 'N'. If I'm using outer joins, I think I need a a union, nicht wahr? drop table order_to_delete ; drop table orders ; create table orders (order_id number not null, order_date date) ; create table order_to_delete (order_id number not null, delete_flag varchar2 (1)) ; insert into orders (order_id, order_date) values (1, sysdate - 1) ; insert into orders (order_id, order_date) values (2, sysdate - 2) ; insert into orders (order_id, order_date) values (3, sysdate - 3) ; insert into order_to_delete (order_id, delete_flag) values (1, 'Y') ; insert into order_to_delete (order_id, delete_flag) values (2, 'N') ; commit ; select a.order_id, a.order_date from orders a, order_to_delete b where a.order_id = b.order_id and b.delete_flag = 'N' union select a.order_id, a.order_date from orders a, order_to_delete b where a.order_id = b.order_id (+) and b.order_id is null ; This query will return order ids 2 and 3, the ones I want to KEEP. The query returns the same value in Oracle 8.1 and Oracle 9.2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Binley Lim On a general note, this older outer join syntax to simulate a not-in requires ALL the b columns to include the (+) sign, including the nvl..., except the b.cusip is null, which is the not-in itself. If you miss one, the logic is completely changed. 9i's new syntax does outer-joins by doing the outer on the tables, rather than the columns, so you can no longer do this outer simulation! In this case, you can achieve the same thing with a not-in, rather than (+), in the SQL syntax (assuming you see a HASH AJ in the plan somewhere). - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:04 AM [EMAIL PROTECTED] wrote: create table ani_prx_faster parallel (degree 5) nologging as select b.* from bo_owner_master.ani_prx b, bo_owner_stage.ani_prx a where a.cusip = b.cusip (+) and a.fund_no = b.fund_no (+) and a.add_cymd = b.add_cymd (+) and nvl(b.ba_reccode, 'X') != 'V' and b.cusip is null This query I got from here I got only 638k, and I have 27m records in my file and there are 17m deletes. so 12m records left. Also all the records are null what do i need to change? Ooops, I have permuted a's and b's in the where clause ... Otherwise not to sure about the nvl(). Wait a minute ... we are getting rows for which there is no matching (cusip, fund_no, add_cymd) in the staging table. Perhaps that the 'cusip is null' condition should go ... the nvl() should be enough. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how can I see which database I am logged into without v$instance and v$database
-Original Message-
From: Olga Gurevich [mailto:[EMAIL PROTECTED]
I think there was a dbms package to get some of
the environment variables for a session, but I can't
remember anyhting specific. If someone know what I'm
talking about and has any details, please Email me or
post here
Oracle9i SQL Reference Release 2 (9.2)
Part Number A96540-01
Functions, 122 of 177: SYS_CONTEXT
...
Table 6-2 Predefined Parameters of Namespace USERENV
...
(parameter, return value, return length (bytes))
DB_DOMAIN
Domain of the database as specified in the DB_DOMAIN initialization parameter. 256
DB_NAME
Name of the database as specified in the DB_NAME initialization parameter. 30
...
HOST
Name of the host machine from which the client has connected. 54
INSTANCE
The instance identification number of the current instance. 30
e.g.
select
sys_context ('userenv', 'db_domain') as db_domain,
sys_context ('userenv', 'db_name') as db_name,
sys_context ('userenv', 'host') as host,
sys_context ('userenv', 'instance') as instance
from dual ;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: COBOL to Oracle
Do you mean that the COBOL code would be translated to PL/SQL packages, or that the COBOL I/O would be changed to SQL insert/update/select using Pro*COBOL instead of READ and WRITE? Or do you mean that the COBOL programs would be automagically transformed into an Oracle database? One datafile for each program source code file? The mind boggles. -Original Message- Does anyone know of tools, or have had experience using tools, that will quickly convert COBOL code to Oracle. Any information you can provide would be greatly appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: question for internals experts
Or unless you have Oracle 10G. rgaffuri, are you still updating TS$, you rascal? You could try the following: set long 100 spool c:\temp\views.log select a.view_name, a.text from dba_views a where a.owner = 'SYS' and a.view_name like 'DBA\_%' escape '\' and exists (select * from dba_tab_columns b where b.owner = a.owner and b.table_name = a.view_name and upper (b.column_name) like '%TABLESPACE%') ; Then read the file, figure out which views are showing tablespace name and from which SYS table the tablespace name comes from. Or you could modify your code to be independent of tablespace name, which would be the better solution. -Original Message- Sounds like you want to rename a tablespace. Not recommended without approval from Oracle support. [EMAIL PROTECTED] Does oracle store the tablespace_name or any data about a tablespace anywhere else accept TS$? Im looking for base data dictionary tables. Not views. Are there any documents out there about how oracle stores information in the data dictionary tables other than what Steve Adams has in his book or on his website? Has anyone else published on this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: question for internals experts
I think this would be the perfect time for you, as DBA, to explain to the Powers That Be that updating SYS.TS$ is a BAD idea and that the transportable tablespace plan has to change. You have damning evidence at this point in time. You will be doing yourself and your successors a big favour. -Original Message- not my call. i posted this on here before. was done that way before i got here. Its part of the transportable tablespace plan since you cant have multiple tablespaces with the same name in one instance. ... However, for the second time flushing the shared pool has not worked. ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC 9i database - gx$ views ?
-Original Message- From: Browett, Darren [mailto:[EMAIL PROTECTED] I know there is the gv$ views which are the cluster wide equivialent of the v$ views, but is there any gx$ views where are equivalent to the x$ views ??? I don't think so, but does the source code for a RAC view show any gx$ views? for example, what does this show you? select * from v$fixed_view_definition where view_name = 'V$PING' ; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: select strings with '_' in it
select col1 from table where col1 like 'v\_lan' escape '\' ; -Original Message- From: Benny Pei [mailto:[EMAIL PROTECTED] I have this data 'v_lan' and also 'vclan'. I would like to select 'v_lan' but not 'vclan'. Is it possible to do that? I tried set escape on select col1 from table where col1 like 'v\_lan' / but it doesn't work. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Limits on PL/SQL block?
-Original Message- From: Rudy Zung [mailto:[EMAIL PROTECTED] I'm seeing a PLS-00123 program too large error. Oracle's documentation says that the actual limit on the size of the block is dependant on the mix of statements in the PL/SQL block. Does anyone know how Oracle determines this limit? Is it a pure size of PL/SQL block in bytes, or is it number of unique statements in the block, or is it dependant on how much redo that the block may generate? I asked a question about the maximum trigger size on Metalink recently (the 9.2 documentation said that the maximum trigger size is 32K). Short answer - the easiest way to tell if a PL/SQL block is too big: try it and see if you get an error. Here is the full answer from Oracle: The limit of 32k for a trigger is platform dependent. The 32K limit is set taking into account the limit of 64K DIANA Nodes which includes the m-code and parsed-code. You may therefore end up having larger source code if the parsed-code ends up being lesser than the source code. In the shared pool, a package spec, object type spec, stand-alone subprogram, or anonymous block is limited to 64K DIANA nodes. The nodes correspond to tokens such as identifiers, keywords, operators, and so on. The m-code is limited to 64K compiler-generated temporary variables. Unfortunately, you cannot estimate the number of DIANA nodes from the parsed size. Two program units with the same parsed size might require 1500 and 2000 DIANA nodes, respectively (because, for example, the second unit contains more complex SQL statements). When a PL/SQL block, subprogram, package, or object type exceeds a size limit, you get an error such as program too large. Typically, this problem occurs with packages or anonymous blocks. With a package, the best solution is to divide it into smaller packages. With an anonymous block, the best solution is to redefine it as a group of subprograms, which can be stored in the database. You can query the user_object_size table on the database to find out the size of the procedure/package/plsql block. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i-OCP Question
Except that: 1) five disks was not one of the possible answers, and 2) we don't know that the database is in archivelog mode. For the 9i OCP examination the correct answer is answer number 4 (4 disks). I'm willing to bet on it! -Original Message- From: Ron Yount [mailto:[EMAIL PROTECTED] Well Who knows what the author of the question intended, but in the example of 2 (duplexed) redo log groups with archiving, they speak of five disks. One of EACH log member (not shared with log members of another group) and one for the archive log destination. That being said, I believe the answer to the question is A: 8 -Original Message- Jacques Kilchoer Sent: Wednesday, July 30, 2003 9:19 PM To: Multiple recipients of list ORACLE-L -Original Message- From: Senthil Kumar [mailto:[EMAIL PROTECTED] What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 The question specifically says how many disks does Oracle recommend...? It seems to me that the correct answer is answer number 4, Oracle recommends 4 disks. (Quote: When setting up a multiplexed online redo log, place members of a group on different disks. If a single disk fails, then ONLY ONE MEMBER of a group becomes unavailable... - capitalization mine) Therefore 4 members - 4 disks. Here is a link to a relevant section from an Oracle manual. Oracle9i Database Administrator's Guide, Release 2 (9.2), Part Number A96521-01 Chapter 7 - Managing the Online Redo Log: Placing Online Redo Log Members on Different Disks http://download-west.oracle.com/docs/cd/B10501_01/server.920/a 96521/onlinere do.htm#5414 Placing Online Redo Log Members on Different Disks When setting up a multiplexed online redo log, place members of a group on different disks. If a single disk fails, then only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function. If you archive the redo log, spread online redo log members across disks to eliminate contention between the LGWR and ARCn background processes. For example, if you have two groups of duplexed online redo log members, place each member on a different disk and set your archiving destination to a fifth disk. Consequently, there is never contention between LGWR (writing to the members) and ARCn (reading the members). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ** table triggers
-Original Message- I have a two tables A and B. Both have a field expected_delay_now and when it gets updated by a user in either table I want update it in corresponding row in the other table. However when this update is as a result of a trigger (and not user updated) how do I skip the update thru the trigger??? Thanks Answer: I see that you have another question on the scope of package variables, so it seems like you have already found the solution. But here's an example to avoid that mutating table error. create table t1 (id number, name varchar2 (30), expected_delay_now number) ; create table t2 (type varchar2 (1), type_desc varchar2 (10), expected_delay_now number) ; create package p as t1_upd boolean := false ; t2_upd boolean := false ; end p ; / create trigger t1_b4u_st before update on t1 begin p.t1_upd := true ; end ; / create trigger t1_b4u_row before update on t1 for each row begin if not p.t2_upd then update t2 set expected_delay_now = :new.expected_delay_now -- where ; end if ; end ; / create trigger t1_afu after update on t1 begin p.t1_upd := false ; end ; / create trigger t2_b4u_st before update on t2 begin p.t2_upd := true ; end ; / create trigger t2_b4u_row before update on t2 for each row begin if not p.t1_upd then update t1 set expected_delay_now = :new.expected_delay_now -- where ; end if ; end ; / create trigger t2_afu after update on t2 begin p.t2_upd := false ; end ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 9i-OCP Question
-Original Message- From: Senthil Kumar [mailto:[EMAIL PROTECTED] What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 The question specifically says how many disks does Oracle recommend...? It seems to me that the correct answer is answer number 4, Oracle recommends 4 disks. (Quote: When setting up a multiplexed online redo log, place members of a group on different disks. If a single disk fails, then ONLY ONE MEMBER of a group becomes unavailable... - capitalization mine) Therefore 4 members - 4 disks. Here is a link to a relevant section from an Oracle manual. Oracle9i Database Administrator's Guide, Release 2 (9.2), Part Number A96521-01 Chapter 7 - Managing the Online Redo Log: Placing Online Redo Log Members on Different Disks http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/onlineredo.htm#5414 Placing Online Redo Log Members on Different Disks When setting up a multiplexed online redo log, place members of a group on different disks. If a single disk fails, then only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function. If you archive the redo log, spread online redo log members across disks to eliminate contention between the LGWR and ARCn background processes. For example, if you have two groups of duplexed online redo log members, place each member on a different disk and set your archiving destination to a fifth disk. Consequently, there is never contention between LGWR (writing to the members) and ARCn (reading the members). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: logon trigger to start tracing
-Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED] The logon user needs to have granted alter session privileges directly to her, not just through a role. I believe you that it's needed for DBMS_SESSION.SET_SQL_TRACE, but then why was I able to get a trace file by using DBMS_SUPPORT, creating the trigger as a user that did not have ALTER SESSION privilege? My example was: --- Jacques Kilchoer [EMAIL PROTECTED] wrote: What database version? What is your setting for O7_DICTIONARY_ACCESSIBILITY? I tried the following in an 8.1.7 database with O7_DICTIONARY_ACCESSIBILITY = FALSE create user x identified by ... ; grant create session, create trigger to x ; (logging on as SYSDBA) grant execute on sys.dbms_support to x ; CONNECT X ... create trigger schema_trace after logon on schema begin sys.dbms_support.start_trace (waits = false, binds = false) ; end ; / DISCONNECT logon as user X: trace file created logon as user Y: no trace file created logon as user Y, say alter sesssion set current_schema = X ; no trace file created No error messages in alert log -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: logon trigger to start tracing
Never mind. I see that DBMS_SESSION has AUTHID CURRENT_USER (and of course DBMS_SUPPORT does not.) -Original Message- From: Jacques Kilchoer -Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED] The logon user needs to have granted alter session privileges directly to her, not just through a role. I believe you that it's needed for DBMS_SESSION.SET_SQL_TRACE, but then why was I able to get a trace file by using DBMS_SUPPORT, creating the trigger as a user that did not have ALTER SESSION privilege? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
Yes, what I meant is that the continuation prompt will be identical to the first line prompt - meaning that there is no SEPARATE continuation prompt. -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 14:25 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? Setting SQLNUMBER OFF will make the SQLPROMPT continuation prompt, not completely eliminate it. Isn't it what the OP wanted in the first place? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:59 PM sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Who Says Oracle does not listen
or no_operator or no_outline or no_profile or no_role or no_rbs or no_sequence or no_snapshot or no_snapshot_log or no_object_generic or no_synonym or no_pub_synonym or no_table or no_tablespace or no_trigger or no_user or no_source then if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; null ; when others then if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; raise ; end drop_object ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Who Says Oracle does not listen
|| ' ' ; else sql_statement := sql_statement || cmd_syntax || ' ' || replace (owner, '', '') || '' ; if cmd_syntax != 'user' then sql_statement := sql_statement || '.' ; end if ; end if ; if cmd_syntax != 'user' then sql_statement := sql_statement || '' || replace (object_name, '', '') || '' ; end if ; c_dynsql := dbms_sql.open_cursor ; dbms_sql.parse (c_dynsql, sql_statement, dbms_sql.native) ; ignore := dbms_sql.execute (c_dynsql) ; dbms_sql.close_cursor (c_dynsql) ; exception when no_cluster or no_cluster2 or no_dblink or no_dimension or no_index or no_indextype or no_indextype2 or no_java or no_operator or no_outline or no_profile or no_role or no_rbs or no_sequence or no_snapshot or no_snapshot_log or no_object_generic or no_synonym or no_pub_synonym or no_table or no_tablespace or no_trigger or no_user or no_source then if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; null ; when others then if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; raise ; end drop_object ; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: possible to set continuation prompt in sql*plus?
You can always set your prompt to take up two lines, but that looks ugly to me too. See example. SQL show user USER est SYSTEM SQL show sqlprompt sqlprompt SQL SQL -- change prompt to be connected username and last 4 SQL -- characters of instance name SQL column new_prompt new_value new_sqlprompt SQL select 2 user || '@' || chr (10) || 3 substr (instance_name, length (instance_name) - 3) || '' 4 as new_prompt 5 from v$instance ; NEW_PROMPT - SYSTEM@ jrk1 SQL set sqlprompt new_sqlprompt SYSTEM@ jrk1select * 2 from dual ; D - X SYSTEM@ jrk1 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Job to run first Wednesday
I know some solutions have already been posted. I will add this one however. Some of
the examples posted have the drawback that they assume your NLS date language is
English.
The formula below uses the fact that 1 January 2003 is a Wednesday.
The expression to find the first Wednesday of the month following test_date. If
test_date is the first Wednesday of the month then the value returned is the first
Wednesday of the following month.
decode (sign (trunc (test_date)
- next_day (last_day (add_months (trunc (test_date), -1)),
to_char (to_date ('20030101', 'MMDD'), 'DY')
)
),
-1, next_day (trunc (test_date), to_char (to_date ('20030101', 'MMDD'),
'DY')),
next_day (last_day (trunc (test_date)),
to_char (to_date ('20030101', 'MMDD'), 'DY')
)
)
proof of concept
SQL column sort_date noprint
SQL break on sort_date skip 1
SQL select
2trunc (test_date, 'MM') as sort_date,
3to_char (test_date, 'S/MM/DD DAY') as test_date,
4to_char (
5decode (sign (trunc (test_date)
6 - next_day (last_day (add_months (trunc (test_date), -1)),
7 to_char (to_date ('20030101', 'MMDD'), 'DY')
8 )
9 ),
10-1, next_day (trunc (test_date), to_char (to_date ('20030101',
'MMDD'), 'DY')),
11next_day (last_day (trunc (test_date)),
12 to_char (to_date ('20030101', 'MMDD'), 'DY')
13 )
14 )
15, 'S/MM/DD DAY') as following_first_wed_of_month
16 from
17 (select to_date ('20030701', 'MMDD') as test_date from dual
18 union
19 select to_date ('20030702', 'MMDD') as test_date from dual
20 union
21 select to_date ('20030703', 'MMDD') as test_date from dual
22 union
23 select to_date ('20030704', 'MMDD') as test_date from dual
24 union
25 select to_date ('20030705', 'MMDD') as test_date from dual
26 union
27 select to_date ('20030706', 'MMDD') as test_date from dual
28 union
29 select to_date ('20030707', 'MMDD') as test_date from dual
30 union
31 select to_date ('20030708', 'MMDD') as test_date from dual
32 union
33 select to_date ('20030801', 'MMDD') as test_date from dual
34 union
35 select to_date ('20030802', 'MMDD') as test_date from dual
36 union
37 select to_date ('20030803', 'MMDD') as test_date from dual
38 union
39 select to_date ('20030804', 'MMDD') as test_date from dual
40 union
41 select to_date ('20030805', 'MMDD') as test_date from dual
42 union
43 select to_date ('20030806', 'MMDD') as test_date from dual
44 union
45 select to_date ('20030807', 'MMDD') as test_date from dual
46 union
47 select to_date ('20030808', 'MMDD') as test_date from dual
48 union
49 select to_date ('20031001', 'MMDD') as test_date from dual
50 union
51 select to_date ('20031002', 'MMDD') as test_date from dual
52 union
53 select to_date ('20031003', 'MMDD') as test_date from dual
54 union
55 select to_date ('20031004', 'MMDD') as test_date from dual
56 union
57 select to_date ('20031005', 'MMDD') as test_date from dual
58 union
59 select to_date ('20031006', 'MMDD') as test_date from dual
60 union
61 select to_date ('20031007', 'MMDD') as test_date from dual
62 union
63 select to_date ('20031008', 'MMDD') as test_date from dual
64 union
65 select to_date ('20031009', 'MMDD') as test_date from dual)
66 order by 1, 2 ;
TEST_DATEFOLLOWING_FIRST_WED_
2003/07/01 MARDI 2003/07/02 MERCREDI
2003/07/02 MERCREDI 2003/08/06 MERCREDI
2003/07/03 JEUDI 2003/08/06 MERCREDI
2003/07/04 VENDREDI 2003/08/06 MERCREDI
2003/07/05 SAMEDI2003/08/06 MERCREDI
2003/07/06 DIMANCHE 2003/08/06 MERCREDI
2003/07/07 LUNDI 2003/08/06 MERCREDI
2003/07/08 MARDI 2003/08/06 MERCREDI
2003/08/01 VENDREDI 2003/08/06 MERCREDI
2003/08/02 SAMEDI2003/08/06 MERCREDI
2003/08/03 DIMANCHE 2003/08/06 MERCREDI
2003/08/04 LUNDI 2003/08/06 MERCREDI
2003/08/05 MARDI 2003/08/06 MERCREDI
2003/08/06 MERCREDI 2003/09/03 MERCREDI
2003/08/07 JEUDI 2003/09/03 MERCREDI
2003/08/08 VENDREDI 2003/09/03 MERCREDI
2003/10/01 MERCREDI 2003/11/05 MERCREDI
2003/10/02 JEUDI 2003/11/05 MERCREDI
2003/10/03 VENDREDI 2003/11/05 MERCREDI
2003/10/04 SAMEDI2003/11/05 MERCREDI
2003/10/05 DIMANCHE 2003/11/05 MERCREDI
2003/10/06 LUNDI 2003/11/05 MERCREDI
2003/10/07 MARDI 2003/11/05 MERCREDI
2003/10/08 MERCREDI 2003/11/05 MERCREDI
2003/10/09 JEUDI 2003/11/05 MERCREDI
25 ligne(s) sélectionnée(s).
SQL
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET
RE: logon trigger to start tracing
What database version? What is your setting for O7_DICTIONARY_ACCESSIBILITY? I tried the following in an 8.1.7 database with O7_DICTIONARY_ACCESSIBILITY = FALSE create user x identified by ... ; grant create session, create trigger to x ; (logging on as SYSDBA) grant execute on sys.dbms_support to x ; CONNECT X ... create trigger schema_trace after logon on schema begin sys.dbms_support.start_trace (waits = false, binds = false) ; end ; / DISCONNECT logon as user X: trace file created logon as user Y: no trace file created logon as user Y, say alter sesssion set current_schema = X ; no trace file created No error messages in alert log -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED] In an attempt to catch all SQL issued by a report, I created a logon trigger in the report's logon schema. (As SYS, I granted the user EXECUTE on DBMS_SESSION before creating the trigger.) As the schema owner, I am able to execute DBMS_SESSION.SET_SQL_TRACE interactively. On logon, trace files are created, but they contain the following: *** SESSION ID:(27.4739) 2003-07-22 18:52:53.000 Skipped error 604 during the execution of RPT_PERF.TRACE_ALL *** 2003-07-22 18:52:53.000 ksedmp: internal or fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges ORA-06512: at SYS.DBMS_SESSION, line 126 ORA-06512: at line 2 When SQL statements are executed in the session, no further trace information is added to the file. Anyone know what's going on here? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: piece-wise return of records from table
Tom Kyte to the rescue! getting rows N through M of a result set http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064, or http://tinyurl.com/hqsl (You can ignore the little argument on that page where Mr. Kyte lays down the law about not using abbreviations like u r for you are.) -Original Message- CASE select * from table gives 1000 records (say) We want only first ten of these records to be returned to the front end then records 11 to 20 so on ... How may the above be optimally possible ? SQL or PL/SQL any for it ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: piece-wise return of records from table
I guess it wouldn't hurt to include Mr. Kyte's answer: select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE -- including the order by ) a where rownum = MAX_ROWS ) where rnum = MIN_ROWS / He demonstrates on the site why this is the best solution. -Original Message- From: Jacques Kilchoer Tom Kyte to the rescue! getting rows N through M of a result set http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DIS PLAYID,F4950_P8_CRITERIA:127412348064, or http://tinyurl.com/hqsl (You can ignore the little argument on that page where Mr. Kyte lays down the law about not using abbreviations like u r for you are.) -Original Message- CASE select * from table gives 1000 records (say) We want only first ten of these records to be returned to the front end then records 11 to 20 so on ... How may the above be optimally possible ? SQL or PL/SQL any for it ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: help with dynamic pl/sql
It looks like you have to join, but the article can be found here: Method 4 Dynamic SQL with Native Dynamic SQL (online subscribers only) http://www.oracleprofessionalnewsletter.com/OP/OPmag.nsf/Index/594D98A6AF90025185256D32006A41CE -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I'd like to see that. Do you have the link? According to Tom Kyte, you cannot do type 4 with execute immediate. [EMAIL PROTECTED] If you do a google search for the online journal 'Oracle Professional'. Steve Fuerstein has an article where he shows you how to do method 4 dynamic sql with execute immediate instead of dbms_sql. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Orbitz issue
I hear that Mr. Ellison has pulled out a Japanese samurai sword and is roaming the corridors hunting for the guilty developers. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] There are some vague and brief news articles on the web stating that Orbitz is down and they are blaming Oracle. Anyone with some inside info on what went wrong? just curious. i know its probably due to someone at Orbitz making a mistake. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Find the table's name that using sequences
If you are using the sequence to generate the primary key for a table, then the sequence should only be used for that table. I can't think of a pro to have one sequence shared for the primary keys on many different tables. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Chris Grabowy Well, there could be business logic reasons as to why you would have one sequence per table. Also, I don't know if I would ever go with one sequence for many tables, sounds like a bottle neck to me. And how would one sequence for many tables impact scalability?? Or having lots of users hammering the database?? And what happens if you have to reset the sequence, then you have to check the primary key values on many tables. One sequence to one table sounds good to me, but I would love to hear pros/cons about this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle security question
In addition to what Dennis said: if Orace init parameter O7_DICTIONARY_ACCESSIBILITY = FALSE (the default in 9.0 and 9.2) then to connect as user SYS you need to specify the connect string like this: connect SYS/password AS SYSDBA The reason the user SYS is more protected than the user SYSTEM is because: a) the user SYS is the owner of the data dictionary - the set of tables that are used by Oracle to store internal information about the objects in the database; b) the user SYS is the one you use for tasks such as starting up or shutting down the database. SYSTEM is s DBA user created along with the database. In general, you should do the following: only sign on as SYS to startup and shutdown the database, or for recovery. create another DBA user and sign on as that other user for any DBA tasks. I hardly ever use the SYSTEM account except for creation of some Oracle-related objects (like a public PLAN_TABLE or the PRODUCT_USER_PROFILE table.) -Original Message- From: Don Yu [mailto:[EMAIL PROTECTED] Sent: vendredi, 11. juillet 2003 13:49 To: Multiple recipients of list ORACLE-L Subject: Re: Oracle security question Dennis: Thanks for your message. Now I have changed sys password by the following command: alter user sys identified by xxx But when I try to login from sql plus window by using sys, I cannot successfully login. Also I get an error message. The message is something like connection to sys should be as sysdba or sysoper. So my question is what sys for? Thank you very much! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: installed users
ctxsys - intermedia text (the old Context text cartridge) ordsys - intermedia data types for storing of audio and video data mdsys - data types for storing of spatial data I think that in 9.0 and 9.2, by default those users are created with expired and locked logins. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED] I've started a project to review all users in all our databases and remove unused/unnecessary users or at the very least restrict the privileges they have to the minimal ones they need. In several of the databases there are both ORDSYS and CTXSYS. From what I've been able to find out by running the above procedures, ORDSYS is for Intermedia. I could have sworn CTXSYS was the Intermedia account. So... is there a manual I've missed that tells me exactly which of these two I have to have in my databases and why? In addition, I know you have to install Spatial Option as part of an Oracle installation, even if you don't want to use it. Some of these databases also have the account MDSYS, some don't. All but one are 9iR2. We don't use Spatial. The two databases that have this account (8.1.7.4 and 9.2.0.2) both use Intermedia. Does that have something to do with it? I don't want to remove an account I need, but I don't want to leave ones I don't need in the databases. Let's not even begin to talk about the fact that just about EVERY user seems to have unlimited tablespace priv. This is not going to be pretty, easy or clean. But I will have secure databases. Except of course, every app has a configuration file in which they hard-code the passwords. Sigh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Restricting access via sqlplus
The problem is that if the decryption and set role were done inside a database package, then that means that the Oracle database user needs execute privilege on the package, and so the user could call the package from inside SQL*Plus on the client. At my old company they were talking about encrypting the network traffic but I left before they implemented that, and I don't know if they ever did. -Original Message- From: Pete Finnigan [mailto:[EMAIL PROTECTED] It sounds like the encrypted password is read by the client? and decrypted on the client? or in the database as a package procedure?. If it was decrypted in the client and then the set role command was sent to the database the password could be read from the network with a tool such as snoop on Unix or using SQL*Net support level trace as that shows packet contents in the SQL trace. The latter could be setup by a user on his PC attempt a logon to the database and then read the password from the trace file. If the decryption and set role were to be done in a package and the password is not passed over the network then its better. You could also encrypt the network traffic of course. But as you say there is still a risk from someone discovering the encryption scheme. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: composite primary key versus unique index
You mean you created an index A on the table with the descending column order, and then added a primary key constraint, which created another index on the same columns (except that all the columns are in ascending order). Or did you do something different? If you did something different I'd be curious to know exactly what you implemented. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] i didnt that try on Oracle, but our mainframe Rdms. We have that situation there with the ascending en descending attributes in the primary key. The workaround is to create a unique index (with and without desc) and create afterwards a primary key constraint on it in oracle. we have oracle 8.1.7.4.10. (including patches) -Original Message- From: Jacques Kilchoer I don't understand how you can have those types of objects created. Let's assume this situation: create table T (a number, b number, c number) ; create unique index t_pk on t (a asc, b desc) ; alter table t add (constraint t_pk primary key (a, b)) ; In 8.1 and later, the third statement (add constraint) will return an ORA-0955 error because Oracle is unable to build the index needed for the primary key constraint. In 8.0 and earlier, the desc keyword will be ignored in the create index statement, so there will be no reason why you cannot create the primary key constraint and foreign key constraints referencing that primary key constraint. Now, in 8.1 and later, if the index and the PK have different names, like so: create table T (a number, b number, c number) ; create unique index t_idx1 on t (a asc, b desc) ; alter table t add (constraint t_pk primary key (a, b)) ; then the third statement (add constraint) will create a second index on the table named t_pk, and again you will be able to create foreign key constraints referencing the primary key constraint. What version of Oracle are you running, and could you describe the tables/indexes/constraints involved? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Restricting access via sqlplus
To prevent these kinds of problems, the only solution I've ever thought was feasible was how we implemented it at a previous company of mine. I think this kind of solution was discussed on the list many months ago. Problem description at my previous company: We had a two-tier application written that required users to sign on with their own Oracle userid/password. (We implemented password expiration rules etc...) The application, while connected to the database as the user, would issue insert/update/delete statements against database tables in a common APPLICATION schema. We wanted to prevent a user from being able to connect to the database using another application (SQL*Plus, Toad, Excel using ODBC, etc.) and issue the same insert/update/delete statements that the application could do. Our solution: Each user would be granted a role, but the role would not be a default role. The role was protected by a password. The application would connect to the database, read the encrypted password for the role from a database table, use a two-way decryption scheme to decrypt the password, and then use the set role command to give the user the role that would allow insert/update/delete to be done from inside the application. The same could not be accomplished from inside SQL*Plus or Toad because the user would not know the password to use for the set role command. The role password could be changed as often as deemed necessary, except that the new password would have to be stored in the database in an encrypted form. This scheme has an obvious flaw, that anyone able to crack the encryption scheme could decrypt the role password, do a set role and then issue DML commands from inside a SQL client. And it would only work if you are building and deploying your own in-house application. But at the time we could not think of anything better. This was using Oracle 8.0. -Original Message- No, if you code your trigger to check if the program is your apps name, then renaming TOAD to TODD doesn't change anything. But of course if you change TOAD to your apps name, then this scheme fails. But as I stated, these kinds of methods only help against dumb users. If you want true security you have to have some kind of middle layer enforcing security and business/data rules.. (could be implemented inside database as well, through PL/SQL packages and no direct access to tables for example). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Restricting access via sqlplus
To prevent these kinds of problems, the only solution I've ever thought was feasible was how we implemented it at a previous company of mine. I think this kind of solution was discussed on the list many months ago. Problem description at my previous company: We had a two-tier application written that required users to sign on with their own Oracle userid/password. (We implemented password expiration rules etc...) The application, while connected to the database as the user, would issue insert/update/delete statements against database tables in a common APPLICATION schema. We wanted to prevent a user from being able to connect to the database using another application (SQL*Plus, Toad, Excel using ODBC, etc.) and issue the same insert/update/delete statements that the application could do. Our solution: Each user would be granted a role, but the role would not be a default role. The role was protected by a password. The application would connect to the database, read the password -Original Message- Sent: mercredi, 9. juillet 2003 14:59 To: Multiple recipients of list ORACLE-L Hi! No, if you code your trigger to check if the program is your apps name, then renaming TOAD to TODD doesn't change anything. But of course if you change TOAD to your apps name, then this scheme fails. But as I stated, these kinds of methods only help against dumb users. If you want true security you have to have some kind of middle layer enforcing security and business/data rules.. (could be implemented inside database as well, through PL/SQL packages and no direct access to tables for example). Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 09, 2003 11:39 PM Tanel, If I change TOAD.EXE to TODD.EXE, this scheme fails instantly ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, July 09, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Hi! I think sqlplus product profile isn't a good idea, because some smarter ones might be using TOAD, SQL Navigator or SQL Worksheet... What you might want to do is to write an after logon trigger which checks the app name from v$session and allows logon if and only if app name (v$session.program) is your 3rd party one. If app name isn't correct, then your trigger raises an exception. But of course, it only protects you from dumb users. Another way would be playing with roles, but since you have 3rd party app, it might be problematic. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:29 PM Is there a way to prevent end users from connecting directly to the database via sqlplus without restricting access of those same users via application code. The application is a third party package which prompts for an id and password and then uses that id/password to connect to the database. I found a note the the archives which suggested making an entry into the SQLPLUS_PRODUCT_PROFILE table, but I have not been able to make this work. Oracle version: 8.1.7 AIX 4.3.3 Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list
RE: composite primary key versus unique index
I don't understand how you can have those types of objects created. Let's assume this situation: create table T (a number, b number, c number) ; create unique index t_pk on t (a asc, b desc) ; alter table t add (constraint t_pk primary key (a, b)) ; In 8.1 and later, the third statement (add constraint) will return an ORA-0955 error because Oracle is unable to build the index needed for the primary key constraint. In 8.0 and earlier, the desc keyword will be ignored in the create index statement, so there will be no reason why you cannot create the primary key constraint and foreign key constraints referencing that primary key constraint. Now, in 8.1 and later, if the index and the PK have different names, like so: create table T (a number, b number, c number) ; create unique index t_idx1 on t (a asc, b desc) ; alter table t add (constraint t_pk primary key (a, b)) ; then the third statement (add constraint) will create a second index on the table named t_pk, and again you will be able to create foreign key constraints referencing the primary key constraint. What version of Oracle are you running, and could you describe the tables/indexes/constraints involved? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I have the following question for you : We have a mainframe database with tables which have composite primary keys with an ascending and a descending item. For example a table with license regisitrations with primary key items LICENSE in ascending and REGISTRATION_DATE in descending order. As far as i know this is not possible with Oracle, only a unique index should be a candidate to do this. I said should be, because if you need a foreign key relation to this specific composite index i get an ORA-2270 !. Anyone with an explanation why a create of a composite unique index with an ascending and descending order works and not with the primary key clause ? Is this triggered in oracle 9i or 10i ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unique constraint violation problem
In that case what happened is that a session inserted a row at 13:35:13 and another session (or the same session) tried an insert at the same second. This is assuming that the time reported from the application is taken from the same source as the time used to populate column date_created. If the application reporting time is taken from a different source than the time used to populate date_created (e.g. the application uses a PC client clock to report errors, and date_created is sysdate from the database server) then the two times will not necessarily match. Not creating unique indexes on tables - I agree with Ms. Carmichael that if you read that statement, what was probably meant was create constraints to enforce uniqueness, and not indexes (though as you will have noticed Oracle will use an index to enforce the constraint, and automagically create the index if necessary.) -Original Message- From: Anna Li [mailto:[EMAIL PROTECTED] Sent: mardi, 8. juillet 2003 07:09 Thanks for all of you who replied the message. I apologize for not explaining the problem clearer. Actually, the error was reported at 13:35:13pm from the application. There is a column called date_created in the table that records what time the record was created. The value of date_created for the record that caused error message is the same as reported from application. That's why I said that the record was inserted into the table successfully. I know Oracle recommends that we do not explicitly define unique indexes on tables. Why? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: unique constraint violation problem
My explanation would be that the record was NOT inserted successfully into the table. Proof of that would be that there are no duplicate records in the table. -Original Message- From: Anna Li [mailto:[EMAIL PROTECTED] I created a unique index on a table called REGISTRATION_K, but no unique constraint. Last week when the application tried to insert a record into the table, we got following error in the log file: Oracle::st execute failed: ORA-1: unique constraint (REGISTRATION_K) violated However, the record was inserted into the table successfully. There are no duplicate records in the table. Could anyone explain why? Any input will be highly appreciated. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ER Tools (was: erwin)
I work for quest so obviously my recommendation should be taken with a grain of salt but Qdesigner is available for trial download and the purchase price can be seen online. http://quest.com/qdesigner/ -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: lundi, 7. juillet 2003 12:29 To: Multiple recipients of list ORACLE-L Subject: ER Tools (was: erwin) Anyone care to ballpark a $$$ on the registered version of ERwin? I'm guessing it's expensive, and Computer ASSociates doesn't exactly trip my trigger. I'm trying to find a low-cost ER tool in order to get some simple data modeling done. So far, this is what I've found (VERY briefly): 1) Visio. Needs Enterprise edition. The UI is a pain, as far as I'm concerned. Pain, pain, pain. Cheapest viable option so far at less than $500/user. It can be used for more than just data modeling. 2) Oracle 9iDS. Hugely bloated. Relies on a 3GB (minimum) repository for some stupid reason. Apparently can't buy just Designer, which is all we need. Difficult to get started (concepts and management of workspace, container). Slow on a 2.4Ghz PC w/512MB RAM, although this may be due to me having to create the [EMAIL PROTECTED] repository DB on the same PC. Very comprehensive, though, from what I've seen. Can't access drawings from the OS, since they're (probably) in the repository. 3) No freeware tools such as dia, VisioModeler (pre-MS version of Visio), OpenOffice, or Koffice seemed to be able to draw crow's foot diagrams. 4) Sybase PowerBuilder. Didn't check out the download yet, although a demo of an older version (v6) seems like it would work. Not impressed with Sybase in general and it's probably in the $1Ks/user. 5) Data Architect from TheKompany.com only does physical modelling and their Kivio product doesn't seem to support crow's foot. 6) DeZign from datanamic.com. Haven't downloaded the demo yet, but the single screenshot looks promising. And at $149/user or $119/user for 10 licenses, I'm hoping... I've been looking thru past messages found on orafaq.com (better searching than fatcity.com) and this is the majority of what I've been able to find. Anyone have more to add? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: grant for package body
User X creates a package. You want user Y to be able to see the package body by querying ALL_SOURCE. The privilege needed by Y would be create any procedure. Or you can create a procedure owned by X that Y can run to retrieve the header and body source code of a package. -Original Message- Sent: jeudi, 3. juillet 2003 11:59 To: Multiple recipients of list ORACLE-L Hi , what prvilage ( role ) can let other schemas (users) to see package body in my schema . without giving select_catalog_role thanks, -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
(OT) ISS warns of coordinated hacker attack on July 6
Jared, I hope this is not too much off-topic for the list. I don't know if you prefer that we check with you first but I imagine you have a lot of e-mails to read already. From ComputerWorld: ISS WARNS OF COORDINATED HACKER ATTACK ON JULY 6 Internet Security Systems Inc. is warning that an international hacking contest could cause headaches for companies worldwide and disrupt the Internet. http://www.computerworld.com/securitytopics/security/hacking/story/0,10801,82730,00.html ISS warns of coordinated hacker attack on July 6 By Paul Roberts, IDG News Service JULY 02, 2003 An international hacking contest scheduled to begin this weekend could cause headaches for organizations worldwide and disrupt the Internet, according to a warning from Internet Security Systems Inc. (ISS). The contest, known as the Defacers Challenge, awards points to malicious hackers who successfully compromise an organization's Web server and deface its Web pages. ISS first became aware of the contest last week by monitoring Web sites and Internet Relay Chat channels frequented by malicious hackers who specialize in defacements, according to Peter Allor, manager of X-Force Threat Intelligence Services at Atlanta-based ISS. Rather than focusing on the volume of defacements, the Defacers Challenge is set up to reward the skill of malicious hackers who can compromise systems running less prominent operating systems, including Apple Computer Inc.'s Mac OS and Unix variants such as IBM's AIX and Hewlett-Packard Co.'s HP-UX. Contest organizers even set up a Web page (www.defacers-challenge.com) that outlines the rules of the game, including a point system for compromised machines -- one point for Windows, five for HP-UX and Macintosh -- and guidelines for what counts as a valid defacement. The target is to deface 6,000 Web sites. A prize of free Web site hosting is offered to the malicious hacker who can reach that goal first or accumulate the most Web sites in trying to do so, according to information posted on the site. The challenge is scheduled to begin Sunday and last for six hours, though information on the exact time hasn't been released, Allor said. ISS doesn't know which hacker or group of malicious hackers is responsible for organizing the challenge. Although the contest and Web page may be a joke, ISS noticed an increase in reconnaissance and probing scans of Web sites that may be connected to the contest, Allor said. Malicious hackers may be scouting out high-value systems, or even compromising them in advance so that they can quickly be defaced once the contest begins, he said. ISS recommends that organizations deactivate unneeded public-facing Web servers and turn off unnecessary services on Web servers that are needed, in addition to applying any necessary software patches to potentially vulnerable machines, Allor said. Recently disclosed software vulnerabilities that haven't yet been patched are attractive targets for hackers, he said. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What are the restrictions in naming an Oracle SID/Database?
And clarifying point 1 - at least the underscore is allowed in database and instance names. EMP_PROD would be a valid database and instance name. -Original Message- From: Senthil Kumar D [mailto:[EMAIL PROTECTED] What are the restriction in naming an Oracle SID/Database? 1. Can I have special character in SID/Database name? e.g. Is a SID name 'emp_prod' is allowed? No You can not use spl char's. SID name should be in alpha's. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
