Exporting Statistics
a@10GR2> create table t1 as select * from all_users;
Table created.
a@10GR2> alter table t1 add constraint t1_pk primary key(user_id);
Table altered.
a@10GR2> create table t2 as select * from all_objects;
Table created.
a@10GR2> alter table t2 add constraint t2_pk primary key(object_id);
Table altered.
a@10GR2> exec dbms_stats.gather_schema_stats(ownname=>USER,estimate_percent=>dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
a@10GR2> exec dbms_stats.create_stat_table(ownname=>USER,stattab=>'mystat');
PL/SQL procedure successfully completed.
a@10GR2> exec dbms_stats.export_schema_stats(ownname=>USER,stattab=>'mystat');
PL/SQL procedure successfully completed.
a@10GR2> select 'Table', table_name, num_rows from user_tables
2 union all
3 select 'index',index_name,num_rows from user_indexes
4 union all
5 select 'col', table_name||'.'|| column_name,count(*) from user_tab_histograms
6 group by table_name||'.'|| column_name;
'TABL TABLE_NAME NUM_ROWS
----- ------------------------------ ---------
Table T1 40
Table T2 40734
Table MYSTAT
index T1_PK 40
index MYSTAT
index T2_PK 40734
col T2.OBJECT_TYPE 2
col T2.OBJECT_ID 2
col T1.USERNAME 2
col T2.STATUS 2
col T2.CREATED 2
col T1.CREATED 2
col T2.SECONDARY 2
col T1.USER_ID 2
col T2.OWNER 2
col T2.OBJECT_NAME 2
col T2.TEMPORARY 2
col T2.GENERATED 2
col T2.TIMESTAMP 2
col T2.DATA_OBJECT_ID 2
col T2.LAST_DDL_TIME 2
21 rows selected.
a@10GR2> update mystat set c5='B';
20 rows updated.
a@10GR2> commit;
Commit complete.
D:\>exp userid=a/a@10.239.61.210:1521/10GR2 tables=mystat file=d:\a_dmp.dmp log=d:\a_log.txt
Export: Release 10.1.0.2.0 - Production on Mon Apr 19 19:39:06 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table MYSTAT 20 rows exported
Export terminated successfully without warnings.
b@10GR2> create table t1 as select * from all_users;
Table created.
b@10GR2> alter table t1 add constraint t1_pk primary key(user_id);
Table altered.
b@10GR2> create table t2 as select * from all_objects;
Table created.
b@10GR2> alter table t2 add constraint t2_pk primary key(object_id);
Table altered.
D:\>imp userid=b/b@10.239.61.210:1521/10GR2 tables=mystat file=d:\a_dmp.dmp log=d:\b_log.txt fromuser=a touser=b
Import: Release 10.1.0.2.0 - Production on Mon Apr 19 19:40:43 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
Warning: the objects were exported by A, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "MYSTAT" 20 rows imported
Import terminated successfully without warnings.
b@10GR2> exec dbms_stats.import_schema_stats(ownname=>USER,stattab=>'MYSTAT');
PL/SQL procedure successfully completed.
b@10GR2> select 'Table', table_name, num_rows from user_tables
2 union all
3 select 'index',index_name,num_rows from user_indexes
4 union all
5 select 'col', table_name||'.'|| column_name,count(*) from user_tab_histograms
6 group by table_name||'.'|| column_name;
'TABL TABLE_NAME NUM_ROWS
----- ------------------------------ ----------
Table MYSTAT
Table T1 40
Table T2 40734
index MYSTAT 20
index T1_PK 40
index T2_PK 40734
col T2.OBJECT_TYPE 2
col T2.OBJECT_ID 2
col T1.USERNAME 2
col T2.STATUS 2
col T2.CREATED 2
col T1.CREATED 2
col T2.SECONDARY 2
col T1.USER_ID 2
col T2.OWNER 2
col T2.OBJECT_NAME 2
col T2.TEMPORARY 2
col T2.GENERATED 2
col T2.TIMESTAMP 2
col T2.DATA_OBJECT_ID 2
col T2.LAST_DDL_TIME 2
21 rows selected.
PL/SQL procedure successfully completed.
b@10GR2> select 'Table', table_name, num_rows from user_tables
2 union all
3 select 'index',index_name,num_rows from user_indexes
4 union all
5 select 'col', table_name||'.'|| column_name,count(*) from user_tab_histograms
6 group by table_name||'.'|| column_name;
'TABL TABLE_NAME NUM_ROWS
----- ------------------------------ ----------
Table MYSTAT
Table T1 40
Table T2 40734
index MYSTAT 20
index T1_PK 40
index T2_PK 40734
col T2.OBJECT_TYPE 2
col T2.OBJECT_ID 2
col T1.USERNAME 2
col T2.STATUS 2
col T2.CREATED 2
col T1.CREATED 2
col T2.SECONDARY 2
col T1.USER_ID 2
col T2.OWNER 2
col T2.OBJECT_NAME 2
col T2.TEMPORARY 2
col T2.GENERATED 2
col T2.TIMESTAMP 2
col T2.DATA_OBJECT_ID 2
col T2.LAST_DDL_TIME 2
21 rows selected.
No comments:
Post a Comment