transfer stats from one schema to another

exporting from schema ORIG_USER and importing into MYUSER

1. create stattab DBMS_STATS.CREATE_STAT_TABLE(ownname=>'ORIG_USER',stattab=>'STATS_FOR_TEST');
2. create table with list of tables: create table test_tabs(object_name varchar2(200));
insert into test_tabs (select ...)
3. export statsselect 'exec dbms_stats.export_table_stats(ownname=>''ORIG_USER'',tabname=>'''|| object_name ||''',stattab=>''STATS_FOR_TEST'');' cmd from (select distinct object_name from test_tabs) t;
4. delete stats on target schemaselect 'exec dbms_stats.import_table_stats(ownname=>''MYUSER'',tabname=>'''|| object_name ||''',force=>true);' cmd from (select distinct object_name from test_tabs) t;
5. copy table with stats: create table myuser.STATS_FOR_TEST as (select * from orig_user.STATS_FOR_TEST);
6. modify the MYUSER.STATS_FOR_TEST:update stats_for_test set c5='MYUSER';
update stats_for_test set ch1 = ora_hash(ch1) where ch1 is not null; <== to mask histogram values
7. import statsselect 'exec dbms_stats.import_table_stats(ownname=>''MYUSER'',tabname=>'''|| object_name ||''',stattab=>''STATS_FOR_TEST'',force=>true);' cmd from (select distinct object_name from test_tabs) t;

STATS Locked after import

Metalink: ORA-38029 "Object Statistics Are Locked" - Possible Causes [ID 433240.1]

To prevent import (imp) from locking the table's statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

GRANT OPTION

if a user has the GRANT OPTION he can pass this privilege on to other users through a view, without granting the privilege on the original source view:
SQL> grant select on dba_users to dbatest with grant option;
SQL> conn dbatest
SQL> create view da_users as select * from sys.dba_users;
SQL> grant select on da_users to dbatest2;
SQL> conn dbatest2
SQL> select count(*) from dbatest.da_users;

COUNT(*)
----------
40

SQL> select count(*) from sys.dba_users;
select count(*) from sys.dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist


DG broker connections

setevent = '16634 trace name context forever, level 1'and restart the Data Guard Broker Process. This will record all Connectstrings used by the Data Guard Broker in the DRC.LOG-Files. So you can see which exact Connect is actually being used by the Data Guard Broker

metalink: [ID 745201.1]

to start and top dgbALTER SYSTEM SET DG_BROKER_START=[TRUE/FALSE];

SQL trace file interpretation

http://wiki.oracle.com/page/Structure+of+an+Extended+SQL+Trace+file


XCTEND rlbk=%d rd_only=%d
XCTEND A transaction end marker.
rlbk 1 if a rollback was performed, 0 if no rollback (commit).
rd_only 1 if transaction was read only, 0 if changes occurred.

prefetch

Execution plan looking like this:

---------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=322 Card=319 Bytes=11803)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TARGET' (Cost=2 Card=1 Bytes=29)
2 1 NESTED LOOPS (Cost=322 Card=319 Bytes=11803)
3 2 TABLE ACCESS (FULL) OF 'DRIVER' (Cost=3 Card=319 Bytes=2552)
4 2 INDEX (RANGE SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=1)


is using a feature called 'prefetch'.

More about it:
http://www.oracle.com/technology/products/oracle9i/daily/dec17.html
http://forums.oracle.com/forums/message.jspa?messageID=4236898

to enable the CBO trace

alter session set tracefile_identifier='_10053';
alter session set events '10053 trace name context forever, level 1';
alter session set events '10053 trace name context off';

http://www.psoug.org/reference/trace_tkprof.html

my old topic...

found my old topic on user groups

main point which I forgot and was looking for:

-- notice "<==" that remote v$session.process = local v$process.spid

incomplete recovery

Even though all of the datafiles might have been restored to the same SCN, still some of them might be left in 'fuzzy' state.
select substr(name,1,40),recover, fuzzy, to_char(checkpoint_change#) checkpoint_scn from v$datafile_header;
select file#,STATUS, FUZZY from v$datafile_header;

to ignore those files:
ALTER DATABASE DATAFILE [datafile name] OFFLINE DROP;
or if you need to open, use hidden parameter:
_allow_resetlogs_corruption=TRUE;
this will open the database even though it's inconsistent. the database then must be rebuilt using export/import.

Refs:
435201.1

ORA-01114 and tempfile id

this error indicates
ORA-01114: IO error writing block to file 1023 (block # 455946)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.

but there's no file with id 1023 in the database.

This is actually a tempfile. Tempfile id is being reckoned from (db_files + dba_temp_files.file_id).
So in this case the value of init.ora parameter "db_files" is 1022 and that makes the first tempfile id = 1023.

fuck all those who say to read documentation. there's supposed to be a metalink note which talks about it, but its hard to find it due to metalinks screwed search engine.
ненавижу бля ебаный оракл.