Sometimes it is necessary for the database administrator to remove existing connected sessions. For example to drop a database when users and/or applications cannot (or will not) disconnect or terminate cleanly.
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'database_name_with_existing_sessions' AND pid <> pg_backend_pid();
When a SQL Server process is killed (kill spid), the non-commited work (transactions) will take a certain amount of time to roll back.
Use kill spid with statusonly to determine how much time a killed process will take to rollback
-- display rman backups
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'dd.mm.yyyy hh24:mi:ss') start_time,
to_char(j.end_time, 'dd.mm.yyyy hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join
(select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-2
order by j.start_time desc;
# java -XshowSettings:properties -version 2>&1 > /dev/null | grep 'java.home'
java.home = /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre
set JAVA_HOME in ./etc/hadoop(hadoop-env.sh
From hadoop installation directory
$ ./bin/hadoop jar share/hadoop/mapreduce/hadoop-mapreduce-examples-3.0.0.jar wordcount input output
output directory should not exist before running above
-- output directories should not exist
./bin/hadoop jar ./share/hadoop/mapreduce/hadoop-mapreduce-examples-3.0.0.jar grep input output2 'dfs[a-z.]+'
./bin/hadoop jar ./share/hadoop/mapreduce/hadoop-mapreduce-examples-3.0.0.jar grep input output3 'yar[a-z.]+'
To display hidden Oracle initialization parameters:
select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and substr(ksppinm,1,1) = '_';
select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and substr(ksppinm,1,1) = '_';
See MOS: Oracle SSO account required.
My Oracle Support note 1513912.1 “TFA Collector – Tool for Enhanced Diagnostic Gathering” at https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1513912.1
DBCC TRACEON(2520)
DBCC HELP (‘?’)
GO
DBCC HELP(<command>)
GO
Following is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands.
DBCC activecursors [(spid)]
DBCC addextendedproc (function_name, dll_name)
DBCC addinstance (objectname, instancename)
DBCC adduserobject (name)
DBCC auditevent (eventclass, eventsubclass, success, loginname
, rolename, dbusername, loginid)
DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])
DBCC balancefactor (variance_percent)
DBCC bufcount [(number_of_buffers)]
DBCC buffer ( {‘dbname’ | dbid} [, objid [, number [, printopt={0|1|2} ] [, dirty | io | kept | rlock | ioerr | hashed ]]])
DBCC bytes ( startaddress, length )
DBCC cachestats
DBCC callfulltext
DBCC checkalloc [(‘database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]
DBCC checkcatalog [(‘database_name’)] [WITH NO_INFOMSGS]
DBCC checkconstraints [( ‘tab_name’ | tab_id | ‘constraint_name’ | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]
DBCC checkdb [(‘database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS] [, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK]
DBCC checkdbts (dbid, newTimestamp)]
DBCC checkfilegroup [( [ {‘filegroup_name’ | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS
[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC checkident (‘table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )
DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])
DBCC checktable (‘table_name'[, {NOINDEX | index_id | REPAIR}])
[WITH NO_INFOMSGS[, ALL_ERRORMSGS] [, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id,[batch_size])
DBCC cacheprofile [( {actionid} [, bucketid])
DBCC clearspacecaches (‘database_name’|database_id,
‘table_name’|table_id, ‘index_name’|index_id)
DBCC collectstats (on | off)
DBCC concurrencyviolation (reset | display | startlog | stoplog)
DBCC config
DBCC cursorstats ([spid [,’clear’]])
DBCC dbinfo [(‘dbname’)]
DBCC dbrecover (dbname [, IgnoreErrors])
DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
DBCC dbreindexall (db_name/db_id, type_bitmap)
DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])
DBCC dbtable [({‘dbname’ | dbid})]
DBCC debugbreak
DBCC deleteinstance (objectname, instancename)
DBCC des [( {‘dbname’ | dbid} [, {‘objname’ | objid} ])]
DBCC detachdb [( ‘dbname’ )]
DBCC dropcleanbuffers
DBCC dropextendedproc (function_name)
DBCC dropuserobject (‘object_name’)
DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number}
| {‘CLEAR’, exception_number})
DBCC errorlog
DBCC extentinfo [({‘database_name’| dbid | 0}
[,{‘table_name’ | table_id} [, {‘index_name’ | index_id | -1}]])]
DBCC fileheader [( {‘dbname’ | dbid} [, fileid])
DBCC fixallocation [({‘ADD’ | ‘REMOVE’},
{‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}
, filenum, pagenum [, objectid, indid])
DBCC flush (‘data’ | ‘log’, dbid)
DBCC flushprocindb (database)
DBCC free dll_name (FREE)
DBCC freeproccache
dbcc freeze_io (db)
dbcc getvalue (name)
dbcc icecapquery (‘dbname’, stored_proc_name
[, #_times_to_icecap (-1 infinite, 0 turns off)])
Use ‘dbcc icecapquery (printlist)’ to see list of SP’s to profile.
Use ‘dbcc icecapquery (icecapall)’ to profile all SP’s.
dbcc incrementinstance (objectname, countername, instancename, value)
dbcc ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )
DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})
DBCC inputbuffer (spid)
DBCC invalidate_textptr (textptr)
DBCC invalidate_textptr_objid (objid)
DBCC iotrace ( { ‘dbname’ | dbid | 0 | -1 }
, { fileid | 0 }, bufsize, [ { numIOs | -1 }
[, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )
DBCC latch ( address [, ‘owners’] [, ‘stackdumps’])
DBCC lock ([{‘DUMPTABLE’ | ‘DUMPSTATS’ | ‘RESETSTATS’ | ‘HASH’}] |
[{‘STALLREPORTTHESHOLD’, stallthreshold}])
DBCC lockobjectschema (‘object_name’)
DBCC log ([dbid[,{0|1|2|3|4}[,[‘lsn’,'[0x]x:y:z’]|[‘numrecs’,num]|[‘xdesid’,’x:y’] |[‘extent’,’x:y’]|[‘pageid’,’x:y’]|[‘objid’,{x,’y’}]|[‘logrecs’,
{‘lop’|op}…]|[‘output’,x,[‘filename’,’x’]]…]]])
DBCC loginfo [({‘database_name’ | dbid})]
DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})
DBCC memobjlist [(memory object)]
DBCC memorymap
DBCC memorystatus
DBCC memospy
DBCC memusage ([IDS | NAMES], [Number of rows to output])
DBCC monitorevents (‘sink’ [, ‘filter-expression’])
DBCC newalloc – please use checkalloc instead
DBCC no_textptr (table_id , max_inline)
DBCC opentran [({‘dbname’| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]
DBCC outputbuffer (spid)
DBCC page ( {‘dbname’ | dbid}, filenum, pagenum
[, printopt={0|1|2|3} ][, cache={0|1} ])
DBCC perflog
DBCC perfmon
DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}
, targetfile, targetpg, order={1|0})
DBCC pintable (database_id, table_id)
DBCC procbuf [({‘dbname’ | dbid}[, {‘objname’ | objid}
[, nbufs[, printopt = { 0 | 1 } ]]] )]
DBCC proccache
DBCC prtipage (dbid, objid, indexid [, [{{level, 0}
| {filenum, pagenum}}] [,printopt]])
DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]
DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid
, formatstr [, printopt = { 0 | 1} ])
DBCC rebuild_log (dbname [, filename])
DBCC renamecolumn (object_name, old_name, new_name)
DBCC resource
DBCC row_lock (dbid, tableid, set) – Not Needed
DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC setcpuweight (weight)
DBCC setinstance (objectname, countername, instancename, value)
DBCC setioweight (weight)
DBCC show_statistics (‘table_name’, ‘target_name’)
DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])
DBCC showdbaffinity
DBCC showfilestats [(file_num)]
DBCC showoffrules
DBCC showonrules
DBCC showtableaffinity (table)
DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid[,option]}})
DBCC showweights
DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage
[, {NOTRUNCATE | TRUNCATEONLY}]])
DBCC shrinkfile ({fileid | ‘filename’}, [compress_size
[, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
DBCC sqlmgrstats
DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
| {THREADS} | {LOGSPACE})
DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]
DBCC tab ( dbid, objid )
DBCC tape_control {‘query’ | ‘release’}[,(‘.tape’)]
DBCC tec [( uid[, spid[, ecid]] )]
DBCC textall [({‘database_name’|database_id}[, ‘FULL’ | FAST] )]
DBCC textalloc ({‘table_name’|table_id}[, ‘FULL’ | FAST])
DBCC thaw_io (db)
DBCC traceoff [( tracenum [, tracenum … ] )]
DBCC traceon [( tracenum [, tracenum … ] )]
DBCC tracestatus (trace# [, …trace#])
DBCC unpintable (dbid, table_id)
DBCC updateusage ({‘database_name’| 0} [, ‘table_name’ [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]
DBCC upgradedb (db) DBCC usagegovernor (command, value)
DBCC useplan [(number_of_plan)]
DBCC useroptions DBCC wakeup (spid)
DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL, Extreme Expert
PS C:\> Get-WindowsFeature RSAT-AD* PS C:\> Install-WindowsFeature RSAT-AD-PowerShell PS C:\> import-module ActiveDirectory PS C:\> Get-Command Get-ADComputer PS C:\> Get-ADComputer -Filter 'Name -like "*SQL*"'
To use Extended Events to display deadlock history, the following method can be used.
In the Object Explorer, Open Management folder
Extended Events to display deadlock history
Open Extended Events/Sessions/system_health
Right-click on package0.eventfile and select “View Target Data”. This will display a list of events.
To apply a filter, for example “deadlock”, select Filters toolbar button (may be hidden in a drop-down list)
Set the filter(s)
The results are displayed.
you are using Oracle Direct NFS, and the NFS is running on Windows, there is a potential issue with permissions and ownership changes required.
An example of issues with Oracle Direct NFS using datapump with the DATA_PUMP_DIR located on an NFS share server by NFS on Windows:
Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.625 MB Processing object type SCHEMA_EXPORT/USER ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [USER:"XYZ"] ORA-19505: failed to identify file "/u01/ORCL/ORCL_testexp.dp" ORA-17503: ksfdopn:4 Failed to open file /u01/ORCL/ORCL_testexp.dp ORA-17500: ODM err:File does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPW$WORKER", line 11014 ----- PL/SQL Call Stack ----- object line object handle number name
RMAN Backup Fail to NFS Shares From a Windows Server When DNFS is Enabled (Doc ID 2171297.1)
Datapump Dump File Permission In DNFS Environment (Doc ID 2049012.1)
Change the value of the following registry key (on the Windows Server providing NFS) to 0 and restart Server for NFS:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServerForNfs\CurrentVersion\Exports\0\RestrictChown = 0 (DWORD)
You could also disable Direct NFS:
set ORACLE environment.
cd $ORACLE_HOME/rdbms/lib/
make -f ins_rdbms.mk dnfs_off
— restart the instance