| 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
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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: sorr_at_rightnow.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 Wed Dec 03 2003 - 08:54:31 CST
 |
 |