Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

'Oracle 11g Express Edition' and creating a new database or schema  RSS feed

 
Rod Hittle
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought I might as well use Oracle to make my databases and tables to use in my Java classes rather than MySQL because the company I work for has been sold to another and they use Oracle.
So as long as I am learning I thought I should try it.
Well, I got the Oracle 11g Express Edition installed and the JAVA JDK and the Oracle SQL Developer.
But I can't figure out how to go about making a schema or database like I did in MySQL.

I seem to be led off to this webpage driven app called 'Application Express'.

Any clues would be greatly appreciated.
 
Tapas Chand
Ranch Hand
Posts: 614
9
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Go through this link.
It explains in detail how to get started with Oracle 11g express edition
 
Rod Hittle
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The info in that link leaves me lost at the very beginning.

I came across the idea of the DBCA utility but then found this statement.
BUT if you are using Oracle express edition then I am sorry guys you will not find this utility there as Oracle XE can only have maximum one database which comes preinstalled.

If so I don't see that database. From SQL developer I just see
Oracle-SQL-DEVELOPER-start.jpg
[Thumbnail for Oracle-SQL-DEVELOPER-start.jpg]
 
chris webster
Bartender
Posts: 2407
36
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You probably need to start with a more structured tutorial e.g. try the 2 Day Developer's guide provided by Oracle.

Oracle XE comes with a single pre-installed database, named "XE". Check the online docs for the connection details (system user, password, port etc), but you just need to create a new connection via SQL Developer to log into the XE database, where you can create your own application user schema etc, or use one of the pre-installed schemas for the tutorials.
 
Rod Hittle
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I will do that Chris.
so is XE database, then what is a schema? I thought they were the same thing.
I am thinking a database is made up of one or more tables. Not sure where the 'schema' plays into the picture.
 
Tapas Chand
Ranch Hand
Posts: 614
9
BSD Java
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In Oracle schema is a user.
A database in Oracle can have many schemas.
You can login into a schema by using username and password.
SYSTEM,SYS these are existing schemas in Oracle.
Schema has the database objects like tables, procedures, views etc.

When you execute the Oracle query
CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD>, you are actually creating a schema in that particular database.
 
Rod Hittle
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Chris and Tapas.

The info provided by Oracle leaves much to be desired. But between the clues there and other sites and the clues you all gave me here at the Big Moose Saloon I was to get Oracle all going. I can now do queries against a test table.

I will later post just what I had to do.
 
Tapas Chand
Ranch Hand
Posts: 614
9
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are welcome
 
sanjeev kumar narayanaswamy
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tapas Chand wrote:You are welcome
Hi Team,

Thanks for information on oracle 11g Express Edition.

I have installed oracle 11g Express Edition and need to create DB and i am not able to get any doc for the same as i need to create below DB.
Open an SQL *Plus window and log in as SYSTEM or SYSDBA.

To create a tablespace for Jazz Team Server, enter the following command. Replace JTS with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step a, and JTS.DBF with the datafile name that you want to create.

CREATE TABLESPACE JTS
DATAFILE 'ORACLE_BASE/oradata/CLMDB/JTS.DBF'
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

To create a temporary tablespace for Jazz Team Server, enter the following command. Replace JTS_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step a, and JTS_TEMP.DBF with the temporary file name that you want to create.

CREATE TEMPORARY TABLESPACE JTS_TEMP
TEMPFILE 'ORACLE_BASE/oradata/CLMDB/JTS_TEMP.DBF'
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

To create a tablespace for the Change and Configuration Management application, enter the following command. Replace CCM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step a, and CCM.DBF with the datafile name that you want to create.

CREATE TABLESPACE CCM
DATAFILE 'ORACLE_BASE/oradata/CLMDB/CCM.DBF'
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

To create a temporary tablespace for the Change and Configuration Management application, enter the following command. Replace CCM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step a, and CCM_TEMP.DBF with the temporary file name that you want to create.

CREATE TEMPORARY TABLESPACE CCM_TEMP
TEMPFILE 'ORACLE_BASE/oradata/CLMDB/CCM_TEMP.DBF'
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

To create a tablespace for the common data warehouse, enter the following command. Replace DW with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step a, and DW.DBF with the datafile name that you want to create.

CREATE TABLESPACE DW
DATAFILE 'ORACLE_BASE/oradata/CLMDB/DW.DBF'
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

To create a temporary tablespace for the common data warehouse, enter the following command. Replace DW_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step a, and DW_TEMP.DBF with the temporary file name that you want to create.

CREATE TEMPORARY TABLESPACE DW_TEMP
TEMPFILE 'ORACLE_BASE/oradata/CLMDB/DW_TEMP.DBF'
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

In the Oracle Enterprise Manager Database Control, click Server > Tablespaces and verify that the tablespaces you created are displayed and accessible. Also ensure that the default Oracle tablespace named USERS already exist. If this tablespace does not exist in your Oracle database, create it.

Can we do this.

Thanks
Sanjeev Kumar N
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!