adding tables in to the oracle 11g database

[lalit khera]

hi friends i need your help its urgent.

I have downloaded oracle 11G R2 .
I have also created a database named "sivu" succesfully using database configuration assistant.
But now i want to know how to add tables in that database.
I am new to oracle. i tried googling but did not find something relevant.

i am using sqlplus.

thanks

[Martin Vashko]

This looks like an excellent introductory documentation for Oracle database: 2 Day Developer's Guide. It employs SQL Developer instead of sqlplus, but you can download that for free. (I haven't read this one myself.)

If you're planning some serious affair with Oracle, you certainly should not miss the Database Concepts guide. I'd even suggest to read it first, but if you can't wait trying out the new shiny database, you can leave this one for a little bit later. (I have read this one, albeit for an older version of Oracle, and that is why I recommend it. The 11g version has been reworked to bring the volume down, they say.)

[lalit khera]

Martin Vajsar wrote:This looks like an excellent introductory documentation for Oracle database: 2 Day Developer's Guide. It employs SQL Developer instead of sqlplus, but you can download that for free. (I haven't read this one myself.)

If you're planning some serious affair with Oracle, you certainly should not miss the Database Concepts guide. I'd even suggest to read it first, but if you can't wait trying out the new shiny database, you can leave this one for a little bit later. (I have read this one, albeit for an older version of Oracle, and that is why I recommend it. The 11g version has been reworked to bring the volume down, they say.)

thanks martin.

For the books that you reffered. Sorry to say i am hurry and rush to work on projects in java.
My target is to get familiarity about oracle environment and feel difference among other Databases.
A sort of comparison from practical point of view.
Yes, i tried the stuff over sql developer, it seems it will work but now i am facing other problem. the sql developer is able to create tables for Sys user of oracle.
For rest users , it says error, not enough privileges.

Could you please tell me how can i create a database for one specific users.
Whatever database i am creating it is dumping in to the sys account of oracle.

Please reply if you know

thanks

[Martin Vashko]

Oracle doesn't support multiple logically separated databases on one database server (roughly speaking). Our FAQ actually describes Oracle's way of achieving the same.

Never ever use the SYS account for anything. When you need admin privileges, use the SYSTEM account (you've provided passwords for these accounts when creating the database). You'll need it at least to create a new user in Oracle and grant him permissions to connect to the DB and to create tables, views etc. From that point on, connect as that user and do all your experiments there. Do not create database objects in the SYSTEM schema.

The 2 Day Developer's Guide I've linked before contains instructions on how to unlock and use the HR user. I'd say it would be easier to get you started, since creating a user has some caveats on its own. If you do need to create your own user promptly, you might start with this script and modify it until it fits your needs. As mentioned before, it has to be run under the SYSTEM account:

CREATE TABLESPACE MYAPP DATAFILE 'MYAPP.ORA' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M; CREATE USER MYAPP IDENTIFIED BY "I" DEFAULT TABLESPACE "MYAPP" PROFILE DEFAULT ACCOUNT UNLOCK; GRANT UNLIMITED TABLESPACE TO MYAPP; GRANT CONNECT TO MYAPP; GRANT CREATE PROCEDURE TO MYAPP; GRANT CREATE SEQUENCE TO MYAPP; GRANT CREATE SESSION TO MYAPP; GRANT CREATE TABLE TO MYAPP; GRANT CREATE TRIGGER TO MYAPP; GRANT CREATE TYPE TO MYAPP; GRANT CREATE VIEW TO MYAPP;
I use this in dev environment only, as some of these grants (such as UNLIMITED TABLESPACE) are quite powerful.

Look up the syntax and exact meanings of these commands in the documentation.

[lalit khera]

Martin Vajsar wrote:Oracle doesn't support multiple logically separated databases on one database server (roughly speaking). Our FAQ actually describes Oracle's way of achieving the same.

Never ever use the SYS account for anything. When you need admin privileges, use the SYSTEM account (you've provided passwords for these accounts when creating the database). You'll need it at least to create a new user in Oracle and grant him permissions to connect to the DB and to create tables, views etc. From that point on, connect as that user and do all your experiments there. Do not create database objects in the SYSTEM schema.

The 2 Day Developer's Guide I've linked before contains instructions on how to unlock and use the HR user. I'd say it would be easier to get you started, since creating a user has some caveats on its own. If you do need to create your own user promptly, you might start with this script and modify it until it fits your needs. As mentioned before, it has to be run under the SYSTEM account:

CREATE TABLESPACE MYAPP DATAFILE 'MYAPP.ORA' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M; CREATE USER MYAPP IDENTIFIED BY "I" DEFAULT TABLESPACE "MYAPP" PROFILE DEFAULT ACCOUNT UNLOCK; GRANT UNLIMITED TABLESPACE TO MYAPP; GRANT CONNECT TO MYAPP; GRANT CREATE PROCEDURE TO MYAPP; GRANT CREATE SEQUENCE TO MYAPP; GRANT CREATE SESSION TO MYAPP; GRANT CREATE TABLE TO MYAPP; GRANT CREATE TRIGGER TO MYAPP; GRANT CREATE TYPE TO MYAPP; GRANT CREATE VIEW TO MYAPP;
I use this in dev environment only, as some of these grants (such as UNLIMITED TABLESPACE) are quite powerful.

Look up the syntax and exact meanings of these commands in the documentation.

hey martin, from all the stuff that you said, i learnt these things:
1. do not create tables in sys account, we need to create users and assign priveliges to them.
2. never use sys/system acount.
3. how to lock/unlock the users

But now i have few more doubts:
1. whats the difference between sys/system account, both are admin accounts, then why its prefferable to use system account not sys??
2. can i create a new account which has same privileges as sys/system account and that admin stuff will be then done through this newly created user account.

May be i will few more questions as i am progressing towards oracle, i believe you never minds.

thanks

[lalit khera]

lalit khera wrote:

Martin Vajsar wrote:Oracle doesn't support multiple logically separated databases on one database server (roughly speaking). Our FAQ actually describes Oracle's way of achieving the same.

Never ever use the SYS account for anything. When you need admin privileges, use the SYSTEM account (you've provided passwords for these accounts when creating the database). You'll need it at least to create a new user in Oracle and grant him permissions to connect to the DB and to create tables, views etc. From that point on, connect as that user and do all your experiments there. Do not create database objects in the SYSTEM schema.

The 2 Day Developer's Guide I've linked before contains instructions on how to unlock and use the HR user. I'd say it would be easier to get you started, since creating a user has some caveats on its own. If you do need to create your own user promptly, you might start with this script and modify it until it fits your needs. As mentioned before, it has to be run under the SYSTEM account:

CREATE TABLESPACE MYAPP DATAFILE 'MYAPP.ORA' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M; CREATE USER MYAPP IDENTIFIED BY "I" DEFAULT TABLESPACE "MYAPP" PROFILE DEFAULT ACCOUNT UNLOCK; GRANT UNLIMITED TABLESPACE TO MYAPP; GRANT CONNECT TO MYAPP; GRANT CREATE PROCEDURE TO MYAPP; GRANT CREATE SEQUENCE TO MYAPP; GRANT CREATE SESSION TO MYAPP; GRANT CREATE TABLE TO MYAPP; GRANT CREATE TRIGGER TO MYAPP; GRANT CREATE TYPE TO MYAPP; GRANT CREATE VIEW TO MYAPP;
I use this in dev environment only, as some of these grants (such as UNLIMITED TABLESPACE) are quite powerful.

Look up the syntax and exact meanings of these commands in the documentation.

hey martin, from all the stuff that you said, i learnt these things:
1. do not create tables in sys account, we need to create users and assign priveliges to them.
2. never use sys/system acount.
3. how to lock/unlock the users

But now i have few more doubts:
1. whats the difference between sys/system account, both are admin accounts, then why its prefferable to use system account not sys??
2. can i create a new account which has same privileges as sys/system account and that admin stuff will be then done through this newly created user account.

May be i will few more questions as i am progressing towards oracle, i believe you never minds.

thanks

Another doubt:
So oracle will be used for creating the database and i can use sql developer to add tables in that database.
My database creation with oracle 11g takes around 2 hrs (using db configuration assistant), i do not know why. It also impacts my system performance for around 15 min. after the boot process, later its fine.

[Martin Vashko]

First of all, please do not use Quote button to quote full contents of the posts. The Quote button is useful only if the post you're quoting is further back in the conversation and it wouldn't otherwise be clear what are you replying to, or when you want to answer one particular portion of the posts. Quoting full posts that are actually present right above only adds unnecessary clutter. If you can, please edit your last two posts and remove the quoted texts. Thanks.

Now to your questions:

1) The SYS account is internal Oracle's account and should not be used. Some things even work differently for SYS and you might get different (incorrect!) results when running queries and statements. Only use it when specifically instructed to do so by Oracle documentation or support.

2) Yes, it is possible to create an account and grant it DBA privileges. I don't know the details, though, I only manage development environments and always use SYSTEM for administration. If you need to manage this kind of thing, there is an Oracle DBA guide too. (If you want to manage production systems, you should really learn a lot first, otherwise you risk losing your data.)

lalit khera wrote:Another doubt:
So oracle will be used for creating the database and i can use sql developer to add tables in that database.

More or less, yes. There is the Database Configuration Assistant you already know, which is a tool to create a database (while a database can be created using Sql*Plus and an SQL script too, the assistant certainly makes it easier). Tables can be created using proper DDL commands, SQL Developer makes that again a bit easier (this is subjective, I actually always create tables by writing scripts "by hand").

2 hours to create a database is quite a lot, but you should do that very rarely. Maybe you have given too small an amount of memory to Oracle, or you have some IO bottleneck in your system. I don't know how much time it took on my computer last time (as I said, I don't do that very often), but it was probably somewhere around 15 minutes. I have an SSD disc for the database and give Oracle quite a lot of memory, though.

[chris webster]

It sounds like you're getting a bit bogged down here. As you are obviously completely new to Oracle, could I suggest you uninstall Oracle 11g and then download and install the much simpler Oracle Express edition (XE) 11g instead?

Oracle XE is really easy to install and use, and it requires far fewer resources than the standard Oracle RDBMS, but it contains most of the core functionality of any Oracle database, so anything you do here will transfer easily to the enterprise RDBMS in future. Oracle XE gives you just one database, which is called "XE", and you cannot create new databases, but you only need one database for learning the basics of Oracle anyway. I often use Oracle XE for prototyping and trying out different techniques etc, before applying these things on an enterprise database.

Oracle XE should be all you need to work through the 2 Day Developer Guide. There is also a quick intro to SQL Developer and Oracle XE. It's slightly out of date e.g.they tell you to use Oracle XE 10g (you should use XE 11g), but it will give you a starting point. Lots of Oracle's other tutorials assume you are using XE as well. You can connect to Oracle XE via JDBC in the same way as any other Oracle database, so it's useful for trying out your Java/database development as well.

When you start building your tables, you need to create a user schema in your XE database via the SYSTEM user (e.g. in SQLDeveloper) with the right permissions to create/modify tables etc. Then log in as your new user to create the tables, which will be created in your user's schema (not SYS or SYSTEM). In general, you should only log in as SYS or SYSTEM if you are performing database system admin tasks, not for developing your own database objects and applications etc.

So I recommend you switch to Oracle XE 11g, then work through some tutorials etc to get an understanding of how Oracle works. Then switch back to the standard/enterprise RDBMS when you're ready.

[Martin Vashko]

That sounds very good, Chris.

I didn't know the XE is so much easier to start with.

[chris webster]

Martin Vajsar wrote:I didn't know the XE is so much easier to start with.

Yes, it's easier, smaller and completely free to use and it's great for newbies, as they don't have to worry about all that DBA stuff etc. Admittedly, it doesn't include all the "enterprise" bells and whistles or DBA tools, but then you don't need that stuff for simple development work, and most of the core packages etc are still available so you can do a heck of a lot of coding on XE before you start hitting the boundaries. I used XE at my last job as a prototyping platform on my personal netbook (2GB RAM, tiny Atom processor) because it was quicker/easier for me to design and build my prototype schema and code in XE using SQL Developer, than to try to persuade the DBAs to let me have access to a "scratch" development schema or an Oracle directory for use with external tables, which we needed for our application.

EDIT:
Unfortunately, as Lalit has just pointed out, XE is not available for Windows 7 64-bit, which is a bit of a problem now we're all on 64-bit machines, and the 32-bit installer doesn't work properly on W7 64-bit. Doh!

[lalit khera]

chris webster wrote:It sounds like you're getting a bit bogged down here. As you are obviously completely new to Oracle, could I suggest you uninstall Oracle 11g and then download and install the much simpler Oracle Express edition (XE) 11g instead?

Oracle XE is really easy to install and use, and it requires far fewer resources than the standard Oracle RDBMS, but it contains most of the core functionality of any Oracle database, so anything you do here will transfer easily to the enterprise RDBMS in future. Oracle XE gives you just one database, which is called "XE", and you cannot create new databases, but you only need one database for learning the basics of Oracle anyway. I often use Oracle XE for prototyping and trying out different techniques etc, before applying these things on an enterprise database.

Oracle XE should be all you need to work through the 2 Day Developer Guide. There is also a quick intro to SQL Developer and Oracle XE. It's slightly out of date e.g.they tell you to use Oracle XE 10g (you should use XE 11g), but it will give you a starting point. Lots of Oracle's other tutorials assume you are using XE as well. You can connect to Oracle XE via JDBC in the same way as any other Oracle database, so it's useful for trying out your Java/database development as well.

When you start building your tables, you need to create a user schema in your XE database via the SYSTEM user (e.g. in SQLDeveloper) with the right permissions to create/modify tables etc. Then log in as your new user to create the tables, which will be created in your user's schema (not SYS or SYSTEM). In general, you should only log in as SYS or SYSTEM if you are performing database system admin tasks, not for developing your own database objects and applications etc.

So I recommend you switch to Oracle XE 11g, then work through some tutorials etc to get an understanding of how Oracle works. Then switch back to the standard/enterprise RDBMS when you're ready.

thanks for your reply.

My dear friend, i want to download the XE edition but the problem is that it is not available for 64 bit os.
I have windows 7 64 bit os, home premium.
XE version is present for win32m should i download it. will it work on my system??

thanks