Month: February 2020

List All Database Files on SQL Server

SELECT 
    db.name AS             [Database Name], 
    mf.name AS             [Logical Name], 
    mf.type_desc AS        [File Type], 
    mf.name AS	           [Logical Name],
    mf.state AS	           [ State ],
    mf.state_desc AS       [ State Desc],
    mf.physical_name AS    [Path],
    CAST((mf.Size*8)/1024/1024 AS DECIMAL(18,1)) AS [Current Size(GB)],
    CASE 
	WHEN mf.is_percent_growth >0 THEN
	     'TRUE'  
	ELSE
	     'FALSE' 
	END AS [Autogrowth],
    CASE 
	WHEN  mf.max_size = 0  THEN  'No growth is allowed'
	WHEN  mf.max_size = -1 THEN  'Unlimited'
	ELSE
             CAST(CAST(mf.max_size as BIGINT)*8/1024/1024 AS VARCHAR(30)) +' MB'  
	END AS [MaximumSize] 
FROM 
     sys.master_files AS mf
INNER JOIN sys.databases AS db 
  ON  db.database_id = mf.database_id
Order by db.name,mf.type_desc;

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

Try to drop a user in a RAC environment with below errors :

SQL> drop user testuser cascade;
drop user testuser cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

There is no sessions, and objects for this user.

SQL> select count(*) from gv$session where username='TESTUSER';

COUNT(*)
----------
0
SQL> select count(*) from dba_objects where owner='TESTUSER';

COUNT(*)
----------
0

It seems there is internal issues in database for this user.

we can either wait for a while , or bounce the database. After this, it is OK to drop the user.

SQL> drop user TESTUSER cascade;

User dropped.