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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
