date:20030727

Jack,
Question - why do you want to use System triggers
to change roles? I don't see how you can define an event for this trigger to be
fired. If the event is the updating of record in USERS table, who updates it?
Obviously not the user himeself; then the whole system becomes insecure to the
core.
Your choice of using a role to allow access to a
specific yesr is sound design. I assume each year is stored in separate tables.
If that is not correct, i.e. the record for all years are stored in a single
table, then roles will not help you, you need to enable FGAC using something
called application context.
Scenario 1: Each year's data is stored in a seprate
table.
You would create roles, but not normal roles. The
roles should be identified by a procedure. This procedure is owned by SYS or
some other secued schema. Inside this procedure you would assign a value to the
application context attribute called user_role, which is set via
dbms_Session.set_context. All the users are revoked execute priv on
dbms_session; so they will never be able to call this procedure directly. The
only way they can do it is by calling the trusted procedure you have defined for
that role.
Scenario 2: All the tables have data for all the
years.
Inthis case you will have to use FGAC; but the FGAC
policy will have to depend on the application context you defined earlier. You
wil define another context attribute called school_year, whic is again set by
the trusted procedure of the role. Since the user does not have the privilege to
call dbms_Session, he will not be able to set the value of this attrbute to any
other year at will.
Summary:
You will define several roles ideintified by
procedure. All these roles are granted to the user but none is a default
role.
When a user logs in, all roles assigned to him are
disabled, since none is a default role. Then he calls the procedure set_role(),
no arguments. Inside the procedure set_role(), you will read the users table,
see the role the user is supposed to have, enable this role via
dbms_Session.set_role and then set the application context, if any.

Since the user does not have execute privs on
dbms_session, he will not be able to set the app context.
Since the role is identified by a procedure, i.e.
set_role(), the user will not be able to set the role himself using "SET ROLE"
command in sql*plus. The only way he can do that is by calling the set_role
procedure.
If the user does not call the procedure, none of
the roles are enabled; therefore he will not be able to do
anything.
In other words, you have a secured
system.
As an added bonus: you will have the application
context attribute you can use any way you want to use. Right now you can use it
for FGAC; but later when you are in 9i, you can use it in other cooler features
such as Fine Grained Auditing.
HTH.
Arup Nanda
www.proligence.com

- Original Message -
From:
[EMAIL PROTECTED]

To: Multiple recipients of list ORACLE-L

Sent: Sunday, July 27, 2003 11:59
AM
Subject: Set Role in Trigger
Short form of my
question: How can I enable a Role for
a User within a database trigger (owned by another Schema) on a table owned by
yet another Schema? - The M's I've
RTF'd indicate that a trigger (and any procedure it calls) can never execute
with Invoker's Rights. - I can't find
a way to execute Set Role for a User as another User, say, System.
- I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to
our 3rd Party Student Information System, whose code I can't touch. I've
successfully implemented FGAC to keep Users at a School from accessing info.at
other schools. Now I need to limit which School Year's data they can
update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del
on its tables via a Role, so I thought I'd just switch Roles when the User
switched School Years (via updating her record in a Users table). Seemed
like a good idea, but now I can't see how to implement it.
SQL and PL/SQL commands like Set Role,
Alter Session, DBMS_Session.Set_Role, etc. only apply to the current User,
which would be the Trigger Owner. I've used
DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure
to Set Role for another User. BTW,
the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax
and a section in the PL/SQL User's Guide and Reference (Ch. 7 Subprograms /
Invoker Rights vs Definer Rights / Using Views and Database Triggers) are the
basis for my being stuck. The only
possible way I see to do this is to create the trigger as System, then use
Dynamic SQL to issue the "Alter User ... Default Role " command.
However, I don't know if that takes effect immediately (within the
User's current Session) or would take effect at the User's n

RE: Set Role in Trigger

2003-07-27 Thread Pardee, Roy E

This is probably too kludgy or simple-minded, or non-maintainable, but is it 
technically possible?

 1) Create a series of views that subset 
the actual tables, according to the rules 
you've got about who the viewer is & what 
year(s) they've selected in the Users table.

 2) Redefine the public synonyms so that they 
point to your views rather than the base
tables.

Cheers,

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487 
-Original Message-
Sent: Sunday, July 27, 2003 11:24 AM
To: Multiple recipients of list ORACLE-L


Jack,

Question - why do you want to use System triggers to change roles? I don't see how you 
can define an event for this trigger to be fired. If the event is the updating of 
record in USERS table, who updates it? Obviously not the user himeself; then the whole 
system becomes insecure to the core.

Your choice of using a role to allow access to a specific yesr is sound design. I 
assume each year is stored in separate tables. If that is not correct, i.e. the record 
for all years are stored in a single table, then roles will not help you, you need to 
enable FGAC using something called application context.

Scenario 1: Each year's data is stored in a seprate table.

You would create roles, but not normal roles. The roles should be identified by a 
procedure. This procedure is owned by SYS or some other secued schema. Inside this 
procedure you would assign a value to the application context attribute called 
user_role, which is set via dbms_Session.set_context. All the users are revoked 
execute priv on dbms_session; so they will never be able to call this procedure 
directly. The only way they can do it is by calling the trusted procedure you have 
defined for that role. 

Scenario 2: All the tables have data for all the years.

Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the 
application context you defined earlier. You wil define another context attribute 
called school_year, whic is again set by the trusted procedure of the role. Since the 
user does not have the privilege to call dbms_Session, he will not be able to set the 
value of this attrbute to any other year at will.

Summary:

You will define several roles ideintified by procedure. All these roles are granted to 
the user but none is a default role.

When a user logs in, all roles assigned to him are disabled, since none is a default 
role. Then he calls the procedure set_role(), no arguments. Inside the procedure 
set_role(), you will read the users table, see the role the user is supposed to have, 
enable this role via dbms_Session.set_role and then set the application context, if 
any. 

Since the user does not have execute privs on dbms_session, he will not be able to set 
the app context.

Since the role is identified by a procedure, i.e. set_role(), the user will not be 
able to set the role himself using "SET ROLE" command in sql*plus. The only way he can 
do that is by calling the set_role procedure.

If the user does not call the procedure, none of the roles are enabled; therefore he 
will not be able to do anything.

In other words, you have a secured system.

As an added bonus: you will have the application context attribute you can use any way 
you want to use. Right now you can use it for FGAC; but later when you are in 9i, you 
can use it in other cooler features such as Fine Grained Auditing.

HTH.

Arup Nanda
www.proligence.com
- Original Message - 
To: Multiple recipients of list ORACLE-L 
Sent: Sunday, July 27, 2003 11:59 AM



Short form of my question: 
How can I enable a Role for a User within a database trigger (owned by another Schema) 
on a table owned by yet another Schema? 
- The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never 
execute with Invoker's Rights. 
- I can't find a way to execute Set Role for a User as another User, say, System. 
- I'm stuck. 
- Environment: 8.1.7 on Win2k and HP-UX. 

Longer form of my question: 
I'm in the process of adding extra security features to our 3rd Party Student 
Information System, whose code I can't touch.  I've successfully implemented FGAC to 
keep Users at a School from accessing info.at other schools.  Now I need to limit 
which School Year's data they can update (Past, Current, Next).  The Application 
grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles 
when the User switched School Years (via updating her record in a Users table).  
Seemed like a good idea, but now I can't see how to implement it. 

SQL and PL/SQL commands like Set Role, Alter Session, DBMS_Session.Set_Role, etc. only 
apply to the current User, which would be the Trigger Owner.  I've used 
DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure to Set 
Role for another User. 

BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax and a 
section in the PL/SQL User'

Re: 2 small tables - Query takes 3 hours

2003-07-27 Thread Mladen Gogala

If you have been a DBA for 7 years, then you know the procedure:
Go to V$SESSION_WAIT and see what are you waiting for. You'll get file
and block (P1 & P2), so you can locate the segment. Do explain plan and
see how this segment is used. In my experience, it's usually an index used to 
retrieve the whole table. Then use the classic RBO methods to disable the
index (||'',+0).
What I object to is the casual fashion you used to talk about the data 
dictionary modifications. Sure, we've all done that but we've never bragged
about it. It's like sex: that's how we all came in this world but it is rude 
to talk about that. Talking about dictionary modifications in "look mom, no 
hands" way makes us look like irresponsible hackers, which is not the image 
I'd like to project. Thus the rebuke.

On 2003.07.27 05:29, [EMAIL PROTECTED] wrote:
This view is part of the upgrade script provided by the Vendor
(duh):
I've been DBA'ing for 7 years in DB2 and Oracle and messing
around with a SYS view is obviously not done- .
 I was looking for some proper advise as Ive never seen anything
like it before.
Sam

- Original Message -
Date: Sunday, July 27, 2003 11:39 am
> People should not play with the SYS schema, period. No ifs, no
buts,
> it just isn't done. You are on your own, pal. If I were your employer,
> you would have hit the road by now. Whatever application that
Maximo
> thing is, creating objects owned by SYS is simply not acceptable.
> It's guys like you that give database administrators a bad name.
>
>
> On 2003.07.27 01:29, [EMAIL PROTECTED] wrote:
> > Unix Solaris 8 and Oracle 8.1.7.4
> >
> > As part of an upgrade to the Maximo application I run a join on 2
> > tables:
> >
> > select count(*) from sys.syskeys s, maxsysindexes m where
> > s.ixname=m.name;
> > sys.syskeys is 705 rows and maxsysindexes is 443 rows.
> > when I trace the statement I find it doing a hash join and it
> estimates> 434 blocks and it does each block in 30 secs.
> >
> > Prior to running the query I create the sys.syskeys view as
detailed
> > below:
> > The only way I get round the problem is to recreate the
> > maxsysindexes table: And of course query then takes 1 second. I
> > have disabled hash join and the query just goes down another
path
> > taking just as long.
> >
> > Any thoughts as this only happened once the first upgrade in 10
> > schemas, now its happening all the time.
> >
> > --schema owner
> >
> > DROP VIEW SYS.SYSKEYS;
> >
> > CREATE VIEW SYS.SYSKEYS
> > (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ,
> > ORDERING, FUNCTION)
> >AS
> > SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', ''
> > FROM SYS.COL$ C, SYS.OBJ$ IDX,
> > SYS.OBJ$ BASE, SYS.ICOL$ IC,
> >  SYS.USER$ IO, SYS.USER$ BO
> > WHERE IO.NAME = 'ADWEA' AND BASE.OBJ# = C.OBJ#
> >   AND IC.COL# = C.COL#
> >   AND IC.BO# = BASE.OBJ#
> >   AND IO.USER# = IDX.OWNER#
> >   AND BO.USER# = BASE.OWNER#
> >   AND IC.OBJ# = IDX.OBJ#
> >   AND (IDX.OWNER# = UID OR
> >BASE.OWNER# = UID
> >OR
> >BASE.OBJ# IN ( SELECT OBJ#
> >  from sys.objauth$
> >  where grantee# in ( select kzsrorol
> >  from x$kzsro
> >)
> >)
> >)
> > ;
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author:
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051
http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services> -
> 
> > To REMOVE yourself from this mailing list, send an E-Mail
message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in
> > the message BODY, include a line containing: UNSUB ORACLE-
L
> > (or the name of mailing list you want to be removed from).  You
may
> > also send the HELP command for other information (like
subscribing).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
>  INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> ---
> --
> To REMOVE yourself from this mailing list, send an E-Mail
message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You
may
> also send the HELP command for other information (like
subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
---

Re: RMAN/Veritas duplicate db article

2003-07-27 Thread Jared Still

Hmm... absolutely no responses.

Perhaps a bit of clarification is in order.

I'm interested in comments only on the content.

This was a write as a you go paper and decidely 
not publishable as is.  If it seems worthwhile, I will
rewrite into something a little more acceptable than
the very rough draft that it is.

Thanks,

Jared

On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote:
> List,
>
> I've written a short ( compared to the length of time it took
> to do this anyway ) article on how to create a duplicate database
> on a remote server using RMAN and Veritas NetBackup.
>
> It can be found at:
>
>http://www.tinyurl.com/hu4r
>
> or at this one if you can't access tinyurl.com:
>
>   
> http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita
>s%20NetBackup%20and%20Oracle%20RMAN.doc
>
> It's kinda rough, so suggestions, comments, criticisms are welcome.
>
> Thanks,
>
> Jared

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

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

Re: Question about the list

Thanks, I'll do that if I hit the problem again..

Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Sunday, July 27, 2003 1:54 AM


>
> I own the list, that is, the logical entity.
>
> Bruce Bergman is owner of the ISP, fatcity.com, and
> the sysadmin and mail admin.
>
> Don't know why some posts appear before others, could
> be a simple explanation, could be a very complex one.
>
> Not being a mail expert, I don't know which it is.
>
> Best ask Bruce if you're curios - [EMAIL PROTECTED]
>
> Jared
>
> On Thursday 24 July 2003 15:29, Tanel Poder wrote:
> > Hi!
> >
> > How does the list server here operate?
> > I sent a reply to one message several hours ago (see below), but it
never
> > reached my mailbox again. Also, sometimes I get the reply first to my
> > mailbox than the original post (I mean several minutes before, it
couldn't
> > be a SMTP latency issue).
> > Is this list moderated somehow or what could be the cause? Or could it
be
> > related to timestamp of sent message (My morning arrives 7-9 hours
earlier
> > than in US).
> >
> > Kind of confused,
> > Tanel.
> >
> > - Original Message -
> > To: <[EMAIL PROTECTED]>
> > Sent: Thursday, July 24, 2003 6:06 PM
> >
> > > Hi!
> > >
> > > About the second one, you see the first read started from 6041 and was
18
> > > blocks, the second one which was executed 7 minutes later, started
from
> >
> > 6042
> >
> > > and was 17 blocks. Probably the extent boundary is at block 6059.
> >
> > Multiblock
> >
> > > reads don't cross extent boundaries.
> > >
> > > So, probably your query acquired block 6041 with FTS and by the time
it
> > > needed next block, the blocks were already out of buffer cache (LRU
list)
> > > and another multiblock read was needed. Maybe you should look at keep
> >
> > buffer
> >
> > > pool, but since the time interval was long, it might not be necessary
> > > anyway...
> > >
> > > Tanel.
> > > - Original Message -
> > > From: "Henry Poras" <[EMAIL PROTECTED]>
> > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > > Sent: Thursday, July 24, 2003 6:04 PM
> > > Subject: RE: Timestamps in trace files (and other trace file oddities)
> > >
> > > > (Tried sending this yesterday. I'll try again)
> > > >
> > > > Dan,
> > > > I was running a 10046 (level 12) trace on an awful piece of
PeopleSoft
> >
> > SQL
> >
> > > > today and got some really odd results in my trace file (8.1.7).
> > > >
> > > > *** 2003-07-23 15:40:59.149
> > > > WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6041 p3=18
> > > > *** 2003-07-23 15:46:06.340
> > > > WAIT #1: nam='latch free' ela= 1 p1=813986232 p2=66 p3=0
> > > > *** 2003-07-23 15:47:53.851
> > > > WAIT #1: nam='db file scattered read' ela= 0 p1=65 p2=6042 p3=17
> > > >
> > > > Two things struck me (three if it takes me too long to write this
and I
> > >
> > > get
> > >
> > > > home late). First, the timestamps show an elapsed time of ~7
minutes,
> >
> > but
> >
> > > > the trace file has ela=1 (one onehundredth of a second). The 7
minutes
> >
> > is
> >
> > > > closer to reality. Huh???
> > > >
> > > > Secondly, the first scattered read reads 18 blocks starting at 6041.
> > > > Why does the next scattered read start at block# 6042?
> > > >
> > > > Any ideas?
> > > >
> > > > Henry
> > > >
> > > >
> > > >
> > > > -Original Message-
> > > > Daniel Fink
> > > > Sent: Wednesday, July 23, 2003 11:04 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Gudmundur,
> > > > Where is this documented (so I can RTFM)?
> > > > The one piece of this I don't quite understand is that the timestamp
is
> > >
> > > not
> > >
> > > > emitted twice in a row. If the long time is the triggering event,
why
> > > > do
> >
> > I
> >
> > > > see a gap of 90 minutes (in another trace file)?
> > > >
> > > > Daniel
> > > >
> > > > Gudmundur Bjarni Josepsson wrote:
> > > > > Daniel,
> > > > >
> > > > > Perhaps someone else can explain this better but the documentation
> >
> > I've
> >
> > > > > got on this says that the Oracle kernel emits timestamps when a
long
> > > > > time has elapsed since the last line was emitted to the trace
file.
> > > > > Long time is defined as tens of seconds.
> > > > >
> > > > > Gudmundur
> > > > >
> > > > > > -Original Message-
> > > > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > > > > > Behalf Of Daniel Fink
> > > > > > Sent: 22. jślķ 2003 21:19
> > > > > > To: Multiple recipients of list ORACLE-L
> > > > > > Subject: Timestamps in trace files
> > > > > >
> > > > > >
> > > > > > I was perusing a 10046 trace file and I noticed that
> > > > > > timestamps are written to the trace file. Sometimes they were
> > > > > > very regular (3 minutes apart give or take 30 seconds) while
> > > > > > other times they were hours apart. I have noticed that two
> > > > > > timestamps are never written without any intervening
> > > > > > activity. Anyone have any idea on the reasonin

Re: 8.1.7 instance on upgraded AIX machine - ORA-1652 all the time

Hi!

I have a suggestion as well:

Tempfiles are sparse files in unix, that is you can use alter tablespace
command to add or resize the files even to 100G if you want, but they don't
actually use much space on disk. When you start writing to this file, OS
tries to allocate more real space to it and will fail if file system is full
(depending on OS and your quota + rights you might actually see some free
space, but it's unusable). You can see real size of a file in file system
blocks using ls -ls (at least in Solaris & HP-UX it works...)

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Sunday, July 27, 2003 10:44 AM


> Check the block sizes. That part of the argument might still stand.
>
> On 2003.07.27 02:24, Saira Somani wrote:
> > We are using 32-bit on 5L. I don't think Oracle 8.1.7
> > is compatible with 64-bit AIX kernel.
> >
> > --- Mladen Gogala <[EMAIL PROTECTED]> wrote:
> > > Your 1652 error means the following:
> > >
> > > 01652, 0, "unable to extend temp segment by %s
> > > in tablespace %s"
> > > // *Cause:  Failed to allocate an extent for temp
> > > segment in tablespace.
> > > // *Action: Use ALTER TABLESPACE ADD DATAFILE
> > > statement to add one or more
> > > // files to the tablespace indicated.
> > >
> > > If everything else is the same, I would say that the
> > > new OS is using a
> > > different OS filesystem block size. That would mess
> > > up all your file sizes,
> > > allocations and alike. You are probably using 64 bit
> > > JFS on 5L, straight up
> > > from 32-bit 4.3.3?
> > >
> > >
> > > On 2003.07.27 00:39, Saira Somani wrote:
> > > > Hi Gurus,
> > > >
> > > > After upgrading the OS from AIX 4.3.3 to 5.1, our
> > > > Oracle 8i instance is dishing out plenty of
> > > ORA-1652
> > > > which means that our application cannot commit any
> > > > transactions (or so I am finding in my tests).
> > > >
> > > > Help. The warehouse needs to be in production
> > > > tomorrow 8AM!
> > > >
> > > > Thanks,
> > > > Saira
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > > --
> > > > Author: Saira Somani
> > > >   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).
> > > >
> > >
> > > --
> > > Mladen Gogala
> > > Oracle DBA
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: Mladen Gogala
> > >   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: Saira Somani
> >   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).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
>   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

Re: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1

Hi!

How did you check that temp ts was filling up? From v$temp_space_header?

Could it be that some execution plans have changed that huge sorts (of
cartesian joins) are done instead of index scans etc..

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Sunday, July 27, 2003 6:34 PM


> As suggested, we checked all the tablespaces.
>
> TEMP space was increased from 2GB to 4GB and then to
> 6GB.
>
> We ended up bouncing the Oracle instance each time
> TEMP filled up because we really had nothing to lose.
>
> After doing this 3 maybe 4 times, we ran the same
> scenario in the application and this time (and a few
> times after that to be sure), TEMP did not fill up -
> only used 0.5%. Then we decreased TEMP to 2GB and
> everything still seemed to work.
>
> We had opened a severity level 1 TAR with Oracle and
> have now reduced it to level 2 because we don't have a
> case to present to them now.
>
> We don't really understand why this was happening or
> how the problem got fixed. Since today is a production
> day, we'll probably find out if it happens again. I
> know we weren't imagining the entire thing!
>
> Thank you all for your suggestions. They were
> extremely helpful.
> Saira
>
> --- John Blake <[EMAIL PROTECTED]> wrote:
> > May not necessarily mean TEMP tablespace... Check
> > all of your tablespaces
> > for available free space, that is contiguous
> > freespace.
> >
> > -Original Message-
> > Guido Konsolke
> > Sent: Sunday, July 27, 2003 3:04 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Hi Saira,
> >
> > maybe temp tablespace isn't large enough.
> > did you try what the doctor suggests?
> > Did you enlarge the temp seg?
> > 8-))
> >
> > hth,
> > Guido
> >
> > <<< [EMAIL PROTECTED] 27.07. 05.54 >>>
> > Hi Gurus,
> >
> > After upgrading the OS from AIX 4.3.3 to 5.1, our
> > Oracle 8i instance is dishing out plenty of ORA-1652
> > which means that our application cannot commit any
> > transactions (or so I am finding in my tests).
> >
> > Help. The warehouse needs to be in production
> > tomorrow!
> >
> > Thanks,
> > Saira
> > --
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Guido Konsolke
> >   INET:
> > [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California-- Mailing list and web
> > hosting services
> >
> -
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > also send the HELP command for other information
> > (like subscribing).
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: John Blake
> >   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: Saira Somani
>   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).

Re: Set Role in Trigger

Technically possible, but prpbably not practical.

You have to create several views for each of the users; possible - but may
become unmaintainable. FGAC may be a btter maintanable option, IMHO.

Arup Nanda
www.proligence.com





- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Sunday, July 27, 2003 2:44 PM


> This is probably too kludgy or simple-minded, or non-maintainable, but is
it technically possible?
>
>  1) Create a series of views that subset
> the actual tables, according to the rules
> you've got about who the viewer is & what
> year(s) they've selected in the Users table.
>
>  2) Redefine the public synonyms so that they
> point to your views rather than the base
> tables.
>
> Cheers,
>
> -Roy
>
> Roy Pardee
> Programmer/Analyst/DBA
> SWFPAC Lockheed Martin IT
> Extension 8487
> -Original Message-
> Sent: Sunday, July 27, 2003 11:24 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Jack,
>
> Question - why do you want to use System triggers to change roles? I don't
see how you can define an event for this trigger to be fired. If the event
is the updating of record in USERS table, who updates it? Obviously not the
user himeself; then the whole system becomes insecure to the core.
>
> Your choice of using a role to allow access to a specific yesr is sound
design. I assume each year is stored in separate tables. If that is not
correct, i.e. the record for all years are stored in a single table, then
roles will not help you, you need to enable FGAC using something called
application context.
>
> Scenario 1: Each year's data is stored in a seprate table.
>
> You would create roles, but not normal roles. The roles should be
identified by a procedure. This procedure is owned by SYS or some other
secued schema. Inside this procedure you would assign a value to the
application context attribute called user_role, which is set via
dbms_Session.set_context. All the users are revoked execute priv on
dbms_session; so they will never be able to call this procedure directly.
The only way they can do it is by calling the trusted procedure you have
defined for that role.
>
> Scenario 2: All the tables have data for all the years.
>
> Inthis case you will have to use FGAC; but the FGAC policy will have to
depend on the application context you defined earlier. You wil define
another context attribute called school_year, whic is again set by the
trusted procedure of the role. Since the user does not have the privilege to
call dbms_Session, he will not be able to set the value of this attrbute to
any other year at will.
>
> Summary:
>
> You will define several roles ideintified by procedure. All these roles
are granted to the user but none is a default role.
>
> When a user logs in, all roles assigned to him are disabled, since none is
a default role. Then he calls the procedure set_role(), no arguments. Inside
the procedure set_role(), you will read the users table, see the role the
user is supposed to have, enable this role via dbms_Session.set_role and
then set the application context, if any.
>
> Since the user does not have execute privs on dbms_session, he will not be
able to set the app context.
>
> Since the role is identified by a procedure, i.e. set_role(), the user
will not be able to set the role himself using "SET ROLE" command in
sql*plus. The only way he can do that is by calling the set_role procedure.
>
> If the user does not call the procedure, none of the roles are enabled;
therefore he will not be able to do anything.
>
> In other words, you have a secured system.
>
> As an added bonus: you will have the application context attribute you can
use any way you want to use. Right now you can use it for FGAC; but later
when you are in 9i, you can use it in other cooler features such as Fine
Grained Auditing.
>
> HTH.
>
> Arup Nanda
> www.proligence.com
> - Original Message -
> To: Multiple recipients of list ORACLE-L
> Sent: Sunday, July 27, 2003 11:59 AM
>
>
>
> Short form of my question:
> How can I enable a Role for a User within a database trigger (owned by
another Schema) on a table owned by yet another Schema?
> - The M's I've RTF'd indicate that a trigger (and any procedure it calls)
can never execute with Invoker's Rights.
> - I can't find a way to execute Set Role for a User as another User, say,
System.
> - I'm stuck.
> - Environment: 8.1.7 on Win2k and HP-UX.
>
> Longer form of my question:
> I'm in the process of adding extra security features to our 3rd Party
Student Information System, whose code I can't touch.  I've successfully
implemented FGAC to keep Users at a School from accessing info.at other
schools.  Now I need to limit which School Year's data they can update
(Past, Current, Next).  The Application grants Sel, Ins, Upd, Del on its
tables via a Role, so I thought I'd just switch Roles when the User switched
School Years (via updating her record in a Users table).  Seemed like a good
idea, b

Re: 2 small tables - Query takes 3 hours

2003-07-27 Thread Reginald . W . Bailey

Sam:

The proper advice is not to do it. Also, warn the vendor that what they are
doing will possibly void the support contract with Oracle for any of their
customers that use their softwarre.  Ask Oracle about it to get their
opinion, but I'm sure they will tell you that if you alter the Data
Dictionary all bets are off.
You should admonish the vendor for taking liberties with the Data
Dictionary and warn your employer that your Oracle support might be voided.
As a rule, I always insist on looking at the vendors installation scripts
and documentation. I never let them use the SYS account for installation
unless  it is absolutely necessary, and even then I try to determine if it
will work with another userid.  Any vendor that insists on using SYS's
schema and altering SYS's objects obviously is not used to installing into
Oracle and I would be wary of them. I encountered a vendor like that who
was used to installing into Microsoft SQL Server.  He wanted to use SYS
under Oracle believing it was the correct counterpart to the Database Owner
in SQL Server. I wouldn't allow them to use it and I wound up rewriting
most of their installation routine for them for the Oracle database. I
suggested that they give our company a break on the price since we had to
modify their product for them.  (I also told our legal and purchasing
department , in case they wanted to arrange compensation or royalties from
the vendor.)

As the DBA you have to put your foot down and let everyone know what the
consequences are for altering the Data Dictionary for one application.
Naturally it will have to be in the instance by itself.  If you use OEM or
some other tool, there is the possiblity that altering the Data Dictionary
might cause these tools to not work properly.  There are some serious
ramifications for doing what the vendor wanted.

As for some practical advice, try updating the statistics with DBMS_STATS,
and if possible, run the Oracle Expert, part of Oracle Enterprise Manager,
against the instance while the query or report is running.

Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
[EMAIL PROTECTED]

[EMAIL PROTECTED]  

s.net.ae To: [EMAIL PROTECTED] 

Sent by: cc:   

[EMAIL PROTECTED]   Subject: Re: 2 small tables - Query 
takes 3 hours   
ity.com

07/27/2003 

04:29 AM   

Please respond 

to ORACLE-L

This view is part of the upgrade script provided by the Vendor
(duh):

I've been DBA'ing for 7 years in DB2 and Oracle and messing
around with a SYS view is obviously not done- .

 I was looking for some proper advise as Ive never seen anything
like it before.

Sam

- Original Message -
Date: Sunday, July 27, 2003 11:39 am

> People should not play with the SYS schema, period. No ifs, no
buts,
> it just isn't done. You are on your own, pal. If I were your employer,
> you would have hit the road by now. Whatever application that
Maximo
> thing is, creating objects owned by SYS is simply not acceptable.
> It's guys like you that give database administrators a bad name.
>
>
> On 2003.07.27 01:29, [EMAIL PROTECTED] wrote:
> > Unix Solaris 8 and Oracle 8.1.7.4
> >
> > As part of an upgrade to the Maximo application I run a join on 2
> > tables:
> >
> > select count(*) from sys.syske

Re: Set Role in Trigger

Hi!
> This is probably too kludgy or simple-minded, or non-maintainable, but is
it technically possible?
>
>  1) Create a series of views that subset
> the actual tables, according to the rules
> you've got about who the viewer is & what
> year(s) they've selected in the Users table.
>
>  2) Redefine the public synonyms so that they
> point to your views rather than the base
> tables.

Oracle Apps actually works that way, that a user gets assigned an
organization id org_id when he logs on (not using trigger, from client side
instead) and uses views which restrict queries & dml by org_id. This is
based on session environment variables, I believe it's better in performance
point of view, if we would have to scan a "privileges" table during every
select on any table, it could become the bottleneck...

Tanel.


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

Top DW requirements needed

2003-07-27 Thread Rick_Cale





Hi DBAs,

I am very new to data warehouse and I am looking for bullet list, URLs,
etc. that list what are the essential data warehousing requirements
that needs identifying.  I know you obviously have to understand the
business prior to identifying requirements.  I assume some of the
requirements are

1.  Understand business process
2.  Identify what information the Users need to better accomplish their job
such as user objectives and challenges
3.  Identify data experts, where data resides,etc.

If anyone has a formal list of MUST or suggested requirements I would like
to see them.

Thanks
Rick

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

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

Re: Set Role in Trigger

I am not an expert on Oracle Apps, but those "session environment variables"
are probably application context attributes I mentioned earlier. They can
also be implemented by a package global variable; but there is no security
in that; the user will be able to set the variable in anyway he wants. Yes,
it is better from the performance point, too.

Arup Nanda
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Sunday, July 27, 2003 4:19 PM


> Hi!
> > This is probably too kludgy or simple-minded, or non-maintainable, but
is
> it technically possible?
> >
> >  1) Create a series of views that subset
> > the actual tables, according to the rules
> > you've got about who the viewer is & what
> > year(s) they've selected in the Users table.
> >
> >  2) Redefine the public synonyms so that they
> > point to your views rather than the base
> > tables.
>
> Oracle Apps actually works that way, that a user gets assigned an
> organization id org_id when he logs on (not using trigger, from client
side
> instead) and uses views which restrict queries & dml by org_id. This is
> based on session environment variables, I believe it's better in
performance
> point of view, if we would have to scan a "privileges" table during every
> select on any table, it could become the bottleneck...
>
> Tanel.
>
>
> --
> 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: 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).

Re: Set Role in Trigger

Hi!

Yep, security wise this solution was not good, Apps uses acutally
dbms_application_info.set_client_info procedure (which sets client_info
column i v$session). That's quite old mechanism, but yeah, one could set
anything for it's value (although IIRC, Apps user had to execute it through
fnd_application_info package, which had some additional checks in it).

Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, July 28, 2003 1:59 AM


> I am not an expert on Oracle Apps, but those "session environment
variables"
> are probably application context attributes I mentioned earlier. They can
> also be implemented by a package global variable; but there is no security
> in that; the user will be able to set the variable in anyway he wants.
Yes,
> it is better from the performance point, too.
>
> Arup Nanda
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Sunday, July 27, 2003 4:19 PM
>
>
> > Hi!
> > > This is probably too kludgy or simple-minded, or non-maintainable, but
> is
> > it technically possible?
> > >
> > >  1) Create a series of views that subset
> > > the actual tables, according to the rules
> > > you've got about who the viewer is & what
> > > year(s) they've selected in the Users table.
> > >
> > >  2) Redefine the public synonyms so that they
> > > point to your views rather than the base
> > > tables.
> >
> > Oracle Apps actually works that way, that a user gets assigned an
> > organization id org_id when he logs on (not using trigger, from client
> side
> > instead) and uses views which restrict queries & dml by org_id. This is
> > based on session environment variables, I believe it's better in
> performance
> > point of view, if we would have to scan a "privileges" table during
every
> > select on any table, it could become the bottleneck...
> >
> > Tanel.
> >
> >
> > --
> > 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: 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: 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).

RE: 8i ORA-1652 after upgrading from AIX 4.3.3 to 5.1

2003-07-27 Thread M Rafiq

1)for temp usage check v$sort_usage while application/job running. It is 
some code which may be resulting in cartesian join using hash join.

2) check for parallel degree in tables or indexes (dba_tables or 
dba_indexes) 'select table_name,degree from dba_tables where degree > 1; or 
same for indexes. If degree is > 1 then lot of temp segments are required 
for sorting.

3-Same may result due use of parallel in hint.

4) In short it is application/code related and that is to be fixed first. 
Beside for DW house application temp space may require big space for large 
sorts may be 6-8GB.

HTH,

Regards
Rafiq




Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Sun, 27 Jul 2003 07:34:24 -0800
As suggested, we checked all the tablespaces.

TEMP space was increased from 2GB to 4GB and then to
6GB.
We ended up bouncing the Oracle instance each time
TEMP filled up because we really had nothing to lose.
After doing this 3 maybe 4 times, we ran the same
scenario in the application and this time (and a few
times after that to be sure), TEMP did not fill up -
only used 0.5%. Then we decreased TEMP to 2GB and
everything still seemed to work.
We had opened a severity level 1 TAR with Oracle and
have now reduced it to level 2 because we don't have a
case to present to them now.
We don't really understand why this was happening or
how the problem got fixed. Since today is a production
day, we'll probably find out if it happens again. I
know we weren't imagining the entire thing!
Thank you all for your suggestions. They were
extremely helpful.
Saira
--- John Blake <[EMAIL PROTECTED]> wrote:
> May not necessarily mean TEMP tablespace... Check
> all of your tablespaces
> for available free space, that is contiguous
> freespace.
>
> -Original Message-
> Guido Konsolke
> Sent: Sunday, July 27, 2003 3:04 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Saira,
>
> maybe temp tablespace isn't large enough.
> did you try what the doctor suggests?
> Did you enlarge the temp seg?
> 8-))
>
> hth,
> Guido
>
> <<< [EMAIL PROTECTED] 27.07. 05.54 >>>
> Hi Gurus,
>
> After upgrading the OS from AIX 4.3.3 to 5.1, our
> Oracle 8i instance is dishing out plenty of ORA-1652
> which means that our application cannot commit any
> transactions (or so I am finding in my tests).
>
> Help. The warehouse needs to be in production
> tomorrow!
>
> Thanks,
> Saira
> --
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Guido Konsolke
>   INET:
> [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> San Diego, California-- Mailing list and web
> hosting services
>
-
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: John Blake
>   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: Saira Somani
  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).
_
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

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

Re: Set Role in Trigger

2003-07-27 Thread JApplewhite

Arup,

Thanks for your reply. As I said in my memo, I really want the User's Role to be changed from within a Trigger on a table, not a System Trigger. Actually, it doesn't matter, since no database trigger can be defined with Invoker's Rights. That means that any trigger in which I issue execute DBMS_Session.Set_Role would fail, since Roles are disabled in any PL/SQL module executed with Definer's Rights - which is all triggers. Yes, this App. updates the User table when the User chooses to change to a different set of Schoolyear tables, but it creates no system insecurity. Why would it?

Using an Applicatioin Context Variable won't work, since the App. Ctx. Var. User_Role is not really a Role to which I could grant object privileges. My situation is your Scenario #1. I understand Application Context and am using it in the current FGAC implementation I've created for this app. However, I can't see how I can substitute an App. Ctx. Var. for a "real" Role. Actually, you should review the docs on Application Context. No User can set his Application Context Variables by calling DBMS_Session.Set_Context, since each Application Context is "registered" to its specific package, which should be inaccessible to the User.

Roy suggested views, which might be an option for a well-designed, low user-count app, but this app. (remember, 3rd Party, we didn't design it!) has over 50,000 tables. Also, we have several thousand teachers and administrators who access the system. Multiply the two and you can see that millions of views are not feasible.

I've really got to find a way to change a User's "real" Role from within a Trigger. It can't be the After_Logon Trigger, since the User's Role must be changeable up to many times during a single Session. Also, remember that I can't make a single modification to the Application's code.

Unless someone comes up with something else, I'll experiment with having the table Trigger owned by System and executing "Alter User... Default Role..." as a Dynamic SQL statement to see if that works to change the User's Role.

Thanks.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]

"Arup Nanda" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/27/2003 01:24 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Set Role in Trigger

Jack,
Question - why do you want to use System triggers to change roles? I don't see how you can define an event for this trigger to be fired. If the event is the updating of record in USERS table, who updates it? Obviously not the user himeself; then the whole system becomes insecure to the core.
Your choice of using a role to allow access to a specific yesr is sound design. I assume each year is stored in separate tables. If that is not correct, i.e. the record for all years are stored in a single table, then roles will not help you, you need to enable FGAC using something called application context.
Scenario 1: Each year's data is stored in a seprate table.
You would create roles, but not normal roles. The roles should be identified by a procedure. This procedure is owned by SYS or some other secued schema. Inside this procedure you would assign a value to the application context attribute called user_role, which is set via dbms_Session.set_context. All the users are revoked execute priv on dbms_session; so they will never be able to call this procedure directly. The only way they can do it is by calling the trusted procedure you have defined for that role.
Scenario 2: All the tables have data for all the years.
Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will.
Summary:
You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role.
When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any.
Since the user does not have execute privs on dbms_session, he will not be able to set the app context.
Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using "SET ROLE" command in sql*plus. The only way he can do that is by calling the set_role procedure.
If the user does not call the procedu

session_cached_cursors

2003-07-27 Thread bulbultyagi

Hello list ,
Performance tuning docs say that : "To enable the caching of session
cursors you must set the initialization parameter set
session_cached_cursors.  You can also enable it dynamically with alter
session set"

But neither of the following works on 9.2.0.1.0 enterprise edition (win32)
alter system set session_cached_cursors=10 scope=memory;
alter system set session_cached_cursors=10 scope=spfile;
alter system set session_cached_cursors=10 scope=both;

However this works
alter session set session_cached_cursors=10 ;

Why ?
...

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

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

RE: Top DW requirements needed

2003-07-27 Thread DENNIS WILLIAMS

Rick - Check out http://www.ralphkimball.com. He has written articles for
years on this topic and has them posted on his web site. His books are also
great. 
One thing that is different from the normal applications is the user
participation. If you develop a payroll application, for example, the users
may dislike it, but they have no choice whether to use it. A data warehouse
on the other hand is a supplement to the users. If a marketing person finds
your DW hard to use, they will just ignore it. 

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


-Original Message-
Sent: Sunday, July 27, 2003 4:09 PM
To: Multiple recipients of list ORACLE-L






Hi DBAs,

I am very new to data warehouse and I am looking for bullet list, URLs,
etc. that list what are the essential data warehousing requirements
that needs identifying.  I know you obviously have to understand the
business prior to identifying requirements.  I assume some of the
requirements are

1.  Understand business process
2.  Identify what information the Users need to better accomplish their job
such as user objectives and challenges
3.  Identify data experts, where data resides,etc.

If anyone has a formal list of MUST or suggested requirements I would like
to see them.

Thanks
Rick

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

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

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

Re: Set Role in Trigger

Jack,
After theclarification, my recommendation actually
holds more ground - I hope you can see that in the explanation here. Creating
views are not practical due to the number of users and year combinations
involved. Since you are using FGAC, that is precisely you should
do.

Actually, you should review the docs on Application
Context. No User can set his Application Context Variables by calling
DBMS_Session.Set_Context, since each Application Context is "registered" to its
specific package, which should be inaccessible to the User.
I am aware of that; in fact that is what makes this
process more secure.
I am writing a book to be published
in September that describes exactly how this is done; as a matter of
fact, the example described mimics your case. Unfortunately as this stage I will
violate the contract with the publisher if I divulge too much details; so I have
to be succinct!
I am assuming you have 10 years of data in table
such as year1, year2, etc. When a user logins, he may be given a role year1,
which has select privileges on year1 only.
1. create a procedure set_role (p_user_id). this
procedure selects from some table that stores the username and role allocated.
from the role allocated, it calls dbms_session.set_role (p_role_name); you can
also specify dbms_session.set_context
2. create a role year1 identified by set_role. Note
"identified by set_role"; this is important. Similarly create all roles year2,
year3, etc.
3. grant select on year1 to year1,
etc.
4. create the user myuser
5. grant year1, year2 to myuser.
6. alter user myuser default role
none;
7. create a procedure validate_me (p_username,
p_password) that returns only YES or NO based on the username/password
combination. Inside this procedure call the set_role and set_context
procs.
8. When the user calls this validate_me procedure,
the application context as well as role can be set.
I wish it were more descriptive; but
..

Using an Application Context Variable won't work,
since the App. Ctx. Var. User_Role is not really a Role to which I could grant
object privileges

the app ctx attr value is simply for the role
name. The actual role setting is done by set_role procedure.
The only thing I don't understand is why this has
to be called from a DML trigger on table. Which table's trigger will trigger
this role setting event; could you elaborate on that?
I really HTH.
Arup Nanda
www.proligence.com

- Original Message -
From:
[EMAIL PROTECTED]

To: Multiple recipients of list ORACLE-L

Sent: Sunday, July 27, 2003 8:59 PM
Subject: Re: Set Role in Trigger
Arup, Thanks for your reply. As I said in my memo, I
really want the User's Role to be changed from within a Trigger on a table,
not a System Trigger. Actually, it doesn't matter, since no database
trigger can be defined with Invoker's Rights. That means that any
trigger in which I issue execute DBMS_Session.Set_Role would fail, since Roles
are disabled in any PL/SQL module executed with Definer's Rights - which is
all triggers. Yes, this App. updates the User table when the User
chooses to change to a different set of Schoolyear tables, but it creates no
system insecurity. Why would it? Using an Applicatioin Context Variable won't work, since the App. Ctx.
Var. User_Role is not really a Role to which I could grant object privileges.
My situation is your Scenario #1. I understand Application Context
and am using it in the current FGAC implementation I've created for this app.
However, I can't see how I can substitute an App. Ctx. Var. for a "real"
Role. Actually, you should review the docs on Application Context.
No User can set his Application Context Variables by calling
DBMS_Session.Set_Context, since each Application Context is "registered" to
its specific package, which should be inaccessible to the User.
Roy suggested views, which might be an
option for a well-designed, low user-count app, but this app. (remember, 3rd
Party, we didn't design it!) has over 50,000 tables. Also, we have
several thousand teachers and administrators who access the system.
Multiply the two and you can see that millions of views are not
feasible. I've really got to find
a way to change a User's "real" Role from within a Trigger. It can't be
the After_Logon Trigger, since the User's Role must be changeable up to many
times during a single Session. Also, remember that I can't make a single
modification to the Application's code. Unless someone comes up with something else, I'll experiment with
having the table Trigger owned by System and executing "Alter User... Default
Role..." as a Dynamic SQL statement to see if that works to change the User's
Role. Thanks.Jack C.
ApplewhiteDatabase AdministratorAustin Independent School
DistrictAustin, Texas512.414.9715 (wk)512.935.5929
(pager)[EMAIL PROTECTED]

RE: Is there any data dictionary to check out the creation statem

2003-07-27 Thread chuan . zhang

Thanks, Chao Zhu.

 Since the "query" column in user_mviews is long data type, when I select
query from user_mview, I can only see part of select statement. Even "set
linesize" doesn't help.

 One more, what I want is to compare two select statements in "querie"
column. But with "query" long data type, I could not do this. 

I just wondering why Oracle set this column as varchar2.


Chuan

-Original Message-
Sent: Friday, 25 July 2003 18:19
To: Multiple recipients of list ORACLE-L
statements


SQL> select query from user_mviews where mview_name='MV_END_ART';

QUERY


SELECT * FROM ARTICLESCATALOG
WHERE END_DATE_DTM > SYSDATE
AND END_DATE_DTM 
Sent: Friday, July 25, 2003 2:24 PM


> Hi, All,
>
>  Is there any data dictionary in Oracle to check out creation statements
of
> materialized view? Something like the user_source view about object
> creation. I have checked user_mviews, user_mview_joins, etc, but I haven't
> got one.
>
> TIA
>
> Chuan
> Important: This transmission is intended only for the use of the addressee
> and may contain confidential or legally privileged information.  If you
are
> not the intended recipient, you are notified that any use or dissemination
> of this communication is strictly prohibited.  If you receive this
> transmission in error please notify the author immediately by telephone
and
> delete all copies of this transmission together with any attachments.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>

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

Important: This transmission is intended only for the use of the addressee
and may contain confidential or legally privileged information.  If you are
not the intended recipient, you are notified that any use or dissemination
of this communication is strictly prohibited.  If you receive this
transmission in error please notify the author immediately by telephone and
delete all copies of this transmission together with any attachments.

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

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

Re: Index Usage ?!

Hi Tanel,

thanks a lot for your wonderful explanation.
sorry to pester u you again with my doubts on 
the first day of the week.

My only doubt is:

1.After analyzing the index,CBO doesn't read the index because
it is costlier in terms of I/O and other resources.

2.But without analyzing,CBO uses the index now,which is supposed 
to be coslty,and hence incurs the same I/O and other resources .

3.when the role of CBO is over,the h/w resources comes into play 
to read the index.even now it reads the full index and the 
response is faster.how is it so ?   

hope i am clear in telling u what i had understood ?!
plz. excuse me if i'm sound silly and stupid.

of the above 3 points,where am i wrong ?

Regards,
Jp.

25-7-2003 21:24:45, "Tanel Poder" <[EMAIL PROTECTED]> wrote:

>Hi!
>
>Your original post shows that when index was not analyzed, optimizer 
used
>default statistics and found out that index scan is quite cheap. 
Also, no
>sorting had to be done, since descending index range scan could be 
used to
>satisfy your order by clause.
>
>Whe index was analyzed, then CBO actually saw, that index isn't that 
good as
>defaul values showed, the number of leaf blocks was higher and also 
the
>clustering factor was probably much-mugh higher, meaning that for any
>non-unique index key value it has to visit several different data 
blocks to
>get all matching rows. That means lot's of IOs. Eventually CBO 
decided that
>it's cheaper to do let say 1600 multiblock (plus one segment header +
>possibly bitmap) reads directly and scan through the whole table than 
to
>traverse through index branches, scan leaf blocks and visit every 
data block
>individually.
>
>So, with not-analyzed index, CBO had nothing else to do, than to be
>super-optimistic about the index (#LB, CLUF) thus using index in your 
case.
>But when analyzed, CBO had accurate data, but did make bad decision 
because
>too pessimistic values for optimizer_index_cost_adj and
>optimizer_index_caching. You should set them at session level for 
testing to
>let say 50 and 90, but read the document below for understanding 
those
>parameters.
>
>http://www.evdbt.com/SearchIntelligenceCBO.doc
>
>Cheers,
>Tanel.

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

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

Re: search archives

David,

search at www.orafaq.net
though late , hope i helped u.

Jp.

26-07-2003 20:34:25, "Ehresmann, David" <[EMAIL PROTECTED]> wrote:
>How do I search the archives of this list?
>thanks,

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

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

RE: Is there any data dictionary to check out the creation statem

2003-07-27 Thread M Rafiq

use set long 4000 or whatever instead of set linesize and try.

Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Sun, 27 Jul 2003 18:19:24 -0800
Thanks, Chao Zhu.

 Since the "query" column in user_mviews is long data type, when I select
query from user_mview, I can only see part of select statement. Even "set
linesize" doesn't help.
 One more, what I want is to compare two select statements in "querie"
column. But with "query" long data type, I could not do this.
I just wondering why Oracle set this column as varchar2.

Chuan

-Original Message-
Sent: Friday, 25 July 2003 18:19
To: Multiple recipients of list ORACLE-L
statements
SQL> select query from user_mviews where mview_name='MV_END_ART';

QUERY


SELECT * FROM ARTICLESCATALOG
WHERE END_DATE_DTM > SYSDATE
AND END_DATE_DTM 
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2003 2:24 PM
> Hi, All,
>
>  Is there any data dictionary in Oracle to check out creation statements
of
> materialized view? Something like the user_source view about object
> creation. I have checked user_mviews, user_mview_joins, etc, but I 
haven't
> got one.
>
> TIA
>
> Chuan
> Important: This transmission is intended only for the use of the 
addressee
> and may contain confidential or legally privileged information.  If you
are
> not the intended recipient, you are notified that any use or 
dissemination
> of this communication is strictly prohibited.  If you receive this
> transmission in error please notify the author immediately by telephone
and
> delete all copies of this transmission together with any attachments.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: zhu chao
  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).
Important: This transmission is intended only for the use of the addressee
and may contain confidential or legally privileged information.  If you are
not the intended recipient, you are notified that any use or dissemination
of this communication is strictly prohibited.  If you receive this
transmission in error please notify the author immediately by telephone and
delete all copies of this transmission together with any attachments.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail

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

Problem with Autotrace ?

Guys,

SET AUTOTRACE works fine on the Database server machine.
the database server ENV is 9.2.0.3/Win2k AS - With SP3.

SQL>connect cti/cti;
SQL>set autot trace
SQL>

whereas when i try the same from a different machine,i get the error below.
{ this client machine has 9.2.0.2/Win2K server - with SP3 }

SQL>connect cti/[EMAIL PROTECTED];
SQL>set autot trace
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

.seems to be unusual.isn't it ?
had anyone faced the same problem ? 
can someone help me out.

Regards,
Jp.








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

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

RE: RMAN/Veritas duplicate db article

2003-07-27 Thread Babette Turner-Underwood

Didn't get a change to read it yet,
but if it makes you feel better,
I REALLY DID bookmark it to read later,
when I thought it might apply to me.

Currently in the big blue world
(Oracle on the mainframe) and Veritas is just
a pleasant memory :-(

Babette

-Original Message-
Jared Still
Sent: Sunday, July 27, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L

Hmm... absolutely no responses.

Perhaps a bit of clarification is in order.

I'm interested in comments only on the content.

This was a write as a you go paper and decidely
not publishable as is.  If it seems worthwhile, I will
rewrite into something a little more acceptable than
the very rough draft that it is.

Thanks,

Jared

On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote:
> List,
>
> I've written a short ( compared to the length of time it took
> to do this anyway ) article on how to create a duplicate database
> on a remote server using RMAN and Veritas NetBackup.
>
> It can be found at:
>
>http://www.tinyurl.com/hu4r
>
> or at this one if you can't access tinyurl.com:
>
>
>
http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita
>s%20NetBackup%20and%20Oracle%20RMAN.doc
>
> It's kinda rough, so suggestions, comments, criticisms are welcome.
>
> Thanks,
>
> Jared

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

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

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

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

RE: RMAN/Veritas duplicate db article

2003-07-27 Thread Peter . McLarty

Ok I will go one better and say I actually did have a look at it at first 
glance it looked a bit like my first effort of dealing with RMAN, and 
Veritas, I will have a better look now I am back in the office and see if 
i can add some useful comment.

Cheers

--
=
Peter McLarty   E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane,  AustraliaMobile: +61 (0)402 094 238
Facsimile: +61 (0)7 3303 3048
=
"If people did not sometimes do silly things, nothing intelligent would 
ever
get done." 
   - Ludwig Wittgenstein
=
Mincom "The People, The Experience, The Vision"

=

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 

"Babette Turner-Underwood" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
28-07-2003 01:54 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: RMAN/Veritas duplicate db article

Didn't get a change to read it yet,
but if it makes you feel better,
I REALLY DID bookmark it to read later,
when I thought it might apply to me.

Currently in the big blue world
(Oracle on the mainframe) and Veritas is just
a pleasant memory :-(

Babette

-Original Message-
Jared Still
Sent: Sunday, July 27, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L

Hmm... absolutely no responses.

Perhaps a bit of clarification is in order.

I'm interested in comments only on the content.

This was a write as a you go paper and decidely
not publishable as is.  If it seems worthwhile, I will
rewrite into something a little more acceptable than
the very rough draft that it is.

Thanks,

Jared

On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote:
> List,
>
> I've written a short ( compared to the length of time it took
> to do this anyway ) article on how to create a duplicate database
> on a remote server using RMAN and Veritas NetBackup.
>
> It can be found at:
>
>http://www.tinyurl.com/hu4r
>
> or at this one if you can't access tinyurl.com:
>
>
>
http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita
>s%20NetBackup%20and%20Oracle%20RMAN.doc
>
> It's kinda rough, so suggestions, comments, criticisms are welcome.
>
> Thanks,
>
> Jared

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

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

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

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

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

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

Re: RMAN/Veritas duplicate db article

2003-07-27 Thread Mladen Gogala

I saved the article but not having too much experience with the database 
duplication using RMAN, I have no comment to make. I can send you the document
in the PDF format, instead of DOC. OpenOffice 1.1 has that possibility
On 2003.07.27 23:54, Babette Turner-Underwood wrote:
Didn't get a change to read it yet,
but if it makes you feel better,
I REALLY DID bookmark it to read later,
when I thought it might apply to me.
Currently in the big blue world
(Oracle on the mainframe) and Veritas is just
a pleasant memory :-(
Babette

-Original Message-
Jared Still
Sent: Sunday, July 27, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L


Hmm... absolutely no responses.

Perhaps a bit of clarification is in order.

I'm interested in comments only on the content.

This was a write as a you go paper and decidely
not publishable as is.  If it seems worthwhile, I will
rewrite into something a little more acceptable than
the very rough draft that it is.
Thanks,

Jared

On Wednesday 23 July 2003 14:29, [EMAIL PROTECTED] wrote:
> List,
>
> I've written a short ( compared to the length of time it took
> to do this anyway ) article on how to create a duplicate database
> on a remote server using RMAN and Veritas NetBackup.
>
> It can be found at:
>
>http://www.tinyurl.com/hu4r
>
> or at this one if you can't access tinyurl.com:
>
>
>
http://www.cybcon.com/~jkstill/Alternate%20Client%20Restore%20With%20Verita
>s%20NetBackup%20and%20Oracle%20RMAN.doc
>
> It's kinda rough, so suggestions, comments, criticisms are welcome.
>
> Thanks,
>
> Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Babette Turner-Underwood
  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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 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).

Problem with Autotrace - Is this a BUG ?

In continuation of my previous mail :

just now i upgraded the cleint to 9.2.0.3 ,
and tried it once again. IT WORKS 

i simply wonder why ?!
is this a bug in 9.2.0.2 ?*!#$#&*

Thanks and Regards,
Jp.

28-07-2003 12:49:24, Prem Khanna J <[EMAIL PROTECTED]> wrote:

>Guys,
>
>SET AUTOTRACE works fine on the Database server machine.
>the database server ENV is 9.2.0.3/Win2k AS - With SP3.
>
>SQL>connect cti/cti;
>SQL>set autot trace
>SQL>
>
>whereas when i try the same from a different machine,i get the error below.
>{ this client machine has 9.2.0.2/Win2K server - with SP3 }
>
>SQL>connect cti/[EMAIL PROTECTED];
>SQL>set autot trace
>SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
>SP2-0611: Error enabling STATISTICS report
>
>.seems to be unusual.isn't it ?
>had anyone faced the same problem ? 
>can someone help me out.
>
>Regards,
>Jp.

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

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

a simple xml output using stylsheet

2003-07-27 Thread rahul sharma




dear list, can anyone please point me to a simple 
example of select * from emp; query
and the output displayed in xml (using a simple 
stylesheet) ...
we have not been able to locate this on 
the net... 
 
TIA
-Rahul
 
 
The 
information contained in this email and its attachments if any may contain 
privileged and confidential information intended only for the attention of 
the recipient(s) specified. If you are not a recipient , any forwarding , 
disclosure , photocopying , distribution or use of the information in any 
way is prohibited . If you have received this email in error , please email 
us immediately on [EMAIL PROTECTED]  or contact 
us on (62 21) 522 
8775.-

RE: a simple xml output using stylsheet

2003-07-27 Thread Charu Joshi




Hi 
Rahul,
 
Have a 
look at http://otn.oracle.com/tech/xml and 
also 'Oracle Application Developers Guide - XML'. There is a utility called XSQL 
which does this for you. Here's an example of that utility cut from the above 
mentioned guide (for 8i):
 

declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin
-- set up the query context...!
queryCtx := 
DBMS_XMLQuery.newContext('select * from emp');
-- get the result..!
result := 
DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it 
in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx); -- 
you must close the query handle..
end;
/
The printClobOut procedure is :
/CREATE OR REPLACE PROCEDURE 
printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
/
Hope that helps,
Regards,
Charu.
 
  

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of rahul sharmaSent: 
  Monday, July 28, 2003 10:51 AMTo: Multiple recipients of list 
  ORACLE-LSubject: a simple xml output using 
  stylsheet
  dear list, can anyone please point me to a simple 
  example of select * from emp; query
  and the output displayed in xml (using a simple 
  stylesheet) ...
  we have not been able to locate this on 
  the net... 
   
  TIA
  -Rahul
   
   
  The 
  information contained in this email and its attachments if any may contain 
  privileged and confidential information intended only for the attention of 
  the recipient(s) specified. If you are not a recipient , any forwarding , 
  disclosure , photocopying , distribution or use of the information in any 
  way is prohibited . If you have received this email in error , please 
  email us immediately on [EMAIL PROTECTED]  
  or contact us on (62 21) 522 
  8775.-

Re: a simple xml output using stylsheet