SQL & PL/SQL :: Privilege Required To Gather Table Statistics?

May 14, 2011

What privilege is required to gather table statistics using dbms_stats ?

View 2 Replies


ADVERTISEMENT

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

Server Administration :: Unable To Gather Statistics Of Neither Schema Nor Table?

Jul 13, 2011

I am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.

SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

[code]....

View 17 Replies View Related

Server Administration :: Privilege Require To Gather Stats?

May 27, 2012

what privilege is required to run dbms_stats package for gathering table and schema statistics.

SQL> show user
USER is "JACK"
SQL>
SQL> select * from user_sys_privs;

no rows selected

SQL> select * from user_role_privs;

no rows selected

SQL> select * from role_sys_privs;

no rows selected

SQL> exec dbms_stats.gather_table_stats('JACK','EN1')

PL/SQL procedure successfully completed.

I revoke all the privileges from JACK user but still i am able to gather stats for a table.what privilege is require to gather stats.

View 4 Replies View Related

Replication :: Gather Statistics On Materialized Views

Jul 15, 2009

Any best way to gather statistics on Materialized Views.

View 1 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

Performance Tuning :: Gather Statistics On SYS Objects

Mar 7, 2012

Do we need to gather statistics on SYS objects and fixed objects in 11gR2 database regularly ?

I read in some article that in 10gR2, statistics for SYS objects are by default whereas in 10gR1, statistics for SYS objects are not by default using

dbms_stats.gather_database_stats(
...
gather_sys = False
...
)

View 4 Replies View Related

Exadata :: Gather Statistics In Parallel On Schema

Sep 7, 2012

I am trying to gather stats in parallel on a schema in which tables are OLTP compressed

The command I use

BEGIN
DBMS_APPLICATION_INFO.set_module ('Gather Stats', user);
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'schemaname', DEGREE=>64, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE, GRANULARITY => 'AUTO');
END;
/

I see blocker with wait event=PX DEQ: Execution Msg

Waiter with wait event= PX Deq: Execute Reply

When I run ASH report all I see is : IPC send completion sync

View 2 Replies View Related

PL/SQL :: Privilege Required For (as Of Timestamp) Clause / ORA-01031 / Insufficient Privileges

Jan 28, 2013

When I try following SQL to retrieve records in some table XXXXX in the past point in time:

select * from XXXXX as of timestamp systimestamp-(6/24);

I am receiving following error:

ORA-01031: insufficient privileges

Without "as of timestamp" clause, it does run fine.Which privilege do I have to receive for above to work?

View 3 Replies View Related

PL/SQL :: How To Grant Privilege On User1 Based On Privilege Of User2

Jun 20, 2013

I have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?

View 2 Replies View Related

Gather Stats On Table

May 26, 2011

I have doupts in gathering stats on table. I analyzed one table it took 2 hours first time.. the same table after one week later i analyzed, its got completed within 45 minutes.. I don't know exact reason why i got completed very soon. Is there any specific reason?

View 2 Replies View Related

SQL & PL/SQL :: Direct Privilege Vs Privilege Through Roles

May 30, 2010

As we all know, privileges granted to a user through role are not visible from within a stored procedure. What is the reasoning behind this design?

Moreover, privileges granted through role are visible from anonymous PL/SQL block. Why such discrimination between anonymous PL/SQL and stored PL/SQL?

View 3 Replies View Related

Performance Tuning :: Analyze Table Compute Statics To Gather Stats

Sep 11, 2012

I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.

View 1 Replies View Related

SQL & PL/SQL :: Privilege - Just Drop Special One Table?

Jul 3, 2013

There two users a and b,and the table b.test_part.And one procedure under a ,text like below:

create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;

now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!

View 5 Replies View Related

SQL & PL/SQL :: How To Check If User Has (create Table) Privilege

Jun 29, 2011

1.How to check a user has 'create table' privilege?

2.how to check a user has privilege to grant 'create table' privilege to other user ?

View 6 Replies View Related

SQL & PL/SQL :: How To Check System Views When Create Table Privilege

Jan 4, 2013

I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.

How do i check from the system views if i have create table privilege under my own schema?

View 2 Replies View Related

SQL & PL/SQL :: Updating Table Statistics - View Not Appropriate

Oct 22, 2010

how many rows certain tables have.

updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?

when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).

Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?

View 3 Replies View Related

PL/SQL :: Oracle 11g - Huge Table Statistics

Jun 13, 2012

I have a table which have 300+ columns and have 13 million rows. It is on a 32 kb block size. This is a table in data ware house environment. There no# of rows in the table haven't changed much but I see that the time taken to collect statistics have increased significantly.Initially it took only 15 minutes (with the same 13M rows) now it runs for 4+ hours. The max parallel servers is 4 (which is unchanged). The table is not partitioned.

OS: HP UX Itanium
Database: Oracle 11g (11.2.0.2)

Command is:
exec dbms_stats.gather_table_stats(ownname=>'ABC',tabname=>'ABC_LOAD',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>dbms_stats.auto_degree);

I would like to understand:

1) What could have been the causes of this change in time. 15 minutes to 4+hours ?
2) How can we gather statistics of huge table at a faster rate?

View 1 Replies View Related

SQL & PL/SQL :: Materialized Views - Take Statistics For Table Again?

Oct 26, 2012

I have created materialized view which hold few million records.Should i have to analyse the view and compute the statistics after i create the materialized view?

Also,just in case i need further indexing,should i have to take the statistics for the table again??

View 5 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

Performance Tuning :: Stale Statistics For Table?

Apr 27, 2012

From the below query i found that there are some stale stats for 3 tables.

=================================
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner= 'SYSADM' and stale_stats='YES'
order by last_analyzed desc

I collect stats for those above 3 tables with dbms_stats.gather_table_stats().But no luck.After collection of stats immediately I ran the above query.But still it is showing there are stale stats for 3 tables.

how can I change "STALE-STATS" status, so that optimizer can use the updated stats eficiently.

View 3 Replies View Related

PL/SQL :: How To Display Required Data From Emp Table

Dec 26, 2012

This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.

select * from emp where mgr=nvl(:mgr,mgr);

when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.

1)I want to display all the records when I won't give any input including nulls
2)I want to display all the records who's mgr is null

Is there any way to incorporate to include all these in a single query..

View 9 Replies View Related

Server Administration :: Time Remaining To Complete Table Statistics?

Jan 16, 2012

I use the following query to find out the remaining time to complete the table statistics which is running currently.

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;

SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?

And also sometime the query never shows the output of the query for the current running query.

View 1 Replies View Related

PL/SQL :: Master Detail Form And Required Table Design

Feb 28, 2013

I have a contact table that will be used for more than one entity (Customers, Sites, Suppliers, etc.) Using APEX, when we define the master-detail, we need to add a FK to the Detail table -- otherwise the Master-Detail Forms can not be implemented.

I am looking for a way to defined/implemented tables so that I can utilize the same table (Contacts) for more than one Master-details relationship.

View 3 Replies View Related

Stats Gather Error On Production Database

Aug 27, 2012

I got following error when i am gathered stats on Schema level.

SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'KDB', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE, force=>TRUE);
BEGIN dbms_stats.gather_schema_stats(ownname=>'MFDB', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE, force=>TRUE); END;
*
ERROR at line 1:
ORA-20003: Specified bug number (9196440) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15342
ORA-06512: at "SYS.DBMS_STATS", line 15688
ORA-06512: at "SYS.DBMS_STATS", line 15766
ORA-06512: at "SYS.DBMS_STATS", line 15725
ORA-06512: at line 1

SQL> EXEC dbms_stats.gather_schema_stats(ownname=>'CDC_DATA', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>10);

BEGIN dbms_stats.gather_schema_stats(ownname=>'CDOMIG_DATA', estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>10); END;
*
ERROR at line 1:
ORA-20003: Specified bug number (9196440) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15342
ORA-06512: at "SYS.DBMS_STATS", line 15688
ORA-06512: at "SYS.DBMS_STATS", line 15766
ORA-06512: at "SYS.DBMS_STATS", line 15725
ORA-06512: at line 1

View 5 Replies View Related

Server Administration :: Gather Schema Stat Utility Of Oracle?

Oct 28, 2010

I have problem/misunderstanding with gather schema stat utility of oracle. Herewith i'm posting my try and output of it. My main question is why the column 'LAST_ANALYZED' of dba_tables not updated on gathering fresh schema level statistics.

SQL>select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL

[code]...

10 rows selected.

SQL>execute dbms_stats.gather_schema_stats(OWNNAME => 'STO', OPTIONS => 'GATHER AUTO');

PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;

OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------- ----------- ---------
STO BILLSLIPB 7695 26 36 7695 29-MAR-10
STO BILL_CHECKING_SLIP 2634 71 28 2634 29-MAR-10
STO FACTORYBILLA 2 119 1 2 29-MAR-10

[code]...

10 rows selected. SQL>

View 4 Replies View Related

Server Administration :: Stats Gather-LAST_ANALYZED Column Not Updated

Sep 11, 2012

Using below script i run stats gather job..But LAST_ANALYZED column is not updated for the table.. Why it was not updated.. My DB version is 11.1.0.7.0

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXXX', tabname => 'XXXXXX',estimate_percent =>5, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', degree => 8, granularity => 'ALL',Cascade => TRUE);

View 11 Replies View Related

Server Administration :: TX Lock Wait Time Disappears When Gather Stat Is Run?

May 5, 2011

We are experiencing tx row lock wait time over hours. There is no blocking session and it seems that the application hangs. What is funny is that when we gather_stats on the tables, those tx row lock wait are being released.

View 8 Replies View Related

SQL & PL/SQL :: How To Get Output Without SYSDBA Privilege

Mar 2, 2010

A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?

CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Grant Privilege To Role

Jun 3, 2010

I have a stored procedure which when executed creates a user and grants some roles to the user and also makes certain roles DEFAULT using "alter user"

The issue comes when i execute the procedure.

I have a User who has a role and this role has the "alter user" privilege, for example lets say that the user1 has the role ABC, and role ABC has the "alter user" privilege.

SQL> grant alter user to ABC;
Grant succeeded.
SQL> grant ABC to user1;
Grant succeeded.

Now, when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges", when i check the line, its this line:
alter user user1 DEFAULT ROLE "ROLE1", "ROLE2"

But as far as i know the user1 has the "alter user" privilege

I want to make those two roles ROLE1, ROLE2 DEFAULT because i don't want the other roles ROLE3,ROLE4 to be default, as you know if there are many roles and if we alter user with default for certain roles other roles become DEFAULT=NO.

So i get the error at "alter user" statement though the user has the "alter user" privilege, what do you think might be wrong?

OR is there anyway to grant roles to the user with default=NO option?

View 9 Replies View Related







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