10ずつ増加するデータを100件投入 
DECLARE /**内部処理回数のカウント数**/ cnt NUMBER; /**内部処理データの値**/ val NUMBER; BEGIN cnt :=1; val := 10000010; WHILE cnt <= 10000 LOOP /**処理開始**/ INSERT INTO TANAKA.TEST_DATA(NO1,NO2) VALUES(val,val); val := val + 10; /**処理完了**/ cnt := cnt + 1; END LOOP; END;
スキーマオブジェクト削除
SET SERVEROUTPUT ON;
DECLARE
/* 削除対象オブジェクト情報取得 */
CURSOR drop_table_data IS
SELECT * FROM DBA_OBJECTS WHERE OWNER='NTT';
/* 削除処理開始 */
BEGIN
FOR drop_table IN drop_table_data LOOP
IF (drop_table.OBJECT_TYPE = 'TABLE') THEN
DBMS_OUTPUT.PUT_LINE (drop_table.OBJECT_TYPE);
/* テーブル削除 [DROP OBJECT_TYPE OBJECT_NAME CASCADE CONSTRAINTS;] */
EXECUTE IMMEDIATE 'DROP '||drop_table.OBJECT_TYPE||' '||drop_table.OWNER||'.'||drop_table.OBJECT_NAME||' CASCADE CONSTRAINTS';
ELSE
DBMS_OUTPUT.PUT_LINE (drop_table.OBJECT_TYPE);
/* その他オブジェクト削除 [DROP OBJECT_TYPE OBJECT_NAME;] */
EXECUTE IMMEDIATE 'DROP '||drop_table.OBJECT_TYPE||' '||drop_table.OWNER||'.'||drop_table.OBJECT_NAME||'';
END IF;
END LOOP;
END;
/
DECLARE
/* 削除対象オブジェクト情報取得 */
CURSOR drop_table_data IS
SELECT * FROM DBA_OBJECTS WHERE OWNER='NTT';
/* 削除処理開始 */
BEGIN
FOR drop_table IN drop_table_data LOOP
IF (drop_table.OBJECT_TYPE = 'TABLE') THEN
DBMS_OUTPUT.PUT_LINE (drop_table.OBJECT_TYPE);
/* テーブル削除 [DROP OBJECT_TYPE OBJECT_NAME CASCADE CONSTRAINTS;] */
EXECUTE IMMEDIATE 'DROP '||drop_table.OBJECT_TYPE||' '||drop_table.OWNER||'.'||drop_table.OBJECT_NAME||' CASCADE CONSTRAINTS';
ELSE
DBMS_OUTPUT.PUT_LINE (drop_table.OBJECT_TYPE);
/* その他オブジェクト削除 [DROP OBJECT_TYPE OBJECT_NAME;] */
EXECUTE IMMEDIATE 'DROP '||drop_table.OBJECT_TYPE||' '||drop_table.OWNER||'.'||drop_table.OBJECT_NAME||'';
END IF;
END LOOP;
END;
/
現行ユーザーテーブル件数カウント
set serveroutput on
SET SERVEROUTPUT ON SIZE 1000000
declare
tname varchar2(100);
tcount number;
count_sql varchar2(300);
begin
for tab_rec in (select table_name from user_tables) loop
tname := tab_rec.table_name;
count_sql := 'select count(*) from ' || tname;
execute immediate count_sql into tcount;
dbms_output.put_line( tname || ',' || tcount );
end loop;
end;
/
SET SERVEROUTPUT ON SIZE 1000000
declare
tname varchar2(100);
tcount number;
count_sql varchar2(300);
begin
for tab_rec in (select table_name from user_tables) loop
tname := tab_rec.table_name;
count_sql := 'select count(*) from ' || tname;
execute immediate count_sql into tcount;
dbms_output.put_line( tname || ',' || tcount );
end loop;
end;
/
自動コンパイル��
SET SERVEROUTPUT ON;
DECLARE
CURSOR cu IS
SELECT * from dba_objects where owner='NTT' and status ='INVALID'
AND OBJECT_NAME NOT IN ('SELECT_DATA');
BEGIN
FOR REC IN cu LOOP
dbms_output.put_line (REC.OBJECT_NAME);
EXECUTE IMMEDIATE 'alter '||REC.OBJECT_TYPE||' '||REC.OWNER||'.'|| REC.OBJECT_NAME||' compile';
END LOOP;
END;
/
自動コンパイル��
SET SERVEROUTPUT ON;
DECLARE
/* ステータスが無効（INVALID）のオブジェクトを検索 */
CURSOR cu IS
SELECT * from dba_objects where owner='NTT' and status ='INVALID' ;
BEGIN
FOR REC IN cu LOOP
/* オブジェクトタイプが「PACKAGE BODY」のものはオプションが「COMPILE BODY」のため先に別途検索 */
IF (REC.OBJECT_TYPE = 'PACKAGE BODY') THEN
dbms_output.put_line (REC.OBJECT_NAME);
EXECUTE IMMEDIATE 'alter '||REC.OBJECT_TYPE||' '||REC.OWNER||'.'|| REC.OBJECT_NAME||' COMPILE BODY';
ELSE
dbms_output.put_line (REC.OBJECT_NAME);
EXECUTE IMMEDIATE 'alter '||REC.OBJECT_TYPE||' '||REC.OWNER||'.'|| REC.OBJECT_NAME||' COMPILE';
END IF;
END LOOP;
END;
/
ユーザーオブジェトを削除
SET SERVEROUTPUT ON;
DECLARE
CURSOR cu IS
SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';
BEGIN
FOR obj IN cu LOOP
DBMS_OUTPUT.PUT_LINE (obj.OBJECT_NAME);
EXECUTE IMMEDIATE 'DROP '|| obj.OBJECT_TYPE ||' '|| obj.OBJECT_NAME ||' CASCADE';
END LOOP;
END;
/
DECLARE
CURSOR cu IS
SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';
BEGIN
FOR obj IN cu LOOP
DBMS_OUTPUT.PUT_LINE (obj.OBJECT_NAME);
EXECUTE IMMEDIATE 'DROP '|| obj.OBJECT_TYPE ||' '|| obj.OBJECT_NAME ||' CASCADE';
END LOOP;
END;
/
タグ
このページへのコメント
ouNlVT Im obliged for the blog article. Great.
FrilZS Great, thanks for sharing this blog post.Really thank you! Awesome.
Cx5z8p <a href="http://pstjhnvjuwsi.com/">pstjhnvjuwsi</a>, [url=http://hwbsnnlzvdir.com/]hwbsnnlzvdir[/url], [link=http://iaormlsfdaco.com/]iaormlsfdaco[/link], http://jzpbcgevcsbs.com/