RE: granting SELECT privilege on SYS.X$ TABLES
Someone must have created sys.x_$ views on some of the sys.x$ tables. Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of the x$ tables as well. I routinely do it for all x$ tables in my test databases and grant select to the select_catalog_role. Then I can access the x$tables without having to log on as sys. In my test databases I always At 07:14 PM 11/14/2003, you wrote: P.S. I forgot to mention that in all the databases (including the 8.1.7 databases) in which I tried this, init parameter O7_DICTIONARY_ACCESSIBILITY was set to FALSE. I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
When / why we resize/clean the temporary tablespace -- was RE:
Not exactly correct. It is 1 single segment, with multiple extents. The
extents
are reused. The segment is never dropped-and-recreated unless you
(a) Stop and start the database instance [it is then created on the
first request
for a temporary segment]
(b) If it is NOT a TEMPORARY Tablespace but a Tablespace .. TEMPORARY,
you issue an ALTER TABLESPACE ... DEFAULT STORAGE ... command
Now why should we worry about the Temporary Segment ?
Of the 60 odd databases I have there are 2 databases [on in 8.1.7 where I am
not using a TEMPORARY TABLESPACE but a TABLESPACE .. TEMPORARY,
and therefore, I can use the ALTER TABLESPACE .. DEFAULT STORAGE ... command
and the other in 9.2 where I am using a TEMPORARY TABLESPACE with TEMPFILEs]
where at least once a month the Tablespace which is usually 2GB [and 400MB
used] goes to 8GB
[or, as I found in a third 9.2 database this week 24GB], the file system is
full
and users may start getting errors. Now, the Unix Administrator does have
a CRON job
checking all the file systems of his 200+ servers, once-an-hour, I have a
CRON job checking all
the tablespaces of the 60 databases every hour but it takes much less than an
hour for some user writing a wonderful SQL query to take Temporary
Tablespace
out from 2GB to 8GB [or 24GB !].
It is in those cases, that I need to know how to bring my Temporary
Tablespace usage
down.
{Of course, I do know that if that wonderful SQL query user has exited, then
he has released all the 8GB or 24GB of extents but my Unix administrator
asks me why I am using so much space and what he should do about the alerts
that the filesystem is 90+% full. If I keep the MAXSIZE of the files too low,
it is quite likely that more than one user gets errors trying to allocate
extents while the
wonderful SQL query user is still around in the database}.
I also issue the ALTER TABLESPACE ... DEFAULT STORAGE .. command in the
daily backup job to drop the Temporary segment when it is not in use.
Yes, there is a cost associated with reallocating extents but this is only
once a day.
If the NEXT is appropriate sized, few user sessions would be allocating
more than
one or two new extents the first time round.
Hemant
At 10:09 AM 14-11-03 -0800, you wrote:
[EMAIL PROTECTED] scribbled on the wall in glitter crayon:
I got the impression that the poster was thinking segments allocated
in temp tablespace must be released, cleaned out, blown away, etc.
before something else can come along and use the space.
i was always under the impression that you didn't need to do anything to the
temp tablespace. that the segments were just reused as needed. am i behind
the times here?
--
Bill Shrek Thater ORACLE DBA
I'm going to work my ticket if I can... -- Gilwell song
[EMAIL PROTECTED]
Shift to the left! Shift to the right! Pop up, push down, byte, byte,
byte!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thater, William
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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Re: RE: Re: Stop using SYS, SYSTEM?
Facetious, but correct. What you need is auditing. Not clipping userids. Achieves nothing. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - What I was saying is that having a different username for each DBA helps you identify the WHOM. Of course a hacker could always cut knock the DBA unconscious and prop up his head to fool an eye retina scan, à la James Bond, but by that argument any username or IP address or whatever else you use is meaningless. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: When / why we resize/clean the temporary tablespace -- was RE:
Hemant
Why don't you get resolve the issue of wonderful SQL query user is still
around in the database}.
which is occupying that much tempspace? Either the query is resulting in
cartesian join or tables involved have degree 1 resulting sort type
'hash'. Check your V$sort_usage when that code runs. It is not that much
difficult to fix such codes.
Just fixing degree of tables/indexes may reduce the usage of tempspace. I
have seen this behaviour when one of index was having degree 1 and temp
usage was passing 4GB and making degree to 1 the temp usage went down to
less than 100M.
For checking degree
select table/index_name , degree from dba_tables/indexes where degree 1;
HTH
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Sat, 15 Nov 2003 00:49:24 -0800
Not exactly correct. It is 1 single segment, with multiple extents. The
extents
are reused. The segment is never dropped-and-recreated unless you
(a) Stop and start the database instance [it is then created on the first
request
for a temporary segment]
(b) If it is NOT a TEMPORARY Tablespace but a Tablespace .. TEMPORARY,
you issue an ALTER TABLESPACE ... DEFAULT STORAGE ... command
Now why should we worry about the Temporary Segment ?
Of the 60 odd databases I have there are 2 databases [on in 8.1.7 where I am
not using a TEMPORARY TABLESPACE but a TABLESPACE .. TEMPORARY,
and therefore, I can use the ALTER TABLESPACE .. DEFAULT STORAGE ... command
and the other in 9.2 where I am using a TEMPORARY TABLESPACE with TEMPFILEs]
where at least once a month the Tablespace which is usually 2GB [and 400MB
used] goes to 8GB
[or, as I found in a third 9.2 database this week 24GB], the file system is
full
and users may start getting errors. Now, the Unix Administrator does have a
CRON job
checking all the file systems of his 200+ servers, once-an-hour, I have a
CRON job checking all
the tablespaces of the 60 databases every hour but it takes much less than
an
hour for some user writing a wonderful SQL query to take Temporary
Tablespace
out from 2GB to 8GB [or 24GB !].
It is in those cases, that I need to know how to bring my Temporary
Tablespace usage
down.
{Of course, I do know that if that wonderful SQL query user has exited,
then
he has released all the 8GB or 24GB of extents but my Unix administrator
asks me why I am using so much space and what he should do about the alerts
that the filesystem is 90+% full. If I keep the MAXSIZE of the files too
low,
it is quite likely that more than one user gets errors trying to allocate
extents while the
wonderful SQL query user is still around in the database}.
I also issue the ALTER TABLESPACE ... DEFAULT STORAGE .. command in the
daily backup job to drop the Temporary segment when it is not in use.
Yes, there is a cost associated with reallocating extents but this is only
once a day.
If the NEXT is appropriate sized, few user sessions would be allocating more
than
one or two new extents the first time round.
Hemant
At 10:09 AM 14-11-03 -0800, you wrote:
[EMAIL PROTECTED] scribbled on the wall in glitter crayon:
I got the impression that the poster was thinking segments allocated
in temp tablespace must be released, cleaned out, blown away, etc.
before something else can come along and use the space.
i was always under the impression that you didn't need to do anything to
the
temp tablespace. that the segments were just reused as needed. am i
behind
the times here?
--
Bill Shrek Thater ORACLE DBA
I'm going to work my ticket if I can... -- Gilwell song
[EMAIL PROTECTED]
Shift to the left! Shift to the right! Pop up, push down, byte, byte,
byte!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thater, William
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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT
SQL*Loader and rollback segment
Dear Gurus, I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? Thanks Jay -- 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: SQL*Loader and rollback segment
[EMAIL PROTECTED] wrote: Dear Gurus, I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? Thanks Jay One work around might be to create a dedicated Oracle account with the suitable rights (INSERT on the table to load) and to use a login trigger to assign the rollback segment. Another, and probably much better, way would be to have several sessions running and parallel (and hopefully assign to different rollback segments by Oracle). If your volume of data is really big, chances are that you are loading into a partitioned table. If your input data had the good taste of being made of several files, each one destined to a separate partition, would be great. Perhaps some preprocessing is required. Otherwise split your data file, be certain to have several free lists to avoid contention, and there you go. You may have to play with constraints, this is usually the price to pay to do things in parallel. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL*Loader and rollback segment
How about just committing every n rows, instead of trying to fit the whole thing into one transaction? This is why they have the ROWS= option on the command line and in the parameter file. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sat, 15 Nov 2003, Stephane Faroult wrote: [EMAIL PROTECTED] wrote: I have a problem loading data from flat file using SQL*Loader. The problem is unable to extend rollbacksegment. Is there a way to assign BIG rollback segment to SQL*Loader transaction? If not what is the work around to load huge volume of data without using TRUNCATE option? One work around might be to create a dedicated Oracle account with the suitable rights (INSERT on the table to load) and to use a login trigger to assign the rollback segment. Another, and probably much better, way would be to have several sessions running and parallel (and hopefully assign to different rollback segments by Oracle). If your volume of data is really big, chances are that you are loading into a partitioned table. If your input data had the good taste of being made of several files, each one destined to a separate partition, would be great. Perhaps some preprocessing is required. Otherwise split your data file, be certain to have several free lists to avoid contention, and there you go. You may have to play with constraints, this is usually the price to pay to do things in parallel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton 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).
