Linked server available to users without permissions

There is no way to "hide" a linked server or prevent unauthorized users from trying to use it. All you can control is whether they actually have access on the other side. A linked server in and of itself is not an actual object; like a synonym, you don't grant access to the alias, just to the thing(s) it references.

Try it out, and you will see that some of your assumptions (e.g. that "a login ... can not see the linked server in object explorer") are false. I did this:

Created a login, and granted no privileges whatsoever, just CONNECT / public role.
Created a linked server, and did not add this login to the list of logins.
Opened a new instance of Management Studio, connecting with this login.
I was able to see all linked servers under Server Objects > Linked Servers.
I was also able to query sys.servers to see the list of linked servers.

I was able to run the following query no problem:

SELECT name FROM [linked server].master.sys.objects;

However I was unable to run queries against non-system databases, since I had not granted explicit access to any non-system database or the objects inside. E.g.:

SELECT SalesOrderID FROM [linked server].AdventureWorks2012.Sales.SalesOrderHeader;

Yielded this error:

  Msg 7314, Level 16, State 1, Line 1
  The OLE DB provider "SQLNCLI11" for linked server "linked server" does not contain the table ""AdventureWorks2012"."Sales"."SalesOrderHeader"". The table either does not exist or the current user does not have permissions on that table.

I expect that you can achieve more restrictive results by explicitly denying access to specific objects on the linked server (or by DENY CONNECT SQL to that login altogether), but this does not limit its effect to the linked server; it also affects direct connections.

In any case, I fail to see the point of what you're trying to accomplish:

If the user doesn't have access on the other side, what is the harm in letting them try? (You might have better luck telling them not to and auditing them than trying to prevent them from trying in the first place.)
If the user does have access on the other side, why can't they use the linked server?


If you are sure that you did not lose any data and the problem is only at redolog level you can issue (as sysdba):

alter database open resetlogs;

backup all the datafiles, control files and online redo log before issuing the command  so you can "rollback" if something goes wrong.


What is the best way to create tables for email campaigns?

True, Postgres 9.1 does not have json / jsonb data types. But there is the additional module hstore providing the data type of the same name.

hstore has been in the core release since Postgres 8.3 (it's very mature). For just text data and no nesting it's just as good as json, if not better. (jsonb introduced some new candy.)

If you only have a few dozen of sparsely populated columns I would also consider wide rows with many columns just being NULL. NULL storage is very cheap in Postgres.

Many columns vs few tables - performance wise

You could also go with an EAV (Entity-attribute-value) design. Related answer discussing pitfalls:

Is there a name for this database structure?

But consider upgrading to a current version of Postgres first. Postgres 9.1 reaches EOL in Sept 2016.


Does using a view over standard queries and table indexes improve performance on large databases?

In general, I would say that you are right. Views are nothing more than a query stored in the catalogue of the DBMS.

Certain types of views (Materialized Query Tables, MQT) can materialize rows on disk but they have problems of there own (the cost of keeping them up to date). They are typically used for data warehouses and not so much for heavy OLTP due to the cost of maintaining them. This is a new concept in MySQL that I was not aware of. @Verace pointed me to the following links in his comment:

https://github.com/greenlion/swanhart-tools
http://www.fromdual.com/mysql-materialized-views .

Another thing that comes to mind is statistical views. At least Db2 have this possibility. In short, you enable a view to become a statistical view, and then you can update statistics for that view. It can be a great help for the optimizer to have statistics for a join instead of using a formula. I don't think this concept exists in MySQL.

With these minor remarks I would say that it's definitely wrong to claim that views in general "allow for much better performance.".

When it comes indexes, I must say that this claim is probably one of the weirdest ones I've heard. Using an index to access a row is O(log n) complexity compared to O(n) for accessing the row directly in the table. Therefore the benefit of an index grows as the table grows in size, it does not become useless when the table reaches some particular size.


Please review the My.INI settings of my PC. File Attached

You need more RAM.  Windows can barely survive with a Gig of RAM.  I would put at least 4 Gigs in the machine.


Imagine there is a row in "Movie" where id is 42.

Then imagine that there are two rows in "UserMovieWatchProgress" where "movieID" is 42.

Then the result of

"Movie" LEFT JOIN "UserMovieWatchProgress"
   ON "UserMovieWatchProgress"."movieID" = "Movie"."id"

would be something like

 "Movie".id | "UserMovieWatchProgress"."movieID"
------------+------------------------------------
         42 |                                143
         42 |                                 93

Now consider the following query:

SELECT "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id"
GROUP BY "Movie".id;

By definition of GROUP BY, there can be only one result row per "Movie".id.
Which number should we take for "UserMovieWatchProgress"."movieID"? That is unclear and not well defined, so this is forbidden by the SQL standard.

Now if PostgreSQL can deduce that there can be only one possible "UserMovieWatchProgress"."movieID", it will allow you to omit the column from the GROUP BY clause.

If you just want any of the "UserMovieWatchProgress"."movieID" in the query result, and you don't care which one, you can do the following in PostgreSQL:

SELECT DISTINCT ON ("Movie".id)
       "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id";

Perhaps that is what you are looking for.


SQL Server CPU spikes - possibly non-query related - suggested indicators

The first thing I would suggest you check is your "max degree of parallelism" via sp_configure.  By default this is set to 0, so a parallel query will consume all CPU on the box (up to 64 threads) until it finishes if this setting is unchanged.  I've seen this occur on more systems than I care to admit over my career, and it tends to be even more frustrating on the larger servers (with a higher number of CPUs).  If this is set to the default of 0, the recommendation from MS on what this should be can be found here: http://support.microsoft.com/kb/2806535

If it's a smaller server with very few CPUs (e.g. 1 or 2) it could be something as trivial as auto-update statistics kicking in, though that has been reportedly rare... but rare still happens.  You'd have to check if that is the culprit via a profiler trace to be certain though.


What you are proposing is not really CAP Theorem unless the cluster is totally a share-nothing architecture. Please note what CAP Theorem is from my old post from Jan 01, 2013 : Consistency in ACID and CAP theorem, are they the same?
CAP

Consistency (All Nodes Have Same Data via Eventual Consistency)
Availability
Partition-Tolerance : system continues to operate despite arbitrary message loss or failure of part of the system

Each node would have a complete copy of the full dataset. The given DB Cluster would need to be support eventual consistency i.e. have ACID Compliance support cluster wide.
YOUR ACTUAL QUESTION

But is it true in fact of using MySQL? Or maybe we need to use any kind of middleman? How does CAP theorem apply to MySQL?

The MySQL setups that operate on CAP Theorem are as follows:

InnoDB Cluster
MariaDB Galera Cluster
Percona XtraDB Cluster

Your best middleman ??? If the data you write is critical and needs to be available, read the data from the Master you wrote it. In any of the aforementioned Cluster Setups, any node can be a Master. If data is written on one Master, they should all have it and be ready to be read back provided all nodes in a Cluster run COMMIT on that same given transaction.
If you have slaves attached to these Masters, each slave must wait on its Master COMMIT for data to be be replicated and become visible on a slave.


FTs catalog in SQL Server for multiple FT index

Yes, you may add several FTS indexes to a single catalog, but a full text index can only be in a single catalog. Since a full text catalog is a database object, all indexes also have to be in the same database.

If you put all of the full text indexes for a database into a single full text catalog, a catalog reorganization will cover all of the indexes in the catalog.  This is usually fine unless your indexes are quite large.

The SQL Server Books Online describe the CREATE FULLTEXT INDEX here:
http://technet.microsoft.com/en-us/library/ms187317.aspx

If you desire to reorganize one table at a time, then you would want to use a catalog per table.


How to use fsyncLock() safely in backing up a single mongod

You could use a filesystem based snapshot (like LVM, EBS or similar) - as long as the snapshot is point-in-time and includes the journal then you do not need to lock the database at all when taking a backup.  Once the snapshot is complete, simply archive it off to your backup drive/host/cloud and then remove the snapshot.

Other options would require this to be a replica set. With a single node replica set you could use MMS backup (no other hosts required, but you do have to have an oplog, and it is not free).

If you added a second node (and an arbiter to break ties in voting), then your secondary could function as your backup.  You could take it down to make a backup without impacting your original node (again, as long as you have an arbiter), or you could use the techniques above also


Any way to speed up reorganize partition of a very large table in MySQL?

You may want to try Percona's pt-online-schema-change

pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.
pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.
The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see --chunk-time). This process is very similar to how other tools, such as pt-table-checksum, work. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table.
When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html


Which timeout(s) are causing my linked server query to time out?

You could try putting the remoteData into a temp table before doing the merge. a lot of your time will be taken up with comparisons of data not the actual retrieval so if you run the

SELECT remoteData.Name AS Name, remoteData.Value AS Value
into #remoteData
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    )

then in the merge statement

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM #remoteData as remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source

It may help (can't promise)


Is ASYNC_NETWORK_IO wait type anything to worry about?

As you said, this wait type indicates that the application is not keeping up with SQL Server. Now what that really means is, that SQL Server can't send the data over the network as fast as it would like.

There can be two underlying causes:

The app is written inefficiently and does not process the rows fast enough.
The network is maxed out.

If the application itself is too slow there will be no or no significant impact on the performance of other queries. If on the other hand the pipe is too small, other queries can't send their results either and have to wait.

In the latter case however you would have all connections waiting on ASYNC_NETWORK_IO. You should be able to see that impact clearly.


Why does MySQL consecutive JOIN give an unexpected result?

Use

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Category c
LEFT JOIN ( Product p
            INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL
            ) ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

or

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Product p
INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL
RIGHT JOIN Category c ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

fiddle

  I guess the Query1 did the LEFT JOIN between Category & Product first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Yes, if at least one outer joining is present then tables scanning is performed in query text ordering (like STRAIGHT_JOIN is used).

  It would be really thankful if you can suggest a name or a link so that I can do some research about it.

MySQL 8.0 Reference Manual  /  ...  /  JOIN Clause


Add constraint to ensure flag column is <= sum of column in other table

You can use a CHECK constraint defined against a UDF to enforce this, but this comes with many caveats.
You can also define INSERT and UPDATE triggers to do this same work.
In Oracle, you could create a materialized view that joined the two tables together and placed your RSEFlag column next to the appropriate sum.  Then you would apply a CHECK constraint to enforce that RSEFlag <= RSECode_SUM.  This Ask Tom discussion has an example of how to do this. (Search for alter table dept_tot_mvw add constraint and note that dept_tot_mvw is a materialized view.)

Unfortunately, you cannot use this approach since constraints are not allowed on indexed views in SQL Server.


How to select blocks with sequential data and aggregate the ids

Basically it set a reset point each time there is a difference greater than 3 seconds between startDate and previous endDate.

Then set blocks (groups) by getting the sum of each reset point, and finally returns MIN/MAX dates and an array with the aggregated id's of every group.

WITH x AS
(
SELECT
    id,
    startDate,
    endDate,
    machine,
    case when
         date_part('second',
                   startDate -
                   coalesce(lag(endDate) over (partition by machine order by machine, startDate),
                            startDate - interval '10' second)) > 3
         then 1 else 0 end as reset

FROM
    tbl
WHERE
    machine = 6
    AND planned = false
ORDER BY
    machine,
    startDate
), y AS
 (
 SELECT
     id, startDate, endDate,
     sum(reset) over (partition by machine order by machine, startDate) grp
 FROM
     x
 )
 SELECT
     MIN(startDate) blockStartDate,
     MAX(endDate) blockEndDate,
     array_agg(id) ids
 FROM
     y
 GROUP BY
     grp
 ORDER BY
     grp;

blockstartdate             | blockenddate               | ids
:------------------------- | :------------------------- | :-------------------------
2019-01-16 07:10:46.984236 | 2019-01-16 07:39:23.786911 | {1989,1990,1991,1992,1993}
2019-01-16 07:50:06.887349 | 2019-01-16 08:00:03.221309 | {1996,1997,1998,1999}

db<>fiddle here


Why would you need to put a domain name when install a Oracle database?

It allows you to have more than one DB with the same name in the same network. If you have only one it can be the network domain.


SQL Server: compare names ignoring non-alpha

You can do it on the fly without storing the stripped down name, but that would mean recalculating the strings (e.g. Lisa's Nights, Lisa's Night, LISASNIGHTS) each time a new entry should to be checked against an existing string.

On the other hand, if your table also has a column for the STRIPPEDDOWNNAME, you can leverage SQL Server's constraints.  Create a UNIQUE CONSTRAINT (or a UNIQUE INDEX) on the STRIPPEDDOWNNAME column.  Then you will calculate the stripped down name only once, when you insert it into the table.  With the UNIQUE CONSTRAINT the SQL Server will immediately let you know if an inserted string is a duplicate.

You can then capture the error and respond as appropriate to your application.

I encourage you to let the SQL Server manage the duplicate check whenever possible.


Should I declare one or two tables to represent Items and Sub Items?

This looks like a straightforward one-to-many relationship, which as you suggested you can model by setting a foreign key in the child items table referring to the parent items table. As for performance, you won't have any kind of performance hit at all for a small site. Modelling your schema properly is your priority.

Consider renaming the tables too. For example, if the shop offers a cleaning service then you could think of the tables in terms of Services (e.g. car) and Costs (seats, trunk, etc)


How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

As others have already pointed out the info you want is in sys.dm_exec_sessions. However if you want to pull from both DMVs this would work:

SELECT c.connect_time
 , s.login_time
 , s.host_name
 , s.login_name
FROM sys.dm_exec_connections AS c
   JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id


possible to reference a jsonb_array_length in a where clause

This is the standard behaviour of SQL. SQL statements are (logically) processed in a certain specified order.

Following that order, the WHERE clause is executed before the SELECT one. That's why the WHERE clause cannot access the value of js_users_count: It has not yet been computed.

That's why your second query works, but your first one doesn't.

On the other hand, it is possible to have js_users_count on the ORDER BY, because that clause is processed after the SELECT:

SELECT
    queryable_type,
    jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
    queryables
WHERE
    queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1
ORDER BY
    js_users_count desc;

Don't be afraid about a certain computation being done twice. It can be either optimized (as some kind of common subexpression elimination optimization) or its impact is completely neglectable in the overall performance of a query.

If you want to really make sure that an expression is not evaluated twice, you can use one (often annoying) feature of PostgreSQL CTE: the fact that they're optimization fences (i.e.: they won't be inlined, or converted to subqueries in the final statement):

WITH q1 AS
(
    SELECT
        queryable_type,
        jsonb_array_length(as_data->'users_who_like') as js_users_count
    FROM
        queryables
    WHERE
        queryable_type = 'Item'
)
SELECT
     *
FROM
    q1
WHERE
    js_users_count > 1
ORDER BY
    js_users_count desc;

This will create a (virtual temporary) table q1 with the already computed values of jsonb_array_length. I would only do this kind of thing if the function used takes a very long time to execute (has a very high cost), and there's evidence that it's actually called twice (you can check with some RAISE DEBUG statements in the function itself, if it's been written by you).

References:

SQL Query Order of Operations
You Can Reference Your Derived SELECT Columns In Your ORDER BY Clause In SQL
SQL Programming: Understanding the Logical Order of Operations in SELECT statements
Order of execution of a Query


AWS RDS showing more disk usage than PgAdmin statistics

It seems to be a lot of junk produced by the failed operations. A vacuum fix this issue.


ERROR 1297: Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER when importing data

By reading some forums, the variables that avoids this issue are:

MaxBufferedEpochs
TimeBetweenEpochsTimeout

This must be configured in MGMT Node

/var/lib/mysql-cluster/config.ini

As we can see in MySQL Documentation, the default for MaxBufferedEpochs is 100, the default for TimeBetweenEpochsTimeout is 4000, change this for the needed value.


PostgreSQL Give idetifier for transaction?

No, you can't assign a transaction an arbitrary user-defined identifier.

Transactions already have transaction IDs, but only read/write transactions get true, persistent ones.

  I'm asking this if transaction has identifiers then true asynchronous database call will be possible with frameworks like Netty or Vert.x

Having a transaction identifier would do you no good there anyway.


The easiest way if you just want to do a quick CREATE VIEW on your destination server would be to script it out:

Right-click your view
Script View as -> CREATE to -> (select your choice here)

The possible choices as a destination for the CREATE VIEW script can be a new query window, a file, the clipboard, or an agent job.  If you need to run this create script at a later date, the file destination is probably your best bet.  But if you want to run it immediately, and you have access to the server, just script it to a new query window and connect to your destination instance/database and run the CREATE VIEW script.


There is some space used for the upper levels of the B-tree (how much depends on how large the table is).  However if you look at a Clustered index and a Non Clustered index of the same data (exact same keys and including all columns of the table in the non clustered index) the upper levels of the B-tree would look very similar (if not the same).  The difference comes when storing the data.  A clustered index is the data in the table where as a non clustered index makes a copy of the data in the table (resulting in double the space taken if you include every column in the table in the index, either in the keys or as included columns).

Because a non clustered index makes a copy of the data it is said to take more space than a clustered index when it comes to the data level.


Consistency in ACID and CAP theorem, are they the same?

They are not really the same, because of the scope of the data
ACID

Atomicity
Consistency : All Applied Data Changes Provide Consistent View of Data For All DB Connections
Isolation
Durability

CAP

Consistency (All Nodes Have Same Data via Eventual Consistency)
Availability
Partition-Tolerance : system continues to operate despite arbitrary message loss or failure of part of the system

SYNOPSIS

ACID addresses an individual node's data consistency
CAP addresses cluster-wide data consistency


Is it secure to set default value for password field?

I see only 2 options to get it to work :

Application inserts value for password field
Table has NULL or default value for password field

If the column allows NULL, that should be enough, right?
If you want it to have a value, having the application insert it, across the network, is probably riskier, than setting a default value in the table.

Setting the same default value for all users may also pose a slight risk (someone gets a hold of the hash and tries to reverse-engineer). If you want to avoid that too, you can use a BEFORE trigger to set a computed default value based on username+timestamp as salt for the encrypted password field.


Can I create a table and database on a slave without breaking replication?

If you're not planning to switch master and you're sure the tables / databases are never going to exist in the master, you can have them without breaking replication. I have a setup like that running for some years now and working just fine.


How do you unescape "C-like backslash escape sequence" that come from a function?

Pg doesn't appear to expose its string parsing/lexing to SQL. It has quote_literal etc but doesn't seem to have the reverse. So I'm not aware of another way to do this without breaking out into a PL or writing a simple C extension.

e.g.

CREATE OR REPLACE FUNCTION pyunquote(quoted text) RETURNS text AS $$
return quoted.decode("string-escape")
$$ LANGUAGE plpythonu;

then

test=# SELECT pyunquote('\n\nAAA');
 pyunquote
-----------
          +
          +
 AAA
(1 row)

but note that this uses Python's string escape rules, not PostgreSQL's.

To get PostgreSQL's rules, you could use the following horribly insecure approach and trust whatever the function passes you:

DO
LANGUAGE plpgsql
$$
DECLARE
  x text;
BEGIN
  EXECUTE format('SELECT E''%s''', '\n\nFOO') INTO x;
  RAISE NOTICE 'value is "%"', x;
END;
$$;

but of course someone can pass ';DROP TABLE customer;-- which won't be fun.


Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON

My understanding is that you are not approaching this in the right way. you just copy the login using the same sid to all the secondary server. Users get replicated since db is part of AG.

Basically you want to create/replicate logins that are created on primary server to all secondary servers in an AlwaysON availablity group topology.

You can just use dbatools Sync-DbaAvailabilityGroup that will take care of

  Syncs depdendent objects for availability groups. Such objects include:

SpConfigure
CustomErrors
Credentials
DatabaseMail
LinkedServers
Logins
LoginPermissions
SystemTriggers
DatabaseOwner
AgentCategory
AgentOperator
AgentAlert
AgentProxy
AgentSchedule
AgentJob

  Note that any of these can be excluded.

Edit : Below script will help you :

SET NOCOUNT ON
DECLARE @user_to_be_added sysname
DECLARE @sqltext nvarchar(max) = N''

-- change below to your user to be added .. make sure that the login is already existing
SET @user_to_be_added = 'Domain/Login'
SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)
FROM sys.dm_hadr_availability_replica_states ars
inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
 where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0
and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.

 if  (@sqltext <> '')
 begin
  select @sqltext
  end
  else
    begin
    print 'There is nothing to print - @sqltext is blank'
    end


Oracle Enterprise Manager - stats split by user (schema)

You should be able to find what you're looking for by clicking the "Top Activity" link in the bottom right.  When you get to the Top Activity page, try changing the drop down menu on the right side from "Top Sessions" to "Top Clients." You can then click on an individual user session to get more details about that session.


As per the code in your comment:

USE msdb

EXEC sp_send_dbmail
    @profile_name = 'SQL Mail',
    @recipients = 'vince.chan@ufa.com',
    @subject = 'T-SQL Query Result',
    @body = 'The result from SELECT is appended below.',
    @query = '(include script from above)'

The reason this won't work is the same reason why this wouldn't work:

declare @query varchar(2048);

set @query = 'Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date
 GO';

 exec (@query);

You're not taking into account the single quotes in the query string.  Something like this should be what you need:

declare @query varchar(2048);

set @query = 'Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date';

 exec (@query);

Therefore, your constructed query string should be:

'Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date'

You need to use two single quotes in a string that contains a single quote.


Which java JDK is compatible with oracle instant client 10g

Java does not use the Instant Client. If you want to connect an Oracle database from Java code, then you need the Oracle JDBC driver, not the Instant Client.

https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html

Example:

https://stackoverflow.com/questions/47966597/how-to-connect-java-with-oracle-8i-database-on-local-network

You will still need to find a place to download such ancient version of these though.


SQL Server default collation vs database with different collation - potential problems?

... new DB that has to be in a different collation (Y Collation) than the SQL Server Instance default collation (X Collation).

Why does the new database need to be in a different collation?

  Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on cols.

Correct. Though you do not need to use COLLATE DATABASE_DEFAULT, and in some cases that can be entirely incorrect / bad. The requirement is that either A) the collation is the same for string columns being joined, or used in concatenation / UNION, etc, or B) you specify the COLLATE clause in the predicate or expression to override one or more of the columns.

Meaning, if you know that your column has a collation of Z and your database has a default collation of Y, then create the temp table specifying COLLATE Z for that column. Keep in mind that table variables use the database's default collation, not the instance's collation. And, contained databases are yet another set of rules.

  What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation?

Built-in functions usually return a string in the database's default collation, unless you pass in a column or an expression using the COLLATE clause.

  do you see any potential data loss?

It depends on what datatype(s) you are using and what collations you are using. NVARCHAR / NCHAR / NTEXT / literals prefixed with an uppercase "N" are Unicode-only (UTF-16) and so there is no potential data loss if switching collations on those. VARCHAR / CHAR / TEXT / literals not prefixed with an uppercase "N" are 8-bit encodings, the particular encoding being determined by the code page association with the collation being used. Here there is potential data loss if switching collations to one that uses a different code page. But even that is not a guarantee of data loss since data loss only occurs when there are characters in the data that are not available in the new code page. (technically speaking, you can also experience data loss when mixing various combinations of single-byte character sets, double-byte character sets, UTF-8, and UTF-16, depending on which direction you are going in, but this is not something most people run into -- though more people likely will as more folks start using the new UTF-8 collations starting in SQL Server 2019)

  or maybe any different problems?

Just keep in mind that:

Instance-level collation controls:

variable name / cursor name / GOTO label resolution. It won't matter what the database-level collation is.
instance-level metadata: login names, server names, server role names, server-level objects, database names, etc.
default collation for:

newly created databases (unless the CREATE DATABASE statement included the COLLATE clause)
tempdb metadata (names of temp object / constraints / indexes / defaults / permanent tables / etc)
columns in temp tables and permanent tables in tempdb

Database-level collation controls:

database-level metadata: user names, schema names, database role names, schema-bound object names, etc.
default collation for:

columns in table variables, permanent tables, TVF results

string literals
string variables (the contents of the variable, not the name)
return value of scalar UDFs

For a more thorough list of what collations do at the various levels and what to look out for when changing them, please see the following post of mine:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?


SSMS: Show execution plan for a stored procedure as a single tree

The Estimated plans (CTRL + L) show a tree structure. Actual plans (CTRL + M) do not.

I've never seen a tree for a procedure in an actual plan (and I never use estimated plans anyway)


Oracle PL/SQL does not work -- bind variable not allowed

You can't pass a procedure as a bind variable.
You should be able to dynamically build the entire anonymous PL/SQL block and execute that.  Something like this
declare
  l_plsql varchar2(1000);
begin
  l_plsql := 'begin
                dbms_hs_passthrough.execute_immediate@mylink ...
              end;';
  execute immediate l_plsql;
end;


Select distinct values between two values in different field

PROPOSED QUERY

SELECT B.num year FROM
(SELECT start_year,end_year FROM products WHERE product_name='apple') A,
(SELECT tth*10000+th*1000+h*100+t*10+u num FROM
(SELECT 0 tth UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TTHN,
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) THN,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) HN,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TN,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UN) B
WHERE num BETWEEN A.start_year AND A.end_year ORDER BY B.num;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS EmaOnTheBlock;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE EmaOnTheBlock;
Query OK, 1 row affected (0.00 sec)

mysql> USE EmaOnTheBlock
Database changed
mysql> CREATE TABLE products
    -> (id int not null auto_increment,
    -> product_name varchar(20),
    -> start_year smallint not null,
    -> end_year smallint not null,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO products (product_name,start_year,end_year)
    -> VALUES ('wine',2001,2012),('cheese',1999,2010),('apple',2008,2015);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM products;
+----+--------------+------------+----------+
| id | product_name | start_year | end_year |
+----+--------------+------------+----------+
|  1 | wine         |       2001 |     2012 |
|  2 | cheese       |       1999 |     2010 |
|  3 | apple        |       2008 |     2015 |
+----+--------------+------------+----------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT B.num year FROM
    -> (SELECT start_year,end_year FROM products WHERE product_name='apple') A,
    -> (SELECT tth*10000+th*1000+h*100+t*10+u num FROM
    -> (SELECT 0 tth UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TTHN,
    -> (SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) THN,
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) HN,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TN,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UN) B
    -> WHERE num BETWEEN A.start_year AND A.end_year ORDER BY B.num;
+------+
| year |
+------+
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
+------+
8 rows in set (0.03 sec)

mysql>

GIVE IT A TRY !!!


Restore of a database failed - Table does not contain an identity column

Do the restore using T-SQL with the RESTORE DATABASE ... statement - that should provide a more meaningful error.

In brief, restore database works like this:

RESTORE DATABASE [database_name]
FROM DISK = N'\Path\to\the\backup\file.bak'
WITH RECOVERY
   , MOVE N'file name' TO N'new\path\for\this\file.mdf'
.
.
.

I wrote a blog post with code that automates creating the RESTORE DATABASE statement.  You simply point the code at an existing database backup file, and it will generate a valid restore command which you can then use to restore the database.

Using T-SQL to restore the database will provide a much more fine-grained set of error messages than using the SSMS GUI.

Also, I'd check the SQL Server Error Log to see if there are any pertinent details.  I also wrote a blog post with a quick script that can be used to show details from the Error Log.


It's not precisely because there are duplicate entries in the SELECT list, it's because the ORDER BY mentions a column that is listed twice in the SELECT list, and SQL Server doesn't know which one you mean (think about the case where you could say SELECT ID, ID = 1-ID..., where which one you mean is important).

The right fix is (in order of my personal preference):

remove the second instance of ID
give the second instance of ID a different alias (like RedundantID)
use ordinal - ORDER BY 1 (would work, but definitely not recommended)

If you can't fix the code in the app, and can't get support from the vendor, I'm not sure there's much else you can do except move back to the copy of the database running on a version that can run in the ancient compatibility level required to support this syntax.


Postgres group records by consecutive types

This is a gaps-and-islands problem, and in this specific case you can solve it using a method that involves two ROW_NUMBER calls to identify distinct "islands" of consecutive rows belonging to the same Fruit:
WITH
  partitioned AS
  (
    SELECT
      Fruit,
      FruitTime,
      ROW_NUMBER() OVER (                   ORDER BY FruitTime ASC) -
      ROW_NUMBER() OVER (PARTITION BY Fruit ORDER BY FruitTime ASC) AS IslandID
    FROM
      YourTable
  )
SELECT
  Fruit,
  MIN(FruitTime) AS StartTime,
  MAX(FruitTime) AS EnfTime
FROM
  partitioned
GROUP BY
  Fruit,
  IslandID
ORDER BY
  StartTime ASC
;

Demo at dbfiddle.uk: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f7402a206a9f12afec40ce242b2bca92


A VIEW is a shorthand for accessing the underlying table(s).  If the table(s) are not there, the VIEW won't work.

You can reach into other servers via FEDERATED.  See also Spider (a 3rd party product).  Each has limitations.

Master-Slave is predicated that the Slave always contains an exact copy of the Master.  (There are exceptions, but your question does not hit any of them.)


SSRS Rectangle visibility based on Parameter Value

Check out this answer. Basically, you want to combine all the values in your selected list using Join then use InStr to detect if the value in question is there.

So if you have Rectangle Rec1 which relies on param option "Rec1Visible" in parameter RecVisibility being selected, then an expression like this should work (pseudo names) for Rec1's visibility property:

=IIF(InStr(JOIN(Parameters!RecVisibility.Value,","),"Rec1Visible")>0,true,false)

Use the same code for each visibility expression, with the relevant value, to control the visibility of your rectangles.


How would I implement interprocess communication from Oracle (10g R1) trigger using DBMS_ALERT?

This could be done this way.  I don't like it much as it is not very elegant and looks fragile if unexpected things happen.

--1)Create a table in the user's schema or another new schema if you want separation.

CREATE TABLE DATA_AUDIT
( Primary_key RAW(16) default SYS_GUID() not null,
  Data_id  Number(10),
  Action   VARCHAR2(10),
  Date_created TIMESTAMP(6)DEFAULT CURRENT_TIMESTAMP);
--add foreign key constraint for Data_id to the parent Table
--add check constraint: Action in ('INSERT','UPDATE','DELETE');

--2) create a trigger on the parent table
CREATE OR REPLACE TRIGGER TGR_PARENT_TABLE
 BEFORE INSERT OR UPDATE OR DELETE
 ON PARENT_TABLE
 FOR EACH ROW
 v_action DATA_AUDIT.action%type;
BEGIN

    IF INSERTING THEN
       v_action := 'INSERT';
    ELSIF DELETING THEN
        v_action := 'DELETE';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSE
        --raise an error for an unexpected event and log it
        RAISE;
    END IF;
       Insert into DATA_AUDIT(Primary_key,Data_id, Action,Date_created) VALUES
       (SYS_GUID(), :old.Data_id, v_action,CURRENT_TIMESTAMP);
END;

--3) create a trigger on DATA_AUDIT
CREATE OR REPLACE TRIGGER TGR_DATA_AUDIT
 BEFORE INSERT
 ON DATA_AUDIT
 FOR EACH ROW

BEGIN

DBMS_ALERT.SIGNAL(:old.action, :old.data_id);
END;


Odd question with terms like temporary storage. General SQL is denormalized so fetching when need rather than a transform.

Question 1: results and be an expression like:

SELECT id, sex, IF(sex='m', 'f', 'm') as antisex FROM table

Question 2: A variable can contain only a single value. So from ref manual on SELECT INTO:

SELECT id, sex, IF(sex='m', 'f', 'm') INTO @id, @sex, @antisex FROM table LIMIT 1


Update different columns on different clauses

I would probably do something like this:

UPDATE tablename
SET col1 = col1 + CASE WHEN col1 > $VAR THEN 2 ELSE 0 END,
    col2 = col2 + CASE WHEN col2 >= $VAR THEN 2 ELSE 0 END
WHERE col1 > $VAR OR col2 >= $VAR

Make sure that col1 and col2 are indexed if you are only updating a few rows and tablename has many rows.


Replication Snapshot folder not cleaning up

The MSDN Blog "SQL Server SSIS and Replication" has a post from a few years ago.
http://blogs.msdn.com/b/mangeshd/archive/2009/05/20/replication-snapshot-folder-cleanup.aspx

How these old snapshot folders are purged depends on the type of replication you are using:

For snapshot and transactional replication: Distribution cleanup agent purges the non-latest folders
For Merge replication: the snapshot agent itself will purge older folders.

Chris Skorlinski, one of the developers on replication, had (from a few years ago) the following to say:  "For merge replication, the snapshot agent ... is responsible for cleaning up the last generated snapshot. The snapshot agent will only try once to remove the previous snapshot files so if those files were locked at that moment (by the merge agent e.g.), it is possible that snapshot files will be left over."

He recommends that you check the snapshot agent history to see if errors were reported.

If this is a continuing problem perhaps you could create a PowerShell script to remove the older snapshot folders. And, of course, you can manually delete them, though that is not a useful long-term solution.


Many sites have taken to using an email address as the login id.  This works well for me as I don't have to remember the user id that goes with the email address used by the site.  This solution would require the couple to share a profile, if they use the same email address.

Some mail sites allow adding a prefix following a punctuation character.  This gives email addresses like me.site@example.com or me+site@example.com.  This can be used to separate customers in the case like a husband and wife sharing an account.

Getting your data cleaned up will be a problem.  Instead of blocking users where there are multiple records with the same email address, you may want to offer to provide the user id(s) matching the email address.  I would send the id or list by email with a link that allows them to switch their id to the email address.

Some sites allow or require an alternate email address.  It may not be appropriate to make that address unique.  However, you may want to monitor the number of accounts linked to the same alternate address.


How to find the logical ordering of a page

The row offset array in an index leaf node page determines the logical order of rows within the page, which may differ from the physical order. Below is a demo script that uses the undocumented DBCC IND. Run only on  a test system.

USE tempdb;

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.ClusteredIndexExample', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.ClusteredIndexExample;
END

CREATE TABLE dbo.ClusteredIndexExample(
     KeyData varchar(10) NOT NULL
        CONSTRAINT PK_ClusteredIndexExample PRIMARY KEY CLUSTERED
    ,OtherData varchar(1000) NOT NULL
    );
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 1', REPLICATE('1', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 3', REPLICATE('3', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 4', REPLICATE('4', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 2', REPLICATE('2', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 5', REPLICATE('5', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 6', REPLICATE('6', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 7', REPLICATE('7', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 8', REPLICATE('8', 1000));
INSERT INTO dbo.ClusteredIndexExample VALUES ('Row 9', REPLICATE('9', 1000));

IF OBJECT_ID(N'tempdb..#dbcc_results', 'U') IS NOT NULL
BEGIN
    DROP TABLE #dbcc_results;
END

CREATE TABLE #dbcc_results(
     PageFID    int
    ,PagePID int
    ,IAMFID int
    ,IAMPID int
    ,ObjectID int
    ,IndexID int
    ,PartitionNumber int
    ,PartitionID bigint
    ,iam_chain_type varchar(20)
    ,PageType int
    ,IndexLevel int
    ,NextPageFID int
    ,NextPagePID int
    ,PrevPageFID int
    ,PrevPagePID int
    );

DECLARE
     @DBID int
    ,@ObjectID int
    ,@PagePID int
    ,@PageFID int;

SELECT
     @DBID = DB_ID()
    ,@ObjectID = OBJECT_ID(N'dbo.ClusteredIndexExample');

INSERT INTO #dbcc_results
    EXEC sp_executesql N'DBCC IND(@DBID, @ObjectID, 1, 1) WITH NO_INFOMSGS;'
        ,N'@DBID int, @ObjectID int'
        ,@DBID
        ,@ObjectID = @ObjectID;

--return DBCC results to client
DBCC TRACEON(3604) WITH NO_INFOMSGS;

--get data pages for this table, following forward pointers in logical order
DECLARE data_pages CURSOR LOCAL FAST_FORWARD FOR
WITH data_pages AS (
    SELECT
         PageFID
        ,PagePID
        ,0 AS PageSequence
    FROM #dbcc_results
    WHERE
        PageType = 1 --data page
        AND PrevPageFID = 0
    UNION ALL
    SELECT
         #dbcc_results.PageFID
        ,#dbcc_results.PagePID
        ,data_pages.PageSequence + 1 AS PageSequence
    FROM #dbcc_results
    JOIN data_pages ON
        data_pages.PageFID = #dbcc_results.PrevPageFID
        AND data_pages.PagePID = #dbcc_results.PrevPagePID
    )
SELECT
     PageFID
    ,PagePID
FROM data_pages;

OPEN data_pages;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM data_pages INTO @PageFID, @PagePID;
    IF @@FETCH_STATUS = -1 BREAK;

    --dump page: NOTE physical order within page may be different than logical order
    DBCC PAGE(@DBID, @PageFID, @PagePID, 2) WITH NO_INFOMSGS;

END
CLOSE data_pages;
DEALLOCATE data_pages;

--don't return DBCC results to client any more
DBCC TRACEOFF(3604) WITH NO_INFOMSGS;

--cleanup
DROP TABLE dbo.ClusteredIndexExample, #dbcc_results;
GO


How to find similar (but different) names with the same soundex value?

You can use a window function for this:

select *
from (
   select name,
          count(*) over (partition by soundex(name)) as cnt
   from poi
) t
where cnt > 1;


How to get old and new values on Redis notification?

As a workaround, you could have a field OLD_VALUE that is filled by your application logic together with the real field in a transaction. Doing so, you would also get a notification for the old value


Multiple concurrent "REFRESH MATERIALIZED VIEW": how to manage?

It looks like you want to refresh the materialized views whenever the data in the tables change. If you do that with materialized views, it will take a long time, and updates will block each other and queries.

Maybe you can build your own “on commit refresh” materialized views as tables.

A simple example:

Instead of

CREATE MATERIALIZED VIEW sum_eumel AS
SELECT eumel_category,
       sum(eumel_data) AS eumel_sum
FROM eumel
GROUP BY eumel_category;

you could do this:

BEGIN;

CREATE TABLE sum_eumel (
   eumel_category text NOT NULL PRIMARY KEY,
   eumel_sum bigint NOT NULL DEFAULT 0
);

CREATE FUNCTION eumel_trigger() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP IN ('UPDATE', 'DELETE') THEN
      /*
       * Will leave rows with value 0 after the last
       * row for a category has been deleted.
       */
      UPDATE sum_eumel
      SET eumel_sum = eumel_sum - OLD.eumel_data
      WHERE eumel_category = OLD.eumel_category;
   END IF;

   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      INSERT INTO sum_eumel (eumel_category, eumel_sum)
      VALUES (NEW.eumel_category, NEW.eumel_data)
      ON CONFLICT TO UPDATE
      SET eumel_sum = sum_eumel.eumel_sum + EXCLUDED.eumel_data
   END IF;

   IF TG_OP = 'TRUNCATE' THEN
      TRUNCATE sum_eumel;
      RETURN NULL;
   END IF;

   IF TG_OP = 'DELETE' THEN
      RETURN OLD;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

CREATE TRIGGER eumel_dml_trig
   AFTER INSERT OR UPDATE OR DELETE ON eumel
   FOR EACH ROW EXECUTE PROCEDURE eumel_trigger();

CREATE TRIGGER eumel_truncate_trig
   AFTER TRUNCATE ON eumel
   FOR EACH STATEMENT EXECUTE PROCEDURE eumel_trigger();

INSERT INTO sum_eumel
SELECT eumel_category,
       sum(eumel_data) AS eumel_sum
FROM eumel
GROUP BY eumel_category;

COMMIT;


interpreting how much data has been lost by analyzing dbcc output

Scanning through the output rather quickly, looks like you lost about 1,045 pages (so 1045 x 8192 bytes). I did this simply by performing a find for "has been deallocated" in the pastebin and seeing how many occurrences of that term were discovered - 1,039. Then I searched for "actual 0:0" and that yielded 6 more pages that weren't found.

No way to guess how many rows that represents. But not sure how that helps you anyway - whether you lost 100 pages or 1,000 pages, or 800 rows or 8,000 rows, what are you going to do with this information?

Whether you can find any more detailed information about the actual data from the transaction log depends on a couple of things, e.g. what recovery model are you in, why were there no backups (because they were on the same drive or because none had ever been taken), and how good are you with a hex editor?


How do I delete a file that has been locked by UTL_FILE?

Is the session dead?

SELECT sid, serial# FROM v$session WHERE /* conditions */;

-- ALTER SYSTEM KILL SESSION '<sid>, <serial>';
ALTER SYSTEM KILL SESSION '123, 456';

If killing the session doesn't work, you can explicitly close the file handle with a SysInternals utility called Handle.

https://technet.microsoft.com/en-us/sysinternals/bb896655

I also found a SuperUser thread on this which mentions LockHunter and Process Explorer as other tools that can do the job:

https://superuser.com/questions/425727/how-to-unlock-file-without-kill-related-process


Oracle update statement using with clause takes for ever

Am I abusing the with statement?

No, you are abusing the UPDATE statement. With an UPDATE statement, the correlated subquery is executed for each row from final_table.

Updating values from an other table should be typically done with MERGE.

Example:

merge into final_table using
(
select c4,c5,c6
from
(
  with temp_table1(c1,c2,c3) as
  select c7,c7,c9 from source_table,

  temp_table2(c4,c5,c6) as
  select "some aggregation on c1,c2",c3 from temp_table

  select * from temp_table2
)
) inner
on inner.c6 = final_table.primary_key
when matched then update set final_table.destination_col1 = inner.c4,
                             final_table.destination_col2 = inner.c5;


MySQL Update: why would rows matched change based on the set clause?

I think I just figured it out...

MySQL must count the number of rows that will be updated, not all the ones that will be generated by the join that happens during the update. The difference being that if there are many [B] to one [A], and many [C] to one [B], joining all would create n([C]) rows, but updating [B] would only count n([B]). Adding a set for [C] makes this number n([B]) + n([C]).

Since the fields in the set clause are on different tables, it changes the number updated.


PARALLEL_MIN_TIME_THRESHOLD parameter when AUTO or MANUAL mode

The following quote is from your link, emphasis mine:

  PARALLEL_MIN_TIME_THRESHOLD is the second initialization parameter that controls automatic DOP. It specifies the minimum execution time a statement should have before the statement is considered for automatic DOP. By default, this is 10 seconds. The optimizer first calculates a serial execution plan for the SQL statement; if the estimated execution elapsed time is greater than PARALLEL_MIN_TIME_THRESHOLD (10 seconds), the statement becomes a candidate for automatic DOP.

Another good resource is Using Default Parameter Settings, emphasis mine:

  PARALLEL_MIN_TIME_THRESHOLD: The execution time, as estimated by the optimizer, above which a statement is considered for automatic parallel query and automatic derivation of DOP.

To answer your questions directly:

  Suppose the parallel_degree_policy is MANUAL and the SQL statement contains a parallel hint or a defined parallel DDL value. Will the Oracle optimizer still check PARALLEL_MIN_TIME_THRESHOLD?

No, PARALLEL_MIN_TIME_THRESHOLD will not be used. If you specify the degree of parallelism why would the query optimizer calculate a cost for the serial plan? What would it do with that information? You've already specified the DOP. This only makes sense in the context of automatic DOP.

  Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?

This has to be the estimated time. Does it make sense for Oracle to possibly execute most of the query in serial and then switch to a parallel plan? As far as I understand it that will not happen, DOP is chosen at the beginning of execution for a data flow operation.


ORA 01722: Invalid Number caused by different comparison

Expressions in SELECT evaluated after filter[s] in WHERE applied. So if phones for records with RATING <1 can be converted to number, you don't have errors. Simplified example :

SELECT
CASE WHEN n1=1 THEN 'a' ELSE 'b' END AS test_str
FROM
(
SELECT '1a' AS n1 , 0 AS n2 FROM dual
UNION ALL
SELECT '1' AS n1 , 1 AS n2 FROM dual
)
x
WHERE n2 =0 -- gives an error
-- WHERE n2 =1 -  runs fine


Is a clustered index locked while a (clustered index) scan is in progress?

There are many many many more factors at play.

isolation level. locking behavior differs wildly between isolation levels. Some don't lock at all (read uncommitted, snapshot, rcsi). The default read committed transiently locks rows it reads as long as is necessary. Repeatable read and Serializable hold on to locks and end up locking everything, and many developers deploy serializable without ever realizing they do so.
scan purpose. Scans for read are compatible with each other so they don't block, no matter what they lock. If you have  scans blocking other scans they must be scans for update, which are incompatible with each other. Again, snapshot isolation level (including rcsi) do not block even when doing a scan for update.
lock granularity, based on cardinality estimates. Scans may choose row, page or rowset level granularity. A table scan will likely choose page locks.
index page locks/row locks configuration, which you so inadvertently changed. You should revert the change, since is not based on any measurement and root cause analysis. Guts feeling has no role in investigation.
lock escalation
row stability requirements when off-row LOB data is present
other

I suggets you follow the procedure described in Capturing wait stats for a single operation to capture the wait stats of the scans you observed as blocking. Se if indeed they block on locks held by the table scan operation. IF the scenario is truly as you described (read scan vs. other read operations) then there is no reason for blocking so something else will be at play. You can also give sp_whoisactive  a shot.


Is the double dollar sign ($$) quotation method in SQL standard?

NO  its postgresql specific many programing languages offer a way to write text that avoids having to be escaped

consider

Select $$";"'\e\r\n$$

vs

select '\";\"\'\\e\\r\\n'

Also keep in mind Postgtesql from version 9.1 by default no longer allows passing escape string in a query,  it has to have a leading E

select E'\r\n'

to be escaped now
see standard conforming string


Grouping in relational algebra with more than one grouping attribute

The usual notation is to write separately the grouping attributes from the aggregation functions, writing the attributes on the left of the γ symbol and the aggregation functions on the right, so your query should be something like this:

employeeId, date γ MAX(salePrice)→ largetSale(Sales)

This means:

make a group for each different combination of employeeId and date in the Sales relation, and
for each group produce a tuple with employeeId, date, maximum sale price of the group.


MySQL (on Windows) general log too large to rotate properly?

First of all, I'd like to commend you for using the general log. If this were Linux, log rotation could all be done with shell scripting and without manipulating mysql in any way. In your case, it is Windows.
Weel, I have some good news and bad news for you, in that order.
GOOD NEWS
You can enable the general log to record events in a table rather than a text file.
On Feb 11, 2012, I wrote a post on how to make the general log a MyISAM table, how to index it, how to enable it, and how to rotate it : MySQL general log. Don't worry, this works for Linux and Windows.
The beauty of having the general log as a table is the fact that you can query it for specific text and specific datetime.
BAD NEWS
You cannot take the text file version of the general log and convert. you will have to stop mysql  with net stop mysql. You can copy the text file or delete it.
GIVE IT A TRY !!!
CAVEAT : It can be a headache to work with the general log as a text file in Windows since Windows places a a strict lock of the table so that you cannot truncate it with MySQL running as you could in Linux.


What is the valid string representation of tstzrange[]?

The thing is that here you have to combine two literals: '{}' for the array and '[...,...]' for the range type.

In all these cases, the easiest is just getting back the output from the system itself:

SELECT array[tstzrange('2014-01-01','2015-01-01','[]')];
                            array
─────────────────────────────────────────────────────────────
 {"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}

Now you simply wrap this into quotes, and there you are.

There is, however, a simpler way, which usually works: just double-quote the inner literal.  See:

select '{"[2014-01-01,2015-01-01]"}'::tstzrange[];
                          tstzrange
─────────────────────────────────────────────────────────────
 {"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}

Note that the output is the same as above.


Consolidate multiple rows into single row

Since the target table obviously exists, CREATE TABLE AS is unrelated to the solution. And you certainly don't need a recursive query, either.
For just three columns you might use conditional aggregates:
INSERT INTO reports_con (id_station, date, tmin, tmax, tavg)
SELECT id_station, date
     , min(value) FILTER (WHERE element = 'TMIN') AS tmin
     , min(value) FILTER (WHERE element = 'TMAX') AS tmax
     , min(value) FILTER (WHERE element = 'TAVG') AS tavg
FROM   reports
GROUP  BY id_station, date
ORDER  BY id_station, date;

The aggregate FILTER clause requires Postgres 9.4. Details and alternatives for older versions:

Return counts for multiple ranges in a single SELECT statement

For more columns of for lots of rows (and you need better performance), consider an actual crosstab() query. The special difficulty is that your key consists of two columns, but you need one for crosstab(). Generate a surrogate key with row_number() like in these related answers:

Pull data from multiple tables in a view or function
PostgreSQL Crosstab Query
Crosstab function in Postgres returning a one row output when I expect multiple rows


The simplest way I can get it is:

SELECT ID
    ,1001
    + (ROW_NUMBER() OVER (ORDER BY (SELECT ID)) - 1) / 150
    AS BatchNumber
FROM Table1;


Applying user-defined fields to arbitrary entities

The immediate question is "what do you mean by 'user defined' fields?"  Who defines these?  For purpose of this I am assuming that these are fixed for each table type by a small number of users (administrators) who have special access.

One thing I have done in the past is to have separate UDF tables for each entity type, but instead of separate rows per value, allow stored procedures to alter the columns on those UDF tables, so you can essentially join straight across, 1:1.  This approach I call "semi-EAV."  I think have catalogs like your user_defined_fields table to help the app generate queries.

Of the methods you are discussing, I would suggest using XML if my semi-EAV approach is not sufficient.  The tradeoff here is that semi-EAV allows you to do more rigorous schema validation using the tools you are familiar with in SQL.  The advantage of XML is that you can have a great deal of additional flexibility that this approach doesn't allow.

As a note, with LedgerSMB, we are talking about moving from semi-EAV for custom values to JSON, but we are on PostgreSQL.  XML might have been an option as well.


Correct place to put very large text field for mysql/mariadb

I'd suggest you split this large column off into its own table.  While you're there take out any other columns which are only used once in a blue moon.  (The smaller your row the faster your queries will run.)

I'll take your current table as MyData.  Rename it to, say, MyData_Crucial.  Let's call the new table MyData_Sometimes.  Create this view:

create view MyData
select
    <all the columns>
from MyData_Crucial
inner join MyData_Sometimes
    on <primary key> = <primary key>

Now, assuming your optimizer is at least a little bit clever, you will not need to change your application code at all.


Can filtering be done more efficiently with/without sql joins?

I don't understand your two choices, but I feel sure that a LEFT JOIN is involved.

Ponder something like this:  Step 1:

SELECT uv.video_id
    FROM (
        SELECT uf.friend_id    -- all your friends
            FROM user_friend AS uf
            WHERE uf.user_id = u.id
              AND u.id = 123   -- you
         ) AS f
        LEFT JOIN user_view AS uv  ON uv.user_id = f.friend_id
        WHERE uv.id IS NULL    -- that they did not view

But, beware, the output could be very long.

I don't know if you need DISTINCT after SELECT.

Step 2:  Now to peel off the 30 highest ranked ones:

SELECT v.id
    FROM ( the above query ) AS b
    JOIN video AS v  ON b.video_id = v.id
    ORDER BY rank DESC
    LIMIT 30

Unfortunately, the query must find all the un-viewed videos before it can sort to locate the top 30.


what if mongos server goes down in shard cluster setup

As per MongoDB documentation here mongos for “MongoDB Shard,” is a routing service for MongoDB shard configurations that processes queries from the application layer, and determines the location of this data in the sharded cluster, in order to complete these operations. From the perspective of the application, a mongos instance behaves identically to any other MongoDB instance.

  My question is what happens when mongos server (foreign-connector)
  goes down.

if mongos server will goes down then config servers connection will be lost with mongos server. Then each shard in cluster connection will be lost.

As per MongoDB documentation here Enable sharding for a database to proceed, you must be connected to a mongos associated to the target sharded cluster.

For your further ref here


DBCC SHRINKDATABASE TRUNCATEONLY - 2008R2 vs 2012

TRUNCATEONLY affects both the LOG and the DATA files in 2008. On BOL for SQL Server 2012 the message simply indicates that if you only wish to SHRINK the database file, then you should use DBCC SHRINKFILE which will allow you to shrink either the data or log files.

For 2008, it is clearly indicated that TRUNCATEONLY only affects DATA files.

Lets test. To visualize what happens using TRUNCATEONLY with SHRINKDATABASE, here is a run down of what happened to a database called performance that I have installed locally.

SELECT @@VERSION;
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
   Jun 28 2012 08:36:30
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

USE performance;

I ran DBCC LOGINFO just to take a peak inside the transaction log and found that all of the virtual log files after about 200 were inactive, status = 0. In my performance database, all those inactive VLFs can be truncated and the space can be given back to the OS.

Here is some sample output from LOGINFO.

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
------ -------- ----------- ------ ------ ------ -----------------
2      253952   8192        23     2      64     0
2      253952   262144      24     2      64     0
2      270336   516096      25     2      64     24000000013400005

I then ran DBCC SQLPERF(LOGSPACE) and got the following output

DBCC SQLPERF(LOGSPACE)
/*
Database Name   Log Size (MB)   Log Space Used (%)  Status
Performance          9870,867             3,395626  0
*/

I then ran

DBCC SHRINKDATABASE(PERFORMANCE, truncateonly)

and got the following result

/*
DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
8       1       66464       288         44944       44944
8       2       116584      72          116584      72
*/

Next, I reran

DBCC SQLPERF(LOGSPACE)

and got

/*
Database Name   Log Size (MB)   Log Space Used (%)  Status
Performance     910,8047        37,08699            0
*/

SHRINKDATABASE with TRUNCATEONLY gave back over 9 GB of available space from the transaction log file back to the OS.

I tried the same experiment with another database called performance2

sp_spaceused

/*
Performance2 52.19 MB   22.81 MB
*/

DBCC SHRINKDATABASE(Performance2, truncateonly)

sp_spaceused

/*
database_name   database_size   unallocated space
Performance2 31.13 MB   21.13 MB
*/

Which gave back 20 MB to the OS. Using SQL Server 2008, SHRINKDATABASE TRUNCATEONLY shrinks both data and transaction log files.


How to install MySQL 5.1 on Ubuntu 10.10?

sudo apt-get install mysql-server-5.1 mysql-client-5.1

... from the command prompt should just work.

If not, edit your question with the full error messages you are seeing.

Post-installation steps are summarized in this blog post.


Adding subscription for replication in sql server

This is the article that helped me with replication.  So the database that you are sending from is your publisher and you'll need to go to replication/local publications and create a new publisher with the databases you want to send.

You may or may not use a middleman/distributor but the subscriber is where you want the database(s) to end up and you will need to set that up as well.

https://www.sqlshack.com/sql-server-replication-overview-of-components-and-topography/


Update values in one table from data in another

To answer the first part of the question, I did the following:

Created two tables:

CREATE TABLE master
(
  m_id INTEGER,
  m_value VARCHAR(4)
);

CREATE TABLE scheduled
(
  key INTEGER,
  s_id  INTEGER,
  s_value VARCHAR(4),
  due TIMESTAMP
);

Added some records:

INSERT INTO master VALUES (1, 'Buy');
INSERT INTO master VALUES (2, 'Buy');

INSERT INTO scheduled VALUES (1, 1, 'Sell', '2018-01-01 12:00:00');
INSERT INTO scheduled VALUES (2, 2, 'Sell', '2018-01-01 12:00:01');
INSERT INTO scheduled VALUES (3, 1, 'Buy',  '2019-01-01 12:00:00');

Then I ran the following query:

UPDATE master
SET m_value =
(
  SELECT s_value
  FROM scheduled s
  WHERE s_id = m_id
  AND due <= CURRENT_DATE
);

The result of SELECT * FROM master after the UPDATE:

m_id   m_value
   1      Sell
   2      Sell

You can find all this on the dbfiddle here. Next time you ask a question, you might find it beneficial to set up a  dbfiddle for those who are trying to solve your issue - help us to help you!

BTW, SQLite (unlike other systems) doesn't support UPDATEs with JOINs. I'm not sure what the standard says about using these, but here is your example done using a join.

UPDATE master
SET m_value = s.s_value
FROM  master m
JOIN scheduled s ON m.m_id = s.s_id
WHERE s.due <= NOW();

To answer the second part of the question, do all the work (two separate statements) in one TRANSACTION (documentation here, example here). Unfortunately, dbfiddle doesn't appear to support this.  BUT, we're in luck, this db-fiddle (note hyphen!) does support TRANSACTIONs - take a look at the complete answer to your question here. If my solution doesn't help you, please let me know.


As is suggested in the comments, you should change the data type to, for example, char(3). If that is not possible, you can transform the value of the column like:

SELECT SUBSTR(DIGITS(CURRENCY_CODE),1,3) AS CURRENCY_CODE
FROM ...

or perhaps:

SELECT DIGITS(CAST(CURRENCY_CODE AS DECIMAL(3,0))) AS CURRENCY_CODE
FROM ...

which would be the same as:

SELECT DIGITS(DECIMAL(CURRENCY_CODE, 3,0)) AS CURRENCY_CODE
FROM ...

when you retrieve it


Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

  WHILE (@@FETCH_STATUS = 0) AND
               ( @About80MoreBooleanExpressionsHere)
   BEGIN   /* 1 */
       -- about 700 lines of logic, math and if-parameter-this-then-that
       -- stuff omitted
        INSERT InnerProcedureCallWithinLoop__TraceTable
                                          VALUES (@from_locn, @About53PARAMSOMITTED
        EXEC  @ConsiderItem =
              InnerProcedureCallWithinLoop
                    @from_locn,
                          @About53PARAMSOMITTED,
                                                      ...

If I know that it is only ever called from one location.  Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions).  If that is your problem, you need to look at CLR or email methods to externalise the captured output.


Does a login need to be created on MSSQL Always On replica before being granted roles on the primary?

It doesn't matter where the login is created first.  The only important thing is that the SIDs match.  All permissions, role memberships, etc. that are assigned to the associated users on databases in the availability group on the primary will be effective on the secondaries for the login with the same SID.


Creating an index in a temporary tablespace

Tables and indexes are created in users tablespace, which is USERS by default. A temporary tablespace, TEMP by default, is used by oracle for sorting large amount of data on disk. Temporary tablespace is typically used for processing joins, order by or creating indexes.

Since the default tablespace TEMP is not large enough for creating a large index, your example creates a large temporary tablespace ts_tmp for the index creation purpose only and drops it afterwards.

When creating the index with CREATE UNIQUE INDEX ... TABLESPACE ... command, don't use ts_tmp  in the TABLESPACE clause. This will give you Attempt to create PERMANENT object in a TEMPORARY tablespace error.

Either don't use the TABLESPACE clause, then the defualt users tablespace will be used. Or give a tablespace name that was created with a CREATE TABLESPACE command.


Drop All Columns in a Table that are not Keys

This T-SQL will generate a series of ALTER TABLE statements that you can copy-and-paste into a query window in SSMS to remove columns that are neither primary keys nor foreign keys.

;WITH ForeignKeys AS (
SELECT t.object_id
    , c.column_id
FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.parent_object_id = fkc.parent_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id
         AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
), PrimaryKeys AS (
SELECT t.object_id
    , c.column_id
FROM sys.key_constraints kc
    INNER JOIN sys.indexes i ON kc.parent_object_id = i.object_id
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
         AND i.index_id = ic.index_id
    INNER JOIN sys.tables t ON i.object_id = t.object_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id
         AND ic.index_column_id = c.column_id
)
SELECT 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
    DROP COLUMN ' + QUOTENAME(c.name) + ';'
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE NOT EXISTS (
    SELECT 1
    FROM ForeignKeys fk
    WHERE fk.object_id = t.object_id
        AND fk.column_id = c.column_id
    )
    AND NOT EXISTS (
        SELECT 1
        FROM PrimaryKeys pk
        WHERE pk.object_id = t.object_id
            AND pk.column_id = c.column_id
        )

It will correctly handle tables in various schemas, as well.

You may also have to first remove any default constraints:

SELECT N'alter table ' + QUOTENAME(t.name) +
    ' drop constraint [' + OBJECT_NAME(so.object_id) + N']'
FROM sys.objects so
    INNER JOIN sys.tables t
        ON t.object_id = so.parent_object_id
WHERE OBJECT_NAME(so.OBJECT_ID) like 'DF%'


Filtering One column into Different Columns in one statement

This is one of the ways

SELECT

CASE WHEN `amount`='type 1' then `amount` end as `amount1`,

CASE WHEN `amount`='type 2' then `amount` end as `amount2`,

CASE WHEN `amount`='type 3' then `amount` end as `amount3`

FROM db.tablename;

Ex:

SELECT

CASE WHEN amount=1000 then amount end as amount1,

CASE WHEN amount=2000 then amount end as amount2,

CASE WHEN amount=3000 then amount end as amount3

FROM db.test;


Can I disable binlogs to save space temporarily

What you need to do is set sql_log_bin to 0 as you start pt-online-schema-change using set-vars
pt-online-schema-change --set-vars sql_log_bin=0

Your binary logs will never know the difference.
Please make sure you have enough space for the temp table (_tablename_new).
UPDATE 2020-12-10 14:45 EST
If you are not doing backups on this server, then you could run
PURGE BINARY LOGS BEFORE CURDATE() - INTERVAL 5 DAY;

to keep the last 5 days worth of binary logs or if you want all the binary logs gone, then run
RESET MASTER;

WARNING !!!
Before doing that, find out if the server is running replication as a Master.
UPDATE 2020-12-10 16:05 EST
Since you are running pt-online-schema-change, you should run it like this
pt-online-schema-change --set-vars sql_log_bin=0 --alter "ENGINE=InnoDB"

This will just create a temp table defragmented. It just so happens that --analyze-before-swap is the default value. That means the temp table has ANALYZE TABLE executed against it before swapping old and new tables. This has the exact same effect as OPTIMIZE TABLE on an InnoDB table.
As mentioned in nbk's comment, you should make sure you have backups. You would also do yourself a world of good by setting up replication and doing the backups from the slave.


Oracle licensing is a complex topic. You can find information on the Oracle pricing page.

You can find there links to a paper called "Software Investment Guide" that has a lot of licensing samples.
Also there can be found some papers about special licensing topics and a price list.

There is also a FAQ concerning Oracle licensing (not an Oracle site) that may be interesting.

DISCLAIMER: All information is offered in good faith and in the hope that it may be of use, but is not guaranteed to be correct, up to date or suitable for any particular purpose.

Oracle has several different types of "editions" of its database prodcut:
Enterprise Editions, Standard Edition, Standarad Edition One. Enterprise Edition has additional options must be licensed if enabled.

Oracle has two types of licenses: so called "Named User Plus" licenses and "Processor" licenses. You can either license the machine where Oracle is used. Then you have to use Processor Licenses.  Or you can license the users/devices that are able use the database. Then you have to use "Named User Plus" Licenses.

If you use Enterprise Edition the number of Processor Licenses needed is the number of cores multiplied by a factor that depends of the processor type. I you use Standard Edition (Standard Edition One) the number of needed Processor Licenses equals the number of processors in the machine. You cannot use Standard Edtition (Standard Edition One) on all machines: machines that have or can have more than 4 processors cannot be licensed with Standard Edition . For Standard Edition One the limit is 2 processors.

If you use Processor Licenses you always have to license whole machines except for some special situations (look at the "partitioning" document in the "Speciality Topics" section of the Oracle pricing page) where it is possible only to license a part of a machine. If you have a cluster , failover, backup or test environment you havee  to check how to  license this environment additional machines.

If you use "Named User Plus" licenses than bear in mind that a user is not a database user but a person or device that uses the database. So for example if you have an application that is accessible by all of your employees and this application stores its data in an oracle database then all of your amployees need "Named User Plus" license. Even if the application uses only one database user.

There is also a minimum number of "Named User Plus" licenses that is needed depending on the number of Processors/Cores that are used for Oracle databases

A machine with 2 intel processors and standard edition  needs 2 processor licenses if one uses processor licensing.

If enterprise edition is used  it depends on the number of cores. The core factor is 0.5 for Intel processors. For  2 Hexacore processors  2*6*0.5=6 "processor licenses" are needed. The same number of licenses is needed for every licensable option, too.


Functions causing Serial plan.What to use instead?

A scalar UDF only forces a serial plan for the query that calls it. You can have a scalar UDF call in a stored procedure that also has queries eligible for parallelism as long as the queries don't call the function. Your function in this example is a constant so you can just store it in a local variable. I had to make a guess about the data type but something like the following should meet your needs:

CREATE PROCEDURE [dbo].[DemoProc]
    @Arg1 VARCHAR(20),
    @Arg2 varchar(9) = NULL
AS
DECLARE @Plant_Id_Filter VARCHAR(100);

SET @Plant_Id_Filter = CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END;

SELECT
    r.Plant_Id AS [Plant_Id],
    @LNumber AS [Incident Number],
    COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
    CASE l.[UserName]
        WHEN NULL THEN 'OPEN'
        WHEN '' THEN 'UNLOCKED'
        ELSE l.UserName
        END AS UserName,
    l.UserId,
    l.Lock LockCode,
    l.LockTime
FROM
    dbo.tblDetailPages idp WITH (NOLOCK)
        INNER JOIN tblForm f WITH (NOLOCK)
            ON idp.tblFormId = f.Id
        INNER JOIN tblReport r WITH (NOLOCK)
            ON f.tblReportId = r.Id
        LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
            ON idp.Id = idps.PageId
        LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK)
            ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
            AND l.Plant_Id = r.Plant_Id
WHERE
    SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
    r.Plant_Id LIKE @Plant_Id_Filter
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO


MySQL doesn't support Window Functions such as SUM(...) OVER(...) but it can be calculated using a variable, a self join or a subquery.

Queries using the question's sample

Variable

SELECT created, value
    , @growth := @growth + value as growth
FROM data
  , (SELECT @growth := 0 as growth) as v
ORDER BY created

Output

| created                   | value | growth |
|---------------------------|-------|--------|
| January, 01 2016 00:00:00 |     1 |      1 |
| January, 02 2016 00:00:00 |     0 |      1 |
| January, 03 2016 00:00:00 |     4 |      5 |
| January, 04 2016 00:00:00 |     1 |      6 |
| January, 05 2016 00:00:00 |     2 |      8 |

Self JOIN

This can also be done with a self JOIN:

SELECT d1.created, d1.value
    , SUM(d2.value) as growth
FROM data d1
INNER JOIN data d2
    ON d1.created >= d2.created
GROUP BY d1.created, d1.value
ORDER BY d1.created;

Subquery

Or with a subquery:

SELECT d1.created, d1.value
    , (
      SELECT SUM(value)
      FROM data
      WHERE created <= d1.created
    ) as growth
FROM mydata d1
ORDER BY d1.created;

This SQL Fiddle contains both queries (variable, self join and subquery)

Query with growth for several IDs

Variables

If you want to calculate the growth for several IDs, an extra @id variable and a CASE statement can be added:

SELECT created, value
    , CASE WHEN @id <> d.id THEN @growth := value
        ELSE @growth := @growth + value
      END as growth
    , @id := d.id as id
FROM data d
  , (SELECT @growth := 0 as growth, @id := 0 as id) as v
ORDER BY d.id, d.created;

Self JOIN

SELECT d1.created, d1.value
    , SUM(d2.value) as growth
    , d1.id
FROM data d1
INNER JOIN data d2
    ON d1.created >= d2.created AND d1.id = d2.id
GROUP BY d1.created, d1.value, d1.id
ORDER BY d1.created;

Subquery

SELECT d1.id, d1.created, d1.value
    , (
      SELECT SUM(value)
      FROM data
      WHERE created <= d1.created AND id = d1.id
    ) as growth
FROM data d1
ORDER BY d1.id, d1.created;

This SQL Fiddle contains both queries for growth by IDs (variables, self join or subquery)


How to exclude tempdb from sp_MSforeachdb?

A possibility would be to check for DB_Name = 'Tempdb', and if it's tempdb just end the script.

This is a quick test I did, and it seems to works :

EXEC sp_msforeachdb 'USE [?] IF DB_Name() = ''Tempdb''BEGIN RETURN END SELECT db_Name()'

That will run the code on each db, but if it's tempdb it wont execute a thing, cause it'll return.

It's also known that using sp_msforeachdb is not always the best thing. Aaron has blogged about using a self made replacement procedure you can use, it might be worth your time to go check it out! making-a-more-reliable-and-flexible-spmsforeachdb


Can I get Oracle to favor particular indexes?

The cost of an index depends on its clustering factor.

Clustering factor measures how well aligned are the rows in the table - in respect of the columns of the index in question. It is calculated as: inspect all the rows from table in order (order is defined by the index columns). Every time a row is found to be in a different (not the currently inspected) block, increase the clustering factor by 1. If consecutive values in a table are near each other in the same block, the clustering factor of the index will be low. If consecutive values are scattered across the table, the clustering factor will be high.

Simply put, the lower the clustering factor is, the cheaper the index becomes.

Despite the myth, the clustering factor can not be decreased by rebuilding the index, because it depends on the table data distribution. However, you can set the clustering factor manually by:

begin
  dbms_stats.set_index_stats
  (
   ownname       => 'USER1',
   indname       => 'INDEX1',
   clstfct       => 1234,
   no_invalidate => false
  );
end;
/

Where 1234 is the desired clustering factor. The lowest valid value of the clustering factor is the number of the table blocks. The highest valid value of the clustering factor is the number of the table rows. But this does not mean you can not set it lower or higher.

This setting will be lost on the next index statistics collection.

You can use the above to change other statistics as well that have effect on the cost: blevel (indlevel) or leaf_blocks (numlblks).


How do I get data from multiple tables if the agency_code doesn't match

You can achieve that by using this select:

select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form  ef, HumanResources hr
where ef.employee_number = hr.employee_number
and ef.agency_code <> hr.agency_code;

Here you select all the columns of the Employment_Form table, fetching only those rows with matching employee_number and wrong agency_code.

If that full_name is the same on both tables, you can either use it, instead of employee_number , like this

select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form  ef, HumanResources hr
where ef.full_name = hr.full_name
and ef.agency_code <> hr.agency_code;

The output would be the same.

Hope this helps.


How do I SELECT from a table with a JOIN with multiple matching values?

Consider that without the group-by or the where clause, your dataset might look like this:

(row) gc.id  gc.name  keyword.keyword
1     1      Fred     mini
2     1      Fred     tall
3     1      Fred     2011
4     2      Bob      mini
5     2      Bob      2012
6     3      Pat      2012
7     4      Kim      2011
8     4      Kim      mini
9     4      Kim      2012

The first thing that happens in your query is that the where clause evaluates each row individually to see if it matches all of the conditions.  Only after this step does it apply the grouping.  (Technically there are a few more steps involved; see Logical Processing Order of the SELECT Statement for more gory details.)
  With that in mind, though, your initial query first finds every row where the keyword is "mini", which in the sample dataset above is rows 1, 4 and 8 (card IDs 1, 2, and 4), then groups the found rows by their card ID. Your modified query is asking for every individual row where the keyword is both "mini" and "2012", in the same row.  There are rows with "mini" and there are rows with "2012", but each row has only one value or the other, not both at the same time.  In fact it is simply not possible for a single field to hold two different values at the same time.

What you need to do is to select all rows where the keyword is any one of your values, then group them by ID, and then select the IDs where the number of rows found for that ID is 2:

Select gc.id, min(gc.name)
from giftcard as GC
join keyword as KW on gc.id = kw.gc_id
where kw.keyword in ('mini','2012')
group by gc.id
having count(gc.id) = 2
order by gc.id desc

From our dataset above, this will basically go through and select rows 1, 4, 5, 6, 8, and 9; reduce that to "card 1: 1 match, card 2: 2 matches, card 3: 1 match, card 4: 2 matches", and then select "card 2, card 4" as having the required 2 matches, and produce the output

gc.id  gc.name
4      Kim
2      Bob

You could then extend this to say "any card having at least X out of these Y keywords" by adding keywords to the in clause (so it would be like  in  ('x','b','z','r','p')), and adjusting the having clause to something like having count(gc.id) >= 3.


What is the difference between CREATE INDEX and ALTER TABLE ADD INDEX?

The  CREATE INDEX syntax requires an index name, whereas the ALTER TABLE ADD INDEX syntax doesn't.

This question as already been asked and answered and should be marked as a duplicate.


What is the best order of columns on a compound index?

In MySQL, the order of the columns in an index is very important. Searching over an INT is faster than search on a string(VARCHAR). So an index on (organisation_id, name) would be better than that on (name, organisation_id).

If a WHERE clause satisfies the prefix an INDEX then that INDEX is used. So no need for a separate index on organisation_id. The existing index will take care of it.


Deadlock occurring on database, SQL Server 2008R2

Enabling snapshot isolation on a production server without testing the application first is rather a high risk strategy, but if you seem to have got away with it, congratulations.

The side effects of snapshot isolation tend to be to do with tempdb rather than the transaction log, see for instance http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx.

The best thing to do is to look at the code that is accessing those two tables, and see if you can correct the problem there.


SQL Server becomes slower over time until we have to restart it

I would suggest collecting performance metrics on this server, so you can eliminate the guesswork from troubleshooting these types of problems. See this article for a more complete guide if you don't know where to start with this.

In particular, I would check the performance counters Memory\Available MBytes and Paging File(_Total)\% Usage because you said the issues only start occurring when the buffer pool is full. The numbers you get back from these counters may indicate that the max server memory setting needs to be adjusted (either up or down) for the amount of physical memory allocated to the server. As I mentioned here, I don't recommend basing the max memory setting on the amount of physical memory except as an educated guess for a starting point. Always measure the result, and adjust from there.

If the amount of free memory is too low (< 500), or the page file usage is over zero, this may indicate that the SQL Server instance is overcommitted: on SQL Server 2008 R2, the max server memory setting only controls buffer pool size, and not other memory usage such as the plan cache. SQL Server also does not care about other applications you may have running on the system. This extra memory usage can put memory pressure on Windows -- or the other applications -- possibly leading to disk swapping. This is something you want to avoid at all cost, particularly if the page file exists on a volume backed by just a simple RAID 1 mirror. My feeling is that this is the issue, and backing off the max server memory setting should fix the problem.

If the amount of free memory is high (> 1000) and the page file usage is zero, you can probably bump up the max server memory slightly (in 256 MB increments) to maximize the memory usage of the server. This most likely won't solve the problem, however, and you'll need to look elsewhere, probably at the physical disk counters and the buffer pool page life expectancy. If queries are thrashing the buffer pool, there's nothing you can do except improve disk performance, increase the amount of physical memory available to the server so all the data pages can fit in memory at once, or modify the database to not take up as much physical space (maybe by using row or page compression, or by rebuilding indexes with a higher FILLFACTOR).

I've published an article on this topic here that goes into more depth about this issue and how to solve it.


MySQL 5.7 Upgradation - Binary log basename

After, discussion with my colleagues, I found that, the log_bin_basename is read only variable and it took the value from the log-bin definition. So I added another variable log-bin, without modifying the default log_bin variable. Now the binary log enabled and basename pointed to the separate directory.

# BINARY LOGGING
#log_bin # This is default
log-bin                       =       /logs/binlog/mysql-bin
log_bin_basename              =       /logs/binlog/mysql-bin
expire_logs_days              =       5
sync-binlog                   =       1


Improve performance on write-only table?

I just read about blackhole engine in mysql today. This could fit your requirement I believe, if you have a slave set-up. You can dump all the data into your master but will get saved only in slave. The load on your master server should be minimal.


Can I use the same filepath for recurring backups?

As mentioned in the comments, backing up to an existing file name will, by default, append the latest backup to that file. For full and differential backups, at least, you don't want to do that.

For your purposes:

Once you have a new full backup, you don't need previous full, differential, or transaction log backups.
Once you have a new differential backup, you don't need previous differential backups or transaction log backups. Each differential backup contains all changes since the last full backup.
Once you have a new transaction log backup, you still need previous transaction log backups. Transaction log backups record new log entries since the last transaction log was taken.

Note that, for maximum safety, you should keep your previous full and differential backups until a new one is finished. If you overwrite your previous full backup with the new one, then as soon as SQL Server starts writing your full backup until it finishes you have no backup. With the full backup they are based on gone, differential and transaction log backups are useless.

If you really want to take that risk: To write to the same backup file, overwriting any existing backups in it, use the following options:

WITH INIT, SKIP, FORMAT

My recommendation: Just as noted by sp_BlitzErik in the comments, use Ola Hallengren's free maintenance scripts, as they will do all the "heavy lifting" for you.

Just to cover the option, depending on the edition of SQL Server you're using, keep in mind that you probably have the option to set up SQL Server maintenance plans to do these backups. Those include a task that will remove files older that however much time you specify. However, I'm assuming that you haven't mentioned those because you either don't have access to use those, or don't want to use them.

If both of those options are out, but you can write a script (in the language of your choice) to delete files more than 2 days old, and can hold up to two days' worth of your backup files on disk:

Set up your jobs as planned, with one change: append the type of backup and the current date and time to the file names. For example:

BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB_FULL_'
          + REPLACE(REPLACE(REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-',''),':',''),' ','_')
          + '.bak'
WITH CHECKSUM;

This would create a full backup file named F:\MSSQLSERVER\BU\MyDB_FULL_20170928_191216.bak in your chosen directory (if run today, at 7:12:16 PM).

I would use MyDB_DIFF_ as the prefix for differential backups, and MyDB_TRAN_ for transaction logs. Note that full and differential backups should be have the extension .bak, while transaction log backups would have the extension .trn.
Then, set up a "clean-up" job to run right before you run your full backup. This will run your script that deletes any files in the F:\MSSQLSERVER\BU\ folder that are more than 48 hours old. This should ensure that you have at least one full backup, and the differential and t-log backups that depend on it, even if the script was accidentally run at noon instead of midnight.

By running it right before you run your full backup, you also ensure that you have the maximum available space for that full backup.

You can always choose to be fancier with the "clean-up" script; since each of the three file types starts with the same prefix, you could delete differential and transaction log backups more than (say) 3 hours old, and still have a good cushion.

Unless you already have a script that would do the deletions for you around to use, I again highly recommend you use Ola Hallengren's scripts. Keep in mind that, unlike whatever you could whip up on your own, they've already been tested by a lot of people the world over.