How do you collect statistics for a table, schema and Database?

How do you collect statistics for a table, schema and Database?
by

1 Answer

Answer :

Using DBMS_STATS package to gather Oracle dictionary statistics.
by

Related questions

How often you should collect statistics for a table?

Description : How often you should collect statistics for a table?

Last Answer : Analyse if it's necessary! - Refresh STALE statistics before the batch processes run but only for tables involved in batch run, - Don't do it if you don't have to. - Oracle ... has default, scheduled job "gather_stats_job" that analyses stats on a daily basis during the maintenance window time.

Can one rename a database user (schema)? (for DBA)

Description : Can one rename a database user (schema)? (for DBA)

Last Answer : No, this is listed as Enhancement Request 158508. Workaround: Do a user-level export of user A create new user B Import system/manager fromuser=A touser=B Drop user A

Name three advisory statistics you can collect.

Description : Name three advisory statistics you can collect.

Last Answer : Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

Consider the following two commands C1 and C2 on the relation R from an SQL database: C1: drop table R; C2: delete from R; Which of the following statements is TRUE? I. Both C1 and C2 delete the schema for R. II. C2 retains relation R, but deletes all tuples in R. III. C1 deletes not only all tuples of R, but also the schema for R. (A) I only (B) I and II only (C) II and III only (D) I, II and III

Description : Consider the following two commands C1 and C2 on the relation R from an SQL database:  C1: drop table R; C2: delete from R; Which of the following statements is TRUE? I. Both C1 and C2 delete the schema for R. II. C2 ... R. (A) I only (B) I and II only (C) II and III only (D) I, II and III

Last Answer : (C) II and III only

How can you gather statistics on a table?

Description : How can you gather statistics on a table?

Last Answer : The ANALYZE command.

Where can one find I/O statistics per table? (for DBA

Description : Where can one find I/O statistics per table? (for DBA

Last Answer : The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and ... For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

How can I dump an oracle schema to a LOCAL file over a network connection from sqlplus?

Description : How can I dump an oracle schema to a LOCAL file over a network connection from sqlplus?

Last Answer : By connecting and spooling are you excluding the exp/imp commands (or expdb/impdb in 10g)?

Give the two types of tables involved in producing a star schema and the type of data they hold.

Description : Give the two types of tables involved in producing a star schema and the type of data they hold.

Last Answer : Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

What is schema?

Description : What is schema?

Last Answer : A schema is collection of database objects of a User.

What is a Schema ?

Description : What is a Schema ?

Last Answer : The set of objects owned by user account is called the schema.

Can objects of the same Schema reside in different tablespaces.?

Description : Can objects of the same Schema reside in different tablespaces.?

Last Answer : Yes.

Can objects of the same schema reside in different tablespaces?

Description : Can objects of the same schema reside in different tablespaces?

Last Answer : Yes.

What are Schema Objects?

Description : What are Schema Objects?

Last Answer : Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

What is schema?

Description : What is schema?

Last Answer : A schema is collection of database objects of a user.

What is a Relation Schema and a Relation?

Description : What is a Relation Schema and a Relation?

Last Answer : A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, …,tn). Each tuple is an ordered list of n-values t=(v1,v2, …, vn).

How many types of database triggers can be specified on a table ? What are they ?

Description : How many types of database triggers can be specified on a table ? What are they ?

Last Answer : Insert Update Delete Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW clause is ... for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the returned Boolean value.

How will you swap objects into a different table space for an existing database ?

Description : How will you swap objects into a different table space for an existing database ?

Last Answer : Export the user Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql. Drop necessary objects. Run the script newfile.sql after altering the tablespaces. Import from the backup for the necessary objects.

A logical schema A. is the entire database B. is a standard wayof organizing information into accessible parts C. describes how data is actually stored on disk D. All of theabove E. None of the above

Description : A logical schema A. is the entire database B. is a standard wayof organizing information into accessible parts C. describes how data is actually stored on disk D. All of theabove E. None of the above

Last Answer : is a standard wayof organizing information into accessible parts

Anything that affects the database schema is a part of A) DML B) DCL C) DDL D) All of the above

Description : Anything that affects the database schema is a part of A) DML B) DCL C) DDL D) All of the above

Last Answer : D) All of the above

A logical schema A) is the entire database B) is a standard way of organizing information into accessible parts. C) Describes how data is actually stored on disk. D) All of the above

Description : A logical schema A) is the entire database B) is a standard way of organizing information into accessible parts. C) Describes how data is actually stored on disk. D) All of the above

Last Answer : B) is a standard way of organizing information into accessible parts.

A top-to-bottom relationship among the items in a database is established by a A) Hierarchical schema B) Network schema C) Relational Schema D) All of the above

Description : A top-to-bottom relationship among the items in a database is established by a A) Hierarchical schema B) Network schema C) Relational Schema D) All of the above

Last Answer : A) Hierarchical schema

The database schema is written in (A) HLL (B) DML (C) DDL (D) DCL

Description : The database schema is written in (A) HLL (B) DML (C) DDL (D) DCL

Last Answer : (C) DDL

A Network Schema (A) restricts to one to many relationship (B) permits many to many relationship (C) stores Data in a Database (D) stores Data in a Relation

Description : A Network Schema (A) restricts to one to many relationship (B) permits many to many relationship (C) stores Data in a Database (D) stores Data in a Relation

Last Answer : (B) permits many to many relationship

Relational database schema normalization is NOT for: (A) reducing the number of joins required to satisfy a query. (B) eliminating uncontrolled redundancy of data stored in the database. (C) eliminating number of anomalies that could otherwise occur with inserts and deletes. (D) ensuring that functional dependencies are enforced.

Description : Relational database schema normalization is NOT for: (A) reducing the number of joins required to satisfy a query. (B) eliminating uncontrolled redundancy of data stored in the database. (C) ... could otherwise occur with inserts and deletes. (D) ensuring that functional dependencies are enforced. 

Last Answer : reducing the number of joins required to satisfy a query.

DBMS provides the facility of accessing data from a database through (A) DDL (B) DML (C) DBA (D) Schema

Description : DBMS provides the facility of accessing data from a database through (A) DDL (B) DML (C) DBA (D) Schema 

Last Answer : (B) DML

Define the term Database Schema

Description : Define the term Database Schema

Last Answer : The overall design of the database is called the database schema. A schema diagram displays only names of record types (entities) and names of data items (attributes) and does not show the relationships among the various files. 

Can you make collection of Statistics for tables automatically?

Description : Can you make collection of Statistics for tables automatically?

Last Answer : YES. Oracle databse has default, scheduled job "gather_stats_job" that analyses stats on a daily basis during the maintenance window time. There are two scheduled activities related to ... GATHER_STATS_JOB'); Oracle collects optimizer statistics for SQL via the default of autostats_target = auto.

How does one manage Oracle database users? (for DBA

Description : How does one manage Oracle database users? (for DBA

Last Answer : Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB ... locked users account ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

How does one manage Oracle database users? (for DBA

Description : How does one manage Oracle database users? (for DBA

Last Answer : Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the ... from SYS.V$BH log buffer spaces Increase LOG_BUFFER parameter or move log files to faster disks

How does one backup a database using the export utility? (for DBA

Description : How does one backup a database using the export utility? (for DBA

Last Answer : Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data ... exports include more information about the database in the export file than user level exports.

What strategies are available for backing-up an Oracle database? (for DBA

Description : What strategies are available for backing-up an Oracle database? (for DBA

Last Answer : The following methods are valid for backing-up an Oracle database: Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. ... , etc. If your database is in ARCGIVELOG mode, you also need to backup archived log files.

Why and when should I backup my database? (for DBA

Description : Why and when should I backup my database? (for DBA

Last Answer : Backup and recovery is one of the most important aspects of a DBAs job. If you lose your company's data, you could very well lose your job. Hardware and software can always be ... things that will get you. Most failed recoveries are a result of organizational errors and miscommunications.

What are the roles and user accounts created automatically with the database?

Description : What are the roles and user accounts created automatically with the database?

Last Answer : DBA - role Contains all database system privileges. SYS user account - The DBA role will be assigned to this account. All of the base tables and views for the database's dictionary ... administrative information and internal tables and views used by oracle tools are created using this username.

What are the responsibilities of a Database Administrator ?

Description : What are the responsibilities of a Database Administrator ?

Last Answer : Installing and upgrading the Oracle Server and application tools. Allocating system storage and planning future storage requirements for the database system. Managing primary database structures ... tape Backing up and restoring the database. Contacting Oracle Corporation for technical support.

How does one create a standby database? (for DBA)

Description : How does one create a standby database? (for DBA)

Last Answer : While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!! On your standby database, issue ... the standby database, stop the recovery process and activate it: ALTER DATABASE ACTIVATE STANDBY DATABASE;

Name two files used for network connection to a database.

Description : Name two files used for network connection to a database.

Last Answer : TNSNAMES.ORA and SQLNET.ORA

When creating a user, what permissions must you grant to allow them to connect to the database?

Description : When creating a user, what permissions must you grant to allow them to connect to the database?

Last Answer : Grant the CONNECT to the user.

Name a tablespace automatically created when you create a database.

Description : Name a tablespace automatically created when you create a database.

Last Answer : The SYSTEM tablespace.

How would you determine the time zone under which a database was operating?

Description : How would you determine the time zone under which a database was operating?

Last Answer : select DBTIMEZONE from dual;

Where would you look for errors from the database engine?

Description : Where would you look for errors from the database engine?

Last Answer : In the alert log.

You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

Description : You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

Last Answer : I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

What is network database link?

Description : What is network database link?

Last Answer : Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

What is public database link?

Description : What is public database link?

Last Answer : Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

What is private database link?

Description : What is private database link?

Last Answer : Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

What are the types of database links?

Description : What are the types of database links?

Last Answer : Private database link, public database link & network database link.

What is Database Link ?

Description : What is Database Link ?

Last Answer : A database link is a named object that describes a "path" from one database to another.

What are the steps involved in Database Shutdown ?

Description : What are the steps involved in Database Shutdown ?

Last Answer : Close the Database, Dismount the Database and Shutdown the Instance.

What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?

Description : What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?

Last Answer : Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

What are the different modes of mounting a Database with the Parallel Server ?

Description : What are the different modes of mounting a Database with the Parallel Server ?

Last Answer : Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

Can Full Backup be performed when the database is open ?

Description : Can Full Backup be performed when the database is open ?

Last Answer : No.