농부지기

 
1. foreignkey : 잠시 stop
    ALTER TABLE  table_name    DISABLE CONSTRAINT  foreign_key_name;
 
2. foreignkey : restart
    ALTER TABLE  table_name    ENABLE CONSTRAINT  foreign_key_name;   
 
 
* foreign key stop sql 문장 생성
SELECT 'ALTER TABLE  QMDBA.' || TABLE_NAME || '   ENABLE CONSTRAINT ' || CONSTRAINT_NAME || ';'
  FROM ALL_CONSTRAINTS
 WHERE OWNER = 'QMDBA'
   AND CONSTRAINT_TYPE = 'R'
 ORDER BY CONSTRAINT_NAME ;

☞ 1. foreign key 삭제

 - ALTER TABLE 테이블이름 DROP CONSTRAINT 외부키이름;

- alter table TABLE_NAME disable constraint FOREIGN_KEY_NAME cascade;
 

☞ 3. foreign 잠시 start(enable) 시키기

- alter table TABLE_NAME enable constraint FOREIGN_KEY_NAME ;
 

--하나의 테이블에 대한 constraint list 조회
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, STATUS
FROM   USER_CONSTRAINTS
WHERE  TABLE_NAME = 'PU_ENT_NOTE_MANG';

--모든 테이블의 foreign key list조회
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, STATUS
FROM   USER_CONSTRAINTS
WHERE  CONSTRAINT_TYPE = 'R';

SELECT a.tablespace_name, ROUND(SUM(a.total) / 1024/1024, 2)             "Totab(M)"
     , ROUND((SUM(a.total) - SUM(NVL(b.free, 0))) / 1024/1024,2)         "Used(M)"
     , ROUND((SUM(NVL(b.free, 0))) / 1024/1024, 2)                       "Free(ML)"
     , ROUND((SUM(a.total) - SUM(NVL(b.free, 0)))/SUM(a.total) * 100, 2) "Used(%)"
FROM (SELECT d.tablespace_name, d.file_id, SUM(d.bytes) total
      FROM   dba_data_files d
      GROUP  BY d.tablespace_name, d.file_id) a,
     (SELECT f.file_id, SUM(f.bytes) free FROM dba_free_space f
      GROUP  BY f.file_id) b
WHERE a.file_id = b.file_id(+)
GROUP BY a.tablespace_name;