Database Profiles

Profile – is a database object – a named set of resource limits to:

Restrict database usage by a system user – profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:

• Disk storage space.
• I/O bandwidth to run queries.
• CPU power.
• Connect time.

Enforce password practices – how user passwords are created, reused, and validated
Profiles are assigned to users as part of the CREATE USER or ALTER USER commands

• User accounts can have only a single profile
• A default profile can be created – a default already exists within Oracle named
• DEFAULT – it is applied to any user not assigned another profile.
• Assigning a new profile to a user account supersedes any earlier profile.
• Profiles cannot be assigned to roles or other profiles.

Profiles only take effect when resource limits are "turned on" for the database as a whole. Specify the RESOURCE_LIMIT initialization parameter.

RESOURCE_LIMIT = TRUE

Use the ALTER SYSTEM statement to turn on resource limits.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;


Profile Specifications

Profile specifications include:

• Password aging and expiration
• Password history
• Password complexity verification
• Account locking
• CPU time
• Input/output (I/O) operations
• Idle time
• Connect time
• Memory space (private SQL area for Shared Server only)
• Concurrent sessions

If users not assigned a specific profile are automatically assigned the DEFAULT profile. The DEFAULT profile has only one significant restriction it doesn't specify a password verification function.

This query lists the resource limits for the DEFAULT profile


SQL> COLUMN profile FORMAT A10;
COLUMN resource_name FORMAT a30;
COLUMN resource FORMAT a8;
COLUMN limit FORMAT a15;
SELECT * FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT';

PROFILE    RESOURCE_NAME                  RESOURCE LIMIT
---------- ------------------------------ -------- ---------------
DEFAULT    COMPOSITE_LIMIT                KERNEL   UNLIMITED
DEFAULT    SESSIONS_PER_USER              KERNEL   UNLIMITED
DEFAULT    CPU_PER_SESSION                KERNEL   UNLIMITED
DEFAULT    CPU_PER_CALL                   KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
DEFAULT    IDLE_TIME                      KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME                   KERNEL   UNLIMITED
DEFAULT    PRIVATE_SGA                    KERNEL   UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT    PASSWORD_LIFE_TIME             PASSWORD 180
DEFAULT    PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX             PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
DEFAULT    PASSWORD_LOCK_TIME             PASSWORD 1
DEFAULT    PASSWORD_GRACE_TIME            PASSWORD 7



Creating a Profile
===================

DBA creates a profile with the CREATE PROFILE command.
This command has clauses that explicitly set resource limits. A DBA must have the CREATE PROFILE system privilege in order to use this command.
Example:

CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 4
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
LOGICAL_READS_PER_SESSION unlimited
LOGICAL_READS_PER_CALL 100
IDLE_TIME 1
CONNECT_TIME 480
PASSWORD_REUSE_TIME 1
PASSWORD_LOCK_TIME 2
PASSWORD_REUSE_MAX 3
FAILED_LOGIN_ATTEMPTS 2;

Resource limits that are not specified for a new profile inherit the limit set in the DEFAULT profile.


Assigning Profiles
===========================

Profiles can only be assigned to system users if the profile has first been created. Each system user is assigned only one profile at a time. When a profile is assigned to a system user who already has a profile, the new profile replaces the old one – the current session,if one is taking place, is not affected, but
subsequent sessions are affected. Also, you cannot assign a profile to a role or another profile

profiles are assigned with the CREATE USER and ALTER USER
command. An example CREATE USER command

CREATE USER userp IDENTIFIED BY userp
PROFILE developer;

SELECT username, profile FROM dba_users
WHERE username = 'USERP';


Altering Profiles
====================
Profiles can be altered with the ALTER PROFILE command.
A DBA must have the ALTER PROFILE system privilege to use this command.
When a profile limit is adjusted, the new setting overrides the previous setting for
the limit, but these changes do not affect current sessions in process.
Example:

ALTER PROFILE developer LIMIT
CPU_PER_CALL default
LOGICAL_READS_PER_SESSION 20000
SESSIONS_PER_USER 1;




Dropping a Profile
=========================

Profiles no longer required can be dropped with the DROP PROFILE command.
The DEFAULT profile cannot be dropped.
The CASCADE clause revokes the profile from any user account to which it was
assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.
When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile.
Examples



SQL> drop profile developer;
drop profile developer
*
ERROR at line 1:
ORA-02382: profile DEVELOPER has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE DEVELOPER CASCADE;

Profile dropped.

SQL> SELECT username, profile FROM dba_users
WHERE username = 'USERP';  

USERNAME                       PROFILE
------------------------------ ------------------------------
USERP                          DEFAULT

SQL>

Changes that result from dropping a profile only apply to sessions that are created after the change – current sessions are not modified.

Managing Tablespaces : #4

View information about Tablespace & Datafiles 


Oracle has provided many Data dictionaries to view information about tablespaces and datafiles. Some of them are:

To view information about Tablespaces in a database give the following query

SQL> select * from dba_tablespaces
SQL> select * from v$tablespace;

To view information about Datafiles
SQL> select * from dba_data_files;
SQL> select * from v$datafile;

To view information about Tempfiles
SQL> select * from dba_temp_files;
SQL> select * from v$tempfile;

To view information about free space in datafiles
SQL> select * from dba_free_space;

To view information about free space in tempfiles
SQL> select * from V$TEMP_SPACE_HEADER;

Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 500 lines 500
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;


Check the tablespace Utilization.

Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"

set pages 500 lines 3276
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 500 lines 3276
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 500 lines 327
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;

Managing Tablespaces : #3

Managing Tablespaces with Oracle Managed Files


Oracle-managed files eliminates the need for you to directly manage the operating system files in an Oracle database. You specify operations in terms of database objects rather than file names. The database internally uses standard file system interfaces to create and delete files as needed for the following database structures:

• Tablespaces
• Redo log files
• Control files
• Archived logs
• Block change tracking files
• Flashback logs
• RMAN backups

A database can have a mixture of Oracle-managed and unmanaged files. The file system directory specified by either of these parameters must already exist; the database does not create it. The
directory must also have permissions for the database to create the files in it.
The example shows that after DB_CREATE_FILE_DEST is set, the DATAFILE clause can be
omitted from a CREATE TABLESPACE statement. The data file is created in the location
specified by DB_CREATE_FILE_DEST. When you create a tablespace as shown, default values are assigned to all parameters.

when you use an OMF approach, the DB_CREATE_FILE_DEST parameter in the parameter file specifies that datafiles are to be created and defines their location.  The DATAFILE clause to name files is not used because filenames are automatically generated by the Oracle Server, for example, /u01/oracle/ducat/ducat_tbs01.dbf’.
Oracle-managed files have a specific naming format. For example, on Linux and Unix based systems the following format is used:
<destination_prefix>/o1_mf_%t_%u_.dbf
Do not rename an Oracle-managed file. The database identifies an Oracle-managed file based on its name. If you rename the file, the database is no longer able to recognize it as an Oraclemanaged file and will not manage the file accordingly.

Note: By default, ASM uses OMF files, but if you specify an alias name for an ASM data file at tablespace creation time or when adding an ASM data file to an existing tablespace, then that file will not be OMF.
You can also use the ALTER SYSTEM command to dynamically set this parameter in the SPFILE parameter file.

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata/ducat/';

Additional tablespaces are specified with the CREATE TABLESPACE command shown here that specifies not the datafile name, but the datafile size.  You can also add datafiles with the ALTER TABLESPACE command.

CREATE TABLESPACE ducat_data DATAFILE SIZE 100M;
ALTER TABLESPACE ducat_data ADD DATAFILE;

example:
SQL> show parameter DB_CREATE_FILE_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string
SQL>
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ducat/omf' scope=both;
System altered.
SQL> show parameter DB_CREATE_FILE_DEST
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/ducat/
                                                 omf
SQL> create tablespace testomf;
Tablespace created.
SQL>

[oracle@localhost omf]$ pwd

/u01/app/oracle/oradata/ducat/omf

[oracle@localhost omf]$ ls -lrt

total 4

drwxr-x--- 3 oracle oinstall 4096 Jan 11 05:00 DUCAT

[oracle@localhost omf]$

[oracle@localhost omf]$ ls -lrt

total 4

drwxr-x--- 3 oracle oinstall 4096 Jan 11 05:00 DUCAT

[oracle@localhost omf]$

[oracle@localhost omf]$

[oracle@localhost omf]$

[oracle@localhost omf]$ cd DUCAT/

[oracle@localhost DUCAT]$ ls -lrt

total 4

drwxr-x--- 2 oracle oinstall 4096 Jan 11 05:00 datafile

[oracle@localhost DUCAT]$ cd datafile/

[oracle@localhost datafile]$ ls -lrt

total 102408

-rw-r----- 1 oracle oinstall 104865792 Jan 11 05:00 o1_mf_testomf_f5f8jkxh_.dbf

[oracle@localhost datafile]$

[oracle@localhost datafile]$ pwd

/u01/app/oracle/oradata/ducat/omf/DUCAT/datafile

[oracle@localhost datafile]$

By default, Oracle-managed data files, including those for the SYSTEM and SYSAUX tablespaces, are 100MB and autoextensible.

Setting the DB_CREATE_ONLINE_LOG_DEST_n parameter prevents log files and control files from being located with datafiles – this will reduce I/O contention.
When OMF tablespaces are dropped, their associated datafiles are also deleted at the operating system level.

Managing Tablespaces : #2

Taking tablespaces Offline or Online

You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

ALTER TABLESPACE tablespace
{ONLINE |OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}

NORMAL:  All data blocks for all datafiles that form the tablespace are written from the SGA to the datafiles.  A tablespace that is offline NORMAL does not require any type of recovery when it is brought back online.

TEMPORARY:  A checkpoint is performed for all datafiles in the tablespace.  Any offline files may require media recovery.

IMMEDIATE:  A checkpoint is NOT performed.  Media recovery on the tablespace is required before it is brought back online to synchronize the database objects.

FOR RECOVER:  Used to place a tablespace in offline status to enable point-in-time recovery. The FOR RECOVER setting has been deprecated. The syntax is supported for backward compatibility
The SYSTEM tablespace cannot be taken offline.
We usually take tablespaces offline for maintenance purposes like below

• Move a datafile without closing the database.
• Recover an individual tablespace or datafile.
• Offline tablespace backup – a tablespace can be backed up while online, but offline backup is faster.

To alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
To Take a Tablespace Offline give the following command

SQL>alter tablespace ducat offline;

To again bring it back online give the following command.

SQL>alter tablespace ducat online;

To take individual datafile offline type the following command

SQL>alter database datafile ‘/u01/oracle/ducat/ducat_tbs01.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/oracle/ducat/ducat_tbs01.dbf’ online;

Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode. If the datafile has become corrupt or missing when the database is running in NOARCHIVELOG mode then you can only drop it by giving the following command

SQL>alter database datafile ‘/u01/oracle/ducat/ducat_tbs01.dbf’ offline for drop;

Making a Tablespace Read /Read Write 

Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historducatl data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.
To make a tablespace read only

SQL>alter tablespace ducat read only;

Again to make it read write

SQL>alter tablespace ducat read write;

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace:

SQL> ALTER TABLESPACE users RENAME TO usersts;

The following affect the operation of this statement:
The COMPATIBLE parameter must be set to 10.0 or higher.
If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.
Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely
To drop a tablespace give the following command.

SQL> drop tablespace ducat;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it anyhow then add the following keyword

SQL>drop tablespace ducat including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.But If you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace ducat including contents and datafiles;