| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Copying stats between/amongst schemas
Nope, that won't do a thing:
SQL> exec print_table('select * from dba_tables where owner=''FACTS84'' and table_name = ''PSTREENODE''');
OWNER : FACTS84 TABLE_NAME : PSTREENODE TABLESPACE_NAME : MEDIUMTBL CLUSTER_NAME : IOT_NAME : PCT_FREE : 8 PCT_USED : 65 INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : 8192 NEXT_EXTENT : 131072 MIN_EXTENTS : 1 MAX_EXTENTS : 2147483645 PCT_INCREASE : 0 FREELISTS : 1 FREELIST_GROUPS : 1 LOGGING : NO BACKED_UP : N NUM_ROWS : 16280 BLOCKS : 376 EMPTY_BLOCKS : 7 AVG_SPACE : 379 CHAIN_CNT : 0 AVG_ROW_LEN : 78 AVG_SPACE_FREELIST_BLOCKS : 3797 NUM_FREELIST_BLOCKS : 2 DEGREE : 1 INSTANCES : 1 CACHE : N TABLE_LOCK : ENABLED SAMPLE_SIZE : 16280 LAST_ANALYZED : 07-sep-2003 11:07:01 PARTITIONED : NO IOT_TYPE : TEMPORARY : N SECONDARY : N NESTED : NO BUFFER_POOL : DEFAULT ROW_MOVEMENT : DISABLED GLOBAL_STATS : YES USER_STATS : NO DURATION : SKIP_CORRUPT : DISABLED MONITORING : NO CLUSTER_OWNER : DEPENDENCIES : DISABLED -----------------
PL/SQL procedure successfully completed.
SQL>
SQL> exec DBMS_STATS.EXPORT_TABLE_STATS
('FACTS84','PSTREENODE',NULL,'stats_table','A',TRUE,'system');
PL/SQL procedure successfully completed.
SQL>
SQL> exec DBMS_STATS.IMPORT_TABLE_STATS
('SCOTT','PSTREENODE',NULL,'stats_table','A',TRUE,'system');
PL/SQL procedure successfully completed.
SQL>
SQL> exec print_table('select * from dba_tables where owner=''SCOTT'' and
table_name = ''PSTREENODE''');
OWNER : SCOTT TABLE_NAME : PSTREENODE TABLESPACE_NAME : USERS CLUSTER_NAME : IOT_NAME : PCT_FREE : 10 PCT_USED : 40 INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : 8192 NEXT_EXTENT : 8192 MIN_EXTENTS : 1 MAX_EXTENTS : 2147483645 PCT_INCREASE : 0 FREELISTS : 1 FREELIST_GROUPS : 1 LOGGING : YES BACKED_UP : N NUM_ROWS : BLOCKS : EMPTY_BLOCKS : AVG_SPACE : CHAIN_CNT : AVG_ROW_LEN : AVG_SPACE_FREELIST_BLOCKS : NUM_FREELIST_BLOCKS : DEGREE : 1 INSTANCES : 1 CACHE : N TABLE_LOCK : ENABLED SAMPLE_SIZE : LAST_ANALYZED : PARTITIONED : NO IOT_TYPE : TEMPORARY : N SECONDARY : N NESTED : NO BUFFER_POOL : DEFAULT ROW_MOVEMENT : DISABLED GLOBAL_STATS : NO USER_STATS : NO DURATION : SKIP_CORRUPT : DISABLED MONITORING : NO CLUSTER_OWNER : DEPENDENCIES : DISABLED -----------------
PL/SQL procedure successfully completed.
You need to change the owner in the stattab table before importing:
SQL> update stats_table set c5 = 'SCOTT' where statid = 'A';
22 rows updated.
SQL> commit;
Commit complete.
SQL> exec DBMS_STATS.IMPORT_TABLE_STATS ('SCOTT','PSTREENODE',NULL,'stats_table','A',TRUE,'system');
PL/SQL procedure successfully completed.
SQL> exec print_table('select * from dba_tables where owner=''SCOTT'' and table_name = ''PSTREENODE''');
OWNER : SCOTT TABLE_NAME : PSTREENODE TABLESPACE_NAME : USERS CLUSTER_NAME : IOT_NAME : PCT_FREE : 10 PCT_USED : 40 INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : 8192 NEXT_EXTENT : 8192 MIN_EXTENTS : 1 MAX_EXTENTS : 2147483645 PCT_INCREASE : 0 FREELISTS : 1 FREELIST_GROUPS : 1 LOGGING : YES BACKED_UP : N NUM_ROWS : 16280 BLOCKS : 376 EMPTY_BLOCKS : 0 AVG_SPACE : 0 CHAIN_CNT : 0 AVG_ROW_LEN : 78 AVG_SPACE_FREELIST_BLOCKS : 0 NUM_FREELIST_BLOCKS : 0 DEGREE : 1 INSTANCES : 1 CACHE : N TABLE_LOCK : ENABLED SAMPLE_SIZE : 16280 LAST_ANALYZED : 07-sep-2003 11:07:01 PARTITIONED : NO IOT_TYPE : TEMPORARY : N SECONDARY : N NESTED : NO BUFFER_POOL : DEFAULT ROW_MOVEMENT : DISABLED GLOBAL_STATS : YES USER_STATS : NO DURATION : SKIP_CORRUPT : DISABLED MONITORING : NO CLUSTER_OWNER : DEPENDENCIES : DISABLED -----------------
PL/SQL procedure successfully completed.
At 07:09 PM 12/2/2003, you wrote:
>IIRC, you can do all of it from one session. Let's say you're copying
>stats from 'SOURCE_USER' to 'DEST_USER' while logged in as ADAWDOA ("a
>DBA account which doesn't own anything" ;-)), who owns a statistics
>table called XFER_STATS.
>
>exec dbms_stats.export_schema_stats('SOURCE_USER','XFER_STATS',null,
>user)
>
>exec dbms_stats.import_schema_stats('DEST_USER','XFER_STATS',null,user)
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: breitliw_at_centrexcc.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Dec 02 2003 - 20:39:26 CST
 |
 |