|
|
IOTN :: Field Book :: ORACLE 
|
|
ALTER SYSTEM SET deferred_segment_creation=FALSE; / database_properties / impdp network_link
|
|
최길호
[LIST]
|
2025-07-07 10:57:12, 조회 : 2 |
19c default deferred_segment_creation = true
-- 상관 없는 memo
select PROPERTY_NAME||': '||PROPERTY_VALUE from database_properties;
GLOBAL_DB_NAME: UHATIS.REGRESS.RDBMS.DEV.US.ORACLE.COM
DEFAULT_PERMANENT_TABLESPACE: USERS
DEFAULT_TEMP_TABLESPACE: TEMP
-- dblink
CREATE SHARED DATABASE LINK dblink CONNECT TO uscott IDENTIFIED BY "pwd"
AUTHENTICATED BY uscott IDENTIFIED BY "pwd" using 'localhost/DB';
-- impdp
impdp nscott/pwd directory=expdp NETWORK_LINK=dblink remap_schema=uSCOTT:NSCOTT \
remap_tablespace=USERS:NSCOTT,TS_SCOTT10_D01:NSCOTT,TS_SCOTT20_D01:NSCOTT,TS_SCOTT20_I01:NSCOTT schemas=USCOTT \
logfile=NSCOTT.imp.`date +%w`.log ACCESS_METHOD=INSERT_AS_SELECT TRANSFORM=OID:N LOGTIME=LOGFILE METRICS=Y
-- +USCOTT impdp network_link 사용시 0 row table import 일부 안됨.
select 'select count(1) from USCOTT.'||table_name||';' from
(select table_name from dba_tables where owner='USCOTT'
minus
select table_name from dba_tables where owner='NSCOTT');
301 rows selected. [ 안된건수 ]
col owner for a10
col object_type for a20
select owner,object_type,count(1) from dba_objects where owner in('NSCOTT','USCOTT') group by owner,object_type order by 2,1;
OWNER OBJECT_TYPE COUNT(1)
---------- -------------------- ----------
NSCOTT TABLE 1349
USCOTT TABLE 1649
-- temp table [ imp 정상 ]
select owner||'.'||table_name from dba_tables where owner in('NSCOTT','USCOTT') and temporary='Y';
-- dba_segments
select owner||'.'||segment_name||' ts:'||tablespace_name||' bytes:'||bytes||' blocks:'||blocks
from dba_segments where segment_name in ('SHOP','TABLOG','TABMSG') order by 1;
NSCOTT.TABLOG ts:NSCOTT bytes:65536 blocks:8
NSCOTT.TABMSG ts:NSCOTT bytes:65536 blocks:8
USCOTT.SHOP ts:TS_SCOTT20_D01 bytes:65536 blocks:8 [ 같은데 들어가지 않았다. ]
USCOTT.TABLOG ts:TS_SCOTT20_D01 bytes:65536 blocks:8
USCOTT.TABMSG ts:TS_SCOTT20_D01 bytes:65536 blocks:8
-- dba_tables
select owner||'.'||table_name||' clu:'||cluster_name||' iot:'||iot_name||' stat:'||status||' init:'||initial_extent||' next:'||next_extent from dba_tables
where table_name in ('SHOP','TABLOG','TABMSG') order by 1;
NSCOTT.TABLOG clu: iot: stat:VALID init:65536 next:1048576
NSCOTT.TABMSG clu: iot: stat:VALID init:65536 next:1048576
USCOTT.SHOP clu: iot: stat:VALID init:65536 next:1048576 [ 같은데 들어가지 않았다. ]
USCOTT.TABLOG clu: iot: stat:VALID init:65536 next:1048576
USCOTT.TABMSG clu: iot: stat:VALID init:65536 next:1048576
-- DDL 이상 없음.
set pagesize 2000 long 90000
select dbms_metadata.GET_DDL('TABLE','TAB200','USCOTT') from dual;
-- 조치
http://iotn.co.kr/power/zboard.php?id=9_1_bbs&no=2242
ALTER SYSTEM SET deferred_segment_creation=FALSE;
-- create
conn nscott/pwd
set pages 0 lines 200
spool /tmp/nh.sql
select 'create table '||table_name||' as select * from USCOTT.'||table_name||'@dblink;' from
(select table_name from dba_tables where owner='USCOTT'
minus
select table_name from dba_tables where owner='NSCOTT');
|
216.73.216.123
|
|
|
|
Copyright 1999-2025 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
07/07 