How to Get All Oracle Databases Size From OEM Repository

Database Manager asks you as a DBA about all the database size in the supported environment .

SQL> select host_name,target_name, sum( FILE_SIZE/1024/1024/1024)
     from mgmt$db_datafiles 
     group by host_name,target_name 
     order by host_name, target_name;

HOST_NAME                                TARGET_NAME                              SUM(FILE_SIZE/1024/1024/1024)
---------------------------------------- ---------------------------------------- -----------------------------
HOST1                                    DB1                                                    38.65
HOST2                                    DB2                                                    385.88
HOST3                                    DB3                                                    27.52
...
..
.

OR we can use another another view mgmt$db_tablespaces :

SQL> select host_name,target_name,sum( TABLESPACE_SIZE/1024/1024/1024) 
     from mgmt$db_tablespaces 
     group by host_name,target_name 
     order by 1,2;

How to Write Errors into SQL Server Log

The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:

IF EXISTS (
    SELECT bs.database_name,  MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
    FROM  msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
    INNER JOIN master.sys.databases d ON d.name = bs.database_name
    WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
    GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log

Check the log as below:

ALso we can check spid 55 details:

select * from sys.sysprocesses where spid =55;

How to remotely kill a Remote Desktop Session

 Check all Current RDP Sessions

C:\>qwinsta /server:SERVERNAME

 SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
 console                                     0  Conn    wdcon
 rdp-tcp                                 65536  Listen  rdpwd
 rdp-tcp#1         orace                     1  Active  rdpwd

Kill a Remote Desktop Session

C:\>rwinsta /server:SERVERNAME 1 

Here “1” is the ID from previous checking of RDP sessions.

The killed connection will get the following message:

How to Know When Oracle User Password Changed?

Oracle database internal table SYS.USER$ contains useful information about users and roles. Some of the columns have got following meaning:

  • NAME – name for user or role
  • TYPE# – 0 for role or 1 for user
  • CTIME – the date of creation
  • PTIME the date the password was last changed
  • EXPTIME – the date the password has last expired
  • LTIME – the date the resource was last locked
  • LCOUNT – number of failed logon
SQL> SELECT
        name,
       ctime,
       ptime
FROM
      sys.user$
WHERE
      name = 'SYS';

NAME                 CTIME     PTIME
-------------------- --------- ---------
SYS                  31-MAR-16 11-MAY-21

How to Create and Maintain Materialized View

Here we will demonstrate how to create a materialized view with fast refresh, make a materialized view updateable, materialized view partitions, SYSDATE usage in materialized view creation etc.

Subscribe to get access

Read more of this content when you subscribe today.