Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
If you don't want to set a profile, just don't, that's optional. I wouldn't give a quota in system either, user objects don't belong there at all. –  Mat Aug 9 '13 at 9:43
    
This is not fair, instead of giving the solution , people vote against the doubts. This is really not fair. Somebody's minor problem could be a roadblock for other. Who says I didn't research, I read whole documentations, but couldn't understand it well, thats why I seek help from you guys. If you behave the opposite way, then this forum will mean nothing. –  Rubbal Bhusri Aug 9 '13 at 10:39
    
@Mat, I tried using : CREATE USER info_sys IDENTIFIED BY admin1 DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE temp QUOTA 5M ON system PROFILE app_user; And when I accessed through this user, It says: "info_sys lacks USER CREATE privilege- logon denied " What to do now ? –  Rubbal Bhusri Aug 9 '13 at 10:58

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
You mean to say that, whatever user we create by : CREATE USER <user_name> statement, Does that newly created user runs under the existing default user(i.e. SYS or SYSTEM) ? –  Rubbal Bhusri Aug 9 '13 at 18:50
    
The SQL statement (DROP TABLE <schema_owner>.<table_name> CASCADE CONSTRAINTS) given by you , worked fine. But if we want to delete foreign key constraints individually, then it doesn't allow that to happen. WHY is it so ? –  Rubbal Bhusri Aug 9 '13 at 18:53
    
I've added new information answering to your comments. –  vegatripy Aug 9 '13 at 19:34
    
I start sqlplus from the start menu and clicking sqlplus. When it starts, it asks for username and password and I've none other than sys/as sysdba as username and admin as password which I created at the installation time. Now when I execute "CREATE USER..." statement, Does that user is separate and has no relation with SYS or it has relation becoz, we created user by entering into sys/as sysdba username ? –  Rubbal Bhusri Aug 10 '13 at 6:06
    
It's an independent user. It's like a Windows user account, or Linux user; You create them from the Administrator/root user account, but then you can log in later into that user account and has his own home directory, environment variables, permissions, groups, etc.. Oracle acts in the same way. Check the chapter 7 in the 2 Day DBA document. –  vegatripy Aug 10 '13 at 10:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.