2 Replies Latest reply: Sep 14, 2011 9:35 AM by 888206 RSS

    dropping objects using a script.

    888206
      Create a script that will drop all the objects owned by you on the dsa10 database. Please just create the script by using an appropriate select statement and data dictionary views. Spool the output of the select statement to a file.

      How will I go about doing this?
        • 1. Re: dropping objects using a script.
          Nadeem M
          Similar requirement is discussed in Asktom. You can use below script.

          set feedback off
          set heading off
          set termout off
          set linesize 1000
          set trimspool on
          set verify off
          spool c:\temp\drop_objects.lst
          SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';' FROM USER_OBJECTS
          WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND OBJECT_TYPE<>'PACKAGE BODY' AND
          OBJECT_TYPE<>'TRIGGER' AND OBJECT_TYPE<>'LOB'
          UNION ALL
          SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;' FROM USER_OBJECTS
          WHERE OBJECT_TYPE = 'TABLE';
          spool off;
          @c:\temp\drop_objects.lst
          prompt 'Purging the recycle bin...'
          purge recyclebin;
          host del c:\temp\drop_objects.lst
          exit;

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:516246700346352069
          • 2. Re: dropping objects using a script.
            888206
            Thank you very much.

            will check ask Tom as well.