Mamun's DBA Blog: Manage Tablespace in oracle

What is a tablespace and datafile:

Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(DISKS) . So when we create a datafile of 30G in database, it will eat 30G of space from defined storage. All the table or index segment is created in tablespace only.

Types of Tablespace

SYSTEM TABLESPACE:

    Each database contains a SYSTEM tablespace.( It is created by default while creating the database itself) .
    It contains the data dictionary tables of the database.
    All the view,procedures, functions, packages and triggers are stored in SYSTEM tablespace
    It is not recommended to user SYSTEM tablespace for creating any other table or index.

SYSAUX TABLESPACE:    

All the database metadata, that doesnt store in SYSTEM tablespace, will be stored here.

    Many database components datas are stored in this tablespace

PERMANENT TABLESPACE:

    When someone says tablespace, Means they are mostly refering to PERMANENT TABLESPACE
    This tablespace are used for storing the actual schema tables or indexes . i.e it Stores user data.
    It doesnt store any of the data dictionary tables.

    There are two types of PERMANENT TABLESPACE.

    SMALL FILE – (DEFAULT) – This is the common size tablespace, It can contain multiple datafiles with each datafile of size 31G;

    BIG FILE – It can contain only one datafile which can grow upto 128 TB.

    UNDO TABLESPACE:

    This tablespace stores Undo data:
    We can’t create any table or index inside undo tablespace.
    Each instance in the database have one defined UNDO tablespace. i.e for standalone database one assigned undo tablespace, and for multi node rac system, each instance on the node will have one assigned undo tablespace.

    TEMPORARY TABLESPACE:

    This tablespace stores the data temporarily for sessions doing sorting and join operations.
    Sort operations are also generated by SELECT * FROM WHERE CLAUSE that join rows from within tables and between tables. The segments generated during this process will be stored in temp tablespace.
    Each database will have one default temporary tablespace

    Create a NEW tablespace:

   Create tablespace DATA datafile '/u01/app/oracle/oradata/data01.dbf' size 5G autoextend on next 500M;
       
Here, 500M of extent will be added to the datafile automatically, when the space is required.

For creating a tablespace on ASM disk group:

– With OMF file system:

Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;

--- Else we can give the complete path also:

Create tablespace DATA datafile '+DATAG/oradata/datafile/data01.dbf' size 5G autoextend on next 500M;

Adding a datafile:

alter tablespace DATA add datafile '/u01/app/oracle/oradata/data02.dbf' size 2G;

Resize a datafile: ( To increase or decrease the datafile)

alter database datafile '/u01/app/oracle/oradata/data02.dbf' resize 3G;

Change default tablespace:

Every database will have one default tablespace. If any user is created without mentioning any tablespace_name, then that schema objects will be created under default tablespace. – Get the current default tablespace:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

ALTER DATABASE DEFAULT TABLESPACE DATATBS;

Rename a tablespace:

set pagesize 200
set lines 200
col file_name for a57

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

   FILE_ID FILE_NAME                            TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
 37 /u01/app/oracle/oradata/cdb1/test1.dbf        TESTING


--- Rename the tablespace_name from TESTING to PRODUCING;

SQL❯ alter tablespace TESTING rename to PRODUCING;

Tablespace altered.

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

   FILE_ID FILE_NAME                            TABLESPACE_NAME
---------- -------------------------------------------------------- ------------------------------
 37 /u01/app/oracle/oradata/cdb1/test1.dbf        PRODUCING

 NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.

Drop a tablespace:

-- Drop a tablespace without removing the physical database files.

SQL❯ drop tablespace TESTING;

Tablespace dropped.

SQL❯ select file_name from dba_data_files where tablespace_name='TESTING';

no rows selected

 -- Drop tablespace including the physical datafiles.

SQL❯ drop tablespace TESTING including contents and datafiles;

Tablespace dropped.

Make tablespace Read only:

alter tablespace DATA2 READ ONLY;

alter tablespace DATA2 read write;

Take tablespace offline:

DBAs usually make the tablespace offline, while restoring or recovering any underlying corrupt datafile.
-- To make a tablespace offline

ALTER TABLESPACE DATATS OFFLINE;

-- To make it online

ALTER TABLESPACE DATATS ONLINE;

Create a temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP1 '/u01/app/oracle/tempfile/temp01.dbf' size 1G autoextend on next 200M;

Adding a tempfile:

alter tablespace TEMP1 add tempfile '/u01/app/oracle/tempfile/temp02.dbf' size 1G autoextend on next 200M;

Resize tempfile:

alter database tempfile '/u01/app/oracle/tempfile/temp02.dbf' resize 2G;

Drop tempfile:

It will drop and remove the tempfile from physical location.

ALTER DATABASE TEMPFILE '/u01/app/oracle/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;

 Change default temporary tablespace:

When we create a new user, we assign a default temporary tablespace. If nothing is assigned, then it will use the default temporary tablespace set a database level.
Get the current default temporary tablespace for the database


SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
 
-- Set New default temp tablespace


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
 
-- Check the new default temp tablespace
SQL❯ SELECT PROPERTY_VALUE FROM  DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP2

Mamun's DBA Blog: DB Link and GLOBAL_NAMES parameter in Oracle

GLOBAL_NAMES is either set to FALSE OR TRUE.

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

If the value of GLOBAL_NAMES is FALSE, then any name can be given to DB_LINK. If value is TRUE, then database link name should be same as that of the database it is pointing.

Default value is FALSE:

SQL> show parameter global_name

NAME                                 TYPE        VALUE

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

global_names                         boolean     FALSE

1. Lets create a database link with any name:

SQL> create public database link TESTDB  connect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created.

SQL>  select sysdate from dual@TESTDB;

SYSDATE

---------

01-MAR-19

SQL>drop public database link TESTDB ;

So with GLOBAL_NAME set to FALSE, db_link name is independent of target database name.

2. Update the GLOBAL_NAMES value to TRUE:

SQL> alter system set global_names=TRUE scope=both;

System altered.

SQL> show parameter global_names

NAME                                 TYPE        VALUE

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

global_names                         boolean     TRUE

3. Create database link as before(with different name)

SQL> create public database link TESTDB  connect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created

SQL> select sysdate from dual@TESTDB;

select sysdate from dual@TESTDB

                         *

ERROR at line 1:

ORA-02085: database link TESTDB connects to ORCL

We can see, when global_name is TRUE, DB_LINKs with different name as that of database name is not working.

4. Lets create a db_link having same name as target db.

SQL> create public database link ORCLconnect to SYSTEM IDENTIFIED BY welcome1 USING 'ORCL';

Database link created.

SQL>  select sysdate from dual@ORCL;

SYSDATE

---------

01-MAR-19

Hope it Works!!!

Mamun's DBA Blog: How to create listener using NETCA utility

This article explains the steps for creating a LISTENER using NETCA GUI utility.

NOTE – > utility need to execute from XMANAGER/XSHELL(From where we can call GUI),Just like DBCA or DBUA utility.

1. Set appropriate ORACLE_HOME as per requirement( EITHER GRID HOME OR ORACLE DB BINARY HOME)
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0/dbhome_3
export PATH=$ORACLE_HOME/bin:$PATH
2. execute NETCA
 


 

 

 

Make sure the port number not used on that server.




The listener got created.

Check the status:

lsnrctl status LISTENER_TEST

LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 04-MAR-2018 11:03:31

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1)(PORT=1587)))

STATUS of the LISTENER

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

Alias                        LISTENER_TEST

Version                    TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date                04-MAR-2019 11:01:35

Uptime                    0 days 0 hr. 1 min. 56 sec

Trace Level             off

Security                  ON: Local OS Authentication

SNMP                     OFF

Listener Parameter File   /oracle/app/oracle/product/12.2.0/dbhome_3/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/diag/tnslsnr/srv1/listener_test/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv1)(PORT=1587)))

The listener supports no services -- >>>>>>      NO SERVICES AVAILABLE.

It shows listener supports no services, because no database has registered with this listener.

Follow below steps for registering the listener port with the database .

1. Add the entry in tnsnames.ora file of the binary.

LISTENER_TEST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = srv1)(PORT = 1587))

2. Set local_listener parameter and register.

SQL> alter system set local_listener='LISTENER_TEST' scope=both;

System altered.

SQL> alter system register;

System altered.

3. Check the listener status again:

# lsnrctl status LISTENER_TEST

LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 04-MAR-2018 11:05:15

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1)(PORT=1587)))

STATUS of the LISTENER

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

Alias                        LISTENER_TEST

Version                    TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date                04-MAR-2018 11:01:35

Uptime                    0 days 0 hr. 3 min. 39 sec

Trace Level             off

Security                   ON: Local OS Authentication

SNMP                     OFF

Listener Parameter File   /oracle/app/oracle/product/12.2.0/dbhome_3/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/diag/tnslsnr/srv1/listener_test/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv1)(PORT=1587)))

Services Summary...

Service "TEST" has 1 instance(s).

  Instance "TEST", status READY, has 1 handler(s) for this service...

Service "TESTXDB" has 1 instance(s).

  Instance "TEST", status READY, has 1 handler(s) for this service...

The command completed successfully

We can see that the service is available now.:)

Mamun's DBA Blog: Oracle Database User Management

User is basically used to connect to database. All db objects like table,index,view etc can be created under that user.In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

1. Create a user:

create user DBA_MAMUN identified by welcome1
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

Minimum privilege required to connect to a database is create session
grant create session to DBA_MAMUN;

2. Change password of a user:

alter user DBA_MAMUN identified by welcome1;

3. Lock/unlock a user

alter user dba_mamun account lock;
alter user dba_mamun account unlock;

4. Make a user password expiry:

When we make a user id expiry, then when the user does login, it will prompt him to set a new password.
alter user dba_mamun account expire;

5. Changing default tablespace of a user:

select username,default_tablespace from dba_users where username='DBA_MAMUN';

USERNAME                DEFAULT_TABLESPACE
----------------------- ------------------------------
DBA_MAMUN                   USERS

alter user  DBA_MAMUN default tablespace DATATS;

select username,default_tablespace from dba_users where username='DBA_MAMUN';

USERNAME                DEFAULT_TABLESPACE
----------------------- ------------------------------
DBA_MAMUN                   DATATS

6. Changing default TEMP tablespace of a user:

SQL>  select username,TEMPORARY_TABLESPACE from dba_users where username='DBA_MAMUN';
USERNAME TEMPORARY_TABLESPACE
----------------------- ------------------------------
DBA_MAMUN TEMP

alter user DBA_MAMUN temporary tablespace TEMP;

SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='DBA_MAMUN';

USERNAME TEMPORARY_TABLESPACE
----------------------- ------------------------------
DBA_MAMUN TEMP

7. PROFILE:

A profile enforces set of password security rules and resource usage limit.
While creating a user if no profile is mentioned, then DEFAULT profile will be assigned.

DEFAULT PROFILE SETTING:
col limit for a12
col profile for a14
set lines 200
set pagesize 200
select profile,resource_name,RESOURCE_TYPE,limit 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


*SESSION_PER_USER – No. of allowed concurrent sessions for a user
*CPU_PER_SESSION – CPU time limit for a session, expressed in hundredth of seconds.
*CPU_PER_CALL – Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
*CONNECT_TIME – Specify the total elapsed time limit for a session, expressed in minutes.
*IDLE_TIME – Specify the permitted periods of continuous inactive time during a session, expressed in minutes.
*LOGICAL_READS_PER_SESSION – Specify the permitted number of data blocks read in a session, including blocks read from memory and disk
*LOGICAL_READS_PER_CALL –permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
*PRIVATE_SGA – SGA a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

*FAILED_LOGIN_ATTEMPTS – No. of failed attempts to log in to the user account before the account is locked
*PASSWORD_LIFE_TIME: No. of days the account will be open. after that it will expiry.
*PASSWORD_REUSE_TIME: number of days before which a password cannot be reused
*PASSWORD_REUSE_MAX: number of days before which a password can be reused
*PASSWORD_LOCK_TIME: Number of days the user account remains locked after failed login
*PASSWORD_GRACE_TIME: Number of grace days for user to change password
*PASSWORD_VERIFY_FUNCTION: PL/SQL that can be used for password verification

8. Create a new profile:

CREATE PROFILE "APP_PROFILE"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME 90
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 180
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION NULL
         PASSWORD_LOCK_TIME UNLIMITED
         PASSWORD_GRACE_TIME UNLIMITED;
        

9. Alter a profile:

ALTER PROFILE APP_PROFILE LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED;

10. Change profile of an user:

SQL> select username,profile from dba_users where username='DBA_MAMUN';
USERNAME PROFILE
----------------------- ------------------------------
DBA_MAMUN DEFAULT

ALTER USER SCOTT PROFILE APP_PROFILE;

SQL> select username,profile from dba_users where username='DBA_MAMUN';

USERNAME PROFILE
----------------------- ------------------------------
DBA_MAMUN APP_PROFILE

11. How to make a user non-expiry:

Usually application users we need to set non-expiry. I.e it will never expire. To set it, we need to either create a profile with PASSWORD_LIFE_TIME UNLIMITED or alter the profile of that user.
SQL> select username,profile,EXPIRY_DATE from dba_users where username='DBA_MAMUN';
USERNAME PROFILE EXPIRY_DATE
----------------------- ----------------------- ---------
DBA_MAMUN APP_PROFILE 16-AUG-17

ALTER PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQL> select username,profile,EXPIRY_DATE from dba_users where username='DBA_MAMUN';

USERNAME PROFILE EXPIRY_DATE
----------------------- ----------------------- ---------
DBA_MAMUN APP_PROFILE

PRIVILEGES:

A privilege is a permission to execute either a particular type of sql statements or to perform particular action on database objects.

Two type of privilege:
1. SYSTEM PRIVILEGE
2. OBJECT PRIVILEGE
SYSTEM PRIVILEGE

A system privilege is the right to perform a particular action or to perform an action on any object of a particular type.

12.List of all system privileges:

SQL>select distinct privilege from dba_sys_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE OPERATOR
CREATE VIEW
CREATE ANY PROCEDURE
CREATE DATABASE LINK
DEQUEUE ANY QUEUE
DEBUG ANY PROCEDURE
CREATE PUBLIC SYNONYM
SELECT ANY TRANSACTION
READ ANY TABLE
CREATE ASSEMBLY
EXECUTE ANY INDEXTYPE
CREATE ANY TYPE
ANALYZE ANY
DROP PUBLIC SYNONYM
AUDIT SYSTEM
EXECUTE ANY ASSEMBLY
CREATE ANY EDITION
ADMINISTER ANY SQL TUNING SET
DROP ANY RULE SET
CREATE ANY EVALUATION CONTEXT
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
GRANT ANY PRIVILEGE
ALTER RESOURCE COST
ALTER ANY TRIGGER
DROP ANY SYNONYM
CREATE USER
CREATE SQL TRANSLATION PROFILE
EM EXPRESS CONNECT
CREATE ANY TRIGGER
EXEMPT REDACTION POLICY
CREATE DIMENSION
CREATE RULE SET
EXECUTE ANY EVALUATION CONTEXT
ALTER ANY OUTLINE
UNDER ANY TYPE
ALTER ANY ROLE
CREATE ANY MINING MODEL
DROP ANY OUTLINE
ALTER ANY INDEX
UPDATE ANY TABLE
CREATE TABLESPACE
USE ANY SQL TRANSLATION PROFILE
DROP ANY VIEW
CREATE ANY SQL TRANSLATION PROFILE
BECOME USER
DROP ANY MEASURE FOLDER
CREATE ANY CUBE
CREATE ANY OUTLINE
COMMENT ANY MINING MODEL
ALTER ANY INDEXTYPE
DROP PROFILE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE JOB
EXEMPT ACCESS POLICY
QUERY REWRITE
EXECUTE ANY RULE SET
CREATE PLUGGABLE DATABASE
ALTER ANY CUBE
ALTER ANY RULE SET
UNDER ANY VIEW
DROP ANY PROCEDURE
CREATE ROLE
CREATE ANY TABLE
RESTRICTED SESSION
ALTER ANY MEASURE FOLDER
ADVISOR
IMPORT FULL DATABASE
DROP ANY TRIGGER
ALTER ANY PROCEDURE
SELECT ANY SEQUENCE
CREATE ANY CONTEXT
UNDER ANY TABLE
ALTER PROFILE
FORCE TRANSACTION
DROP ANY MINING MODEL
CREATE ANY OPERATOR
CREATE PUBLIC DATABASE LINK
MANAGE ANY FILE GROUP
MANAGE TABLESPACE
CREATE CUBE DIMENSION
UNLIMITED TABLESPACE
SELECT ANY TABLE
CREATE EVALUATION CONTEXT
ON COMMIT REFRESH
CREATE ANY INDEX
EXECUTE ANY PROGRAM
ALTER ANY CUBE BUILD PROCESS
CREATE ANY MEASURE FOLDER
EXECUTE ASSEMBLY
CREATE ANY SQL PROFILE
ALTER ANY TYPE
CREATE PROFILE
EXECUTE ANY PROCEDURE
CREATE ANY CLUSTER
CREATE ANY ASSEMBLY
CREATE ANY RULE
EXECUTE ANY TYPE
ALTER ANY CLUSTER
DROP ANY CUBE
DROP PUBLIC DATABASE LINK
SELECT ANY MEASURE FOLDER
REDEFINE ANY TABLE
SELECT ANY CUBE
CREATE ANY INDEXTYPE
CREATE ANY CUBE DIMENSION
EXEMPT DDL REDACTION POLICY
MANAGE SCHEDULER
ALTER SESSION
CREATE TRIGGER
CREATE MATERIALIZED VIEW
ALTER ANY SEQUENCE
EXEMPT IDENTITY POLICY
CREATE ANY CREDENTIAL
SET CONTAINER
GLOBAL QUERY REWRITE
ALTER ANY LIBRARY
GRANT ANY ROLE
ALTER USER
CREATE MEASURE FOLDER
UPDATE ANY CUBE
READ ANY FILE GROUP
GRANT ANY OBJECT PRIVILEGE
DROP ANY OPERATOR
CREATE CREDENTIAL
CHANGE NOTIFICATION
CREATE ANY SYNONYM
INSERT ANY TABLE
EXEMPT DML REDACTION POLICY
EXECUTE ANY RULE
INSERT ANY MEASURE FOLDER
DROP ANY CUBE DIMENSION
ALTER ANY ASSEMBLY
LOGMINING
CREATE ANY VIEW
CREATE TYPE
FLASHBACK ARCHIVE ADMINISTER
ADMINISTER SQL MANAGEMENT OBJECT
ALTER ANY MINING MODEL
SELECT ANY MINING MODEL
CREATE EXTERNAL JOB
DROP ANY EVALUATION CONTEXT
CREATE LIBRARY
DROP ANY SQL TRANSLATION PROFILE
CREATE MINING MODEL
DROP ANY CONTEXT
MANAGE ANY QUEUE
DROP ANY DIMENSION
CREATE ANY DIMENSION
CREATE ANY LIBRARY
DROP ANY MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER DATABASE
DROP ANY ROLE
LOCK ANY TABLE
DROP USER
DROP TABLESPACE
MERGE ANY VIEW
DROP ANY TYPE
COMMENT ANY TABLE
ALTER TABLESPACE
CREATE CUBE
ALTER ANY SQL PROFILE
DROP ANY INDEXTYPE
ALTER ROLLBACK SEGMENT
DROP ANY CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DELETE ANY CUBE DIMENSION
ANALYZE ANY DICTIONARY
CREATE TABLE
ALTER ANY TABLE
SELECT ANY DICTIONARY
CREATE CLUSTER
DEBUG CONNECT SESSION
CREATE INDEXTYPE
INHERIT ANY PRIVILEGES
DROP ANY SQL PROFILE
CREATE ANY DIRECTORY
DROP ANY INDEX
ENQUEUE ANY QUEUE
DROP ANY CLUSTER
SELECT ANY CUBE BUILD PROCESS
ADMINISTER KEY MANAGEMENT
ALTER ANY SQL TRANSLATION PROFILE
DROP ANY EDITION
CREATE ROLLBACK SEGMENT
SELECT ANY CUBE DIMENSION
ALTER ANY EVALUATION CONTEXT
FORCE ANY TRANSACTION
INSERT ANY CUBE DIMENSION
ALTER ANY OPERATOR
EXECUTE ANY LIBRARY
ALTER ANY MATERIALIZED VIEW
ALTER ANY CUBE DIMENSION
CREATE SYNONYM
FLASHBACK ANY TABLE
CREATE RULE
EXECUTE ANY CLASS
CREATE ANY SEQUENCE
ALTER SYSTEM
UPDATE ANY CUBE DIMENSION
UPDATE ANY CUBE BUILD PROCESS
CREATE CUBE BUILD PROCESS
DROP ANY ASSEMBLY
ADMINISTER SQL TUNING SET
EXECUTE ANY OPERATOR
DROP ANY LIBRARY
AUDIT ANY
DELETE ANY TABLE
RESUMABLE
DROP ANY TABLE
ALTER ANY EDITION
EXPORT FULL DATABASE
DROP ANY DIRECTORY
DROP ANY SEQUENCE
DROP ROLLBACK SEGMENT
CREATE ANY JOB
BACKUP ANY TABLE
DELETE ANY MEASURE FOLDER
MANAGE FILE GROUP
DROP ANY RULE
ALTER ANY DIMENSION
CREATE ANY RULE SET
ALTER ANY RULE

13.Grant a system privilege to a user:

Grant create any table,alter any table to DBA_MAMUN;

SQL>  select privilege,grantee from dba_sys_privs where grantee='DBA_MAMUN';

PRIVILEGE                                GRANTEE
---------------------------------------- ---------
CREATE ANY TABLE                        DBA_MAMUN
ALTER ANY TABLE                           DBA_MAMUN

14. Revoke a system privilege from a user:

REVOKE create any table from dba_mamun;
OBJECT PRIVILEGE:

An object privilege is the right to perform a particular action on an object or to access another user’s object.

15.list of object privileges:

SQL> select distinct privilege from DBA_TAB_PRIVS;

PRIVILEGE
----------------------------------------
EXECUTE
SELECT
INSERT
INDEX
DEQUEUE
USE
QUERY REWRITE
READ
ON COMMIT REFRESH
REFERENCES
INHERIT PRIVILEGES
DEBUG
ALTER
UPDATE
WRITE
FLASHBACK
DELETE

16.Grant object privilege:

grant insert,update,delete on SIEBEL.TEST to DBA_MAMUN;

-- grant execute on a procedure

grant execute on SIEBLE.DAILYPROC to DBA_MAMUN;

-- View the granted object privilege:

select grantee,owner,table_name,privilege from dba_tab_privs where grantee='DBA_MAMUN';

17.Revoke object privilege:

revoke update on siebel.test from DBA_MAMUN;
ROLE:

A role is a collection of privileges. It allows easier management of privileges.

17.Create a role:

create role DBA_ROLE;

18.Grant privileges to a role:

grant create session to DBA_ROLE;
grant select any table to DBA_ROLE;
grant insert on siebel.test to DBA_ROLE;

-- List of  SYSTEM privileges granted to a ROLE

SQL>  select role,privilege from role_sys_privs where role='DBA_ROLE';

ROLE         PRIVILEGE
------------ ----------------------------------------
DBA_ROLE     CREATE SESSION
DBA_ROLE     SELECT ANY TABLE

-- List of OBJECT privileges granted to ROLE;

SQL> select role,owner,table_name,privilege from role_tab_privs where  role='DBA_ROLE';

ROLE         OWNER        TABLE_NAME   PRIVILEGE
------------ ------------ ------------ ----------------------------------------
DBA_ROLE     SIEBEL       TEST          INSERT

19. Grant role to a User:

grant DBA_ROLE to dba_mamun;

-- List of the user and granted role:

SQL> select grantee,GRANTED_ROLE from dba_role_privs where granted_role='DBA_ROLE';

GRANTEE      GRANTED_ROLE
------------ -----------------------
SYS          DBA_ROLE
DBA_MAMUN    DBA_ROLE

20. Drop a user:

Dropping a user will drop all the objects it owns.

drop user DBA_MAMUN cascade;

21. Drop a Role:

Drop role DBA_ROLE;

Mamun's DBA Blog: How to drop a private database link from SYS user

Everyone commonly knows that, the private db_links can be dropped only by the owner of the database_link.  But you don’t have the password that db_link owner, then you can go for below steps.

SQL>select * from dba_db_links where OWNER='HR';

OWNER DB_LINK USERNAME HOST CREATED

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

HR testlink HR testdb 04-Feb-19

Now create one procedure under scoot user:

SQL> CREATE PROCEDURE hr.drop_db_link AS

BEGIN

EXECUTE IMMEDIATE 'drop database link testlink';

END drop_db_link;

/

Procedure created

 

Mamun's DBA Blog: How to find all active transactions in oracle database

Connect SQL Plus & Run below scripts.

col name format a10

col username format a8

col osuser format a8

col start_time format a17

col status format a12

tti 'Active transactions'

select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",

decode(t.space, 'YES', 'SPACE TX',

decode(t.recursive, 'YES', 'RECURSIVE TX',

decode(t.noundo, 'YES', 'NO UNDO TX', t.status)

)) status

from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s

where t.xidusn = r.usn

and t.ses_addr = s.saddr

/

Mamun's DBA Blog: How to create DB Link without modifying the tnsnames.ora

Suppose you want to create a database_link, but you don’t have the privilege to modify the tnsname entry. In that case you can create the database_link directly using tns description.

sql>create public database link DBA connect to orcl identified by welcome1 USING

'(DESCRIPTION=(ADDRESS_LIST=(

ADDRESS=(PROTOCOL=TCP)(HOST=srv1)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=DBA)))'

/

database link created.

Most easiest one is below. No need to write the complete tns description also.

create public database link TEST connect to system identified by welcome1 

 using  '192.168.227.4:1521/TEST';

Database link created.

SQL> select sysdate from dual@TEST;

SYSDATE

---------

13-APR-19