Oracle categorizing alert log error messages We can find oracle alert log error messages by querying in v$diag_alert_ext where message_text LIKE '%ORA-%'. I want to categorize the error messages like

OS errors
DB errors
Listener errors

Local Listener Errors
Scan Listener Errors

Is it possible to do so by querying in the v$diag_alert_ext table? What is the way to do that?

From the Database Reference I see that the view V$DIAG_ALERT_EXT contains a MESSAGE_GROUP column. Maybe this column contains such kind of information.
I am not aware of a view that contains such information. I searched for *us.msg files in the $ORACLE_HOME directories  and subdirectories of a 12.2 installation by executing find $ORACLE_HOME -name '*us.msg' and found the file $ORACLE_HOME/rdbms/mesg/oraus.msg that contains error messages in increasing order of error number and often containing a comment lines that describes the type of the error numbers in the following range. I extracted these comment lines. The output is shown in the following code block. There are more captions in comments that have a format thast will not ber recognized by this grep command.

 grep '^/ [0-9]'  $ORACLE_HOME/rdbms/mesg/oraus.msg
/ 10 - 49 user session and session switching errors
/ 70 .. 90 generic debugger errors
/ 91 .. 95 ksm large_pool_size and large_pool_min_alloc size errors
/ 101 .. 149 shared server errors
/ 150 .. 159 Oracle*XA errors
/ 160 .. 199 Distributed transaction errors (continued)
/ 200 .. 249 control file management errors
/ 250 .. 299 archival & media recovery errors
/ 300..369 - Redo File errors
/ 370-389 kcb external errors
/ 390-399: more Redo log errors
/ 400-420: KCK errors
/ 436-439: Oracle licensing ercs
/ 440-460 Detached process startup errors
/ 469-525 Detached process death errors (signaled in foreground)
/ 00526-00565
/ 00570-00599 SQL*Connect Opening & Reading Files
/ 00710 - 00719 reserved for tablespace rename feature
/ 1070 - 1099: V6 program interface errors
/ 1100 to 1279: db file errors (continue at 63000)
/ 1384 - 1396 Reserved for 4K block size log file errors
/ 1490 - 1499 Analyze Parse and Execution Errors
/ 1500 - 1699 V6 Execution errors
/ 2040 .. 2099 - Reserved for distributed update
/ 2100 .. 2139 - Reserved for precompiler runtime (SQLLIB)
/ 2140 .. 2499 - V6 parser errors continued
/ 6571-6579 Reserved for Plsql functions called from Sql
/ 6581-6591 Reserved for European OEM Ports (contact: egroenen.nl)
/ 6600-6699 Reserved for more SQL*Net errors
/ 6700-6899 Reserved for TLI SQL*NET driver errors
/ 6900-6999 Reserved for more SQL*Net errors
/ 06609 is not used by the LU6.2 Driver.
/ 6700-6899 Reserved for TLI SQL*NET driver errors
/ 6900-6999 Reserved for more SQL*Net errors
/ 7000-7099 Reserved for SQL*CONNECT/DB2 errors
/ 7100-7199 Reserved for SQL*CONNECT/IMS errors
/ 7200 - 7499 Reserved for UNIX OSD errors
/ 7500-7999 VMS OSD ERRORS
/ 7700 - 7720: sksarch errors
/ 7721-7729 scg overflow errors
/ 7740 - 7760 error message file errors
/ 7820-7839  SSP error messages
/ 7840-7859 SLLF error messages
/ 7880 - 7889 Error message for sd
/ 8000 - 8174  Data layer error messages
/ 8175 - 8190 Discrete transaction error messages
/ 8200 - 8399 Reserved for NCUBE OSD errors
/ 8270 - 8273 Reserved nCUBE archival
/ 8273 - reserved nCUBE archival
/ 8320 - 8329 Reserved for scn.c
/ 8340-8359 Reserved for nCUBE redo server errors
/ 8362 - 8399 Reserved for nCUBE sosd errors (unassigned)
/ 8400 UTL_RAW and UTL_PG error and warning messages
/ 8401 - 8499 Reserved for APPC Gateway
/ 8500 - 8599 Reserved for Protocol Procedural Gateway for
/ 8600 - 8699 Reserved for ??? sql*connnect errors
/ 8700 - 8799 Reserved for ??? sql*connnect errors
/ 8800 - 8999 Reserved for Tandem sql*connnect errors
/ 9000 - 9099 Reserved for TERADATA sql*connnect errors
/ 9100 - 9199 Reserved for file-based sql*connnect errors
/ 9200 - 9499 Reserved for OS/2 and MSDOS OSD errors
/ 9500 - 9699 Reserved for MPE/XL OSD errors
/ 9700 - 9999 Reserved for UNIX OSD errors
/ 09720-09739 reserved for Philips MPX P9x00
/ 09786-09825 reserved for Loader V2.0
/ 10630-10639 Reserved for Online Segment Shrink
/ 09858-09859 Reserved For NeXT port
/ 09860-09869 Reserved For Oracle Europe
/ 10302 - 10318 Reserved for internal triggers
/ 10325 - 10335 reserved for recovery testing
/ 10530 -10540 reserved for lightweight threads
/ 10552 -10589 reserved for recoverable media/standby recovery
/ 10710 - 10729 are Reserved for BITMAP row sources
/ 10730 - 10800 general debugging events
/ 10780 - 10785  Reserved for LogMiner
/ 10786 - 10788  Reserved for ks*
/ 10789 - 10790 Reserved for LogMiner
/ 10791 - 10799  Reserved for Logical Standby
/ 10810 - 10825 Reserved for OLTP
/ 10826 - 10829 Reserved for upgrade/downgrade
/ 10830 - 10838 Reserved for sort and aggregation row sources
/ 10841 - 10849 Reserved for OCI
/ 10850 - 10869 Reserved for AQ
/ 10870 - 10879  Reserved for recovery layer
/ 10880 - 10882  Reserved for Java Virtual Machine
/ 10900 - 10924  Reserved for SPACE management
/ 10901 - used in 7.3.4 for disabling extent trimming for merge
/ 10970 - 10974 Reserved for Lob Access
/ 10975 - 10995 Reserved for Replication
/ 10996 - 10998 Reserved for ks*
/ 10999 End Pseudo-error debugging events
/ 11000 - 11999 Reserved for mvs sql*net errors
/ 12000 - 12099 Reserved for table materialized view errors
/ 12100 - 12299 Reserved for Net8 errors
/ 12150 - 12195   Reserved for OSN (Not to be used by TNS)
/ 12196 - 12285   NR errors reportable by OSN (returned by nricall())
/ 12286 - 12499   ---(Reserved)---
/ 12500 - 12699   See net12500.msg
/ 12196 - 12285 NR errors reportable to OSN.
/ 12300 - 12399 Reserved for Multiple Mounts
/ 12150 - 12285   See net12150.msg
/ 12286 - 12499   ---(Reserved)---
/ 12500 - 12530   NSG errors reportable by OSN (returned in cndo).
/ 12531 - 12629   NS (and NT errors reported as NS errors) reportable by OSN
/ 12630 - 12699   NA errors (returned by native services)
/ 12500 - 12530 Reserved for NSG errors reportable to OSN.
/ 12531 - 12629 ns errors reportable to osn:
/ 12606 - 12629 ns secondary errors
/ 12630 - 12699 Reserved for NA errors.
/ 12700 - 12999: Reserved.
/ 12700 - 12750 Reserved for NLS
/ 12751 - 12799 reserved for VOS
/ 12800 - 12893 Reserved for Parallel Query/Index Creation
/ 12857 - 12893 unused (parallel query/index creation messages)
/ 12874 - 12893 unused currently for PQ
/ 12894 - 12898 are reserved for asmcmd setsparseparent error messages.
/ 12899 - Stole this one for new "value too large" message
/ 12920 - 12939 reserved for database/tablespace force logging mode messages
/ 12930 - 12939 Reserved for 12c Nologging Standby Project.
/ 12940 - 12949 Reserved for server side DBVerify
/ 12950 - 12960 Reserved for Space Management
/ 12980 - 12999 Reserved for Drop Column
/ 13000 - 13499   (Reserved for Oracle Spatial)
/ 13000 - 13199   Spatial Data Option and Spatial Cartridge 8.0
/ 13200 - 13499   Spatial Cartridge 8.1.3
/ 13391 - 13499 for all GeoRaster errors
/ 13500 - 13999 reserved for Server Manageability Layer
/ 13500 - 13599 reserved for AWR
/ 13600 - 13699 reserved for the adv fmwk.
/ 13700 - 13749 reserved for the ADDM, ASH
/ 13800 - 13809 reserved for the SQL Tuning Base
/ 13810 - 13824 reserved for DBOP Monitor
/ 13825 - 13849 reserved for SQL profiles
/ 13850 - 13899 reserved for end-to-end tracing
/ 13900 - 13949 reserved for threshold-based alert
/ 13950 - 13969 reserved for MMON infrastructure
/ 13970-13998 reserved for common reporting framework
/ 14000 - 14999 reserved for Partitioned object project
/ 14000 - 14099 reserved for parsing new syntax introduced by the Partitioned
/ 14150-14249 reserved for parsing errors which may be generated
/ 14201-14239 reserved for errors which may be generated during
/ 14250-14349 reserved for errors which may be generated during
/ 14400 - 14499 reserved for execution errors introduced by the Partitioned
/ 14450 - 14499 reserved for execution errors introduced by the
/ 14601 - 14700 reserved for 9.0.2 partitioning project
/ 14650 - 14689 reserved for reference-partitioning project
/ 14690 - 14699 reserved for 32k varchar project
/ 14750 - 14799 reserved for interval-partitioning project
/ 14800 - 14849 Reserved for composite list/* partitioning
/ 14850 - 14869 Reserved for autolist partitioning
/ 14870 - 14889 Reserved for interval subpartitioning
/ 14900 - 14949 Reserved for KJAC error messages
/ 14950 - 14999 Reserved for KJAC PL/SQL error messages
/ 15000 - 15499 Reserved for Automatic Storage Management
/ 59000 - 59999 Reserved for Automatic Storage Management (e57000.msg)
/ 15000 - 15099 Used for ASM run-time errors
/ 15100 - 15179 Used for ASM SQL usage errors
/ 15180 - 15188 Used for ASMLIB related error messages
/ 15189 - 15199 Used for ASM tracing and testing errors
/ 15200 - 15206 ASM messages added in 10.2
/ 15210 - 15227 ASM SQL parsing messages added in 11.1
/ 15228 - 15229 Used for ASM testing errors
/ 15230 - 15250 ASM runtime error messages added in 11.1
/ 15252 - 15280 Reserved for TrailBlazer
/ 15380 - 15399 ASM File Group error messages
/ 15400 - 15439 ASM appliance error messages
/ 15430 - 15439 ASM Flex Disk Group Quota Group Messages
/ 15440 - 15499 ASM Volume Messages
/ 59000 - 59999 Reserved for Automatic Storage Management (e57000.msg)
/ 15500 - 15999 Reserved for Server Manageability Layer
/ 15500 - 15599 reserved for Workload Capture and Replay (KEC)
/ 15600 - 15649 reserved for Automated Maintenance Tasks (KET)
/ 15650 - 15699 reserved for Direct Access to Fixed Tables
/ 16000 - 16499 Reserved for ORACLE HOT STANDBY
/ 16100 - 16130 Reserved for Logical Standby
/ 16200 - 16374 Reserved for Logical Standby
/ 16375 - 16499 Reserved for Physical Standby
/ 16500 - 16699 Reserved for Data Guard Broker
/ 16700 - 16899 reserved for Data Guard Broker RSM errors
/ 16900 - 16949 Reserved for Data Guard Broker
/ 17000 - 17499 Reserved for JDBC
/ 17500 - 18000 Reserved for I/O subsystem
/ 17675-18000 Reserved for OFS
/ 18000 - 18099 Reserved for PLAN STABILITY
/ 19391 - 19399 are reserved for autonfs.
/ 19400 - 19499 are reserved for KOT.
/ 19500 - 19998 Reserved for Server Managed Recovery
/ 19830 - 19835: reserved for SQL Engine Errors:
/ 19926 - 19929: reserved for transport database errors
/ 21100 - 21299 Reserved for CORE
/ 21300 - 22799 Reserved for OI/KO/KG (object Interface and Subsystem)
/ 22800 - 22999 Reserved for object SQL:
/ 23000 - 23299 Reserved for object Management Subsystem (OMS)
/ 23300 - 24299 Reserved for errors from DBMS* pl/sql packages
/ 20000 - 20999 Reserved for callers of the
/ 21100 - 21299 Reserved for CORE
/ 21300 - 22799 Reserved for OI/KO/KG (object Interface and Subsystem)
/ 22275 to 22279 reserved for KOLL - LOBs
/ 22280 to 22299 reserved for LOBs - LBS(KOLB), DBMS_LOB(KKXL) and BFILE(KOLF)
/ 22799 IS THE MAXIMUM ERROR NUMBER for ORI/KO (object Interface and Subsystem)
/ 22800 - 22999 Reserved for object SQL:
/ 22999 IS THE MAXIMUM ERROR NUMBER for object SQL
/ 23000 - 23299 Reserved for object Management Subsystem (OMS)
/ 23300 - 24299 Reserved for errors from DBMS* pl/sql pakages
/ 24280 -24290 Reserved for DBMS_LOB (FILE) package
/ 24291 - 24299 Reserved for Sorted Hash Clusters
/ 24300 - 24999 Reserved for OCI/UPI/TTC/OPI/RPI/PSDs etc
/ 24500 - 24549 Reserved for OCI codepoint length semantics and UTF16
/ 24550 - 24574 Reserved for OCI et al Diagnosability
/ 24750 - 24800 Reserved for Transaction OCI
/ 24800 - 24849 Reserved for OCI Lob/File related Calls
/ 24850 - 24874 Reserved for Shared OCI
/ 24900 - 24939 Reserved for OCI Notification
/ 24940 - 24949 Reserved for Generic Notification
/ 24950 - 24959 Reserved for PL/SQL Notification
/ 25474 is reserved for dumping info
/ 25475 is reserved for debug mode
/ 26500 - 26649 Reserved for Replication (KN)
/ 26650 - 26999 Reserved for Log Based Replication Replication (KN)
/ 27000-27299 Reserved for Oracle8 Unix OSDs
/ 27000 - 27030 keep for SKGFQ (Also 27190-27229)
/ 27031 - 27099 keep for SKGFR (if more needed, allocate another range later)
/ 27100 - 27139 keep for SKGM
/ 27140-27189 RESERVED FOR SKGP
/ 27190 - 27229 reserved skgfq
/ 27231 - 27249 reserved for sskgof
/ 27250 - 27269 reserved for sm
/ 27270 - 27274 reserved for sz
/ 27300 - 27350 reserved for slos Error Reporting
/ 27351 - 27499 RESERVED FOR NEW DATABASE SCHEDULER
/ 27401 - 27410 Reserved for scheduler events
/ 27411 - 27420 Reserved for scheduler repeat interval errors
/ 27431 - 27500 Scheduler API errors continuation
/ 27500-27599 RESERVED FOR IPC ERRORS
/ 27600-27699 RESERVED FOR EXADATA (SAGE) and HOST/CELL ERRORS
/ 27700-27800 RESERVED FOR MGA
/ 27800-27900 RESERVED FOR EVENT NOTIFICATION (KSEVN)
/ 28000-28499 RESERVED FOR SECURITY RELATED ERRORS
/ 28060-28099 RESERVED FOR RADM (Real-time Application-controlled Data Masking)
/ 28065 is reserved for RADM internal error.
/ 28100-28149 RESERVED FOR FINE-GRAINED SECURITY
/ 28150-28199 RESERVED FOR N-TIER SECURITY
/ 28200-28230 RESERVED FOR THE APPLICATION ROLE
/ 28221-28230 RESERVED FOR PASSWORD/ACCOUNT ENHANCEMENT
/ 28230-28260 RESERVED FOR THE OBFUSCATION TOOLKIT
/ 28261-28270 RESERVED FOR APPLICATION CONTEXT
/ 28270-28290 RESERVED FOR PASSWORD AUTHENTICATED GLOBAL USER(KZAL)
/ 28290 - 28300 RESERVED FOR THE Kerberos Enterprise User Security
/ 28300 - 28329 RESERVED FOR THE ENTERPRISE USER SECURITY
/ 28330-28499 RESERVED FOR TRANSPARENT DATA ENCRYPTION
/ 28500-28749 RESERVED FOR THE HETEROGENEOUS SERVICES
/ 28575 - 28600 reserved for untrusted and distributed external procedures
/ 28700-28799 RESERVED FOR CODE-BASED ACCESS CONTROL
/ 29250-29299 RESERVED FOR MORE PL/SQL AND UTILITY PACKAGE ERRORS
/ 29300 - 29399 Reserved for errors from DBMS* pl/sql pakages.
/ 29300 - 29354 RESERVED FOR THE DBMS_SQL PACKAGE
/ 29355 - 29399 RESERVED FOR DBMS RESOURCE MANAGER PACKAGES
/ 29400 - 29469 Reserved for Oracle8 Data Cartridges
/ 29470 - 29489 RESERVED FOR THE DBMS_SQL PACKAGE
/ 29490 - 29499 RESERVED FOR THE DBMS_PARALLEL_EXECUTE PACKAGE
/ 29500 - 29699 Reserved for DDL and other integration for
/ 29700-29799 reserved for Global Enqueue Service
/ 29780 -29789 Reserved for Gpnp Generic modules
/ 29800 - 29999 Reserved for Extensible Indexing project
/ 30000 - 30010 Reserved 8.1 miscellaneous features
/ 30051 - 30059 Reserved for row versions query feature
/ 30060 - 30069 Reserved for Transaction layer internal tracing
/ 30070 - 30099 Reserved for datetime/interval feature
/ 30100-30134 Reserved for OCIExtract Cartridge Services
/ 30300 - 30330 reserved for transaction recovery
/ 30665 - 30675 are reserved for 12g SQL enhancements projects
/ 32791 - 32799 are reserved for 12g SQL enhancements projects
/ 32800 - 32999 are reserved for Messaging Gateway (MGW)
/ 65535 -  (MAX ERROR NUMBER; NONE CAN BE LARGER)
/ 32800 - 32999 reserved for Oracle Messaging Gateway (MGW)
/ 40001 - 40020 RESERVED FOR BLAST
/ 40021 - 40040 RESERVED FOR GLM
/ 40101 - 40180 Generic Errors Across All Data Mining Algorithms
/ 40181 - 40200 RESERVED FOR DBMS_DATA_MINING_TRANSFORM
/ 40201 - 40250 RESERVED FOR DBMS_DATA_MINING
/ 40251 - 40260 RESERVED FOR SVM
/ 40261 - 40270 RESERVED FOR NMF
/ 40271 - 40280 RESERVED FOR ABN
/ 40281 - 40300 RESERVED FOR DMF
/ 40301 - 40320 RESERVED FOR DECISION TREE CLASSIFICATION AND REGRESSION
/ 40321 - 40340 RESERVED FOR CLUSTERING MODELS
/ 40341 - 40360 RESERVED FOR MODEL MAINTENANCE UPGRADE/DOWNGRADE MESSAGES
/ 40361 - 40380 RESERVED FOR SECURITY (PRIVILEGE/AUDIT) MESSAGES
/ 40381 - 40500 RESERVED FOR PMML IMPORT
/ 40401 - 40420 RESERVED FOR SVD
/ 40421 - 40440 RESERVED FOR UNSTRUCTURED TEXT
/ 40441 - 40500 RESERVED FOR JSON
/ 40501 - 40550 Reserved for Recovery
/ 40551 - 40600 Reserved for JSON
/ 40581-40619 JSON data guide related
/ 40620-40700 JSON related
/ 40651 - 40700 JSON related
/ 41095 is reserved for tuning
/ 41096 is reserved for tuning component state handling
/ 41097 is reserved for tuning migratability checks
/ 41098 is reserved for tracing
/ 41099 is reserved for debug mode
/ 48000 - 48099 are reserved for diagnosability framework errors
/ 48100 - 48179 are reserved for ADS portable layer
/ 48180 - 48199 are reserved for ADS OSD layer
/ 48200 - 48299 are reserved for AMS layer
/ 48300 - 48389 are reserved for AMI layer
/ 48390 - 48389 are reserved for ADM layer
/ 48400 - 48499 are reserved for ADV layer
/ 48500- 48599 are reserved for AME layer
/ 48600 - 48699 for AMH layer
/ 48800 - 48899 are reserved for ADV layer
/ 48900- 48999 are reserved for UTS layer
/ 51000 - 51999 are reserved for HM, IR diagnosability framework errors
/ 51000 - 51099 for HM infrastructure related
/ 51100 - 51189 for IR infrastructure related
/ 51190 - 51199 for DBMS_IR package errors
/ 51200 - 51399 for DATA corruption checks (physical, logical)
/ 51400 - 51499 for ASM checks
/ 51500 - 51699 for Check Name, Descriptions and Parameter Descriptions
/ 51700 - 51749 for Report Framework (dbgx)
/ 51257 is available to be used
/ 51700 - 51749 for Report Framework (dbgx)
/ 53000 - 53199 : Reserved for repository errors
/ 53200 - 53209 General Errors
/ 53210 - 53229 Source Checking Errors
/ 53230 - 53249 Destination Checking Errors
/ 53250 - 53269 Other Parameter Checking Errors
/ 57000 - 58999 are reserved for TimesTen IMDB errors
/ 15000 - 15499 Reserved for Automatic Storage Management [continued from]
/ 59000 - 59999 Reserved for Automatic Storage Management
/ 59000 - 59299 ASM run-time error messages
/ 59300 - 59399 ASM SQL usage error messages
/ 59400 - 59499 ASM tracing and testing error messages
/ 59500 - 59599 Flex ASM and Member Cluster error messages
/ 59600 - 59699 ASM Flex Disk Group (File Group, Quota Group) error messages
/ 59700 - 59749 ASM Extended Disk Group error messages
/ 59750 - 59799 ASM appliance (ODA, EXADATA) error messages
/ 59800 - 59819 ASMLIB related error messages
/ 59820 - 59849 ASM Volume Messages
/ 60000 - 60999 are reserved for SPACE MANAGEMENT
/ 61001 - 62000 are reserved for ADE Virtual Folder (RNFS)
/ 62001 - 62005 are reserved for parameter properties
/ 62006 - 62009 are reserved for XML and object replication
/ 62010 - 62029 are reserved for DB Unicode Migration Assistant Kernel-Side
/ 62031 - 62080 are reserved for Vector Processing (IMA)
/ 62081 - 62130 are reserved for Vector Encoding (DSBs)
/ 62500 - 62549 are reserved for PATTERN MATCHING
/ 62550 - 62554 are reserved for the SQL Row Limiting clause
/ 62555 - 62599 are reserved for Polymorphic Table Function
/ 63000 - 63999 are reserved for DATABASE FILE ERRORS (cont. from 1100-1279)
/ 64201 - 64299 are reserved for LOB errors
/ 64300 - 64349 are reserved for ARCHIVE COMPRESSION
/ 64350 - 64399 are reserved for In-Memory Columnar (IMC)
/ 64400 - 64599 are reserved for XDB errors
/ 64610 - 64619 are reserved for PL/SQL package UTL_CALL_STACK
/ 65000 - 65399 are reserved for CONSOLIDATED DATABASES ERRORS
/ 65400 - 64450 are reserved for CLUSTERING clause
/ 65455 - 65466 reserved for family errors
/ 65470- 65499 reserved for LOST WRITE PROTECTION
/ 65501- 65529 reserved for LOB layer errors
/ 65530 - 65534 reserved for INDEX layer errors


How can I learn to become a DBA? Question for you DBAs and such: How do you go about learning the skills to become a DBA without having the 'on hands' or on-the-job training?

My experience in DB work has been messing with MySQL via MyPHPAdmin or something similar. Not doing a ton of in-depth work.

Classes? SQL books?

Having been a DBA in a past life (and now in charge of hiring them), I have some thoughts:

How do you know you want to be a DBA without any experience admin-ing a database management system? That's like saying "How do I be an auto mechanic but the closest I've come to working on them is driving to and from work each day"... I'd take a long look at why you like database systems and see if it's on the production (SQL) or the support (DBA) end...

You should not be able to get a job as a production DBA without production, on-the-job experience as a DBA, so don't start applying for any DBA positions.

You may be able to be a junior DBA on a team if and only if you show some experience administrating DBMS's combined with lots of knowledge and/or certifications.

Your best bet is to start at a company as a SQL developer -- chances are there is no dedicated DBA and as you work more with it you can grow into a dedicated DBA.

Or, run a production multi-user application that uses a DBMS as the backend. Run your own website that uses MySQL and keep the DBMS running 24/7 and you'll get enough war stories to prove that you know what you're talking about. Hang out on user forms (such as DBForums) and provide support to other users for production DBMS issues.

Focus on backup/recovery, performance tuning, etc. and that'll be enough to at least get you in the door for a junior DBA position.

Good luck!


Why is removing the Identity property on a column not supported I have read that after SQL Server 2000, the ability to "un-identity" an identity column was removed.  And that this was "By Design" (not just a missing feature).

Here is an example I found on a blog. It involves updating the system tables.  (And that ability was removed after SQL Server 2000.) I get that doing this via system tables is not a good idea.  I am just wondering why a feature to do this another way is not around.

Working around this is going to cause me a considerable amount of work.  (Copying many hundreds of millions of rows to new tables in a downtime intolerant environment.)

So I thought I would ask "Why".

What changed in Sql Server 2005 and later versions that made this a bad thing?  Or was it always bad, and just not locked down?

What "Best Practice" (or similar principle) would be violated by making an identity column a normal column again?

--

Update to answer the request for "why I am doing this":
This is a very high level summary:  I am going to start adding partitions to my tables.  (So that I can archive/purge old data.)  That is all easy.  But I occasionally need to move a record to a different partition so it does not get removed (when a partition comes up for archival/deletion).  (I am having my partitioning column increase by 2 so that there is always space to move the row to a different partition.)

But if the partitioning column is an identity column, then I have to delete and re-insert the value (there is no way to update the value of an identity column).  Which causes issues with replication.

So I am wanting to use a sequence instead of an identity column.  But that switch is very very hard on large databases.

Your question is, essentially:

  Why can I no longer do this risky thing that I should never have been allowed to do in the first place?

The answer to that question is largely irrelevant (though you can see some Microsoft comments in these Connect items asking for this functionality: #294193 and #252226). For completeness, my synopsis is: The ability to remove the identity property was an unintended side effect of having the ability to mess with the system tables in the first place. This was not intended to be used in the many ways that it was, often with very bad consequences, and thus it was removed. It was an undocumented, unsupported, system table hack. The ability to change data in system tables wasn't removed because Microsoft no longer wanted you to hack your way out of a column being an identity column, it was removed because mucking with the system tables is extremely risky. Removing the IDENTITY property itself wasn't a specifically targeted feature removal, and I would have never fully trusted this approach even back in the ancient days when it was possible.

That said, how about we answer this question instead?

  How do I remove the IDENTITY property of a column with minimal or no downtime?

This you can do easily, using ALTER TABLE ... SWITCH, a technique I am certain I first learned from our own Paul White in the workarounds for Connect #252226. Quick example, given this simple table:

CREATE TABLE dbo.Original
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  name SYSNAME
);
GO

INSERT dbo.Original(name) VALUES(N'foo'),(N'bar');
GO

SELECT * FROM dbo.Original;
GO

Results:

ID  name
--  ----
1   foo
2   bar

Now, let's create a shadow table, and switch to it, then drop the old table, rename the new one, and then resume normal activity:

CREATE TABLE dbo.New
(
  ID INT PRIMARY KEY,
  name SYSNAME
);
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  ALTER TABLE dbo.Original SWITCH TO dbo.New;
  DROP TABLE dbo.Original;
  EXEC sys.sp_rename N'dbo.New', N'Original', 'OBJECT';
COMMIT TRANSACTION;
GO

INSERT dbo.Original(ID,name) VALUES(3,N'splunge');
UPDATE dbo.Original SET ID = 6 WHERE ID = 1;
GO

SELECT * FROM dbo.Original;
GO

Results:

ID  name
--  -------
2   bar
3   splunge
6   foo

Now clean up:

DROP TABLE dbo.Original;

This is a metadata operation only, with no data movement, and will only block other users while the metadata is being updated. But, admittedly, it is a very simplistic example. If you have foreign keys or are using other features like replication, Change Data Capture, Change Tracking, etc. you may need to disable or remove some of those before making this change (I haven't tested all combinations). For foreign keys specifically, see this tip which shows how to generate scripts to drop and re-create all (or selected) foreign key constraints.

Additionally, you will need to update your application code to not expect SQL Server to populate this column, and check any insert or select statements that may be depending on the order of columns or the columns they need to specify. Generally, I would grep your entire code base for any mention of this table.

Also see this script from Itzik Ben-Gan (source: this ancient article) for another way to handle this, but there is data movement involved here, so it doesn't deliver on the "no or minimal downtime" requirement.


upgrading sql server 2000 database to sql server 2012 Seeking some advice about an oudated database system we're working with.... hope this is an appropriate place to post this question.

I work with a city government arts program.  In 2004, we contracted with a local database developer to develop a system that allows us to track student participation in our after school art classes.  It tracks each student's address, demographics, what classes they enroll in, daily attendance, paperwork/documentation, class progress,  etc.   It was built on SQL Server 2000.

Because of issues with the city's technology systems back then we were not able to install it right away.  When they finally got around to supporting us (I think around 2006 or 07), they gave us SQL Server 2005.  We ended up installing an instance of SQL Server 2000 on SQL Server 2005 which itself runs on Windows Server 2003.

As of a few years ago, we are no longer under the city's technology systems and manage our own systems through independent contracted consultants.  Last year, we terminated our contract with the original database developer because he wasn't following through on a number of issues we needed addressed over the years.  Last year, we also went through a major overhaul of all our servers and desktops.

We've upgraded all  our other servers but we still have one server left that's still running SQL server 2000 and Windows 2003 just  because of this one outdated db system.  We'd like to upgrade both SQL Server to 2012 and Windows Server  to 2008R2 on this last server.

I'm trying to budget for how many hours it would take to upgrade a SQL Server 2000 database so it will run on SQL Server 2012. I realize you can't really tell much without actually seeing what we're working with, but all I'm looking for now is a ballpark figure (i.e., is it in the 20-30 hour range? 50 hours? 100 hours?).

If there is any other info I can provide to help get at an estimate feel free to post a comment....

Thanks for any help!

You can easily restore your 2000 DB to any higher version of SQL Server. The issue you may face is feature deprecation. If you aren't using any deprecated features, the actual DB migration is a non-event for the most part.

You can also use Profiler to trace for deprecated calls.


Synchronize 2 sql server 2019 database over intermittent connection (no replication) I'm a PostgreSQL DBA learning SQL Server 2019.
The setup :

1 Desktop, 1 Laptop
Both running SQL Server 2019 Developer
Windows 10 Home (nothing else, no active directory, nas, san, ...)
a database in simple recovery model, with partial containment enabled

The problem :

I want to be able to synchronize a database from my laptop to the desktop, or the other way around
I don't want to use replication (log shipping or others) since I may not connect both servers for a long time and my laptop is very limited in disk space. (I'm in simple recovery model for the same reasons)
both (desktop/laptop) database must be writable (so no primary/secondary)
if the database is modified on both side, I'll lose modification on one side. It's ok! I accept this.

The solution I know :

Create a SQL Server dump, send it to the desktop over shared network, apply the dump. or the other way around.

Is there a way to export a database to another database, on demand ? Or is the dump/transfer/restore the way to go ?  (that's what I would do with PostgreSQL)
I don't know all the SQL Server keywords and terminology and my google-fu was unsuccessful. Most solutions I find are obviously about how to set up replication over a reliable/permanent network. which doesn't fit my requirements.

Well... it turn out it couldn't be easier.
Right click on database -> Tasks -> Copy database...


Shortest method to upload database to server I have a SQL Server script file which ranges size of 3gb and I am in need to upload the script file to an external server.

i generated script for my 6gb database by following these steps

right-clicked the database-->tasks--->genrate scripts-->script entire database and objects and at scripting options i clicked advance -->type of data to script-->set it  as  schema and data and finished the data..

What are the shortest methods to upload faster into the server?

Personally, I'd do it using a compressed backup file.

Install an (Express will do) instance of SQL Server locally and run the script. Then, back it up and compress the backup file with a good utility (7-Zip/WinRAR compress very well and can create self-extracting archives). Transfer the compressed backup file (e.g. FTP), uncompress and restore the backup.

You can also compress just the 3GB textual script file and compare to see which is smaller to transfer.


Does Reporting Services lock tables? Possible Duplicate:
  Does SSRS lock the table when querying?

I'm using SQL Server 2008 R2 and Reporting Services. Do the reports block other queries or are they executed in highly isolated transactions, such as a snapshot?

Next obvious question: Can reporting services produce deadlocks or timeouts in other queries as a result of locking tables for a long time?

FYI, unless specifically directed, all TSQL statements are presumed Snap-Shots.

1st Question:

So Think of it as your SSRS is looking at your data as if it were a View/Select statement.

2nd Question:

It could, pending on the volume of calculation (SSRS) and volume of users.  Think you would have to have well over 10% CPU/RAM utilization from SSRS and over 50% CPU/RAM utilization from general queries in order for you to notice an effect, albeit hardly noticeable (like 1-10 nSecs).


What is the easiest way to move data from Oracle to SQL Server? One of our products supports both Oracle and SQL Server as database backend.  We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition for us.

What is the easiest way to get all the data from the entire Oracle Schema into the SQL Server database?

The schema only contains plain old tables and nothing fancy.  There might be one or two stored procedures that we'll have no problem migrating by hand.

I could use Oracle's SQLDeveloper to export the table data as CREATE and INSERT statements, but these won't match the Syntax used on SQL Server and I am not looking forward to having to manually fix the syntax errors.

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do:

SQL Server Migration Assistant for Oracle (documentation)
Microsoft SQL Server Migration Assistant v6.0 for Oracle (download)
SQL Server Migration Assistant (SSMA) Team's Blog

However in my case it was not as fast as I would have expected for a 80 GB Oracle-DB (4 hours or something) and I had to do some manual steps afterwards, but the application was developed in hell anyway (one table had 90+ columns and 100+ indices).


How do I truncate the transaction log in a SQL Server 2008 database? How do I truncate the transaction log in a SQL Server 2008 database?

What are possible best ways?

I tried this from a blog as follows:

  1) From the setting database to simple recovery, shrinking the file
  and once again setting in full recovery, you are in fact losing your
  valuable log data and will be not able to restore point in time. Not
  only that, you will also not be able to use subsequent log files.

  2) Shrinking database file or database adds fragmentation.

  There are a lot of things you can do. First, start taking proper log backup using
  the following command instead of truncating them and
  losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'
GO

  Remove the code of SHRINKING the file. If you are taking proper log
  backups, your log file usually (again usually, special cases are
  excluded) do not grow very big.

You could backup the log to the null device:

backup log [databasename] to disk = 'nul';

Or you could switch the recovery model to simple and then back to full/bulk again.


Right Join vs Left Join - My joins are broken and I don't understand why http://sqlfiddle.com/#!3/ef71e/1

I rather drastically cut down the fiddle, but I think the intent shines through. [Def] is a [DefaultClassification] table, and [Cls] is a [Classification] table that has some old records that need to be added in. The DefaultClassification table will be used in the future to spawn new groups of records into yet another table, and we're "unbreaking" an existing bit of logic/data with this process.

I did my best to distill the entire thing down to the barest essentials, but I've got another similar process that runs right after this one, so I'm looking to learn best practices and why this query is broken.

When I wrote this I intended it to run in a 2008+ environment, scripted or manually in SSMS. I don't know that it won't be scripted, but it might. Right now it's entirely run by hand. The rollback and select at the top above the print messages at the end are because it doesn't work right, so this lets me validate it before committing. I would prefer a valid script that I don't have to muck with, however

The specific issue I have is on line 62 of that revision one link http://sqlfiddle.com/#!3/ef71e/1 and it looks like this:

  RIGHT OUTER JOIN [Def] tcd

if I flip that RIGHT to a LEFT it does the insert but doesn't detect duplicates (to prevent insertion) and if I do the RIGHT it detects duplicates but doesn't handle insertions.

What did I break? And Why?

And I'm told the use of the @@ERROR was bad juju, but I don't know why, so bonus points (a 100 rep bounty) for that explanation too.

Why is @@ERROR bad juju?

In SQL Server 2005, using @@ERROR makes life harder than it needs to be for detecting and trapping errors.

1) You must test after every statement. Just from a maintenance perspective, my dog the goggles will do nothing to protect you. You can also argue that all the copy/paste logic for error handling will obscure the "true" purpose of your code.

2) @@ERROR is too volatile. In the following code, line 2 throws error 8134. Line 3 I select the value and 8134 is consumed. Why? Because the SELECT statement didn't raise an error. Even if it had, it would have been a different error than the one that failed.

DECLARE @ErrorCode int
SELECT 1/0 AS div_zero
SELECT @@error AS [@@error]
SET @errorCode = @@error
SELECT @errorCode AS errorCode

3) Even if you handle the @@ERROR well with the above 2, my challenge around it is that error still "comes through" at least in SSMS. Run the first half of the query and even though I have logically handled the error, it will still percolate back to the caller.

SET NOCOUNT ON

DECLARE @ErrorCode int;
SELECT 1/0 AS div_zero;
SET @errorCode = @@error;
IF @ErrorCode <> 0
BEGIN
    -- I did something here to handle the error
    -- SSMS still reports that the Query completed with errors
    print 'developer electrocuted';
END
GO

Contrast that with

-- error handled *and* query executes successfully
BEGIN TRY
    -- This will report query executed successfully
     SELECT 1/0 AS handled_div_zero;
END TRY
BEGIN CATCH
SELECT
    ERROR_NUMBER()AS error_number --returns the number of the error.
,   ERROR_SEVERITY() AS error_severity --returns the severity.
,   ERROR_STATE()AS error_state  --returns the error state number.
,   ERROR_PROCEDURE() AS error_procedure --returns the name of the stored procedure or trigger where the error occurred.
,   ERROR_LINE() AS error_line --returns the line number inside the routine that caused the error.
,   ERROR_MESSAGE() AS error_message; --returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
END CATCH

SQL Server 2012 finishes what 2005 started and provides THROW This allows you to handle your error in your CATCH block and then instead of smashing your call stack, you are now able to percolate that same error back up to the caller. Otherwise, you're reduced to having to raiserror each time you try to fix a level in the call stack.


What can I do to find out since which release a given DBMS supports a feature, function, etc? It is rather easy to find links describing a feature.
In most cases there is no hint in which release the feature was introduced.

Often the last resort is to google for the feature in combination with special releases and to compare the hit counts or something similar like old lists of new features etc.

Normally I would just google with some specific keywords. If you know the official website of the given DBMS, that could be even more helpful.

Example: To find out which version or release of Oracle the CTX_DOC.SNIPPET function comes from, just google: oracle new CTX_DOC SNIPPET function site:oracle.com .

Sometimes the last resort is probably the easiest way. You just need to fine-tune your search terms.


cross-database and cross-Availability Group queries and updates: which node do they go to with ApplicationIntent? This is a simplified view of the scenario we are considering:

Node1
  AG1
    Database A (master readwrite)
  AG2
    Database B (secondary readonly)
Node2
  AG1
    Database A (secondary readonly)
  AG2
    Database B (master readwrite)

This is for SQL Server 2016 SP2+. I believe the behavior regarding cross-AG
transactions is essentially the same in later editions, though in later editions
the DTC is always used.

Now suppose a client application connects to the AG2 listener with ApplicationIntent=ReadOnly and Database=B.
Then presumably they are connected to Node1.
Now suppose that the app does a select ... join between database A and B. Is Node1
(readwrite for AG1) used for the database A data, or Node2?
Similarly, suppose the client executes some update SQL against database A
on that "readonly" connection; will this succeed (since database A is readwrite
on that node) or not?

Also I presume the behavior is identical in this respect if the client app
just connects directly to Node1 without using the listener.

Is there any way in the sql itself to namespace the reference to a db to
force it to be from another node?

Are there additional performance costs to cross-AG transactions (in addition
to whether they are entirely serviced in the same node or not)?

Does it matter if what is actually done from the client app is to exec
a stored procedure (which might even be in another database C), and then
the body of that SP does cross-AG operations?

Thanks....

Goodness, you have a lot of questions in here. Here goes:

  Now suppose that the app does a select ... join between database A and
  B.

To be clear, let's say you use:

SELECT Stuff
FROM DatabaseB.dbo.Customers
JOIN DatabaseA.dbo.Sales

  Is Node1 (readwrite for AG1) used for the database A data, or Node2?

It would use Node1, the node you're connected to. It's just a plain ol' cross-database query.

  Similarly, suppose the client executes some update SQL against
  database A on that "readonly" connection; will this succeed (since
  database A is readwrite on that node) or not?

If you connected with "ApplicationIntent=ReadOnly", you shouldn't be trying to execute updates.

  Is there any way in the sql itself to namespace the reference to a db to force it to be from another node?

Sure, use views that point to linked servers, and the linked server name would be the AG listener name. That isn't necessarily performant, but you asked how you could do it from the SQL itself, and that would be one way.

  Does it matter if what is actually done from the client app is to exec a stored procedure (which might even be in another database C) […]

One thing to keep in mind is that availability groups are independent of each other and do not necessarily have the same replicas in their group. This means your AG1 might not have the same primary and secondary replicas as AG2 or AG3. Keeping this in mind, if you absolutely must do cross database queries then all of those databases should reside in the same availability group.

I'll stop here - your best bet for additional questions would be to break this question up into components and ask them individually. They could each have several great answers. Hope that helps get you started!


Oracle OEM Database Backup Failure I am trying to back up an oracle database from OEM, but upon completion the job report says the job failed with the following error:

RMAN-03002: failure of backup command at 07/09/2013 10:26:52
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file C:\APP\RM\FLASH_RECOVERY_AREA\RONNIE\ARCHIVELOG\2013_06_10\O1_MF_1_1508_8VDHNOLY_.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file

A DBA set this database up but he is not currently available. I have tried running a crosscheck and a delete expired in RMAN but experienced the same problem when trying again.

The path it is looking in: C:\APP\RM\FLASH_RECOVERY_AREA\RONNIE\ARCHIVELOG\2013_06_10\O1_MF_1_1508_8VDHNOLY_.ARC

...does not exist on the server, but it did exist on an original machine from which the DBA copied the database. The path containing the archive log now is:

C:\APP\CS\FLASH_RECOVERY_AREA\RONNIE\ARCHIVELOG\2013_06_10\O1_MF_1_1508_8VDHNOLY_.ARC

Can anyone help?

Thanks

The control file (or recovery catalog) holds information about the paths to the data files, online redo log files, archived redo log files, and other information RMAN uses during backup and recovery.

Whenever redo log switch occurs, Archiver (ARCn) process copies online redo logs to archive log location specified by LOG_ARCHIVE_DEST_n initialization parameter(s) and adds the appropriate record to the control file (or recovery catalog) which contains the path to the archived log, log sequence number and other related info.

Because the path has changed, you should either update information about existing archived redo log files and their paths in the RMAN recovery repository or ignore the existing archived redo log files and perform full database backup so that you can recover your database in case of media failure.

The first scenario can be accomplished with the following commands in RMAN:

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

Now you can add info about existing archived redo log files using CATALOG RMAN command:

RMAN> catalog archivelog '/ora/archivelog/O1_MF_1_1508_8VDHNOLY_.ARC';

Or you can use CATALOG START WITH to catalog multiple files specifying the path to the files:

RMAN> catalog with '/ora/backups/';

Set the LOG_ARCHIVE_DEST_n initialization parameter value correctly so that new archived redo log files are copied to the appropriate locations:

SQL> alter system set log_archive_dest_1 =
  'LOCATION=USE_DB_RECOVERY_FILE_DEST'
  scope = both;

Manually switch the redo log group to make sure ARCn copies the archived redo log files to the new location:

SQL> alter system switch logfile;

If the new archived redo log files are successfully copied to the destination you specified, you can backup the database including old archived redo log files and new ones in RMAN:

RMAN> backup database plus archivelog delete input;

In the second scenario, you perform the full backup of the database without including the archived redo log files from the now invalid path You just clean up existing records about archived redo log files in RMAN repository and make the full database backup including all subsequent archived redo log files copied to the valid location. The commands used are the same, you just omit the CATALOG command.


When will PostgreSQL execute archive_command to archive wal files? I use the archive_command below to archive WAL files. Suppose I have touched the file archive_active already. When will PostgreSQL execute the supplied archive_command to archive WAL files?

I know we can use the function pg_switch_xlog() to archive the WAL on demand, are there other trigger conditions?

PostgreSQL version: 9.3.0.

--postgresql.conf

archive_command = 'test ! -f /archive/pg93/archive_active || cp %p /archive/pg93/%f'
wal_level = hot_standby
archive_mode = on

--archive directory

[pg93@redhatB pg93]$ pwd
/archive/pg93

[pg93@redhatB pg93]$ ll
total 32M
-rw-------. 1 pg93 pg93 16M Oct 16 11:05 00000001000000000000007A
-rw-------. 1 pg93 pg93 16M Oct 16 11:07 00000001000000000000007B
-rw-rw-r--. 1 pg93 pg93   0 Oct 16 11:05 archive_active

The archive command is executed every time it switches the archive log to a new one.
Which as you say can be triggered manually by calling the pg_switch_xlog() function.

Other than that, an archive log needs to be changed to a new one when it is full, which by default is when it reaches 16MB, but can be changed at compile time.

You can also specify a timeout value using the parameter archive_timeout which will execute the command after the set amount of seconds, which is useful for databases that have low activity.

Update:

Version 10 introduced a change that renames everything referencing "xlog" to "wal", so pg_switch_xlog() is now called pg_switch_wal()


Value of columns inOracle tables I have many schemas in one oracle databse named SCHEMA1, SCHEMA2...SCHEMA999

I want to  find specific value of column named "JN_OSUSER" which is string 'Ed.Spencer'

How to find in every table, in my wholedatabase, column value which contain string value 'Ed.Spencer'?

You can generate an SQL script with sqlplus and run that.

spool /tmp/get_the_ed.sql
select 'select ''' || owner || ''', ID from ' || owner
       || '.' || table_name || ' where jn_osuser = ''Ed Spencer'';'
  from all_tab_columns where column_name = 'JN_OSUSER';
spool off
start /tmp/get_the_ed

You need to replace "ID" by the primary key of those tables.  If that isn't always the same, you can go for rowid.


compouned (composite) unique keys I was looking in this page but also on stackoverflow and google because I though this may be a quite common question but could not find anything related... I may not be using the right terminology here.

The question is quite straight forward.

Is it a good practice having nested unique keys (or mix of primary and unique keys) inside another unique key?

example:

I have a table with columns A, B, C, D, E, F and G
Primary or UNIQUE Key would be (A, B) and UNIQUE KEYS (A, B, C) and (A, B, D).

I would like to know whether or not this is kind of things are a good practice in general but also for mysql.

Thank you very much

Every table should have a PRIMARY KEY.  In MySQL this implies two things: a UNIQUEness constraint, and an index.
Any kind of index can be "composite" ("compound"), that is be composed of more than one column.
It is rarely useful to have two UNIQUE keys in a single table.  (Remember: PRIMARY counts as UNIQUE.
A common exception is when you are 'normalizing' a long string and 'mapping' it to a short INT.
If you already have a unique key (a), it is unreasonable and unnecessary to have also have UNIQUE(a,b) or UNIQUE(b,a).  On the other hand, it may be useful to have a non-unique INDEX(a,b) and/or INDEX(b,a).  Think how the uniqueness of (a) implies the uniqueness of the other two.
With INDEX(a,b), there is virtually no reason to also have INDEX(a)  (Note:  I am not talking about UNIQUE in this bullet item.)

If I have not addressed your specific question (perhaps indirectly), please rephrase your question.


SSIS Package fails to "Transform File and create output file" I have an SSIS (2012) package which was written by one of the developers.  I've been tasked to with fixing the error. Each time the package is run I receive the following three messages indicating the Write Output File [339] produced errors.

Transform File and create output file:Error: Write Output File failed the pre-execute phase and returned error code 0xC020200E

Transform File and create output file:Error: Cannot open the datafile "\\servershare\folder\file"

Transform File and create output file:Warning: Access is denied.

I have tried granting full control to the folder share to myself, the SQL Agent account, and the SQL Services account. Whether I run the package as myself or as a service account I receive the error messages.

I'm not seeing any more details in the SQL Server error logs or the Windows Event logs.

This ended up being a Kerberos problem.  We were able to identify through some of the security logs and some network monitoring, that the request was coming in as anonymous.  Once we applied the relevant SPNs for MSSQLSVC for the account that was being used to kick off the process, it worked.


Why `distinct` and `count` operations generate `"op": "command"` in system.profile According to documentation count and distinct queries should result in op = count and op = distinct in system.profile. However when I tried to run such queries in shell both were generated as op = command.

> db.p.count()
23
> db.p.distinct("item")
[ "pencil", "eraser" ]
> db.system.profile.find().sort({$natural: -1}).limit(2).pretty()
{
    "op" : "command",
    "ns" : "test.p",
    "command" : {
        "distinct" : "p",
        "key" : "item",
        "query" : {

        }
    },
    (...)
}
{
    "op" : "command",
    "ns" : "test.p",
    "command" : {
        "count" : "p",
        "query" : {

        },
        "fields" : {

        }
    },
    (...)
}

For remove this works perfectly fine, I execute remove and I get op = remove in system.profile.

> db.p.remove({item: "pen"})
WriteResult({ "nRemoved" : 0 })
> db.system.profile.find().sort({$natural: -1}).limit(1).pretty()
{
    "op" : "remove",
    "ns" : "test.p",
    "query" : {
        "item" : "pen"
    },
    (...)
}

Originally, all database operation in MongoDB are encoded in the Wire Protocol. However, this would mean that creating a new command would involve:

Creating a new opcode in the wire protocol, and
Bumping the wire protocol version.

Another disadvantage with this approach is that the format of the opcodes cannot change without changing the wire protocol version. As more features are introduced, this situation will become unavoidable and potentially occur more often in the future.

To overcome this limitation, newer version of MongoDB started to migrate the existing wire protocol functions into its command form. For example, the command forms of the CRUD operations are detailed in the Query and Write Operation Commands documentation page.

This is the main reason why some operations are recorded as commands, and some are recorded as the actual wire protocol codes. Eventually, the command forms will be the preferred form in the future.

There is no single generic ticket that describes this move, but the DOCS-7489 ticket kind of describes the process for the drivers.

For a more in-depth technical details on upcoming driver changes with regard to the wire protocol, please see New Driver Features for MongoDB 3.6, specifically the section about OP_MSG.


Why EXPLAIN doesn't show heap fetches for index scan I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output.

Test environment

-- function to fill test table
CREATE OR REPLACE FUNCTION fillTable (n INTEGER)
   RETURNS INTEGER AS $rowsCount$
DECLARE
   counter INTEGER := 0 ;
BEGIN

   IF (n < 1) THEN
      RETURN 0 ;
   END IF;

   LOOP
      EXIT WHEN counter = n ;
      counter := counter + 1 ;
      insert into key_value_test(key, value) VALUES (counter,counter);
   END LOOP ;

   return counter;

END ;
$rowsCount$
LANGUAGE plpgsql;

Test case for simple b-tree index

drop table key_value_test;
create table key_value_test
(
    key bigint not null,
    value integer not null,
    test VARCHAR(100),
    CONSTRAINT pk_key_value_test
    PRIMARY KEY(key)
);

SELECT  fillTable(1000000);

vacuum;

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

Test run results:

Index Scan using pk_key_value_test on key_value_test  (cost=0.42..8.44 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (key = 913680)
Buffers: shared hit=4
Planning Time: 0.062 ms
Execution Time: 0.026 ms

Test case for covering b-tree index

drop table if exists key_value_test;
create table key_value_test
(
    key bigint not null,
    value integer not null,
    CONSTRAINT pk_key_value_test
      PRIMARY KEY(key) include(value)
);
SELECT  fillTable(1000000);

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

vacuum;

EXPLAIN( ANALYZE,BUFFERS ) SELECT  key, value
FROM key_value_test
WHERE  key = 740080;

Test run results

Before vacuum:

Index Only Scan using pk_key_value_test on key_value_test  (cost=0.43..8.45 rows=1 width=12) (actual time=0.127..0.128 rows=1 loops=1)
  Index Cond: (key = 740080)
  Heap Fetches: 1
  Buffers: shared hit=4
Planning Time: 0.078 ms
Execution Time: 0.146 ms

After vacuum:

Index Only Scan using pk_key_value_test on key_value_test  (cost=0.42..4.44 rows=1 width=12) (actual time=0.143..0.144 rows=1 loops=1)
  Index Cond: (key = 740080)
  Heap Fetches: 0
  Buffers: shared hit=4
Planning Time: 0.273 ms
Execution Time: 0.156 ms

My qiestion is why Heap Fetches shows only for index-only scans, but for index scans it information is hidden?
Now i see that i has same request count to shared buffers for simple index and covering index. So where is perfomance
impact for covering indexes in this situation? It realy strange, that i can't measure potential perfomance impact using EXPLAIN.

Index scans fetch from the heap for every single row.  That is what makes it not be an index-only scan.  It only makes sense to display the count for index-only scans, as that is the only case in which it is informative.

The line "Buffers:" line may be more informative in general (for a realistic case where you have more than one row at stake).  But for your IOS that line shows 4 in both cases.  The reason that that doesn't change is that before vacuuming, the table doesn't have a visibility map, so there is no page to be read into a buffer.  It realizes the vm doesn't exist, an hits the table page instead.  After vacuuming, it hits the (now existing) vm page, which tells us doesn't need to hit the table.  So you always get 4 Buffers hit, 3 for the index and 1 for either the vm or the table.

If you were to partially populate the table, then vacuum so that the vm exists, then insert another record, and then query for that record, you would see that the IOS now hits 5 buffers.  3 for the index, one for the visibility map page which says the page is not all visible, and 1 for the table.

If you have large queries which hits the index many times, then interpreting the buffer hit count gets really tricky.  Within a single query execution, it keeps a pin on the most recently used visibility map page.  Hitting the same page repeatedly while you still hold a pin on it doesn't count as a new buffer hit.  Only if you need to switch to a different page in the vm does it count as a new buffer hit.

All of this unlikely to be very effective.  Make up a realistic data set of a realistic size on realistic hardware, and run realistic queries against it, and measure actual performance.  Once you have legitimate performance differences which need explaining, you can dig into the details to find the explanation.  But trying to extrapolate up from a one-row query is not very useful.


How to write SQL queries to populate the tables while synchronizing transactions and account balance? I have two tables having the following schema.

CREATE TABLE users(
  `id` INT AUTO_INCREMENT,
  `balance` DECIMAL(10, 2),
  PRIMARY KEY(`id`)
);
CREATE TABLE user_transactions(
  `id` INT AUTO_INCREMENT,
  `user_id` INT,
  `previous_balance` DECIMAL(10,2) COMMENT 'Balance before transaction',
  `amount` DECIMAL(10,2) COMMENT 'It will be either positive or negative and added to the previous balance',
  `updated_balance` DECIMAL(10, 2) COMMENT 'Balance after transaction',
  `created` BIGINT,
  PRIMARY KEY(`id`)
);

I am using MySQL version 5.7. That's a minimal DB structure and indicates all the indexes going to be used. There will be more columns in both tables such as who carried out the transaction etc. I have to keep a record of the transaction in the user_transactions table and update the balance in the users table.

The transactions can be carried out by the user, the parent user, and the system. Therefore, I have to query the balance every few seconds and show it to the user. There will be no delete operations on both tables. Just insert and update on the users table, and only insert on the user_transactions table. Since there's going to be thousands of users and thousands of transactions every day, most probably at the same time, these insert and update queries should be optimized.

There are three approaches I could think of:

Run three queries in a stored procedure as follows.

CREATE PROCEDURE `update_balance` (user_id INT, amount DECIMAL(10,2))
BEGIN
    DECLARE previous_balance DECIMAL(10,2);
    -- fetch previous balance
    SELECT balance INTO previous_balance FROM users WHERE id = user_id;
    -- create an entry in user_transactions table
    INSERT INTO user_transactions(user_id, previous_balance, amount, updated_balance, created) VALUES (user_id, previous_balance, amount, previous_balance+amount, UNIX_TIMESTAMP());
    -- updating the balance in users table
    UPDATE users SET balance = previous_balance+amount WHERE id = user_id;
END

Create a trigger after updating the balance in the users table.
Don't use the balance column in the users table at all and fetch balance from the user_transactions table instead.

1st and 2nd approach will lock the row and prevent any update. It won't be feasible if there is another column in the users table being updated. 3rd approach appears not feasible to me as there's going to be a million records at some point.

This question points at writing schema. While I already have the schema, I need to write an optimized query(or queries) to populate the tables.

So, how to write queries to update balance while keeping every record of the transaction and the balance synced?

Most financial systems of record are very complex. They involve separate concepts of "posting" transactions, then "settling" transactions afterwards, as well as "closing" of financial periods, after which settled transactions can't be modified.

Say you walk into your bank and withdraw $100. The banker posts that transaction in front of you, but it isn't settled until later (often overnight). If the banker accidently typed in the wrong withdrawal amount, they can adjust the transaction and that corrected transaction will appear on your statement as a single -$100 withdrawal. If the error isn't noticed until after the transaction is settled, the correction is posted as a separate transaction and your statement will show the incorrect transaction + correction = net correct amount. Most banks close each day as part of settling standard bank accounts, making the two fairly similar. Credit cards generally close months (which is when they often compute fees, send statements, etc.)

One reason for the complexity is the concerns for concurrency and isolation on these methods. Consider the race condition on option 1:

two users try to execute transactions on the same user at the same time.
The two SELECT statements run in parallel to get balance (say, $500).
Then they both insert transactions (again in parallel) of -100.
Both will calculate the new balance of $500-$100=400. They'll block each other on the update, but the race condition will make the balance incorrect because they selected the starting balance without knowledge of the prior transaction.

So... That's my way of saying this is actually extremely complex if you care about being correct. Assuming accuracy matters, you might consider an alternate approach. Real life will be a bit more complicated to implement than this, but this is a high level approach :
- post transactions as normal, but don't update the balance.
- asynchronously, "close" a time period, at which time you process all transactions during that period to compute the new balance & cache that on the user, along with the time it was computed.
 - when you need a balance, look up the cached balance, and all transactions since it was computed. Return that balance to the user.


Cannot login to Oracle11g with System username I just installed Oracle 11g into my machine.
I was about to shut down a database instance, and Oracle asked me for username and password, I provided the credentials I used to login into the web system, and it said that the credentials were wrong.

I googled how to change credentials, so I logged in into SQL Plus and did the following:

   ALTER USER sys IDENTIFIED BY "my_new_password";

then I tried to shut down the database instance logging in with the user sys and the new password and oracle still complaint about wrong credentials. Then I logged off, and when I tried to log in with my username and password they didn't work. I tried to login with sys and the new password and it didn't work, and now I completely out of the database management web site, and I can't do anything.

I do not know where to go from here, I'm working in a Windows 7 Machine.

You mentioned you were on a Windows machine and a brand new DB.  A problem I've run into is the ORA_DBA group.  Windows has a habit of not allowing you to log into an Oracle DB with high level credentials if your OS account is not in the ORA_DBA group.  We found this problem when we migrated to Windows 2008.  I have no experience with Windows 7, but feel like its a safe assumption that it has a security setup similar to 2008, and this may be your problem.


How to recover from two queries stuck in kill state causing all the database to not respond? During a INSERT INTO new_table (f1, f2, ..., fn) (SELECT f1, f2, ..., fn FROM table) I clicked the new_table in php-myadmin which ran a simple SELECT * FROM new_table LIMIT 0, 30 which ofcourse couldn't be completed because new_table was not ready.

I tried Killing the query, but it just kept itself in Kill state and therefore I assume it caused some sort of rollback in new_table.

The copy data query is for a 200GB table and was running for about 10hours till this interruption (I think it was almost finished). I tried to also kill that query thinking it will free things up, but again it got stuck.

I'm seeing this in SHOW ENGINE INNODB STATS

    ---TRANSACTION 5254EC173, ACTIVE 39375 sec rollback
mysql tables in use 2, locked 2
ROLLING BACK 4169557 lock struct(s), heap size 371276216, 34345786 row lock(s), undo log entries 26939759

Is there anything I can do? Any time estimation? Will it try to rollback the whole table and then try to return that whole data? Will it try to use diskspace for all these operations? Is there anything to avoid the lock? would it be a good idea to do mysql restart or will this just start the operation over again?

Please seem my answer to your other similar question involving innodb_force_recovery = 4 https://dba.stackexchange.com/a/53355/30545


Mapping logins to linked servers for migration My team is migrating a SQL 2012 server with an extensive list of linked servers to SQL 2016. My task is to script the creation of linked servers on the new server.
It looks like I have all the information I need to create the script in sys.servers but am having trouble finding where local logins are mapped to the linked servers.

You'll find the linked servers login mappings in sys.linked_logins view.
This, in turn accesses the master.sys.syslnklgns table (in case this gives something useful), but you can only access the real systems tables when you connect using the Dedicated Admin Connection (/A with sqlcmd.exe).


Are foreign keys better than indexes? I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application database, I know that the referential integrity is in check.

What is the impact of primary key and foreign key relationships in this copy? If I carefully set up the indexing I require, is there any benefit to foreign keys?  Any detriment?

Foreign Key Constraints and Indexes are two completely separate entities within a database.  As you have explicitly stated in your question, foreign key relationships are to ensure Referential Integrity.  If your database copy is soley for OLAP that wouldn't really come into play (as long as you aren't doing any DML batches against it).

The indexes are what are going to impact your query performance.  And if the above is true (no INSERT, UPDATE, or DELETE), you shouldn't be too concerned with the overhead of index maintenance.

Because of this, you can wisely choose indexes in order to maximize performance.  But without seeing more details on your data, and what already exists, generalized ideas are all that can be given to you.

EDIT:  As per Grant Fritchey's blog post, there can be a performance gain due to foreign key constraints, as the query optimizer can omit certain unnecessary operations due to the referential aspects of the constraint.  For more information, please see Grant's blog post: Do Foreign Key Constraints Help Performance?


ORA-00984: column not allowed here I am running Oracle 10g. I am writing a script (a delta script over a created database) that will be executed in SQL*Plus. It is needed to fix a bug.

I need to know if a row exist in a table, and if it does not, insert it. So I start like:

BEGIN
    DECLARE
        COL_EXIST NUMBER(10);
    BEGIN
        -- Reset ET_SEVERITY_LEVEL
        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 5
        WHERE CODE='FATAL';

        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 4
        WHERE CODE='ERROR';

        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 3
        WHERE CODE='WARNING';

        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 2
        WHERE CODE='INFO';

        -- Insert ET_SEVERITY_LEVEL register DEBUG
        SELECT COUNT(*) INTO COL_EXIST
        FROM ET_SEVERITY_LEVEL
        WHERE CODE like 'DEBUG';

        IF COL_EXIST = 0 THEN
            EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, "DEBUG", "Debugging")';
        END IF;
    END;
END;
/

But I get the following error:

ORA-00984: column not allowed here
ORA-06512: in line 30
00984. 00000 -  "column not allowed here"
*Cause:
*Action:

How could I do this? Is there a way to write a script to query tables and DML at the same time?

You need to use escaped single quotes in your EXECUTE IMMEDIATE statement, like so:

EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, ''DEBUG'', ''Debugging'')';


How to restore/create linked server logins from one server to another Here is the situation I am in. I have backup files for databases that I am using to restore them on a test server(B). The issue that I am having is that linked server logins appear to be on the master server level and I am unable to use those through a database's backup file.

I create these backup files from another server(A) and store them in a remote location. Is there a way to create a file or something with all the linked server logins from server A and use that file to restore them somehow when I use backup files to create Databases on Server B?

You can use Copy-DbaLinkedServer from dbatools to migrate linked servers.

For login migration, you can use copy-dbalogin

Just install dbatools and you will get all the cmdlets.

Also, see my answer if you want to dump the scripted output into file.


Storing whether a user is a student or teacher? Is a lookup table necessary? Users can be either student or teachers, and are able to do different things depending on which role they are. Can I just use role with a 0 or 1? I usually see lookup tables which map a user to a role to permissions and whatnot, but would that be overkill for this scenario?

user
---
id      1
name    John
role    0

id      2
name    Jane
role    1

or

user
---
id      1
name    John
role    1

id      2
name    Jane
role    2

role
---
id      1
type    Student

id      2
type    Teacher

id      3
type    Principal

Your sample data suggests a User can be a Principal, so 0 and 1 won't work.  IF a User can only be 'a student' OR 'a teacher' OR 'a Principal' OR (a Janitor, a Librarian, etc.), this looks like a simple enumeration pattern and your second example would be the best approach.  Make sure your User table has a foreign key defined that points to the Role table to ensure that only valid Roles are used

Create Table dbo.Role (ID INT, RoleName varchar(30)
CONSTRAINT [PK_Role] PRIMARY KEY ([Id]))
Insert into dbo.Role (ID,RoleName)
values(1,'Student'),(2,'Teacher'),(3,'Principal')

Create Table [User] (ID INT, UserName varchar(30), [Role] int
CONSTRAINT [PK_User] PRIMARY KEY ([Id])
CONSTRAINT [FK_User_Role] FOREIGN KEY ([Role]) REFERENCES [Role] ([Id])
)

--Works
Insert into dbo.[User] (ID,UserName,Role) values(1,'John',1),(2,'Teacher',2)

--Fails due to Role type 4 not in Role enum table
Insert into dbo.[User] (ID,UserName,Role) values(3,'Sam',4)


Source of Entry in Postgres I want to make a small web app using Postgres as database.

Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?

Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?

No.

Postgres doesn't care which application the query came from.

It does, however, care a great deal about the machine from which the query was issued and under which account.

The first is managed by Host-Based Authentication (HBA), by adding entries into pg_hba.conf.  As long as your application server is configured in there, it stands a chance of getting in.  For example:

#     Database Account   Address         Auth  (Comment)
host  db1      app_user  11.22.33.44/32  md5   # server44

Then, your application will need a database account with which to connect.

Create a Postgres "User" (a.k.a. "login Role") for your application to use and only use those credentials within your application.  Use a separate Role to grant privileges on database objects (e.g. tables) and grant that Role to your application User:

create table table1 ... ;

# Application permissions
create app_role ;
grant select, insert, update, delete on table1 to app_role ;

# Developer permissions
create dev_role ;
grant select on table1 to dev_role ;

# Application account
create user app_user with password '...' valid until 'infinity' ;
grant app_role to app_user ;

# A Developer account
create user fred with password '...' ;
grant dev_role to fred ;

Of course, fred's Development machine will also have to appear in pg_hba.conf!


Partitioning in MySql 5.7 with Primary Key and Unique Key I am trying to create a hash partition in MySQL 5.7 AWS RDS, cause the table is growing much rapidly approximately with 200 Million Rows in a month.
The table structure is like this and tried to create the partitions like below
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime DEFAULT NULL,
  used_time datetime DEFAULT NULL,
  ......
  ......
  ......
  ......
  PRIMARY KEY (itemid),
  UNIQUE KEY owner_product_uidx (ownerid,product_id),
  KEY prod_indx (product_id)
)
PARTITION BY HASH(itemid,ownerid,product_id)
 PARTITIONS 5;
ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

throws error
Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function.
Tried Partition KEY instead of HASH with
PARTITION BY key(item_id,owner_id,product_id,creation_time;

Still with no luck. Did anyone succeed creating the partition,
Would also be fine i can do it with year(creation_time)-month(creation_time) as partition key.
like (2021-01,2021-02....etc)
Ref: https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html
Can someone help.

Partitions have a lot of restrictions
You can'_t use HASH with multiple columns
If you want multiple  columns they have to be in the primary key, like shown in the example.
For your time you see in the second example. you have to make even more sacrifices and if that is what you seek, you have to test it
.

CREATE TABLE abc
 (
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime DEFAULT NULL,
  yearmonth VARCHAR(20) GENERATED ALWAYS AS (CONCAT(YEAR(creation_time),'-',MONTH(creation_time))),
  used_time datetime DEFAULT NULL,
  PRIMARY KEY (itemid,ownerid,product_id),
  UNIQUE KEY owner_product_uidx (itemid,ownerid,product_id),
  KEY prod_indx (product_id)
) ENGINE=INNODB
PARTITION BY KEY(itemid,ownerid,product_id)
 PARTITIONS 5;

✓

CREATE TABLE abc2
 (
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime NOT NULL,
  month_ tinyint NOT NULL,
  used_time datetime DEFAULT NULL,
  PRIMARY KEY (itemid,creation_time,month_),
  UNIQUE KEY owner_product_uidx (itemid,creation_time,month_),
  KEY prod_indx (product_id)
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(`creation_time`))
SUBPARTITION BY KEY (month_)
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

✓

db<>fiddle here


Disabling DBMS_SCHEDULER queue Despite a lot of searching, I can't seem to find a solution to this problem we are having.

Our system enables users to submit certain processes to be run in background while they continue working. These processes are submitted as jobs using DBMS_SCHEDULER and we have a couple of job classes for serial and parallel processing depending on the job the user needs doing.

When we take the system down for maintenance, we stop running jobs and reenter them on the queue, and disable all scheduled jobs. This is fine for a standard maintenance window as without our system online, users have no ability to submit new jobs. After maintenance, all jobs are re-enabled and everyone's happy.

However, there are times when we would like to be able to disable the queue while the system is still live - often to free up some resources in peak times. The only thing we can do at the moment is disable all jobs currently scheduled, but obviously that doesn't stop people submitting new jobs to the queue.

Does anyone know of a way to either

stop people entering jobs on the queue
force any new jobs to be entered on the queue in a disabled state (preferred option)?

Many thanks in advance

I can't test it right now but I think you could do this with open/close scheduler window:

first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.
modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.


How to auto start oracle listener on Linux with machine restart? I've a Linux VM with Oracle database serve and it gets restarted periodically and hence next time, oracle listener is not running. So I've to start it manually with following commands -
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl;
$ORACLE_HOME/bin/lsnrctl start LISTENER

sqlplus
sys as sysdba/password
startup

This activity I've to do every time machine is restarted.
Is there a way with which oracle listener is started along with machine start? i.e. something like windows services which gets automatically started with machine start.

This question has been asked and answered hundreds, if not thousands, of times. A simple google search turns up dozens of hits. When in doubt, refer to the official oracle docs: https://docs.oracle.com/database/121/UNXAR/strt_stp.htm#UNXAR140
As an aside, there is no reason for you to manually enter all those commands to set the oracle environment.  That is what the 'oraenv' utility is provided for.  First, as a one-time setup, make sure your database is properly listed in /etc/oratab.  Creating the database with dbca should have done this for you. Mine looks like this (the 'cat' command types the file, piping the output to 'grep -v' filters out the comment lines.
[oracle@vbol83-02 dba]$ cat /etc/oratab | grep -v ^#

cdb:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@vbol83-02 dba]$

The format of the entry is
oraclesid:oraclehome:Y|N

Now before invoking oraenv, let's check our enviornment:
[oracle@vbol83-02 dba]$ env|grep ORA
[oracle@vbol83-02 dba]$ echo $PATH
/usr/local/bin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
[oracle@vbol83-02 dba]$

The 'env' command shows all environment variables. Piping the output to 'grep' allows me to show only those that contain the string 'ORA'.  And you see I have none. Also, you see that $ORACLE_HOME/bin is not in my PATH.
Now envoke oraenv. When prompted, enter the value of ORACLE_SID, as listed in /etc/oratab
[oracle@vbol83-02 dba]$ source oraenv
ORACLE_SID = [oracle] ? cdb
The Oracle base has been set to /u01/app/oracle
[oracle@vbol83-02 dba]$

Now let's check the environment again:
[oracle@vbol83-02 dba]$ env|grep ORA
ORACLE_SID=cdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vbol83-02 dba]$ echo $PATH
/usr/local/bin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/app/oracle/product/19.0.0/dbhome_1/bin
[oracle@vbol83-02 dba]$

And you can see that 'oraenv' set it all for me.
I take it one step further, and put that in by .bash_logon script, so that my environment is set 'automagicly" when I log on to the server:
Relevent contents of my .bash_profile:
#----------------------------------------------------------------------
# 3 Aug 2012 - Ed Stevens
#   - replaced hard coded settings of ORACLE_* vars with use of oraenv
#     utility.
export ORACLE_TERM=xterm
export PATH=/usr/local/bin:/usr/sbin:$PATH
export ORACLE_SID=cdb
export ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK


SQL Allow user to run Read Only Stored Procedures We have stored procedures in database SQL Server 2016. Say TestDB.

Is there method allowing users to execute only Read-only stored procedures (No write insert/update stored procedures)? The way everything is setup in our legacy system, schemas are not being utilized to separate read access queries. Thank you,

The closest you could come is by putting them into a specific role, and granting that role explicit execute access on just those stored procedures. But you would have to identify all of those stored procedures manually.

Even if a procedure were somehow marked in the metadata as "this procedure doesn't write," the permissions system has no way to say "only do things with read-only objects."


Inbound Outbound Shipping Database should we combine both inbound and outbound shipping into a single table (then create an additional column to identify the direction) or to split them into separate table? What's the pros and cons?

Pros for Single table:
Fewer tables therefore less maintenance. If a record could change from inbound to outbound you just need to change the indicator.

Cons for single table:
There's a bit more effort in code logic.
You can't use foreign key constraints on one type versus another.

Pros for separate tables:
Logically easier to comprehend and code.
Simple way of splitting large tables if volumes are large.
Allows for different attributes and table definitions.
Allows for different foreign keys for each type. (I have to give @ypercube credit for this)

Cons for separate tables:
If a shipment is in transit moving from inbound to outbound it could fall between tables. (Less likely with single table)
More tables/indexes to maintain.


How to configure read transactions to never cause other write serialization transactions to fail I am protecting critical transactions with application mutexes and SERIALIZABLE.

I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures to those write transactions because of locking.

The reads are in libpqxx prepared statement transactions.  I have read the docs, wiki, and white paper, but I can't find out how to configure a read transaction so that it never risks a failure of another critical serializable write transactions.

I cannot determine if locks will cause serialization failures.  Can they?

The reads do not need to be perfectly acid because the application compensates for race conditions, old data, etc.  The primary concern is that the serializable critical write transactions do not fail due to other reads.

How should a read only prepared statement be configured so absolutely not to risk another serializable transaction write failure?

A READ ONLY transaction cannot cause a write transaction (that does not perform DDL) to fail, unless it explicitly uses LOCK TABLE or advisory locks.

READ ONLY transactions cannot SELECT ... FOR SHARE or SELECT ... FOR UPDATE. As they can't do DML, the strongest lock they can take on a table is ACCESS SHARE, which conflicts only with the ACCESS EXCLUSIVE lock taken by DDL.

Nor can a read-only transaction cause serialization failures if the write transaction is SERIALIZABLE, because serialization failures require that both transactions perform writes. It is always possible to logically serialize a read only transaction either before or after a read/write transaction, as it is impossible for them to be mutually interdependent.

So: It should be fine to use READ COMMITTED or SERIALIZABLE transactions, with READ ONLY, so long as you do not explicitly LOCK TABLE.

You also need to make sure you don't use advisory locks that might interact between the two sets of transactions. Most likely you don't use advisory locks at all and can forget about this entirely.

Separately, though, an application must be prepared to deal with serialization failures or other transaction aborts. Any design that tries to avoid this is broken. Transactions can be aborted because of OS / host machine level issues, admin action, etc. Do not rely on transactions that "cannot fail". If you absolutely must do this, you need to use two-phase commit, where you PREPARE TRANSACTION (at which point it's guaranteed that the tx cannot fail to commit), do the other work that relies on the tx committing safely, then COMMIT PREPARED. If something goes wrong with the other work, you can ROLLBACK PREPARED. 2PC has significant overheads and is best avoided when you can, but sometimes there's just no choice.


On long-running queries, which exact time is current_timestamp? I've got a query that checks for new patients in the ER, and if they have been there before in the last 72 hours, it notifies the killbo... uhhh... care managers.

I could set this up as a trigger, but I don't think this is a good idea.

What I want to do instead is set it up as a job that runs every 5 minutes, with this in the where clause:

    datediff(minute, TriageDateTime, current_timestamp) <= 5

But the query itself takes 03:31.

Question: is current_timestamp the timestamp of the beginning of the query?

here's the entire big, bad query:

    with visits as(
    select LocationID, ab.RoomID, ab.UnitNumber as MRN, datediff(year,ab.BirthDateTime, CURRENT_TIMESTAMP) as age,
    ed.TriageDateTime, ab.AdmitDateTime,
    ab.ReasonForVisit as reason, ab.DischargeDateTime,  ab.PtStatus,
    lag(ed.TriageDateTime,1) over (partition by ab.UnitNumber order by ed.TriageDateTime) as last_TriageDateTime,
    lag(ab.ReasonForVisit,1) over (partition by ab.UnitNumber order by ed.TriageDateTime) as last_reason,
    lag(ab.DischargeDateTime,1) over (partition by ab.UnitNumber order by ab.AdmitDateTime) as Last_DischargeDateTime,
    lag(ab.PtStatus,1) over (partition by ab.UnitNumber order by ab.AdmitDateTime) as Last_PtStatus

    from
    [eih-dr01].livefdb.dbo.EdmAcct_Main ed
    left join
    [eih-dr01].livendb.dbo.AbstractData ab
    on ed.VisitID = ab.VisitID
    where
    TriageDateTime >= dateadd(hour, -100, current_timestamp)
    and
    ab.UnitNumber is not null)

     select *, datediff(hour, last_TriageDateTime, TriageDateTime) as time_out
     into #temp
     from visits
     where datediff(hour, last_TriageDateTime, TriageDateTime) between 0 and 72
     and datediff(minute, TriageDateTime, current_timestamp) <= 5

     declare @mrn varchar(55),
     @reasonforvisit varchar(255),
     @lasttriage varchar(255),
     @lastreasonforvisit varchar(255),
     @text nvarchar (max),
     @sql nvarchar (max),
     @subject varchar(255)

     declare c cursor local for
     select MRN, reason, cast(cast(last_TriageDateTime as date) as varchar(55)) + ' ' + convert(varchar(5), last_TriageDateTime, 108), last_reason
     from #temp

     open c
     fetch from c into @mrn, @reasonforvisit, @lasttriage, @lastreasonforvisit

     while @@FETCH_STATUS = 0
     begin

    set @text = 'Patient ' + @mrn + ' is in the ED for ' + isnull( @reasonforvisit, 'unkown reasons') + '. Their last visit was '
    + @lasttriage + ' for ' +  @lastreasonforvisit
    print @text

    set @subject = 'ED Return visit'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'NoReply',
    @recipients =
    '**********@vtext.com;',

    @subject = @subject,
    @execute_query_database = 'Master',
    @body = @text,
    @body_format = 'html'

    fetch next from c into @mrn, @reasonforvisit, @lasttriage, @lastreasonforvisit
     end
     close c
     deallocate c
     select * from #temp
     drop table #temp

It does move over the duration of the query. If I toss in some

    print current_timestamp;

they change.

I'm going to work around it like this:

    declare @time datetime
    set @time = curent_timestamp


Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2 I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins and password.

But is there a way I should see if all the login and password are moved correctly?

Is there any documentation to follow during migration? I tried looking online couldn't find one.

Thanks

Use something like SSDT to do a schema comparison on the before and after. Check this out: https://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx

Note: This is only for the logins, obviously showing passwords isn't supported


Using Oracle XE, how do I create a CASE statement To return a specific date I am Trying to create a view based on a business calendar table called Business Payment Calendar view with two new calculated columns:

Payment Warning Date (Payment_Warning_Date)
Payment Due Date (Payment_Due_Date)

And, also the non-calculated Transaction_Date

Those dates are based on the number of BUSINESS days from the transaction date from the business calendar, 7 and 10 business days. The Business Calendar table contains columns populated for the year.

CAL_Date (which is our datestamp),
Day_Name,
Month_Name,
Cal_Year,
Month_Num,
Weekend (True/False),
Holiday (True/False),
Bus_Working_Day (True/False).

So for example, say you have a purchase today, the output will be:

Transaction_Date: 6/26/2017
Payment Warning Date: 7/6/2017
Payment Due Date: 7/11/2017

Figure we have the weekend coming up, next Monday (July 3) is a work day, but Tuesday (July 4) is a US holiday, then 3 more work days.

I'm just not having any luck building a case statement that properly takes into account the Bus_Working_Day field for SYSDATE+7 and +10 and then return the correct date.

The following will generate a list showing the current date, and the warning and due dates for the current date.

CREATE TABLE myCal (CAL_Date date,
Bus_Working_Day int)

INSERT INTO myCal
SELECT DATE '2017-06-27', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-28', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-29', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-30', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-01', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-02', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-03', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-04', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-05', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-06', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-07', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-08', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-09', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-10', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-11', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-12', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-13', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-14', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-15', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-16', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-17', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-18', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-19', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-20', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-21', 1 FROM DUAL
;

SELECT cal.CAL_Date
      ,warn.Warning_Date
      ,due.Due_Date
  FROM myCal cal
         LEFT  JOIN (SELECT mn.CAL_Date
                           ,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
                           ,alt.CAL_Date as Warning_Date
                       FROM myCal mn
                              INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
                      WHERE alt.Bus_Working_Day = 1
                    ) warn ON (cal.CAL_Date = warn.CAL_Date AND warn.rn = 7)
         LEFT  JOIN (SELECT mn.CAL_Date
                           ,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
                           ,alt.CAL_Date as Due_Date
                       FROM myCal mn
                              INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
                      WHERE alt.Bus_Working_Day = 1
                    ) due ON (cal.CAL_Date = due.CAL_Date AND due.rn = 10)
 ORDER BY CAL_Date
;

dbfiddle here

It requires more than a CASE statement, because you need to evaluate whether each following day is a business day or not to find the days you need.

Here's how it works:
 - join the table against itself (with mn as the first instance and alt as the second);
 - ignore all alt rows where the alt date is less than or equal to the mn date (because the warning and due date will always come after the current date, of course), and where the alt date is not a business working day;
 - for each mn date, number the alt dates in ascending order;
 - take the calendar table, and outer join it to the results of the above twice (joining to the mn date) as warn and due;
 - for the join to warn, take the 7th alt date where the calendar date matches the mn date, for due, the 10th alt date.

You may want to consider (if possible) adding warning_date and due_date to the existing calendar table - you could modify the code above to populate the values, once the base information on the dates is in place. Saves you from joining the calendar table to itself five times to get what you want. (OK, if you have a specific date, then you can hit the warn and due subqueries directly, and don't really have to join back to the calendar table - I did that for demonstration purposes. So, only joining it to itself 4 times).

NOTE: In newer versions of Oracle, where CROSS APPLY and OUTER APPLY are available, you may be able to change these to a more direct (and possibly better performing) query - however, without knowing what version you've got available (and needing to use dbfiddle for my testing, where the version is 11g), I went with this solution.

You should be able to modify this to meet your needs.

UPDATE: You asked what changes would need to be made to use the query in a trigger.

Actually, now that I think about it, I'd be inclined not to use a trigger directly. You see, even if one row is added, all the rows (or, at least, all the rows whose date is, say, within 20 days before any changed date) will need to be updated - which would mean we'd have a trigger that was (technically, at least) recursive.

To avoid that, it might be better to do something somewhat more complicated (of course!).

Create a new table, let's call it FLAG_JOB_TO_RUN, and give it one column, JOB_NAME.
In your trigger, INSERT the value Update_Warn_Due_Dates into FLAG_JOB_TO_RUN.JOB_NAME (if it doesn't already exist).
Schedule a script (or, even better, create a stored procedure and schedule that) to do the following:

Check if there is an 'Update_Warn_Due_Dates' entry in FLAG_JOB_TO_RUN
If there is:

Run the UPDATE version of the SELECT query above to set Warning_Date and Due_Date for all rows in myCal, and
DELETE that row from FLAG_JOB_TO_RUN

Run this job as often as you think is necessary.  It might suffice to run it once a night, and to manually run it right after you know there's been a major change; or, you might need to run it every 5 minutes.


Oracle csv line size more than 32767 How to generate csv in oracle if it's query result return more than 32767 character per line  in linux shell script

You can't use utl_file, since it has a maximum line length of 32767.
It's possible that sqlplus has a similar limit. If you are tying to
move data to a different Oracle database you can use data pump. You
can eve use datapump on a materialized view. If you want to migrate
to a non Oracle database you can use Perl or heterogeneous services
to create a database connection to mySQL, SQL Server, etc. Then you
won't have to worry about the line length.

UTL_FILE


Database design, 1-to-many or many-to-many? Just a little background information pertaining to the question at hand:
I'm developing an application for users to be able to log the work they have been doing hourly, to be compiled into a weekly log. They should log 8 consecutive hours a day.
Only these users can log their work, as they are in a specific table of their own.

My question is how to handle these weekly logs, as a 1-to-many relation or a many-to-many relation? That is, since there will be many users and thus many logs, create an intermediate relation linking both of these tables, or look at it from a more general point of view and think one user can have many reports, so as to label it a one-to-many relation.

In short, would it be a good idea to see this as follows: there is a relation stating the hour and description of the work done or task performed, another relation which would be the actual report (which has user field and an hour field, both foreign keys so this would be an intermediate relation), and then the user relation with all of its pertinent information.

I guess my doubt is more conceptual than anything, but I'm interested in learning this so I can make an optimal design.

Don't try to over-design your solution. Relational theory, like many other academic subjects can often be taken to extremes, to the point where they are difficult to grasp for a third party that will (eventually) inherit your work.

I think the most common data model you'll find, with regards to time reporting, looks something like this:

CREATE TABLE rep.Users (
    UserID     int PRIMARY KEY,
    ManagerID  int NULL,
    Name       varchar(100),
    FOREIGN KEY (ManagerID) REFERENCES rep.Users (UserID)
);

CREATE TABLE rep.Projects (
    ProjectID  int PRIMARY KEY,
    Name       varchar(100),
);

CREATE TABLE rep.TimeReport (
    UserID     int,
    ProjectID  int,
    StartTime  datetime,
    EndTime    datetime,
    Approved   bit DEFAULT (0),
    PRIMARY KEY (UserID, StartTime), /* possibly also ProjectID */
    FOREIGN KEY (UserID) REFERENCES rep.Users (UserID),
    FOREIGN KEY (ProjectID) REFERENCES rep.Projects (ProjectID)
);

In this design, the notion of a "report" is implicitly what you decide it to be: a day, a week, a month, etc. You can add various elements depending on your needs, for instance:

Is there a start or end validity date to each project and/or user?
Do you want EndTime or NumberOfHours? You can always calculate one from the other, so you never need both.
Do you need approval history or is it enough to know if the report is approved (i.e. closed) or not?