How is ‘tempdb’ getting used when Always On Failover happens? How is tempdb utilized as Failover occurs?

Let's say there are two servers, server 1 is primary and server 2 is secondary. Suddenly, Failover happens, so, how does tempdb behave during this situation?

Tempdb doesn't move when an AG failover occurs. Each replica is its own standalone instance. Each has its own tempdb. Queries will break during the failover. Once the application reconnects after the failover is complete and crash recovery is also complete, the queries can then run on the other instance where it has its own tempdb.

A Failover Cluster Instance fails over at the instance level, so tempdb is affected. But not for Availability Groups.

There is a caveat here... if the local disk with the tempdb fails, SQL will not initiate a failover.
My colleague Twan and I have written an article detailing that on https://www.sqlservercentral.com/articles/do-not-place-tempdb-on-a-local-disk-in-a-sql-failover-cluster
TLDR: you need to check in SQL for the loss of the local disk, and initiate the failover yourself.


Use sp_Blitz to gather information on all servers in a centralized server/database I am using sp_Blitz, and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.

Do I need to create linked servers on the central instance to connect to the other instances, or is there some other way to input the server names into the sp_Blitz stored procedure?

Brent Ozar folks already have got you 50% - Inserting sp_Blitz® Output Into a Table

My steps would be :

Log the output to a local dbastats or some dba database.
Have a list of all sql servers in your central server.
use sqlcmd and opendatasource to query remote servers and dump all the info into centralized server .. make sure you include something like collection_datetime and server_name. You can use powershell (Write-DbaDataTable) as well.
schedule above logic using sqlagent job as per your needs.
Make sure you have proper purge policy defined locally per server. I would add custom column like notified = 0 (as default) and update to 1 when you dump the data into central database. The collection_datetime and server_name will help you find out what has changed or do aggregation per server instance.

Or, just convert sp_blitz from an SP to a QUERY (compatible with your sql versions), and use SSMS to execute against all Registered Servers at once. simples … then save the results. ok ok qed.


Loading subscription slow in SSRS I have a SQL Server 2014 test box with SSRS installed. The site loads ok and reports are quick. When I try to go to Subscribe the page takes over a minute to load. From there if I pick a schedule it also takes minutes to load. This is a fresh box with nothing else installed. There's not antivirus installed.

The box has 2 x xeon @ 2.66 and 6GB of memory. I'm the only user on this box as it is just for testing.

This may be too late but try looking at the File settings for ReportServer and ReportServerTempDB, just like you normally would through SSMS on the instance. They may be defaulted to a few MB with a 1MB autogrowth.


How to drop all connections to a specific database without stopping the server? I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases.

How can I do that?

The query like this should help (assuming the database is named 'db'):

select pg_terminate_backend(pid) from pg_stat_activity where datname='db';

pid used to be called procpid, so if you're using a version of postgres older than 9.2 you could try the following:

select pg_terminate_backend(procpid) from pg_stat_activity where datname='db';

However you have to be a superuser to disconnect other users.

It might also be useful to REVOKE CONNECT ON DATABASE FROM PUBLIC or something similar, and then GRANT it afterward.

This can be used to "free" a database from client connections, so that you for example can rename it:

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='current_db';
ALTER DATABASE current_db RENAME TO old_db;
ALTER DATABASE new_db RENAME TO current_db;

Be aware that this might cause problematic behaviour to your client apps. Data actualy should not be currupted due to using transactions.


Referencing a single table from all tables in the database At current, I have around 130 tables in my database, anticipating a growth of circa 200 tables in total.

Now I have several tables (well, most of them, actually) that contain properties like CreatedBy / ModifiedBy / DeletedBy, which take an nvarchar(MAX) as their data type and contain the name of the user.

This makes versioning a lot harder, because the name of the user might change during the lifetime of the application.

Now I've thought about referencing each and every *By-Column via a Foreign Key to the users table. However this would result in 600 foreign keys on a single table (expecting the 200 tables with 3 foreign keys per table, if not more).

Are there any drawbacks? What's the best practice?

As a side note (not relevant for the dba aspect of my question, I presume): I'm using Entity Framework in C#, only referencing a single part of it in my code (because I don't want and don't need 600 collections in my User class).

You may want to consider a Type 2 slowly changing dimension table and normalize your user name data. This will give you a better picture of whom is changing the data over time and allow you to use a smaller more efficient surrogate key for your *By columns.

I wouldn't use an actual FK constraint on every table. If you are controlling what happens to that table in the centre of your design, you could prevent deletes and updates to the PK easily enough. Otherwise, every time you insert a record in any table you need to confirm the row is there in the centre table. But using the key values - by all means. :)


ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block" I faced an error while logging to database with regular user (non SYS/DBA):

C:\Users\user>sqlplus tester@orcl
ORA-01033 ORACLE initialization or shutdown in progress

However I can connect via sysman@orcl as sysdba user. Before I start with solving the issue, below are the errors that can tell what state my orcl database is in:

SQL> select * from dba_data_files;
ORA-01219: database not open: queries allowed on fixed tables/views only

So I use recover database and alter database open commands and they point at the same logfile and block:

SQL> recover database;
ORA-00600: internal error code, arguments: [3020], [3], [192], [12583104], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 192, file offset is 1572864 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'

SQL> alter database open;
ORA-01172: recovery of thread 1 stuck at block 192 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed

I did also that:

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

After restarting oracle.exe service and diagnosing the database, the "redo" error has a bit changed (block 705):

ORA-10567: Redo is inconsistent with data block (file# 3, block# 705, file offset is 5775360 bytes)

My log files:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                IS_
---------- ------- ------- ------------------------------------- ---
         3         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO03.LOG     NO
         2         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO02.LOG     NO
         1         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO01.LOG     NO

I'm in a point where I cannot use my database at full even by a sysman user. Please help, advise.

EDIT:
After following this guide which ideally addresses my situation, I get different error and cannot follow it anymore:

SQL> alter system switch logfile;
ORA-01109: database not open

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Question aside: Who can tell if this db is open or not? :-)

Since the problem is in your undo tablespace. You can drop it and create a new one. Steps in this article:

http://www.my-whiteboard.com/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files/


The SQL Server (MSSQLSERVER) service terminated unexpectedly. Event ID: 7034 Unable to find the cause why the SQL Server Service was Terminated Unexpectedly? (Event ID: 7034)

I looked into the SQL Server Error Logs but could find the reason about why the SQL Server service was terminated.

So check the windows event Viewer and I found the following error msg:

Event Message : The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).

Error Event Id : 7034

Since last night my SQL server service is terminating with the this error.

This service terminates and restarts automatically and sometimes doesn't restart we have to manually start it again.

Version Details:
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Standard Edition (64-bit)

OS: Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hyper-visor)

Error Event Id : 7034

Service Control Manager (SCM) stops services and driver services. It also reports when services terminate unexpectedly or fail to restart after it takes corrective action.

How to Resolve

Change the recovery actions

To resolve this issue, change the recovery actions that the Service Control Manager (SCM) will take when a service fails.
To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.

To change the recovery actions for a service:

Open the Services snap-in by clicking the Start button, Control
Panel, and Administrative Tools, then double-clicking Services.
Note: For Windows Vista, use the Classic View display option in
Control Panel to see the Administration Tools.
In the details pane of the Services snap-in, right-click on the name
    of the service you want to stop and select Properties.
Click the Recovery tab and specify the recovery actions for the
    service (for example, restart the service or the computer, take no
    action, or run a program).

For your further ref Here and Here


How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1? I have two databases - Database1 and Database2.

Both databases contain a table that has similar structure, exemplified as follows:

===========================================================================================
| ID | Name | PhoneNoFormat | DialingCountryCode | InternationalDialingCode | InternetTLD |
===========================================================================================
|    |      |               |                    |                          |             |
===========================================================================================

However, due to some reason, (a) one of the tables in one of the databases has data that is not exactly the same as (b) that contained in the other table in the another database.

So, how can I compare Database1.Table1 against Database2.Table1?

I tried using the following query, but nothing happened so was wondering if I have to rewrite it:

SELECT MIN(TableName) as TableName,
       ID,
       Name,
       PhoneNoFormat,
       DialingCountryCode,
       InternationalDialingCode,
       InternetTLD

FROM

(

  SELECT  'Table A' as TableName,
          A.ID,
          A.Name,
          A.PhoneNoFormat,
          A.DialingCountryCode,
          A.InternationalDialingCode,
          A.InternetTLD

  FROM [D:\DATABASE1.MDF].[dbo].[Table1] AS A

  UNION ALL

  SELECT 'Table B' as TableName,
         B.ID, B.Name,
         B.PhoneNoFormat,
         B.DialingCountryCode,
         B.InternationalDialingCode,
         B.InternetTLD

  FROM [D:\DATABASE2.MDF].[dbo].[Table1] AS B

) tmp

GROUP BY ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD

HAVING COUNT(*) = 1

ORDER BY ID

The name of your database can't be [D:\DATABASE1.MDF], that may be the path to the physical data file. So the complete table name (3 part name as in db.schema.table) is [DATABASE1].[dbo].[Table1].

If it's a one time task, use the Red Gate SQL Data Compare. It's a great tool and it offers a trial version.

If you have Visual Studio Professional you should also have Data Comparison Tool inside it. It's another great tool to compare data and schema.

If you don't have access to any other tools to compare the data in two tables, I would use tablediff.exe, which is included in the SQL Server installation. You'll find it in the SQL Server program path. Details here, on MSDN.

If you want to use SQL, than you can use the function binary_checksum (or its relative, checksum) to generate a hash and compare over two different versions of the same row, in case you can compare row by row. You can also use the functions for each column, not only for the complete row (off course, going row by row, based on a key). See an example here.

If you are looking for something more visual, one trick is to use any compassion tool (in my case Devart Code Compare) and copy/paste the content (select *) of every table:


1062 Duplicate entry but there are no duplicates? I keep getting this error:

  failed to INSERT: [1062] Duplicate entry 'Upping Your Type
  Game-http://jessicahische.is/talkingtype' for key 'PRIMARY'.

The problem is, is that there are no duplicate entries. Which is why I'm confused when receiving that error.

Is there any way to check using InnoDB?

I have tried exporting the table and reading the SQL code and ran a search for any duplicates but there wasn't any.

It isn't saying that there is a duplicate entry in the table already, it is saying that there is already one entry in there with that value for the primary key and it is refusing to insert a second for that reason.

I suspect if you run:

SELECT *
FROM   <table>
WHERE  <keyfield> = 'Upping Your Type Game-http://jessicahische.is/talkingtype'

or if that key is a composite

SELECT *
FROM   <table>
WHERE  <keyfield1> = 'Upping Your Type Game'
AND    <keyfield2> = 'http://jessicahische.is/talkingtype'

You will find one matching row, and that your code at the point of the error is trying to insert a second.

Dealing with duplicates on insert:

If you try INSERT a duplicate values for a primary key (or a unique index) you will always get that error. There are a couple of ways around it: check before you insert and either do an update (if something might have changed) or just don't do anything.

There is also the mysql specific ON DUPLICATE KEY UPDATE option (see https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) if you are happy to sacrifice compatibility with other RDBMSs.

As of version 9.5 Postgres supports a similar feature with slightly different syntax (ON CONFLICT DO UPDATE/NOTHING - see https://wiki.postgresql.org/wiki/UPSERT).

MS SQL Server, Oracle and some other systems support MERGE statements as defined in the SQL:2003 standard (see https://en.wikipedia.org/wiki/Merge_(SQL)) which is intended to achieve the same functionality and more. Be careful with any of these options if cross database compatibility is (or is likely to be in future) one of your goals.

I have such error if I have system shutdown or network problem. You really don't have a duplicate in your db. That is mysql db error...all you need to do is that, if you do your insertion and it is not true, just alter one of the columns of your table you want to insert into either from varchar to text or bigint and then redo the insertion. That will solve the problem.

If(!$insert){
$alter=Mysql_query("alter table `table_name`
                    change `table_name` `table_name` bigint(255) not null");
If($alter){
//you then redo your insertion.
}
}


What are the challenges of administrating an International stock trading database? I am a student, and have a question for an assignment:

What problems and challenges do database administrators face when designing systems to trade international stocks in real time and 24 hours a day, 6 days a week? Are cloud based database systems as a service a viable option?

Because it's a system, you will have a LOT of interacting subsystems, including power, environmental controls, communications, hardware, and then on top of that the data management and everything else.

There are facets which cut across all these layers, like security, redundancy, business continuity, etc.

Just from the database side, you would need to consider the transactional load, the architecture for balancing the load, the storage requirements, the response time requirements.

Everything depends upon the requirements - of which there will be many from regulatory bodies, clients, users, business partners.

As to whether cloud-based databases, I don't think any of those can be viable if your organization has to be responsible for the system.

Any time you outsource anything, you limit your ability to be responsive to requirements.  Some things you delegate are easier to mitigate than others.  For instance, you decide to use a software library as a component in your system - you can always modify the library if you have the source, or rewrite it yourself or swap it out.  Or picking a RDBMS like Oracle or SQL Server where you can get a vendor to do a proof of concept and have a high level of support because the sale is large.

That's not to say by relaxing your requirements, you couldn't use Google Apps BigTable or Amazon or Azure or whatever, but the simple fact is that even with a contract, you have a limited amount of control over another organization's system and you are relying on them.

All systems are built by balancing tradeoffs.

None. 24/6 is quite good - thee are systems where mainteannce is a real problem.

The main problem can be - and please do not edit out the blunt langauge - if some morong stupid architects have NOT designed the system for 24/6. I.e. if it does not work with replication, or needs exsessive maintenance (on the free day) so that the admin has no chance to do system level stuff there.

If it is designed for using all features of the sql server for uptime, 24/6 is a quite good and easy to work with SLA.

You also may have a small maintenance window every day - you did not say that, but I know even forex sleeps an hour per day simply because no major location is having busienss hours during this time. And forex is unregulated. It may thus be you can do some small stuff daily.

That said, a good design would likely partition the database by location (or a group code, so you can keep all european in one server, all american in one) and allow sharding - if US markets are off, the US responsible servers would not update. As "contintens" mostly share timezone (yes, I know the america cover more - but not excessively so) that gives you maintenance windows for every continent.

Cloud based likely will not work at all because cloud based is small stuff only. Try finding someone offering you a cloud storage for a medium size database and you be surprised. Hint: medium size likely is 100gb+. It also depends what "stock trading" does. Do you test strategies? Large scale? I currently do only futures trading - 22 symbols - and our simulations generate around 50gb-75gb data per day for analysis AFTER compression. 200 before. I utilize partitioning in staging (sql server) just to be able to delete bad results fast during regular maintenance - otherweise I would do multi hundred million row delete statement. The last risk management database I build for a customer (energy, not stocks - so less symbols) had a space allocation of 20 terabyte and was using an Oracle ExaData hardware cluster (in 3 copies - in 3 locations, replicating).  It really depends how large the customer scale is for this.

That said, here is something really to think of: what is "stock trading"? If it is "manual" or "automated slow" then this is not too demanding - even 1000 traders wont do a TON of trades from a db perspective.

If it is HFT - High Frequenvy Trading - you may have a single algo in a single stock sometimes doing 100 trades per second. Add many markets, many symbols, many algos and you can not scale that in sql - this is why those algos write logs, keep things in memory and you then regularly import the transactions from the logs. Real time is NOT doable for HFT in SQL - especially not because it would impact the machines (slow down the trading side). When you cound millionths of a second (and yes, they work by now in microsecond scale) and use programmed hardware (FPGA) to make the price feed parsing to be faster than the next machine - you really decouple things. By the time SQL decides to commit the transaction about the purchase, the price may have moved too far ;) And no - do not say multi threading. I know some of the infrastructure. Thos algos run one thread per core, hard wired to the core, in burning mode - and endless loop checking for the position the other core has put the data. You easily may get 20.000 transactions per SECOND peak load from someone trying to do arbitrage between US stock markets alone. That is per exchange - and there are some.

As I said - this is a very complex system setup. A lot depends on the architecture.


PostgreSQL multi-column unique constraint and NULL values I have a table like the following:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);

And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.

How can I guarantee my unique constraint even if id_C can be NULL in this case?
Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?

You can do that in pure SQL. Create a partial unique index in addition to the one you have:

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

This way you can enter for (id_A, id_B, id_C) in your table:

(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

But none of these a second time.

Or use two partial UNIQUE indexes and no complete index (or constraint). The best solution depends on the details of your requirements. Compare:

Create unique constraint with null columns

While this is elegant and efficient for a single nullable column in the UNIQUE index, it gets out of hand quickly for more than one. Discussing this - and how to use UPSERT with partial indexes:

PostgreSQL UPSERT issue with NULL values

Asides

No use for mixed case identifiers without double quotes in PostgreSQL.

You might consider a serial column as primary key or an IDENTITY column in Postgres 10 or later. Related:

Auto increment table column

So:

CREATE TABLE my_table (
   my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
-- my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
 , id_a int8 NOT NULL
 , id_b int8 NOT NULL
 , id_c int8
 , CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);

If you don't expect more than 2 billion rows (> 2147483647) over the lifetime of your table (including waste and deleted rows), consider integer (4 bytes) instead of bigint (8 bytes).

A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row (example EscapeVelocity for a black hole Star).

In my opinion, it's usually better to normalize the tables by eliminating all Nulls.

In your case, you want to allow NULLs in your column, yet you want only one NULL to be allowed. Why? What kind of relationship is this between the two tables?

Perhaps you can simply change the column to NOT NULL and store, instead of NULL, a special value (like -1) that is known never to appear. This will solve the uniqueness constraint problem (but may have other possibly unwanted side effects. For example, using -1 to mean "not known / does not apply" will skew any sum or average calculations on the column. Or all such calculations will have to take into account the special value and ignore it.)


MySQL: List databases with tables Is there a way to list all my databases with tables in a single command?

Expectation:

mysql> SHOW DATABASES, TABLES;

    DB1 table1
        table2
    DB2 table1
        table2
        table3
        table4
        table5
    DB3 table1
        table2

Use the information stored in INFORMATION_SCHEMA:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
ORDER BY table_schema, table_name;

table_schema is the database name.

To show also the databases without any tables:

SELECT s.schema_name, t.table_name
FROM INFORMATION_SCHEMA.schemata AS s
  LEFT JOIN INFORMATION_SCHEMA.tables AS t
    ON t.table_schema = s.schema_name
-- optional, to hide system databases and tables
-- WHERE s.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY schema_name, table_name ;

Documentation links: INFORMATION_SCHEMA.tables,  INFORMATION_SCHEMA.schemata

If you want to exclude MySQL internal databases,

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' );

No need to add 'ORDER BY'


How to uniquely identify SQL instance I need to find a way how to identify SQL Instance no matter what changes were done to SQL Server and\or Windows Server where it is running, because we're collecting remotely information about instances into our operational database and still encountering way we're identifying it is not that unique as it might look.

In example, if I have server WIN1\SQL and due to server refresh will install on different server WIN2\SQL, migrate data and swap server names so that WIN2\SQL will become WIN1\SQL and vice versa, I need to be able identify that current WIN1\SQL is different from previous WIN1\SQL disregards OS version and or SQL version\patch level

Already tried following:

NetworkName\InstanceName - that worked until server hostname\network name has been changed due to server refresh as described above

SQL Server SID - I was searching for some SID of SQL Installation like Windows has, but seems nothing like that really exists

SID of ##MS_SQLAuthenticatorCertificate## - this was last best shot I found, scanned hundreds of SQL Servers and it was really unique - thought I found it finally, until I applied Service Pack to one server and find out SID has changed

Any idea how to uniquely identify SQL Instance (like SQL Instance SID) would be appreciated, because I'm getting lost

EDIT: I'm not administrating all SQL servers. Modifications on out of scope devices cannot be done, thus looking for some "already built-in" way that would work on SQL2005+

Well, it is somewhat possible to have an Instance GUID, but only if you are including [msdb] in your backup / restore process. Assuming that this is indeed the case, then just check the service_broker_guid field in sys.databases for the [msdb] database:

SELECT [service_broker_guid]
FROM   sys.databases
WHERE [name] = N'msdb';

It will be different across each instance, but should be consistent across restores of backups of the same [msdb] database. This even appears to hold true for the Express editions. And while I have not tracked this value across updates and Service Packs, I don't see why it would change unless forced to manually.

If you want to create new SQL Server instances from a "master" set of backup files (including [msdb]) and need the new instances to be seen as different, then just run the following after the initial restore of the system databases:

ALTER DATABASE [msdb] SET NEW_BROKER;

NOTES:

The service_broker_guid field in sys.databases for the [master] and [model] databases is always set to 00000000-0000-0000-0000-000000000000 and cannot be changed via ALTER DATABASE [{database_name}] SET NEW_BROKER;.
If you are using this technique to distinguish instances of SQL Server Express LocalDB, then the the service_broker_guid field in sys.databases for the [msdb] database will initially contain the same value for all newly created instances. In this case, you just need to execute ALTER DATABASE [msdb] SET NEW_BROKER;, just like when creating new instances from a backup restore.

Avoiding duplicate keys: The only way I see is creating a fresh token on every start of the database. To make it truly unique, you could place this token generation on one highly-available central server.

Keeping keys: This is pretty difficult to realize, you never know if the database has been moved or cloned or just started. If you manage to put all those operations under your control (special scripts, etc.) you could keep up the illusion that it works. But go on vacation for 2 weeks and your replacement does not know about the tools or forgets, you're doomed.

You could disable board tools, but if he sets up a fresh machine, he can use the tools there. I think this is a question of change/configuration management, keeping track and not so much about sql-server.


Explicit locks in RedShift I'm new to AWS Redshift and I wanted to know How i can implement my use-case.

I have a Redshift table which gets lot of Inserts/Selects.
In some cases I need to delete some data from that table. I don't want my Delete to impact other writes and reads.

Can someone suggest what is the best locking mode available in Redshift to implement my use case.

I don't want to end up with dadlocks and some locks hanging in my application.

I tried without locks and I'm observing the following violation:

Serializable isolation violation on table - 156300, transactions forming the cycle are: 6416378, 6416913 (pid:3770);

Also I went through the Redshift cxplicit locks but couldn't find any Official documentation for the same.

I know that Redshift is a wrapper on PostgreSQL, so can I use that documentation for implementing?

Assuming that it works like PostgreSQL in this case:

This is a serialization error that you get because you are using the SERIALIZABLE transaction isolation level.

That is nothing to worry about, just repeat the transaction.

In order to avoid the above mentioned error, you can use the LOCK  command to explicitly lock in that transaction.


Alter table on live production databases How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?

I know the correct way is to backup everything schedule downtime and do then do the changes.

But... does any current database system support doing these things "on-line" without stopping anything? (maybe just delaying the queries that reference a column that is just being changed/deleted)

And what does it happen when I just do an ALTER TABLE... on a live running database?
Does everything stop when this happens?
Can data get corrupted?
etc.

Again, I'm mostly referring to Postgres or MySQL as these are what I encounter.

(And, yes, anytime I had to do this before I did it "the right way", backing things up, scheduling downtine etc. ...but I just want to know if it's possible to do this sort and things "quick and dirty" or if there is any DB system that actually has support for "quick, live and dirty" schema changes)

Someone just suggested Online Schema Change for MySQL from Facebook script (with a tutorial here and source here)... seems like a nice way to automate a the set of "hacky" ways to do it... has anyone ever used it in something resemblig production?

When you issue an ALTER TABLE in PostgreSQL it will take an ACCESS EXCLUSIVE lock that blocks everything including SELECT. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE, CHECK or FOREIGN KEY constraints need expensive full-table scans to verify, etc.

If in doubt, you can generally just try it! All DDL in PostgreSQL is transactional, so it's quite fine to cancel an ALTER TABLE if it takes too long and starts holding up other queries. The lock levels required by various commands are documented in the locking page.

Some normally-slow operations can be sped up to be safe to perform without downtime. For example, if you have table t and you want to change column customercode integer NOT NULL to text because the customer has decided all customer codes must now begin with an X, you could write:

ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );

... but that would lock the whole table for the re-write. So does adding a column with a DEFAULT. It can be done in a couple of steps to avoid the long lock, but applications must be able to cope with the temporary duplication:

ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;

This will only prevent writes to t during the process; the lock name EXCLUSIVE is somewhat deceptive in that it excludes everything except SELECT; the ACCESS EXCLUSIVE mode is the only one that excludes absolutely everyting. See lock modes. There's a risk that this operation could deadlock-rollback due to the lock upgrade required by the ALTER TABLE, but at worst you'll just have to do it again.

You can even avoid that lock and do the whole thing live by creating a trigger function on t that whenever an INSERT or UPDATE comes in, automatically populates customercode_new from customercode.

There are also built-in tools like CREATE INDEX CONCURRENTLY and ALTER TABLE ... ADD table_constraint_using_index that're designed to allow DBAs to reduce exclusive locking durations by doing work more slowly in a concurrency-friendly way.

The pg_reorg tool or its successor pg_repack can be used for some table restructuring operations as well.

To address the question about what happens with an ALTER TABLE statement, it depends on the extent of your changes. In specific cases, if you add a new column, at least in MS SQL Server, the engine will create a temporary copy of the table, while it creates the new table definition, and then inserts the data back in there. For the duration of the change, the table thus would be inaccessible to users.

An example of the specific operations for MSSQL server is here: http://support.microsoft.com/kb/956176/en-us

I would assume that other RMDBs have similar methods, though the exact implementation would be something you would have to verify with the vendor documentation.


Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11) The ORACLE SQL manual (Page 2-58 of the main 11.1 SQL Manual) states:

  The total length of a datetime format model cannot exceed 22 characters.

However, I've been able to use a format string for TO_CHAR with many more characters, almost all of which are text "literals".

Is this safe (ORACLE 11)? (Perhaps the limit does not include non-date literals?)

For example:

SELECT TO_CHAR(sysdate,
       '"SOMESTRING_"YYYYMMDD"_SOME LOTS MORE STRINGS OF A FILENAME PREFIX"')
FROM DUAL;

Result:

SOMESTRING_20200226_SOME LOTS MORE STRINGS OF A FILENAME PREFIX

It also works with dates from tables.

The reason I want to do this is to build a filename template in a database column; otherwise I will need to put the date format in a separate field.

P.S. Please tell me if this should be asked in a different community. I've never asked a question before, so I have no reputation to lose :-). Be nice.

Just because you can jump off a cliff, doesn't mean you should.

Use REPLACE() with a token:

select REPLACE('SOMESTRING_##DATE##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX',
               '##DATE##',
               TO_CHAR(sysdate,'YYYYMMDD'))
from DUAL;

You could take this a step further to allow different format strings:

with mytable as (
select 'SOMESTRING_##YYYYMMDD##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX' as format_string
from DUAL
)
select REGEXP_REPLACE(format_string,'##(.*)##',TO_CHAR(sysdate,replace(
                    regexp_substr( format_string, '##(.*)##', 1, 1),'##','')))
  from mytable;

Note that there have been parsing bugs with long TO_CHAR identifiers that have caused ORA-600 errors & session crashes.

I would use this one instead:

SELECT 'SOMESTRING_' || TO_CHAR(sysdate,'YYYYMMDD') || '_SOME LOTS MORE STRINGS OF A FILENAME PREFIX'
FROM DUAL;


Why shouldn't we allow NULLs? I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL.  I don't remember why this was the case though.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

But what do you do in the case of dates, datetime, and time (SQL Server 2008)?  You'd have to use some historic or bottomed-out date.

Any ideas on this?

I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"

Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my birth date? What are you going to put in the column until you know? If you're anything like a lot of anti-NULL folks, you're going to enter 1900-01-01. Now I'm going to be placed in the geriatric ward and probably get a call from my local news station congratulating me on my long life, asking me my secrets to living such a long life, etc.

If a row can be entered where it is possible that you don't know the value of a column, I think NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown - a value which others will have to already know, reverse engineer, or ask around to figure out what it means.

There is a balance, though - not every column in your data model should be nullable. There are often optional fields on a form, or pieces of information that otherwise don't get collected at the time the row is created. But that doesn't mean you can defer populating all of the data. :-)

Also the ability to use NULL can be limited by crucial requirements in real life. In the medical field, for example, it can be a life-or-death matter to know why a value is unknown. Is the heart rate NULL because there wasn't a pulse, or because we haven't measured it yet? In such a case, can we put NULL in the heart rate column, and have notes or a different column with a NULL-because reason?

Don't be afraid of NULLs, but be willing to learn or dictate when and where they should be used, and when and where they shouldn't.

Wow, the correct answer "Don't allow NULLs when you don't have to because they degrade performance" is somehow the last rated answer. I will upvote it and elaborate. When a RDBMS allows NULLs for a non-sparse column, that column is added to a bitmap that tracks whether the value is NULL for each individual row. So by adding NULL-ability to a column in a table where all columns do not allow NULLs, you are increasing the storage space required to save the table. Further, you are requiring the RDBMS to read and write to the bitmap, degrading performance on all operations.

Further, in a number of instances, allowing NULLs will break 3NF. While I am not a stickler for 3NF like many of my colleagues, consider the following scenario:

In the Person table there is a column, called DateOfDeath, which is nullable. If a person has died, it will be filled in with their DateOfDeath, otherwise it will be left NULL. There is also a non-nullable bit column called IsAlive. This column is set to 1 if the person is alive, and 0 if the person is dead. The vast majority of the stored procedures use the IsAlive column, they care only if a person is alive, not their DateOfDeath.

However, the IsAlive column breaks database normalization, because it is completely derivable from DateOfDeath. But since IsAlive is hard-wired into the majority of SPs, the straightforward solution is to make DateOfDeath non-nullable, and assign a default value to the column in the event the person is still alive. The few SPs that use DateOfDeath can then be rewritten to check the IsAlive column, and only honor the DateOfDeath if the person is not alive. Again, since the majority of SPs care only about IsAlive (a bit) and not DateOfDeath (a date) using this pattern speeds up access considerably.

A useful T-SQL script for finding nullable columns with no NULLs across all schemas is:

select 'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' IS NULL)
    AND (SELECT COUNT(*) FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ') > 1 PRINT ''' + s.name + '.' + t.name + '.' + REPLACE(c.name, '''', '''''') + ''''
    from sys.columns c
    inner join sys.tables t ON c.object_id = t.object_id
    inner join sys.schemas s ON s.schema_id = t.schema_id
    where c.is_nullable = 1 AND c.is_computed = 0
    order by s.name, t.name, c.name;

If you run this on a copy of your production database you can find the columns developers marked as allowing NULLs that have no NULLs in practice. The vast majority of these can be marked as NOT NULL, thereby increasing performance and lowering storage space.

It may not be possible to eliminate all NULLs in all tables and still have a clean design, but there is a considerable advantage in eliminating as many NULLs as possible. The optimizer works much faster with this information, and if you can eliminate all NULLs in a table you can regain a considerable amount of storage space.

I know that performance isn't something that DBAs think about all that much, but you can only throw a limited amount of memory and processor power at a solution, a some point you're going to have to start thinking about logical and physical design.

Also note that this is only for true RDBMSes and I am basing the technical portion of my answers off SQL Server. The listed T-SQL to find nullable columns without nulls is also from SQL Server.


Does ELT data end up in an RDBMS? I think I am confusing things.

In ETL, data conversion starts at Database A and ends up in Database B, both are relational databases.  Maybe I have a 20-year-old system.  I use ETL to get the data between the two databases.  I do not know what else ETL is used for.

In ELT, is that the same except the data from Database A never ends up in Database B?  Instead, the raw data is sitting in tables or some other data structure on Server B, from Database A, but requires something like Hadoop to transform that data into something an application that is specifically designed to use Hadoop?

Edit:  I have done it again.  Hadoop != ELT.  I was looking only at Hadoop and thought it was ELT or the manifestation of it. edit: and that ELT meant you required a unique non-RDBMS file data structure instead of an RDBMS and you dumped the RDBMS altogether.

I think this will be easier with an example:
Database A has table C.  Database B has table D.  C and D are very similar but not identical and the data needs to be cleaned up before being loaded in to D.

ETL
An ETL program (SSIS, Pentaho, whatever) pulls the data from table C.  It then makes some changes to the data to clean it up and put it in the format that is needed.  The ETL process then moves/copies that data up to table D on Database B.

ELT
The data from Table C is extracted.  It is uploaded to Database B as table C.  Database B now has two tables, C and D.  A database process (SQL, a job, whatever) now makes some changes to the data in table C and puts it in the format that is needed.  That same process then copies the now clean data in to table D.  The temporary Table C is no longer needed in Database B and can be deleted or truncated.

In both cases the data is now loaded in Database B, Table D.  The front end application continues to use Table D.

There are many ways i can think of having an ELT system and also treating Hadoop as an ELT design.

Data warehouses with very high quality of keys across all application databases.
Types of transformations which can be hundreds of ways, based on how the user wants to look at information. Users accesses data based on predefined list of transformations (For example, in a Financial Planning and Analysis reporting, we could have Calculations at Year-To-Date, YTD-1, YTD-2, YTD-3, QTD, QTD-1, QTD-2, QTD3, Month-To-Date..... 100s of such calculations are used by Analysts)

A Hadoop implementation generally gets data from many data sources. And having a data extraction layer such as Pig or Hive performing the transformations is quite similar to an ELT design.

As for a differentiation between ETL & ELT, it is based on scope of design.


Mongo crash after 2 seconds of starting My mongod service crashes within 2-5 seconds of starting. I don't understand why.

Here are the systemctl logs:

  systemctl status mongod.service

mongod.service - High-performance, schema-free document-oriented database
Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Thu 2016-05-05 11:43:57 UTC; 4min 40s ago
Process: 7597 ExecStart=/usr/bin/mongod $OPTIONS run (code=exited, status=0/SUCCESS)
Main PID: 7601 (code=exited, status=14)

May 05 11:43:52 database13.choozon.net systemd[1]: Starting High-performance, schema-free document-oriented database...
May 05 11:43:52 database13.choozon.net mongod[7597]: warning: bind_ip of 0.0.0.0 is unnecessary; listens on all ips by default
May 05 11:43:52 database13.choozon.net mongod[7597]: about to fork child process, waiting until server is ready for connections.
May 05 11:43:52 database13.choozon.net mongod[7597]: forked process: 7601
May 05 11:43:54 database13.choozon.net systemd[1]: Started High-performance, schema-free document-oriented database.
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Main process exited, code=exited, status=14/n/a
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Unit entered failed state.
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Failed with result 'exit-code'.

So far I have:

checked permissions (everything looks good)
checked the service
tried deleting mongo.lock

mongod appears to start without any errors, but then fails after a couple of seconds.

Any ideas ?

Search for mongod.lock and remove it and restart the service.

Find out the Mongo data path cat /etc/mongod.conf | grep dbPath
Look for mongod.lock file in it.
If its there then delete the file and restart the server.


How to store likes to posts in Parse.com database? In my parse database I have a classed called Post and Like. Should I create a field likes in Post class with Relation<Like> type or there is another better option of storing likes in Parse DB?

For those purpose, I only use relations in the USER table with users ids stored as pointers inside.

Using a cloud code function, I provide a  parameter to add , del or whatever instructions to apply to a single or an array of users. Cloud gives the opportunity to create collateral events such as "a user like this"

As a reading step, a query is applied to the relation to retrieve parts or all users.

The column is named with the purpose such as I will add 2 columns to mention  respectively "like" , "dontLike"

However, if one need more than a single information , a second table needs to be created and linked also with a relation.
For example, a new table with a first column which is a regular pointer to users, other  columns to stored the additional attributes.
Which means that this new table must be first saved, to be able to stored its id in the relation.


Load specific element from XML I have the following xml:

<root>
 <123 type="line">
  <aa type="item">
   <value>1</value>
  </aa>
 </123>
 <234 type="line">
  <bb type="item">
   <value>2</value>
  </bb>
 </234>
</root>

How can I from bulk loading the xml into a database table (field xmldata)
query the xml so I get all values under type="line" and then all values under type="item" so I can then get to the value attribute. I can't hard code the attribute (eg 123) because it changes all the time, but the type value will remain constant.

You should use the nodes() method of the XML data type to shred your XML.

If I understand you correctly you want to fetch the nodes from the root node where the attribute type is line and from that node you want the nodes where the attribute type is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you are using but that the node names are valid XML names.

declare @XML xml = '
<root>
  <A123 type="line">
    <aa type="item">
      <value>1</value>
    </aa>
  </A123>
  <B234 type="line">
    <bb type="item">
      <value>2</value>
    </bb>
  </B234>
</root>';

select T.X.value('text()[1]', 'int') as Value
from @XML.nodes('/root/*[@type = "line"]/*[@type = "item"]/value') as T(X)

A breakdown of the xPath in the nodes function:

From /root give me all nodes /root/* where the attribute type is line /root/*[@type = "line"] and from there give me all nodes /root/*[@type = "line"]* where the attribute type is item /root/*[@type = "line"]/*[@type = "item"].

Finally get the value node /root/*[@type = "line"]/*[@type = "item"]/value and extract the text node using the value() function.

As i understand you need the XML upload option which customized but not hard coded one you need.

SELECT * FROM T UPDATE T SET XmlCol =(
SELECT * FROM OPENROWSET(
   BULK 'C:\SampleFolder\SampleData3.txt',
           SINGLE_BLOB
) AS x
)
WHERE IntCol = 1;
GO

The answer is available in MSDN, Please refer the below link
MSDN Article


Select historical prices for an exact datetime - MySQL I have a price_list table where I store historical prices for each item.

columns are -  id, name, price, start_date, item_id

When a price is changed, a one new row is inserted with a current start_date. (Start_date is a datetime format).

Now I need to select prices of all items for an exact datetime. Problem is that this query is slow (1.058 sec):

SELECT * FROM (SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id

Very slow is a GROUP BY. Without it it takes 0.075 sec, but each item (item_id) is there a multiple times.

bad: SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY item_id, start_date DESC

Please, can this query be rewritten to be quicker?

Duration goes to 1 sec when I'm selecting name column. But when I use this query, time is only 0.094 sec:

SELECT h.* FROM (SELECT * FROM (SELECT id, item_id FROM price_list x WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id) b LEFT JOIN price_list h ON h.id = b.id

Thank you, Ryan!


Oracle full expdp/impdp but skip data in some tables I'm trying to export a schema and then import it, but there are some huge tables that I must skip all the data from (that is, I need the tables and all stuff related to them, just not the rows).

So I thought I'd do

1) expdp SCHEMAS=X CONTENT=METADATA_ONLY DUMPFILE=meta.dmp
2) exportSCHEMAS=X CONTENT=DATA_ONLY EXCLUDE=TABLE:"IN \('FOO','BAR'\)" DUMPFILE=data.dmp

and then

4) impdp SCHEMAS=X EXCLUDE=REF_CONSTRAINT DUMPFILE=meta.dmp
5) impdp SCHEMAS=X DUMPFILE=data.dmp

The plan is in step 4 to get all the meta data, and then in step 5 import all data but the rows in the large tables FOO and BAR,
But how can I get the foreign keys?

Is this a way to do it, or are there other ways?

No, it is not a good approach, there are a lot of problems, e.g.

Doing a data import after the indexes are created will slow down the import considerable.
the import will fire all insert triggers. That may slow down the import, too, and it may have unwanted side effects
The current values of the sequences do not match to data.

You should use the QUERY parameter. So if you want to exclude the LOG and MESSAGE table of the APP schema add

QUERY=APP.LOG:"where 0=1"
QUERY=APP.MESSAGE:"where 0=1"

to the parameter file of your export.

Another way to accomplish this task is to split this into two export. This is a little bit more complex but avoids reading table data.

export the schema and exclude the large tables. his task is independent from the size of the two large tables
export the structure of the two large tables. The fime for this task is also independent of the size of the two tables

first export (schema export with data)

DUMPFILE=schema.dmp
SCHEMAS=APP
EXCLUDE=TABLE:"in ('LOG', 'MESSAGES')
CONTENT=ALL  # this is the default, so it must not be specified

second export (table exports without data)

DUMPFILE=table.dmp
TABLES=APP.LOG,APP.MESSAGE
CONTENT=METADATA_ONLY

At first you import the schema dump, then you import the table dump. For both imports you can use the FULL=Yparameter.

schema import:

DUMPFILE=schema.dmp
FULL=Y

table import:

DUMPFILE=table.dmp
FULL=Y

While importing in step 4 do not EXCLUDE REF_CONSTRAINTS but rather IMPORT all metadata including constraints and before step 5 disable the constraints and start data import.

You may also need to disable triggers (if any) if you only think that it would not affect your database  for faster import.

Hope this helps !!


ORA-01017 error when conecting to Oracle Database Standard Edition 2.12.2.0.1.0 for Microsoft Windows x64 (64-bit) I've installed and created non-CDB database (single tenant) database. Created a user account using the SYS user, granted it the CONNECT and CREATE SESSION roles. When I attempt to login using the newly created account I get the "ORA-010170: invalid username/password; login denied" error.

I'm I missing something with respect to the Oracle 12c Standard database?? Since I've created a "normal" database I should be able to login with a non SYS account.

Thanks!!

Check the netca config. There needs to be an entry in tnsnames.ora.


Is there a way to get count based on multiple columns but still have it be distinct in SQL? I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL.

Hopefully, this is possible as the algorithm I have is not giving me the correct results.

Not sure I understand the problem, but if you first pick distinct rows, and then count number of rows per date

select dt, count(1)
from (
    select distinct dt, job, title, name
    from t
) as x
group by dt

I used dt instead of date since it is a reserved word

I am not quite sure what you are asking, but I think you would want to concatenate the other columns you have into one string. then you group by the date, and count the distinct concatenated fields.

In the below i dump to a temp table first to make it easier to read, but you can do it all in one.

DROP TABLE IF EXISTS #X
SELECT
Date,
Job + ' ' + Title + ' ' Name As NewColumn
INTO #X
FROM
YourTable

SELECT
Date,
COUNT(DISTINCT NewColumn) AS CNT
FROM
#X
GROUP BY
Date


'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces I am trying to backup mysql using the command
mysqldump -u root -p  database_name > backup.sql

, but it is throwing an error:

'Access denied; you need (at least one of) the PROCESS privilege(s)
for this operation' when trying to dump tablespaces

Also, I wanted to make a backup my database with all tables seperately stored as a file. How can I do it?
mysql version = 5.7.31

use --no-tablespaces
see mysqldump-Documentation

mysqldump requires at least the SELECT privilege for dumped tables,
SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES
if the --single-transaction option is not used, and (as of MySQL
8.0.21) PROCESS if the --no-tablespaces option is not used.
Certain options might require other privileges as noted in the option
descriptions.

and see documentation of param no-tablespaces

--no-tablespaces, -y
This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE
statements in the output of mysqldump.

Resolve by adding:
--column-statistics=0 --no-tablespaces

to your dump command. I had to add the column-statistics also as just no-tablespaces was not working.


Split duration hourly depending on start and end time I have a table that registers the status for equipment. The status has a start and an end time. But now I want to know for every hour what the status is. If the status was "Operating" from 15:20 to 17:10, I want to see that it was 40 minutes on Operating for the 16th hour of the day, 60 minutes operating on the 17th hour and 10 minutes operating on the 18th hour of the day.

This is what I have now:

Shift_date  Status     Start_timestamp  End_Timestamp
----------  ---------  ---------------  ---------------
5/20/2017   Operating  5/20/2017 8:21   5/20/2017 10:40
5/21/2017   Delay      5/20/2017 10:40  5/20/2017 11:10
5/22/2017   Operating  5/20/2017 11:10  5/20/2017 13:50

This is what I want:

Shift_date  Hour  Status     Duration (minutes)
----------  ----  ---------  ------------------
5/20/2017   1     ..         ..
5/20/2017   ..    ..         ..
5/20/2017   9     Operating  39
5/20/2017   10    Operating  60
5/20/2017   11    Operating  40
5/20/2017   11    Delay      20
5/20/2017   12    Delay      10
5/20/2017   12    Operating  50
5/20/2017   13    Operating  50
5/20/2017   13    ..         ..
5/20/2017   14    ..         ..
5/20/2017   ..    ..         ..
5/20/2017   24    ..         ..

The basic idea here is to add rows for each row in the status table depending on how many hours it covers. I'm doing this by joining to a numbers table. From there, you just need to account for all of the different cases to figure out how many minutes for the status are relevant for each hour bucket.

Data prep:

CREATE TABLE #t (
Shift_date DATETIME NOT NULL,
[Status] VARCHAR(20) NOT NULL,
Start_timestamp DATETIME NOT NULL,
End_Timestamp DATETIME NOT NULL
);

INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 8:21', '5/20/2017 10:40');
INSERT INTO #t VALUES ('5/20/2017', 'Delay', '5/20/2017 10:40', '5/20/2017 11:10');
INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 11:10', '5/20/2017 13:50');

One approach which should work on 2008:

-- may need more rows here depending on how long the events can be
WITH numbers (n) AS (
    SELECT 0
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
)
SELECT
   t2.Shift_date
 , DATEPART(HOUR, start_final) AS [hour]
 , t2.[Status]
 , DATEDIFF(MINUTE, start_final, end_final) Duration
FROM
(
    SELECT Shift_date
    , [Status]
    , Start_timestamp
    , End_Timestamp
    , CASE WHEN t.Start_timestamp > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final
    , CASE WHEN t.End_timestamp > n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final
    FROM #t t
    CROSS APPLY
    (
        SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers
        , dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbers
        FROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) >= n.n
    ) n
) t2
ORDER BY start_final;

My results (it looks like you have a few issues with your sample data):

╔═════════════════════════╦══════╦═══════════╦══════════╗
║       Shift_date        ║ hour ║  Status   ║ Duration ║
╠═════════════════════════╬══════╬═══════════╬══════════╣
║ 2017-05-20 00:00:00.000 ║    8 ║ Operating ║       39 ║
║ 2017-05-20 00:00:00.000 ║    9 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Operating ║       40 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Delay     ║       20 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Delay     ║       10 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Operating ║       50 ║
║ 2017-05-20 00:00:00.000 ║   12 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   13 ║ Operating ║       50 ║
╚═════════════════════════╩══════╩═══════════╩══════════╝

select convert(varchar(11), GETDATE()-1, 113) as ProductionDate,DATEPART(hour,sr.testTime) as [hour], COUNT(DISTINCT SR.number) AS AchievedCount
from Testertable(nolock) sr
where testTime  >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), '06:00:00') and
testTime <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), '06:00:00')
group by  DATEPART(hour,testTime) order by hour

Instead of GETDATE() you can use the actual date


MySQL Union All Causing date field to lose format I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for example '2016-11-01' is showing as '2,016, -11, -01'. This is the same for every row.

I've had a search and have found some threads concerning aliases but they do not seem to be the problem here as the query is running fine, with the exception of the date format.

Any help would be appreciated, thanks.

Here is the code:

    # offers_8a
    SELECT advisor, ats_id, offer_date
    FROM max_offers
    WHERE offer_date > (CURDATE()- INTERVAL 135 DAY)
    GROUP BY advisor, ats_id, offer_date

    UNION ALL

    # offers_8b
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN source_tables.dates as dt
            ON ic.intake = dt.current_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY)

    UNION ALL

    # offers_8c
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN

            #s_prev_intake
            (SELECT current_next_intakes.current_intake, assign,         forecast_intake, forecast_intake_sugar

                FROM source_tables.dates
                    INNER JOIN source_tables.current_next_intakes
                        ON source_tables.dates.current_intake =  source_tables.current_next_intakes.current_intake

                WHERE assign = 'Prev') AS prv

            ON ic.intake = prv.forecast_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY);

I have fixed this by enclosing the Union in brackets and using it as a derived table in a Select query, didn't need to alter any formats etc.

Is it always best to do this with Union queries? The examples I found online did not show this.

Thanks


Getting time delta between two related rows I have a table with a lot of entries in pairs, related to one another by a field name, each entry has a timestamp timestamp_received. I'd like to get the delta of the timestamp per name

table:

|id|name|timestamp_received|
|1|A|2016-09-21 11:11:00|
|2|A|2016-09-21 11:11:10|
|3|B|2016-09-21 11:11:20|
|4|B|2016-09-21 12:12:21|

desired output:

|name|delta|
|A|00:00:10|
|B|01:01:01|

What I have tried so far:

SELECT name, timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) delta
FROM message;

Which does not produce the expected output

SELECT fpm_left.name, age(fpm_right.timestamp_received, fpm_left.timestamp_received)
FROM
  (SELECT * FROM message WHERE id IN (SELECT min(id) AS id FROM message GROUP BY name)) fpm_left
JOIN (SELECT * FROM message WHERE IN in (SELECT max(id) AS id FROM message GROUP BY name)) fpm_right
    ON fpm_left.name = fpm_right.name AND fpm_left.id != fpm_right.id;

Which produces the expected output, but seems a little innefficient

Can this be done more elegantly?

You can use the solution with the window function, you just need to filter out the "other" row. This can e.g. be done using Postgres' distinct on ()

SELECT distinct on (name)
       name,
       timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) as delta
FROM message
order by name, timestamp_received desc;

This will always pick exactly one row per name (the one with the latest timestamp_received), even if there are more then two rows per name.

This will return the difference from first and last timestamp_received by name

SELECT distinct
    name,
    datediff(ms,
        (select timestamp_received
         from message
         where name = m.name
         order by timestamp_received desc),
        (select timestamp_received
         from message
         where name = m.name
         order by timestamp_received asc)
    ) as delta
FROM message m


Should I separate frequently updated columns? I have a users table that contains users' information and a column named credits that is frequently updated.

On index page I'm showing list of users with basic user information but I don't need credits.
On details page I'm also showing credits with user information.

Two main operations are dense in project. The first operation is the SELECT operation on index page. The second operation is the UPDATE operation on credits column. credits column is not indexed but since it's frequently updated, will it effect the indexed columns on users table? If so, I think I should separate credits column as a table and join it only when required. Doing by this, can I improve SELECT performance on index page?

Also, I'm using MySQL. What kind of table structure should I use for frequently updated columns?

It seems what you want is a so-called Index-Only Scan for the query on the "index" page. That means that you have all the data (columns) that you select on the index page in the index you are using for this query. This makes it unnecessary to go to the table when executing this query.

Example:

SELECT name, basic, information
  from users
 where filter = something

(I just suppose you are using some filter).

If you create the following index:

 ALTER table users add index (filter, name, basic, information);

MySQL can execute the above query without actually reading anything from the table (just the index—hence, Index-Only Scan). In other words, the number of columns in the table does not affect the performance of this query.

You can recognize an Index-Only Scan by the presences of "Using Index" in the Extra column of the execution plan (explain).

If you update columns that are not part of this index, the index doesn't need to be updated.

Some links to my site http://use-the-index-luke.com/

Index-Only Scan (aka. covering indexes)
Update performance
Getting MySQL execution plans & Operations

Yes you are right. You should separate credits column from the table in order to make detail work efficiently. And try not to make indexes of frequently used tables.


How do I cancel an app lock request in SQL Server? The sp_getapplock stored procedure has the following return values:

  0: The lock was successfully granted synchronously.
  1: The lock was granted successfully after waiting for other incompatible locks to be released.
  -1: The lock request timed out.
  -2: The lock request was canceled.
  -3: The lock request was chosen as a deadlock victim.
  -999: Indicates a parameter validation or other call error.

I am writing a wrapper for calling sp_getapplock in our data access layer and I want to know under which circumstances -2 can be returned so that I can throw a descriptive and helpful exception. It is obvious what return values of -1 and -3 mean and I can easily create test conditions that cause those values to be returned. How would I manage to get a return value of -2?

Looking at the source of the sp_getapplock wrapper proc, all the return values except for -999 originate from the underlying sys.xp_userlock  internal stored procedure. I'd bet the internal proc returns a -2 when the request is cancelled by an attention event (client query timeout or explict client query cancel). However, no further sp_getapplock code executes after the batch is cancelled, including the RETURN statement. Consequently, the -2 return code may be returned internally but there's no practical way for the client to get the value.

Assuming this theory is correct, there isn't any value in translating to -2 to more descriptive message since it's the client that cancelled the request in the first place.

I'll leave it to Paul to confirm this by stepping through the SQL database engine code with a debugger :-)

There's a corresponding Release application lock stored procedure called sp_releaseapplock.

I wrote an article confusingly titled "Mutexes in SQL" here on SQL Server Central about using these stored procedures to control application flow.


Bigint and SQL Server performance I inherited a database in which many of the tables have bigint as a datatype for many of the fields, and when you see the content it does not requiere all the space that bigint offers. Does using bigint for fields that dont need it affect the database performance?

Using bigint compared to int has, at least, these potential performance drawbacks:

the data will use more pages on disk

this can affect how long it takes to read data from disk when it's not in RAM
it will also make any maintenance operations involving those fields take longer (backups, index rebuilds, CHECKDB)

these data pages will take up more space in RAM

this means you'll either need to purchase more RAM, or incur the cost of reading from disk more often

memory grants that include these columns will be larger

this affects memory-consuming query plan operators, like sorting and hashing data
as a secondary impact, this has the effect of reducing concurrency

That being said, how much practical impact these things have for you is very dependent on your specific environment and workload.

Note: the increases in disk and RAM usage issues can be mitigated by using row compression, at the cost of increased CPU usage.  As servers often have more CPU overhead than RAM, this is generally a good tradeoff (thanks to Andy for this reminder!)

Yes, performance got affected.

In the optimization phase, query optimizer uses 50% data size per column to estimate row data size. If we use larger data type for columns that store smaller data, estimated data size coming from tables will be higher and cause higher ideal memory grant requirement for queries.

Over estimated memory grant cause memory pressure for SQL Server and effect Page Life Expectancy badly (make them lower). Lower PLE value means data pages read from disk are cached in memory (RAM) for shorter period.

To avoid performance problems, every variable length columns, local variables and parameters should be designed properly. We can use int instead of bigint for an identity column and set -2,147,483,648 as start value to get double size benefit from int data type.


Right way to delete the archive - Oracle So I have an Oracle database (standalone) running in RHEL and I have scheduled RMAN for backups on daily and weekly basis. Sometimes my file system (archive mount-point) becomes full because of high archive generation. Now what is the right way to delete the archives from the file system?

My RMAN script has this

delete archivelog all completed before 'SYSDATE-5'

but the above command isnt deleteing the archives properly as I see, some of the very old archives are still there..

How to delete them properly?

Should I run crosscheck archivelog all? and then run the above command once again? What is the right way?

Thanks!

In order to delete them you can do: RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all; , you can also include the delete input clause when you back them up, and they will be deleted after they have been backed up (it is up to you). You can try and run the command you have shown manually to see what will happen in RMAN (in other words, does it work or does it give an error).

type rman from oracle user then hit connect target then delete archivelog all;


Escape characters in Oracle dump When I take a dump of a particular table which has CLOB data type, the resulting dump doesn't handle escaping of special characters.

I get ' un escaped, so insert statements won't work.

Any ways to fix it?

I'm right clicking on the query result and getting the dump.

I'm using SQL Developer and am new to Oracle.

As someone who spent 4 years of his life dealing with converting CLOB type data from legacy systems into Oracle, I feel your pain.

I highly recommend using an Oracle built function, such as REPLACE to go through and scrub all of the garbage characters from CLOB type fields. Additionally, remember that most systems consider a "return" to be a new line and carriage return in unison, so you might have to scrub ascii characters below 33. IIRC the new line/carriage return combo was char(10)char(13). You can easily google a list of of the "invisible" ascii characters, and hunt down the ones you want removed.

HTH.


mysqldump doesn't restore database. why not? Why doesn't my restore operation work?

I created a dumpfile for a database using:

mysqldump -u root -p databasename > /home/databasename_bkup.sql

I then opened the dumfile and confirmed that it contains CREATE TABLE statements for each of the tables in the database.  So I dropped the database and re-created an empty database of the same name before running the following restore command:

mysqldump -u root -p databasename < /home/databasename_bkup.sql

This restore command resulted in the following printing in the terminal:

-- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost    Database: databasename
-- ------------------------------------------------------
-- Server version   5.6.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-05 17:56:29

But then when I log into mysql to check the contents of the database, I found out that the database is empty, as follows:

mysql> use databasename;
Database changed
mysql> show tables;
Empty set (0.00 sec)

So why is the database not being restored?  What specific syntax can I use to ensure that the database does get restored properly?

You have to use the mysql client to reload

mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql

Another way to reload would be

mysql -u root -p -Ddatabasename

then from the MySQL prompt, do this

mysql> source /home/databasename_bkup.sql

If you would like the mysqldump to drop and recreate the database for you, create the dump like this:

mysqldump -u root -p --add-drop-database -B databasename > /home/databasename_bkup.sql

Then, running the script

mysql -u root -p < /home/databasename_bkup.sql

does the DROP DATABASE command for you.

put "", like this
mysqldump -u root -p databasename > "c:/home/databasename_bkup.sql"


Mysql error while creating FK here is structure of both tables

category table
CREATE TABLE category (
`catID` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(60) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`catID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

and the second table projects

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`industryID` int(11) NOT NULL,
 `dsID` int(11) NOT NULL,
`countryID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
KEY `industryID` (`industryID`),
KEY `dsID` (`dsID`),
KEY `countryID` (`countryID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now to add FK to tables here is the query but i keep getting the error

Can't create table .... (errno: 150)
ALTER TABLE `projects`  ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID)  REFERENCES category (catID)

When you create a foreign key between two tables the data type of the columns that are taking part in the relationship must have the same data type. In the example you have posted above it is failing because the Categories table defines CatID as a BIGINT wheras in the Projects table you have defined it as a INT. Changing the data type of CatID in the Projects table to BIGINT should resolve your problem.

I hope this helps you.

Please find the solution here.

mysql> CREATE TABLE category (
    -> `catID` bigint(20) NOT NULL AUTO_INCREMENT,
    -> `title` varchar(60) NOT NULL,
    -> `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->  PRIMARY KEY (`catID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `projects` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `catID` bigint(20) NOT NULL,
    -> `industryID` int(11) NOT NULL,
    ->  `dsID` int(11) NOT NULL,
    -> `countryID` int(11) NOT NULL,
    -> `title` varchar(255) NOT NULL,
    -> PRIMARY KEY (`id`),
    -> KEY `catID` (`catID`),
    -> KEY `industryID` (`industryID`),
    -> KEY `dsID` (`dsID`),
    -> KEY `countryID` (`countryID`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE `projects`
        ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID)
            REFERENCES category (catID) ON DELETE RESTRICT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


Controlling the order in which merge articles are applied by snapshot at initialization? I have a replication scheme that was created several months ago.  Since that time the schema has had a variety of changes.  All existing subscriptions were updated as these changes were made as the publication is set to push schema changes as well.

Recently however when trying to add a new subscription the initial snapshot fails to apply.  The cause of the error is that two different views fail to apply as tables they rely on have not been created by the snapshot yet.

I know that I can specify processing order of articles via the sp_changemergearticle stored procedure.  But as I understand... the processing order setting here is for specifying the order in which data changes are applied to subscribers during sync.  Is there a way to specify the order in which articles are applied when a new subscription is initialized?  Does it use the same setting?

I figured that generating a new snapshot would cause the engine to figure out the proper order but in the case of my current publication it did not.  The new subscription fails to apply the snapshot even after I have generated a new snapshot.  It tells me the exact script that fails and when applying by hand it's obvious that it's failing because a table needed by the view does not yet exist.

What is the best way to fix this?  Does setting the processing order of an article affect the schema order as well as the data order or do I need to look somewhere else to set the order that articles are built on new subscriptions.

From what I can tell the processing order setting has no effect whatsoever on the order in which the snapshot applies its articles during initialization.  Mostly because currently all my articles have a value of 0 for their processing order.  So, is this something I can control or am I at the mercy of the snapshot generation process?

Yes, utilizing sp_changemergearticle to change the processing order will alleviate this issue.

I have the same question, very well exposed by Thomas.
As far as I know, processing order doesn't make any difference on snapshot schema order, but only in data applying order.

I think that the schema is applyed in the same order than the articles are added to the publication. I'am not sure and would appreciate a confirmation of this.

Numbers added at the end of .SCH files could be a track to understand the logic behind the snapshot constitution and application.


How do I find PostgreSQL's data directory? I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory.

I'm on MacOsX if that helps.

/usr/local/postgres doesn't exist on my Mac.

Using the answers provided below, I found that it was here:

/usr/local/var/postgres

If you can connect to the database with superuser access, then

SHOW data_directory;

is the shortest way.

If the server is not running and you forgot where the data directory was, then you really have to guess yourself.  An operating system specific convention or the shell history might provide hints.

How about using the standard utility pg_config such as pg_config --bindir One could also get the the configure parameters used to compile PostgreSQL and parse them with pg_config --configure


Oracle: are objects created by sys in a schema the same as objects created by that user? For example, are there any differences between:

SCOTT@ORCL> create index a_idx on a(x);

and

SYS@ORCL> create index scott.a.idx on scott.a(x);

Apart form all the DOs and DONTs about using SYS in this case is  that "if you connect as sysdba, your schema name will appear to be SYS".

Phil's answer demonstrates it. A nice quote by Thomas Kyte on a similar question -

  In general, unless the documentation tells you, you will NEVER LOG IN
  as sys or system, they are our internal data dictionary accounts and
  not for your use. You will be best served by forgetting they exist.

  sysdba and sysoper are ROLES - they are not users, not schemas. The
  SYSDBA role is like "root" on unix or "Administrator" on Windows. It
  sees all, can do all. Internally, if you connect as sysdba, your
  schema name will appear to be SYS.

  In real life, you hardly EVER need sysdba - typically only during an
  upgrade or patch.

We keep saying the following so many times -

  Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
  SYS/SYSDBA is special
  SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
  SYS/SYSDBA does not act like any other user
  When you use SYS/SYSDBA Oracle deactivates some code path and activates others
  Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

  NEVER EVER use SYS/SYSDBA for anything that can be done by another
  user.  Use SYS/SYSDBA ONLY for something that can't be done by someone
  else.

You might also like to read this.

I'll start my answer with this:

As a general rule: Never, ever, create objects as the user SYS/SYSTEM (or any other  "internal" Oracle user) unless directed to do so by Oracle support.

A simple test case shows that there's a big difference, due to ownership.

As SCOTT:

SQL> create table a ( b number );

Table created.

SQL> create index a_idx on scott.a ( b ) ;

Index created.

SQL> show user
USER is "SCOTT"
SQL> select owner from all_indexes where table_name = 'A';

OWNER
------------------------------
SCOTT

SQL> drop index a_idx;

Index dropped.

As SYS:

SQL> conn / as sysdba
Connected.
SQL> create index a_idx on scott.a ( b ) ;

Index created.

SQL> show user
USER is "SYS"
SQL> select owner from all_indexes where table_name = 'A';

OWNER
------------------------------
SYS

SQL>

This obviously causes problems due to ownership etc, as well as the data residing in a tablespace that you don't want user objects belonging in (from above example, because we didn't specify a TABLESPACE clause:

SQL> select owner, tablespace_name from all_indexes where table_name = 'A';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SYS                            SYSTEM

SQL>


SQLServer - treat table as index I have table that is more or less as follows:
table Sample (
    location bigint,
    device bigint,
    timestamp datetime,
    type bigint,
    value bigint,
    )

For performance reasons we also have one mighty index with same columns in identical order, except that value is included rather than used in index.
This works as expected, except that AFAIU data is kept twice in database - once in the table and once in an index.
Question: is there some way to, conceptually, keep data only in the index? I believe that would require keeping table organized in the same way index is organized, effectively removing need for the index. I tried to create composed primary key that would emulate aforementioned index, but it boiled down to automatic creation of identical index.

What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data.
A clustered index don't have the concept of included columns since all columns are in the clustered index - the clustered index being the data. So you have the key columns (what you use to "drive the query", like the WHERE clause, so to speak); and the rest of the columns are in the leaf.
You can think of the non-key columns in a clustered index just like included columns in a non-clustered index.
Don't confuse the PK with the clustered index. You decide whether the PK should come with a clustered index or a non-clustered index.
You might have a better candidate for the clustered index than the PK. Fine! Just make the PK non-clustered and create the clustered index on that better candidate.

Just use a clustered index on all the key columns in the table


sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor There are similar questions asked and answered in this network related to the error message given in the question, however this problem is a little different from each.

I have installed oracle 12c in my ubuntu can successfully connect to my hr user with sqlplus hr/hr@pdborcl through command prompt.

oracle@ubuntu:~$ sqlplus hr/hr@pdborcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 16 21:57:24 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Mon Nov 16 2015 21:24:32 +05:45
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>show user;
USER is "HR"

I am however trying to use sqldeveloper for ease. In the create connection option in sqldeveloper when I give the following credentials

I get the following error.

    ORA-12505. TNS: listener does not currently know of SID given in connect descriptor.

However connection with

Username: sys
Password: ******
SID:orcl

And same all other credentials work fine.

My tnsname.ora has

PDBORCL =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = pdborcl)
   )
 )

ORCL =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
  )
 )

You have to use Service Names to connect to Pluggable Databases in 12c.

It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.

You need to start the database to connect. Im using Express Edition 11g. I had faced the same problem. Problem disappears once you select Start Database option from Start>All Programs>Oracle 11g Express edition. Alternatively, you can go to the drive where you have installed oracle (in my case) D:/app/oracle/product/11.2.0/server/bin. Here you will find a Windows Batch File called StartDB. Double click on this and your database will start and your connections will test successfully.


SSIS Data Transfer vs. Linked Server I'm currently designing a SSIS package, which will do following:

Clear unwanted records in destination
Do ETL using stored procedures and put everything into a table prepared for export in source
Inserts data from source to destination

So the final step is pretty much to transfer whole table data from Source into Destination and I've got two options now:

SSIS Data Flow
Linked Server

Ideally I'd like to use Data Flow task in SSIS, but looking at performance comparison, it looks like Linked Server wins. I'm doing it that way:
In SSIS I do the following

Run a select statement using OLE DB Source
Insert into destination using OLE DB Destination
Takes about 4 seconds when deployed, about 10 seconds on my machine

Using Linked Server I do the following

Insert records into #Table querying remote server
Insert into actual table
Takes about 2-4 seconds

Here's query I'd use:
SELECT *
INTO #Table
FROM RemoteServer.MyDB.dbo.MyTable;

INSERT INTO dbo.MyTable
SELECT *
FROM #Table AS T;

I'm testing with relatively small data set, just about 14k rows (75 columns in total, most of them are FLOATs).
Both servers are under the same network.
I've configured connection managers in SSIS to use Microsoft OLE DB Provider for SQL Server with Packet Size of 32767 as suggested here:
https://gqbi.wordpress.com/2013/06/19/ssis-fastest-data-flow-task-item-for-transferring-data-over-the-network/
Am I doing something wrong, because I'd expect SSIS to run faster than Linked Server?
Is there another way to speed performance up in SSIS package and win against Linked Server?

hi Linked servers in general not best practice.  I generally recommend against them.  To many reasons to explain here....this would be come a white paper.  hahahah  I have been doing ETL and data security for 25 years.  please consult an architect and don't ask people on internet claiming to be architect.  there are very few people out there with my level of experience.  there is never one answer either...it depends on many factors how you approach this. AND testing small amount of records is not valid test.  do more intense testing.   best of luck!


Return the uniqueidentifier generated by a default on insert Goal
Retrieve the latest guid value in real time after you have inserted the value in the table
Problem
Don't know how to do it
Info

The code should only specify new values for address and zipcode
There can be lots of data in the table

Table
CREATE TABLE [AddressBook]
(
    [testID] [uniqueidentifier] NOT NULL default newid(),
    [address] [nvarchar](50) NULL,
    [zipcode] [nvarchar](50) NULL
)

I think you are looking for output

DECLARE @MyTableVar table([testID] [uniqueidentifier]);
 INSERT [AddressBook] ([address], [zipcode])
        OUTPUT INSERTED.[testID] INTO @MyTableVar
 VALUES (N'address', N'zipcode');

--Display the result set of the table variable.
 SELECT [testID] FROM @MyTableVar;

GO

uniqueidentifier may not be the most efficient id here but this is an answer to the stated question

Another way,

DECLARE @id varchar(50) = CONVERT(VARCHAR(50), NEWID());

INSERT INTO [yourtable]
( [id])
VALUES
(@id);

SELECT @id;


Need to group by column while ignoring some text format I have the following data.

Sport
--------------
Baseball
Football
Baseball [a]
Football [a] [b]
Basketball

How can I group this to ignore the data in the brackets?

The expression

regexp_replace(the_column, ' +.*$', '')

will remove everything starting with the first space character up until the end of the string. So you just need to group by that expression:

with test_data (sport) as
(
  values
    ('Baseball'),
    ('Football'),
    ('Baseball [a]'),
    ('Football [a] [b]'),
    ('Basketball')
)
select regexp_replace(sport, ' .*$', ''), count(*)
from test_data
group by regexp_replace(sport, ' .*$', '')

This will return:

regexp_replace | count
---------------+------
Baseball       |     2
Basketball     |     1
Football       |     2

SQLFiddle: http://sqlfiddle.com/#!15/d41d8/3379

Edit

To deal with legitimate multi-word sports there are two options:

remove everything after the first [: regexp_replace(sport, ' \[.*$', '')
remove all characters that occur between [..] and then trim the result: trim(regexp_replace(sport, '\[.*\]', '', 'g'))

USE tempdb
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP    TABLE #temp
GO
SELECT  Sport
INTO    #temp
FROM    (
            SELECT 'Baseball' Sport
            UNION ALL
            SELECT 'Football'
            UNION ALL
            SELECT 'Baseball [a]'
            UNION ALL
            SELECT 'Football [a] [b]'
            UNION ALL
            SELECT 'Basketball'
            )a

--Using CTE To Strip SpecialText
;WITH AvoidCertainText
AS
(
    SELECT  Sport
            , CASE
                WHEN CHARINDEX('[',Sport) <> 0 THEN RTRIM(LTRIM(LEFT(Sport,CHARINDEX('[',Sport)-1)))
                ELSE Sport
            END FormatedSport
    FROM    #temp
)
SELECT   FormatedSport Sport
        , COUNT(FormatedSport)SportCount
FROM    AvoidCertainText
GROUP BY FormatedSport


How does a database conciliate data to files on distributed filesystems? The question title might be a little misleading, but I was wondering what happens internally in a database implementation when the file used for storage are placed on a distributed filesystem (like Gluster or Ceph).

I would imagine that the database implementation keeps some data in memory and will conciliate to disk periodically. Does the database instance assume that it is the only one writing to the storage file and use this for optimizations somehow? Can someone elaborate and suggest some good materials on this topic?

My previous understanding was that this wouldn't work, but after a quick search on Google it seems that not only it works but it is fairly common (I'm not talking about explicit replication features).

In simple terms, the DBMS doesn't care (or in some cases even know) if the file system where its files are located is distributed or not. In a high availability scenario with shared (distributed) storage an active-passive setup is most commonly used, where only one instance of the DBMS at a time is running and has access to the database files. It's the responsibility of the cluster manager (software or human) to control DBMS instance startup and failover.

In a shared data cluster, such as Oracle RAC or Db2 pureScale, there are multiple instances of the DBMS running concurrently and accessing the same set of database files, and a special component of the cluster manages concurrent access to the files by the DBMS processes.

That "special component" is usually tightly integrated with the DBMS itself but the actual implementations differ wildly. Oracle Clusterware plays this role in an Oracle RAC; Cluster Cache Facility in Db2 pureScale; both are tightly integrated with their respective underlying shared file systems.

I'm not aware of any open source shared-data cluster databases; they all tend to choose shared-nothing architectures, which are somewhat simpler to implement and more tolerant towards low-end hardware.

but I was wondering what happens internally in a database implementation when the
  file used for storage are placed on a distributed filesystem (like Gluster or
  Ceph).

The db thinks you are not the smartest. Performance goes down the drain. Nothing gained.

  Does the database instance assume that it is the only one writing to the
  storage file and use this for optimizations somehow?

Not somewhow. Every server system I know assumes, hard, it is the only one writing on the file.

  Can someone elaborate and suggest some good materials on this topic?

RTFM. There probably also are books on database server design CS students are supposed to learn ;)

  My previous understanding was that this wouldn't work, but after a quick
  search on Google it seems that not only it works but it is fairly
  common

20 years working with databases (not Postgres, oracle and sql server mostly) i have not ONCE seeing a distributed file system used as storage in the real world. I would say that is how common this is. Not sure what you looked up in google. But then maybe I deal more in the high performance sector where "network to storage" - unless a high speed SAN, and even then - would make people laugh. Even 10 gigbabit is only 1 gigabyte per second, which is quite low for IO for a dedicated high end server.