Collation issue after update (KB3072779) of SQL Server 2012 If found the following error while starting my SQL Server 2012 instance, after the update KB3072779 performed by Windows Update (Windows Server 2012 Foundation).

Creating procedure sp_sqlagent_get_perf_counters...
2016-11-14 12:09:07.84 spid5s      Error: 468, Severity: 16, State: 9.
2016-11-14 12:09:07.84 spid5s      Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

So now I'm unable to start the SQL Server instance. I managed to run it with -t902 and perform some queries.

I did check the following:

select serverproperty('collation')

which returned:

Latin1_General_CS_AS

also

select name, collation_name from sys.databases

which returned:

master  Latin1_General_CI_AS
tempdb  Latin1_General_CI_AS
model   Latin1_General_CI_AS
msdb    Latin1_General_CI_AS

From what I've read so far, system databases must have the same collation as the server. What I'm not aware of is what happened here and what should I do now.

Did this update change the server collation?

What can I do now to run my instance normally?

===========================

EDIT: I finally arrive to solve the problem by following the steps described in one of the links posted by @hot2use

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation

I would go and look at the setup.log files for the Service Pack 3 Upgrade. They should be found here:

Each execution of Setup creates log files are created with a new timestamped log folder at %programfiles%\MicrosoftSQL Server\110\Setup Bootstrap\Log\. The time-stamped log folder name format is YYYYMMDD_hhmmss. When Setup is run in an unattended mode, the logs are created at % temp%\sqlsetup*.log. All files in the logs folder are archived into the Log*.cab file in their respective log folder.

More information can be found here: View and Read SQL Server Setup Log Files (Microsoft)

This might provide you with information on what happened.

If you feel like rebuilding you SQL Server instance with the correct collation, then you could try the solution here:

How to change server level collation for a SQL Server Instance

There are slight variations of above post involving re-installing the system databases and or just modifying some objects:

Changing SQL Server Collation After Installation
Script and Alter SQL Server Database Objects with PowerShell to Correct Collation Issues

It's not highly recommended and is best tested in a non-productive environment first. Currently there are no documented cases of the collation being changed during the setup of a service pack on SQL Server. If you don't find any information in the setup logs, then I would try and contact Microsoft and open a case/ticket.


SQL Server Maintenance Plan TSQL query to log the backup databases Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up?

Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3.

I wanted a way to do

INSERT INTO LOG VALUES (BD1,...)
INSERT INTO LOG VALUES (BD2,...)
INSERT INTO LOG VALUES (BD3,...)

But I did not want to statically type this, but have a generic SQL that knows which DBs where backup and insert those.

Thanks

I would recommend using these free backup (and other) scripts from http://ola.hallengren.com/

These have full logging already and have lots of other useful options.

Personally, these are what I use. Thoroughly tested and used by many, many folk.


When are InnoDB table index statistics updated? In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated?  What events trigger such updates?  I've seen comments suggesting that the following might trigger it:

table is opened for first time
query is run against table
ANALYZE TABLE
size of table changes by some threshold

You should look for this variable

mysql> show variables like '%metadata%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

According to the MySQL Docs, when innodb_stats_on_metadata is set (by default), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Once disabled, you would have to run ANALYZE TABLE on the InnoDB tables of your choice. Make sure you have SELECT and INSERT privileges.


MySQL binary logging with just one log file? I have a MySQL database running with binary logging turned on. Each time the database is restarted a new binary log file is created in the format :

mysql-bin.00001
mysql-bin.00002
...
mysql-bin.0000n

My question is, is there a way to have the binary log file stay the same after each database restart.

So for example it's always mysql-bin.00001

Any help would be greatly appreciated.

There are five(5) methods you can do

METHOD #1

Don't run restart. Delete the binlogs between shutdown and startup.

Let's say mysql-bin.* is in /var/lib/mysql. Run the following:

cd /var/lib/mysql
service mysql stop
rm -f mysql-bin.*
service mysql start

After startup, login to mysql and run

SHOW MASTER STATUS;

an you should see mysql-bin.000001

METHOD #2

Create a script to execute RESET MASTER; and have it executed

Step 01 : Add this line to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init.sql

Step 02 : Run the following

echo "RESET MASTER;" > /var/lib/mysql/init.sql
chown mysql:mysql /var/lib/mysql/init.sql

Step 03 : service mysql restart

METHOD #3

Do it by hand. Login to mysql and run

mysql> RESET MASTER;

METHOD #4

Erase binary logs immediately after restart

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
service mysql restart
mysql ${MYSQL_CONN} -e"RESET MASTER"

METHOD #5 (Permanent)

Step 01 : Go to my.cnf and comment out the log-bin option

[mysqld]
#log-bin=mysql-bin

or delete the line that says log-bin=mysql-bin

Step 02 : Run the following

cd /var/lib/mysql
service mysql stop
rm -f mysql-bin.*
service mysql start

Step 03 : Login to mysql and run

mysql> SHOW BINARY LOGS;

The output should be

ERROR 1381 (HY000): You are not using binary logging

GIVE IT A TRY !!!


How to use the below expression in my SQL Query I have this expression for sorting. I need to use this expression in my SQL code for the sorting purpose. Can anyone help me with this??

I tried case when stmt but it fails.

Expression:

if ([All Accounts].[ea_end_of_activity] is missing or
[All Accounts].[ea_end_of_activity] is null)
then (
if   ([Japanese Accounts].[aco_ac_ea_id] is missing)
then ('1')
else ('0')
)
else ('2')
+
[All Accounts].[aco_acc_short_name]

CODE:

SELECT DISTINCT
    First.aco_ac_ea_id,
    Sec.aco_acc_short_name,
    Sec.aco_acc_marketing,
    Sec.ea_spark_number,
    Sec.ea_end_of_activity
FROM
    (
    SELECT aco_ac_ea_id
    FROM CD.dbo.MAC
    WHERE
    MAC.aco_locale = 'ja' AND
    MAC.aco_acc_marketing_status = 'VALIDATED'
    ) as First
RIGHT OUTER JOIN
    (
    SELECT
     aco_ac_ea_id,
     aco_acc_short_name,
     aco_acc_marketing,
     ea_spark_number,
    ea_end_of_activity
    FROM
    CD.dbo.MAC,
    CD.dbo.MEA
    WHERE
    MAC.aco_locale ='en-us' and
    MAC.aco_ac_ea_id = MEA.ea_id
    ) as Sec
ON First.aco_ac_ea_id=Sec.aco_ac_ea_id

No idea what the is missing is supposed to mean (is it the same as is null?). If yes, the CASE expression and the ORDER BY can be written like this:

ORDER BY
  CASE
    WHEN Sec.ea_end_of_activity IS NOT NULL
      THEN 2
    WHEN First.aco_ac_ea_id IS NULL
      THEN 1
    ELSE 0
  END,
  Sec.aco_acc_short_name ;

The First. and Sec. can be omitted (at least in other DBMS, not 100% sure about Sybase), since they are in the SELECT list.


Importing and exporting data from a database to another using SyBase is taking a long time In order to update a database(Sybase Adaptive Server Anywhere 8) I'm taking all the data from one database on clients computer and using

SELECT * FROM tabelName OUTPUT TO 'C:\path' FORMAT ASCII DELIMITED BY ';';

Then I'm using those files to update another database that's connected to a server by making temporary tables, checking if anything is missing and then dropping the temporary table.

INPUT INTO tempTable FROM 'C:\path' FORMAT ASCII DELIMITED BY ';';

INSERT INTO mainTable
SELECT *
FROM tempTable
WHERE id NOT IN (SELECT id FROM mainTable);

DROP TABLE tempTable;

So I am wondering if there is a better way to do this since two tables are taking a very long time to export and import the data(they do have a ton of columns and data though but still and by ton of data I mean ~100 columns and ~100,000 worth of rows). It's especially weird since another table with similar amount of data goes by very quickly(although it does have less columns). The two tables that are taking a while are reading data ~1000 per sec while others are much faster.

Each client has the database.db file on their laptop. They get some data and need to submit it but have no connection, so they use their own .db file and update the one on the server later.

Getting the SQL definitions might be hard since tables were made a while back and I have no clue how to get them. The destination database is the database that holds all the data while source ones are the ones that are not connected and can be used offline if needed.

Some recommendations, tips would be greatly appreciated.

Without getting too involved in specific details on the language required to do this in Sybase SQL Anywhere 8, which is now more than 15 years old, I'd recommend something like the following:

create a table on the laptop database that contains a column which uniquely identifies rows that have already been exported to the main database.
the export function would do something like the following:

SELECT *
FROM tableName
WHERE tableName.KeyColumn NOT IN (
    SELECT KeyColumn
    FROM PreviouslyExportedTable
    )
OUTPUT TO 'C:\path' FORMAT ASCII DELIMITED BY ';';

Exporter then inserts rows into the PreviouslyExportedTable that don't already exist.  Next time the exporter runs, it will only export new rows that weren't already exported.  The insert code might look like:

INSERT INTO PreviouslyExportedTable
SELECT KeyColumn
FROM TableName
WHERE KeyColumn NOT IN (
    SELECT KeyColumn
    FROM PreviouslyExportedTable
    );

This should drastically reduce the number of rows that need to be exported and imported each time.  It is uncertain to me if SQL Anywhere 8 supports using WHERE NOT EXISTS clauses instead of WHERE NOT IN; the NOT EXISTS variant might be faster, if it works.


Why are backups of a previous incarnation marked obsolete without regard to redundancy? After incomplete recovery and opening a 9i database with resetlogs we ran a full backup which completed successfully. The backup includes a command to delete obsolete backups after it finishes:

delete noprompt obsolete device type sbt;

RMAN is configured to use REDUNDANCY 2:

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

However all the backups from the old incarnation were immediately marked obsolete and deleted without regard to redundancy.

Would this behaviour have been different if we had a RECOVERY WINDOW configured instead of REDUNDANCY 2?
Is this behaviour the same in later versions of Oracle?

edit: added output of LIST INCARNATION:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN      Reset Time
------- ------- -------- ---------------- --- -------------- ----------
1       1       LIVE     3494832994       NO  1              19-JAN-04
2       2       LIVE     3494832994       NO  11966702870498 01-JAN-14
3       3       LIVE     3494832994       YES 12041003378277 04-JUL-18

For The Interested RMAN Beginners

The RMAN policies REDUNDANCY and RECOVERY WINDOW are mutually exclusive. This means you can either set one or the other.

Redundancy Policy

Having set REDUNDANCY 2 will always keep only the last two backups and deleting (or marking obsolete) any other previous backups, that are no longer required to bring back the database into a consistent state.

Note:
If you have LEVEL 1 and ARCHIVELOG backups in between the FULL and/or LEVEL 0 backups, then they should be retained until no longer required.

Additional Information

According to the PDF file Oracle Database - Backup and Recovery Reference your retention policy REDUNDANCY 2 should not have deleted your older backups:

  The following scenario illustrates how redundancy works in an incremental
  backup strategy. Assume that the redundancy level is 1. You run a level 0
  database backup at noon Monday, a level 1 cumulative backup at noon on
  Tuesday and Wednesday, and a level 0 backup at noon on Thursday.
  Immediately after each daily backup you run a DELETE OBSOLETE. The
  Wednesday DELETE command does not remove the Tuesday level 1 backup
  because this backup is not redundant: the Tuesday level 1 backup could be used
  to recover the Monday level 0 backup to a time between noon on Tuesday and
  noon on Wednesday. However, the DELETE command on Thursday removes the
  previous level 0 and level 1 backups.

The above example uses a value of 1 but is clearly stating that earlier backups of full backups are not deleted until they are larger than the value configured.

Answering your questions

It depends,  .... (see 2. and "Possible Issues")
According to the documentation the retention policy setting still seems to be the same for all versions:

Oracle 11c (11.1) : CONFIGURE
Oracle 11c (11.2) : CONFIGURE
Oracle 12c (12.1) : CONFIGURE
Oracle 12c (12.2) : CONFIGURE
Oracle 18c (18.x) : CONFIGURE

Possible Issues

You encountered a bug during the backup and restores of your database instance.

Reproducing with Oracle 12c

Because Oracle RDBMS 9i is pretty much obsolete and our environment is nearly up-to-date, I was only able to re-iterate/reproduce the steps in a 12c environment.

RMAN Configuration

RMAN was set up using the defaults:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORACLE12JN are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE_12\PRODUCT\12.2.0\DBHOME_1\DATABASE\SNCFORACLE12JN.ORA'; # default

RMAN Backups

RMAN backups were performed by issuing a simple backup database; command:

RMAN> backup database;

Starting backup at 17-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
input datafile file number=00005 name=C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
input datafile file number=00003 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
input datafile file number=00007 name=C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 17-JUL-18
channel ORA_DISK_1: finished piece 1 at 17-JUL-18
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP tag=TAG20180717T151608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-JUL-18

Starting Control File and SPFILE Autobackup at 17-JUL-18
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\AUTOBACKUP\2018_07_17\O1_MF_S_981731783_FNVV17QR_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 17-JUL-18

Verifying Backups, Check Obsolete Backups and List Incarnations

After a certain period of backups had been performed I double-checked the RMAN catalog:

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3       B  F  A DISK        2018-07-16 10:03:01 1       1       NO         TAG20180716T100301
5       B  F  A DISK        2018-07-16 10:27:44 1       1       NO         TAG20180716T102744
7       B  F  A DISK        2018-07-16 10:49:50 1       1       NO         TAG20180716T104950
9       B  F  A DISK        2018-07-16 10:50:08 1       1       NO         TAG20180716T105007
11      B  F  A DISK        2018-07-16 10:50:22 1       1       NO         TAG20180716T105022
13      B  F  A DISK        2018-07-16 10:51:21 1       1       NO         TAG20180716T105121
15      B  F  A DISK        2018-07-16 13:06:13 1       1       NO         TAG20180716T130612
17      B  F  A DISK        2018-07-16 15:26:23 1       1       NO         TAG20180716T152623
19      B  F  A DISK        2018-07-16 15:57:07 1       1       NO         TAG20180716T155707
20      B  0  A DISK        2018-07-17 08:22:01 1       1       NO         TAG20180717T082154
21      B  F  A DISK        2018-07-17 08:22:02 1       1       NO         TAG20180717T082202
22      B  0  A DISK        2018-07-17 14:36:07 1       1       NO         TAG20180717T143558
23      B  F  A DISK        2018-07-17 14:36:14 1       1       NO         TAG20180717T143614
24      B  F  A DISK        2018-07-17 15:16:15 1       1       NO         TAG20180717T151608
25      B  F  A DISK        2018-07-17 15:16:23 1       1       NO         TAG20180717T151623
26      B  A  A DISK        2018-07-17 15:55:52 1       1       NO         TAG20180717T155551
27      B  F  A DISK        2018-07-17 15:55:53 1       1       NO         TAG20180717T155553
28      B  F  A DISK        2018-07-17 16:05:17 1       1       NO         TAG20180717T160517

Verified that no obsolete backups were lying around:

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

RMAN>

And listed the current incarnations:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORACLE12 2083073378       PARENT  1          2017-03-08 15:57:31
2       2       ORACLE12 2083073378       CURRENT 1490582    2018-07-16 09:27:03

Perform First Restore / Recovery

The following commands were issued to restore/recover the database into a consistent state:

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1610612736 bytes

Fixed Size                     8747368 bytes
Variable Size                385877656 bytes
Database Buffers            1207959552 bytes
Redo Buffers                   8028160 bytes

RMAN> run
2> {
3> set until time '2018-07-17 14:36:14';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2018-07-17 16:04:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP tag=TAG20180717T143558
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2018-07-17 16:04:38

Starting recover at 2018-07-17 16:04:38
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2018-07-17 16:04:39

RMAN> alter database open resetlogs;

Statement processed

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORACLE12 2083073378       PARENT  1          2017-03-08 15:57:31
2       2       ORACLE12 2083073378       PARENT  1490582    2018-07-16 09:27:03
3       3       ORACLE12 2083073378       CURRENT 1701490    2018-07-17 16:05:13

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3       B  F  A DISK        2018-07-16 10:03:01 1       1       NO         TAG20180716T100301
5       B  F  A DISK        2018-07-16 10:27:44 1       1       NO         TAG20180716T102744
7       B  F  A DISK        2018-07-16 10:49:50 1       1       NO         TAG20180716T104950
9       B  F  A DISK        2018-07-16 10:50:08 1       1       NO         TAG20180716T105007
11      B  F  A DISK        2018-07-16 10:50:22 1       1       NO         TAG20180716T105022
13      B  F  A DISK        2018-07-16 10:51:21 1       1       NO         TAG20180716T105121
15      B  F  A DISK        2018-07-16 13:06:13 1       1       NO         TAG20180716T130612
17      B  F  A DISK        2018-07-16 15:26:23 1       1       NO         TAG20180716T152623
19      B  F  A DISK        2018-07-16 15:57:07 1       1       NO         TAG20180716T155707
20      B  0  A DISK        2018-07-17 08:22:01 1       1       NO         TAG20180717T082154
21      B  F  A DISK        2018-07-17 08:22:02 1       1       NO         TAG20180717T082202
22      B  0  A DISK        2018-07-17 14:36:07 1       1       NO         TAG20180717T143558
23      B  F  A DISK        2018-07-17 14:36:14 1       1       NO         TAG20180717T143614
24      B  F  A DISK        2018-07-17 15:16:15 1       1       NO         TAG20180717T151608
25      B  F  A DISK        2018-07-17 15:16:23 1       1       NO         TAG20180717T151623
26      B  A  A DISK        2018-07-17 15:55:52 1       1       NO         TAG20180717T155551
27      B  F  A DISK        2018-07-17 15:55:53 1       1       NO         TAG20180717T155553
28      B  F  A DISK        2018-07-17 16:05:17 1       1       NO         TAG20180717T160517

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

The restore was successful and no backups were marked as obsolete even though the incarnation changed after the restore and with the ALTER DATABASE OPEN RESETLOGS issued.

Alternate Incarnation Listing

An alternate representation of the incarnations can be achieved with the following command:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
set pages 50
set lines 230
column Path format a20
select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME,
PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path
from v$database_incarnation
WHERE LEVEL >=1 START WITH INCARNATION# = '1'
CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION#
order by Level, Path, RESETLOGS_TIME;

Output:

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 CURRENT          3  -> 1 -> 2 -> 3

The advantage of querying the local catalog in the database instance is the representation of the incarnation as a path to follow

Perform Second Restore / Recovery

The following commands were issued to restore/recover the database into a consistent state again:

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1610612736 bytes

Fixed Size                     8747368 bytes
Variable Size                520095384 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   8028160 bytes

RMAN> run
2> {
3> set until time '2018-07-17 08:22:01';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2018-07-17 16:14:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/17/2018 16:14:37
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Ok, we've hit an incarnation "bump" in the restore path. We are currently on incarnation 3 as can be seen in the incarnation listing below which I copied from above:

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 CURRENT          3  -> 1 -> 2 -> 3

...and the backup we are aiming for is after the RESETLOGS for incarnation 2. Let's reset the incarnation to 2 and proceed with the backup:

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN>

Seems to work. Let's restart the restore/recover again:

RMAN> run
2> {
3> set until time '2018-07-17 08:22:01';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2018-07-17 16:16:12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP tag=TAG20180717T082154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2018-07-17 16:16:20

Starting recover at 2018-07-17 16:16:20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC
archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018-07-17 16:16:21

RMAN> alter database open resetlogs;

Statement processed

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

RMAN>

The restore was successful and no backups were marked as obsolete even though the incarnation changed after the restore and the ALTER DATABASE OPEN RESETLOGS was issued again. Now we are on a newer incarnation and still no Obsolete Backups reported.

List of Current Incarnations After Second Restore (SQL)

SQL> select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path from v$database_incarnation WHERE LEVEL >=1 START WITH INCARNATION# = '1' CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION# order by Level, Path, RESETLOGS_TIME;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 ORPHAN           3  -> 1 -> 2 -> 3
           4           1691865 2018-07-17 16:16:29                  2 CURRENT          3  -> 1 -> 2 -> 4

Initiate Backup

After restoring the second time let's backup the database again:

RMAN> backup database;

Starting backup at 2018-07-17 16:51:39
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF
input datafile file number=00005 name=C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF
input datafile file number=00003 name=C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF
input datafile file number=00007 name=C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 2018-07-17 16:51:39
channel ORA_DISK_1: finished piece 1 at 2018-07-17 16:51:46
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T165139_FNW0MVRT_.BKP tag=TAG20180717T165139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2018-07-17 16:51:46

Starting Control File and SPFILE Autobackup at 2018-07-17 16:51:46
piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\AUTOBACKUP\2018_07_17\O1_MF_S_981737506_FNW0N31S_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 2018-07-17 16:51:47

RMAN>

Check Obsolete

We'll now check which (backup) files have become obsolete:

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           20     2018-07-17 08:22:01
  Backup Piece       20     2018-07-17 08:22:01 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP
Backup Set           22     2018-07-17 14:36:07
  Backup Piece       22     2018-07-17 14:36:07 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP
Backup Set           24     2018-07-17 15:16:15
  Backup Piece       24     2018-07-17 15:16:15 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP
Archive Log          2      2018-07-17 15:55:51 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC
Backup Set           26     2018-07-17 15:55:52
  Backup Piece       26     2018-07-17 15:55:52 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_ANNNN_TAG20180717T155551_FNVXC84F_.BKP
Archive Log          3      2018-07-17 16:05:13 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_4_FNVXWSDY_.ARC

RMAN>

Check Incarnations (SQL)

SQL> select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path from v$database_incarnation WHERE LEVEL >=1 START WITH INCARNATION# = '1' CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION# order by Level, Path, RESETLOGS_TIME;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_INCARNATION# STATUS       LEVEL PATH
------------ ----------------- ------------------- ------------------ ------- ---------- --------------------
           1                 1 2017-03-08 15:57:31                  0 PARENT           1  -> 1
           2           1490582 2018-07-16 09:27:03                  1 PARENT           2  -> 1 -> 2
           3           1701490 2018-07-17 16:05:13                  2 ORPHAN           3  -> 1 -> 2 -> 3
           4           1691865 2018-07-17 16:16:29                  2 CURRENT          3  -> 1 -> 2 -> 4

As can be seen the incarnation 3 is now orphaned, because its direct line of heritage in regards to the current state of the database is broken. After the first restore we went back in time an re-restored the database again which results in the incarnation path 1 -> 2 -> 4 being the direct line of current ancestors for the open database.

Because the direct line for restoring the current database is along the incarnations of 1, 2, 4 there is no need for RMAN to keep the obsolete backups listed above. They can safely be deleted.

Delete Obsolete

Let's go ahead and delete the obsolete backups:

RMAN> delete obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           20     2018-07-17 08:22:01
  Backup Piece       20     2018-07-17 08:22:01 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP
Backup Set           22     2018-07-17 14:36:07
  Backup Piece       22     2018-07-17 14:36:07 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP
Backup Set           24     2018-07-17 15:16:15
  Backup Piece       24     2018-07-17 15:16:15 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP
Archive Log          2      2018-07-17 15:55:51 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC
Backup Set           26     2018-07-17 15:55:52
  Backup Piece       26     2018-07-17 15:55:52 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_ANNNN_TAG20180717T155551_FNVXC84F_.BKP
Archive Log          3      2018-07-17 16:05:13 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_4_FNVXWSDY_.ARC
Backup Set           30     2018-07-17 16:17:52
  Backup Piece       30     2018-07-17 16:17:52 C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T161743_FNVYN815_.BKP

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP RECID=20 STAMP=981706915
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP RECID=22 STAMP=981729359
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T151608_FNVV0RH4_.BKP RECID=24 STAMP=981731768
deleted archived log
archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC RECID=2 STAMP=981734151
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_ANNNN_TAG20180717T155551_FNVXC84F_.BKP RECID=26 STAMP=981734152
deleted archived log
archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_4_FNVXWSDY_.ARC RECID=3 STAMP=981734713
deleted backup piece
backup piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNNDF_TAG20180717T161743_FNVYN815_.BKP RECID=30 STAMP=981735464
Deleted 7 objects

List Backup Summary

Now that we have deleted some backup files and RMAN has performed some internal cleaning, let's have a look at the backup summary:

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3       B  F  A DISK        2018-07-16 10:03:01 1       1       NO         TAG20180716T100301
5       B  F  A DISK        2018-07-16 10:27:44 1       1       NO         TAG20180716T102744
7       B  F  A DISK        2018-07-16 10:49:50 1       1       NO         TAG20180716T104950
9       B  F  A DISK        2018-07-16 10:50:08 1       1       NO         TAG20180716T105007
11      B  F  A DISK        2018-07-16 10:50:22 1       1       NO         TAG20180716T105022
13      B  F  A DISK        2018-07-16 10:51:21 1       1       NO         TAG20180716T105121
15      B  F  A DISK        2018-07-16 13:06:13 1       1       NO         TAG20180716T130612
17      B  F  A DISK        2018-07-16 15:26:23 1       1       NO         TAG20180716T152623
19      B  F  A DISK        2018-07-16 15:57:07 1       1       NO         TAG20180716T155707
21      B  F  A DISK        2018-07-17 08:22:02 1       1       NO         TAG20180717T082202
23      B  F  A DISK        2018-07-17 14:36:14 1       1       NO         TAG20180717T143614
25      B  F  A DISK        2018-07-17 15:16:23 1       1       NO         TAG20180717T151623
27      B  F  A DISK        2018-07-17 15:55:53 1       1       NO         TAG20180717T155553
28      B  F  A DISK        2018-07-17 16:05:17 1       1       NO         TAG20180717T160517
29      B  F  A DISK        2018-07-17 16:16:34 1       1       NO         TAG20180717T161634
31      B  F  A DISK        2018-07-17 16:17:59 1       1       NO         TAG20180717T161759
32      B  F  A DISK        2018-07-17 16:51:46 1       1       NO         TAG20180717T165139
33      B  F  A DISK        2018-07-17 16:51:47 1       1       NO         TAG20180717T165146
34      B  F  A DISK        2018-07-18 07:24:13 1       1       NO         TAG20180718T072405
35      B  F  A DISK        2018-07-18 07:24:21 1       1       NO         TAG20180718T072421

RMAN>

As we can see we still have "at least" two complete backup  that will allow us to restore the database two backups in the past. RMAN (in 12c) did not delete any other backups along the incarnation path or outside of the orphaned incarnation.

Conclusion

Regarding the deleted backups after your initial restore in 9i I believe there are two possible scenarios:

You hit a bug.
After the restore two (FULL) backups were initiated which resulted in the backups being deleted on tape.

Reference Material

Doc ID 206862.1 (How does the RMAN Retention Policy Obsolete Incremental Backupsets and Archivelog Files)

Valid for Oracle 9i+


SQL Server: track database changes I have two very similar database questions related to changes tracking.

There are a lot of automation tests for an application, all of them use one database backup. Currently before each test execution the backup is restored.  It's needed as these tests might change data, so if several tests were executed in a row without restoring database, they might fail. Restoring a database is rather slow operation and I want to avoid it. Is there a way to restore a database to the previous state, before executing the test? I want the following scenario:

The database is restored
Test 1 is executed (it might change data)
The database is restored to previous state
Test 2 is executed and so on

I think about wrapping up all tests operation into a transaction and using rollback when a test finishes. But probably there is a better way to do so? Can someone at least point me to the right direction?
I have rather large database which I use for performance testing. Typically, I add experimental indexes, change stored procedures, functions and so on. What I want is to track my changes: which indexes were added, which stored procedures were changed and so on.
How can I do it? I want to be able to restore the original database state to compare performance with and without changes.

You should separate the tests into tests that can execute inside a transaction and rollback and tests that require a full restore. Execute accordingly :)

If you have any DDL in the test then don't bother with transactions, the only safe option is to restore. Most single threaded DML tests will work if wrapped in a transaction and rolled back, but not all.

For speeding up restore consider snapshots, see Revert a Database to a Database Snapshot but keep in mind that snapshot feature requires Enterprise edition (or whatever its current name for 'Enterprise').


Oracle service names with a minus sign (?) Are there any reasons not to use minus signs/dashes/hyphens in Oracle service names?
DBMS_SERVICE, tnsping and sqlplus seem to be fine, but the documentation says no.

We are switching from Oracle SIDs to service names. Apparently, they can be longer than SIDs (64 chars instead of 8), and can contain a domain name (hence dots . and minus signs -). The documentation of DBMS_SERVICE says that service_name is limited to 64 characters, and network_name is limited to the NET service_name character set
which is restricted to

[a...z] [A...Z] [0...9] _

So, what about dots and hyphens? Are they ok or not?

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_serv.htm#ARPLS68018
http://docs.oracle.com/cd/E11882_01/network.112/e10835/syntax.htm#NETRF175

Oracle said no, only [a...z] [A...Z] [0...9] _


How to write validation trigger which works with all isolation levels Background

I need to write validation trigger for a table. Due to nature of validation I can not use builtin integrity checks. For purpose of this question let's say that validation counts number of rows with specific properties and number of this rows has to be less than 5. In fact my conditions are more complicated (there is a relation between two columns). Please consider validation as blackbox with only one property, it uses whole test table, no data from outside.

I don't want limit usage of database isolation levels. SERIALIZABLE works by design but other two should be also possible to use.

The main idea is, that usage of the trigger should be as easy as usage of other constraints.

Ideas

raise error when repeatable read mode is used (can be detected by current_setting('transaction_isolation'). This is the least preferred solution but I least it ensures data integrity
after table lock check if table is different from the current visible snapshot, then raise serialization failure - I have no idea how do it
Use another transaction inside trigger (after table is locked) to see if data are unchanged or not autonomous transactions

Trigger example - my current best solution

SERIALIZABLE seems working by design - no locking is required
READ COMMITTED I was able to fix this mode by using table lock
REPEATABLE READ No idea how to fix this mode

Code

CREATE OR REPLACE FUNCTION verify() RETURNS trigger
AS $$
DECLARE
    count integer;
BEGIN
    LOCK TABLE test IN EXCLUSIVE MODE; --fixes races in READ COMMITTED mode
    count := (SELECT count(*) FROM test where val = NEW.val);

    IF count >= 5 THEN -- this is simplified for example, validation real validation uses more columns
        RAISE EXCEPTION '% Already present', NEW.val USING ERRCODE = 'integrity_constraint_violation';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

registration

CREATE TRIGGER validate BEFORE INSERT OR UPDATE
 ON test FOR EACH ROW EXECUTE PROCEDURE verify();

You can solve this for any isolation level by taking an exclusive lock on a row in a parent table - which is much cheaper and less intrusive than locking the whole table like you do currently.
If you don't have one already (which is a typical case), just create a simple table with some kind of UNIQUE constraint, a PK serves nicely:
CREATE TABLE parent (val integer PRIMARY KEY);  -- match data type

Achieve the lock with an UPSERT statement (as first statement in the transaction!) which either inserts a row in the parent table (holding an exclusive lock automatically) or takes a lock on an existing row. This way, only a single transaction at a time can write to rows with the same NEW.val in the child table. And all such rows are visible to the current transaction (even in REPEATABLE READ) because it started with the lock.
You do not even need a foreign key constraint between child and parent (though it won't hurt). You can just start with an empty parent table.
Waring: not reliable as trigger in REPEATABLE READ mode! See below.
CREATE OR REPLACE FUNCTION verify()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO parent AS p (val)    -- insert parent
   VALUES (NEW.val)
   ON     CONFLICT (val) DO UPDATE  -- if already there ...
   SET    val = NEW.val
   WHERE  FALSE;                    -- never executed, just locks the row

   IF (SELECT count(*) >= 5 FROM child c WHERE c.val = NEW.val) THEN
      RAISE EXCEPTION '% already present 5 times', NEW.val USING ERRCODE = 'integrity_constraint_violation';
   ELSE
      RETURN NEW;                   -- proceed with INSERT / UPDATE
   END IF;
END
$func$  LANGUAGE plpgsql;

The manual:

all rows will be locked when the ON CONFLICT DO UPDATE action is taken.

When inserting / updating multiple rows, make sure the rows are in consistent order to avoid deadlocks.
On second thought, while cheaper than your current trigger function, this can still fail because a trigger function is not necessarily the first statement in the transaction. Concurrent transactions may have committed between transaction start and the time the lock is taken. And any such rows in the child table remain invisible in REPEATABLE READ mode, making the count fail.
You would have to encapsulate each write on one or more rows with the same val in a separate transaction, starting with the UPSERT to take the lock first.
Related:

How to perform conditional insert based on row count?
How to use RETURNING with ON CONFLICT in PostgreSQL?
Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Alternative: add a number column and a UNIQUE constraint
Untested, no more time ...
ALTER TABLE child
   ADD COLUMN val_nr int NOT NULL
 , ADD CONSTRAINT child_max5_per_val CHECK (val_nr BETWEEN 1 AND 5)
 , ADD CONSTRAINT child_val_nr UNIQUE (val, val_vr);

These constraints enforce your rules in all transaction isolation modes. But you need to assign a val_nr between 1 and 5 for every row. After taking the lock, you can look up existing val_nr and fill gaps, which can still fail in REPEATABLE READ mode, but not silently. You'd get a duplicate key violation error.


Does query string length matter? It could be just a question for a micro-performance, but it always keeps coming back to my head (just making me crazy). I can't find any answer in Google about it. I just want to clarify it.

I have these two example queries.

SELECT tbl_person.first_name, tbl_person.last_name, ....
FROM tbl_person
WHERE tbl_person.tbl_person_id=1
LIMIT 1

and this one.

SELECT p.first_name, p.last_name, ....
FROM tbl_person p
WHERE p.tbl_person_id=1
LIMIT 1

Is there any advantage or disadvantage between them?

I know the second one is easier to read and write, but it doesn't matter because the query will be just generated.

I am interested in performance impact. If the query has a few hundred times more characters, does it hurt performance?

does it would hurt performance

Not in any significant way. The initial parsing may take a little longer, as might the network transfer (though for hundreds or even thousands of characters you are not going to really see that unless you are talking to the server over a cellular connection or such!), but after that stage the query planner will be given the same tokenised statements whether you used table aliases or not. Time spent in the query planner's realm will be much more significant than the parsing+tokenising time, and running the resulting plan will usually be orders of magnitude more expensive again.

tl;dr: if there is a performance difference it will be practically undetectable.


Run query using each day of last month I have query that returns a count of records with a matching date:

DECLARE @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
SELECT
DISTINCT(CAST(Response_Date AS DATE)) AS Response_Date
,COUNT(CAST(Response_Date AS DATE)) AS Response_Count
...
WHERE Response_Date BETWEEN @StartDate AND @EndDate

And I get a return as expected.

^Response_Date^Response_Count^
|2016-09-22   |2             |
|2016-09-24   |4             |
...

However, these values will be used in report builder in lists and charts.  The problem is that there are gaps between the dates.  Is there some way to run the query returning all dates from previous months, and a 0 when nothing is on that date?

Here is my current query:

DECLARE @JurisdictionName AS CHAR(30) = 'Something'
DECLARE @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

SELECT
    DISTINCT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Date
    ,COUNT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Count
FROM
    Response_Master_Incident
WHERE EXISTS
    (SELECT *
        FROM Activity_Log
        WHERE
        Activity_Log.Radio_Code IS NOT NULL
        AND (Radio_Name LIKE 'V[0-9][0-9]' OR Radio_Name LIKE 'U[0-9][0-9]')
        AND Response_Master_Incident.ID = Activity_Log.Master_Incident_ID
        )
    AND Response_Master_Incident.Time_First_Unit_Assigned IS NOT NULL
    AND Response_Master_Incident.Response_Date BETWEEN @StartDate AND @EndDate
GROUP BY CAST(Response_Master_Incident.Response_Date AS DATE)

You have 2 good options that I see.  One is to use a numbers table that you put into the database.  This is simply a table that has a sequential list of numbers from 1 to 1,000,000 or any upper limit that you find useful.  This table will allow you to accomplish what you want here, but you will also find many other useful things to do with it. See here for other potential uses of a number or tally table.

So first create a numbers table:

SELECT TOP 1000000 N=IDENTITY(INT, 1, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;

ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

From here we can write the query.

IF OBJECT_ID('tempdb..#test_dates') IS NOT NULL
  DROP TABLE #test_dates

CREATE TABLE #test_dates (Response_Date DATE, Response_Count INT)

INSERT INTO #test_dates (Response_Date, Response_Count)
VALUES ('8/3/2016',123),('8/12/2016',43)

DECLARE
  @JurisdictionName AS CHAR(30) = 'Something'
,  @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
,  @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1);

WITH all_dates AS
       (SELECT DATEADD(day, n - 1, @StartDate) AS Dates
          FROM dbo.Numbers n
         WHERE n.n <= DATEDIFF(day, @StartDate, @EndDate) + 1)
    ,response_dates AS
       (  SELECT Response_Date
                , Response_Count
            FROM #test_dates
           WHERE Response_Date BETWEEN @StartDate AND @EndDate)
SELECT a.Dates, ISNULL(Response_Count, 0) AS Respose_Count
  FROM all_dates a LEFT JOIN response_dates r ON a.Dates = r.Response_Date

The other way to accomplish it is to use a date table.  This table is very similar to the numbers table but it has all the dates for a time frame with some additional information about the dates.  Again you will find many different uses for the table for the limited amount of space that it will take.  See here for more information on how to create a date table and what you can use it for:

You could also use a recursive CTE to accomplish something similar, but it would likely have performance issues as things scale up.


Table function in oracle I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that will do that. Now, as an example, to see if I can get it working, I've created this so far:

create or replace type test_row as object (date_id number,day date)

and

create or replace type test_table as table of test_row

and written this function:

create or replace FUNCTION test_function
(
  p_year_start_in integer,
     p_year_end_in integer
) RETURN test_table AS

      v_ret test_table;
      p_start date;
      p_end date;

BEGIN
    select td.date_id, td.day
    bulk collect into v_ret
    from T_D_DATES td
    where date_id between p_year_start_in and p_year_end_in;

  RETURN v_ret;
END test_function;

but the compiler throws an error. Specifically is says:

PL/SQL: SQL Statement ignored for the select td.date_id, td.day part and

PL/SQL: ORA-00947: not enough values on  from T_D_DATES td

but I am not completely sure how to fix it. I've tried to make a PIPELINED
 function but got lost on how to insert all my values from my select into the table type.

Funtions aren't really my forte and especially ones that return a table as a result. So, how can I write a function that would work with the sample select?

P.S: I've looked online for solutions but it's either a super simple loop that writes some hardcoded values or something really complex and I can't decipher it.

That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below:

create or replace FUNCTION test_function
(
  p_year_start_in integer,
     p_year_end_in integer
) RETURN test_table AS

      v_ret test_table;
      p_start date;
      p_end date;

BEGIN
    select test_row(td.date_id, td.day)
    bulk collect into v_ret
    from T_D_DATES td
    where date_id between p_year_start_in and p_year_end_in;

  RETURN v_ret;
END test_function;
/


DBCC IND not working I'm trying to get the pages used by a clustered index on table1 in a SQL2008 R2 DEV box using the below query.

dbcc traceon(3604, -1)
go
dbcc ind (0, 'dbo.table1', 1)
go

but it still displays the below output and doesn't display the results.What am i doing wrong here?

Also the below message is logged in the SQL server log

  Message DBCC TRACEON 3604, server process ID (SPID) 59. This is an
  informational message only; no user action is required.

I was able to reproduce your issue and this might be one of the reason for no result.

Cause:

When you create a table and when no record is present in the table and you run dbcc ind on such table then you get the "message like below but NO tabular result".

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If table on other hand has records you would get tabular output.

For more understanding I suggest you read following blogs from Paul Randal

More about undocumented Command DBCC IND
How to use DBCC IND command to find out various internal SQL Server information

These blogs would help you in understanding how to use DBCC IND to get various information from database and how to interpret the result.


How to continuously keep two databases in sync Me and a friend are developing a range of programs which uses a basic currency called "points" each user can gain and spend points for free.

The problem we face is MySQL we can connect to the databases and execute query's etc however I recently purchased a new Data Server however we would like to use more than just one server for our project incase one should go down for any reason (DDoS/DoS, Maintenance).

We have done many searches and we have encountered "MySQL Replication" (we are still reading about this) however we would like to know if there was a program or something which would keep our servers in sync (a bit like DropBox) where if a user spends 20 points on a server and the other server is down a program would have a record of the 20 points and update the other server when its back online.

I would just like some ideas if you need anything clearing up let me know, personally I would like to use just my database but he seems insistent that we use his server but sometimes his internet can really drop.

This is a textbook use case for standard MySQL Replication.

You will need to decide if one DB will be the master and the other the slave in which case only the master replicates to the slave. If you want clients to be able to connect to either machine and issue writes and then have writes be propagated to both machines than you will need to look into Multi-Master replication:

http://mysql-mmm.org/

If you will be doing just standard master -> slave than MySQL has native tools as thats the default stack.


column name as variable in sql statement I have a MySQL database with a table that contains 4 columns, A, B, C, and D.
I need to access those fields dynamically i.e., instead of saying Table.col_A , I need to say Table.x where x can have the value of any of the columns A or B or C or D. I am getting x from the join with another table.

SELECT CASE another_table.field
           WHEN 'A' THEN main_table.field_A
           WHEN 'B' THEN main_table.field_B
           WHEN 'C' THEN main_table.field_C
           WHEN 'D' THEN main_table.field_D
           ELSE NULL
       END, ...


How to use TO_NUMBER function to update columns of a table Using oracle pl/sql Hope you're doing well.
I have a table with this structure :

Table1(Cust_No    Varchar2(50),
       First_Name Varchar2(50),
       Last_Name  Varchar2(50),
       Error_Code number(1),
       Error_Desc Varchar2(50))

At first , data will be inserted into column Cust_No and I should
Update other columns of the table based on this column.The point is that all cust_No must have number format. For example Cust_No ='aa674' does not have a correct format.
I need to write a query to update the table and set
column First_Name='--' , columnLast_Name = '--',
column Error_Code=1 and column Error_Desc='Incorrect format'
for those Cust_No which has incorrect formats.
I wanted to use TO_NUMBER() function but it gives me invalid number error.
how may I solve this problem?
Thanks in advance

As per your comment, requesting a regex solution:

update table1 set
  error_code = 1
, error_desc = 'Incorrect format'
where not regexp_like( cust_no, '^[0-9]+$' )

However, I would suggest that this is too little, too late.

You should not be allowing this defective data into the database in the first place!  (Unless this is part of a migration exercise and this is a transitional table).

Your application should be detecting this bad formatting and not even saving the record to the database.

Second Thought:

Alternatively, put the check into the table definition

[Air Code; untested]

create table Table1
( Cust_No Varchar2(50)
, CHECK( regexp_like( Cust_No '^[0-9]+$' )
. . .

Third Thought:

If you customer number is, indeed, a number, then why are you storing it in the wrong Data Type?

create table Table1
( Cust_No  NUMBER
. . .


How to create a job that uses Powershell script? I have a powershell script which deletes old backups from NAS storage. I have already tested it using Windows Powershell and it works but I couldn't perform the same thing in SQL Server. I have created a cmdExec type job and then wrote the script below (script is on desktop):

"C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe"

"C:\Users\domainAccount\Desktop\"

".\deleteold.ps1 -FolderPath \\192.168.x.x\Backups -FileAge 2 -ListOnly -LogFile \\192.168.x.x\Backups\log.log"

But I do not get any result as when I run the job it just keeps running infinitely until I stop that job.

You need to pass the powershell file in as an argument

powershell.exe -file C:\Users\domainAccount\Desktop\deleteold.ps1 -FolderPath \\192.168.x.x\Backups -FileAge 2 -ListOnly -LogFile \\192.168.x.x\Backups\log.log

Or if the step type doesn’t have to be CmdExe you can change the type to PowerShell


PostGIS radius query I have a table setup with the following columns, and it contains about 100K listing of data.

Column: address   |  Type: Text
Column: geo_point | Type: geometry(Point,4326)

My question is for each listing, how do I write the radius search query to find all the listing within 5 miles?

Best use ST_DWithin() in a LATERAL join:

SELECT t.*, neighbor.*  -- or only selected columns
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT *
   FROM   tbl t1
   WHERE  ST_DWithin(t.geo_point, t1.geo_point, 5000 * 1.609344)
   ) neighbor ON true
WHERE t.address = 'my_address';  -- to restrict to a particular address

1.609344 being the factor for converting miles to kilometers. Source: Wikipedia. Assuming meter as unit! The documentation:

  For Geometries: The distance is specified in units defined by the
  spatial reference system of the geometries. For this function to make
  sense, the source geometries must both be of the same coordinate
  projection, having the same SRID.

  For geography units are in meters

I think your SRID 4326 uses degrees, not meters. Instructions in this related answer:

ST_DWithin takes parameter as degree , not meters , why?

You need an index on geo_point to make this fast (used by ST_DWithin() automatically):

CREATE INDEX tbl_geo_point_gist ON tbl USING gist (geo_point);

A GiST index can also be used to identify nearest neighbors (in combination with a maximum number - LIMIT).

Related:

Order by distance


Ensuring that 1 row has 5 specific records referencing it Context: a TEST has many CASEs. Typical.

However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5).

I could do something like this:

create table TEST (
   ID           Integer                                   not null,
   CASE1_OK       varchar                                 not null,
   CASE2_OK       varchar                                 not null,
   CASE3_OK       varchar                                 not null,
   CASE4_OK       varchar                                 not null,
   CASE5_OK       varchar                                 not null,
   CHECK        (CASE1_OK IN ('PASS', 'FAIL', 'INVESTIGATE'))         ,
   ...
   constraint pk_TEST primary key (ID)
)   ;

That's obviously a violation of 1NF.

Is there a way of having a separate CASE table while ensuring that TEST record can not be inserted without having exactly 5 existing CASE rows for it?

This can be done with DDL alone but it's not at all trivial. The following will ensure that any test will either have 0 or 5 test cases:

create table test (
   test_id  integer  not null,
   constraint pk_test
      primary key (test_id)
)   ;

create table test_case (
   test_id       integer   not null,
   case_no       smallint  not null,
   case_no_prev  smallint  not null,
   case_ok       text   not null,
   constraint  pk_test_case
      primary key  (test_id, case_no),
   constraint  fk_test__test_case
      foreign key  (test_id)
         references  test (test_id),
   constraint  ck_case_ok
      check  ( case_ok in ('PASS', 'FAIL', 'INVESTIGATE') ),

   constraint  ck_case_numbers
      check  ( case_no between 1 and 5 ),
   constraint  fk_test_case__test_case
      foreign key  (test_id, case_no_prev)
         references  test_case (test_id, case_no),
   constraint  ck_cases_are_5
      check  ( case_no > 1 and case_no_prev = case_no - 1
            or case_no = 1 and case_no_prev = 5 )
)   ;


MySQL replicated tables for Cognos BI the Business Intelligence guy is asking for faster machines, latest software etc. and some of his customers are complaining of query times in excess of an hour.
The tool is Cognos/Insight and the DB is replicated copy of a master MySQL which we are shortly to migrate to MariaDB.

My gut feel ( before I get down and dirty with the SQL and Execution plans ) is that we are simply dealing with poorly optimised querys coming out of Cognos and an inappropriate collection of indexes to support them.

My Question is ( before I bespoke a solution ) :
Are there accepted strategies for realtime slave tables that allow for indexes that are not present on the master table?

Enable the slow query log and log_queries_not_using_indexes on the slave server and should be able to confirm what your gut is telling you.  You can take the captured queries, run EXPLAIN on them, and have some better documentation of your suspicions.

To more directly answer the question, it's absolutely valid to declare indexes on a slave that are not declared on the master.  I would suggest that it's a common practice, and one of many reasons why replicas are useful for so many things.  Connect to the slave with an account with sufficient privilege, and alter the table directly on the slave.

The only exceptions to this -- which should be intuitively obvious, since the row data needs to be consistent on master and replica -- is that you cannot safely declare a UNIQUE constraint or a foreign key constraint on a slave when it doesn't exist on the master.  This won't work, which is fine, since it doesn't make sense at any rate.

Remember also that indexes in MySQL have names, which are automatically generated if they are not supplied when the index is added.  A good practice might be to explicitly name these indexes on the replica so that future indexes added to the master cannot possibly cause an index name collision, which would break replication.  My local convention is to prefix the index name with "ix_repl_" which would of course never be used on the master.

ALTER TABLE t1 ADD KEY ix_repl_last_first (last_name,first_name);

Note also that proper duplicate indexes (where exactly the same columns, and no others, are included in more than one index) are deprecated in MySQL 5.6 and disallowed by default in MySQL 5.7, making it apparently possible in later versions to cause replication to stop if you subsequently declare an identical index (even with a different name) on the master.  This wouldn't be a critical issue (as long as you're monitoring replication -- you are, right?) since replication would be safe to restart by simply removing the now-redundant index on the replica before restarting the slave SQL thread.  The failed event would be retried, and would now be valid, since there's no conflicting index, and the replacement index would be built on the replica.

Side note: remember that MySQL replication requires the version of MySQL on the replica servers to be the same as, or newer than, the version on the master... so when upgrading (or migrating to MariaDB) you will almost certainly want to upgrade the replicas first, and then the master.  The reason for this is that a newer replica will understand the capabilities and quirks of an older master, but a newer master may introduce behaviors in the replication stream that an older replica server won't be able to interpret.  There are limited exceptions to this rule, but it's most definitely the rule.


How to check 'contains' in sql? Suppose 'allotedTo' column contains multiple values like 'ABC/xyz/RAM'
How do I load all rows, with 'allotedto' column containing any one of given input values?
(eg: all rows alloted to RAM or KLM) ?

Here, we don’t want the solution to repeat column name multiple time like below:

select * from table where [allotedTO] like '%RAM%'  OR [allotedTO] like '%KLM%'

We want to implement solution similar to below queries, but it is not working   :

select * from table where [allotedTO] like '%RAM|KLM%'

SELECT PATINDEX('%[RAM|KLM]%', [allotedTO])

For the specific example:

WHERE [allotetTO] LIKE '%RA[MJ]%'

For more complex ones, you could use a join to a values table with the wanted patterns:


you can create a recursive CTE that will result in a table returning all dates between the give dates, and then JOIN this with your result:

WITH abc as
(
 SELECT CAST('2019-04-20' as date) as i
 UNION ALL
 SELECT DATEADD(d,1,i)
 FROM abc WHERE i < CAST('2019-04-25' as date)
)
SELECT
  ISNULL(AVG(Value),0) AS VALUE,
  CAST(Timestamp AS DATE) AS TimeStamp
FROM abc
LEFT JOIN MyTable on TimeStamp = abc.i
GROUP BY abc.i


Is XFS still the best choice for mongodb? There is this guide for production servers:

https://docs.mongodb.com/manual/administration/production-notes/#kernel-and-file-systems

It tells that for the WiredTiger storage engine, XFS is strongly recommended against ext3 or ext4. But that article must be very old. Nowdays we are using Linux kernel 4. The 2.6 series of kernels date back to 2003. That is incredibly old.

So here comes the question: did something change in the last 14 years? (This sounds a silly question in the IT field, and it feels ridiculous to write down.) Is XFS still prefered for a production MongoDb cluster? Or should we use ext4 instead, or something else?

The provenance of information on the MongoDB production notes isn't apparent at the moment, but WiredTiger and the recommendation to use XFS was definitely added much later than the Linux kernel details. The production notes share collective experience from known issues, but are typically recommendations rather than strict guidance. Most notes are added at the time a widespread problem is observed, but circumstances could certainly change.

  It tells that for the WiredTiger storage engine, XFS is strongly recommended against ext3 or ext4.

The specific recommendation against ext4 is based on observed stalls during WiredTiger checkpoints as reported by a number of production users. Per SERVER-18314 (May 2015), there is a likelihood of stalls when ext4 journaling coincides with WiredTiger checkpoints. This may not be an issue for all workloads, but is a significant enough caveat that a startup warning was added in MongoDB 3.4 (released Nov 2016) for deployments using ext4 with WiredTiger. There have been no similar reports with XFS, and it has been observed to generally perform better with MongoDB.

There are other available filesystems on Linux (ZFS, btrfs, ...) but these are currently not widely used in production (as compared to ext4 and XFS) so XFS is the recommended filesystem based on testing and experience.

  Nowdays we are using Linux kernel 4. The 2.6 series of kernels date back to 2003. That is incredibly old.

While most distributions have moved on from the 2.6 kernel, there are some Enterprise stalwarts like RHEL6 that will not upgrade the default kernel during their release support lifecycle. Redhat does cherrypick and backport fixes with their own kernel subversion, but will remain on the original kernel base from the time of release. RHEL has a 10-year support policy followed by 4 years extended lifecycle support, so old versions tend to remain in the field much longer than you might expect. RHEL6 moves into extended support in 2020, so there are still new deployments using the 2.6 kernel in 2017. RHEL7 uses the 3.10 Linux kernel and won't reach extended support until 2024.


Insert and Update with AES_ENCRYPT is mangling character set on double byte script DB and fields are UTF8_unicode_ci collation - MySql v 5.6

HTML charset is utf 8

Double byte script was entered successfully prior to this issue (Chinese, Japanese, plus Cyrillic etc) when using plain non encrypted input.

But trying to use AES_ENCRYPT on input mangles all non-english script.
DB field is BLOB.

 UPDATE mytable SET bodytext=AES_ENCRYPT('任車心是，防少劇和的；子反華不就','aeskey') WHERE t_id=123

produces strings of question marks - English is fine though

I'm not sure if I should try to CONVERT the input to UTF8 first - but when I try that I get an error.
Any suggestions of what to review?

Forcing utf8mb4 does the trick. Run a query against MySQL immediately after making the connection with:

SET NAMES'utf8mb4';

Alternatively, use these settings in your my.cnf file to get the same effect:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

(Note: These settings are defaults (for the most part) in MySQL 8.0 and MariaDB 10.3, so this probably isn't a problem there.)

Chinese (and other) scripts are multibyte - standard utf8 allows 3 bytes per character - fine for plain text but using AES_ENCRYPT ON 3 bytes requires another byte - thus setting utf8mb4 allows the required 4 bytes per character.


RETURNING value from SELECT table in INSERT .. SELECT I have a table that looks like this:

| column | def  |
| ------ | ---- |
| id     | uuid |
| status | int  |
| data   | text |

I'd like to write a query to duplicate a row (changing only a few columns) and return the old (replaced) data:

INSERT INTO tmp (status, data)
SELECT source.status, :data
FROM table AS source
WHERE id = :id
RETURNING id, source.data

but RETURNING can only access values from the inserted row, is there an alternative to do this?

You can 'store' your old data in a CTE, then do the change in another (writable) CTE, and return the original values like this:

WITH old AS (
    SELECT status, data
      FROM your_table
     WHERE id = :id
),
new AS (
    INSERT INTO your_table
    SELECT status, :data
      FROM old
)
SELECT * FROM old;

The writable CTE (new) works on the results of the previous one (old).


Best Way To Create Time Series from Postgres Table Update History? I have a Postgres Table (500 rows, 10 columns) that gets updated every 6 hours by a Daemon (that performs some calculations) and executes a series of row-by-row upsert operations. How can I construct day-by-day time series of the state of the table? Is there some way to build this by walking through the update/operation history? Are there any frameworks/techniques out there to make this easy?

Table will grow to max of 5000 rows.

You could maintain a history table that has the same structure as the original table plus a timestamp column.

Then create a trigger that copies the old row into that history table before applying the UPDATE. During the INSERT into the history table, the trigger would populate the timestamp column to store the point in time when the change occurred. The UPDATE will then proceed normally on the main table.

Depending on your needs you might also want to store new rows when they are INSERTed or the old rows before they are DELETEd.

This approach typically does not require any changes in the application. To find out old "versions" of your data, you need to query that history table, rather than the real table. Whether that is acceptable I can't tell.


Oracle error "too many values", must be solved with nested SELECTs I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs.

A simplified description of my problem, on a toy problem describing the hierarchy in a company. There are two tables:

ACG_EMPLOYEES, which lists all the employees

ACG_BOSS_OF, which simply lists who (b_id) is the boss of whom (e_id)

I can do

SELECT b_id as boss,
             LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
             KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
             e_id,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
      FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1

to get the list of "minions" for every employee which is not a leaf in the hierarchy:

But let's say I also want to get other fields from the ACG_EMPLOYEES table, in this case the name for each boss:

I have tried

SELECT
emp.name,
(SELECT b_id as boss,
             LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
             KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
             e_id,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
             ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
      FROM acg_boss_of)
GROUP BY b_id
CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
START WITH curr = 1) minion
FROM acg_employees emp

but I get the "too many values" error I mentioned.

I have seen some suggestions for solving this kind of issue; however, I cannot use any of them, since this statement will be used in a Product Lifecycle Management app for which this must be defined as a single SELECT, no matter how complicated (ok, I could use JOINs as well, but no stored procedures or such).

Using a join?

SELECT
  minion.boss,
  emp.name,
  minion.minion
FROM acg_employees emp
JOIN
    (SELECT b_id as boss,
                 LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
    FROM (SELECT b_id,
                 e_id,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
          FROM acg_boss_of)
    GROUP BY b_id
    CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
    START WITH curr = 1) minion
ON (minion.boss=emp.e_id)


MySql replication failed with error 1236 data position > file size with relay-bin file not increasing My slave machine reset and now on SLAVE SHOW STATUS\G I have the error:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

The slave settings are showing as:

Master_Log_File: bin.003767
Read_Master_Log_Pos: 751854814
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.003767
Slave_IO_Running: No
Slave_SQL_Running: Yes

However the relay-bin files on the slave are: mysql-relay-bin.000001 with 1KB in size. The Master on the other hand has the files: bin.003767 at over 700 megs. But no relay files are being updated or copied to the slave.

Is there any way to resolve this without a full data synchronization from beginning? It takes days to achieve this.

Does anyone know what the issue is here?

Much appreciated!

To avoid the problem in the future, turn on sync_binlog.

What is happening is that the replication stream had not yet gotten to the Slave when the Master shutdown.  The offset into the binlog had been bumped up, but the data was not on the Slave.

After coming up, move to the next binlog and set the position to 0 (or 4, either one works).


Should an OFFLINE database be SET EMERGENCY before dropping? As a rule before dropping a database I, SET OFFLINE, for 30 days before dropping it.  If there is anything that was missed and the database is still needed, I can SET ONLINE quickly for a full restore.

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

If you drop a database while it is offline, the files stay on the server forever unless you purposely go clean them up.  Assuming the you have taken whatever backups you want and are sure you want to completely drop the database on the server, you put it back online then drop it.

USE [master]
GO
ALTER DATABASE database_name
SET ONLINE

DROP DATABASE database_name
GO

The database comes fully back online then gets dropped.  Might it be a better practice to SET EMERGENCY before dropping?  Depending on many variables, in theory when you go online a missed OLTP connection could access the database between ONLINE and DROP.

Using SET EMERGENCY, grants "READ_ONLY to members of sysadmin and brings the database far enough online so that files on the server are deleted. At least with my limited testing on SQL2014

USE [master]
GO
ALTER DATABASE database_name
SET EMERGENCY

DROP DATABASE database_name
GO

If a missed OLTP (or anything) manages to connect to the database between ONLINE and DROP you would have deleted any record of it with the drop.

Might it be a better practice with an OFFLINE database to SET EMERGENCY before dropping?  If you are using T-SQL is it to quick for any possible connection to occur?

If you are using the Delete Option on SSMS, even best case between online and delete, is going to measurable in seconds. Should this always be avoided?

Note: make sure you have a final full backup of the database before you drop it, just incase if you need to restore.

Normally, EMERGENCY mode is used for getting your database out of corruption.

You are right, that EMERGENCY mode will allow only sysadmin members and the db will be read-only - nothing gets written to T-LOG.

I would suggest that get the database to RESTRICTED USER and then drop it.

e.g. ALTER DATABASE your_db_name SET RESTRICTED_USER or READ_ONLY

Note: This is just my preference which distinguish between EMERGENCY mode --> use to take DB out of corruption vs RESTRICTED_USER or READ_ONLY --> give it chance to breathe before you drop !


ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER I give an example to show my problem. I create a table as the following:

CREATE TABLE a
(
  id INT
)

I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"

CREATE TRIGGER [dbo].[insert_a]
ON [dbo].[a] AFTER INSERT
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK;
 END
SELECT * FROM inserted
END

Then I insert id=1 into table "a":

INSERT INTO a VALUES(1)

I get nothing from INSERTED table.

I realize that when I ROLLBACK then:

the data in table "a" was ROLLBACK (I know)
data in INSERTED table is also removed.Why is that?

If I change AFTER INSERT into INSTEAD OF INSERT in trigger.

ALTER TRIGGER [dbo].[insert_a]
ON [dbo].[a] INSTEAD OF INSERT
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK
 END

SELECT * FROM inserted
END

INSERT INTO a VALUES(1)

Then I get the result:

id
1

That means data in INSERTED table is not removed though have been ROLLBACK.

Help me explain?

Your row is not being inserted in either case.

For after insert trigger rollback is part of the transaction. See this Q&A for details.

SQL Server - After Insert/ For Insert - Rollback

Quote from the above Q&A.

  You're inserting one (or multiple) row into your table. Then - still
  inside the transaction - the AFTER INSERT trigger runs and checks
  certain conditions - typically using the Inserted pseudo table
  available inside the trigger, which contains the rows that have been
  inserted.

  If you call ROLLBACK TRANSACTION in your trigger, then yes - your
  transaction, with everything it's been doing, is rolled back and it's
  as if that INSERT never happened - nothing shows up in your database
  table.

For instead of trigger the id value of 1 you are seeing is not from the table. It is from the code within your trigger.  This value of 1 is coming from:

SELECT * FROM inserted

Run this code and you will not get a record back.

ALTER TRIGGER [dbo].[insert_a]
ON [dbo].[a] INSTEAD OF INSERT
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK
 END

SELECT * FROM inserted
END
GO
INSERT INTO a VALUES(1);
GO
SELECT * FROM dbo.a;
GO


Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets? I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks the tables up into multiple files.

Have you used sharding or horizontal partitioning before? If so, could you suggest any patterns that, in your experience, achieves better results compared to a single massive table?

First, note that 1 billion rows can be handled effectively with a partitioned architecture on ordinary commodity server hardware.  Exotic shared nothing architectures will not be necessary for this data volume, however, you will probably get significant benefits from table partitioning.

Sharding is something different from horizontal partitioning, and implies a 'shared nothing' architecture, which is not supported by most versions of SQL Server1

SQL Server can support horizontal partitioning, and a shared disk architecture will be adequate for ~1 billon rows.

In SQL Server, you create a partition function selects a partition based on values or ranges of values in a column on a table, e.g.

create partition function F_AccPrdPart (int)
    as range left for values (
    180001                         -- Dummy value
   ,199012 ,199112 ,199212 ,199312, 199412 ,199512
   ,199612 ,199712 ,199812 ,199912 ,200012 ,200112
   ,200212 ,200312 ,200412 ,200512 ,200612 ,200712
   ,200812
   ,200901, 200902, 200903, 200904, 200905, 200906
   ,200907, 200908, 200909, 200910, 200911, 200912
   ,201001, 201002, 201003, 201004, 201005, 201006
   ,201007, 201008, 201009, 201010, 201011, 201012
   ,201101, 201102, 201103, 201104, 201105, 201106
   ,201107, 201108, 201109, 201110, 201111, 201112
   ,202012
   ,939999                         -- Dummy value
)
go

Then create one or more filegroups to allocate the partitions to.  For a large data set, these file groups could be set up on different physical volumes.  Note that direct attach storage will be much faster than a SAN for this in almost all cases.  In the example below, we would have created 6 filegroups called PartVol1-PartVol6.

One or more partition schemes can be created to allocate table partitions to filegroups based on the value of the partition function, e.g.

create partition S_AccPrdPart as partition F_AccPrdPart
    TO ([PRIMARY]
      ,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
      ,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
      ,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
      ,[PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2]
      ,[PRIMARY]
      ,[PRIMARY])
go

This scheme is designed to partition on an accounting period.  Dates are also frequently used for this, although any key could be used.

You can create a table on the partition scheme as if it was a filegroup, e.g.

Create table FooTrans (
       FooTransID        int identity (1,1) not null
      ,AccPeriod         int not null
      ,[...]
) on S_AccPrdPart (AccPeriod)
go

Note that table is created on the partition scheme instead of a specified file group, and the clause specifies the column to be used as the partition key.  Based on the partition key, rows in the table will be allocated to one of the filegroups in the partition scheme.

Note: A rule of thumb for designing a partitioning scheme is that each partition should have a row count in the low 10's of millions, say between 10 and 50 million depending on the width of the rows.  The disk volume that the partition sits on should be fast enough to do a scan of at least a single partition in a few seconds.

Partitioning, Sharding and Shared Nothing systems

A bit of terminology seems in order here to disambiguate some of the discussion on this topic.

A 'shared nothing' system is a parallel system where the nodes do not have shared SAN storage, but use storage local to the node.  The classic example of this type of architecture is Teradata.  Shared nothing systems scale well to very large data sets as they have no central I/O bottlenecks.  I/O throughput scales with the number of nodes in the system.
A 'shared disk' system is one where one or more database servers share a single disk storage subsystem.  The database can be a single server with either local storage or attached to a SAN, or a cluster of servers attached to a shared SAN.  Systems of this type are constrained by the throughput available from the storage subsystem.
'Sharding' is a term used to describe splitting a database amongst multiple physical servers in a shared nothing architecture.  Various platforms will have greater or lesser support for sharded databases.  In Teradata circles, the term is not used because Teradata presents a transparent single system image to the clients, even though the physical architecture is a shared nothing type.Older versions of SQL Server have limited support for sharding through distributed partitioned views.  Microsoft now make a version of SQL Server 2008 R2 that supports a shared nothing architecture with a single system image, but this version is only available to OEMs and can only be purchased in a hardware bundle.

For 1 billion rows

For 1 billion rows (unless the individual rows are extremely wide) a shared nothing or sharded architecture is comfortably in the realms of overkill.  This type of volume can be handled on a single server of reasonable spec if it has an adequately fast disk subsystem.

Local direct attach disk is by far the most cost effective in terms of price for performance.  A single SAS RAID controller can take several arrays, and multiple controllers can be installed in a server.  Depending on the configuration, a modern 24-25 slot SAS array can put through thousands of IOPS or 1GB+/sec in streaming performance; a server with multiple PCI-e busses and multiple controllers can theoretically handle more.

The type of performance necesary to work with a 1 billion row database can be achieved quite easily and cheaply with commodity server hardware and direct attach storage of this type.  A SAN could also be used, but you might need multiple SAN controllers to get equivalent performance, and the hardware is likely to be an order of magnitude more expensive.

As a general recommendation, use direct attach storage for applications with heavy I/O
requirements unless you need really good uptime.  Configuration and
change control errors are a far bigger source of unscheduled downtime than hardware failure in modern data centre ops.

SANs can give you a more manageable storage platform if you have a large portfolio of
applications as they give you a range of centralised storage management facilities.  However, this comes at a steep price and getting high performance out of SAN based infrastructure is difficult and expensive.

1 Microsoft do make a parallel version of SQL Server, but it is only available through OEM channels bundled with hardware.  The versions available off the shelf do not support this capability.


Columnstore index query performance based on number of columns in SELECT statement I have a table 'FactsTable1' which is horizontally partitioned on basis of date & it also has clustered columnstore index on it. I am running following queries on it.

Query 1 :

SELECT
    SUM([Column1]),
    SUM([Column2]),
    SUM([Column3]),
    SUM([Column4]),
    SUM([Column5]),
    SUM([Column6])
FROM [FactsTable1]

Query 2 :

SELECT
    SUM([Column1])
FROM [FactsTable1]

When I ran above 2 queries with 'STATISTICS IO,TIME ON', I received following as output.

Query 1 :

SQL Server parse and compile time:
   CPU time = 94 ms, elapsed time = 95 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'FactsTable1'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 1184, lob physical reads 31, lob read-ahead reads 0.
Table 'FactsTable1'. Segment reads 22, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 247 ms.

Query 2 :

SQL Server parse and compile time:
   CPU time = 46 ms, elapsed time = 57 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'FactsTable1'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 198, lob physical reads 0, lob read-ahead reads 0.
Table 'FactsTable1'. Segment reads 22, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 52 ms.

I can see some difference always in CPU time when checked multiple times but I dont see any difference in query cost. Its always 50-50%.

So I want to know, does number of columns affect performance of a query for a partitioned table with clustered columnstore index? If yes, then is there any way to measure performance difference?

Additional information :

FactsTable1 contains 400000+ rows & 240 columns
Its partitioned on date column
It is heavily queried for analytic data.
@@version : Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

So I want to know, does number of columns affect performance of a
  query for a partitioned table with clustered columnstore index?
  If yes, then is there any way to measure performance difference?

Yes, the number of columns is often a significant factor for columnstore query performance as shown in shown in the last line your STATISTICS IO output. This is because columnstore allows data for only the columns needed by the query to be scanned. The elapsed time difference (247ms versus 52ms) indicates query performance is proportional to the number of columns (as I would expect), with elapsed time being the primary measure of performance.

Although one can generally use elapsed time as the primary performance indicator, there are other factors to consider. Cached data or other activity on shared resources could affect timings.  Both plans are serial in this case so elapsed times are comparable for relative performance, with the assumption of a repeatable test in an isolated environment. However, if one plan were serial and the other parallel, the parallel plan could consume more CPU time than the wall clock time and result in a biased or incorrect conclusion (e.g. the query with more columns runs faster).


Table doesn't exists issue with Oracle stored procedure I am new to Oracle and in process of converting SQL Server stored procedures into Oracle stored procedures.
I was able to transform the SQL statements and have tested them individually and they are working fine as expected.

However facing some challenges when trying to execute them in Oracle stored procedures.

For example I have a stored procedure TEST_PROC wherein I am creating a TEST_TABLE_2 using existing table TEST_TABLE_1

BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE TEST_TABLE_2 as
 SELECT
 COL1,
 COL2,
 COL3
 FROM TEST_TABLE_1';
END;

and then inserting data to some other table with the help of table TEST_TABLE_2

INSERT INTO TEST_TABLE_MAIN
(
COL1,
COL2,
COL3
)
WITH CTE_TEST_TABLE_DATE AS (
SELECT
a.COL1,
a.COL2,
a.COL3
FROM TEST_TABLE_2 a LEFT JOIN
 (SELECT GROUP_ID FROM TEST_REF_TABLE) b
 on a.COL1= b.GROUP_ID
)
SELECT * FOM CTE_TEST_TABLE_DATE;

As you can see that TEST_TABLE_2 is getting created on the fly in this procedure
and will be getting dropped in next subsequent procedure.

when I am running this TEST_PROC I am facing below issue which is probably due to that table doesn't exists.

exec TEST_PROC;

  PLS-00905 object SYSTEM.TEST_PROC is invalid

The table does not exist at compile time, so with static SQL, the procedure remains invalid.

Run the INSERT from dynamic SQL as well (execute immediate).

Or even better, do not create tables on the fly.