Oracle Example

import java.io.*;
import java.sql.*;
import java.util.*;


/*
D:\temp>d:\jdk1.3\bin\javac -classpath ".;c:\orant\jdbc\lib\classes111.zip" Tuning.java
D:\temp>d:\jdk1.3\bin\java -classpath ".;c:\orant\jdbc\lib\classes111.zip" Tuning
*/

public class Report
{
  static String sid = "localhost";
  static String owner = "OWNER";

  public static void drop_pk(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
		try
		{
			System.out.println("Drop Primary Key...");
			out.println("rem Drop Primary Key...");

			String string1 = new String();
			String string2 = new String();

			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("select table_name from dba_constraints where owner = '" + owner + "' and constraint_type = 'P'");
			System.out.println("select table_name from dba_constraints where owner = '" + owner + "' and constraint_type = 'P'");
			out.println("rem select table_name from dba_constraints where owner = '" + owner + "' and constraint_type = 'P'");
			while (rset.next())
			{
				string1 = rset.getString(1);
				System.out.println(string1);
				out.println("alter table " + string1 + " drop primary key cascade;");
			}
      out.println(" ");
			stmt.close();
		}

		catch(SQLException sqle)
		{
			err.println(sqle.toString());
			err.println(sqle.getErrorCode());
		}
	}

  public static void drop_indexes(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
		try
		{
			System.out.println("Drop Indexes...");
			out.println("rem Drop Indexes...");

			String string1 = new String();
			String string2 = new String();

			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("select index_name from dba_indexes where owner = '" + owner + "'");
			System.out.println("select index_name from dba_indexes where owner = '" + owner + "'");
			out.println("rem select index_name from dba_indexes where owner = '" + owner + "'");

			while (rset.next())
			{
				string1 = rset.getString(1);
				System.out.println(string1);
				out.println("drop index " + string1 + ";");
			}
      out.println(" ");
			stmt.close();
		}

		catch(SQLException sqle)
		{
			err.println(sqle.toString());
			err.println(sqle.getErrorCode());
		}
	}


  public static void create_unique_index(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
    System.out.println("Create Unique Index");

		String string1 = new String();
		String string2 = new String();
    String string3 = new String();

		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery("select index_name, table_name, tablespace_name from dba_indexes where owner = 'OWNER' and uniqueness = 'UNIQUE'");
   	while (rset.next())
		{
			string1 = rset.getString(1);
			string2 = rset.getString(2);
      string3 = rset.getString(3);

			String string4 = new String();
			String string5 = new String();

      Statement stmt2 = conn.createStatement();
 			ResultSet rset2 = stmt2.executeQuery("select column_name, column_position from dba_ind_columns where index_owner = '" + owner + "' and index_name = '" + string1 + "' order by column_position");
     	while (rset2.next())
			{
				string4 = rset2.getString(1);
				string5 = rset2.getString(2);

				if (string5.compareTo("1") == 0)
        {
          out.println("create unique index " + string1 + " on " + string2);
          out.print("( " + string4);
        }
				else
          out.print(", " + string4);
			}
			stmt2.close();

      out.println(") tablespace " + string3 + ";");
      out.println(" ");
		}
    out.println(" ");
		stmt.close();

  }

  public static void create_index(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
    System.out.println("Create Index");

		String string1 = new String();
		String string2 = new String();
    String string3 = new String();

		Statement stmt = conn.createStatement();
    //System.out.println("select index_name, table_name from dba_indexes where owner = 'OWNER' and uniqueness = 'NONUNIQUE'");
		ResultSet rset = stmt.executeQuery("select index_name, table_name, tablespace_name from dba_indexes where owner = '" + owner + "' and uniqueness = 'NONUNIQUE'");
   	while (rset.next())
		{
			string1 = rset.getString(1);
			string2 = rset.getString(2);
      string3 = rset.getString(3);

			String string4 = new String();
			String string5 = new String();

      Statement stmt2 = conn.createStatement();
      //System.out.println("select column_name, column_position from dba_ind_columns where index_owner = 'OWNER' and index_name = '" + string1 + "' order by column_position");
			ResultSet rset2 = stmt2.executeQuery("select column_name, column_position from dba_ind_columns where index_owner = '" + owner + "' and index_name = '" + string1 + "' order by column_position");
     	while (rset2.next())
			{
				string4 = rset2.getString(1);
				string5 = rset2.getString(2);

				if (string5.compareTo("1") == 0)
        {
          out.println("create index " + string1 + " on " + string2);
          out.print("( " + string4);
        }

				else
          out.print(", " + string4);
			}
			stmt2.close();

      out.println(") tablespace " + string3 + ";");
      out.println(" ");
		}
    out.println(" ");
		stmt.close();

  }

  public static void add_pk(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
		System.out.println("Add Primary Key");

		String str_owner = new String();
		String str_constraint_name = new String();
		String str_table_name = new String();

		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery("select owner, constraint_name, table_name from dba_constraints where owner = '" + owner + "' and constraint_type = 'P'");

		while (rset.next())
		{
			str_owner = rset.getString(1);
			str_constraint_name = rset.getString(2);
			str_table_name = rset.getString(3);


			String str_column_name = new String();
			String str_position = new String();

      Statement stmt2 = conn.createStatement();
			ResultSet rset2 = stmt2.executeQuery("select column_name, position from dba_cons_columns where owner = '" + owner + "' and constraint_name = '" + str_constraint_name + "' order by position");

			while (rset2.next())
			{
				str_column_name = rset2.getString(1);
				str_position = rset2.getString(2);

				if (str_position.compareTo("1") == 0)
        {
          out.print("alter table " + str_table_name + " add constraint " + str_constraint_name + " primary key ");
          out.print("( " + str_column_name);
        }
				else
          out.print(", " + str_column_name);
			}
			stmt2.close();

      out.println(");");
      out.println(" ");
		}
    out.println(" ");
		stmt.close();
	}

  public static void add_fk(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
		System.out.println("Add Foreign Key");

		String str_owner = new String();
		String str_constraint_name = new String();
    String str_r_constraint_name = new String();
		String str_table_name = new String();
    String str_delete_rule = new String();

		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery("select owner, constraint_name, table_name, r_constraint_name, delete_rule from dba_constraints where owner = 'OWNER' and constraint_type = 'R'");

		while (rset.next())
		{
			str_owner = rset.getString(1);
			str_constraint_name = rset.getString(2);
			str_table_name = rset.getString(3);
      str_r_constraint_name = rset.getString(4);
      str_delete_rule = rset.getString(5);

			String str_column_name = new String();
			String str_position = new String();

      Statement stmt2 = conn.createStatement();
			ResultSet rset2 = stmt2.executeQuery("select column_name, position from dba_cons_columns where owner = 'OWNER' and constraint_name = '" + str_constraint_name + "' order by position");

			while (rset2.next())
			{
				str_column_name = rset2.getString(1);
				str_position = rset2.getString(2);

				if (str_position.compareTo("1") == 0)
          out.print("alter table " + str_table_name + " add constraint " + str_constraint_name + " foreign key (" + str_column_name);
				else
          out.print(", " + str_column_name);
			}
			stmt2.close();

      String str_references = new String();

      Statement stmt3 = conn.createStatement();
      ResultSet rset3 = stmt3.executeQuery("select table_name from dba_constraints where constraint_name = '" + str_r_constraint_name + "'");

      while (rset3.next())
			{
				str_references = rset3.getString(1);
      }

      stmt3.close();

      out.print(") references " + str_references + " ");

      // My New
      Statement stmt4 = conn.createStatement();
			ResultSet rset4 = stmt4.executeQuery("select column_name, position from dba_cons_columns where owner = 'OWNER' and constraint_name = '" + str_r_constraint_name + "' order by position");

			while (rset4.next())
			{
				str_column_name = rset4.getString(1);
				str_position = rset4.getString(2);

				if (str_position.compareTo("1") == 0)
          out.print("(" + str_column_name);
				else
          out.print(", " + str_column_name);
			}
			stmt4.close();
      // End My New
    if (str_delete_rule.compareTo("CASCADE") == 0)
      out.println(") on delete cascade;");
    else
      out.println(");");
		}
		stmt.close();
	}

   public static void add_fk2(PrintWriter out, PrintWriter err, Connection conn) throws SQLException
	{
		System.out.println("Add Foreign Key");

		String str_owner = new String();
		String str_constraint_name = new String();
    String str_r_constraint_name = new String();
		String str_table_name = new String();
    String my_new_string = new String();

		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery("select owner, constraint_name, table_name, r_constraint_name from dba_constraints where owner = 'OWNER' and constraint_type = 'R'");

		while (rset.next())
		{
			str_owner = rset.getString(1);
			str_constraint_name = rset.getString(2);
			str_table_name = rset.getString(3);
      str_r_constraint_name = rset.getString(4);

			String str_column_name = new String();
			String str_position = new String();

      Statement stmt2 = conn.createStatement();
			ResultSet rset2 = stmt2.executeQuery("select column_name, position from dba_cons_columns where owner = 'OWNER' and constraint_name = '" + str_constraint_name + "' order by position");

			while (rset2.next())
			{
				str_column_name = rset2.getString(1);
				str_position = rset2.getString(2);

				if (str_position.compareTo("1") == 0)
        {
          out.print("alter table " + str_table_name + " add constraint " + str_constraint_name + " foreign key (" + str_column_name);
          my_new_string = "( " + str_column_name;
        }
				else
        {
          out.print(", " + str_column_name);
          my_new_string = my_new_string + ", " + str_column_name;
        }
			}
			stmt2.close();

      String str_references = new String();

      Statement stmt3 = conn.createStatement();
      ResultSet rset3 = stmt3.executeQuery("select table_name from dba_constraints where constraint_name = '" + str_r_constraint_name + "'");

      while (rset3.next())
			{
				str_references = rset3.getString(1);
      }

      stmt3.close();

      out.println(") references " + str_references + " " + my_new_string + ") ;");
		}
		stmt.close();
	}

  public static void VectorSort(Vector myVector, PrintWriter out2, PrintWriter err, String replace)
  {
      String [] myArray = new String[myVector.size()];
      String token1 = new String();
      String token2 = new String();
      String token3 = new String();

      System.out.println("VectorSort");
      for (int i = 0; i < myVector.size(); i++)
      {
        //System.out.println(myVector.elementAt(i));
        myArray[i] = myVector.elementAt(i).toString();
        //System.out.println("myArray[" + i + "] = " + myArray[i]);
        //out2.println("myArray[" + i + "] = " + myArray[i]);
      }


      java.util.Arrays.sort(myArray);
      out2.println("rem My Sorted Array");
      //out2.println(myArray);

      for (int i = 0; i < myVector.size(); i++)
      {
        String tempString = myArray[i];
        StringTokenizer ST = new StringTokenizer(tempString, "|", true);
        //out2.println("Sorted myArray[" + i + "] = " + myArray[i]);
        token1 = ST.nextToken();
        token2 = ST.nextToken();
        token3 = ST.nextToken();

        //+out2.println("token1 = " + token1 + " token2 = " + token2 + " token3 = " + token3);

        if (i%2==0)
        {
          out2.println(token3);
          out2.println(" rem " + token1);
        }
        else
          out2.println("rem " + token3 + " rem "  + token1);
      }

  }

   public static void drop_extra_constraints(PrintWriter out, PrintWriter out2, PrintWriter err, Connection conn) throws SQLException
	{
		try
		{
      Vector myVector = new Vector();

			System.out.println("Drop Extra Constraints...");
			out.println("rem Drop Extra Constraints...");

			String string1 = new String();
			String string2 = new String();
      String string3 = new String();
      String newString3 = new String();

			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("select table_name, constraint_name, search_condition from dba_constraints where owner = '" + owner + "' and constraint_type = 'C' order by table_name");
			System.out.println("select table_name, constraint_name, search_condition from dba_constraints where owner = '" + owner + "' and constraint_type = 'C'");
			out.println("rem select table_name, constraint_name, search_condition from dba_constraints where owner = '" + owner + "' and constraint_type = 'C'");

      int i = 0;

			while (rset.next())
			{
        i++;
				string1 = rset.getString(1);
        string2 = rset.getString(2);
        string3 = rset.getString(3);

				//System.out.println(string1);
				//out.println("alter table " + string1 + " drop constraint " + string2 + "; rem Search: " + string3);

        if (string3.charAt(0) != '"')
        {
          newString3 = "\"" + string3;
          myVector.add(string1 + newString3 + " | alter table " + string1 + " drop constraint " + string2 + ";");
        }
        else
          myVector.add(string1 + string3 + " | alter table " + string1 + " drop constraint " + string2 + ";");
			}

      VectorSort(myVector, out2, err, string1+string3);

      out.println(" ");
			stmt.close();
		}

		catch(SQLException sqle)
		{
			err.println(sqle.toString());
			err.println(sqle.getErrorCode());
		}
	}


	public static void main(String[] args) throws IOException, SQLException
	{
		try
		{
			System.out.println("Welcome to Report");

			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:pmdcrs01", "scott", "tiger");
      			Connection conn2 = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:dev1", "scott", "tiger");

			String file_name = "c:\\temp\\" + sid + ".sql";
			String file_name2 = "c:\\temp\\Dev1DropConstraints.sql";

			PrintWriter out = new PrintWriter(new BufferedWriter(new FileWriter(file_name)));
      			PrintWriter out2 = new PrintWriter(new BufferedWriter(new FileWriter(file_name2)));
			PrintWriter err = new PrintWriter(new BufferedWriter(new FileWriter("Exception.txt")));

      			drop_pk(out, err, conn2);
      			drop_indexes(out, err, conn2);
     			create_unique_index(out, err, conn);
      			create_index(out, err, conn);
      			add_pk(out, err, conn);
      			add_fk(out, err, conn);
      			drop_extra_constraints(out, out2, err, conn2);
     			 out.close();
      			out2.close();
      			System.out.println("I just closed");
      			System.out.println(file_name);
      			System.out.println(file_name2);
      			System.out.println("Finished...");
		}

		catch(SQLException sqle) { System.out.println(sqle.toString()); }
		catch(IOException ioe) { System.out.println(ioe.toString()); }
	}
}