Step 1 : Create Temporary Table : CREATE TABLE unpar_table ( a NUMBER, y number, name VARCHAR2(100), date_used date); Step 2 : load table with 1,000,000 rows begin for i in 1 .. 1000 loop for j in 1 .. 1000 loop insert into unpar_table values… Read More
AWR Difference Report for NON RAC: @?/rdbms/admin/awrddrpt.sql AWR Difference Report for RAC : @?/rdbms/admin/awrddrpi.sql AWR Report for Non RAC : @?/rdbms/admin/awrrpt AWR Report for RAC : @?/rdbms/admin/awrrpti Global AWR RAC Report : @?/rdbms/admin/awrgrpt.sql ASH Report : @?/rdbms/admin/ashrpt.sql GLOBAL AWR RAC Differenct Report : @?/rdbms/admin/awrgdrpt.sql AWR… Read More
Instance Information : set linesize 300 select name||’ – ‘||instance_name||’@’||host_name||’ – ‘||DB_UNIQUE_NAME||’ – ‘||version||’ – ‘||open_mode||’ – ‘||to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) || ‘ – ‘|| database_role from v$database, v$instance; FRA Usage : set linesize 300 col file_type for a40 select * from V$FLASH_RECOVERY_AREA_USAGE; Check Restore Point :… Read More
Quickly Check SQL Server CPU Pressure Following query results how many threads are waiting to be executed on the server, if there are more than two threads waiting to be executed on a server, then that server can be assumed to have a CPU bottleneck… Read More
Live Insert Counts Without Disturbing Running Transaction There are times long running insert transactions are annoying and we don’t get direct details of running transaction like how many rows inserted and how many are pending. Following query gets estimated rows inserted for the specific table mentioned. SELECT… Read More
Check Active Queries and Related Details Many times DBAs need to check currently active queries on the DB servers and other details like estimated completion time, blockings, query plan, query text, host name, user login details etc. Following query generate such detail for active queries.… Read More
Expensive Queries of a database Following Query will find the expensive queries on specific database on SQL Instance. select DB_NAME(s.dbid) DBNAME , t.Text, spid from sysprocesses s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t where DB_NAME(s.dbid) like ‘Database_Name%’ Thank you for giving your valuable time… Read More
AlwaysOn SQL Agent Alerts Please find below script to create SQL Server Agent Alerts. Please note that operator name should be changed according to your SQL operator. The below script will provide one output, you will have to execute the out again to create the… Read More
Capture SQL Server Important Details Please find the below script to capture SQL Server Important Details before upgrade, migration etc. PRINT ‘***************** Server Name ********************’ SET NOCOUNT ON select @@SERVERNAME go PRINT ‘***************** Version ********************’ SET NOCOUNT ON select @@version go PRINT ‘***************** License Information********************’… Read More
Find the Database Restore History On SQL Instance, at times we should know what DB’s got restored on specific instance or want to know when specific DB got restored. Below query will help to achieve that. SELECT * FROM MSDB..RESTOREHISTORY ORDER BY RESTORE_DATE DESC … Read More
