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


ADVERTISEMENT

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 :: 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 :: Oracle Export Import Utility Different Versions?

May 10, 2012

We are migratingfrom [IBM P5 - AIX 5.3 - Oracle DB 10.2.0.1 64bit]to [IBM P7 - AIX 6.1 - Oracle DB 10.2.0.5 64bit]

The new database is already up and running, our next step now is the following:
1. Create Tablespaces (done)
2. Export the Database from P5
3. Import the dump to P7.

We have this following options:

I. Use a Windows XP workstation 32bit with Oracle 10.2.0.1 Database software to export from P5, then import the dump to P7. (Export/Import Utility 10.2.0.1 Windows 32bit)
II. Use the Export Utility of the P7 (10.2.0.5 AIX 64bit) to do the export and import.
III. Use the Export Utility of P5 (10.2.0.1 AIX 64bit) then use the Import Utility of P7 (10.2.0.5 AIX 64bit)

BTW, our colleague tried to do the following:
A. Use Windows - Export Utility 10.1.0.2.0 - 32bit to make a dump of our database (Oracle 10.2.0.1.0 AIX 64bit)
B. Use Windows - Import Utility 10.1.0.2.0 - 32bit to import the dump file from step A to our new database (Oracle 10.2.0.1.0 AIX 64bit)

But after issuing imp system/password@NEWDB file=(a.dmp, b.dmp... c.dmp) full=y ignore=y statistics=none

The import seems to hang here...importing SYSTEM's objects into SYSTEM

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

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

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

Server Administration :: Use OPatch Utility For Patch Upgradation

Mar 14, 2012

How to set Path environment variable if we want to use OPatch utility for patch upgradation.

View 2 Replies View Related

Server Administration :: Difference Between A User And Schema On Oracle?

Apr 26, 2010

difference between a user and schema on Oracle.By difference, I do not mean what is a user and what is a schema.

I understand that if we create a user, the schema gets created by default for that user. Does that mean that we cannot create a user/schema alone.

What if I just want to create a user and grant him some read access on an existing schema or just want to create a schema.

View 7 Replies View Related

Server Administration :: Accessing Single Schema From Multiple Schema Logging?

May 16, 2011

A single master schema where many developers are accessing. all share same password.

now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema

View 2 Replies View Related

Server Administration :: One Schema Versus Multiple Schema

Feb 1, 2010

single schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?

View 4 Replies View Related

Server Administration :: Create / Drop User / Schema In Another Oracle Database Through Apex

Jul 7, 2012

Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.

View 12 Replies View Related

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 Utilities :: Import A Dump File Using Impdp Data Pump Utility On Oracle 10g

Feb 19, 2012

Is it possible to import a dump file using impdp data pump utility on oracle 10g where the export dump was taken using traditional exp utility and vice versa.

View 1 Replies View Related

Server Utilities :: Does Oracle Forms / Report Services (Standalone) Support Data Export Utility

Jun 10, 2010

I have a From which take Logical Backup through oracle export utility. This Form work fine when i Start OCJ4 but when i want to take backup after running oracle Forms & Report services its not take backup.

My Question is dose Oracle Forms & Report Services (Standalone) Support data backup through Export (exp) utility. I have install Oracle Forms and Report Services (Standalone) on Window XP (SP-3).

View 1 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 :: Schema Comparing Tool

Apr 8, 2012

we have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.

View 7 Replies View Related

Server Administration :: Difference Between Table_owner And Schema?

Sep 14, 2011

a table can have many schema or just 1 schema which is the table owner?

View 5 Replies View Related

Server Administration :: How To See Schema User Password In Text

Oct 1, 2012

I need to see all schema users password in a text.I am database admin,but it difficult to remember the password of all users created in single database So we also dont want to change password of all time,table to view password in text. Also we all know we can see using dba_users, but it was showing in hash value i need it in a text

View 3 Replies View Related

Server Administration :: How To Give Full Access To Other Schema

Apr 15, 2012

I want to get clear with one thing yesterday i installed oracle9i and dev 2000 to my client.

when they run one report they got stuck with pl/sql compilation errorrep-1247

when i checked that report in the report builder, in the query they are using some other table which is not belongs to that schema,then I give that schema.tablename and compiled, but this is coming for other reports also, then only i came to know that they are acceessin other schema also, how can i sort this out.

can i fix this by givin full access privilige or what privilige can i give to get full access of other schema table.

how can i check in the old database what are all the roles and privileges given to this user,

View 5 Replies View Related

Server Administration :: Pooling Meta Data Into New Schema?

May 22, 2012

I have created different scripts to monitor growth planning of the database my idea was generate a simple output file to a directory and create a report and send it as mail thru mail server. but the client requested me to create new schema and population all the stats i have collected into the tables in this schema .

How to pool all the stats from different databases into this new schema efficiently.

View 3 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 :: Deleting Unwanted SYS Schema Objects?

May 25, 2011

I was importing one schema from Oracle 10g to 11g using traditional import. I imported as a SYS user, so all the objects created in SYS schema. how can I remove these objects and retain only default SYS objects

View 11 Replies View Related

Server Administration :: USERS Tablespace Should Not Contain Schema Objects?

Jan 31, 2012

I heard that USERS tablespace should not contain any other application schema objects.

If the above statements is true , why it should not contain other schema objects ?

View 7 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Server Administration :: How To Refresh Test Schema From Production Request

Oct 31, 2012

Steps to Refresh test Oracle 9i schema from production schema on a Windows platform..

View 3 Replies View Related

Server Administration :: Split Existing Schema Into 3 Logical Divisions

Mar 2, 2011

Presently have one instance, one Schema on my DB Server. Want to split the existing schema into 3 logical divisions. Have created the 3 Schemas. Through Oracle Export from the old Schema am able to Export (using wild cards) the tables and indices for each grouping and am able to Import into the new specific schema. The problem i'm having is that i cannot seem to Export the specific sequences due for each of the new schemas. Is there an easy way to accomplish this?

View 6 Replies View Related

Server Administration :: Impact Of Up-to-date Statistics On Main Schema Objects

Feb 29, 2012

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

View 1 Replies View Related







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