ORA-01940: cannot drop a user that is currently connected

Issue with drop user in Oracle

While Dropping a user if the drop user command is throwing error like:

ORA-01940: cannot drop a user that is currently connected

Follow the below steps:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' "SQL Statement" from v$session where username=UPPER('&username');

SQL Statement

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

alter system kill session '101,265842' immediate;

Execute the alter statement to kill the active session.

alter system kill session '101,265842' immediate;

System altered.

Now the drop user will work:

drop user <username> cascade;

Note: cascade option drops all the dependent objects under the schema.

Gather Statistics

 

Oracle Gather Stat 

Gather Table, Index and Schema Statistics

 

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);

( Note: Cascade gathers Index stats associated with the table )

How to check RMAN progress?

Check RMAN job progress 

The below query can be used to identify the progress of any rman job. Either backup or restore.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set line 200;

set pages 500;

set long 5000;

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,

sysdate+(TIME_REMAINING/60/60/24) done_by

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sofar != totalwork

and totalwork > 0

/

Failed to start ASMB (connection failed) state=0x1

Issue with Oracle ASM

OPatch Failed with error code 73

Oracle OPatch  Error

Error:

Oracle Home       : <ORACLE_HOME> Path 

Central Inventory : The inventory that is being used by this opatch session

   from           : The inventory that is stored in the Oracle Home

OPatch version    : 12.2.0.1.44

OUI version       : 12.2.0.7.0

Log file location : /u01/oracle/product/19.3.0/cfgtoollogs/opatch/opatch<date>.log

OPatchSession cannot load inventory for the given Oracle Home <oracle_home>. Possible causes are:

   No read or write permission to ORACLE_HOME/.patch_storage

   Central Inventory is locked by another OUI instance

   No read permission to Central Inventory

   The lock file exists in ORACLE_HOME/.patch_storage

   The Oracle Home does not exist in Central Inventory

UtilSession failed: OPatch failed to locate Central Inventory.

Possible causes are:

    The Central Inventory is corrupted

    The oraInst.loc file specified is not valid.

This is because, sometime the /etc/oraInst.loc is pointing to the ora inventory in a temp location which the $ORACLE_HOME/oraInst.loc is not aware.

cat /etc/oraInst.loc

cat $ORACLE_HOME/oraInst.loc

check if both the files are pointing to the same location. if not, take a backup of the file and update the central inventory in $ORACLE_HOME/oraInst.loc.

cp -p $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.old

Modify the file accordingly for parameter inventory_loc=<Full PATH of the inventory, which can be copied from /etc/oraInst.loc>

Note: One more observation on the file permission, if you are running the grid with different usernames, the same group and sharing the same inventory, make sure, the files under /u01/oraInventory/ContentsXML have 644 permissions. So that while doing patching with Oracle users, it can modify those XML files.

Hope this helps resolve the error, happy patching!!

Best Wishes!!

Find Concurrent Program Run History from backend

Query to find concurrent program run history and status

SELECT f.request_id,

         pt.user_concurrent_program_name

             user_conc_program_name,

         f.actual_start_date

             start_on,

         f.actual_completion_date

             end_on,

         p.concurrent_program_name

             concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'RUNNING',

                 'C', 'COMPLETED',

                 f.phase_code)

             phase,

         DECODE (f.status_code,  'C', 'NORMAL',  'E', 'ERROR',  f.status_code)

             Status,

         f.requested_by,

         fu.user_id,

         fu.user_name

    FROM apps.fnd_concurrent_programs   p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests   f,

         apps.fnd_user                  fu

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.language = USERENV ('lang')

         AND f.actual_start_date IS NOT NULL

         AND f.actual_start_date >

             TO_DATE ('15-DEC-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

         AND f.actual_completion_date <

             TO_DATE ('17-DEC-2021 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

         AND f.requested_by = fu.user_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME = '&User_concurrent_program_name'

--AND fu.user_name = '&user_name'

ORDER BY f.actual_start_date ASC; 

##Change the date accordingly.

Temp Tablespace Usage

Oracle Temp Tablespace usage

Check for Temp tablespace usage

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 as FILE_SIZE,sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_temp_files

where tablespace_name ='TEMP' group by file_name,tablespace_name,autoextensible order by file_name;

Data file usage of a tablespace

Oracle Tablespaces 

Check Data File Size in any given Tablespace (Dynamic) :

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 "FILE_SIZE(MB)",sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_data_files

where tablespace_name ='&tablespace_name' group by file_name,tablespace_name,autoextensible order by file_name;

Query to find Tablespace utilization in Oracle Database

Oracle Tablespace Utilization

Find the tablespace utilization on Oracle Database (10g,11g,12c,19c)

column file_name format a45

column tablespace_name format a10

col tablespace_name for a40

set verify off

set pages 3000

set line 3000

 

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes* 100, 0), '990.00') free_pct,

decode(sign((NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes/1024/1024, 0)),-1,0,

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB" FROM

sys.dba_tablespaces dts,

(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,

(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs

WHERE

dts.tablespace_name = ddf.tablespace_name(+)

AND dts.tablespace_name = dfs.tablespace_name(+)

order by free_pct;

Best Wishes!!

Create Oracle Database User

Oracle Database User Management

This is the simplest task for a Database Administrator. 

Login to sqlplus session from your OS(Windows/Linux/AIX) using sysdba / a user who has create user privilege. 

sqlplus / as sysdba

alter user <username> identified by <password>;

this will create a user with default tablespace and profile. Depending on the organization's policy and user's requirement you might need to mention few other options in the query. Those are advanced options you can explore more on my advance create user query.

Now user needs some privileges to connect to the database and do some transactions or query any tables.

CONNECT - this is the Oracle-defined default privilege for any user to be able to connect to the database.

grant connect to <username>;

alternatively, grant create session to <username>;  will do the same.

Now, for the user to be able to query any table in the db, one basic privilege is required.

grant select any table to <username>;

This will create a db user in Oracle. 

To see the user is created properly you can validate with below query.

select * from dba_users where username='<username>';

The dba_users is a data dictionary view which eventually being created from all_users. To know more about all_users and dba_users column properties stay tuned!!

Best Wishes!!