Month: May 2021

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 .

On 13.1 OMS repository:

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;
Advertisement

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 and materialized view log shrink etc.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe