tablespaces

tablespaces

Dictionary Managed tablespace

Prior to oracle 8i, extent management was dictionary managed. In this case oracle uses data dictionary tables to keep track of the free and used blocks. Free space is maintained in sys.fet$ table and used space is maintained in sys.uet$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes against these tables. As only one process can acquire the ST enqueue at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Creating Dictionary Managed tablespace:

CREATE TABLESPACE <tablespace name>

  DATAFILE <location of datafile>

SIZE <size of data file>

EXTENT MANAGEMENT DICTIONARY

  DEFAULT STORAGE (

    INITIAL <size of initial extent>

    NEXT <size of next extent>

    MINEXTENTS <min no of extents>

    MAXEXTENTS <max no extents>

    PCTINCREASE 0);

Locally Managed tablespace

A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle simply changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information).

Creating Locally Managed tablespaces:

CREATE TABLESPACE <tablespace name>

DATAFILE <datafile location> SIZE <size of datafile>

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT <AUTO | UNIFORM>;

Note: We cannot specify storage parameters in Locally Managed tablespace.

In Locally Managed tablespace, Segment space management can be auto allocate or uniform

1. Auto Allocate: If the tablespace is expected to contain objects of varying sizes which requires different extent sizes and having many extents, then AUTOALLOCATE is the best choice. If it is not important to you to have a lot of control over space allocation and de-allocation, AUTOALLOCATE presents a simplified way to manage a tablespace. Some space may be wasted but the benefit of having Oracle manage your space most likely outweighs this.
2. Uniform: If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. It ensures that you will never have an unusable amount of space in your tablespace.

1mb is the default size.

Auto-allocate is the default parameter if nothing is specified.

Converting DMTS to LMTS:

Use the TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a dictionary-managed tablespace to a locally managed tablespace. This operation is done online, but space management operations are blocked until the migration has been completed. This means that you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional extents, then the operation may be blocked.

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('<tablespace name>');

Converting LMT to DMT:

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('<tablespace name>');

Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

Use the DBMS_SPACE_ADMIN package to migrate the SYSTEM tablespace from dictionary-managed to locally managed. The following statement performs the migration:

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

Before performing the migration the following conditions must be met:

• The database has a default temporary tablespace that is not SYSTEM.
• There are no rollback segments in the dictionary-managed tablespace.
• There is at least one online rollback segment in a locally managed tablespace, or if using automatic undo management, an undo tablespace is online.
• All tablespaces other than the tablespace containing the undo space (that is, the tablespace containing the rollback segment or the undo tablespace) are in read-only mode.
• The system is in restricted mode.
• There is a cold backup of the database.

All of these conditions, except for the cold backup, are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure.

Note:

After the SYSTEM tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made read/write. If you want to be able to use the dictionary-managed tablespaces in read/write mode, then Oracle recommends that you first migrate these tablespaces to locally managed before migrating the SYSTEM tablespace.

Temporary Tablespace as Locally Managed Tablespace :-

You can also create a temporary tablespace as Locally Managed tablespace as

  CREATE TEMPORARY TABLESPACE <temp tablespace name> TEMPFILE <location of temp datafile>      SIZE <size of tempfile> REUSE

     EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent size>;

Advantages of locally managed tablespace

1. Performance is enhanced since the storage values are placed in bit maps rather than in tables .Every time when oracle has to use an extent it has to check the sys.fet$ table and update sys.fet$ and sys.uet$ tables so this leads to performance degradation
2.  Oracle does not need to manage the tables
3.  Oracle manages the extent size based on transaction size in locally managed tablespace (auto allocate).
4.  Reduced contention on Data Dictionary tables: Because LMTS do not record free space in Data Dictionary.
5. Avoids recursive space management operations, which can occur in DMTS If space consuming or releasing in an extent results in another operation that consumes or releases space in another extents.
6. No rollback generated: Changes in the bitmap will not generate rollback because they do not update tables in Data dictionary.
7. Less Fragmentation due to uniform or oracle determined extent sizes.
8. Less Wasted free space due to uniform or oracle determined extent sizes.
9. Since tables are not managed(sys.fet$ and sys.uet$ as in dictionary managed tablespaces),no need for tables to be in database buffer cache
10. No need to acquire space transition latch

Disadvantage of Locally Managed Tablespace

If a tablespaces with a large number of mostly small segments is locally managed rather than dictionary managed, then access to these views can cause a lot more physical I/O and thus impact the cache retention of user data.

Differences between locally managed tablespaces and dictionary managed tablespaces

Dictionary Managed tablespace	Locally Managed tablespace
Maintains sys.fet$ and sys.uet$ tables for free and used blocks	Maintains bit maps
Sys.fet$ and sys.uet$ tables need to be cached in buffer cache	No need to cache the bit maps
For space requirements ,Space Transition latch must be obtained	No need to obtain any latch to use bit maps
Performance degrades	Performance enhances
Storage is specified using the parameters initial ,next ,min extents ,max extents and pct increase	Auto Allocate: Depending on size of transaction oracle allocates the extents Uniform: All the extents are of same size as specified
All the tablespaces can be dictionary managed	If system tablespace is locally managed, then all other tablespaces should be locally managed tablespace.

ORACLE USER v/s ORACLE SCHEMA

 

Database User

To access the database a user must specify a valid user account and successfully authenticate as required by that account.
The user accounts will have the following:

·         Unique Username : Usernames cannot

ü  exceed 30 characters

ü  contain special characters

ü  must start with a letter

·         Authentication mode: Common authentication mode is password, but oracle 10g supports several other authentication modes like certificate, and token authentication.

            Authentication techniques:

ü  Password: Create user with an associated password that must be supplied when the user attempts to establish a connection.

ü  External: also referred as authentication by operating system, users can connect to oracle more conveniently, without specifying a username or password.

ü  Global: Strong authentication through oracle advanced security option. This allows users to be identified through the use of certificates, token devices and the oracle internet directory.

·         Default Tablespace: Location where user will create objects if user doesn’t specify some other tablespace. (Note: User must be granted with privilege of creating objects and a quota of tablespace explicitly).

·         Temporary Tablespace: Place where user can create temporary objects such as sorts and temporary tables.

 i) Creating a User:

        CREATE USER user_dba 

        IDENTIFIED BY user_dba        -- Assign password

        DEFAULT TABLESACE  praveen     -- Assign space for table and 

        TEMPORARY TABLESPACE temp;    -- Assign sort space

ii) Assign the required privileges:

GRANT CONNECT, RESOURCE TO user_dba;

GRANT DBA TO user_dba; -- Make user a DB Administrator

 iii) Give the user some space quota on its tablespaces:

ALTER USER user_dba quota unlimited on praveen;

Locking and Unlocking User Accounts:
Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.
Look at these examples:

ALTER USER scott ACCOUNT LOCK    -- lock a user account

ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account

ALTER USER scott PASSWORD EXPIRE; -- Force user to choose new password

Changing user’s passwords:

ALTER USER <username> IDENTIFIED BY <new password>;

SQL> password

Changing password for SCOTT

Old password:

New password:

Retype new password:

SQL> passw scott

Changing password for scott

New password:

Retype new password:

Password changed

Note: Oracle usernames and passwords are not case sensitive in database versions below Oracle 11g.

Dropping a user:

DROP USER user_dba CASCADE;           -- Remove user

Profiles and Users :
Profiles impose a named set of  Resource limits on database usage and instance resources. Profiles also place limitations on user’s password (length, expiration time,..). Every User is assigned to a profile and may belong to only one profile at any time.

CREATE PROFILE my_profile LIMIT

       PASSWORD_LIFE_TIME 30;

ALTER USER user_dba PROFILE my_profile;

Note: Profiles cannot impose resource limitation on user unless RESOURCE_LIMIT initialization parameter is set to TRUE . With RESOURCE_LIMIT at its default value of FALSE, Profile limitations are ignored.
Administrative Privileges :

Administrative privileges that is required for an administrator to perform basic

database operations, such as creating the database and instance startup and shutdown,

are granted through two special system privileges, SYSDBA and SYSOPER. You must

have one of these privileges granted to you, depending upon the level of authorization

you require.

Note: The SYSDBA and SYSOPER system privileges allow access to a

database instance even when the database is not open. Control of

these privileges are totally outside of the database itself.

The SYSDBA and SYSOPER privileges can also be thought of as types of connections

that enables you to perform certain database operations for which privileges cannot be

granted in any other fashion. For example, you if you have the SYSDBA privilege, you

can connect to the database by specifying CONNECT AS SYSDBA.

Schemas and Common Schema Objects :

A schema is a collection of database objects owned by a particular user. A schema has the same name as the user that owns the schema. Schema objects are the logical structures that directly refer to the database’s data.

                 Schema objects include structures like:

ü  Tables

ü  Views

ü  Indexes

ü  Synonyms

Note : There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces and a tablespace can hold objects from different schemas.

Illustrates the relationship among schema objects, tablespaces, and datafiles.

When we create the database, a number of schemas are created. Two of particular importance is:

SYS Schema: All of the base tables and views that constitute the data dictionary are created in SYS Schema. Data dictionary is created in SYSTEM Tablespace when the database is created and is updated by oracle database server when a DDL statement is executed.

Note: Objects in SYS Schema should never be modified by any user or database administrator, and no one should create any tables in schema of user SYS.

SYSTEM Schema: Contains additional tables and views that sore administrative information and internal tables and views used by various oracle options and tools. Users shouldn’t create any additional objects in the SYSTEM schema.

            During a complete installation of an oracle database, the sample schemas are installed automatically with the database. The sample schema serves the purpose of providing a common platform. For example:  HR (Human Resource)

Difference between Schema and user:

* A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
* A user owns a schema.
* A user and a schema have the same name.
* The CREATE USER command creates a user. It also automatically creates a schema for that user.
* The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
* For all intents and purposes you can consider a user to be a schema and a schema to be a user.