{"id":91,"date":"2009-06-09T17:02:57","date_gmt":"2009-06-09T15:02:57","guid":{"rendered":"http:\/\/demeringo.ovh.org\/blog\/?p=91"},"modified":"2009-06-09T17:02:57","modified_gmt":"2009-06-09T15:02:57","slug":"drop-all-user-objects-prior-to-an-oracle-import","status":"publish","type":"post","link":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/?p=91","title":{"rendered":"Drop all user objects (prior to an oracle import)"},"content":{"rendered":"<p># &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n<strong>Principle:<\/strong><\/p>\n<ul>\n<li>Step 1: the generate_drop_all.sql script is used to generate drop statements for all objects that belongs to the current user. Theses statements are generated into drop_all_objects.sql<\/li>\n<li>Step 2: the drop_all_objects.sql script is used to drop everything prior to import<\/li>\n<li>Step 3: import&#8230;<\/li>\n<\/ul>\n<p><strong>Details:<\/strong><\/p>\n<p># Step 1 :Move to the \/tmp directory<br \/>\n# Copy the generate_drop_all.sql file here<br \/>\n# Generate the drop script<br \/>\nexit | sqlplus target_user\/target_password @generate_drop_all.sql<\/p>\n<p># -&gt; a new file drop_all.sql is generated<\/p>\n<p># Step2 : Run the drop script<br \/>\nexit | sqlplus target_user\/target_password @drop_all_objects.sql<\/p>\n<p># Step3 : Import the dump<br \/>\nimp userid=target_user\/target_password file= the_dump_file.dump log=the_log_file.log fromuser=origin_user_name touser=target_user_name<\/p>\n<p><strong>Content of the generate_drop_all.sql file<\/strong><\/p>\n<p>set feedback off<br \/>\nset trimspool on<br \/>\nset echo off<br \/>\nset verify off<br \/>\nset linesize 200<br \/>\nset pagesize 0<br \/>\nspool drop_all_objects.sql<\/p>\n<p>select &#8216;drop &#8216; || object_type || &#8216; &#8216; || object_name || &#8216; cascade constraints;&#8217;<br \/>\nfrom user_objects<br \/>\nwhere object_type in (&#8216;TABLE&#8217;);<\/p>\n<p>select &#8216;drop &#8216; || object_type || &#8216; &#8216; || object_name || &#8216;;&#8217;<br \/>\nfrom user_objects<br \/>\nwhere object_type not in (&#8216;TABLE&#8217;, &#8216;INDEX&#8217;, &#8216;PACKAGE BODY&#8217;, &#8216;LOB&#8217;);<\/p>\n<p>select &#8216;commit;&#8217;<br \/>\nspool off<\/p>\n","protected":false},"excerpt":{"rendered":"<p># &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- Principle: Step 1: the generate_drop_all.sql script is used to generate drop statements for all objects that belongs to the current user. Theses statements are generated into drop_all_objects.sql Step 2: the drop_all_objects.sql script is used to drop everything prior to import Step 3: import&#8230; Details: # Step 1 :Move to the \/tmp directory # &hellip; <a href=\"https:\/\/le-moulin-de-verre.com\/fieldnotes\/?p=91\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Drop all user objects (prior to an oracle import)&#8221;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_newsletter_tier_id":0,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[1],"tags":[14],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5WcEf-1t","_links":{"self":[{"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=\/wp\/v2\/posts\/91"}],"collection":[{"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=91"}],"version-history":[{"count":0,"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=\/wp\/v2\/posts\/91\/revisions"}],"wp:attachment":[{"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=91"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=91"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/le-moulin-de-verre.com\/fieldnotes\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=91"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}