Oracle – Difference Between Schema and Database

oracleoracle-10goracle-11g-r2

In Oracle, datafile are system files where actual data is stored. The collection of datafiles make tablespace and, at last, database is collection of tablespaces.

Correct me if I am wrong on the concepts of datafile, tablespace and database.

I would like to understand the difference between Schema and Database in details. Online resources are helpful but seemed confusing, regarding this difference.

Best Answer

If all else fails read the documentation. Try here for starters (v. good diagram). In Oracle, a schema is a database. Also see here - particularly this:

One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects [i.e. related tables]. A database schema is owned by a database user and has the same name as the user name.

A further distinction is between the physical database files "the database" and the "instance" - the running software that makes up the fully working system.

In one sense, an Oracle "database" is useless - it's just a bunch of bits on a disk - it's the software which turns it into a system (i.e. a database in the sense that information is retrievable/modifiable).

An Oracle database is a running instance plus data (+ control + redo &c.) files which itself may contain 1 or many schemas which are themselves "databases" - i.e. as the layman would understand them - i.e. a HR, CRM or ERP (&...) schemas/systems.

Related Solutions

ORA-01654: unable to extend index by 128 in tablespace

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a great post about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move;

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
You're not specifying a tablespace when creating the index and it's not using the one you think, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace:

select * from dba_users where username = 'MY_USER'

If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table 
    on my_table ( my_column ) 
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use DBMS_STATS.GATHER_TABLE_STATS() and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.

Oracle Tablespaces – Advantages and Use Cases

Few uses for the tablespaces:

When you no longer need the data you can drop the tablespace and delete datafiles. This is basically the only way to release space back from Oracle to the operating system.
When doing backups you can exclude tablespace from backup. E.g. if it contains non essential or easily recreatable data.
When doing restore you can skip tablespace. E.g. if you need only specific table or you need to start as soon as possible you can skip index tablespaces and rebuild indexes online.
Tablespace per user can be useful. Then when user is no longer needed you drop the user then drop tablespace and have disk space back. Also if user suddenly starts to generate lots of data only his tasks will break when tablespace becomes full. Other users will be unaffected.
Data blocks corruption usually is contained within tablespace. In such cases only one tablespace is affected.
You can store tablespaces (datafiles) on different types of disks. So you can manually manage which data goes on SSD and which on SATA.
In really rare cases you may need different block size for the tablespace.
You can choose to compress the whole tablespace content.
You can move selected data to a tablespace, unplug it from the database and attach to another database. This is transportable tablespace.

Best Answer

Related Solutions

ORA-01654: unable to extend index by 128 in tablespace

Oracle Tablespaces – Advantages and Use Cases

Related Question