User And Their Privilege

  How to Create a User and Grant Permissions in Oracle

       Description

       The CREATE USER statement creates a database account that allows you to log into the                    Oracle database.

        Creating a User

       CREATE USER CAG

        IDENTIFIED BY xyz

         DEFAULT TABLESPACE USERS

         PROFILE DEFAULT

        ACCOUNT UNLOCK;

            NOTE --Parameters or Arguments

1             1)    user_name

The name of the database account that you wish to create

.

2               2)      TABLESPACE

Optional , It is the name of the tablespace that you wish to assign to the users.

3               3)      PROFILE profile_name

Optional. It is the name of the profile that you wish to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the DEFAULT profile is assigned to the user.

4               4)      PASSWORD EXPIRE

Optional. If this option is set, then the password must be reset before the user can log into the Oracle database.

           5) ACCOUNT LOCK

Optional. It disables access to the user account.

            6) ACCOUNT UNLOCK

  Optional. It enables access to the user account

    The Grant Statement

With our new CAG account created, we can now begin adding privileges to the account using the GRANT statement. GRANT is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both users and roles throughout the database.

 Providing Roles

Typically, you’ll first want to assign privileges to the user through attaching the                   account to  various roles, starting with the CONNECT role:       

GRANT CONNECT TO CAG;

In some cases to create a more powerful user, you may also consider adding the RESOURCE role (allowing the user to create named types for custom schemas) or even the DBA role, which allows the user to not only create custom named types but alter and destroy them as well.

GRANT CONNECT, RESOURCE, DBA TO CAG;

Assigning Privileges

Next you’ll want to ensure the user has privileges to actually connect to the database and create a session using GRANT CREATE SESSION. We’ll also combine that with all privileges using GRANT ANY PRIVILEGES.

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO CAG;

We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll GRANT TABLESPACE like so:

GRANT UNLIMITED TABLESPACE TO CAG;

Table Privileges

While not typically necessary in newer versions of Oracle, some older installations may require that you manually specify the access rights the new user has to a specific schema and database tables.

For example, if we want our CAG user to have the ability to perform SELECT, UPDATE, INSERT, and DELETE capabilities on the books table, we might execute the following GRANT statement:

GRANT

  SELECT,  INSERT,  UPDATE,  DELETE ON   schema.books

TO

  CAG;

This ensures that CAG can perform the four basic statements for the books table that is part of the schema schema.

Oracle: Execute a SQL script file in SQLPlus (Step By Step)

We can execute a SQL script file in SQLPlus. For consistency, use the .sql extension for the script file name.

A SQL script file is executed with a START or @ command.

Below are very easy step to execute a script.

1)      Step --Firstly we create file with .sql extension.

              For example

              vi  my_scripts\my_sql_script.sql

       2)  Step – Now logging in oracle

                  Sqlplus “/as sysdba”

             3) Step –Now executed scripts

            SQL> @ my_scripts\my_sql_script.sql

                      Or

          SQL> START   my_scripts\my_sql_script.sql

How To Extend/Decrease the Size of a tablespace

How To Extend/Decrease  the Size of a tablespace

Option 1

 You can extend the size of a tablespace by increasing the size of an existing datafile by typing the following command.

SQL> alter  database jsm datafile ‘/u01/oracle/data/jsmtbs01.dbf’ resize 100M;

This will increase the size from 50M to 100M

Option 2

You can also extend the size of a tablespace by adding a new datafile to a tablespace. This is useful if the size of existing datafile is reached o/s file size limit or the drive where the file is existing does not have free space. To add a new datafile to an existing tablespace give the following command.

SQL> alter tablespace add datafile‘/u02/oracle/jsm/jsmtbs02.dbf’size 50M;

Option 3

You can also use auto extend feature of datafile. In this, Oracle will automatically increase the size of a datafile whenever space is required. You can specify by how much size the file should increase and Maximum size to which it should extend.

To make a existing datafile auto extendable give the following command.

SQL> alter database datafile ‘/u01/oracle/jsm/jsmtbs01.dbf’ auto extend ON next 5M maxsize 500M;

You can also make a datafile auto extendable while creating a new tablespace itself by giving the following command.

SQL> create tablespace jsm datafile ‘/u01/oracle/jsm/jsmtbs01.dbf’ size 50M auto extend ON next 5M maxsize 500M;

 How To decrease the size of a tablespace

You can decrease the size of tablespace by decreasing the datafile associated with it. You decrease a datafile only up to size of empty space in it. To decrease the size of a datafile give the following command

SQL> alter database datafile ‘/u01/oracle/jsm/jsmtbs01.dbf’      resize 30M;

Coalescing Tablespaces

A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the database uses the free extent closest in size to the required extent. In some cases, when segments are dropped, their extents are deallocated and marked as free, but adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.

You should often use the ALTER TABLESPACE ... COALESCE statement to manually coalesce any adjacent free extents. To Coalesce a tablespace give the following command.

SQL> alter tablespace jsm coalesce;

Rman List Failure, Advise Failure and Repair Failure

List Failure, Advise Failure and Repair Failure with Oracle RMAN

 Rman is very easy tool for recovery. Suppose you lost your datafile or a block is corrupted or you lost a tablespace, Dont worry about it . Talk to RMAN that takes care of the rest.  

The Data Recovery Advisor,one of the RMAN beauties come with 11g (DRA).

Below is the easiest step to recover it.

Firstly I removed one of my datafile.

rm –r  /u01/app/oracle/oradata/TEST/datafile/myts01.dbf 

Now I  try to open the database . I will get an error.

SQL> startup;

ORACLE instance started.

Total System Global Area 2042241024 bytes

Fixed Size 1337548 bytes

Variable Size 1224738612 bytes

Database Buffers 805306368 bytes

Redo Buffers 10858496 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 – see DBWR trace file

ORA-01110: data file 7: ‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’

Now I open the database in mount stage;

SQL> select status from v$instance;

STATUS

————

MOUNTED

Rman target/

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

282        CRITICAL OPEN      29-JAN-18     myts01.dbf’ datafile 7:

‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

78802 HIGH OPEN 29-JAN-18   One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file ‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’ was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

—— ——————

1 Restore and recover datafile 7

Strategy: The repair includes complete media recovery with no data loss

Repair script: /oracle/diag/rdbms/talip/TALIP/hm/reco_3655040472.hm

RMAN suggested to us a script to solve the problem. Let’s see what the contents of this script;

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /oracle/diag/rdbms/talip/TALIP/hm/reco_3655040472.hm

contents of repair script:

# restore and recover datafile

restore datafile 7;

recover datafile 7;

Yes, we are looking for exactly that suggested the recovery script. I agree with RMAN and let’s recover our datafile.

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /oracle/diag/rdbms/talip/TALIP/hm/reco_3655040472.hm

contents of repair script:

# restore and recover datafile

restore datafile 7;

recover datafile 7;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 29-JAN-18

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00007

input datafile copy RECID=7 STAMP=756487675 file name=/oracle/yedek/TALIP/data_D-TALIP_I-1561456315_TS-MYTS_FNO-7_09mhe5f3

destination for restore of datafile 00007: ‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’

channel ORA_DISK_1: copied datafile copy of datafile 00007

output file name=‘/u01/app/oracle/oradata/TEST/datafile/myts01.dbf’ RECID=0 STAMP=0

Finished restore at 29-JAN-18

Starting recover at 29-JAN-18

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-JAN-18

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

RMAN>exit;

Everything is fine. RMAN took care of everything. Our database is in good hands 

SQL> select status from v$instance;

STATUS

————

OPEN

Buffer Cache Hit Ratio

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

•  Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

•  DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

•  Physical Reads - The cumulative number of blocks read from disk.

•  Logical reads are the sum of consistent gets and db block gets.

•  The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

•  Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

 To Check with Query

select   sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",

            sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",

            sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",

            round((sum(decode(name, 'consistent gets',value, 0)) +

                   sum(decode(name, 'db block gets',value, 0)) -

                   sum(decode(name, 'physical reads',value, 0))) /

                  (sum(decode(name, 'consistent gets',value, 0)) +

                   sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"

from   v$sysstat;

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

•  Gets - Total number of requests for information on the data object.

•  Cache Misses - Number of data requests resulting in cache misses

•  Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

To Check with Query

select   sum(GETS),

            sum(GETMISSES),

            round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)

from    v$rowcache;

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

•  Pins - The number of times a pin was requested for objects of this namespace.

•  Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

•  Hit Ratio should be > 85%

To Check with Query

select   sum(PINS) Pins,

            sum(RELOADS) Reloads,

            round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from    v$librarycache;

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

•  Executions - The number of times a pin was requested for objects of this namespace.

•  Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

•  Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

To Check with Query

select   sum(PINS) Executions,

            sum(RELOADS) cache_misses,

            sum(RELOADS) / sum(PINS) miss_ratio

from    v$librarycache;