Oracle使用存储过程批量导出建表语句和索引语句

--1. 用system用户创建路径 datA_dump用于存放文件
-- /u01/dump必需是已经存在的目录
create or replace directory DATA_DUMP as '/u01/dump';
--用sys用户登录给要访问的用户指定访问目录的权限
grant write on directory DATA_DUMP to hr;
grant read on directory DATA_DUMP to hr;
--2. 在需要导出表结构的用户下创建存储过程（导出单个表结构）

CREATE OR REPLACE PROCEDURE P_EXPORTDLL(P_TABLE_NAME VARCHAR2,
                                        P_FILENAME   VARCHAR2) IS
BEGIN
 
  DECLARE
    L_FILE     UTL_FILE.FILE_TYPE;
    L_BUFFER   VARCHAR2(1000);
    L_AMOUNT   BINARY_INTEGER := 100;
    L_POS      INTEGER := 1;
    L_CLOB     CLOB;
    L_CLOB_LEN INTEGER;
  BEGIN
    SELECT DBMS_METADATA.GET_DDL('TABLE', P_TABLE_NAME) || ';'
      INTO L_CLOB
      FROM DUAL;
    L_CLOB_LEN := DBMS_LOB.GETLENGTH(L_CLOB);
    L_FILE     := UTL_FILE.FOPEN('DATA_DUMP', P_FILENAME || '.sql', 'a', 1000);
 
    WHILE L_POS < L_CLOB_LEN LOOP
      DBMS_LOB.READ(L_CLOB, L_AMOUNT, L_POS, L_BUFFER);
      UTL_FILE.PUT(L_FILE, L_BUFFER);
      L_POS := L_POS + L_AMOUNT;
    END LOOP;
    UTL_FILE.FCLOSE(L_FILE);
  END;
END P_EXPORTDLL;

--3. 创建导出所有结构的存储过程函数

CREATE OR REPLACE PROCEDURE P_WHOLE AS
BEGIN
  FOR X IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
    P_EXPORTDLL(X.TABLE_NAME, 'hr_tab');
  END LOOP;
END;

--4. 调用存储过程，批量导出表结构

--EXEC P_WHOLE;

##############################################

##在需要导出表结构的用户下创建存储过程（导出单个索引结构）

CREATE OR REPLACE PROCEDURE P_EXPORTDLL_IND(P_TABLE_INDEX VARCHAR2,
                                        P_FILENAME   VARCHAR2) IS
BEGIN
 
  DECLARE
    L_FILE     UTL_FILE.FILE_TYPE;
    L_BUFFER   VARCHAR2(1000);
    L_AMOUNT   BINARY_INTEGER := 100;
    L_POS      INTEGER := 1;
    L_CLOB     CLOB;
    L_CLOB_LEN INTEGER;
  BEGIN
    SELECT DBMS_METADATA.GET_DDL('INDEX', P_TABLE_INDEX) || ';'
      INTO L_CLOB
      FROM DUAL;
    L_CLOB_LEN := DBMS_LOB.GETLENGTH(L_CLOB);
    L_FILE     := UTL_FILE.FOPEN('DATA_DUMP', P_FILENAME || '.sql', 'a', 1000);
 
    WHILE L_POS < L_CLOB_LEN LOOP
      DBMS_LOB.READ(L_CLOB, L_AMOUNT, L_POS, L_BUFFER);
      UTL_FILE.PUT(L_FILE, L_BUFFER);
      L_POS := L_POS + L_AMOUNT;
    END LOOP;
    UTL_FILE.FCLOSE(L_FILE);
  END;
END P_EXPORTDLL_IND;

####创建导出所有索引结构的存储过程函数

CREATE OR REPLACE PROCEDURE P_WHOLE_IND AS
BEGIN
  FOR X IN (SELECT INDEX_NAME FROM USER_INDEXES) LOOP
    P_EXPORTDLL_IND(X.INDEX_NAME, 'hr_ind');
  END LOOP;
END;

 

----EXEC P_WHOLE_IND;

 

感谢&参考https://blog.csdn.net/qq_24452475/article/details/53159679