wif LiNoUz/Oracle,Sql
Table_Random 컬럼 생성
크라소니
2012. 10. 26. 14:00
--랜덤 생성 쿼리 좋다, procedure로 10000개 생성해보자.
INSERT into meta_tables(OWNER
,TABLE_NAME
, TABLESPACE_NAME)
values ('TEST_AAA'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC')
,'TEST_BBB'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC')
,'TEST_CCC'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC'));
BEGIN
FOR i IN 1 .. 5000
LOOP
INSERT INTO meta_tables(OWNER,
TABLE_NAME,
TABLESPACE_NAME,
CLUSTER_NAME,
IOT_NAME,
STATUS,
PCT_FREE,
PCT_USED,
INI_TRANS,
MAX_TRANS,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
FREELISTS,
FREELIST_GROUPS,
LOGGING,
BACKED_UP,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS,
NUM_FREELIST_BLOCKS,
DEGREE,
INSTANCES,
CACHE,
TABLE_LOCK,
SAMPLE_SIZE,
LAST_ANALYZED,
PARTITIONED,
IOT_TYPE,
TEMPORARY,
SECONDARY,
NESTED,
BUFFER_POOL,
FLASH_CACHE,
CELL_FLASH_CACHE,
ROW_MOVEMENT,
GLOBAL_STATS,
USER_STATS,
DURATION,
SKIP_CORRUPT,
MONITORING,
CLUSTER_OWNER,
DEPENDENCIES,
COMPRESSION,
COMPRESS_FOR,
DROPPED,
READ_ONLY,
SEGMENT_CREATED,
RESULT_CACHE)
VALUES ('1234567'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --OWNER, VARCHAR2(30)
,'1234567'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --TABLE_NAME, VARCHAR2(30)
,'1234567'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --TABLESPACE_NAME, VARCHAR2(30)
,'1234567'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --CLUSTER_NAME, VARCHAR2(30)
,'1234567'||RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --IOT_NAME, VARCHAR2(30)
,'12345678' --STATUS, VARCHAR2(8)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --PCT_FREE, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --PCT_USED, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --INI_TRANS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --MAX_TRANS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --INITIAL_EXTENT, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --NEXT_EXTENT, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --MIN_EXTENTS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --MAX_EXTENTS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --PCT_INCREASE, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --FREELISTS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --FREELIST_GROUPS, NUMBER
,'AAA' --LOGGING, VARCHAR2(3)
,'A' --BACKED_UP, VARCHAR2(1)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --NUM_ROWS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --BLOCKS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --EMPTY_BLOCKS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --AVG_SPACE, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --CHAIN_CNT, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --AVG_ROW_LEN, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --AVG_SPACE_FREELIST_BLOCKS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4) --NUM_FREELIST_BLOCKS, NUMBER
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --DEGREE, VARCHAR2(40)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --INSTANCES, VARCHAR2(40)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --CACHE, VARCHAR2(20)
,'12345678' --TABLE_LOCK, VARCHAR2(8)
,'7777777777777' --SAMPLE_SIZE, NUMBER
,to_date(sysdate) --LAST_ANALYZED, DATE
,'AAA' --PARTITIONED, VARCHAR2(3)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --IOT_TYPE, VARCHAR2(12)
,'A' --TEMPORARY, VARCHAR2(1)
,'A' --SECONDARY, VARCHAR2(1)
,'AAA' --NESTED, VARCHAR2(3)
,'1234567' --BUFFER_POOL, VARCHAR2(7)
,'1234567' --FLASH_CACHE, VARCHAR2(7)
,'1234567' --CELL_FLASH_CACHE, VARCHAR2(7)
,'12345678' --ROW_MOVEMENT, VARCHAR2(8)
,'AAA' --GLOBAL_STATS, VARCHAR2(3)
,'AAA' --USER_STATS, VARCHAR2(3)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --DURATION, VARCHAR2(15)
,'12345678' --SKIP_CORRUPT, VARCHAR2(8)
,'AAA' --MONITORING, VARCHAR2(3)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --CLUSTER_OWNER, VARCHAR2(30)
,'12345678' --DEPENDENCIES, VARCHAR2(8)
,'12345678' --COMPRESSION, VARCHAR2(8)
,RPAD(CEIL(DBMS_RANDOM.VALUE*1000),4,'ABC') --COMPRESS_FOR, VARCHAR2(12)
,'AAA' --DROPPED, VARCHAR2(3)
,'AAA' --READ_ONLY, VARCHAR2(3)
,'AAA' --SEGMENT_CREATED, VARCHAR2(3)
,'1234567');
END LOOP;
END;
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
--세션에 혹시 LOCK 걸렸나 체크 하는것 반드시 sys계정으로
select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid and
b.id1=c.object_id and
b.type='TM' and c.object_name='META_TABLES';
-- 이후 SID 나오면
-- alter system kill session 'SID, SERIAL';
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------