Solved

Oracle XE Tablespaces

Posted on 2006-06-19
13
2,367 Views
Last Modified: 2008-01-09
I mostly use SQL Server, but lately have been experimenting with Oracle XE.  There are some questions I have regarding how data is logically separated within Oracle.  Please excuse me if I need to refer to SQL Server to put these questions in context.
It seems Oracle XE only comes with one database installed.  You cannot create a new one.  After reading some of the documentation about how Oracle stores data, it seems that an Oracle server does not store information in the same way SQL Server does.  That is, there is no concept of separate databases on a server.  Im guessing that the Oracle equivalent is Tablespaces.  There is a oracle tablespace called USERS that it seems I should be putting all the data I need.  My question is, how do I logically distinguish what I used to consider separate databases in SQL Server.  In SQL Server, these databases had different owners, and were administered separate from one another.  As well, any application that connected to a specific database, could only see that database?
Could someone put this into context for me?
Thanks
0
Comment
Question by:BillPowell
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 16934657
>It seems Oracle XE only comes with one database installed.
in fact, you can define several INSTANCES, but each instance is working as 1 database.
to create additional instances, check out oradim (windows environment) and the init<SID>.ora files to control additional database (it's not as easy as in M$SQL

> Im guessing that the Oracle equivalent is Tablespaces.
the relationship i "Oracle Tablespace = SQL Server Filegroup"

>There is a oracle tablespace called USERS that it seems I should be putting all the data I need.
you can, but you can add/should additional tablespaces. in fact, each schema should have it's own tablespaces (and like in mssql, split table and index into dedicated tablespaces)

>My question is, how do I logically distinguish what I used to consider separate databases in SQL Server.
it depends on the needs. while most often you would simply create a dedicated schema (with it's own tablespaces), some cases will require a separate instance.

> As well, any application that connected to a specific database, could only see that database?
correct. but one can always create multiple connection object in the application code, and/or use the concept of database link (as the linked server concept in mssql)
0
 
LVL 11

Author Comment

by:BillPowell
ID: 16934770
<<>There is a oracle tablespace called USERS that it seems I should be putting all the data I need.
you can, but you can add/should additional tablespaces. in fact, each schema should have it's own tablespaces (and like in mssql, split table and index into dedicated tablespaces)>>

Ok, I was thrown off a bit because of what the documentation had to say about this:
"You can create additional permanent tablespaces in Oracle Database XE, although typically there is no need to do so."

Can you explain the concept of the schema in Oracle, and how it differs from tablespaces?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16934846
a schema, which is in oracle almost synonym with user, is a logical concept only. objects owned by 1 schema can be spread over multiple tablespaces.
this is what the referred doc extract tells you. you CAN have multiple tablespaces, but technically there is no need to do so. from performance point of view (and later, backup managability, better have more tablespaces/more datafiles)
0
 
LVL 11

Author Comment

by:BillPowell
ID: 16934870
So I guess to start out rather simply, for learning purposes, I can just create a new tablespace for each separate database that I plan to import from SQL Server??
0
 
LVL 8

Assisted Solution

by:gvsbnarayana
gvsbnarayana earned 50 total points
ID: 16934936
Hi BillPwell,
> Im guessing that the Oracle equivalent is Tablespaces.
Oracle's equvalant of SQL Server Database is User..
The user is the owner of the tables, sequences, procedure, functions that reside in his schema. Just like you can access tables in another database using dbtwo.dbo.table1 , you can access other user's tables using otherusername.tablename, provided that you have access privileges to the otheruser's tables.
>> There is a oracle tablespace called USERS that it seems I should be putting all the data I need
You really don't need to create a separate tablespace for each user. Tablespaces should be used to keep interrelated data together... incase of maintenance of that tablespace, you can tell that schema/application users that their data will not be available temporarily while others can use their schema/application.

HTH.
Regards,
Badri.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 16935847
"it seems that an Oracle server does not store information in the same way SQL Server does".  Yes!  That is correct.  There are vast differences between Oracle and SQL Server!  Sure, they both use SQL, and they store data, but most of the implementation, tuning and configuration details are quite different in these two systems.

Even the definition of the word "database" has different meanings for SQL Server, than for Oracle.  In Oracle, that is basically the entire system, but in a SQL Server system, there may be multiple databases.  The Oracle concept of a schema (user account) is similar to the SQL Server concept of a "database".

Oracle applications do not work directly with (or care about) tablespaces and they are usually invisible to the application.  Tablespaces are a logical unit of storage that an Oracle DBA uses to group tables and/or indexes together, but they are basically independent of schemas (users).  That is, it is possible to have tables and indexes from multiple schemas in the same tablespace(s) and it is possible to have multiple tablespaces for just one schema.

Here are a few general comments about Oracle that may help you.  In my opinion, Microsoft SQL Server is designed
for ease-of-use and easy integration with Microsoft tools.  Oracle is designed for stability, performance/scalability and platform independence.  This does have have practical implications:

1. SQL Server is quite easy to install, while Oracle is somewhat complex to install and configure.

2. Oracle is very tunable for different types of applications, different numbers of users, different amounts of RAM in the server, etc. so Oracle's performance can be optimized for many different combinations and configurations.  (In fact, Oracle's default initialization settings need to be changed for almost every installation if you want to maximize performance for your hardware and application.)  SQL Server is not so tunable, but may not need manual tuning efforts either.

3. Oracle's GUI management tool (OEM, Oracle Enterprise Manager) is not as easy to use as the corresponding SQL Server tool.

4. The record-locking mechanisms are very different. In Oracle, "writers do not block readers" and "readers do not block writers", so Oracle queries and/or stored procedures can select directly from the data tables without using "temporary tables" even though other users may be making changes to the data in the tables being selected from.  In SQL Server, "temporary tables" are often needed in stored procedures.

5. Oracle stored procedures use "early binding" (at compile time) of table and column names in stored procedures to their corresponding database objects.  This results in very fast performance at run time, but it means that Oracle stored procedures do not easily (or efficiently) support "dynamic SQL" where the table and/or columns can be determined or even changed at runtime.

6. The simplest Oracle stored procedures *DO NOT* return record sets.  It is possible to get Oracle procedures to return "ref cursors"  or arrays, but the coding for these is somewhat complex.  This is easier in SQL Server.

7. Oracle databases are optimized for applications that that use "bind variables" in their SQL statements.  SQL Server does not expect "bind variables" in SQL statements.

8. Oracle date columns can optionally include a time-of-day component.  Which parts of the date (month, day, week, quarter, year, etc.) and/or which parts of the time (hour, minute, second) are displayed, and which format of each of these date/time elements is used for displaying date/time information is highly customizable in Oracle.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:BillPowell
ID: 16936645
Ok, all good information.  Very informative.  I have created a new tablespace, created a new user whose default tablespace is the newly created tablespace and while logged in as this user, I have imported the contents of one of my SQL Server Databases.  Now, when I create new users and wish to give them access to all the data created by that Schema, how do I accomplish that.  When using the Oracle XE interface, I only see User Privileges related to creating objects as opposed to options like:
Grant Select
Grant Update
Grant Delete

or the abiltiy to create custom Roles for that matter
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16936685
Security in Oracle is very granular, at least at the object (table) level.  If you want to allow other users to use the tables in your schema, you have to do explicit grants for each table, with whatever privileges you want them to have.  This may be "select" only on some tables, "select, insert, update" on others, and "select, insert, update, delete" on still others, etc.  Usually it is best to create a custom role, do the table grants to that role, then grant that role to the user(s) who need it.  This gives you the ability to quickly set up a new user and give them whatever role(s) they need, without having to go back to individual table grants whenever a new user is added, or an employee's responsiblity changes.
0
 
LVL 11

Author Comment

by:BillPowell
ID: 16939484
I make extensive use of Roles already in SQL Server, I just didnt see a create role menu item in the Express Edition.  I guess Ill have to do everything in the command line.  The little web interface that comes with express 10g is pretty clever, but falls short of being a good administrator tool.  What do you guys use to administer Oracle, to perform routine day to day stuff.  Is there any free tool out there that is like SQL's Enterprise Manager?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16941529
Remember, Microsoft is basically a software tools company, and they also have a database (SQL Server).  Oracle is primarily a database company, and they also have some software tools, but the user-friendliness or number of features in Oracle tools may not match those in Microsoft tools.

Creating roles in Oracle is such a simple task that you don't need a GUI for it.  It is just a three-word command:
create role [role_name]

How do I administer Oracle for routine day-to-day stuff?  I just use Oracle's SQL*Plus utility plus a few dozen *.SQL scripts that I've accumulated over the years.  Yes, Oracle offers OEM (Oracle Enterprise Manager) as a GUI tool for managing Oracle databases, but I rarely use it.  TOAD (from Quest Software) is another GUI tool for Oracle primarily for developers, but it does support some DBA tasks as well.
0
 
LVL 11

Author Comment

by:BillPowell
ID: 16942968
-->create role [role_name]
Your right, that part is easy.  The time consuming part is granting specific permissions on each and every object using SQL.  In some of Oracles documentation, I see that they have a tool that is a snap in to an MMC console, I just dont know where to get it.  I think it was calleda:Oracle Administration Assistant for Windows

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16943035
I often use SQL to generate the SQL statements I need.  For example, if I need to grant select, insert and update on every table in a schema to a role named "ORDER_ENTRY", I would log in to SQL*Plus as the schema owner, then run this statement:
select 'grant select, insert, update on '||table_name||' to ORDER_ENTRY;' "Command"
from user_tables;

That will generate a list of SQL commands that I then copy and paste back into SQL*Plus to execute.  A slight variation of this is to use the "spool" command first with a file name, then "spool off" at the end.  That will put this list of SQL commands into the file name specified, then I can run that script file at any time.

You might want to do this command first in SQL*Plus to avoid (most) page breaks:
set pagesize 999;

If you need to eliminate all page breaks (and column headers) use this instead:
set pagesize 0;
0
 
LVL 11

Author Comment

by:BillPowell
ID: 16943365
Thanks guys, I think Ive beat this one long enough.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now