Thanks Wolfgang,
That's what I was thinking. I figured a "hack" to one of those less than
intuitive c# columns would neatly do the trick so I thought I'd ask if
anyone had done it before I started down that road myself. Request for
Enhancement to Oracle Corp., add a procedure to the dbms_stats package
to make it a hackless, officially sanctioned operation.
I also thought about the exp/imp approach. Now I'm asking myself why I
prefer the DBA community accepted hack over the sanctioned exp/imp
approach but myself is having difficulty answering myself.
In wonderment of fuzzy cognitive processing, seeking clear explanations
of the obtuse, straining to hear divine utterances from the oracle,
enjoying the mysteries of the journey at hand...
Steve Orr
-----Original Message-----
Wolfgang Breitling
Sent: Tuesday, December 02, 2003 7:39 PM
To: Multiple recipients of list ORACLE-L
Nope, that won't do a thing:
SQL> exec print_table('select * from dba_tables where owner=''FACTS84''
SQL> 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Orr, Steve
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).
