Timeline for Global command to destroy all disconnected data files in Oracle 11g
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 19, 2016 at 0:57 | vote | accept | HereAndThere | ||
| Feb 29, 2016 at 22:30 | comment | added | EdStevens | Yes, those commands should work. Just be aware of what I said about tablespaces being able to contain segments from more than one user. Just because you drop a user, that does not mean that a given tablespace is now empty. It could very well still have segments belonging to other users. Query dba_segments to see. You misunderstand what @BalzasPapp said. If you drop a user, everything belonging to that user is gone, including objects that are in the SYSTEM ts. | |
| Feb 28, 2016 at 23:03 | comment | added | HereAndThere | I am trying to do a data pump import to test how effective this delete exercise was. But I am encountering an obstacle that I documented in a separate posting. Are you willing to take a look? Here is the link: dba.stackexchange.com/questions/130732/… | |
| Feb 28, 2016 at 21:57 | comment | added | HereAndThere |
@EdStevens Thank you. So then would I be completely cleaning out the schemas and tablespaces if I use the following three commands in the following sequence: .) drop user ... cascade;, 2.) alter tablespace ... offline;, and 3.) drop tablespace ... including contents and datafiles;`? Would these three commands also clean the dictionary in the SYSTEM tablespace as BalazsPapp suggested?
|
|
| Feb 27, 2016 at 20:03 | comment | added | EdStevens | As for your second question - 'how do I drop a schema' - exactly like you showed in your first msg. DROP USER .. CASCADE. Note that a user may have objects in more than one TS, and a given TS may have objects from more than one schema. There is no inherent one-to-one relationship between users (schemas) and tablespaces. | |
| Feb 27, 2016 at 20:01 | comment | added | EdStevens | Once you successfully issue a DROP TABLESPACE, any remaining file(s) from that tablespace has no more meaning to oracle than does a Word document. There are no leftover connections to the database. So nothing you do to it either directly or from the recycle bin has any meaning at all to oracle. | |
| Feb 27, 2016 at 19:47 | comment | added | HereAndThere | Thank you. How do I drop the schema? I imagine the steps are 1.) query all schemas, 2.) identify the ones to delete, then 3.) delete schemas by name. But how do I do that in Oracle 11g? What commands? If you think this is a separate question, I need help framing it so that I do not spam the ssite with poorly defined questions. | |
| Feb 27, 2016 at 19:40 | comment | added | Balazs Papp | @HereAndThere Yes, you can empty the recycle bin. I don't know what you plan to import, but some object types, like views, stored procedures, packages are not stored in user tablespaces, hence they are not dropped (they are stored in the dictionary, in the SYSTEM tablespace). If I were to re-import an existing schema, I would drop the schema first. | |
| Feb 27, 2016 at 19:13 | comment | added | HereAndThere | I do not have an oracle support account, so I cannot read your link. But thank you for what you posted in your answer as best practices for avoiding similar problems in the future. | |
| Feb 27, 2016 at 19:12 | comment | added | HereAndThere |
Thank you and +1 for taking the time to look into this. I have deleted the files as you suggested, given that the commands in the OP have already been run and failed to delete them. The .dbf files are thus now in the Windows recycle bin. Are there any other precautions that I should take to clean up Oracle 11g before trying to import more schema and data using other scripts? Also, is it safe to empty the windows recycle bin and thus permanently eliminate these .dbf files? I plan another data pump to replace all this from scratch, and want things clean first to avoid errors.
|
|
| Feb 27, 2016 at 10:40 | history | answered | Balazs Papp | CC BY-SA 3.0 |