DBMS_STATS

Сбор статискики:

DBMS_STATS.gather_database_stats;
DBMS_STATS.gather_database_stats(estimate_percent => 15);
DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

DBMS_STATS.gather_schema_stats('owner');
DBMS_STATS.gather_schema_stats('owner', estimate_percent => 15);
DBMS_STATS.gather_schema_stats('owner', estimate_percent => 15, cascade => TRUE);

DBMS_STATS.gather_table_stats('owner', 'table_name');
DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => 15);
DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => 15, cascade => TRUE);
DBMS_STATS.gather_index_stats('owner', 'index_name');

DBMS_STATS.gather_dictionary_stats;

Удаление статистики:

DBMS_STATS.delete_database_stats;
DBMS_STATS.delete_schema_stats('owner');
DBMS_STATS.delete_table_stats('owner', 'table_name');
DBMS_STATS.delete_column_stats('owner', 'table_name', 'column_name');
DBMS_STATS.delete_index_stats('owner', 'index_name');

DBMS_STATS.delete_dictionary_stats;


Представления такие:

DBA_TABLES
DBA_TAB_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUB_PARTITIONS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTIONS

DBA_TAB_HISTOGRAMS
DBA_PART_HISTOGRAMS
DBA_SUBPART_HISTOGRAMS


Примеры:

create  table  t  (x int);

insert into t values ( 1 );


begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' );
end;
/

begin
dbms_stats.delete_table_stats( 'ANGOR', 'T' );
end;
/

begin
dbms_stats.lock_table_stats( 'ANGOR', 'T');
end;
/

begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , force=>true);
end;
/

begin
dbms_stats.unlock_table_stats( 'ANGOR', 'T');
end;
/

begin
dbms_stats.delete_table_stats( 'ANGOR', 'T' );
end;
/

begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , estimate_percent => 15 );
end;
/



select owner,
table_name,
round(sample_size/decode(num_rows, 0, 100000000000, num_rows)*100,2) percent,
last_analyzed
from all_tables
where owner = 'ANGOR'
     and table_name = 'T';



drop table t;

create table t as select * from all_objects;

create index t_idx on t (object_name);


begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , cascade => true );
end;
/


select owner,
table_name,
round(sample_size/decode(num_rows, 0, 100000000000, num_rows)*100,2) percent,
last_analyzed
from all_tables
where owner = 'ANGOR'
     and table_name = 'T';
    
    
select owner,
table_name,
index_name,
round(sample_size*100/nvl(decode(num_rows, 0, 100000, num_rows), 1000000),2) percent,
last_analyzed
from all_ind_statistics
where owner = 'ANGOR'
     and table_name = 'T';


begin
dbms_stats.delete_index_stats( 'ANGOR', 'T_IDX' );
end;
/


begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , cascade => true, estimate_percent => 15 );
end;
/

select owner,
table_name,
round(sample_size/decode(num_rows, 0, 100000000000, num_rows)*100,2) percent,
last_analyzed
from all_tables
where owner = 'ANGOR'
     and table_name = 'T';
    
    
select owner,
table_name,
index_name,
round(sample_size*100/nvl(decode(num_rows, 0, 100000, num_rows), 1000000),2) percent,
last_analyzed
from all_ind_statistics
where owner = 'ANGOR'
     and table_name = 'T';



begin
dbms_stats.gather_index_stats( 'ANGOR', 'T_IDX' , estimate_percent => 15 );
end;
/

Oracle Recreate DB Control

Удаление DB Control объектов:

1. Удаление DB Control Configuration Files используя EMCA скрипт:

emca -deconfig dbcontrol db


2. Удаление DB Control Configuration Files руками:

Удалите следующие директории:

$ORACLE_HOME/hostname_sid
$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid

3. Удаление DB Control Repository используя RepManager:

$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager hostname listener_port sid -action drop

4. Удаление DB Control Repository Objects руками:

Шаг 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYSMAN

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE);

Шаг 2: Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP RESTRICT;

SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

SQL> EXEC sysman.setEMUserContext('',5);

SQL> REVOKE dba FROM sysman;

SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE
IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

SQL> DROP USER mgmt_view CASCADE;

SQL> DROP ROLE mgmt_user;

SQL> DROP USER sysman CASCADE;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

SQL> drop user sysman cascade;

SQL> drop role MGMT_USER;

SQL> drop user MGMT_VIEW cascade;

SQL> drop public synonym MGMT_TARGET_BLACKOUTS;

SQL> drop public synonym SETEMVIEWUSERCONTEXT;

5. Удаление DB Control Configuration Files and Repository Objects используя EMCA:

$ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop

B. Создание DB Control объектов.

1.Создание DB Control Configuration Files:

$ORACLE_HOME/bin/emca -config dbcontrol db

2.Создание DB Control Repository Objects и Configuration Files:

$ORACLE_HOME/bin/emca -config dbcontrol db -repos create

C. Recreate/ReConfig DB Control

1. Recreate the DB Control Configuration Files only (leave Repository intact):

$ORACLE_HOME/bin/emca -config dbcontrol db

2. Recreate the DB Control Configuration Files and Repository

$ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate




ORACLE_SID=test
ORACLE_BASE=/app/oracle
ORACLE_HOSTNAME=dbserv.msk.ru
ORACLE_HOME=/app/oracle/product/10.2.0/db_1

-bash-3.00$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Mar 1, 2009 11:14:41 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: test
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Mar 1, 2009 11:15:00 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test/emca_2009-03-01_11-14-41-AM.log.
Mar 1, 2009 11:15:00 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Mar 1, 2009 11:15:00 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
-bash-3.00$

-bash-3.00$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Mar 1, 2009 11:29:23 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: test
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Mar 1, 2009 11:29:51 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test/emca_2009-03-01_11-29-23-AM.log.
Mar 1, 2009 11:29:52 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Mar 1, 2009 11:29:52 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Mar 1, 2009 11:29:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 1, 2009 11:29:56 AM


-bash-3.00$ ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
kill: 10390: no such process
-bash-3.00$
-bash-3.00$ ps -ef|grep emagent
oracle 10396 29217 0 18:05:58 pts/4 0:00 grep emagent
-bash-3.00$ ps -ef|grep dbsnmp
oracle 10398 29217 0 18:06:25 pts/4 0:00 grep dbsnmp
-bash-3.00$
-bash-3.00$ ps -fu oracle


-bash-3.00$ emca -config dbcontrol db -repos create

STARTED EMCA at Mar 1, 2009 11:31:54 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: test
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/10.2.0/db_1

Database hostname ................ dbserv.msk.ru
Listener port number ................ 1521
Database SID ................ test
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 1, 2009 11:32:36 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/test/emca_2009-03-01_11-31-54-AM.log.
Mar 1, 2009 11:32:37 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 1, 2009 11:34:00 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 1, 2009 11:34:02 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 1, 2009 11:35:39 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 1, 2009 11:35:39 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://dbserv.msk.ru:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 1, 2009 11:35:39 AM