It sounds like Oracle Total Recall may fit your needs:
Regulatory oversight such as Sarbanes-Oxley, HIPAA, and Basel-II, as
well as internal audits, require companies to keep historical data
available for long periods of time. Oracle Total Recall, part of
Oracle's comprehensive portfolio of database security solutions, works
with Oracle Database 11g, Enterprise Edition to help companies store
their data in secure, tamper-proof databases while keeping it
accessible to existing applications
The documentation states that it can support refactoring:
In Oracle Database 11g Release 1, the Add Column DDL operation is
supported with Flashback Data Archive. With Oracle Database 11g
Release 2, the following DDL operations are supported, with full
support for Flashback queries across all associated changes:
• Add, Drop, Rename, Modify Column
• Drop, Truncate Partition
• Rename, Truncate, Drop Table
For more complex DDL –
upgrades, split table, etc. – the Disassociate and Associate PL/SQL
procedures can be used to temporarily disable Flashback Data Archive
on specified tables.
You can use DROP TABLE <schema_owner>.<table_name> CASCADE CONSTRAINTS to drop the referential integrity too. Notice that there must be a reason to have a referential constraint in this table. Check it twice before drop. (I recommend to read documentation first http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm )
SYS is a special user, has some restrictions (like objects owned by SYS cannot be exported), but can alter any object in the database, including droping or alter some DB engine objects. That's why (and many other reasons) you should NEVER create user objects in SYS schema.
Objects created in SYS schema has a different treatment, and that's why you couldn't drop the column (or constraint, but I've never tried this).
I can say that the source of the problem was not the DROP TABLE syntax. The problem is that your objects where created in SYS schema ;). Oracle was designed to create and manipulate user objects within user's schemas.
It's like if you have a hammer and try to use it grabbing it by the head. It's not effective , It wont work as expected and you could brake it.
The good practice is to create USERS, and then create objects within user's schema.
You can create a new user just with:
CREATE USER <username> IDENTIFIED BY <password> ;
This will create a User (and a Schema), but it wont have any privileges. Logged in as SYS you can create and manipulate objects in this (and any) user schema, but this user will never be able to connect nor create objects. This are system privileges that can be easily granted with the roles CONNECT (just grant the CREATE SESSION privilege) and RESOURCE (grant a bunch of privilege to create several DB objects in his own schema):
GRANT CONNECT TO <username>;
GRANT RESOURCE TO <username>;
With this, you'll have a user with the DEFAULT profile (search in documentation for User Profiles) which can log in and can create user objects in his schema. A typical oracle db user.
These are all Oracle Administration basics. I've recommend to read the 2 Day DBA, DB Concepts, and DB Administrator's Guide documentation. All available at http://www.oracle.com/pls/db112/homepage (or, easy to remember, http://tahiti.oracle.com )
Regards.
-- edit- The OC4J_DBConsole_SID is related with EM configuration. I wouldn't mess around with this until you dominate Oracle's concepts. A GUI won't teach you the basis and it will progressively make you dependent of it.
Best Answer
Please try it:
And for restore dropped table , you should know , that drop did without purge command. If he/she used purge you can just restore your object just with dump or flashback and if he/she didn't use purge you can use this
select * from dba_recyclebin where orginal_name=<object_name>, if the select has result you can restore that and otherwise just falshback or restore backup