最近用到的,因为plsql是收费的,不让用,找了很多方法终于发现了这个。
核心语句
- SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name), U.OBJECT_TYPE
- FROM USER_OBJECTS U
- where U.OBJECT_TYPE = 'TABLE'
- or U.OBJECT_TYPE = 'VIEW'
- or U.OBJECT_TYPE = 'INDEX'
- or U.OBJECT_TYPE = 'PROCEDURE'
- or U.OBJECT_TYPE = 'SEQUENCE'
- or U.OBJECT_TYPE = 'TRIGGER'
- order by U.OBJECT_TYPE desc
自己写的Java方法,未做封装。
- package sql;
- import java.io.FileInputStream;
- import java.io.FileWriter;
- import java.sql.Clob;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Properties;
- public class Main {
- private static final String TYPE_MARK = "-1";
- private static String SQL =
- "SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name), U.OBJECT_TYPE " +
- "FROM USER_OBJECTS U " +
- "where U.OBJECT_TYPE = 'TABLE' " +
- "or U.OBJECT_TYPE = 'VIEW' " +
- "or U.OBJECT_TYPE = 'INDEX' " +
- "or U.OBJECT_TYPE = 'PROCEDURE' " +
- "or U.OBJECT_TYPE = 'SEQUENCE' " +
- "or U.OBJECT_TYPE = 'TRIGGER' " +
- "order by U.OBJECT_TYPE desc";
- private static String URL = "jdbc:oracle:thin:@192.168.1.2:1521:orcl";
- private static String USERNAME = "abc";
- private static String PASSWORD = "abc";
- private static String OUTFILE = "tables.sql";
- /**
- * @param args
- * @throws Exception
- * @throws
- */
- public static void main(String[] args) throws Exception {
- // TODO Auto-generated method stub
- Properties properties = new Properties();
- properties.load(new FileInputStream("config.properties"));
- URL = properties.getProperty("url", URL);
- USERNAME = properties.getProperty("username", USERNAME);
- PASSWORD = properties.getProperty("password", PASSWORD);
- OUTFILE = properties.getProperty("outfile", OUTFILE);
- SQL = properties.getProperty("sql", SQL);
- FileWriter fw = new FileWriter(OUTFILE);
- Class.forName("oracle.jdbc.driver.OracleDriver");
- Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
- Statement statement = con.createStatement();
- ResultSet rs = statement.executeQuery(SQL);
- Clob ddl;
- String type = TYPE_MARK;
- int count = 0;
- List<String> list = new ArrayList<String>();
- while(rs.next()) {
- ddl = rs.getClob(1);
- fw.write(ddl.getSubString(1L, (int)ddl.length()));
- if(!rs.getString(2).equals(type)) {
- if(!type.equals(TYPE_MARK)) {
- list.add(type + "," + count);
- type = rs.getString(2);
- count = 1;
- } else {
- type = rs.getString(2);
- count ++;
- }
- } else
- count ++;
- }
- list.add(type + "," + count);
- fw.flush();
- fw.close();
- rs.close();
- statement.close();
- con.close();
- for(String type1 : list)
- System.out.print(type1.split(",")[0] + ":" + type1.split(",")[1] + ";");
- System.out.println();
- }
- }
config.properties
- url=jdbc:oracle:thin:@192.168.1.2:1521:orcl
- username=abc
- password=abc
- outfile=tables.sql
- sql=SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name), U.OBJECT_TYPE \
- FROM USER_OBJECTS U \
- where U.OBJECT_TYPE = 'TABLE' \
- or U.OBJECT_TYPE = 'VIEW' \
- or U.OBJECT_TYPE = 'INDEX' \
- or U.OBJECT_TYPE = 'PROCEDURE' \
- or U.OBJECT_TYPE = 'SEQUENCE' \
- or U.OBJECT_TYPE = 'TRIGGER' \
- order by U.OBJECT_TYPE desc