How to Drop tables being a SYS user

installationoracle-11g-r2permissions

I am not being able to drop my tables using :

drop table <table_name>;

command. It says "the table is referencing primary keys of some other tables as foreign keys."
, and when I tried to drop foreign key constraints using :

alter table <table_name> drop constraint <constraint_name>;

statement. It says: "cannot drop constraint", and when I tried to drop column itself on which foreign key constraint was applied, using:

alter table <table_name> drop <column_name>;

statement. It says: "cannot drop column owned by SYS table.". Somebody told me, SYS doesn't has the privileges to drop columns especially when foreign keys constraint are present, and he suggested me to make new User account apart from default SYS and customise all the privileges. Making new user is very lengthy process and I read many articles of making a new user already, but it doesn't help , coz, I've done the installation by choosing the option of "install database software only" and then later created and configured Database using DBCA(Database configuration Assistant ). I might have skipped certain steps or didn't look it properly while using DBCA and just kept the default settings.

Creating a new user requires you to use the CREATE USER privilege and the syntax given in the oracle documentation to create user is :

CREATE USER sidney 
IDENTIFIED BY out_standing1 
DEFAULT TABLESPACE example 
QUOTA 10M ON example 
TEMPORARY TABLESPACE temp
QUOTA 5M ON system 
PROFILE app_user 
PASSWORD EXPIRE;

This is the example I copied from the oracle documentation. Now the problem I am facing is with the PROFILE keyword in the above statement. I don't know what value to fill for PROFILE keyword and moreover the documentation suggests :

"Oracle recommends that you use the Database Resource Manager rather SQL profiles to establish database resource limits. The Database Resource Manager offers a more flexible means of managing and tracking resource use."

Now, when I started looking for Database Resource Manager, I came accross various steps such as:

To administer the Resource Manager in Enterprise Manager:

1) Access the Database Home page.

and it says: The Database Home page is the main database management page in Oracle Enterprise Manager Database Control (Database Control).

It asks to confirm certain steps to access the "DataBase Home Page" such as :

1.) Ensure that the dbconsole process is running on the database host computer.

I checked the dbconsole process by running the following statement on the command prompt :

C:\oracle_base\product\11.2.0\dbhome_1\BIN>./emctl status dbconsole

It showed a warning on the command prompt saying :

"OC4J configuration issue . C:\oracle_base\product\11.2.0\dbhome_1\oc4j\j2ee\OC4J_DBConsole_rubbalbhusri-PC_orcl not found."

Note : rubbalbhusri-PC is my system-name(the name I defined for my Operating System)

Now, my question is this :

Is "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory created during installation itself ?

Why, I don't have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

What should I do to have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

Please don't tell me to install the whole "oracle database 11g" again.

Tell me some repair, which solves this problem, if any ?

Best Answer

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.

Related Question