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


ADVERTISEMENT

Server Administration :: Delete Stats Gather Stats

Sep 11, 2012

During STATS gather running for the table, unknowingly i deleted the old stats using EXEC DBMS_STATS.DELETE_TABLE_STATS. I would like to know will it affect the stats gather job currently running for the table and whether my stats will be gathered successfully.

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

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

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

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

Server Administration :: How To Find Non-indexed Queries And Columns That Require Indexes

Sep 1, 2011

How to find non-indexed queries and columns that require indexes?

View 3 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 :: 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 :: 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 :: 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 :: Insufficient Privilege?

Mar 10, 2011

When i want to connect inro database by Following:

# sqlplus /nolog
sql>conn / as sysdba

Insufficient privilege.

What are the reasons.

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

Server Administration :: Ora-01030 Insufficient Privilege?

Jan 3, 2012

I want to create new database and i follow the following steps.

C:SET ORACLE_SID=ASIM
ORAPWD FILE=ORAPWDASIM.ORA PASSWORD=ASIM ENTRIES=6
ORADIM -NEW -SID ASIM -STARTMODE AUTO
THE TRY TO CONNECT TO SQLPLUS
SQLPLUS /NOLOG
AFTER THAT I WRITE THE CODE TO CONNECT THE SYS
Connect sys/ASIM as SYSDBA
BUT IT DISPLAY ERROR ORA-01030, INSUFICENT PREVILAGES.

View 6 Replies View Related

Server Administration :: Extract Tablespace Privilege

Apr 28, 2011

I want to extract roles and privileges DDL for tablespace using dbms_metadata.get_ddl. How to do it?

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 :: DBMS-STATS And ORA-20000?

Dec 20, 2011

I've just installed an Oracle 11.2.0.1.0 64 bit server on my windows 7 machine in order to play around while using attempting to run

exec dbms_stats.gather_table_stats('eii','v2x4e')

I get the following:
ORA-20000: Unable to analyze TABLE "EII"."V2X4E", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 20327
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

My initial google searches indicate that I need the select any table and analyze any privileges. I don't think that can be right/appropriate - but I've granted them anyway to no avail.

Select * from user_tables

returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?

View 5 Replies View Related

Server Administration :: Method To Execute Schema Stats?

Mar 22, 2012

When we want to gather schema statistics which method we should follow and why ?

exec dbms_utility.compile_schema('SHIKHAR');

or

BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'JACK'

[Code].....

View 3 Replies View Related

Server Administration :: Remove Synonym STATS$SNAPSHOT-ID?

Sep 11, 2007

I've got to collect statistics by DBMS_STAT. For the first time, the spcreate.sql is to be called, however, it returned one error following:

If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence Sequence created.

create public synonym STATS$SNAPSHOT_ID for STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

And then, I checked it again with its owner:

SQL> select owner, synonym_name
2 from dba_synonyms
3 where synonym_name='STATS$SNAPSHOT_ID';

OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC STATS$SNAPSHOT_ID

Then, I checked again after dropping schema PERFSTAT:

SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status

[code]...

What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?

View 13 Replies View Related

Server Administration :: Collect Object Stats - When Enough Of Data Has Changed

Aug 29, 2013

Quote:Oracle collects new statistics when enough of the data (about 10%) has changed.I read the above statement in the oracle documentation that Oracle collects new statistics when enough of the data (about 10%) has changed every day during 10 pm to 6am .

But still i see some object statistics are stale and empty.Do i need to collect statistics for these objects manually ? and would like to know why these objects statistics were not collected during maintenance window ?

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

Performance Tuning :: Using Stats Table Instead Of Regular Stats To Get Plan?

Jan 27, 2011

DBMS_STATS.CREATE_STATS_TABLE
DBMS_STATS.EXPORT_SCHEMA_STATS
DBMS_STATS.GATHER_TABLE_STATS

I have used the above to get a copy of schema stats and gather new stats for specific tables into a STATS TABLE in my personal schema. What I want to do now is use this stats table to generate plans for queries where I believe stats are off. Is it even possible? To be clear, I do not want to import stats because this replaces the stats currently there. I just want to point the CBO to my stats table for generating plans.

there was a session parameter I could set to tell oracle to use my stats table when generating plans, or an explain plan clause I could use or a DBMS_XPLAN paramter I could provide that would tell these tools to use my stats table when generating a plan, or even some way to tell autotrace. But I have found none of this.

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

PL/SQL :: Require A Query With All Clauses

Jul 30, 2013

I have been asked for a query to write by using all sql clauses like group by, where, having, order by in a single query. I just wrote the below query but I did not get out put. select deptno, max(sal) from emp group by deptno having  deptno > ( select distinct deptno from emp where deptno >= 20)

View 3 Replies View Related

PL/SQL :: Require Logic / Oracle 10g R2 On Windows

Sep 5, 2012

I have oracle 10g R2 on windows.I have two tables and say table A ant Table B. Both have column Total_amount. There are primary key's on Both tables.

Sample query
SELECT A.EMPNO,A.EMP_CODE,nvl(A.TOTAL_AMOUNT,0) "A_TOTAL_AMOUNT",
    nvl(B.TOTAL_AMOUNT,0) "B_TOTAL_AMOUNT",(nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)) "DIFFERENCE"
    FROM A, B
    WHERE A.EMPNO =B.EMPNO (+)
    AND A.EMP_CODE = B.EMP_CODE (+)
    AND round((nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)),3) <> 0Above query retrives only Non-matching rows.

I need to retrive :-

1)output of the query shown above and

2)Records which are present in B and not in A.

View 2 Replies View Related

Forms :: Tool Tip Require To Display The Data

Jul 25, 2011

If it possible to display Tool Tip of a Column Require to Display the Data contain in that Column.

View 11 Replies View Related

SQL & PL/SQL :: External Table Does Not Require Database But Where They Stored

Mar 10, 2010

For external tables does not require any database space.But where the external tables are storing.

View 3 Replies View Related

Forms :: Modify Data Will Require Automatic Authentication

May 8, 2012

I have made an application in forms 6i. Now i want if user can update, or modify the data it will required automatic authentication. Administrator will gives his password then the updation will continue otherwise rollback the transaction.

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







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