Tablespace
Background Information
Schema is collection of schema objects(logical structure of data). The set of objects owned by user is its schema. or you can say A schema is owned by database user and have same name as that user.Basically Two type of user in oracle database - one who owned the object and other who used that object.
Schema owner create other users and grant permission to those users for performing database operation like SELECT, INSERT etc.
For Example consider HR schema from oracle Example, The user hr owned all the objects of HR Schema, hr user can create other user and grant privileges to them.
Schema Objects
| Object which are Part of Schema | Store in database but these Objects are not Part of Schema |
| Table, Index, View, cluster, Materialized views, triggers etc | Tablespace, users, role, profile etc |
[Question : Why Tablespace required ]
Answer:
Schema objects / database objects are not directly mapped to physical files, However database object are logically store in Tablespace. A Tablespace can hold one or more physical files called DATAFILE
There is no relationship between schema and tablespace, a tablespace can contain object from different schema, and a schema object can store in more than one tablespace.
Tablespace Type
Basically Two Category of Tablespace based on their creation
- By default for internal database structures i.e. system, sysaux, users, temp, undo
- By the DBA to store user data.
- Permanent - The Permanent keyword has been deprecated. This is for normal persistence object like table, indexes etc.
- Temporary - The Temporary keyword is used for Temporary Tablespace. Such type of tablespace is used for temporary storage of object during session of user. Data disappears from the object either after commit or logout of session.
- Note : Insted of Datafile use Temporary Tablespace use tempfile
- Only tablespace_group_clause and extent_management_clause are allowed
- Undo - This Tablespace is used oracle database undo management system.
- Note : Only extent_management_clause and tablespace_retention_clause are allowed
- separate user data from database owned schema for IO concern
- separate tablespace for different application
- separate tablespace for table and indexes
- SmallFile - datafile less than 32GB.
- BIGFILE - Only one datafile or tempfile can be used, maxsize of this file will be 128 TB. and can't be use Extent Management Dictionary
- You must specify full path along with filename for datafile or tempfile unless you don't set db_create_file_dest initializing parameter for example alter system set db_create_file_dest='$Oracle_data/"
- REUSE has no meaning when ASM is not used
- Logging Clause is default for all persistence object like table, index etc. Logging clause can't be used with temporary tablespace and undo tablespace.
PREREQUISITE
Must have create tablespace, Alter Tablespace or Alter database system privilege.
Naming Convention Best Practice
Use application name and schema name(User) and number.
Use application name and schema name(User) and number.
SQL QUERY - you can use create tablespace or create temporary tablespace or create undo tablespace or alter tablespace or alter database
