Server Administration :: Granted Privileges But Not Able To See In DBA_SYS_PRIVS Table

Jul 4, 2011

I have been granting "ALTER ANY MATERIALIZED VIEW" to a role but not able to see the same granted in DBA_SYS_PRIVS.

I have used the image for the first time, so pasting the sql prompt output as well:

SQL> select * from v$version where banner like 'Oracle%';

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
SQL> grant ALTER ANY MATERIALIZED VIEW to OPS_1ST_LINE;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee = 'OPS_1ST_LINE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
OPS_1ST_LINE CREATE SESSION NO
OPS_1ST_LINE ALTER ANY SNAPSHOT NO

SQL> commit;

Commit complete.

SQL> select * from dba_sys_privs where grantee = 'OPS_1ST_LINE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
OPS_1ST_LINE CREATE SESSION NO
OPS_1ST_LINE ALTER ANY SNAPSHOT NO

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE = 'ALTER ANY MATERIALIZED VIEW';

no rows selected..why is this grant not been shown. Is "ALTER ANY MATERIALIZED VIEW" not present for Oracle 9i database.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Trace Privileges Granted And Revoked History?

Nov 20, 2012

I have written a windows service which grants and revokes based on request. How to trace in oracle data dictionary if those privilages were granted and at what time to whom and by whom. I mean is there any table which can be queried for past privilages granted to users.

View 2 Replies View Related

Server Administration :: Privileges From One Schema To Other

Feb 10, 2012

I have 10 important schemas in my database,and i want to give only select privileges from SCHEMA PRODUSER to other 10 schemas. And also want that the new objects that are created in PRODUSER after granting the privileges are also have select privileges. Is it possible that i should directly grant select privileges from one schema to other without granting via individual objects(script to grant individual privileges from individual objects)

Details are as below:
database version: 9.2.0.8.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2

View 1 Replies View Related

Server Administration :: Privileges Required For Export

Dec 1, 2011

I have one question regarding privileges required for export.I read in a document that

Quote:
If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.

what are the system privileges required in EXP_FULL_DATABASE role to perform export objects contained in another users schema?

View 2 Replies View Related

Server Administration :: Privileges To Gather Statistics

Nov 9, 2010

What privileges is required to gather statistics of oracle database using DBMS_STATS/ANALYZE command.

View 1 Replies View Related

Server Administration :: ORA-01031 - Insufficient Privileges?

Feb 7, 2012

I can not login my db using tnsname,how can i do?

SQL> connect / as sysdba
Connected.

SQL> connect sys/sys@dup_oracl as sysdba
ERROR:
ORA-01031: insufficient privileges

View 1 Replies View Related

Server Administration :: Insufficient Privileges To Other User

May 21, 2011

WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE

ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.

View 5 Replies View Related

Server Administration :: Privileges For DROP Partition

Jun 21, 2011

I have one user CD_APP. I have one partition table CD.T_FCDR_DT. User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..

Now when I try to drop a partition, I get error as below:
-------------------------------------
SQL> show user
USER is "CD_APP"
SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES;
ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------
Do I have to grant some other privileges for this user.

View 12 Replies View Related

Server Administration :: Insufficient Privileges While Connecting (sys As Sysdba)

Apr 2, 2013

SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges

The oracle 11g installed in eucalyptus cloud..

In the same server I can connect as a different user 'd6' but not as sysdba.

View 7 Replies View Related

Server Utilities :: Privileges Error - Executing INSERT Statement For Table

Jul 9, 2013

I am using OWB to load a table which write sql loader command. When running the load i am getting below error.

SQL*Loader-643: error executing INSERT statement for table "STG_EWORK"."STG_ISF_LUCC"

I am unable to guess which privileges is missing.

My control file as below

OPTIONS (SKIP=2,BINDSIZE=50000,ERRORS=0,ROWS=200,READSIZE=65536)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE '\devora003.dev.tfl.localPDWPDW_SourceISF_LUCC_Loadfile.csv'
CONCATENATE 1
INTO TABLE "STG_EWORK"."STG_ISF_LUCC"

[code]....

View 4 Replies View Related

PL/SQL Package Can't See Tables Granted Via Role

Mar 12, 2010

A Package cannot query a table that the package owner has permission to query via a role specifically:

There are schema_1 and schema_2

schema_1 owns package_1
schema_1 has select, update, insert on schema_2 via role_1
schema_1 CAN query schema_2
package_1 cannot access schema_2

(288/22 PL/SQL: ORA-00942: table or view does not exist)

How can this be resolved?

View 6 Replies View Related

Security :: ORA-01932 - ADMIN Option Not Granted For Role

Aug 23, 2011

When i try to grant role to user , i get following error.

SQL>
SQL> GRANT MAINTAIN_TEO TO CEE_WIRELINE;
GRANT MAINTAIN_TEO TO CEE_WIRELINE
*
ERROR at line 1:
ORA-01932: ADMIN option not granted for role 'MAINTAIN_TEO'
SQL>

View 1 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

List All Users That Have Privileges On A Table

Aug 6, 2011

Is possible to list all users that have any privilege on a specific table?

View 2 Replies View Related

Server Administration :: How To Compress Table

Sep 8, 2011

There is a huge table,i want to compress it,how can i do? alter table tb_my_table compress After executed the sql,the size of table have any change.

View 2 Replies View Related

Server Administration :: Table Cannot Shrink?

Sep 26, 2011

My table can not shrink, why?

SQL> Alter Table tb_hxl_user Shrink Space Cascade;
Alter Table tb_hxl_user Shrink Space Cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

SQL> desc tb_hxl_user;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEDATE NOT NULL DATE
USERNUMBER NOT NULL VARCHAR2(13)
PROVCODE NOT NULL NUMBER

[code]...

View 9 Replies View Related

Server Utilities :: What Privileges Are Required To Perform Only Schema Level Export And Import

Feb 20, 2012

Quote:The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export and import.

what privileges are required to perform only schema level export and import?

View 3 Replies View Related

Server Administration :: Collect Table Stats?

Oct 10, 2013

We are running 11g (11.2.0.3)We have a "working table" that is empty at the beginning of the day.Then we start adding rows (insert) with a key column called STATE with a value of 100.At the same time, there are other apps that pickup data in state 100 , process that data and change that state to 200 or 300.There is also another app that pickup data in state 200 , process that data and change that state to 300 or 400.

So in summary, the data on that table is at the beginning empty, then all the rows are in state 100, they slowly move to different states (200, 300, etc) and by the end of the day, they are all in 400.

My question is what would be the best way to collect stats on this table?

I was thinking to create an hourly job to collect stats on that table:
exec dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'WORK_T

View 6 Replies View Related

Server Administration :: How To Shrink Table Size

Jan 4, 2012

I have a table: desc STG_XML

Name Null Type
------------------------------ -------- ------------------------
ENTITY_ID NOT NULL VARCHAR2(100 CHAR)
ENTITY_TYPE_ID NOT NULL NUMBER
SOURCE_ID NOT NULL VARCHAR2(512 CHAR)
XML_SCHEMA_ID NOT NULL NUMBER
JOB_ID NOT NULL NUMBER
FINGERPRINT NOT NULL VARCHAR2(100 CHAR)
ENTITY_XML_DATA CLOB()
ARCHIVED NUMBER(1)
CREATION_DATE TIMESTAMP(6)
MODIFICATION_DATE TIMESTAMP(6)
ARCHIVING_DATE TIMESTAMP(6)
CREATED_BY VARCHAR2(50 CHAR)
MODIFIED_BY VARCHAR2(50 CHAR)

The problem is that the data of the table are 40GB while on the DB the table holds 400GB! How can I shrink and reuse that space except from drop/recreate and drop/import?

The table has no initial data, so that I can play with the INITIAL parameter. Data are inserted, updated and deleted all the time. I have run DBMS_ADVISOR which recommended to SHRINK table. I have performed the shrink :

alter table STG_XML shrink space COMPACT;

but I haven't gained any space.

View 12 Replies View Related

Server Administration :: Default Partition In Table?

Oct 17, 2011

How can i check whether a partition tbale have default partition?

View 9 Replies View Related

Server Administration :: Rename A Partitioned Table?

Mar 21, 2007

i want to rename a table that has partitions.

alter table
testora.oldtablename
rename to
testora.newtablename;

ORA-14048: a partition maintenance operation may not be combined with other operations

View 2 Replies View Related

Server Administration :: How To Apply Composite Key On A Table

Jul 21, 2011

How can i apply composite key on a table?

View 2 Replies View Related

Server Administration :: How To Find Updated Table

Mar 9, 2012

how to find a table is updated and when the table is updated.

View 1 Replies View Related

Server Administration :: DDL Used To Create A Table That Is Partitioned By Day

Nov 4, 2010

DDL used to create a table that is partitioned by day, then rolled up to a month using the interval partitioning technique.

View 3 Replies View Related

Server Administration :: How To Reduce Size Of A Table

Oct 2, 2012

One of our solaris machines is running Oracle 8.0.3

A table reached the 2 Gb size and oracle failed due to the operating system file size limitation.

The information in the table is not relevant and can be deleted, but the table contains a lot of indexes.

I would like to know the best procedure to delete the information and reduce the size of the file.

View 3 Replies View Related

Server Administration :: Table Statistics For Oracle 11g

Oct 25, 2012

I want to get the stale stats for Table resides at APPS schema. Is there is any table or view exists to get the details like DBA_STALE_STATS or anything? Currently I am checking LAST_ANALYZED column from DBA_TABLES?

View 2 Replies View Related

Server Administration :: How To Track The Last Access To A Table

Oct 11, 2011

is it possible to track the last access to a table?

View 2 Replies View Related

Server Administration :: How Table Owner Is Changed

Mar 21, 2012

I got query to investigate, how table owner is changed.

SQL> conn test/test
Connected.
SQL> create table guri(name varchar2(9));

Table created.

SQL> select table_name from user_tables;

no rows selected
SQL> conn /as sysdba
Connected.
SQL> select owner,object_name,object_type from dba_objects where object_name = 'GURI';
OWNER OBJECT_NAME OBJECT_TYPE
---------- ----------- ------------
CISREPLICA GURI TABLE

View 8 Replies View Related

Server Administration :: When To Gather Table Statistics

Apr 21, 2012

Why do we gather table statistics manually ?Is it because of database performance.

I know In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.

Optimizer gathers statistics when 10% table rows have been changed.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved