Author: dbalifeeasy

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

Version store bloat detected

In MSSQL database log, there are following errors :

Date 1/05/2021 1:02:03 PM
Log SQL Server (Current -  1/05/2021 1:02:03  PM)

Source spid 123

Message
Version store bloat detected
Date  1/05/2021 1:02:03  PM 
Log SQL Server (Current -  1/05/2021 1:02:03  PM )

Source spid 123

Message
Error: 51000, Severity: 17, State: 1.

Check which session caused this issue:

select * from sys.sysprocesses where spid = 123;

Check tempdb space usage:

use tempdb
exec sp_spaceused @oneresultset = 1

Check all version records in the version store in each database:

select db_name(database_id), COUNT(*)
from sys.dm_tran_version_store
GROUP BY database_id

dbname	        totalVersionRecords
TESTDB  	5535970
msdb	        126687
...
..
.

Check space usage of objects in tempdb:

select  
 db_name(database_id) as dbname, 
 FILEGROUP_ID, 
 FILE_ID,    
 version_store_reserved_page_count/128/1024 AS Version_Store_Size_GB 
from 
  tempdb.sys.dm_db_file_space_usage ;

dbname	FILEGROUP_ID	FILE_ID	Version_Store_Size_GB
tempdb	1	        1	5
tempdb	1	        3	2
tempdb	1	        4	5
tempdb	1	        5	2
tempdb	1	        6	2
tempdb	1	        7	2
tempdb	1	        8	2
tempdb	1	        9	2


SELECT * FROM sysfiles order by groupid;

 Check all active transactions that generate or potentially access row versions:

is_snapshotint0 = Is not a snapshot isolation transaction.

1 = Is a snapshot isolation transaction.
select session_id, is_snapshot, transaction_sequence_num, commit_sequence_num, elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by 5 desc;

How To Resume Suspended Database Mirroring In SQL Server

In SQL Server Management Studio, Right click principle database -> properties -> Mirroring, the click Resume button. The alternative way is to right click principle database -> Tasks -> Mirror, the click Resume button.

We can also run the following Transact-SQL query to resume the database mirroring:

ALTER DATABASE userdb SET PARTNER RESUME;

To check the synchronizing progressing by running following Transact-SQL query:

SELECT [counter_name] as CounterName,[cntr_value] as CounterValue
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')
AND [instance_name]='TESTDB';

CounterName	      CounterValue
Redo Queue KB     1260219                                                                                              
Log Send Queue KB 8407945

“Redo Queue KB” : Logs to applied onto mirroring database.
“Log Send Queue KB” : Logs to be sent to mirroring database.

Another alternative way to check mirroring status and statistics:

Right click principle database -> Tasks -> Launch Database Mirroring Monitor.

Database Mirror Monitor

Click History, you can find when the mirror broke:

Oracle Multitenant Database System User ORA-01031: insufficient privileges

DBA is trying to setup a job to purge table “sys.aus$” periodically with following sql and errors:

SQL> show user;

USER is "SYSTEM"

SQL> delete FROM sys.aud$ WHERE  NTIMESTAMP# < sysdate -31;

ERROR at line 1:
ORA-01031: insufficient privileges

Solution

Though SYSTEM user has DBA role privilege, the specific object privilege is still needed in this case:

SQL> grant delete on sys.aud$ to system container=all;

Grant succeeded.

SQL>  delete FROM sys.aud$ WHERE  NTIMESTAMP# < sysdate -31;

2006228 rows deleted.


SQL> commit;

Commit complete.