How to keep history of SQL Server stored procedure revisions Note: I am not asking about full version control.

Is there any way automatically to keep a history of stored procedures on SQL Server.

Similar to how Google Docs automatically keeps a history of versions of documents and Wikipedia automatically keeps a history of versions of articles.

I don't want users updating stored procedures to have also to maintain a repository of stored procedures. This is too much work and people won't do it.

Hopefully this is something I can turn on in SQL Server...

(And by stored procedures really I mean functions, triggers, etc. Basically everything under Programmability.)

I posted to https://stackoverflow.com/questions/14522224/how-to-keep-history-of-sql-server-stored-procedure-revisions first cos I suspect it would get more views there.

While I am in full agreement that source control is the right way to do this, I also understand that not all environments are disciplined enough to rely on that alone (if at all), and that sometimes changes have to made directly to keep the app running, save a client, what have you.

You can use a DDL trigger to keep all revisions in a table in a separate database (and of course back up that database frequently). Assuming you have a utility database:

USE Utility;
GO

CREATE TABLE dbo.ProcedureChanges
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(100),
    EventDDL     NVARCHAR(MAX),
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     NVARCHAR(255),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Now in your database, first let's grab what we'll call "initial control" - the current version of the stored procedures:

USE YourDB;
GO

INSERT Utility.dbo.ProcedureChanges
(
    EventType,
    EventDDL,
    DatabaseName,
    SchemaName,
    ObjectName
)
SELECT
    N'Initial control',
    OBJECT_DEFINITION([object_id]),
    DB_NAME(),
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
FROM
    sys.procedures;

Now to capture subsequent changes, add a DDL trigger to the database:

USE YourDB;
GO

CREATE TRIGGER CaptureStoredProcedureChanges
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);

    SELECT @ip = client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

    INSERT Utility.dbo.ProcedureChanges
    (
        EventType,
        EventDDL,
        SchemaName,
        ObjectName,
        DatabaseName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO

Over time it will become easy to see and compare changes to procedures, watch new procedures get added to the system, see procedures get dropped, and have a good idea of who to talk to about any of these events.

More information here:

http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/


The fastest way to get the most recent records I am looking for the fastest way to return the most recent record for each reference number.

I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition (SequenceId). It appears to work only when I specify the Id and the creation date.

To understand my problem, You will need to create the modified sample table, which is essentially a copy of the table on the above referenced website but with a little twist.

CREATE TABLE [dbo].[TestTable](
      [Id] [int] NOT NULL,
      [EffectiveDate] [date] NOT NULL,
      [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
      [CustomerId] [varchar](50) NOT NULL,
      [AccountNo] [varchar](50) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
      [Id] ASC,
      [EffectiveDate] ASC,
      [SequenceId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[TestTable] ON
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (1, CAST(0xDF300B00 AS Date), 1, N'Blue', N'Green')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (2, CAST(0xDF300B00 AS Date), 2, N'Yellow', N'Blue')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (1, CAST(0xE0300B00 AS Date), 3, N'Red', N'Yellow')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (3, CAST(0xE0300B00 AS Date), 4, N'Green', N'Purple')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (1, CAST(0xE1300B00 AS Date), 5, N'Orange', N'Purple')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (2, CAST(0xE3300B00 AS Date), 6, N'Blue', N'Orange')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (3, CAST(0xE6300B00 AS Date), 7, N'Red', N'Blue')
SET IDENTITY_INSERT [dbo].[TestTable] OFF
GO

If I run a similar query as on the website, I get exactly the same result.

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
WHERE ttNewer.id IS NULL

However, the little twist is that I added a SequenceId column to the table as you may have noticed. The purpose of this column is because the client may want to do a post dated entry for a date in the past. This entry must supercede the other entries made on the same date which is in the past. If I run the query before I add the the post dated entries, I get the same result as previously.

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON    (
            tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
            AND tt.SequenceId < ttNewer.SequenceId
      )
WHERE ttNewer.Id IS NULL

If I add two post dated entries as shown below, then I start to get interesting results.

INSERT INTO TestTable(Id,EffectiveDate,CustomerId,AccountNo) values
(
      2,'20090103','Blue','Orange'
);

INSERT INTO TestTable(Id,EffectiveDate,CustomerId,AccountNo) values
(
      2,'20090105','Blue','Orange'
);

What you should notice is that the two queries below no longer return the last record whether I use the one which is similar to what is on your website or I use the one which adds another condition (SequenceId)

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
WHERE ttNewer.id IS NULL

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON    (
            tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
            AND tt.SequenceId < ttNewer.SequenceId
      )
WHERE ttNewer.Id IS NULL

What I would like the query to do is to return the last record for a reference number (Id) based on the last sequence number for any given day. In other words, the record with the last sequence number on the most recent EffectiveDate.

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag):

;WITH cte AS
(
  SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo,
    rn = ROW_NUMBER() OVER (PARTITION BY Id
      ORDER BY EffectiveDate DESC, SequenceId DESC)
  FROM dbo.TestTable
)
SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo
  FROM cte
  WHERE rn = 1
  ORDER BY Id; -- because you can't rely on sorting without ORDER BY

This still has to scan, but it only has to scan once, compared to all the self-join variants, which will always have two scans (or potentially a scan and a seek executed multiple times, with better indexes).

If you want a more efficient query (eliminating an expensive sort, at the potential cost of writes, and perhaps other queries that don't need to support this sorting), change the primary key to match the query pattern:

PRIMARY KEY CLUSTERED
(
  [Id] ASC,
  [EffectiveDate] DESC,
  [SequenceId] DESC
)

The direction of the secondary columns has no effect on uniqueness, and should change writes minimally as long as the table isn't terribly wide and there aren't an extremely large number of rows per id.


RMAN catalog database I'm about to create a RMAN catalog database for my setup, what are the general precautions or major pre-requisites as in DB parameters, compatibility,etc that I should take care while creating the catalog database?

1 - Make sure Recovery Catalog DB is created either on a different server or a different disk (because in case of failure of target DB and lost of access to its files, if your recovery catalog db stills up and running, you'll have access to control file and probably be able to restore and recover failed instances, given all backups/ archivelogs are available.

2 - Follow the Recovery Catalog 3 step creation:
- Create the recovery catalog owner/schema, tablespace. (It's noteworthy that sys can't be the rec cat owner)
- create the recovery catalog
- register the target databases

3 - Regarding compatibility, in order to check recover catalog version, connect to the recovery catalog db through sqlplus and issue:
- SELECT * FROM rcver; (if there are multiple lines displayed, the highest will be the current catalog schema version)

4 - Configure control file autobackup to ON (this is always a best practice, with or without Reco Catalog)
- Don't use another reco catalog, or things can get really messed.
- Run reco catalog db in archivelog, because it will contain very important information about registered db, and you'll sure want recover if needed.

5 - Backup your Recovery Catalog and set proper retention policies to meet your requirements.

Hope it helps.


Oracle sql plan for a select with procedure in terms section Is there any difference in optimizing a plan to execute any sql request in case of using a procedure from a package which checks to display row or not and in case of constructing a plain sql query without procedures ?

I mean, should be any difference in sql plans ?
And how it might affect the performance of execution.

for instance

select * from atbl t where checkme(t.fld) = 1;

or

select * from atbl t where t.fld = 'OK'

Thank you in advance.

Yes, there can be changes in the plan, and they can be quite drastic in terms of performance.

To be sure in your specific case, there's only one way to find out: look at the plans and check (i.e. measure with real data and up-to-date statistics) the performance.

The potential problem with your query with the function is that it will prevent the optimizer from using an index on atbl.fld if there is one – this can result in drastic performance differences.
If there isn't an index, you'll probably get the same plan.

(And if  checkme is deterministic you can create a function-based index to optimize that query if necessary.)


How to better manage hard coded cross db references in stored procs? I wanted to make a copy of a db and give it a different name, and have our application connect to this new copy of the db. The only problem is that other databases our application references have stored procs with hard coded references to the original db name. So even though I can make the application connect to the copy, any other databases it connects to will still connect to the original.

I have to believe this is probably a common problem that's been solved, but I'm wondering whether the solution is something I can still do on top of the implementation we have or if it's something that needed to be designed differently to begin with... and if so what is the "proper" design to handle this issue?

Referencing the last last paragraph in your question

  I have to believe this is probably a common problem that's been
  solved, but I'm wondering whether the solution is something I can
  still do on top of the implementation we have or if it's something
  that needed to be designed differently to begin with... and if so what
  is the "proper" design to handle this issue?

Yes, I have seen many posts and blogs about this issue and the recommended approach that I have seen is to use SYNONYMS.

A synonym is like an alias.  It provides a way to reference an object like a table, view or stored procedure, but the challenging part is that a SYNONYM can only be defined as a two-part name (schema.object).  This limitation would require you to make code changes to your existing objects that reference 3-part names (Database.Schema.Object).

Here is an example of a synonym from the documentation.  The following example first creates a synonym for the base object, Product in the AdventureWorks2012 database, and then queries the synonym.

-- Create a synonym for the Product table in AdventureWorks2012.
CREATE SYNONYM MyProduct
FOR AdventureWorks2012.Production.Product;
GO

-- Query the Product table by using the synonym.
SELECT ProductID, Name
FROM MyProduct
WHERE ProductID < 5;
GO

--You can also create a `SYNONYM` for a remote object.
CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee;

In your particular situation, let's assume you have references to db1.dbo.Customers.  You could create a synonym called Db1Dbo.Customers that points to db1.dbo.Customers.  Any objects that currently reference db1.dbo.Customers would need to be changed to Db1Dbo.Customers.

Now, you want to make a copy of database db1 and call it db1copy.  After creating the db1copy database, you simply drop the existing synonym in database db1copy for DB1Dbo.Customers and recreate it to point to db1copy.dbo.Customers.

There are some 'gotchas' when using SYNONYMS though.  Take a look at Aaron Bertrand's post - #BackToBasics : The Beauty of the Synonym.  As he says, 'But it's not all roses'.  Among the 'gotchas' that Aaron discusses are:

They introduce a layer of abstraction.
IntelliSense won't always work in certain versions.
You can't ignore performance.
You can't alter a synonym.
You can only point to certain entity types.
Permissions can get messy.


T-SQL Check another table rows if match delete them I am creating a script in SSIS where I want table A to check if rows exist in table B. If they exist, they must be deleted.

Table A is an archive table named Customer_archive
Table B is a table that will get updated frequently and is named Customer_Information.

The idea is that once a row is stored in Customer_archive, that row will not appear in Customer_information, even after Customer_information has been updated.

It has to check the entire row and not WHERE = 'something'.

Is it possible to do a WHERE clause that is a combination of 2 columns?

I can't say anything about SSIS but in SQL you can check whether a whole row is identical to another (including checking for NULL values which can get rather complicated otherwise), using this technique, explained by @PaulWhite in his blog post: Undocumented Query Plans: Equality Comparisons .

For example in you case. "For any identical rows of table B and table A, delete those B rows":

DELETE b                        -- from table B: Customer_information
FROM Customer_archive AS a
  JOIN Customer_information AS b
  ON a.pk = b.pk
WHERE EXISTS (SELECT a.* INTERSECT SELECT b.*) ;

I have serious concerns though about efficiency when the tables are big - and an archive table by definition is going to be quite big. The a.pk = b.pk is not needed really, as the pk columns are obviously included in the row check of the EXISTS but I kept them for efficiency. Assuming that the two tables have the same primary key and that after a period, most of the archived rows have been already deleted from table B, the PK indexes will have very few matching values so the join will be relatively fast - and using the row checks only for matching pk values.


Audit trigger 91plus: setting up with proper role? One of the solutions available for auditing in PostgreSQL is Audit trigger 91plus.

wiki page
github page

There is a section in the wiki that is confusing to me:

  Changes by the table owner and superusers are tracked, but can be trivially tampered with.

Ok... I am not sure if this means someone with access rights to the database via console or GUI or if this means something else...

  If you want this audit log to be trustworthy, your app should run with a role that has at most USAGE to the audit schema and SELECT rights to audit.logged_actions. Most importantly, your app must not connect with a superuser role and must not own the tables it uses. Create your app's schema with a different user to the one your app runs as, and GRANT your app the minimum rights it needs.

Apologies for my lack of understanding: I don't understand the picture here. If I have a web app using a PostgreSQL db, what exactly are the dangers if I use Audit trigger 91plus without the different role the wiki talks about?

If I own the table (the owner is the user that created the table) or am a superuser, I can modify and drop triggers on a table.

This makes it trivial to tamper with trigger based auditing.

For good security, it is a good idea to use an application user different from the object owner, and of course not a superuser.


ANSI_Padding setting Recently I started looking at SQL Server ANSI_Padding setting. After reading BOL and some online articles, I find myself with more questions and confusion than I started with.

I understand that I would be better off sticking to ANSI_Padding On, and I understand the different behaviors with NULL and NOT NULL columns when the setting is changed to ON or OFF.

But I cannot grasp this concept. What purpose does padding serve? Doesn't appending zeros to a binary number (or spaces to a character string) change the value?

Binary number 0x0000ee is equal to 0x00ee (at least when it is converted to decimal) But how is 0x00ee00 the same as 0x00ee?

A string 'a ' (1 space) is not the same as 'a' (no space), and yet they both become 'a   ' (3 spaces) in a char(3) column. How is that even acceptable?

Same confusion also exists when values are trimmed.

BOL does seem to mention that ANSI_Padding is used to control how values are stored. So my initial guess was the actual values are not changed. But BOL does not elaborate on this point, nor do any of the articles I have found so far.

Thanks for your help.

For a varbinary column created with ANSI padding set OFF, values containing trailing zeros, such as 0x100 will have the trailing zeros truncated prior to the value being written to the column.  This certainly does change the value contained in the column.

This might be useful for a system which displays values in a left-aligned fashion with right-padding zeros added at display-time, thereby reducing storage requirements.  For instance, if your system knows all numbers stored in a column are 8 hex digits, left aligned, then 0x1 becomes 0x01000000 at run-time.  In my opinion, the confusion this would create for future developers would far outweigh the potential space savings.

Take for example, the following:

USE tempdb;

SET ANSI_PADDING ON;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100);

SELECT *
FROM dbo.PadTest;

Result:

╔════════╗
║   v    ║
╠════════╣
║ 0x0100 ║
║ 0x0001 ║
╚════════╝

SET ANSI_PADDING OFF;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100)
    , (0x001);

SELECT *
FROM dbo.PadTest;

Result:

╔════════╗
║   v    ║
╠════════╣
║ 0x01   ║
║ 0x0001 ║
╚════════╝

The first table defined with ANSI padding enabled requires more storage for the first row than the second table defined with ANSI padding disabled.  As you can see, this does indeed change the values stored in the table since 0x01 <> 0x0100.

SET ANSI_PADDING OFF; for varchar columns operates in a similar way, replacing the zeros with spaces.  Take the following example:

SET ANSI_PADDING OFF;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt

╔═════════════╦══════════════╗
║ varchar(10) ║ char(10)     ║
╠═════════════╬══════════════╣
║ a|          ║ a          | ║
║ a|          ║ a          | ║
╚═════════════╩══════════════╝

I've added the pipe symbol to the output to show where padding exists and doesn't exist.

SET ANSI_PADDING ON;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt

╔═════════════╦══════════════╗
║ varchar(10) ║ char(10)     ║
╠═════════════╬══════════════╣
║ a|          ║ a          | ║
║ a         | ║ a          | ║
╚═════════════╩══════════════╝

The char(10) columns operate "correctly" for both variations of the SET ANSI_PADDING statement - the varchar(10) columns are a bit problematic with SET ANSI_PADDING OFF; since it's removing the spaces from the value.  This is why in Microsoft's ANSI_PADDING documentation, it states:

  We recommend that ANSI_PADDING always be set to ON.


How to indicate what will be measured with a NONCLUSTERED COLUMNSTORE INDEX? I am new to columnstore INDEXes.  But I am fairly sure I need one now.
Here is my setup.  I have a table that has the columns ApplicationId, GroupId and TimeInMilliseconds.
I am going to want to run queries like what is the average time for a given ApplicationId or what is the total time for a given ApplicationId and GroupId.
My column store INDEX looks like this:
 Create NONCLUSTERED COLUMNSTORE INDEX CsIdx_PerformanceData
 ON perf.PerformanceData
 (TimeInMilliseconds, ApplicationId, PerformanceGroupId, PerformanceSubGroupId)

But I am confused on what this is saying.  It seems there is no distinction between dimensions and measures here.
Meaning, it seems like I could really be wanting to get the SUM of ApplicationIds when TimeInMilliseconds is 200 (which makes no sense at all).
Question
How do I set this up so that I can do the math on TimeInMilliseconds using the other columns?  (Or am I confusing the issue here?  Do I need a cube for what I am thinking?)

It is not necessary to distinguish between measures and dimensions when defining your columnstore. All columns are stored in the same manner. All columns included in any query should be in the columnstore, in most cases it is best to put every column in the table into the columnstore.


SQL Agent Jobs hanging at last Run date and time For maintenance tasks on my LogShipped environment I run T-SQL script to disable the copy/restore agent job for the duration of maintenance and then enable when done.
When I enable the jobs via TSQL it seems the schedule hangs (at last run date) and does not execute future schedules and LS DBs fall out of sync. Restarting the SQL Agent & manual start of the job resolves the issues ok.

SQL Server 2016 SP1.
Using msdb..sysjobs to disable/enable the job and
msdb..sysjobschedule to disable/enable the schedule

Anyone any ideas?

Are you using this correct way for disable jobs?

  USE msdb ;
  GO
  EXEC dbo.sp_update_job
      @job_name = N'NameOgYourJob',
      @enabled = 0 ;
  GO

You don't have to update with update statement, it's describe HERE and HERE


Inheritance of "dictionary tables" (In case this matters: I'm using SQLite)

My silly little database has a surprising number of tables with identical structure:

CREATE TABLE GTag (
    ID            INTEGER PRIMARY KEY NOT NULL,
    Name          TEXT    UNIQUE      NOT NULL,
    Description   TEXT
);

Back here (Poland) they're known as "dictionary tables" ("tabele słownikowe"); I couldn't find the proper English equivalent. Their role is to provide a limited number of pre-determined choices, usually in drop-down boxes and the like. Storing tags seems like the sort of thing they're designed to handle.

As I'm finalizing the structure of my database, it struck me that all the tags and other tag-like entities (like "GStatus", "SStatus"...) have these exact 3 fields in common, yet are not connected to one another in any way. Should I make them all sub-types of a more general "Tag" table?

I see 3 solutions to my predicament:

Create a super-type with ID, Name, Description. All current tables become EMPTY save for primary ID keys that correspond to Key.IDs (i.e: are foreign keys). If I want to lookup all the GTags, I get all IDs from GTag, and then get data from corresponding Tag entries. The sub-types, however, bring completely nothing new to the picture. They don't have any fields that would set them apart from one another or even the parent type.
Dump everything into one big Tag super-table; add Type field. Since I NEVER want tags from more than on category at the same time, all queries will have to filter via the Type field. Seems clunky like hell, and a huge headache of a rewrite.
Leave things well enough alone. I avoid creating useless entities that have all structure but no meaning in common.

I get the impression that trying to "tidy up" my schema will ultimately result in tangling it up into a confusing mess, but I don't have the experience to say that with conviction. What am I missing here? What possible benefits (and drawbacks) does each solution offer (in terms of performance, neatness, following design principles...), and which should I ultimately implement?

Using one supertable Tag will give you less tables in your schema, but you will basically lose guarantees about referential integrity. If one column in another table must have one 'colour' value, you add a referential constraint to the table 'colours' (one of your dictionaries). If you have a big one, you can have one column that should have one colour to have a value 'wide', because this value is also in your dictionary, even if it is not a colour.

With one super-table, you move the responsibility of guaranteeing referential integrity from the database to your application (or middleware). If you do so, you should make sure there is no other way to change values in your database than through your application.

I don't see any advantage on having an inheritance hierarchy. Even if your structures look all the same, their meanings are not at all the same. You could gain some operational advantage somewhere (like having only one procedure to do the 'maintenance' of the Tag table), but I guess you could do the same using just table names as parameters to a function call or method somewhere in your application.

I'd not really care much about performance. Having a number of small 'dictionary' tables (lookup tables) won't harm you.


Generate & Email report from Oracle Forms 11g I generate a report in PDF through passing of parameters from Oracle Forms 11g. I have a requirement to email the same report automatically as well. Following is the part of forms where I have passed on the parameters.

pl_id := Create_Parameter_List('tmpdata1');
add_parameter(pl_id, 'FDT', TEXT_PARAMETER,  :a);
add_parameter(pl_id, 'TDT', TEXT_PARAMETER,  :b);
add_parameter(pl_id, 'VC', TEXT_PARAMETER, :e);
add_parameter(pl_id, 'SYS_DATE', TEXT_PARAMETER, :f);
ADD_PARAMETER(pl_id,'DESTYPE',TEXT_PARAMETER,'FILE');
ADD_PARAMETER(pl_id,'DESFORMAT',TEXT_PARAMETER,'PDF');
ADD_PARAMETER(pl_id,'DESNAME',TEXT_PARAMETER,'C:\Downloads\abc.pdf');

Now a copy of report is saved in C:\Downloads folder as well as its shown in browser.

I use the following procedure to send current data shown in Forms via emails.

create or replace procedure send4 (p_sender IN VARCHAR2, p_recipient IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2)
IS
crlf         VARCHAR2(2)  := chr(13)||chr(10);
l_mailhost VARCHAR2(255) := <IP ADDRESS>;
v_connection             UTL_SMTP.connection;

BEGIN
V_CONNECTION := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.Helo(V_CONNECTION, l_mailhost);
utl_smtp.Mail(V_CONNECTION, p_sender);
utl_smtp.Rcpt(V_CONNECTION, p_recipient);
utl_smtp.Data(V_CONNECTION,
'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: '   || p_sender || crlf ||
'Subject: '|| p_subject || crlf ||
'To: '     || p_recipient || crlf ||
'MIME-Version: 1.0'|| crlf ||   -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
p_message|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="file.txt"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="attachment.txt"'|| crlf ||
crlf ||
p_message|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--'         -- End MIME mail
);
UTL_SMTP.quit(v_connection);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail', TRUE);
END;

Can I email the Report generated directly? I tried passing the parameter 'MAIL' and 'Email address' in destype and desname but I am guessing there is some settings need to be done?

OR

How can I use the email procedure to send the said report as attachment?

OR

How can I copy the report pdf generated in user's PC to a specific directory at server? Because I have one separate procedure for email where I can mail PDFs from server directly. User has Windows PC while server is LINUX.

OR

Generate report in PDF format at server directly so I could use the procedure (#3 point) to email it?

I am posting this here in Database Administrators because I believe there needs to be a database level configuration done (developers have not been able to help). Sorry if its a wrong forum to post.

You can download a file from filesystem like this:

FUNCTION GetFile(FolderName IN VARCHAR2, FileName IN VARCHAR2) RETURN BLOB IS

    xbfile   BFILE;
    xblob    BLOB;
    destOffset INTEGER := 1;
    srcOffset INTEGER := 1;

BEGIN
    xbfile := BFILENAME (FolderName, FileName);
    DBMS_LOB.OPEN (xbfile);
    DBMS_LOB.CREATETEMPORARY(xblob, TRUE, DBMS_LOB.SESSION);
    DBMS_LOB.LOADBLOBFROMFILE(
        dest_lob => xblob,
        src_bfile => xbfile,
        amount => DBMS_LOB.GETLENGTH(xbfile),
        dest_offset => destOffset,
        src_offset => srcOffset);

    DBMS_LOB.CLOSE(xbfile);

    RETURN xblob;

END GetFile;

In order to access filesystem you must create a DIRECTORY (if not already exists):

CREATE OR REPLACE DIRECTORY PDF_REPORTS AS 'C:\Downloads';

In my application I use this generic package to send mails:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
/

CREATE OR REPLACE PACKAGE Mailing AS

PRIORITY_HIGH           CONSTANT INTEGER := 1;
PRIORITY_NORMAL         CONSTANT INTEGER := 3;
PRIORITY_LOW            CONSTANT INTEGER := 5;

MIME_MIXED              CONSTANT VARCHAR2(50) := 'multipart/mixed;';
MIME_TEXT               CONSTANT VARCHAR2(50) := 'text/plain;';
MIME_HTML               CONSTANT VARCHAR2(50) := 'text/html;';

PROCEDURE SendMail(
    Subject IN VARCHAR2,
    Message IN OUT CLOB,
    ToMail IN VARCHAR_TABLE_TYPE,
    FromMail IN VARCHAR2, FromName IN VARCHAR2,
    PRIORITY IN INTEGER DEFAULT PRIORITY_NORMAL,
    FileName IN VARCHAR2 DEFAULT NULL,
    MimeType IN VARCHAR2 DEFAULT MIME_TEXT, --> determines the MIME-Type of binary attachment "BinAttachment"
    TxtAttachment IN CLOB DEFAULT NULL,
    BinAttachment IN BLOB DEFAULT NULL);

END Mailing;
/

CREATE OR REPLACE PACKAGE BODY Mailing AS

PROCEDURE SendMail(
    Subject IN VARCHAR2,
    Message IN OUT CLOB,
    ToMail IN VARCHAR_TABLE_TYPE,
    FromMail IN VARCHAR2, FromName IN VARCHAR2,
    PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL,
    FileName IN VARCHAR2 DEFAULT NULL,
    MimeType IN VARCHAR2 DEFAULT MIME_TEXT,
    TxtAttachment IN CLOB DEFAULT NULL,
    BinAttachment IN BLOB DEFAULT NULL) IS

    MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';

    con UTL_SMTP.CONNECTION;
    ret UTL_SMTP.REPLY;
    Charset VARCHAR2(20);
    Footer VARCHAR2(1000);
    Recipients VARCHAR2(1000);

    LobLen PLS_INTEGER;
    amount PLS_INTEGER := 8192;
    BUFFER VARCHAR2(16384);
    OFFSET PLS_INTEGER := 1;
    isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), '(< *html)|(< *body)', 'i');

BEGIN

    SELECT UTL_I18N.MAP_CHARSET(VALUE)
    INTO Charset
    FROM NLS_DATABASE_PARAMETERS
    WHERE parameter = 'NLS_CHARACTERSET';

    -- setup mail header
    con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
    ret := UTL_SMTP.MAIL(con, FromMail);
    FOR i IN ToMail.FIRST..ToMail.LAST LOOP
        Recipients := Recipients ||ToMail(i)||',';
        ret := UTL_SMTP.RCPT(con, ToMail(i));
    END LOOP;
    ret := UTL_SMTP.OPEN_DATA(con);

    IF REGEXP_LIKE(FromName, '^[ -~]+$') THEN
        UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'From: =?UTF-8?B?'|| UTL_ENCODE.TEXT_ENCODE(FromName, NULL, UTL_ENCODE.BASE64) ||'?= <'||FromMail||'>'||UTL_TCP.CRLF);
    END IF;
    UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
    IF REGEXP_LIKE(Subject, '^[ -~]+$') THEN
        UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'Subject: =?UTF-8?B?'|| REPLACE(REPLACE(UTL_ENCODE.TEXT_ENCODE(Subject, NULL, UTL_ENCODE.BASE64), CHR(13), NULL), CHR(10), NULL) ||'?='||UTL_TCP.CRLF);
    END IF;
    UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);

    IF FileName IS NOT NULL THEN
        UTL_SMTP.WRITE_DATA(con, 'Mime-Version: 1.0' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MIME_MIXED||' boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'This is a multipart message in MIME format.' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
    END IF;

    -- Append common footer to mail
    Footer := 'Message from '||SYS_CONTEXT('USERENV', 'DB_NAME')||' sent at '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
    IF isHTML THEN
        UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_HTML||' charset='||Charset || UTL_TCP.CRLF);
        Message := REPLACE(message, '', '
'||Footer||'
');
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'Content-type: '||MIME_TEXT||' charset='||Charset || UTL_TCP.CRLF);
    END IF;

    -- Mail Body
    UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    LobLen := DBMS_LOB.GETLENGTH(Message);
    LOOP
        EXIT WHEN OFFSET > LobLen;
        DBMS_LOB.READ(Message, amount, OFFSET, BUFFER);
        UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
        OFFSET := OFFSET + amount;
    END LOOP;
    UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    IF NOT isHTML THEN
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, Footer);
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    END IF;

    IF FileName IS NOT NULL THEN
        -- Mail Attachment
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MimeType||' name="'||Filename||'"'|| UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: attachment; filename="'||Filename||'"'|| UTL_TCP.CRLF);

        OFFSET := 1;
        IF TxtAttachment IS NOT NULL THEN
            UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
            LobLen := DBMS_LOB.GETLENGTH(TxtAttachment);
            LOOP
                EXIT WHEN OFFSET > LobLen;
                DBMS_LOB.READ(TxtAttachment, amount, OFFSET, BUFFER);
                UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
                OFFSET := OFFSET + amount;
            END LOOP;
        ELSIF BinAttachment IS NOT NULL THEN
            amount := 48; -- must be a whole multiple of 3
            UTL_SMTP.write_data(con, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
            UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
            LobLen := DBMS_LOB.GETLENGTH(BinAttachment);
            LOOP
                EXIT WHEN OFFSET > LobLen;
                DBMS_LOB.READ(BinAttachment, amount, OFFSET, BUFFER);
                UTL_SMTP.WRITE_RAW_DATA(con, UTL_ENCODE.BASE64_ENCODE(BUFFER));
                OFFSET := OFFSET + amount;
            END LOOP;
        END IF;
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);
    END IF;

    -- finish mail
    ret := UTL_SMTP.CLOSE_DATA(con);
    ret := UTL_SMTP.QUIT(con);

EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
        UTL_SMTP.QUIT(con);
END SendMail;

END Mailing;
/

It provides some extras:

You can optionally attach a file (txt or binary, but only one file is supported)
From and Subject may contain Non-ASCII characters like öäü (of course the mail Body may also)
Multiple recipients
Automatically use correct character set from database
Automatically detects plain-text or HTML mail body
Common footer to indicate mail

Then you can call the function like

DECLARE
   pdf BLOB;
   Message CLOB := 'Some message text';
BEGIN
   pdf := GetFile('PDF_REPORTS','abc.pdf');

Mailing.SendMail(
    Subject => 'The Subject',
    Message => Message,
    ToMail => VARCHAR_TABLE_TYPE('name@domain.net'),
    FromMail => 'oracle@domain.net',
    FromName => 'DB User: '||USER,
    FileName => 'abc.pdf',
    BinAttachment => pdf,
    MimeType => 'application/pdf' );

END;


Query Linked Server Logins I'd like to produce a listing of linked servers and their mapped logins.  Anyone know how?  sys.sysservers will show servers, but no login information is presented.

This query will give you a listing of all the linked servers and their logins:

-- linked servers and their logins
select s.name, p.principal_id, l.remote_name
from sys.servers s
    join sys.linked_logins l
        on s.server_id = l.server_id
    left join sys.server_principals p
        on l.local_principal_id = p.principal_id
where s.is_linked = 1
go

Add more columns if you need more information. Here are the base DMVs:

-- list the linked servers
select *
from sys.servers
where is_linked = 1
go

-- linked server logins
select *
from sys.linked_logins
go

-- local logins
select *
from sys.server_principals
go


Query to get all the columns in a view I've got this little ditty to concatenate all the columns in a table, for writing scripts quickly:

    create function [dbo].[get_columns] (@table_name varchar(255))
    returns varchar(max)

    as
    begin
    declare @name varchar(255),
    @object_id varchar(255),
    @string varchar(max)

    set @object_id = (select object_id from sys.objects with (nolock) where name = @table_name and type = 'u')

    set @string = (select stuff( (
    select ', ' + name   from sys.columns with (nolock)
    where object_id = @object_id
    and name != 'row_timestamp'
    for xml path ('')),1,2,''))

    return @string
    end

Question: how would you re-write this to get the columns of a view?

Why have one function for tables and a separate function for views? You can handle both just by removing the restrictive filter on type = 'u' (and actually you don't need sys.objects at all). Here is a rewrite with some other minor enhancements:

CREATE FUNCTION dbo.get_columns
(
  @object_name nvarchar(513)
  -- needs to support Unicode!
  -- should pass schema.object
)
RETURNS nvarchar(max) -- can contain Unicode!
AS
BEGIN -- we don't need any variables
  RETURN
  (
    SELECT STUFF((SELECT N', ' + name
      FROM sys.columns WITH (NOLOCK)
      WHERE [object_id] = OBJECT_ID(@object_name)
      AND name <> N'row_timestamp'
      FOR XML PATH(''), TYPE).value(N'.[1]',N'nvarchar(max)'),1,2,'')
  );
END


Problem Installing PostgreSQL 12.1 Win8.1: "the database cluster initialisation failed" I'm getting an error at the end of installing PostgreSQL 12.1 which is "the database cluster initialisation failed"

I have been searching all the day, so here what I tried:

1- Run the installation file as Administrator: didn't work.

2- Change the directory of "Data" to different place other than C:: didn't work.

3- Changed the whole installation directory to C:\Postgre or F:.... : didn't work

4- Turned off the KasperSky antivires: didn't work

5- Checked if there is an old Postgre user: not found

I checked the log file and I found the following :

  Script output:
   WScript.Shell Initialized...
  Scripting.FileSystemObject initialized...
  Called CreateDirectory(F:\PostgersSql\data)...
  Called CreateDirectory(F:\PostgersSql)...
  Called ClearAcl (F:\PostgersSql\data)...
      Executing batch file 'rad712E9.bat'...
  Called Die(Failed to reset the ACL (F:\PostgersSql\data))...
  Failed to reset the ACL (F:\PostgersSql\data)

Any one can tell me what is causing this error?

your having an ACL error Access Control List so this is windows security issue

try turning off UAC

if that fails try running initdb manually
C:\pathtoPosgresq\Bin\initdb -D path\to\data\

doing this manually make sure postgres.conf has the correct path to the directory its located in the data directory and you may need to adjust the service registry entries to the correct  database path or just start postgresql manually

https://www.postgresql.org/docs/current/creating-cluster.html
https://www.postgresql.org/docs/current/server-start.html


Using CONVERT TO CHARACTER SET for changing only collation? I think the answer to this is an obvious yes, but I'm having trouble finding confirmation on it. All the questions related to charsets seems to be about changing the charset and not only collations.

So I have tables in utf8mb4_general_ci that I want to convert to utf8mb4_unicode_ci. Can I safely run the following query to get the task done?

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

I would run that on all tables that have a table collation in information_schema set to utf8mb4_general_ci. Anything I need to be aware of before beginning this task? Should I double check that the columns in the table also have the same charset set? I've received a warning that even just changing the collation can cause charset issues to pop up afterwards, but not sure how much truth there is to that...

I also have latin1-tables and utf8-tables in these databases to convert. But I think I'll just try to tackle one challenge at a time.. Since I don't want to have the column types changed, I guess I'll have to do it in a more complicated way than using that command.

(I'm trying to fix charset inconsistencies between tables in numerous databases we have)

So I would like to start by unifying the collations on those tables already utf8mb4 and then just continue gradually from there.

Server version: 5.7.16-10-log Percona Server (GPL), Release '10', Revision 'a0c7d0d'

There are a few options for managing character set conversions. As you have found, there is the alter table option, and you can use the character set clause or the collate clause or both in your statements.

Other options include changing the character set and collation for the whole database (which I can see you don't want to do just yet).

Or there's a tool in the free and open source Percona Toolkit that is very popular with developers, pt-online-schema-change, that helps you manage such migrations for tables with a primary key.

David Berube, a consultant independent from Percona, wrote a detailed blog post on our community blog about some of the problems that you can encounter when changing character sets and collations. You can read that at https://www.percona.com/community-blog/2018/06/12/character-sets-migrating-utf8mb4-pt_online_schema_change/

The potential pitfalls included database version compatibility, application "expectations", key length management (the length could change), and the fact that you could see false positives i.e. a column that says it's latin1 may not be!

This time on the Percona website, there are a number of blog posts and also a free webinar Troubleshooting Issues with MySQL Character Sets https://www.percona.com/resources/webinars/troubleshooting-issues-mysql-character-sets

Hope some of these will help you work out the best way to approach your scenarios.

--
Disclosure: I work for Percona.


Availability Group- Redo Rate displayed on AG Dashboard vs Perfmon counter I am bit confused on checking the metrics for REDO Rate KB/Sec from Always on AG dashboard, which for some scenarios matches with perfmon counter Database replica : Redone Bytes/Sec ( hopefully it's the correct counter for redo rate via perfmon), and sometimes not at all.
Most of the times when there is lot of activity ongoing, my belief is that AG dashboard and DMV shows REDO rate for e.g. 40 MB/Secs at given time which matches with perfmon counter.
However, during less activity time or nothing much to send over to sec, REDO Rate on Dashboard and dmv seems to be showing incorrect values compared to perfmon counter.
Not able to understand which value is correct and how to analyze. Any idea why or is it bug in dashboard?
Screenshot as requested:

No transactions occurred around that time - no major activity on primary end. I am collecting that perfmon on secondary and primary since after failover new secondary we would still need those counters running. However the data pulled in screenshot perfmon data is from secondary.

Those two numbers are measuring slightly different things.  You're right that they are both measure redo, but they do it in different ways.
The Perfmon counter is updated in near-real-time - it's the number of bytes redone in the last second:

Amount of log records redone in the last second to catch up the database replica

The AG dashboard is based on the sys.dm_hadr_database_replica_states DMV, specifically the redo_rate column:

Average Rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.

So the AG dashboard is based on an average, but over what period?  I suspect it's "the last active period" based on the phrasing in the log_send_rate description from the same DMV:

Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.

Let's try and see.  I'll open up a lab AG dashboard, and first thing I notice is that the redo rate is not zero, despite the fact that I haven't used this thing in a couple weeks:

Perfmon is flat on the secondary, as I'm not doing anything yet:

Now I'll insert some data into my test database:
INSERT INTO dbo.A
SELECT TOP (1000)
    REPLICATE(N'A', 50)
FROM master.dbo.spt_values;

Now Perfmon on the secondary shows a brief blip:

And if I open up the AG dashboard, I can see the redo rate changed (from 3535 to 3873), but it didn't drop back down to zero:

So it looks like this DMV (and the dashboard) is only updated when redo is actually happening, and it holds the last value that it calculated.


Postgres equivalent of SQL Server profiler In SQL Server when we have applications for which we do not have source code we could use the SQL Server profiler to analyze which queries or procedures are called when we do something in the application. Now I have similar situation with a .NET third party library and a Postgres database when it would be very useful to track queries called by that library. I found something similar in pgAdmin, but so far it appears to be useful to track only SQL queries that are executed in pgAdmin, not queries from external applications.

So to sum up: Is there a tool that allows me to track queries executed on a Postgres database from external applications?

EDIT: I tried to select from pg_stat_activity, but when I expect to see which stored procedures or statement is executed it prints only "COMMIT" string in the "query" column.

Set log_statement = 'all' in postgresql.conf and reload PostgreSQL. Then all SQL statements will be written to the log.


Why does most Galera cluster documentation recommend having at least 3 nodes as the typical set up? The Galera documentation and most experts implementing this type of setup, recommend having at least 3 nodes in the Galera cluster. What is the reasoning behind this?

Is a cluster setup with only two nodes essentially flawed?

If you want real HA ("High Availability"), you need 3 nodes in 3 separate locations.

When one machine goes down, the rest of the nodes vote and see that they are in the majority.  They see 2 out of 3, so they declare themselves to be the cluster and their sibling to be out of the cluster.  If the dead machine comes back online, the majority chatter with the reanimated one and decide how to repair it.  All of this is automagic.

Let's take a slight variant of that -- 3 machines, but a network outage splits one away from the two.  The two, as above, declare them still in the cluster.  The one, not having a majority, realizes that he should not be the cluster.  So, it plays dead (or something).

What about only 2 nodes? -- If one dies, or is isolated, the other one does not have a majority, so it plays dead.  You have no cluster, and you are hosed.

2 + garbd -- This is where you have 2 machines with all the data, plus a third, little "arbitrator" machine.  The garbd pretends to have the data, but is really empty.  He votes, so any single machine outage can keep the cluster alive.

Why did I say "separate locations"?  What if you have a 3-node cluster with all 3 in the same rack in the same building on the same flood plain on the same earthquake fault on the path of a tornado, etc?  Some disaster is very likely to take out all 3 nodes.  No HA.

I have a few more comments and tips on Galera in http://mysql.rjweb.org/doc.php/galera


Can I copy the table structure of a #temp table to a new physical table? I have a #tempTable that was created using

SELECT *
INTO #tempTable
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls',
'SELECT * FROM [Sheet1$]')

Is there an easy way to copy the table structure of the #tempTable to a new physical table?

The temp table contains a lot of columns which contain numbers, dates, decimals, and strings, and I do not want to have to identify and manually define every column.

I am using SQL Server 2005

Why not use the same approach you used to create the #temp table?

SELECT * INTO dbo.NewTable FROM #tempTable;


do a sum for each entry in table i got two tables a and b
table a contains some names like cobol, php, cmd and a refnumber

name ref
Cobol 1
php   2
cmd   3
...

table b contains multiple projekts where i used one of table a
like year, mm, and a reference to table a
that means f.e

year mm ref
1982 8  1
1982 4  2
1983 5  3
1983 1  1
1982 6  3
1994 12 1
1995 12 2
1996 6  1
1996 6  2

now i want to know how many month in total (from b) i used one of table a to get a list like

Cobol 27
php   20
cmd   11

Note :sums might be not corret in this example.
An answer provided below made me realize I need to clarify the situation further.
Table a has the ref in a so far not mentioned table c with the content:

id number uses
1   1      1
2   1      2
3   2      1
4   2      3
5   2      4

So table a (pgojekt) contains each project I've worked on and each entry there has a year and number of month in it. It references an other table for example reflanguages.  In Languages there are the possible languages while reflanguages contains the reference to the language.
I hope this is understandable.
For example in 1982 i did 2 projects (1 and 2)  each with 6 month duration. While Project 1 uses languages 1 and 2, project 2 uses language 3 and 4. In 1983, I did another 12 month project that uses languages 1 and 2 and in 1984 one with 3 month 1 and 2 and one with 4 and 5 and 6.
Now the problem seems that a select name, sum(mm) as you suggested shows all but not the sum for Cobol in all those years, followed by php in all those years, followed by....
if this gets to complicated i can add a db dump.
what i want is a list like written above.
I got it working when specifying the name in a where clause, however I want it for all names in table a as shown above
How can this be achieved with MySQL?

Something like the following (untested) should do what you want:
select name, sum(mm)
from a join b using (ref)
group by name;


Can Fill Factor be changed on specific tables without index rebuild? I've been digging around without any luck.

SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table?

For example, if it has 400 million records, can we adjust the fill factor and have SQL Server use the new fill factor on all pages that haven't already exceeded that limit (as well as new splits), and then adjust pages that are over limit during index maintenance?

Is it possible to pre-define the fill-factor that an index should be built to during the next index maintenance?

This is not how FILLFACTOR works. It only applies to pages written during a CREATE or REBUILD operations. Pages are always filled as much as possible as they are created or split.

As per the MSDN documentation for CREATE INDEX:

  The FILLFACTOR setting applies only when the index is created or rebuilt.

So, even if you change the FILLFACTOR during a REBUILD (since your Clustered Index already exists), only the existing pages will make use of that value. As new pages are created, and existing pages are split, they will be filled as if the FILLFACTOR were set to 100.

Or, stated another way: the rebuild operation is not your problem here as you will never get new and split pages to use a FILLFACTOR below 100.

Also: No, it is not possible to "pre-define" a change to the FILLFACTOR value that will take affect the next time there is a REBUILD. It can only be specified in the WITH clause of a REBUILD operation. BUT, once you have applied the new value during a rebuild, that new value will be stored in sys.indexes and will be used again for subsequent rebuilds unless it is changed again by someone specifying WITH (FILLFACTOR = x).

Or, again stated another way:

The FILLFACTOR value can be changed only when actually doing a REBUILD (I am not considering a DROP and re-CREATE of an Index to be a "change").
The FILLFACTOR value is used only by CREATE INDEX and ALTER INDEX ... REBUILD (and technically also DBCC DBREINDEX, but that shouldn't be used as it has been deprecated as of SQL Server 2005); FILLFACTOR is not used as rows are inserted or updated as that would defeat the purpose of having this option in the first place.

FILLFACTOR reserves space on existing data pages so that the space can be used (hence reducing the chances of getting page splits); not allowing pages to fill beyond the FILLFACTOR value would permanently reserve that space for no purpose, effectively reducing the size of the data pages, which would actually increase, not decrease, the chances of getting page splits.

Page split operations always move approximately 50% to the new page.


combine rows from table with NUM columns I have this table:

Product | Qtdy | Price
A       | NULL | 123.00
A       | 23   | NULL

How Can I represent convert like that?

Product | Qtdy | Price
A       | 23   | 123.00

Which means group by product using Null to return value print in the next line?

Thank you

Here's one way.

SELECT Product, MAX(Qtdy), MAX(Price)
FROM table
GROUP BY Product

The better answer is to clean up your data so it isn't ditry.


What is "special" about PostgreSQL update vs delete+insert My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple.
In other words, update = delete + insert.
Or so I had believe. But it appears there something fundamentally different about update from delete+insert in MVCC.

Setup:
CREATE TABLE example (a int PRIMARY KEY, b int);
INSERT INTO example VALUES (1, 1);

Method 1: Update
-- session A                          session B
BEGIN;
UPDATE example SET b = 2 WHERE a = 1;
                                      DELETE FROM example WHERE a = 1;
COMMIT;
-- now there are 0 rows in table example (1 row was deleted by session B)

Method 2: Delete and insert
-- session A                          session B
BEGIN;
DELETE FROM example WHERE a = 1;
INSERT INTO example VALUES (1, 2);
                                      DELETE FROM example WHERE a = 1;
COMMIT;
-- now there is 1 row in table example (0 rows deleted by session B)

Thus
UPDATE example SET b = 2 WHERE a = 1;

is different from
DELETE FROM example WHERE a = 1;
INSERT INTO example VALUES (1, 2);

How am I to understand the MVCC nature of update? Does the tuple has some sort of MVCC "identity" that is preserved during the update? What is it?

Yes, there is a difference between UPDATE and DELETE + INSERT.
Let's use the pageinspect extension to look at the tuples and the tuple headers.
If you want to repeat my experiment, you have to drop and re-create the table in between. Also, there may be additional flags (hint bits) if you selected the rows before examining them.
The meaning of infomask2 and infomask can be found in src/include/access/htup_details.h, see the quotations at the end of the answer.
After the UPDATE:
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask2, t_infomask, t_attrs
FROM heap_page_item_attrs(get_raw_page('example', 0), 'example');

 lp | t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask |            t_attrs
----+--------+--------+--------+-------------+------------+-------------------------------
  1 | 380943 | 380944 | (0,2)  |       16386 |        256 | {"\\x01000000","\\x02000000"}
  2 | 380944 |      0 | (0,2)  |       32770 |      10240 | {"\\x01000000","\\x02000000"}
(2 rows)

The first tuple is the dead one. Its t_ctid has been changed to point to the updated version.
This is one of the key points, so let me expand on this: the ctid of a tuple is the combination of the block number and the “line pointer” (lp in the query result. t_ctid is normally redundant, but in this case it is used to point to the new row version. This is the link between the original tuple and the updated version.
t_infomask2 is 2 (the number of columns) plus HEAP_HOT_UPDATED, so this row received a HOT update (there was enough space in the block, and there is no index). t_infomask is HEAP_XMIN_COMMITTED (a hint bit).

The second tuple is the new version.
t_infomask2 is 2 plus HEAP_ONLY_TUPLE, so this is  “heap-only tuple” that is only reachable via the updated ctid of the old version. t_infomask is HEAP_XMAX_INVALID (true, it is 0) plus HEAP_UPDATED (this is the updated version).

After the DELETE + INSERT:
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask2, t_infomask, t_attrs
FROM heap_page_item_attrs(get_raw_page('example', 0), 'example');

 lp | t_xmin | t_xmax | t_ctid | t_infomask2 | t_infomask |            t_attrs
----+--------+--------+--------+-------------+------------+-------------------------------
  1 | 380958 | 380961 | (0,1)  |        8194 |        256 | {"\\x01000000","\\x02000000"}
  2 | 380961 |      0 | (0,2)  |           2 |       2048 | {"\\x01000000","\\x02000000"}
(2 rows)

Again, the first tuple is the dead one.
t_infomask2 is 2 plus HEAP_KEYS_UPDATED (this is a deleted or updated tuple), and t_infomask is HEAP_XMIN_COMMITTED (the tuple was valid before it was deleted).

The second tuple is the inserted one:
t_infomask2 is 2 plus, and t_infomask is HEAP_XMAX_INVALID (it is 0), so this is a new tuple.

Explanation of the observed difference:
In READ COMMITTED isolation level, a transaction always sees the latest committed version of a row. The DELETE in session B has to lock the row and is blocked by the UPDATE or DELETE in session A.
The documentation explains what happens when the lock is released:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

In the case of the UPDATE there is a link between the old and the new row version, so PostgreSQL locks and deletes the new row version, while in the case of DELETE + INSERT there is no valid version of the row after the lock is gone, and nothing is deleted.
So while in many respects UPDATE and DELETE + INSERT are quite similar in PostgreSQL, they are not the same: in the second case, there is no connection between the deleted and the inserted row.
Appendix: the meaning of infomask and infomask2
t_infomask:
/*
 * information stored in t_infomask:
 */
#define HEAP_HASNULL            0x0001  /* has null attribute(s) */
#define HEAP_HASVARWIDTH        0x0002  /* has variable-width attribute(s) */
#define HEAP_HASEXTERNAL        0x0004  /* has external stored attribute(s) */
#define HEAP_HASOID             0x0008  /* has an object-id field */
#define HEAP_XMAX_KEYSHR_LOCK   0x0010  /* xmax is a key-shared locker */
#define HEAP_COMBOCID           0x0020  /* t_cid is a combo cid */
#define HEAP_XMAX_EXCL_LOCK     0x0040  /* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY     0x0080  /* xmax, if valid, is only a locker */

 /* xmax is a shared locker */
#define HEAP_XMAX_SHR_LOCK  (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)

#define HEAP_LOCK_MASK  (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \
                         HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_XMIN_COMMITTED     0x0100  /* t_xmin committed */
#define HEAP_XMIN_INVALID       0x0200  /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN        (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED     0x0400  /* t_xmax committed */
#define HEAP_XMAX_INVALID       0x0800  /* t_xmax invalid/aborted */
#define HEAP_XMAX_IS_MULTI      0x1000  /* t_xmax is a MultiXactId */
#define HEAP_UPDATED            0x2000  /* this is UPDATEd version of row */
#define HEAP_MOVED_OFF          0x4000  /* moved to another place by pre-9.0
                                         * VACUUM FULL; kept for binary
                                         * upgrade support */
#define HEAP_MOVED_IN           0x8000  /* moved from another place by pre-9.0
                                         * VACUUM FULL; kept for binary
                                         * upgrade support */
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)

#define HEAP_XACT_MASK          0xFFF0  /* visibility-related bits */

t_infomask2:
/*
 * information stored in t_infomask2:
 */
#define HEAP_NATTS_MASK         0x07FF  /* 11 bits for number of attributes */
/* bits 0x1800 are available */
#define HEAP_KEYS_UPDATED       0x2000  /* tuple was updated and key cols
                                         * modified, or tuple deleted */
#define HEAP_HOT_UPDATED        0x4000  /* tuple was HOT-updated */
#define HEAP_ONLY_TUPLE         0x8000  /* this is heap-only tuple */

#define HEAP2_XACT_MASK         0xE000  /* visibility-related bits */


Does a Target Service need to do anything for a conversation that is ended? We have two queues, SSBInitiator and SSBTarget.  In my example situation, I have ran the following code to create a conversation:

BEGIN DIALOG CONVERSATION @ConversationHandle
                FROM SERVICE [//BLAH/Events/SSBInitiator]
                TO SERVICE '//BLAH/Events/SSBTarget'
                ON CONTRACT [//BLAH/Events/SSBContract]
                WITH ENCRYPTION = OFF;

Now lets say that, for some reason I've decided this conversation is unnecessary: either the user cancelled the request, or something else happened.  I know I can end the conversation using either:

end conversation @ConversationHandle

or

END CONVERSATION @ConversationHandle
      WITH ERROR = 1 DESCRIPTION = 'Ending Convo cause reasons';

Now, with the conversation ended and no messages have been sent on it, does the Target service need to do anything with the conversation?  IE, is there additional processing that needs to happen to make sure the conversation is completely closed?

From Microsoft Docs:

Both participants in a conversation must call END CONVERSATION for the conversation to complete.

If only one side ends the conversation, then the conversation metadata stays valid.
The END CONVERSATION ... WITH CLEANUP can be used to clean up any un-ended conversations.
Remus Rusanu has an excellent article showing how to do that here.


selecting and Inserting data from two different servers I have two server mysql and psql. I am fetching data from mysql server database table and inserting it into my psql server. The way I am doing is by selecting all the column from mysql server and saving it into an array.

sql->prepare("SELECT id, date,studentName, FROM student", array());

variablArray= array();

Then inserting the data in psql with the help of implode().

psql->prepare("INSERT INTO student",implode(",", $variableArray);

The problem is every time I ran the query I too 45 min to complete, because I truncate the psql database table before inserting so that the query can run.

Is there is an another way to do this?

If the duration is related to data volume, you should consider syncing only the modified data:

put an IsDirty flag on each student record
sync (delete + insert or simple updates) data only for records where IsDirty = 1

This should work decently if a fairly small amount of records change between the sync.

Another option is to try to sync data directly between MySql and PSql as indicated here. Also, take a look upon dblink.


Shell Script / Tool to check the MySQL Replication Status? Possible Duplicate:
  Monitoring replication on mysql

I am running with MySQL Master/Slave Replication on Linux.

Is there any tool/Shell script which monitors the Replication Status and send the email alerts when there is Replication failure.

If you don't have a monitoring framework in place, I would highly recommend looking into Nagios. There are others, but for the purpose of this question, Percona has many MySQL monitoring tools available. Two of them specifically monitors slave lag and slave failures. From their blog post:

  The pmp-check-mysql-replication-delay plugin checks for excessive delay, and pmp-check-mysql-replication-running checks that replication is functioning correctly.


how to grant permissions to all the synonyms inside a view? I have to grant permissions to a view.  dbo.my_view, however, this view joins many tables, and other views and synonyms.

using the following script I can find all all the synonyms in my server:

Script to list synonym contents

SELECT
name as synonymName,
base_object_name as synonymDefinition,
COALESCE(PARSENAME(base_object_name,4),@@SERVERNAME) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
FROM sys.synonyms
ORDER BY serverName,dbName,schemaName,objectName

If I have to grant select on dbo.myview how can I find out what select permissions I need to grant in any other databases that because of the synonyms also need to be granted?

Cross-database permissions are quite tricky. So, similar to my answer here:

  What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I would suggest using module signing to accomplish this since, in the end, it will be more secure and yet less complex than the alternatives. This will require changing the View into a multi-statement Table-Valued Function so that it can be signed yet still be selected from.

Just do the following:

In the DB containing this view, create a Certificate (my preference is to specify a password instead of relying upon the Database Master Key (DMK) for the protection).
Sign the TVF with the Certificate using ADD SIGNATURE
Back up the Certificate to files (a .cer file for the Public Key a.k.a. "Certificate", and a .pvk file for the Private Key)
Remove the Certificate's private key using ALTER CERTIFICATE
USE [master];
Create the same Certificate from the .cer file (no need to create the Private Key from the .pvk) file
Create a Login from that Certificate
Grant that Login whatever permissions are necessary to accomplish the goal (a.k.a. "git 'er done"). This might be a combination of CONNECT ANY DATABASE (I think that's in 2016) and something else, maybe even up to CONTROL SERVER. If no permission combination works, try instead to add this Login to the sysadmin fixed Server Role.

The permissions granted to the Certificate-based Login are a bit much, but that Login cannot be impersonated (i.e. EXECUTE AS LOGIN = N'that_Login';) and cannot log in; that Login is merely a container for the extra permissions and only ever apply to what has been signed with that Certificate, which in this case is just the one TVF, which is just a SELECT statement.

If you ever need to change the code within that TVF, or grant those permissions to another module via signing with ADD SIGNATURE, then you will need to either create that same Certificate in another DB if the new code to sign is in another DB, or you will need to restore the Private Key into this Certificate using the ALTER CERTIFICATE statement. While you can extract the public and private keys to VARBINARY hex byte strings using two built-in functions and then create a new Certificate from those VARBINARY strings (starting in SQL Server 2012), you can only restore a Certificate's private key from a .pvk file. Hence Step 3 above to use BACKUP CERTIFICATE as it can be used for both creating the full Certificate in any other DB as well as for restoring the private key into the existing Certificate here.


Do I need to create index for the column when using it in update statement? I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table.
I need to perform below update statement on the table.
update entity set name='xxxx' where userId = 'yyy.
The userId is in the where condition but I don't know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?

Indexes are meant to improve performance of predicates used in WHERE, JOIN, and HAVING clauses for any type of query. So yes having an index on userId theoretically can improve performance of your UPDATE example query.
After you create the index you don't need to specify it in your query, PostgreSQL will automatically use it if its engine thinks it'll help the query performance.


Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY.
One imagines this could be an adaptation of Bw-Tree or Bz-Tree. However, these rely on variable-sized pages whereas the current storage engine requires fixed-size pages.
How is the optimization implemented? How are current B-Tree algorithms altered by this optimization? In what circumstances would I choose not to deploy this option?
Research
A patent for a reverse key approach.
I had a quick look using DBCC PAGE, comparing 2017 to 2019 and 2019 with and without OPTIMIZE_FOR_SEQUENTIAL_KEY on an unique clustered index for an int IDENTITY column. There was nothing that obviously explained the new behaviour. This makes me think its an algorithmic thing, rather than a structural thing, which makes sense.
A blog post from MS.
This feature seems to centre on the detection and avoidance of convoys.

The optimization is implemented by applying flow control on the workers trying to insert to reduce heavy contention and convoys. The idea is based on an unfair insert mutex per scheduler and it can help avoid latch waitlist buildup and convoys as a result. When users opt-in per index with the option, OPTIMIZE_FOR_SEQUENTIAL_KEY, and then we do traffic/flow control on insert workers. Essentially, using an unfair-mutex per scheduler (at a HoBt level where the optimization is on) the flow control will allow only one worker per scheduler to enter the insertion code path that contends for page latches, so that we can reduce convoys and improve the scalability of concurrent inserts. Pam Lahoud provides all this detail in her blog post and I can find nothing in the functional spec or design doc that indicates changes to the current B-Tree algorithms.  Other solutions that were considered (one of which was leveraging an optimized form of reverse-key indexes you referenced in your question) have potential drawbacks, and most would involve fundamental changes to the engine. These other solutions may be considered in the future, but this solution provided a significant improvement without fundamentally altering the engine.

You wouldn't want to deploy this where you are not expecting or experiencing last page insert contention.


Put Postgres query result in array How can I put an sql query result in a string array with Postgres?
I have some attributes in the column 'zone' of the table 'mytable' and I must put them all in a String[] areas;
This is my first request, sorry if I made some format mistake.

You can use an array constructor:

select array(
select zone
from mytable)
as areas;


ORACLE: Can I get results of "SHOW PARAMETER" into a variable or table? When I execute SHOW PARAMETER CONTROL_MANAGEMENT_PACK_ACCESS, it gives me the value of this Oracle parameter as part of what appears to be a single row with three columns (name, type, and value). How can I get this value into a variable or table that I can use later in my script?

I'm trying to make a decision later in my script based on a few parameter values and I'm not sure how to store them in something persistent.

Query v$parameter instead (or v$system_parameter in the event that you've changed session-level parameters in your current session and want to ignore those changes).  show parameter is just SQL*Plus syntactic sugar on top of those data dictionary views.


Creating Conditional Rules for Column Values I am currently working on the following table in SQL Server

Table: order_status_logs
    log_id      INT NOT NULL IDENTITY(1,1)
    order_id    INT NOT NULL
    status_id   INT NOT NULL
    log_date    DATE NOT NULL
    expected_by DATE NULL

In this table for the status_id column there are the following values possible:

[1] - Pending
[2] - Ordered
[3] - Backordered
[4] - Received

I would like to set up my table so that if a new record is trying to be created, expected_by goes from NULL to NOT NULL if the status_id is not equal to 4.  Essentially unless the order has been received, the table will always need data for the expected_by column.

Is it possible to do this with a CONSTRAINT?  Or is this something I'd need to do via a Procedure where I return an error message or something if I don't provide that column data?

Yes, you can create a table check constraint without going to triggers or stored procedures. This is valid for Sql Server 2008 or higher:

ALTER TABLE dbo.order_status_logs
   ADD
   CONSTRAINT CHK_EXPECTED_BY
   CHECK ((status_id <> 4 AND expected_by IS NOT NULL) OR status_id = 4);
GO


MYSQL item in select value where value is enum I have some hard time with a select, i  think i used in past but i cant remember where.

SELECT * FROM tableX WHERE id in ( select ids from tableY );

in tableY i have: (ids is a string field with multiple ids separated by comma)

ids,...other fields
'1,2,3',...
'1',....

Anyone can help with a suggestion ?

Thanks a lot!

I managed to find a solution on google ...

SELECT * FROM tableX
WHERE FIND_IN_SET(id, (SELECT GROUP_CONCAT(ids) FROM tableY WHERE ...))


Make sure slave has finished reading from master I am planning to have a master-slave system in which occasionally the master and slave will reverse roles.  To do this, I will stop further updates to master, and redirect traffic to the slave.  But, before I make the slave the new master, I need to make sure the slave has finished reading all the updates from the old master.  How can I do that?  Is running "flush logs" on the master sufficient?

An solution that can be readily scripted would be preferred.

FLUSH LOGS won't do anything useful for you.

With no log_type option, FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.
-- http://dev.mysql.com/doc/refman/5.5/en/flush.html

The slave gets events as soon as they're written to the file... not when fsync() or fdatasync() is called and not when the file is closed or rotated... the events are sent across almost instantaneously.
Additionally, it's not only that you need the slave to finish reading everything from the master.  You really need to make sure it has also executed everything from the master.
A better solution would likely be to set the two machines up in master-master where each of them is always a slave to the other.  You'll read that master-master (a.k.a. "circular") replication brings with it some new issues and concerns, but many of these are related to writing to both machines at the same time and potentially causing replication to break due to conflicting writes applied to each machine at almost the same time.
What you'll do, instead, is treat one machine as the master, only allowing your application to write to that one.  When you want to switch... then all you have to do is switch... point your application connections that do the writing to the opposite machine.
An excellent and painless way to do this is with HAProxy.  It can even connect your application only to the preferred server if the preferred server is up, and switch your application to the backup server if the preferred server is down.  You configure your application's MySQL connection to point to HAProxy and configure HAProxy to relay those connections into the appropriate MySQL instance.
But, if you really want to do it manually, here's the way to know the two machines are currently in sync:
On the master:
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (6.53 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.002211
        Position: 977403994
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

Keep that connection open, because it holds the read lock, which prevents any thread from writing to the master.  Your application will be able to read, but will block on writes.  Check SHOW MASTER STATUS several times in a row and when the Position stops incrementing (which should be almost immediately since you shouldn't get back the prompt from the FLUSH TABLES WITH READ LOCK until all pending writes are done) your master is frozen and will not write anything else to the binary log until you unlock the tables with UNLOCK TABLES; or you disconnect.  If you shut down the master while still holding the read lock, nothing else on the system should be changed.
On the slave: (extra fields removed for clarity)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxxxxx
                  Master_User: xxxxxxxx
              Master_Log_File: mysql-bin.002211    <<<<<
          Read_Master_Log_Pos: 977403994           <<<<<
        Relay_Master_Log_File: mysql-bin.002211    <<<<<
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 977403994           <<<<<
        Seconds_Behind_Master: 0
             Master_Server_Id: 11
1 row in set (0.00 sec)

mysql>

When the Master_Log_File and Relay_Master_Log_File on the slave match the log file on the master and when Read_Master_Log_Pos (the last position the slave has received from the master) and Exec_Master_Log_Pos (the last position the slave has executed from the master) agree with the information you got from the master, and the *Running states are Yes and Seconds_Behind_Master is 0, then your slave has received and executed everything from the master and the two servers at that moment contain identical data in their tables.
The one caveat here is that if you're going to do this manually, you should probably -- somehow -- disconnect your application from the master before locking the tables, because if there are pending writes, they will happen on the master as soon as you unlock the tables.


Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries) Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this:

"ORA-04031: unable to allocate 56 bytes of shared memory ("shared
pool", "unknown object", "KKSSP^4366", "kglseshtSegs")"

Here is another ORA-04031 for different days:

ORA-12801: error signaled in parallel query server ...
ORA-04031: unable to allocate 40 bytes of shared memory ("shared
pool", "unknown object", "KGLH0^4ad1e9c3", "kglHeapInitialize:temp")

And another one:

ORA-00604: error occured at recursive SQL level 1
ORA-04031: unable to allocate 40 bytes of shared memory ("shared
pool", "unknown object", "KGLH0^4ad1e9c3", "kglHeapInitialize:temp")

My SGA parameters are as follows:
SQL> show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 10G

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 10G

SQL> show parameter sga_min_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_min_size                         big integer 0

And here is my cursor_sharing parameter (Oracle recommends EXACT value for the best practice):
SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing                       string      EXACT

And here is the my free memory for sharing pool (even if the values are changing, they are generally close to that):
SQL> select inst_id, name, bytes/1024/1024 MB from gv$sgastat where pool='shared pool' and name='free memory';

   INST_ID NAME                                                                           MB

---------- ---------------------------------------------------------------------- ----------

  1        free memory                                                            183.724869

  2        free memory                                                            258.926163

In document for 12c, Oracle says:

"If free memory is always available in the shared pool, then
increasing its size offers little or no benefit."

So, I will not increase the shared_pool size.
I'm suspecting hard parse and parallel queries for ORA-04031.
And here are some statistics:
SQL> select name, sum(value) from gv$sesstat s1 join gv$statname s2 on s1.statistic#=s2.statistic# where name like '%parse count%' group by name order by 1,2;

NAME                                                                   SUM(VALUE)

---------------------------------------------------------------------- ----------

parse count (describe)                                                         68

parse count (failures)                                                        490

parse count (hard)                                                          40772

parse count (total)                                                       1295326

Here is a temporary (workaround) solution:
alter system flush shared_pool;

Do you have any advice to solve this problem completely? If other information is needed I can provide it.
NOTE: I edited my question.
Best regards,

With only that information, I can give only the below.
Yes, I am aware that the bug is fixed in 12.2 and you wrote in the title the database was 12.2 (but no other proof).
ORA-04031: ("shared Pool".."KKSSP^nnnn","kglseshtSegs") (Doc ID 2151847.1)

SOLUTION

Download and apply Patch 23315153.

In case the needed patch is not available, please open a Service
Request with Oracle Support.

The fix is included in Oracle release 12.2.

The bug has no workaround.


Oracle - Need help with RMAN Active Duplication on Windows 32 bit to a 64 bit I need to have an Oracle 11g R1 on a Windows 32-bit duplicated to a Windows 64-bit using RMAN Active Duplication.

32-bit setup:

  Windows Server 2003 R2 Standard Ed. SP2
  Database - Oracle 11g  11.1.0.6.0

64-bit setup:

  Windows Server 2008 R2 Standard Ed.
  Startup Database - Oracle 11g 11.1.0.7.0

Although 11g R1 is not certified on Windows Server 2008 R2 it installed fine without any problems.

RMAN was invoked from 11g 11.1.0.6.0 as the TARGET to AUXILIARY 11g 11.1.0.7.0
(This was done for backward compatibility issues; to match the same or higher RMAN version).
Either database is not an RCVCAT, but connections are established as TARGET or AUXILIARY.

For privacy purposes, I replaced the names for the databases to match 32-bit and 64-bit accordingly.

Working from the 32-bit system, after executing RMAN I got the following error:

Starting Duplicate Db at 07-JAN-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=152 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   file  'D:\dbData\product\11.1.0\db\DATABASE\PWD32bit.ORA' auxiliary format
   'C:\app\oracle\product\11.1.0.7.0\db_1\DATABASE\PWD64bit.ORA'   ;
}
executing Memory Script

Starting backup at 07-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/07/2010 16:39:21
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/07/2010 16:39:21
ORA-17627: ORA-12577: Message 12577 not found;  product=RDBMS; facility=ORA

While invoking RMAN from the 64-bit I had the following:

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 11.01.00.06 in TARGET database is notcurrent
PL/SQL package SYS.DBMS_RCVMAN version 11.01.00.06 in TARGET database is not current
connected to target database: 32-bit (DBID=2889597870)

If the problem is RMAN compatibility issues is it possible to upgrade to a higher RMAN version (in the 32-bit) or downgrade to a lower RMAN (if at all possible, in the 64-bit)
instead of upgrading the 32-bit database to patch 7 (11.1.0.7.0)?
The 32-bit database will be decommissioned after the duplication to the 64-bit.

If this is possible can anyone help in providing information about how to accomplish this?
Or if unrelated to this, then what is causing this problem and which way should I start troubleshooting?

Any help would be greatly appreciated.

You have to initiate the duplicate from the target database to be able to connect to the not mounted target database. I suggest you to downgrade the oracle version on the 64 bit machine. The easiest way to do this will be to install 11.1.0.6.0 version on the 64-bit server into a separate oracle home and perform the duplicate.  You can upgrade the database  after duplicate and switch it to the 11.1.0.7.0 home.

The error you are getting on the 32-bit database seems to be  a connection problem to the database. The target instance is not mounted and you cannot connect to is via SQLnet.

When performing the duplicate form 64 bit system, please make sure that you are using username/password in the connection string to the target database and not the system authentication.

Best regards,
Yuri


Retaining earliest instances of duplicate entries from a table We have a situation where duplicate entries have crept into our table with more than 60 million entries (duplicate here implies that all fields, except the AUTO_INCREMENT index field have the same value).  We suspect that there are about 2 million duplicate entries in the table.  We would like to delete these duplicate entries such that the earliest instances of the duplicate entries are retained.
Let me explain with an illustrative table:
CREATE TABLE people
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
phrase VARCHAR(40) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);

INSERT INTO people(name, age, phrase) VALUES ('John Doe', 25, 'qwert'), ('William Smith', 19, 'yuiop'),
('Peter Jones', 19, 'yuiop'), ('Ronnie Arbuckle', 32, 'asdfg'), ('Ronnie Arbuckle', 32, 'asdfg'),
('Mary Evans', 18, 'hjklp'), ('Mary Evans', 18, 'hjklpd'), ('John Doe', 25, 'qwert');

SELECT * FROM people;
+----+-----------------+-----+--------+
| id | name            | age | phrase |
+----+-----------------+-----+--------+
|  1 | John Doe        |  25 | qwert  |
|  2 | William Smith   |  19 | yuiop  |
|  3 | Peter Jones     |  19 | yuiop  |
|  4 | Ronnie Arbuckle |  32 | asdfg  |
|  5 | Ronnie Arbuckle |  32 | asdfg  |
|  6 | Mary Evans      |  18 | hjklp  |
|  7 | Mary Evans      |  18 | hjklpd |
|  8 | John Doe        |  25 | qwert  |
+----+-----------------+-----+--------+

We would like to remove duplicate entries so that we get the following output:
SELECT * FROM people;
+----+-----------------+-----+--------+
| id | name            | age | phrase |
+----+-----------------+-----+--------+
|  1 | John Doe        |  25 | qwert  |
|  2 | William Smith   |  19 | yuiop  |
|  3 | Peter Jones     |  19 | yuiop  |
|  4 | Ronnie Arbuckle |  32 | asdfg  |
|  6 | Mary Evans      |  18 | hjklp  |
|  7 | Mary Evans      |  18 | hjklpd |
+----+-----------------+-----+--------+

On smaller sized tables the following approach would work:
CREATE TABLE people_uniq LIKE people;

INSERT INTO people_uniq SELECT * FROM people GROUP BY name, age, phrase;

DROP TABLE people;

RENAME TABLE people_uniq TO people;

SELECT * FROM people;
+----+-----------------+-----+--------+
| id | name            | age | phrase |
+----+-----------------+-----+--------+
|  1 | John Doe        |  25 | qwert  |
|  2 | William Smith   |  19 | yuiop  |
|  3 | Peter Jones     |  19 | yuiop  |
|  4 | Ronnie Arbuckle |  32 | asdfg  |
|  6 | Mary Evans      |  18 | hjklp  |
|  7 | Mary Evans      |  18 | hjklpd |
+----+-----------------+-----+--------+

Kindly suggest a solution that would scale to a table with tens of millions of entries and many more columns.  We are using MySQL version 5.6.49.

DELETE t1
FROM people t1
JOIN people t2 USING (name,age,phrase)
WHERE t1.id > t2.id;

fiddle

PS. None column is indexed now - so on 60kk table this query may be infinite with high probability. Create an index at least by the most variable column (I'd prefer phrase) including id, i.e.
CREATE INDEX idx_name
ON people (phrase, id);

Of course the best option is to create an index by (phrase, name, age, id).

Alternatively you may gather id values to be deleted into temporary table and then delete them:
CREATE TEMPORARY TABLE tmp (id INT PRIMARY KEY)
SELECT t1.id
FROM people t1
JOIN people t2 USING (name,age,phrase)
WHERE t1.id > t2.id;

DELETE people
FROM people
JOIN tmp USING (id);

This may be faster then the former method. And the same index creation will improve.
fiddle


Is it possible to refer to alias before it is assigned? Does the query shown below work? (Sorry for this type of question, but I am a novice and it is not possible for me to run it right now).

SELECT
   table.id
   SUM(10/ta.value) AS amount
JOIN tableA ta
         ON ta.key = t.key

What I am curious about if it is possible to refer to a alias "before" (i.e higher up) it has been assigned.

Thanks!

FROM is - logically - evaluated before SELECT, so despite the textual order of your query, ta is known to SUM.

There are however a number of things in your query that won't work:

You need a FROM clause
A JOIN is performed between 2 tables

You can probably rewrite your query to:

SELECT table.id
     , SUM(10/ta.value) AS amount
FROM tableA ta
WHERE ...

but perhaps this is what you meant?

SELECT t.id
     , SUM(10/ta.value) AS amount
FROM ... as t
JOIN tableA ta
     ON ta.key = t.key
WHERE ta.key = t.key
GROUP BY t.id

Note that you need a GROUP BY for the latter query

The link provided by Jim Horn below is a good start towards a better understanding, so I'll just add a few lines on a methodology for constructing a query.

Step one, figure out what relations that are involved and how the relate. Example:

FROM customers as c
JOIN orders as o
    ON c.custno = o.custno

SQL is closed under operations like JOIN, UNION and so forth, meaning that the result is a new relation. It is normally not given a name, but in this case, we can think of it as customer_orders

Step two, what customer_orders are we interested in?

FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019

What details of these customer_orders are we interested in? In this example, I'll assume that we want to know how many orders each customer have:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno

It is not valid to refer to an aggregate function in the where clause, since the where clause is evaluated before the aggregation (count(...)), but there is a similar construction that can be used. Say that we want to know which customers that have more than 20 orders:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20

This is the same as:

SELECT custno, cnt
FROM (
    SELECT c.custno, count(o.orderno) as num_orders
    FROM customers as c
    JOIN orders as o
        ON c.custno = o.custno
    WHERE year(order_date) = 2019
    GROUP BY c.custno
) as customer_orders
WHERE num_orders > 20

Finally, you may want to retrieve the rows in a particular order.

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20
ORDER BY count(o.orderno) desc -- most orders first

Up until the last step the result is a relation, but the order by invalidates the fundamental property that a relation is unordered. It may seem like an irrelevant note, but it is much easier for the DBMS in question to optimize a query if it does not have to take the order of rows in consideration.