Server Administration :: Privileges To Gather Statistics
Nov 9, 2010What privileges is required to gather statistics of oracle database using DBMS_STATS/ANALYZE command.
View 1 Replies
ADVERTISEMENT
Server Administration :: When To Gather Table Statistics
Apr 21, 2012Why 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.
Server Administration :: Unable To Gather Statistics Of Neither Schema Nor Table?
Jul 13, 2011I 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]....
Replication :: Gather Statistics On Materialized Views
Jul 15, 2009Any best way to gather statistics on Materialized Views.
View 1 Replies View RelatedSQL & PL/SQL :: Privilege Required To Gather Table Statistics?
May 14, 2011What privilege is required to gather table statistics using dbms_stats ?
View 2 Replies View RelatedPerformance Tuning :: Gather Statistics On SYS Objects
Mar 7, 2012Do 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
...
)
Exadata :: Gather Statistics In Parallel On Schema
Sep 7, 2012I 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
Server Administration :: Privilege Require To Gather Stats?
May 27, 2012what 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.
Server Administration :: Gather Schema Stat Utility Of Oracle?
Oct 28, 2010I 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>
Server Administration :: Stats Gather-LAST_ANALYZED Column Not Updated
Sep 11, 2012Using 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);
Server Administration :: TX Lock Wait Time Disappears When Gather Stat Is Run?
May 5, 2011We 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 RelatedServer Administration :: Table Statistics For Oracle 11g
Oct 25, 2012I 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 RelatedServer Administration :: Gathering Database Statistics?
Sep 3, 2013,I need to view database statistics after executing SQL> exec dbms_stats.gather_database_stats;is that really possible? how to check it?
View 7 Replies View RelatedServer Administration :: Updating Statistics - Not Working?
Jul 4, 2010 if there are any problems with updating statistics? My manager told me that they had big performance issue when they updated the statistics of whole database. Is this a rare case?
I know the advantages of updating statistics,but are there any dis-advantages?
Server Administration :: Stale Statistics In Oracle Database?
Jun 7, 2012Is there any script that give us output of all Stale Statistics in Oracle Database
View 8 Replies View RelatedServer Administration :: New Feature Of 11g Called-Pending Statistics
Mar 31, 2011What is the scenario, we should use the new feature of 11g called-Pending Statistics.
Eg:-
EXEC DBMS_STATS.set_table_prefs ('SCOTT', 'EMP', 'PUBLISH', 'false');
Server Administration :: Time Remaining To Complete Table Statistics?
Jan 16, 2012I 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.
Server Administration :: Refreshing Stale Statistics In Oracle Database?
Apr 21, 2010the importance of refreshing the stale statistics in oracle database and also how often we should do this?
View 3 Replies View RelatedServer Administration :: Impact Of Up-to-date Statistics On Main Schema Objects
Feb 29, 2012I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.
For this I wanted to:
first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.
However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.
Server Administration :: Delete Stats Gather Stats
Sep 11, 2012During 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 RelatedServer Administration :: Privileges From One Schema To Other
Feb 10, 2012I 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
Server Administration :: Privileges Required For Export
Dec 1, 2011I 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?
Server Administration :: ORA-01031 - Insufficient Privileges?
Feb 7, 2012I 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
Server Administration :: Insufficient Privileges To Other User
May 21, 2011WHEN 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.
Server Administration :: Privileges For DROP Partition
Jun 21, 2011I 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.
Server Administration :: Granted Privileges But Not Able To See In DBA_SYS_PRIVS Table
Jul 4, 2011I 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.
Server Administration :: Insufficient Privileges While Connecting (sys As Sysdba)
Apr 2, 2013SQL> 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.
Server Utilities :: Privileges Error - Executing INSERT Statement For Table
Jul 9, 2013I 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]....
Server Utilities :: What Privileges Are Required To Perform Only Schema Level Export And Import
Feb 20, 2012Quote: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?
Gather Stats On Table
May 26, 2011I 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