Designing a join table for max n records

In that case the application should manage the amount of products on each order.

If you want to enforce a limit on database level you can add a column ProductOrderPosition in OrderProduct that's the ordering position of each product by each order, and you can add a CHECK constraint to limit this value, for example:

ALTER TABLE OrderProduct ADD CONSTRAINT CHK_OrderProduct_ProductOrderPosition
    CHECK (ProductOrderPosition <= 10)

If you don't want additional columns you could validate with an after insert trigger, checking that each order doesn't have more than X products.

CREATE TRIGGER dbo.utrOrderProductCheckAmount ON OrderProduct AFTER INSERT
AS
BEGIN

    DECLARE @v_ProductsByOrderLimit INT = 10

    IF EXISTS (
        SELECT
            'more than allowed!'
        FROM
            OrderProduct AS O
            INNER JOIN (SELECT DISTINCT I.OrderID FROM inserted AS I) ON O.OrderID = I.OrderID
        GROUP BY
            O.OrderID
        HAVING
            COUNT(1) > @v_ProductsByOrderLimit
        )
    BEGIN

        ROLLBACK

        RAISERROR('Maximum products by order limit reached.', 11, 1)

    END

END

You definitely don't want to store pivoted Products IDs on the Order table for the reasons you mentioned before. I'll add another reason, what happens if you want to know in how many orders does a product appear? You need 10 poorly indexed joins, instead of 1 optimized SELECT against OrderProduct.


Why Does ad_zd_table.upgrade Convert Triggers To Point at the # View?

I believe now this is by design. The solution is to point the source code for insert/update/delete statements at the editioned # view of the table. Then the triggers work. Alternately, you can create a synonym and point the insert/update/delete statements at the synonym. I'm not sure which is a better solution--although everything I see on the web is recommending the synonym approach. (Specifically putting the synonyms in the APPS schema. You can't put a synonym with the same name as the table in the same schema as the table, but I'm not sure why the APPS in particular is recommended, or why the synonym approach is recommended. If anyone knows the answer to that please comment.)

For more info:
Oralce: Why Do Triggers on Editioning Views in My Custom Schema Fail?


AWS Aurora RDS 2nd reader gets no queries

We have also faced the same issue and it got resolve after reduced the DNS TTL value in our Java application.

Amazon Aurora for MySQL or PostgreSQL distribute workload between reader nodes as below (From AWS Manual)

  Aurora uses a  DNS based round-robin load balancing mechanism for
  balancing connections between the reader instances.  This load
  balancing is handled through Amazon Route 53's DNS service.  This
  means that your application makes a DNS query for each new connection
  request. Each time the reader endpoint is resolved, the instance IP of
  one of the reader is provided for the connection. In order to get the
  instance IP of a different reader each time, one must keep resolving
  the endpoint without caching DNS. Aurora DNS cache uses a short Time
  to Live (TTL) of  5 seconds. To ensure both the readers are equally
  utilized, care must be taken not to increase the TTL more than 5
  seconds for a client and network configuration. Caching DNS responses
  for longer than 5 seconds can result in overutilization of one reader
  over others as you have been experiencing.

In your application check the DNS TTL set for the application and adjust it down to 5 seconds.


Remove nulls from TSQL connection string

SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not think it is a database library issue--using different drivers won't change a thing. This is just the way SQL Server behaves.

If you want to convert NULLs to something else, you'll need to explicitly use the IsNull(Expression, ValueIfNull) function or the similar Coalesce() function which takes multiple parameters and returns the first non-null value.

SELECT O.OrderID, IsNull(CustomerName, '') CustomerName
FROM
   dbo.OrderTable O
   LEFT JOIN dbo.CustomerTable C
      ON O.OrderCustomerID = C.CustomerID

Though one comment... why would there be an order with no customer? Wouldn't a customer with no order be more likely?

Update

Can you put in a wrapper that, client side, converts the NULLs to what you want? What process or program are you using to convert the result sets to text?


Quoting columns with spaces in PostgreSQL?

In most RDBMSs, double-quotes are what are used to specify an exact spelling of something..  (single quotes being string delimiters).

SELECT
  tab."This IS My Column EXACTLY" AS col
FROM "My TabLE Name Contains Spaces Too!" tab
WHERE tab."ANOTHER UGLY COLUMN name" = 'MyFilterString';

Notice that capital/lowercase also matters when using double-quotes.  (Postgres lower-cases everything when double-quotes are not used ... Oracle upper-cases everthing, etc..)

SELECT COLUMN1 FROM TABLE

in postgres, is different from

SELECT "COLUMN1" FROM TABLE

where as in oracle, is different from

SELECT "column1" FROM TABLE


Taking the difference in timestamps between successive entries

You don't need a self join of any kind. Just use variables.

SELECT
time_recorded AS current,
@prev AS previous,
timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous,
@prev := time_recorded
FROM
reading
, (SELECT @prev := null) var_init_subquery
WHERE device_id = 1154
ORDER BY time_recorded

What's important in this query is first the ORDER BY and then the ordering in the SELECT clause.

@prev AS previous, /*here it outputs the value of the previous row*/
@prev := time_recorded /*here the value of the current row is assigned*/

The subquery in the FROM clause is just a fancy way to not have to write "two queries" like this:

SET @prev := NULL;
SELECT
time_recorded AS current,
@prev AS previous,
timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous,
@prev := time_recorded
FROM
reading
WHERE device_id = 1154
ORDER BY time_recorded


How to check if a PostgreSQL table is temporary?

Quote from the manual

  relpersistence:  p = permanent table, u = unlogged table, t = temporary table


The disappearing act of the "Invalid length parameter passed to the LEFT or SUBSTRING function" error

I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

Specifically what Aaron Bertrand mentions in his answer:

  ... because you can't always rely on SQL Server filtering rows before attempting calculations

What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.

Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.

To avoid the issue, I suggest you use CASE, replacing

LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)

with:

LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
                         THEN CHARINDEX('@', Email.Locator) - 1
                         ELSE 0
                    END)


It's important to remember that the query optimizer doesn't choose each join individually and independently from everything else going in on the query. Joins have different properties which means that a different join type may be better or worse depending on other joins or operations in the plan. I can generate test data that shows behavior similar to what you see here, but the reproduction depends on available memory for the server.

Put 10k rows each into two tables:

CREATE TABLE dbo.tbl_detail (
    id bigint PRIMARY key,
    descr varchar(300),
    descr_p varchar(300),
    txt_max varchar(MAX),
    txt_5k varchar(5000)
);

INSERT INTO dbo.tbl_detail WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('DESCR', 60)
, REPLICATE('descr_p', 40)
, NULL
, NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE TABLE dbo.OUTER_TABLE (
    id bigint primary key,
    id2 bigint
);

INSERT INTO dbo.OUTER_TABLE WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Here is the query to test:

SELECT o.*, d.id, d.descr, d.descr_p
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d ON o.ID2 = d.ID
ORDER BY o.ID;

If I add d.txt_5k to the SELECT list I naturally get a hash join:

However, if I add d.txt_max to the SELECT list I naturally get a loop join:

The key here is that a loop join preserves the order of the outer table. That means that a nested loop join can avoid an explicit sort for some queries. Queries with a hash join may need to do the sort. The cost of the sort depends on (among other things) the estimated size of the data. The query optimizer estimates the size of the data based on the estimated number of rows and the data types. With the VARCHAR(5000) column I get an estimated size of 27 MB and with the VARCHAR(MAX) column I get an estimated size of 42 MB. On my machine the 42 MB sort is expected to spill to disk so that makes the hash join plan much more expensive than the loop plan for the VARCHAR(MAX) column.

We can see this more clearly by using hints to force the different plans so we can compare them. Here's the comparison for the VARCHAR(5000) query:

And the comparison for the VARCHAR(MAX) query:

Of course, this may not be why you're seeing the behavior that you're seeing for your particular query. I just wanted to provide an example of why changing a data type could change the join type. I'm going to assume that there's a problem caused by the loop join and that you really need it to be hash join. The OPTION (HASH JOIN) hint may not be a good workaround because it will force every join to be a hash join in the query. The LEFT HASH JOIN hint may not be a good workaround because it implies a FORCE ORDER hint which means that the query optimizer will not be able to change the join order. Perhaps you can move the problematic part of the query to a temp table and apply whatever hints are needed to the smaller query. It's really hard to say much without more information.

You could also try a FORCESCAN hint to encourage the hash join, although I'm loathe to recommend it. Be sure to test carefully:

SELECT o.*, d.id, d.descr, d.descr_p, d.txt_max
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d WITH (FORCESCAN) ON o.ID2 = d.ID
ORDER BY o.ID
OPTION (MAXDOP 1);


Beyond some point, waiting too long to COMMIT slows things down.  Think of it as having to save more and more "undo" stuff.

innodb_autoinc_lock_mode only applies to AUTO_INCREMENT.

It seems that your real question is about high speed ingestion.  Are you INSERTing one row at a time?  You can get 10x speedup if you insert 100 at a time.  That is, a single INSERT with 100 rows.  Are you inserting from multiple connections?  Etc.  Here's one discussion on high speed ingestion.


Maintenance Plan for all Online databases doesn't execute for all databases

In your Backup task, is your Backup Type set to Full?  If it is set to Differential then a backup may not be taken of every database.


DB2 - How to get value for: Transation Per Minute parameter for DB?

There is no "per minute" metric available, you will need to query the monitor repeatedly and divide the delta of the metric you're after by the time interval since the previous query. The following two queries will return the total number of application units of work and the total number of completed units of work, respectively.

SELECT
   TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS
FROM SYSIBMADM.MON_DB_SUMMARY

SELECT
   SUM(TOTAL_APP_COMMITS) +
   SUM(INT_COMMITS) +
   SUM(TOTAL_APP_ROLLBACKS) +
   SUM(INT_ROLLBACKS)
FROM TABLE(MON_GET_WORKLOAD('',-2))

There are other monitor views that you may find useful: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0023485.html#r0023485__mon_23485

You can obtain the same information using the GET SNAPSHOT command, but the use of old-style snapshot monitor is not recommended. For example, you can try this:

db2 get snapshot for database on sample | grep -Ei "commit|rollback"


What is the right data type to store only -1 0 and 1 in postgres column?

If you want to save space, you can use the "char" data type. It stores a single byte.

you can cast integer or text to "char":

SELECT 'u'::"char", 'd'::"char", 'n'::"char";

 char | char | char
------+------+------
 u    | d    | n
(1 row)

An enum uses 4 bytes since it is internally stored as a real.

What you are out to save space, you'll have to take alignment into account. The values are always aligned according to the type alignment. For example, a bigint always has to start at an address that is divisible by 8.

Now if your table is defined as

CREATE TABLE (
   smallflag "char",
   largenum  bigint
);

there will be 7 padding bytes between the columns, which would render all the space gains from "char" moot.

So place your table columns carefully.


Best practices for sql server log shipping method

I supposing you are using Alwayson AG with a SQL server enterprise edition (because you are in a business critical production environment). Why don't you add the DR instance as a node of the alwayson AG cluster? It's free and included in the license you already own.
Instead if you are using basic availability group, you are limited to one copy.
So you have to look up for something like log shipping.
Log shipping is based on a share that plays as a repository for tlog backup between primary and distare recovery site.
After saying that, you have to evaluate that the source environment is composed by two nodes that have to use the same repository for LS tlog backups. So a network path is more friendly and reliable.
The risk of having someone to disrupt the share you created is something you have to handle with security and rights in your environment; it should not prevent smart architectural choises.


High tempdb disk I/O during merge replication of BLOBs

There is a TechNet blog that discusses some merge replication issues with blobs on a SQL Server 2008 or higher server.

http://blogs.technet.com/b/claudia_silva/archive/2011/10/31/replication-watch-out-for-stream-blob-columns-when-setting-up-replication-on-your-sql-2008-server.aspx

Note that the author cautions about which settings to use when there are SQL Server 2005 clients, such as you have.


I'm trying to create a database for a hotel company with multiple hotels. I'm stuck on how to reserve a room?

I started making a comment but noted that it would be too long. I'm not sure what parts of your ERD are given, and what parts you have created. Anyhow, here are some reflections
A room exists in a hotel, no? Assume your hotel H1 have 10 rooms and hotel H2 have 20 rooms. What prevents someone from booking room 11 in H1?
I don't understand your entity for availibility. If there are no bookings, nothing is availible? I would say that availibility is defined as (All dates x All rooms) - Bookings. Question is, do you need to store this somewhere, or can you derive it?


Too many TIME_WAIT connections in PostgreSQL

1500 to 2200 concurrent connections are only useful when you need them. Otherwise, bring the total number of open connections down and start using a connection pool like pgBouncer or PgPool-II. See Number of Connections (on the wiki). We do about 2500 transactions per second on 150 connections.

Don't forget, each connection uses RAM, RAM that could be used to get some real work done. PostgreSQL gets slower when you use too many connections without a connection pool. What "too many" is depends on your hardware and workload, but it's very unlikely that your system will perform better with 2000 than it will with 100; probably much worse.


Efficient way to check for number of records more than a limit from a table in SQL Server

CREATE NONCLUSTERED INDEX IX_StagingStatusId ON Accounts(StagingStatusId)INCLUDE(IsActive, IsApproved);

SELECT T.StagingStatusId
     ,'StatCounts'= CASE WHEN T.Total>999 THEN '>999'
                         WHEN T.Total<999 THEN '<999'
                       END

 FROM (
select StagingStatusId, count(*) AS Total
from Accounts
where IsActive = 1 and IsApproved = 1
group by StagingStatusId
) AS T


If I run a REINDEX ( organize or rebuild ) is my database going to be
  smaller?

No, after reindex or rebuild database size may only increase. This is because when you do a rebuild(offline), a new copy of index will be built, and only then the old index will be dropped. This means that at a certain moment your db will accommodate 2 copies of the same index.

I.e., if your database is 1Tb with 10Gb free, and inside this db you have an index of 500Gb, after offline index rebuild the size of your database will be nearly 1,5Tb with 500Gb free.

This is without log growth. Log file will grow in any case, it can grow less if you rebuild offline and database recovery model is simple or bulk logged. The log will grow proportionally of your index size if you rebuild it offline in full recovery model (because every index page will be fully logged). Finally, if you rebuild online or reorganize, the log will grow even more because these operations are fully logged. (Note: if index is not fragmented, reorg will do nothing so the log will not grow).

Rebuilt index will not preserve ghost rows, but this does not mean that database size decrease.

This miens that unused space of your database will increase. But these free pages will still reserved to database.

The only mode to decrease database size is to shrink it. I still did not understand if you want to shrink or not. Shrinking means to release free database pages to OS. It is resource intensive process that is not recommended unless you really need to release some space to OS.


Does MySQL close cursor if stored procedure encounters an exception?

use Approach #1:
according to http://www.brainbell.com/tutorials/MySQL/Working_With_Cursors.htm

If you do not explicitly close a cursor, MySQL will close it automatically when the END statement is reached.


varchar work is good enough for a lot of Western European languages (Norwegian, Danish, German, French, Dutch etc too) subject to some collation issues
See this on SO varchar vs nvarchar performance nvarchar has serious performance implications
This is trivial compared to dealing with dates MDY vs DMY


Parsing, Planning and Optimizing Prepared Statements

The PostgreSQL doc for PREPARE is clear about the advantages of prepared statements in terms of performance:

  PREPARE creates a prepared statement. A prepared statement is a
  server-side object that can be used to optimize performance. When the
  PREPARE statement is executed, the specified statement is parsed,
  analyzed, and rewritten. When an EXECUTE command is subsequently
  issued, the prepared statement is planned and executed. This division
  of labor avoids repetitive parse analysis work, while allowing the
  execution plan to depend on the specific parameter values supplied.


Keep transaction log size under control during batch processing

Even with the SIMPLE recovery model, the trans log can still blow up if there's a long running transaction going on.  I'm wondering if something on your Dev server was running while your script was doing its thing and cause the trans log to not be able to truncate since the MinLSN was from some long running transaction.

To answer the question though, unless you restrict access to the DB (like running your script using ALTER DATABASE  SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE or something similar like SINGLE_USER), there's no way you can force the log file to free space as other transactions may be running and the trans log has to stay consistent for those transactions.

Check TechNet (or BOL: Checkpoints and the Active Portion of the Log) for more information.


Certain database engines do support pinning objects to cache, Oracle and MS-SQL to name a couple. MySQL does not have this option, as far as I know.

On the other hand I cannot think of many use cases for this option, as modern database systems are very good at managing cache. MySQL offers block caching with an LRU algorithm through the InnoDB storage engine.

There is also an in memory storage engine, but there is no easy and efficient way replicate changes back to disk.

There are complete in-memory databases out there if you want everything in RAM, including MySQL Cluster.

On a sidenote, in practical use there is a trade-off between I/O and CPU resources. More cache means less I/O but more CPU is consumed, and there is a real possibility of introducing new bottlenecks.


In memory table vs temporary table - benefits if temdb in already on ram disk

In a comment from Ned Otter:

  If the bottleneck was due to locking/blocking on system tables in tembdb, then I would imagine that placing it in RAM would not make much difference.

If your current workload is getting slowed down by contention over internal components, then putting tempdb on a ramdisk won't help you.  This is because the problem is not I/O throughput related.  Check out this detailed post from Paul Randal on the subject:

The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention

  Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Ned continues with:

  That's why SQL 2019 supports a memory-optimized version of tempdb.

This is a new feature in 2019 that's designed to help with these types of contention issues.  You can see a demo of how this helps, and how it's not quite ready in the current preview of SQL Server 2019, on Erik Darling's blog:

SQL Server 2019 In Memory tempdb

You really need to measure whether the slowdown is due to I/O, or due to contention.  Use Paul Randal's post above to dig into that.

Since you updated your question to mention that the slow cases are when you insert millions of rows into a table variable, I'll assume the above-mentioned contention isn't likely you're problem (unless you're creating these table variables from many different connections at a time).  In which case, the ramdisk approach could certainly help.

In your last update to the question, you said:

  It seems that parallel plan is not supported for in-memory plan table (at least on insert), so I failed to optimize anything

This is a well-known "issue" with in-memory OLTP.  Rather than using built-in parallelism, you need to "roll your own" by loading data into the in-memory tables from multiple connections.


SQL Server 2012: Full Text Catalog taking up all the disk space

Please provide Full-text index definition, rowcount and total size of that column. Having VARCHAR(max) gives 32 megabytes per row of storage. If someone inserts huge text in that column many times, your db and index will grow very fast.

You can create index on a different FILEGROUP, which in turn can map to db file on another drive. ALTER DATABASE to add file(s) to new file group. FG is created and file(s) added to it in single statement:

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

For further reading (and source of this code): ALTER DATABASE File and Filegroup Options

Sorry, cannot add a comment for some reason. wil update /delete this answer later.

UPDATE

Although possible via a mapped drive, storing an index on the network has big performance penalty. If you are using all records in the table frequently and FT index is new functionality to explore, then this is ok: table on local storage and index on the network.

Alternatively I would suggest considering table usage and partition table into current-frequently used - and archive - almost unused - data. You can have several archive tables on different shares. Table and indexes will be stored on the same partition.

RE: Compression. It is a trade off between storage and CPU. If you have plenty of CPU resource, do consider this. If index stored on the network and compression rate is good, then it would also help.

UPDATE on Compression: Compression would requires row size to be less than 8060 bytes. So if you are storing text, then compression would not be possible for the table (= clustered index or heap). But if FTI row size is less than that, then you can compress FTI index.


PgAdmin 4 - How to connect to database via ssh tunnel as posgres when its password is empty?

Does user postgres have to have a password if I want to connect via SSH tunnel? Is it possible? And what should I do in order to connect?

Generally it does.

Your SSH tunnel is configured so that the remote SSH server connects to PostgreSQL through a TCP connection to localhost. And generally, a default pg_hba.conf maps this type of connection to the md5 authentication scheme, for which a password is required. Otherwise any user with a shell account could connect to PostgreSQL, which would be wrong as a default configuration.

The more common way to use PostgreSQL through a SSH tunnel is to set a password to the PostgreSQL databases accounts that you need to connect to. Use the \password command inside psql or the ALTER USER SQL statement.


Oracle Data Pump for extracting DDL as a set of files – one per object

Why bother with Data Pump for a task like that? It is designed for something else, and there are simpler solutions for this without Data Pump, like using DBMS_METADATA directly.

$ mkdir /home/oracle/ddl

Then create the directory object:

SQL> create or replace directory ddl_dir as '/home/oracle/ddl';

Then just run something like below. In this example I generated the DDL for only first 5 (order based on object_id) packages, package bodies and tables to keep the output short.

declare
  l_ddl clob;
begin
  for o in (select * from (select owner, object_name, object_type, row_number() over (partition by object_type order by object_id) as rn from dba_objects where owner = 'SYS' and object_type in ('PACKAGE', 'PACKAGE BODY', 'TABLE')) where rn <= 5)
  loop
    l_ddl := dbms_metadata.get_ddl(replace(o.object_type, 'PACKAGE BODY', 'PACKAGE_BODY'), o.object_name, o.owner);
    dbms_xslprocessor.clob2file(l_ddl, 'DDL_DIR', o.object_name || '.' || case when o.object_type = 'PACKAGE' then 'pks' when o.object_type = 'PACKAGE BODY' then 'pkb' when o.object_type = 'TABLE' then 'tbl' end);
  end loop;
end;
/

And the files:

$ ls -l /home/oracle/ddl/
total 268
-rw-r--r--. 1 oracle oinstall    587 Aug 22 15:04 CDBVIEW_INTERNAL.pkb
-rw-r--r--. 1 oracle oinstall    732 Aug 22 15:04 CLU$.tbl
-rw-r--r--. 1 oracle oinstall  11901 Aug 22 15:04 DBMS_REGISTRY.pkb
-rw-r--r--. 1 oracle oinstall  19790 Aug 22 15:04 DBMS_REGISTRY.pks
-rw-r--r--. 1 oracle oinstall   3822 Aug 22 15:04 DBMS_REGISTRY_SERVER.pks
-rw-r--r--. 1 oracle oinstall    818 Aug 22 15:04 DBMS_REGISTRY_SIMPLE.pkb
-rw-r--r--. 1 oracle oinstall   1102 Aug 22 15:04 DBMS_REGISTRY_SIMPLE.pks
-rw-r--r--. 1 oracle oinstall   8659 Aug 22 15:04 DBMS_STANDARD.pks
-rw-r--r--. 1 oracle oinstall    202 Aug 22 15:04 FET$.tbl
-rw-r--r--. 1 oracle oinstall    740 Aug 22 15:04 SEG$.tbl
-rw-r--r--. 1 oracle oinstall  18722 Aug 22 15:04 STANDARD.pkb
-rw-r--r--. 1 oracle oinstall 164861 Aug 22 15:04 STANDARD.pks
-rw-r--r--. 1 oracle oinstall   1239 Aug 22 15:04 TAB$.tbl
-rw-r--r--. 1 oracle oinstall    344 Aug 22 15:04 UET$.tbl
-rw-r--r--. 1 oracle oinstall    870 Aug 22 15:04 XML_SCHEMA_NAME_PRESENT.pkb

The rest is up to you: customize the above for specific types and file extensions.


The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have:

INNER JOIN descendants d ON d.parent_id = o.object_id

It should be the other way around:

INNER JOIN descendants d ON d.object_id = o.parent_id

You want to join the objects with their parents (that have already been found).

So the query that calculates depth can be written (nothing else changed, only formatting):

-- calculate generation / depth, no updates
WITH RECURSIVE descendants
  (id, customer_id, object_id, parent_id, ancestor_id, depth) AS
 AS ( SELECT id, customer_id, object_id, parent_id, ancestor_id, 0
      FROM objects
      WHERE object_id = parent_id

      UNION ALL

      SELECT o.id, o.customer_id, o.object_id, o.parent_id, o.ancestor_id, d.depth + 1
      FROM objects o
      INNER JOIN descendants d ON  d.customer_id = o.customer_id
                               AND d.object_id = o.parent_id
      WHERE d.id <> o.id
    )
SELECT *
FROM descendants d
ORDER BY id ;

For the update, you simply replace the last SELECT, with the UPDATE, joining the result of the cte, back to the table:

-- update nodes
WITH RECURSIVE descendants
    -- nothing changes here except
    -- ancestor_id and parent_id
    -- which can be omitted form the select lists
    )
UPDATE objects o
SET generation = d.depth
FROM descendants d
WHERE o.id = d.id
  AND o.generation = -1 ;          -- skip unnecessary updates

Tested on SQLfiddle

Additional comments:

the ancestor_id and the parent_id are not needed to be in the select list (ancestor is obvious, parent a bit tricky to figure out why), so you can keep them in the SELECT query if you want but you can safely remove them from the UPDATE.
the (customer_id, object_id) seems like a candidate for a UNIQUE constraint. If your data comply with this, add such a constraint. The joins performed in the recursive CTE would not make sense if it wasn't unique (a node could have 2 parents otherwise).
if you add that constraint, the (customer_id, parent_id) would be a candidate for a FOREIGN KEY constraint that REFERENCES the (unique) (customer_id, object_id). You most probably do not want to add that FK constraint though, since by your description, you are adding new rows and some rows can reference others that haven't been yet added.
There are certainly problems with the efficiency of the query, if it's going to be performed in a big table. Not in the first run, as almost the whole table will be updated anyway. But the second time, you'll want only new rows (and those that were not touched by the 1st run) to be considered for update. The CTE as it is will have to build a big result.
The AND o.generation = -1 in the final update will make sure that the rows that were updated in the 1st run will not be updated again but the CTE is still an expensive part.

The following is an attempt to address these issues: improve the CTE as to consider as few rows as possible and use (customer_id, obejct_id) instead of (id) to identify rows (so id is completely removed from the query.
It can be used as the 1st update or a subsequent:

WITH RECURSIVE descendants
  (customer_id, object_id, depth)
 AS ( SELECT customer_id, object_id, 0
      FROM objects
      WHERE object_id = parent_id
        AND generation = -1

      UNION ALL

      SELECT o.customer_id, o.object_id, p.generation + 1
      FROM objects o
        JOIN objects p ON  p.customer_id = o.customer_id
                       AND p.object_id = o.parent_id
                       AND p.generation > -1
      WHERE o.generation = -1

      UNION ALL

      SELECT o.customer_id, o.object_id, d.depth + 1
      FROM objects o
      INNER JOIN descendants d ON  o.customer_id = d.customer_id
                               AND o.parent_id = d.object_id
      WHERE o.parent_id <> o.object_id
        AND o.generation = -1
    )
UPDATE objects o
SET generation = d.depth
FROM descendants d
WHERE o.customer_id = d.customer_id
  AND o.object_id = d.object_id
  AND o.generation = -1        -- this is not really needed

Note how the CTE has 3 parts. The first two are the stable parts. The 1st part find the root nodes that haven't been updated before and have still generation=-1 so they must be newly added nodes. The 2nd part finds children (with generation=-1) of parent nodes that have previously been updated.
The 3rd, recursive part, finds all the descendants of the first two parts, as before.

Tested on SQLfiddle-2


DBMS_DATAPUMP error: The current API cannot be executed because of inconsistencies between the API and the current definition of the job

Your versions differ between source and destination.

hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null, version=>'COMPATIBLE');

Oh, the bad news: you need this for the expdp too.


If you're using SQL 2008, look at the MERGE statement, it can do what other platforms call UPSERT, UPDATE if it exists, otherwise INSERT.


Active Directory group as a schema in SQL Server

No, an Active Directory group cannot be the schema, though I suppose you could name a schema with the same text as your AD group, but that does not get you more auditability.

If individual logins are all members of the AD group, they still appear in the database by default as their login name, not the AD group name.  (You can, of course, rename the database users to different names, but I do not see how that would really help you.)

However, using 'multiple users' (i.e one login and user per person) does not mean that they will all create their own schema.  You have considerable control over this behavior.

When you create the user's you can assign them to a default schema, such as dbo.  Likewise, if you need to set that up after the fact you can:

ALTER USER userName
     WITH DEFAULT_SCHEMA = schemaName

In addition, you can create an audit table and use a DDL trigger to track the changes being made to the database by tracing the details you want to know about.  You can also filter out nuisance changes, such as disabling and enabling indexes and so forth.

Sample DDL Audit Trigger:

CREATE TRIGGER [AuditDDLChanges]
    ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @Data XML

DECLARE
    @EventType NVARCHAR(128),
    @DatabaseName NVARCHAR(128),
    @SchemaName NVARCHAR(128),
    @ObjectName NVARCHAR(128),
    @ObjectType NVARCHAR(128),
    @LoginName NVARCHAR(128),
    @TSQLCommand NVARCHAR(MAX),
    @IgnoreMarker NVARCHAR(1)

SET @Data = EVENTDATA()

SELECT
    @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
    @SchemaName =   @data.value('(/EVENT_INSTANCE/SchemaName)[1]',   'nvarchar(128)'),
    @ObjectName =   @data.value('(/EVENT_INSTANCE/ObjectName)[1]',   'nvarchar(128)'),
    @ObjectType =   @data.value('(/EVENT_INSTANCE/ObjectType)[1]',   'nvarchar(128)'),
    @EventType =    @data.value('(/EVENT_INSTANCE/EventType)[1]',    'nvarchar(128)'),
    @LoginName =    @data.value('(/EVENT_INSTANCE/LoginName)[1]',    'nvarchar(128)'),
    @TSQLCommand =  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',  'nvarchar(max)')

-- Clause to avoid tracking things you do not want in the event table
IF ( (IS_SRVROLEMEMBER('sysadmin',@LoginName) = 1) OR
     (@EventType LIKE '%STATISTICS')
   )
   BEGIN
      SELECT @IgnoreMarker = 1
   END
ELSE
   BEGIN
        INSERT AuditDatabase.dbo.ChangeLog
           (EventType,
            PostTime,
            SPID,
            ServerName,
            LoginName,
            UserName,
            DatabaseName,
            SchemaName,
            ObjectName,
            ObjectType,
            TSQLCommand)
           VALUES
           (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
            @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
            @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') ) ;
      END
GO


What is the fastest method to migrate large tables from Oracle to SQL Server?

BCP would be a good choice. Export your data to a text file and then import it. To do this follow these steps:

Create your new table in sql server
Insert 1 row of data into this new table
Use BCP to get an export from this table containing this 1 row
This is necessary so that you can create an export from Oracle that matches this. Example:

  bcp yourdatabase.dbo.yourtable out yourtable.dat -T -c

Check the format in yourtable.dat
Generate an output file from Oracle that matches the format of yourtable.dat
Use bcp again to load the data file that was created by oracle into sql server. Example:

  bcp yourdatabase.dbo.yourtable in yourtable.dat -T -c

Try with 5000 rows. Get it working properly then time it and load. BCP will be fast.


How to Set Up Sync of two mysql databases without master slave

You can use any of the available ETL Tools:

Talend
Pentaho
others, like Informatica

Initial size not important, the most important is the daily number of changes.

You will need to add a timestamp for INSERT/UPDATE or a bit column and triggers to mark the new sync status.

My clients sync millions-row databases (DB2/MySQL/Web) by cron jobs.

An other solution may be SymmetricDS (read the article about transformation of data using it: http://www.symmetricds.org/doc/3.0/html-single/user-guide.html#transform-data).


This is a surprising behaviour but from MSDN page, SET ANSI_NULLS, we can at least know that is the expected behaviour. One more reason to never use ANSI_NULLS OFF:

  SET ANSI_NULLS affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal  NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.


The performance will be the same. The optimizer will recognize this and create the same plan.

On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.

For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.

You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.

SELECT * FROM salestable , custtable
WHERE salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable
JOIN  custtable
ON salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable
ON salestable.custaccount = custtable.accountnum
WHERE salestable.dataareaid = custtable.dataareaid

Gives these execution plans


It's just a different way of representing the data. Also known as wide (pivoted) and long (folded). As an example and to keep it simple, imagine that we have a record with 3 properties: id, name, description in the wide (pivoted) version each property would be its own column like so:

CREATE TABLE `example_table` (
  id int not null,
  name archer(50) not null,
  description archer(100) null
)

In the long (folded) format you would have two columns like so:

CREATE TABLE `example_table` (
  property varchar(50) not null,
  value varchar(100) not null
)

The later gives you greater flexibility if the columns are not known in advance or are likely to change often. However, it is generally more complicated to implement because you have to design a way of knowing what type the value is, and then you have the overhead of serialization/deserialization to the storage type (in this example varchar(100)).

The wide format is better when the properties do not change very often and are known in advance. This will offer better performance but will be less flexible if changes are required.

Generally, where possible choose the wide format when you know the structure of the data you are storing, and choose long if you need maximum flexibility and are willing to accept the added complexity.


MySQL Workbench can either Dump or Restore - How Do I Configure To Do Both?

there are two common solution for cannot parse version from mysqldump: the first one you need to make sure path for MySQLDUMP is correct you can check it from the workbench by going to Edit->Preferences->Administrator and the second one the environment variable is not correctly configured on the server.


"ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql

You can only pass values to DML statements. The manual:

Another restriction on parameter symbols is that they only work in
SELECT, INSERT, UPDATE, and DELETE commands. In other statement types
(generically called utility statements), you must insert values
textually even if they are just data values.

So CREATE TABLE statement does not accept parameters via USING clause, even if expressions in a CHECK constraint look like values that might be parameterized.
That aside, you can largely simplify your function:
CREATE OR REPLACE FUNCTION mk_child(_year int, _month int)
  RETURNS text AS
$func$
DECLARE
   start_date date := to_date(_year::text || _month::text, 'YYYYMM');
   tname text := to_char(start_date , '"child_y"YY"m"MM');
BEGIN
   RAISE NOTICE 'Creating child table %', tname;
   EXECUTE format('
      CREATE TABLE %I (CHECK (end_time >= %L AND end_time < %L))
      INHERITS (parent_table)'
    , tname, start_date, (start_date + interval '1 month')::date);
   RETURN tname;
END
$func$ LANGUAGE plpgsql;

Related:

Self-managing PostgreSQL partition tables

If this is about table partitioning, look to the new declarative table partitioning in Postgres 10 or later. Related answer with sample code and links:

Better database for "keep always the 5 latest entries per ID and delete older"?


'go to symbol' in Allround Automation PL/SQL Developer?

The plugin rrProject adds some keyboard features. The Code Contents List Tool allows you to find the function or procedure of the code contents panel and go to it. The IDE Insight tool is a global search for windows, connections, menu items and browser objects and performing the necessary action.

It’s not exactly that 'go to symbol', but it allows you to use the mouse less.


Is it possible to set up ADSI linked server in SQL Server Express?

Linked Servers do not seem to be an edition specific feature according to this MSDN article : Linked Servers (Database Engine) SQL 2016

To add a linked server without the SQL Server Management Studio you would use the system stored procedures sp_addlinkedserver (SQL 2008+ or SQL 2012) and sp_addlinkedsrvlogin (SQL 2008+).

A similar Q&A was previously posted in SO as Linked Server In SQL Server Express


Can a connection's prepared statements be altered by Postgres's admin functionality?

If the user has:

Physical hardware access;
Root / "Administrator" access to the system; or
PostgreSQL superuser rights;

then they effectively have total control and can do whatever they feel like.

It's a little trickier when starting with superuser access, but the PostgreSQL superuser is not isolated from the underlying OS user that PostgreSQL its self runs as. They can modify the data directory contents in-place, change views, install extensions, etc.

If you're running a database on someone else's hardware, they control the database - not you. You must design with that in mind, allowing for the idea that they can modify the data, queries, etc in any way they feel like.

It's pretty simple to recompile PostgreSQL with whatever modifications you feel like, or add extensions to do so. Feel like matching query text and replacing it with a different query? Easy, that's just a parser or executor hook.

Or you can just attach a debugger and do it live.

So, while the user can't just go modifying prepared statements directly from SQL, if they have control of the system they can still do it with varying degrees of difficulty. Whether it's "hard enough" for your purposes depends mainly on what the incentives for breaking the system are, the skill level of the attacker(s), whether attackers communicate with each other, etc.

There are some strategies you can use to make untrusted local storage better protected - for example, signing data with a key known only to the entity that generates it, so others can verify its authenticity but not modify it.


Can a non-owner of a table be allowed to disable the table's triggers?

As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:

CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;

Then grant the necessary privilege to your user to execute the function:

GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;

Now alice will be able to disable that one trigger.  You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.

The trick is in SECURITY DEFINER.  It makes the function running with the privileges of the user that defines (creates) the function.  As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.


Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?

Here's the trick, at least with pgAdmin v4:
Leave the tablespace blank. It will default to "pg_default" when creating the database.


Install PostgreSQL using source code or using apt-get?

If you install from apt-get you will be able to use apt-get upgrade option later and other apt-get build in options (available from Ubuntu).
Furthermore apt-get installs binaries and manages their versions. You just install and nothing more.

Installing from source gives you the ability to do a more detailed installation. (You can customize the build and installation process with command line options connected to ./configure PARAMETERS LIST.) It's useful if you need a feature that can be enabled only by compiling the package yourself. Next thing is that installation from source may not be visible to apt-get commands so you may not be able to use them.

But if you need to install Postgres stable version without any specialized functionalities use apt-get option.


Replace 2 spaces by 1 space in all values of a column

You don't need to use a regular expression for that. Instead, you could use the replace function in SQL like this:

replace(MODEL, '  ', ' ')

where MODEL is your column, '  ' is the character(s) that you want to replace and ' ' is the character(s) you want to replace with.


Including null values in unpivoted column

You can do the unpivot in a cross apply instead to get the result you want.

declare @T table
(
  id int,
  title varchar(50),
  genre1 varchar(50),
  genre2 varchar(50),
  genre3 varchar(50),
  genre4 varchar(50),
  genre5 varchar(50)
);

insert into @T values
(1, 'Movie1', 'Thriller', 'Crime',  NULL,  NULL, NULL),
(2, 'Movie2', 'Fantasy', NULL, NULL, NULL, NULL),
(3, 'Movie3', 'Political', 'Philosophical', 'Historical', NULL, NULL),
(4, 'Movie4', 'Science Fiction', NULL, NULL, NULL, NULL);

select T.id,
       T.title,
       G.genre,
       G.original_column
from @T as T
  cross apply (
              select T.genre1, 'genre1' union all
              select T.genre2, 'genre2' union all
              select T.genre3, 'genre3' union all
              select T.genre4, 'genre4' union all
              select T.genre5, 'genre5'
              ) as G(genre, original_column);

If you are on SQL Server 2008 or later you can use the Table Value Constructor instead of union all as suggested by Vladimir Baranov in a comment.

select T.id,
       T.title,
       G.genre,
       G.original_column
from @T as T
  cross apply (values (T.genre1, 'genre1'),
                      (T.genre2, 'genre2'),
                      (T.genre3, 'genre3'),
                      (T.genre4, 'genre4'),
                      (T.genre5, 'genre5')
              ) as G(genre, original_column);


Replicated Database Log File Maintenance

I would first concentrate on the Autogrowth settings for the database as too frequent and incorrect autogrowths can cause massive internal log fragmentation affecting any processes that reads transaction log file (e.g. replication, CDC, backups, etc) and there-by soaring the number of VLF's. Remember that the log file has to grow as it cannot be ZERO initialized and hence if the autogrowth settings are not appropriate, then SQL Server has to wait till the auto growth event finishes.

Now, if you are using T-Rep, the log reader agent will read the log when replicating changes from the publisher to distributor and then the distributor agent will distribute the changes to the subscribers which are called "pending transactions". Once the changes are replicated, the log entry is marked as "replicated".

The problem arises when the log reader becomes slow or it gets delayed leading to accumulating "pending replication" commands for long periods and they will remain part of active log and hence VLF cannot be truncated.

You can check the log_reuse_wait_desc column in sys.databases which will be showing "Replication" as the problem.

Note: Merge replication and snapshot replication do not affect transaction log size. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database.

If the log file grows out of control, a common technique for T-Rep is to use sp_repldone command to mark all the log records currently waiting on the log reader as replicated and then you have to re-initialize the subscribers.

Note: Even if you try to switch to simple recovery model thinking that it will truncate the log, it wont work as replication is supported in SIMPLE recover as well, and the log wont be able to get truncated as the log reader agent will need them to process.

This brings us to doing Proper Log Management - size the log file to support the volume of data changes occuring in the database and take log backups frequently so as to allow rapid reuse of space within the log file.

Best is to monitor your Transaction log along with other monitoring that you might be normally doing using PERFMON and DMV's.

Just and FYI, sys.dm_db_log_space_usage is a new DMV in SQL Server 2012 that will get basic log size and space info.

References :

Manage the Size of the Transaction Log File
Frequently Asked Questions for Replication Administrators
Stop and checked your Virtual Log Files (VLFs)
Free e-book : SQL Server Transaction Log Management by Tony Davis and Gail Shaw


Php code to update the profit column in database in every 1 hour

Never update it.  No UPDATE, noTRIGGER`.  Simply compute it based on the current time minus the time at which the user's entry was established.

There are standard formulas for "compound interest"; MySQL has all the math formulas needed to do the arithmetic.


Oracle Exchange partition from non partitioned table

We have done the same operation using the following approach:

Create the destination table with the correct structure nologging
Insert append parallel into it (verifiy direct path load in the explain plan)
Create the indexes nologging
in some case add the FK novalidate
compute the stats
Alter the objects LOGGING
Drop the initial table


Searching for a close numeric match on spatial coordinates

The smart and fast solution for this class of problems is an index-backed "nearest neighbor" search.
For the record: if you want precise results with spatial data use PostGis and operate with geometry or geography types. Here is a starting point. And operate with ST_DWithin(). Examples:

Order by distance
PostGIS radius query

Sticking to your setup (2-D points, no PostGis), and ignoring the additional approximation error of handling spatial data in a 2-D plain, which seems negligible for the case at hand - I suggest a space-partitioned GiST index (on an expression in your case):
CREATE INDEX tbl_location_spgist_idx ON tbl USING SPGIST (point(longitude, latitude));

Why SP-Gist? See:

Perform this hours of operation query in PostgreSQL

https://www.2ndquadrant.com/en/blog/postgresql-12-implementing-k-nearest-neighbor-space-partitioned-generalized-search-tree-indexes/

To get a maximum of 10 "nearest neighbors" in next to no time:
SELECT *
FROM   tbl
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;

You can then filter the ones close enough. To get a maximum of 10 closest within a maximum distance:
SELECT *, ((longitude - 9.876543210987654321) ^ 2
        + (latitude   - 1.234567890123456789) ^ 2) AS squared_dist
FROM   tbl
WHERE  ((longitude - 9.876543210987654321) ^ 2
      + (latitude  - 1.234567890123456789) ^ 2) < 0.000000001  -- or whatever
ORDER  BY point (longitude, latitude)
      <-> point '(9.876543210987654321,1.234567890123456789)'
LIMIT  10;

db<>fiddle here
I use a squared distance to get nearest neighbors based on simple Pythagorean theorem. The beauty of it: the calculation is only performed on the nearest neighbors, so it's still very fast when the calculation gets more expensive - even in big tables.


AlwaysOn Availability Group error after Windows Update in environment without a failover cluster

Community Wiki answer to document the solution

KenWilson:

Skip all the steps pertaining to AlwaysOn and run the SSIS_hotfix_install.sql script.


If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?

Metalink bug 9103343 states:

  This is expected behaviour.  SQL*Plus is written in oci and oci has a
  default  prefetch value of 1 row.  However prefetch upon a fetch (as
  opposed to upon  an execute) only takes place when you are not
  performing an array fetch, so  when arraysize is 1.   Regardless of
  arraysize the first fetch in the trace is always 1 row as 1 row  is
  prefetched on the execute.  Then it either performs a scalar fetch, so
  one  requested row plus one prefetched row, or it performs an array
  fetch so you  see eg :   a) arraysize = 1, fetches are:  1, 2, 2, ...
  b) arraysize = 2, fetches are:  1, 2, 2, ... c) arraysize = 5, fetches
  are:  1, 5, 5, ...

Also see Metalink doc 1265916.1


Correct way to perform backup of Cassandra/Scylladb

For Scylla, please look at the following official documentation:

Backup your Data

  Full Backup Snapshots are taken using nodetool snapshot.
  Enabling the incremental backup (disabled by default) will create a hard-link from each SSTable, right after it is flushed, to a backups directory.
  For a complete point in time backup you need: a snapshot plus incremental backups and commit logs since that time of the snapshot. Make sure to clean unnecessary incremental backup, Scylla will not do it for you.

Restore from a Backup and Incremental Backup

  Restoring a keyspace from backup requires all snapshot files of the tables, and incremental backup files taken after the snapshot - if available. Before restoring from backup, you should truncate the table data, making sure existing data will not override the restored one.

See the linked documentation for detailed instructions.


MySql date range query not showing all records

The date column in database is varchar.

You should really fix that.  If the string always looks like yyyy-mm-dd, then it will happen to work as a date.  (And my code below will work.)  That is, the string version of a DATE or DATETIME is handled almost identically the same as the proper datatype.  One major difference is:  "2019-08-01" versus "2019-8-1"

Do not hide a column (Date) inside a function call (str_to_date)

WHERE str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR
  AND str_to_date(b.Date,'%Y-%m-%d') <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')

-->

WHERE b.Date >= CURDATE() - INTERVAL 1 YEAR
  AND b.Date  < CURDATE() + INTERVAL 1 DAY

Did you really want 365 days, plus today?  This disagrees with your statement of "from march 2018 to march 2019".  (Plus March is 03, not 08.)

Now, INDEX(Date) may help with the speed of the query.  I say only "may" because the index will be ignored if the year is most of the table; it would be faster to ignore the table and simply scan the data.  (And my version will be as slow as yours).

Please rearrange your query to use LEFT JOIN instead of RIGHT JOIN.  My brain has trouble with RIGHT JOIN.


Always use InnoDB by default.

InnoDB is durable and crash-safe.  Your database will not be corrupted in a crash.  MyISAM is very susceptible to data corruption.  This should be a more important consideration than performance -- unless you don't really care if you lose your data.

InnoDB caches both data and indexes in RAM.  MyISAM caches only indexes in RAM.  So if you have enough RAM, queries will almost certainly be faster in InnoDB.

InnoDB is the default.  It has been MySQL's default storage engine since MySQL 5.5 (circa 2010).

InnoDB continues to improve.  MyISAM is not being improved.  All of the storage engine engineering work is going into InnoDB.

InnoDB is faster anyway.  Here's a performance comparison from way back in 2007 that shows InnoDB beats MyISAM in most tests:  http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
Of course, benchmarks results show the performance of the benchmark, not the performance of your application.  If you want a more authoritative answer, you should run sysbench to test your database on your hardware.

There are cases where MyISAM is faster, but these cases are becoming more and more rare.  For instance, doing table-scans.  Or SELECT COUNT(*) FROM Table.  But you should treat InnoDB as the default storage engine choice until you can demonstrate a case where MyISAM beats it.


Large INSERT INTO SELECT [..] FROM gradually gets slower

The large offsets can have this effect. I would try to remove the offset and use only LIMIT 10000:

INSERT INTO domains.dictionary_language
  (text_id, english)
    SELECT      t.old_id,
                IF(t.old_text LIKE '%==English==%', 1, 0)

    FROM        wikt.text AS t
      JOIN      ( SELECT COALESCE(MAX(text_id), 0) AS offset
                  FROM domains.dictionary_language
                ) AS m
                ON  t.old_id > m.offset

    ORDER BY    t.old_id
    LIMIT       100000;


why would command text not execute at all?

Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started.

  CREATE EVENT SESSION query_check ON SERVER
    ADD EVENT sqlserver.sql_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text))
    ADD TARGET package0.asynchronous_file_target(SET filename=N'D:\temp\monitor.xel',max_file_size=(5),max_rollover_files=(4))
    --,ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON);

You can start it like this:

ALTER EVENT SESSION query_check ON SERVER STATE=START;

Run your query a few times and then stop the event session:

ALTER EVENT SESSION query_check ON SERVER STATE=STOP;

Then you can query it like this:

SELECT
    fired_event = event_data.value('(/event/@name)[1]','nvarchar(25)'),
    fired_event_time = event_data.value('(/event/@timestamp)[1]','datetime2(0)'),
    event_database_id = event_data.value('(/event/action[@name=''database_id''])[1]','int'),
    event_sql_text = event_data.value('(/event/action[@name=''sql_text''])[1]','nvarchar(max)')
FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file(N'D:\temp\monitor*.xel',
                                            N'D:\temp\monitor*.xem',
                                            NULL, NULL)
    ) events

You can capture more data, you can choose what you'd like from here:

SELECT p.name AS package_name,
       o.name AS action_name,
       o.description
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects AS o
     ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'action'

So if you wanted to capture login information, you'd modify your ACTIONs from

ACTION (sqlserver.database_id, sqlserver.sql_text)),

to

ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.username)),

I'd actually recommend against filtering on the query text because it's expensive. Instead, I'd try to find the correct username. If you don't think this will work, then I'd filter based on the query text of the result XML file. In fact, only in SQL Server 2012 can you filter on the statement text anyway (it's called a predicate).

If you wanted to perhaps capture data from a specific username you'd add WHERE to each EVENT that you'd like to filter.

ADD EVENT sqlserver.sql_statement_starting(
    ACTION (sqlserver.database_id, sqlserver.sql_text)
    WHERE (sqlserver.username = N'{{ your username here }}')
)


Cannot Create or Drop User in MySQL Workbench

Do not import the tables in the database mysql from a server running a different version of MySQL.


Query to find all dates between two specified dates using plsql

This should get you started:
declare
  s date := date'2020-01-01';
  e date := date'2020-01-04';
  ndays number;
begin
  ndays := e-s+1;
  for i in 1..ndays loop
    dbms_output.put_line (s);
    s := s+1;
  end loop;
end;
/

01-JAN-2020
02-JAN-2020
03-JAN-2020
04-JAN-2020

PL/SQL procedure successfully completed.

Just use simple date arithmetic: the difference between two dates is the number of days between the two:
date'2020-01-04' - date'2020-01-01' + 1

The above returns 4 (the +1 is so as to also include the last day, since this is what you describe in your example).
Then the rest is just a loop over the number of days, incrementing the start date by one day on each loop:
s := s + 1;

The next stage is to turn that into a usable function. First we need a type:
create or replace type date_array as table of date;
/

This defines an array of dates as is used as the output of the pipelined function:
create or replace function dates_between (s date, e date)
return date_array
pipelined
is
  d date := s;
begin
  for i in 1..e-s+1 loop
    pipe row(d);
    d := d + 1;
  end loop;
end;
/
show errors

This function returns the list of dates between two dates (including the end date). Like this:
SQL> select * from table (dates_between (date'2020-02-23',date'2020-03-05'));

COLUMN_VALUE
-----------------
23-FEB-2020
24-FEB-2020
25-FEB-2020
26-FEB-2020
27-FEB-2020
28-FEB-2020
29-FEB-2020
01-MAR-2020
02-MAR-2020
03-MAR-2020
04-MAR-2020
05-MAR-2020

12 rows selected.

Let's try that with your example. First let's create and populate the test table:
drop table test_table purge;
create table test_table (
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
);
insert into test_table values (1,'x',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'y',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'z',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/15/2020','MM-DD-YYYY'));
insert into test_table values (1,'b',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/20/2020','MM-DD-YYYY'));
insert into test_table values (1,'w',to_date('1/12/2020','MM-DD-YYYY')  ,to_date('1/17/2020','MM-DD-YYYY'));
insert into test_table values (1,'a',to_date('1/18/2020','MM-DD-YYYY')  ,to_date('2/12/2020','MM-DD-YYYY'));
commit;

(Noticed I corrected the first date to match your description).
Here is the query that gives the result you describe:
select customer_num, card_number, column_value as card_date
from test_table, table (dates_between (card_issue_date,card_expire_date));

which returns:
CUSTOMER_NUM CARD_N CARD_DATE
------------ ------ -----------------
           1 x      01-JAN-2020
           1 x      02-JAN-2020
           1 x      03-JAN-2020
           1 x      04-JAN-2020
           1 y      01-JAN-2020
           1 y      02-JAN-2020
           1 y      03-JAN-2020
           1 y      04-JAN-2020
           1 z      11-JAN-2020
           1 z      12-JAN-2020
           1 z      13-JAN-2020
           1 z      14-JAN-2020
           1 z      15-JAN-2020
           1 b      11-JAN-2020
           1 b      12-JAN-2020
           1 b      13-JAN-2020
           1 b      14-JAN-2020
           1 b      15-JAN-2020
           1 b      16-JAN-2020
           1 b      17-JAN-2020
           1 b      18-JAN-2020
           1 b      19-JAN-2020
           1 b      20-JAN-2020
           1 w      12-JAN-2020
           1 w      13-JAN-2020
           1 w      14-JAN-2020
           1 w      15-JAN-2020
           1 w      16-JAN-2020
           1 w      17-JAN-2020
           1 a      18-JAN-2020
           1 a      19-JAN-2020
           1 a      20-JAN-2020
           1 a      21-JAN-2020
           1 a      22-JAN-2020
           1 a      23-JAN-2020
           1 a      24-JAN-2020
           1 a      25-JAN-2020
           1 a      26-JAN-2020
           1 a      27-JAN-2020
           1 a      28-JAN-2020
           1 a      29-JAN-2020
           1 a      30-JAN-2020
           1 a      31-JAN-2020
           1 a      01-FEB-2020
           1 a      02-FEB-2020
           1 a      03-FEB-2020
           1 a      04-FEB-2020
           1 a      05-FEB-2020
           1 a      06-FEB-2020
           1 a      07-FEB-2020
           1 a      08-FEB-2020
           1 a      09-FEB-2020
           1 a      10-FEB-2020
           1 a      11-FEB-2020
           1 a      12-FEB-2020

55 rows selected.


Multiple system.replset collections in local

I ran into this issue while trying to reconfigure my replicaset. db.system.replset.find().pretty() yielded two replicasets with different versions and different members.
Running db.system.replset.remove({"version":199}) ended up removing the bad replicaset configuration. Reconfiguring my replicaset worked after that.


I accidentally removed SELECT permission from my user on SQL Server

Checking all the items in Membership means you are adding yourself to all the roles defined in the database. That includes the sp_denydatareader role, which is probably the main reason you cannot open the Properties box any more.

If you have been given the permission to alter roles previously, it is likely that you have not removed it by your erroneous actions. So, you could just try to remove yourself from the sp_denydatareader role by executing this command in a query window:

ALTER ROLE sp_denydatareader DROP MEMBER your_user_name;

If that succeeds, you can then try opening the Properties box again and undoing your mistakes.


Query Tuning and Index Tuning - SQL Server

I am an Oracle Developer. Based on my understanding of Oracle performance tuning, this is how I would approach your situation.

I am seeing three entry points to your SQL.

a. xMobiles
b. xDrivers
c. GPSTime endpoints.

Each entry point will produce a different execution plan. Based on the selectivity of rows upfront in the execution plan tree, the best plan needs to be selected dynamically. Go through the data volume that gets generated at each step for the three possible execution plans. That will give you a good insight into the fact that "which plan suits your particular scenario".

Regarding your first execution plan, you have stated that the number of Actual Rows from dbo.Logs is 230768 and the Clustered Index(CI) Scan is showing 5676246 (24x greater).

Please note the difference between "SEEKs" and "SCANs". IX_Log_NodeID1 was accessed via an Index Seek. This uses the predicate values passed for that index and seeks only those rowids matching the predicate values. It does not scan the entire index. Whereas, in step two of the execution plan, the entire CI PK_LogMobiles is scanned into memory and only then a nested loop is performed. This is not optimal.

That is our issue. In step two PK_LogMobiles must be accessed via CI Seek, seeking only the 230768 logIDs out of the entire 5676246 logIDs. Instead the entire CI is scanned.

In Oracle such scenarios arise when the 230768 rows are scattered across too many pages (SQLServer) or blocks (Oracle) causing a major part / the entire CI to be scanned. This means that the records are not clustered enough for seeks. Improve the ClusteringFactor (Oracle Terminology) of your so called CI to resolve your performance issue.


You should construct an UPDATE statement that updates many rows at once, along the lines of:

UPDATE table1
SET population_count_hour_1 = sub.pop_count_1,
    population_count_hour_2 = sub.pop_count_2,
    ...
FROM (SELECT state,
             count(*) FILTER (WHERE start_time < 3600 AND end_time < 0)    AS pop_count_1,
             count(*) FILTER (WHERE start_time < 7200 AND end_time < 3600) AS pop_count_2,
             ...
      FROM table2
      GROUP BY state) AS sub
WHERE p.state = sub.state;

That should be considerably faster.


Trouble connecting to Oracle database with sqlplus and OS authentication

For anything looking for an answer: I was connecting the correct way. On the backend, remote_os_authent was set to false, which would not allow this kind of authentication.


Docs don't just "suggest" that you "can" install into a new location.  It is required.  Two different installations must be in their own ORACLE_HOME directories.  And you do not "add" another path/directory to ORACLE_HOME. ORACLE_HOME specifies only one home/directory at a time.

To quote from the Installation Guide (emphasis mine):

  "Do not install Oracle Database 12c software into an existing Oracle
  home."

Also, the installation guide specifically says to unset ORACLE_HOME and several other environment variables before launching the OUI.

  If you have had an existing installation on your system, and you are
  using the same user account to install this installation, then unset
  the ORACLE_HOME, ORACLE_BASE, ORACLE_SID, TNS_ADMIN environment
  variables and any other environment variable set for the Oracle
  installation user that is connected with Oracle software homes.


How to count the amount of values grouped by some column?

You really don't need a view for something this simple. But this is what it would look like

CREATE VIEW desired_view
AS
  SELECT client_id, sum(score)
  FROM source
  GROUP BY client_id;

Output

 client_id |  sum
-----------+-------
         6 |  3000
         7 | 11000
(2 rows)


Is indexing the following columns efficient?

For this query:

SELECT `orderNumber`
FROM orders
WHERE `status` = 'shipped’
ORDER BY orderDate, shippedDate;

Try a composite index on orders(status, orderDate, shippedDate, orderNumber).


Service Account Groups in SQL Server 2012

Jon,
From what I've seen, the local Windows groups are going away in favor of using the NT SERVICE SIDs on W2K8R2 - http://msdn.microsoft.com/en-us/library/ms143504.aspx#Serv_SID

HTH,
Nancy Hidy Wilson


Sql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'?

The final reason for this effect was that the Backupexec process responsible to take regular log backups was still present and running (so IT monitoring did not alert anything), but it was kind of internally crashed, so it did not do it's job properly.
In order to address this kind of event I decided to add the log file events and it's metadata details to the SQL Server monitoring. Once every day those information is captured and added to the monitoring information repository, where I can run reports and queries against to see progress and find out abnormal behavior.
This ended in a report about the log files looking similar to this sample. It shows the file size over time of each TL and also the percentage of usage at each point. If point size increases, the file contents grow. If they grow too much, the file size increases. Dramatically file size decrease was caused by manual shrink operations (followed by rolling eyes after it grew and grew again...). Basically log file usage and backups should be aligned in a way it almost never grows.

This is very useful to keep an eye on and eventually could also be used to alert by using this report as a data driven subscription (which I have not tried yet).


My application uses set schema and unqualified tables and I get confusing numbers from snapdyn_sql

The more modern table function:

select EXECUTABLE_ID, STMT_TEXT, num_executions
from TABLE(MON_GET_PKG_CACHE_STMT('D', NULL, NULL, -2)) as T
where stmt_text like 'select *%'"

returns individual metrics for the different schemas.

It still takes some further examination to find out what executable_id that belongs to each schema, so I much prefer qualified table names in queries over the set schema pattern.

If tables are not qualified by schema, the schema can be detected by:

select EXECUTABLE_ID, VALUE as SCHEMA, STMT_TEXT, num_executions
from TABLE(MON_GET_PKG_CACHE_STMT('D', NULL, NULL, -2)) as T
CROSS JOIN TABLE(COMPILATION_ENV(T.COMP_ENV_DESC)) S
where stmt_text like 'select *%'
  and name = 'SCHEMA'

More details on COMPILATION_ENV can be found in the answer provided by Mark Barinstein


There appear to be two key issues here.

One is finding a way to match an 'out' quantity with all relevant 'in' quantities. In your case, row 5 needs to be matched with rows 1 and 3 because it uses both rows' quantities, as follows from the order of ins and outs.

You could try using a method like this. Take the two subsets of the table, ins and outs, and calculate two running totals for each, one including the current value (call it qty_from) and the other excluding it (i.e. the running total of all preceding values). Let us call the former qty_to and the latter qty_from. You will get these results for each subset:

for ins:

qty_from  qty_to
--------  ------
0         2000
2000      3800

for outs:

qty_from  qty_to
--------  ------
0         30
30        1030
1030      2010

Now join the two subsets using a well-known range matching method: A.from < B.to AND B.from < A.to. That will give you intersecting ranges – or, translating into our problem, matching ins and outs. For the example given, the resulting set will look like this:

ins.qty_from  ins.qty_to  outs.qty_from  outs.qty_to
------------  ----------  -------------  -----------
0             2000        0              30
0             2000        30             1030
0             2000        1030           2010
2000          3800        1030           2010

You can see that the first 'in' range repeats three times. That is because the first 'in' row lends its quantity to three 'out' rows. You can also see that the last 'out' range is repeated too, meaning it borrows from both 'in' rows.

Now that ins and outs are successfully matched, the big issue is to correctly determine the quantity that an 'out' row borrows from an 'in' row in case it matches more than one 'in'. There may be variations on the logic to use, here is one:

if ins.qty_to < outs.qty_to,

if ins.qty_from > outs.qty_from,

take the current ins.qty

otherwise,

take ins.qty_to - outs.qty_from;

otherwise,

if outs.qty_from > ins.qty_from,

take the current outs.qty

otherwise,

take outs.qty_to - ins.qty_from.

It only remains to apply your formula to the obtained values, which is essentially SUM(ins.unit_price * borrowed_qty) / outs.qty.

Translating all the above into SQL, we can get a query like this:

SELECT
  outs.id,
  outs.qty,
  ROUND(SUM(x.borrowed_qty * ins.unit_price) / outs.qty, 5) AS unit_price,
  outs.date,
  outs.action
FROM
  (
    SELECT
      *,
      COALESCE(SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'in'
  ) AS ins
  INNER JOIN
  (
    SELECT
      *,
      COALESCE(SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'out'
  ) AS outs
  ON  ins.qty_from < outs.qty_to
  AND outs.qty_from < ins.qty_to
  CROSS JOIN LATERAL
  (
    SELECT
      CASE
        WHEN ins.qty_to < outs.qty_to THEN
          CASE
            WHEN ins.qty_from > outs.qty_from THEN -ins.qty
            ELSE -(ins.qty_to - outs.qty_from)
          END
        ELSE
          CASE
            WHEN outs.qty_from > ins.qty_from THEN outs.qty
            ELSE -(outs.qty_to - ins.qty_from)
          END
      END
  ) AS x (borrowed_qty)
GROUP BY
  outs.id,
  outs.qty,
  outs.date,
  outs.action
;

A demo of this solution can be found at Rextester.


As Martin Smith said in the comments, there is no Date/Time based trigger, therefore you have two options.

Batch Update - Use a batch process to update the data.  This requires determining how often the data needs to change and then creating a batch process that runs at least that often to update the data.  If the information changes once a day this might be acceptable, but as the update need gets more frequent this solution becomes less usable.
Dynamic Evaluation - As Martin Smith suggested, determine availability when the information is requested.  This can be in the select statement itself or you can use a computed column on the table.  In either case the data will be as up to date as it can be.


Restarting primary SQL Server instance with log shipping

It has happened in our case wherein we had to restart our secondary server or log shipping monitoring server as some patches were getting applied and we don't have clustering on them. Our log shipping job is configured to take backup at primary server every 15 minutes, copying and restore also at every 15 minutes however there is gap of 5 minutes in between each of them considering network latency. Only impact we had was one of the job which was scheduled during that window got skipped however no impact on log shipping as lsn number caught up itself on the next copy and restore.

Based on above experience, I don't expect any issue to happen in your log shipping even if you don't  disable any jobs as the backups will not be taken if primary is down , since remaining two jobs are on secondary server.  Log sequence number will catch up on next backup.

I hope this helps.


Is there a way to get slow-query-log-like profiling information on a single query?

I think you're looking for the "show profiles" query.  See http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html


Tracing, debugging and fixing Row Lock Contentions

Would it be possible to find out which
  line of a stored procedure is causing
  these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

  What would be the general guideline to
  reduce/avoid/eliminate such problems
  with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer."  Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue.  To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently.  Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values.  For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;.  Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used.  If the function is necessary consider making it a function based index.
Think in sets.  Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement.  If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT.  For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2.  You can then catch the inability to lock the row and inform the user that another session is modifying the same data.


Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server

Please keep in mind that OPTIMIZE TABLE does not perform defragmentation. Internally, OPTIMIZE TABLE perform several operations (copying data to a temp file, recreate indexes, recompute index statistics). In fact, the example I have can be performed manually as shown.
Example: If you optimize mydb.mytable, you enter this command:
OPTIMIZE TABLE mydb.mytable;

Note that mysql performs something the following under the hood:
CREATE TABLE mydb.mytable2 LIKE mydb.mytable;
ALTER TABLE mydb.mytable2 DISABLE KEYS;
INSERT INTO mydb.mytable2 SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable2 ENABLE KEYS;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytable2 RENAME mydb.mytable;
ANALYZE TABLE mydb.mytable;

This is quite useful for tables that experience a high volume of UPDATEs and DELETEs
Performing this can accomplish two things

Prevent mysql from looking through fragments in a table in an attempt to load data into the right sized fragments. Eliminating these fragments will reduce this operation.

Having the index statistics recomputed helps the MySQL Query Optimizer construct better EXPLAIN plans. Otherwise, queries may deteriorate in execution time because the MySQL Query Optimizer decided to take bad guesses at the EXPLAIN plan. This would be a definite symptom of a table that has had a high volume of UPDATEs and DELETEs.

CAVEAT
With regard to caching, caching takes a dive quickly because of doing a full table scan. For MyISAM index pages flow in and out of the MyISAM Key Cache. For InnoDB, data and index pages flow in and out of the InnoDB Buffer Pool.


Reject Specific Database PostgreSQL User Authentication

If those two databases are on the same cluster (beware of the PostgreSQL terminology, it's a name for an instance or installation or whatever you want), then you cannot prevent the users from logging in.  All databases on a single cluster share the same users:

  Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database).

As Neil McGuigan pointed out in his comment, this is not completely true - one can set db_user_namespace to have database specific user names.  I don't recommend using this, the reason is in the documentation:

  Note: This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed.

Also, if user B knows the password of a@databasea, this cannot prevent him from logging in.

What you can do, however, is preventing user A from doing anything in databaseB by revoking every permission there might be, especially connecting to a database.  The background is that logging in to a cluster is not the same thing as connecting to a database.  Preventing the latter is easy:

REVOKE ALL ON DATABASE databaseb FROM a; -- ALL means CONNECT, too

Please note that pg_hba.conf does its work also on the database level, so you can disallow connections there, too:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
host    databasea       a               10.228.116.0/24         md5
host    databaseb       b               10.228.116.0/24         md5


When postgresql sequences get out of sync?

There are a few possible cases where things can get out of sync.

Very old versions (unsupported) used to sometimes fail to set sequences on backup restore.  If you have manual backup and restore routines, this is somewhere to look.
setval('sequence_name', 1) will set to to 1.

Those are your only two possibilities unless you have a short cycle, and are cycling.


Uninstalling Oracle Components on Production

have the datafiles from the currently active production database
  restored on the new database, then change over to the newly populated
  database on production

This will not work at all, unless you meant the transportable tablespace method and you intend to restore user tablespaces, and you create the new database using different binaries without the unnecessary options.

Sure, you can do this with TTS. Or export/import. These methods require downtime.

Or you could just remove options from the existing database. It all depends on the components you want to remove if you need downtime for this.

Some components you can remove at binary level (which requires a database shutdown) using chopt starting with version 11.2 (which is the lowest currently supported version):

Chopt Tool

For example, do disable Partitioning, shutdown the database, then:

chopt disable partitioning

The removal of other options ususally include running scripts provided by Oracle and/or dropping schemas.

For example to remove APEX, you run the below in a regular (non-cdb) 12.2 database:

$ORACLE_HOME/apex/apxremov.sql

More details at (referring this blog post of an Oracle employee, because I could not find all this nicely collected together in the official public documentation):

Component Clean Up Series


Prevent UPDATE TRIGGER to fire when no changes made in SYBASE

I don't know SyBase but from the documentation, it appears as if you can check whether one or more columns are updated. It should be sufficient to check if one is updated like:

create trigger test_utrig on test
for update as
    if update (id)
        insert into audit..test_a (updated_by, date, Id, ,NAME, DESC)
        select update_by(), getdate(), inserted.Id, inserted.NAME, inserted.DESC
        from inserted

It appears to be a mismatch between the number of columns in the trigger and in the audit table, so I removed the constant 'update'.


MySQL in Ubuntu Linux - I cannot connect to the server

AppArmor is a Linux kernel security module that allows the system administrator to restrict programs' capabilities with per-program profiles. Have you tried to disable it and see if the connection works?
Check here on how to do that: help.ubuntu.com/community/AppArmor#Disable_AppArmor_framework
Another idea you can check with ss -tnl if you can see localhost:3306 on listening mode, if ss is not available use netstat -tnl
$ man ss
ss - another utility to investigate sockets
-t, --tcp
    Display TCP sockets.

-n, --numeric
    Do not try to resolve service names. Show exact bandwidth values, instead of human-readable.

l, --listening
    Display only listening sockets (these are omitted by default).

# Usually something like that means you have the service listening on
# localhost port 3306 in that case the port you configured your mysql to be running
State   Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process
LISTEN  0        4096           127.0.0.1:3306          0.0.0.0:*


How to check the status of log file restore for SQL Server

You can run the following code to see how much longer a backup or a restore will take. It also tells you how long a DBCC checkdb will take and a few other commands as well.

SELECT session_id, command, s.text, start_time, percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
    + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
    + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
    + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
    + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s


Checking SQL Server's memory utilization

You can run DBCC MEMORYSTATUS at any time (or probably better to look at the sys.dm_os_memory* DMVs).

As for getting the data from an hour ago, that would require some kind of collection using a monitoring tool, MDW, etc. SQL Server doesn't keep snapshots of memory utilization for all of time - that would get pretty expensive.