Oracle 12c; cannot connect to HR without running ALTER PLUGGABLE DATABASE ALL OPEN I just installed Oracle 12c and I'm trying to connect to the default HR database.

When I open a command prompt and type connect hr/hr@pdborcl; I get the following:

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

To connect to HR, I have to do this first:

connect / as sysdba;

alter pluggable database all open;

Only then can I run hr/hr@pdborcl;  and connect successfully

This is a bit of a pain... having to connect as sysdba and alter the database just to connect.  Is there a better way?

As a side note, all of this is on my development laptop.  I don't keep Oracle running when it's not needed as that's a waste of CPU and RAM.

Check out this from Oracle-Base about starting and stopping pluggable DBs.

First, they provide a trigger to auto-start your PDB:

CREATE OR REPLACE TRIGGER open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

And from Oracle 12.1.0.2 onward, we have this command to have Oracle restart the pluggable DB in whatever state it was in at that time (so open the PDBs then run this for each):

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

But see the link for full details on both examples.


innodb_buffer_pool_size not changed I use MySQL  5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf

default-storage-engine = innodb
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

The problem is that no matter what value I choose for innodb_buffer_pool_size (I tried also 4G and 8G and 8000M). I get the following from mysql> SHOW global  VARIABLES;

| innodb_buffer_pool_size | 134217728

Result from the Tuning primer script:

INNODB STATUS
Current InnoDB index space = 621 M
Current InnoDB data space = 3.82 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

I appreciate your hints to fix this.

Ok, thanks this answer I figured out that I needed to put the config WITHIN [mysqld] block in my.cnf to take effect.

My mistake was that I just had appended the commands at the end of my.cnf file.

Then at mysql restart I encounterd

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

To resolve this error, I needed to deleted log

  rm -f /var/lib/mysql/ib_logfile[01]

and restart as Rolando told here.


Mysql thinks Master & Slave have the same server-id I've been setting up a master->slave_a->slave_b relationship and having a little trouble. I currently have the master->slave_a replication working fine.

Each of the two slaves have exactly the same configuration, other than the hostname, server-id setting. When ever I try and start the slave on slave_b I have the following error pop up:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

I really don't understand, because I have all of the server IDs miles apart 1->2626->2629 (m->s->s).

I'm setting them in my /etc/mysql/conf.d/replication.cnf file - and it seems to be being included, but I have no idea how I can check which server id mysql is picking up.

Thanks in advance :) Tom.

The server_id of the Master is recorded in its binary logs along with each query it executed to completion. To see those server_id values in the binary log, you run mysqlbinlog against any binary log.

Since Master -> Slave_A works fine, here is what you can do to clean things up between Slave_A and Slave_B:

On Slave_A, run the following in mysql:

mysql> STOP SLAVE;
mysql> RESET MASTER;

On Slave_A, run the following in the OS:

$ echo "STOP SLAVE;" > /root/MyData.sql
$ mysqldump -u... -p... --master-data=1 --routines --triggers --all-databases >> /root/MyData.sql
$ echo "START SLAVE;" >> /root/MyData.sql
$ mysql -hSlave_B -u... -p... -A < /root/MyData.sql

On Slave_A, run the following in mysql:

mysql> START SLAVE;

This will get Slave_B to stare at the correct values for server_id, and also reestablish MySQL Replication.

BTW You need to make sure binary logging is disabled on Slave_B since it is not necessary for a Slave unless Slave_B is going to a Master to some other Slave.


Optimizing column types for reduced overall database size Given the following table:

CREATE TABLE avl_historico (
    fecha timestamp with time zone NOT NULL,
    latitud double precision DEFAULT 0 NOT NULL,
    longitud double precision DEFAULT 0 NOT NULL,
    altitud double precision DEFAULT 0 NOT NULL,
    velocidad double precision DEFAULT 0 NOT NULL,
    cog double precision DEFAULT 0 NOT NULL,
    nsat integer DEFAULT 0 NOT NULL,
    tipo character(1),
    utc_hora time without time zone,
    fix_fecha date,
    imei bigint NOT NULL,
    registro timestamp with time zone,
    input1 integer DEFAULT 0,
    input2 integer DEFAULT 0,
    input3 integer DEFAULT 0,
    input4 integer DEFAULT 0,
    hdop double precision,
    adc double precision DEFAULT (-99),
    ignicion integer DEFAULT 1,
    adc2 double precision,
    power integer,
    driverid integer,
    ibutton2 integer,
    ibutton3 integer,
    ibutton4 integer,
    trailerid integer,
    adc3 double precision,
    adc4 double precision,
    horometro bigint,
    odometro bigint,
    panico integer DEFAULT 0,
    bateria double precision,
    bateriaint double precision
);

Ref: 240 bytes / row using pg_column_size()

With about 15K tables (Over 1TB total used space) that inherit from this table using "partitioning" based on the imei column as in following example:

CREATE TABLE avl_historico_201424 (
    CONSTRAINT reports_avl_historico_only_201424 CHECK ((imei = 201424))
)
INHERITS (avl_historico);

And, considering that several column types are "bigger" that what they are actually used for. For example: column tipo could be changed into boolean and, several integer columns into smallint.

Would altering some column types actually reduce overall database size?

Specs:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
Debian GNU/Linux 8.6 (jessie)
/dbdata (ext4): 2.1TB used out of 2.9TB
Sector size (logical/physical): 512 bytes / 512 bytes

Should I provide any additional info for better understanding of the underlying structure and/or data?

Reference for numeric types: https://www.postgresql.org/docs/9.4/static/datatype-numeric.html

In addition to the links a_horse_with_no_name provided on padding a few points here,

This is tremendously wasteful.

latitud double precision DEFAULT 0 NOT NULL,
longitud double precision DEFAULT 0 NOT NULL,
altitud double precision DEFAULT 0 NOT NULL,
velocidad double precision DEFAULT 0 NOT NULL,

You're storing each 0 as a bigint rather than dealing with null which would be (almost) free one bit in the null bitmap.

USE NULL, stop using 0 to represent defaults. Your average row size will drastically shrink.

Also,

    odometro bigint,

Are you measuring kilometers in micrometers? That's a gigantic number for any odometer representation.


Why can't trigger be dropped from database? One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and remove all of the users from the vendor. There is one user, we'll call it userx, which can't be removed because there's a trigger which is set to execute as this user, we'll call it trg_write_something. When I run:

DROP TRIGGER trg_write_something

I get these error messages:

  Msg 50000, Level 16, State 1, Procedure PreserveTrigger, Line 18
  Trigger [trg_write_something] cannot be dropped from the database.

  Msg 3609, Level 16, State 2, Procedure trg_write_something, Line 4
  The transaction ended in the trigger. The batch has been aborted.

If I try to alter the trigger I get the same error.

The transaction is being rolled back in the DDL trigger, PreserveTrigger, not the trigger you're trying to drop, trg_write_something. This has nothing to do with the code in the table-level trigger, and nothing to do with permissions either. A sysadmin doesn't get to blindly bypass things like DDL triggers, though it is possible for the code in the trigger to have been written to check the user's security level before deciding what to do (but that is uncommon). It is, of course, very easy for a sysadmin to disable or drop a DDL trigger, obviously.

You're seeing these specific errors because a user-defined error message is being raised within the DDL trigger, followed by an explicit ROLLBACK TRANSACTION;. In a comment above, I mentioned that it could be because of a runtime error, but that is not enough to generate the pair of error messages you are receiving (or force an explicit rollback, unless you somehow managed to get a DDL trigger to raise a severe enough error level to terminate the batch - 16 wouldn't do that).

You can discover this DDL trigger as follows:

SELECT name
  FROM sys.triggers
  WHERE name = N'PreserveTrigger'
  AND parent_class_desc = N'DATABASE';

Or all DDL triggers like this:

SELECT name
  FROM sys.triggers
  WHERE parent_class_desc = N'DATABASE';

(You can also find them in Object Explorer under Programmability > Database Triggers.)

And you can see the code this way (which you should probably do before you drop it - maybe it's there for a reason, even if it is currently standing in your way of dropping this specific trigger):

SELECT m.[definition]
  FROM sys.sql_modules AS m
  INNER JOIN sys.triggers AS t
  ON m.[object_id] = t.[object_id]
  WHERE t.name = N'PreserveTrigger';

Or by right-clicking in Object Explorer and selecting Script Database Trigger As > CREATE To > New Query Editor Window.

You can disable the trigger temporarily like this...

DISABLE TRIGGER PreserveTrigger ON DATABASE;

...which, again, is probably better/safer than just dropping it.


Converting a graph database schema to a relational database schema I currently am using a graph database and am considering moving off of it to a relational database management system like PostgreSQL.
My concern is that I have lots of relationships between entities (since this is what a graph database is structured as) and I'm not sure the best way to handle these when moving to a relational database.
For example, I have tasks {id, projectid, scheduleid, name, desc, ...} that have a 1:m relationship with:

activities {id, name, startat, data, ...}
other tasks
materials {id, name, desc...}
invoice line items {id, price, qty, ...}
alerts {id, type...}
files {id, name, url}
messages {id, name, createdby, ...}
contacts {id, name, email...}
tags {id, name, ...}
equipment {id, name, ...}

Using the graph database I can start at one or more task nodes and then query all of the directly connected nodes which allows me to grab all of the associated data.
What's the strategy for modeling such highly connected data in a relational database without having to do 15 JOINs to get everything?  I need all the data in order to populate my views and I really don't want to do one query to get the tasks and then 15 more queries to get the rest of the data.
Considerations
I'm considering creating a task_relationship table with {task_id, target_id, target_type, target_name, target_desc} which would contain links to anything that can be related to a task along with some basic denormalized data.
However, I would still need to do JOINs to get additional data for some things and other 1:1 relationships that would be stored with the task such as project information and schedule information.
I also wouldn't be able to use cascading updates when data changes so I would have to handle that via triggers or in code I think.
Questions
Is this a sane approach or is there a better way?
Also, if I have a server with 24 cores and 128GB of memory backed by a 8 disk SSD array - how many JOINs of million row tables can I do before queries start taking more than 50ms?  I know that's impossible to answer but is it more:

“with only a few million rows you don't really need to worry about that”, or
“with a few million rows doing more than three or four JOINs will start to slow things down without very careful schema planning and optimizations”?

Starting at the end - if you have, say, 20 large tables of 10 millions rows each at 200 bytes per row that works out at just under 40GB.  Add twice as much for indexes and your entire database will fit into memory.  With sensibile indexes in place you really shouldn't worry too much about joining multiple tables.  Joins are what relational databases do, it's what their code is optimised for.  Don't be frightend of using your new software for its intended purpose.

Have you considered having different views for each specific use-case?  These will be easier to write and maintain.  The optimiser will likely do a better job, too, if it has fewer objects to consider in each view.

If you want an alternative, however, you could try modeling your data in a star schema, like a data warehouse. Task will take on the "fact" role and the others taking on the "dimension" roles.  You'll still have multiple joins but the opportunity for successive calculations on statistics producing silly answers is much reduced.

I would not try to shoehorn all the task-related values into one generic mega-table.  Think what the SQL would look like if you implemented task_relationship.  Does that really look better than joins on normalised tables?


DB2 users/ beginer questions New to this.

Looking at db2 schemas, which should be equal to OS system users, right ?
Doh apparently not. Except for PC,DB2ADMIN beeing on my OS system

How  is it, that this

select * from sysibm.systabauth
where grantee = 'DB2ADMIN'

produsces something and 'DB2ADMIN' is not on list of schemas ?

'DB2ADMIN' was created at db setup, so I assume it should have all privileges, but no, if I'm at 'PC' schemata 'DB2ADMIN' can't even execute simple select statement.

How to grant all privileges to 'DB2ADMIN' on 'PC schemata ?'

if this is my connection url:
jdbc:db2://127.0.0.1:50000/SAMPLE
What is SAMPLE, should be db, a little confuzig coz looking at db tree structure at sqirell client there is DB2 & schematas listed under it, and nowhere 'SAMPLE' ?

Unlike some other databases, in DB2 schema name does not necessarily match any user name. Users are created and authenticated outside the database, in the operating system. After a user successfully connects to the database, the schema name defaults to its login ID. For example, connecting as DB2ADMIN and issuing the statement CREATE TABLE MYTAB ... will create the table DB2ADMIN.MYTAB. If that same user issues the statement CREATE TABLE BOGUS.MYTAB ..., the table BOGUS.MYTAB (and implicitly the schema BOGUS) will be created, although there may be no user named bogus.

DB2 takes the concept of separation of duties quite seriously. For example, the database instance administrator, which presumably DB2ADMIN is, does not by default have access to data, except the tables created by that same user. There are a variety of GRANT statements that allow you to grant access to objects and data as necessary. You can read more about them in the documentation.


How to detect inactive tables? I started working on a project whose former developer ragequited, and it left a complete mess both in the code and database. The server is using MySQL 5.5

There are a lot of tables created for backup or test purposes. For example, from all this tables, only two are really being used.

Is there any way to check which tables weren't used in the last XX hours, to be able to safely delete them? Can I set a log for that, besides from the general log, which is really big and fills the available space too quickly?

I cant trust names as in some cases, the table being used is the one that ends with _test or _bk.

Unfortunately, using the update_time column from INFORMATION_SCHEMA.TABLES only works for MyISAM tables. It does not work for InnoDB.

The most effective way to get the last time a table was written is to rely on the OS. You must check the timestamp of the .ibd or .MYD files within a database folder.

I wrote earlier posts about how to do this:

Dec 21, 2011 : Fastest way to check if InnoDB table has changed
Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
Sep 25, 2014 : want to find out which databases are used in last 30 days or not
Jan 16, 2015 : How can I determine which Innodb table is being written?


Sync between two database on azure I would like to ask a question how to setup azure syncing of two database on azure

i need real time data replication to the other database

Depends on the Azure offering you want to use.

Azure SQL Database

You can get Geo-replication setup which is very straight forward and will give you a read-only secondary.

Azure Portal : Geo replication

Azure SQL Managed Instance : You can use regular transaction replication (under preview) or geo-replication.

Azure Managed Instance


Postgresql function for detecting alerts need help with PGSQL function for detecting temperature alerts.
Let's imagine we have tables

**measurment_tab**
id:int
room_id:int
measurment_date:timestamp
value:long

**alert_tab**
id:int
room_id:int
alert_value:long
date_add:timestamp
last_check_date:timestamp
alert_type:varchar [two possible values: below, over]

Last measurement value is:

10|2|2020-01-02 15:33:00|11.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

In this case function should detect alert when:

measurement value is over 12.7
each check I believe should updated last_check_date field to current value (now()) to limit the collection of values to check

I think this scenario is quite simple. Next one is more complicated.

Last measurement value is:

10|2|2020-01-02 15:33:00|13.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

As You can see current measurement value is over alert value. In this case alert shouldn't be triggered. Alert should be triggered when:

measurement value goes below alert value
after that measurement goes again over alert value

Thanks for advice.

If I'm understanding the question correctly?

I would run the alert code in a Insert Trigger, and I would  add a column to measurement_tab to track which alerts matched the measurement.

alter table measurement_tab add column trigger_alert_id integer[];

CREATE TRIGGER alert
BEFORE INSERT
ON measurement_tab
FOR EACH ROW
EXECUTE PROCEDURE _alert();

CREATE FUNCTION _alert()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$

declare _r record;

begin

for _r in select id from alert_tab
        where alert_tab.room_id = measurement_tab.room_id
        and value < alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

for _r in select id from alert_tab
        where alert_tab.room_id = measurement_tab.room_id
        and value > alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

Run_alert_function(new.trigger_alert_id);
         # this is where the other alert code
         # that emails/sends out the alerts gets fired off;
return new;
end;

$BODY$

If there is need to block alerts that have already been triggered.  Meaning once alert has been set it must be reset before it can be fired off again,   this can be done with additional logic in the loop,  that looks for previous entries in measurement_tab that set this alert previously.


How to control psql displaying rows with (END)? After my computer upgraded from Mac Sierra to High Sierra, my psql started exhibiting an annoying behaviour. Sometimes when it lists rows, it prints (END) instead of the prompt, and I have to press any button to stop go out of the display and get the prompt. It doesn't happen for every query, but it's not easy to predict when it happens. Sometimes it puts an (END) after 2 rows, but not after 15 rows.

What controls these aspects of psql, and how can I control it and turn it off? It's annoying that I have press a key for every query I run, or it swallows the first character and make me write "elect * from".

Additional input

It seems to trigger when the display of the rows is wider than the terminal window - i.e. it flows over multiple lines. I use the normal, built-in Terminal app for Mac.

The (END) is the mark by your pager (more or less).  Turn it off using

\pset pager off

inside psql.  I can imagine you had a ~/.psqlrc file earlier that had this line in it, or the default behaviour changed otherwise.  Just add the above command to this file to return to the desired behaviour.

Scroll down to 'Files' on the documentation page for more details about what can be done using this file.


Unable to restore database because it exists, unable to drop because it does not exist I've seen this mentioned a couple of times but none of the suggested solutions worked for me, so I'm posting a question together with a solution in hope that it helps someone else.

During restore of a development database I encountered:

SQL1005N  The database alias "XXXX" already exists in either the local
database directory or system database directory.

db2 drop db XXXX
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031

The solution suggested elsewhere is to catalog the database and then drop it:

db2 catalog db XXXX
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

db2 drop db XXXX
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031

So it appears as there's a zoombie database lurking around, that we can't get rid of. What to do?

Disclaimer, the following solution where for a development box. Use with caution. For a production like scenario, open a PMR.

It turned out that during a previous restore the file system got full and therefore not all files necessary got created. I cataloged the database:

db2 catalog db XXXX
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

and tried to connect to it (fails). In db2diag log there's an entry showing where he files where supposed to be located, in my case:

/data/db/db2/db2inst1/NODE0000/SQL00010

As it turned out this is the same location as shown in

db2 list db directory

So what I did was:

db2stop force

and then rsynced all files from another database:

#> cd /data/db/db2/db2inst1/NODE0000/SQL00010
#> rsync -av ../SQL00011/* .
#> db2start

Before dropping the database I tried connecting (for the fun of it) and got a new error:

#> db2 connect to XXXX
SQL0294N  The container is already in use.  SQLSTATE=42730

Dropping the database was possible though

#> db2 drop db XXXX
DB20000I  The DROP DATABASE command completed successfully.

As mentioned this was performed on a developer box with no important data, in a production environment I would have opened a PMR instead.


How can I use package classes and call packages from Oracle11gr2 stored procedure in certain schema? When a stored procedure begins with

4 create or replace procedure user0.import
5 is
6   f1 utl_file.file_type;

compiler throws an error Error(6,6): PLS-00201: identifier 'UTL_FILE' must be declared.

But if I replaced is with declare and ran the same as anonymous block selecting everything after line 4, it would work fine and import data as expected. This is runnning under SYS AS SYSDBA. Also it would compile fine if user0 schema was not used.

Why cannot I as sysdba compile a procedure in a user0 schema?

Try granting directly execute to utl_file to user0:

grant execute on util_file to user0;

Then try to recompile the code.


postgresql: make objects owned by parent role I have a set of apps that produce data. Each app has its own credentials to access the DB, and they all inherit from a common role (producer).

I'd like to set default privileges to grant access to objects created by a producer, but it does not work because objects are owned by each particular app's login, not by producer.

So: is there a way to have all tables created by these apps to be owned by producer not by their particular login ? Or, is there a way to write these default privileges differently ?

EDIT: and I'd like to avoid changing all apps to make them do a SET ROLE...

EDIT2: and... I'm not the only one: Automatically invoke `SET ROLE` when connecting to PostgreSQL, Inherit default privileges: every table created by *any member of* role A is readable by role B

No postgresql does not inherit permissions in this fashion,  Pretty much anything using the Create command sets the owner to the users that created it.

Use the GRANT command

Or after creating the tables set the Owner with Alter  command

Create table Mytable (list of columns);
Alter table Mytable  owner to producer;


Jump to a given line number in pgAdmin III I wonder whether there is any way to jump to a given line number in the "edit data" window in pgAdmin III.

I am aware that I can use the sort/filter window to add filters, but I just want to jump.

Following Vérace's comment, I filed a feature request: https://redmine.postgresql.org/issues/1429


Oracle Table Design, Storing Small Values In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm looking at a system where it was decided to store these values as VARCHAR2(11 CHAR), but it seems like the wrong option for a column type. From the online Oracle documentation and the almighty Google it seems like the more space optimal storage option with be CHAR(1) as it seems to be the smallest on disk type.

Am I completely wrong in my thinking or doesn’t it not matter either way?

The documentation states the size of datatypes here.

0 (the number) is stored as 1 byte. Other single digit numbers will be stored in 2 bytes (one for the exponent, one for the mantissa). You can test this yourself by creating a test table and using the VSIZE() function on test data (doc link).

A CHAR(n) will be stored in n bytes.

I'd always store numbers in a NUMBER. Unless you're dealing with trillions of rows, the space saving will not be significant enough.


Select max value from many to many tables i have next tables in may data base:

I want to select the book that has the max number of pages for each author. I did this :

select auteur.nomauteur, auteur.prenomauteur ,livre.Title, livre.nombrepages from auteur, livre, livreauteur
  where (livre.idlivre = livreauteur.idlivre and livreauteur.idauteur = auteur.idauteur and
            livre.NOMBREPAGES in (select max(livre.nombrepages) from auteur, livre, livreauteur
  where (livre.idlivre = livreauteur.idlivre and livreauteur.idauteur = auteur.idauteur)
            group by auteur.nomauteur, auteur.prenomauteur)) group by auteur.nomauteur, auteur.prenomauteur, livre.title, livre.NOMBREPAGES;

Correct answer:

But the expression is too long, and i need to do this select with JOIN. I tried to do like this:

select auteur.NOMAUTEUR, auteur.PRENOMAUTEUR, livre.TITLE, max(livre.nombrepages) from auteur inner join livreauteur on auteur.idauteur = livreauteur.idauteur inner join livre on livre.idLivre = livreauteur.IDLIVRE group by  auteur.NOMAUTEUR, auteur.PRENOMAUTEUR, livre.TITLE;

Wrong answer, 2 extra rows for the same author:

also I tried with:

select
        auteur.idauteur,
          (select top 1
            livre.idlivre
            from livreauteur
            inner join livre
            on livre.idlivre = livreauteur.idlivre
            where livreauteur.idauteur = auteur.idauteur
            order by livre.nombrepages desc )
            from auteur;

But it is wrong. How can I do the first select with JOIN ?

The question would have been a better candidate for stackoverflow.

Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title.

From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of pages.

You would also have to consider the possibility that multiple books can have the same number of pages and if that should result in all of them being returned or just 1.


SQL Server Min Server Memory using SQL 2008 R2 with SP2 with a server with 64 GB of RAM. I have set max server memory to 58GB considering SQL Server is limited to only database engine related service only.
I have a question related to setting min server memory

If I set min server memory to 48 GB does that mean

a) If windows sends low memory notification to SQL -- SQL server will trims all its pool and try to maintain memory usage up to 48 GB of memory?

b) In case of aggressive working set trimming by windows -- would it leave SQL server's memory usage to 48 GB setting a "floor" for SQL's memory usage.

c) Are there any recommendation on min server memory?

Searching the web -- internet has plenty of information for max server memory but only few place I read (Brent Ozar blog for configuration best practices) to set min server memory to 50% of total available RAM on the server.

I would suggest you to leave MIN Server memory to DEFAULT.
Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory
usage has increased beyond the Min Server Memory setting, SQL Server won’t release any
memory below that amount.
Bob Dorr explains this settings as :

Min Server Memory

Use the min server memory setting with care.   This is a floor to SQL Server.   Once committed memory to reach the min server memory setting SQL Server won't release memory below the mark.   If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won't drop below 56GB.    When you combine this setting with locked pages in memory the memory can't be paged.  This can lead to unwanted performance behaviors and allocation failures.

Searching the web -- internet has plenty of information for max server memory

This is because, this settings is least tuned (people just set it to default), instead max memory is what is generally tuned as it is the "ceiling" for the buffer pool. A good value for max memory will ensure that windows and other processes runing on the server will have enough physical memory to perform their work without forcing sql server to trim.


Suggestion for enforcing unique constraint in a table where soft delete is implemented I am using SQL Server 2014 and in my database, I have a Position table which is structured like this:

ID         int           PK
Name       nvarchar(50)  Unique Index
IsDeleted  bit

A user can't enter positions with similar names; IsDeleted is a flag for soft delete as I want to avoid deleting records from this table. However it got me thinking in a scenario like this:

1. User enters position 'President'.
2. User deletes position 'President'.
3. User changes mind and again enters a position 'President'.

This would result in an error due to unique constraint. Even if I use Name and IsDeleted combination for unique constraint, there is no stopping the user from deleting again and adding several times. One thing I can think of is having Name and TimeStamp combination as unique constraint, but it doesn't sound correct.

I'd like to enforce this if possible in the database, and not the application logic. Can you guys give some suggestions?

Why not somthing simple like this:

User enters position 'President' and it does not exist in the table.

Insert Into table

User deletes position 'President'.

Update table set isDeleted = 1

User changes mind and again enters a position 'President'.

Update table set isDeleted = 0


Deny access to one database, allow all the rest How can I deny access to specific databases from a login?  I know that I can walk through and make the login a db_owner on all the ones I want the login to have access to, but how do I do the opposite?  I want the login to have access to everything but the ones that I specify

I would highly suggest you not assign a login to db_owner just "to have access to" a database unless you want them to have "admin" privileges in that database.

You also have to consider what permissions the login has at the instance level first. Assigning a user to db_denyXXX will have no affect if the login is sysadmin or securityadmin privileges.

If we are talking about a "regular" login on the instance you prevent them from accessing the database by not mapping that login to a user in the database. If no database user exist in the database for the login then they cannot connect to it, as long as they do not have higher privileges at the instance level.


merge like rows then update fk from another table to match the new values I have 2 tables tb1 (id, name, record_no, location,...) tb2(id, test, date,...)

Tb1 is joined to tb2 on tb1.id = tb2.id

The issue is that TB1 has duplicated entry's that have new ids. I need to merge these ids into one 1 for each unique entry then update tb2.id to match the change.

Not sure of the most effective way to do this without having to manually update each row.

Before

SELECT * FROM TB1

ID, NAME,    DOB,        RECORD_NUM
1, John Doe, 01/01/1900, 123456789
2, John Doe, 01/01/1900, 123456789
3, Jane Doe, 11/03/2016, 294018400
4, Jane Doe, 11/03/2016, 294018400
...

SELECT * FROM TB2

ID, Test,    Result, Date
1,  English, Pass,   01/01/1900
1,  Grammer, Fail,   01/02/1900
2,  Gym,     Pass,   01/01/1900
3,  Art,     Pass,   11/02/2016
4,  Gym,     Pass,   11/03/2016
...

Basically I need to take row ID 2 and merge it will ID 1 from TB1 then where 2 appears in TB2 I need to update it to 1.

I know the entries are the same usually by the Record_num or if that is a null value I can use the name and dob (since together they should be unique in the set).

After

SELECT * FROM TB1

ID, NAME,    DOB,        RECORD_NUM
1, John Doe, 01/01/1900, 123456789
3, Jane Doe, 11/03/2016, 294018400
...

SELECT * FROM TB2

ID, Test,    Result, Date
1,  English, Pass,   01/01/1900
1,  Grammer, Fail,   01/02/1900
1,  Gym,     Pass,   01/01/1900
3,  Art,     Pass,   11/02/2016
3,  Gym,     Pass,   11/03/2016
...

I hope this helps explain a little more.

You could update the second table first, then delete the duplicate (and now unreferenced) rows from the first table.

The (PARTITION BY name, dob, record_num) is what identifies rows as duplicates. If more or less columns are needed to identify then, adjust accordingly.

It would be good to put the two statements in a transaction to avoid weird effects / errors if other sessions access the table (inserting new rows or deleting between the 2 statements may result in the 2nd one to fail or having unreferenced rows in the end:

WITH ids AS
( SELECT dup_id = id,
         good_id = MIN(id) OVER (PARTITION BY name, dob, record_num)
  FROM tb1
)
UPDATE t2
SET t2.id = i.good_id
FROM tb2 AS t2
  JOIN ids AS i
    ON i.dup_id = t2.id
WHERE i.dup_id <> i.good_id ;

WITH ids AS
( SELECT dup_id = id,
         good_id = MIN(id) OVER (PARTITION BY name, dob, record_num)
  FROM tb1
)
DELETE d
FROM tb1 AS d
  JOIN ids AS i
    ON i.dup_id = d.id
WHERE i.dup_id <> i.good_id ;

Tested a rextester.com

The 2nd statement could have been written more simply but I find the above 1st way slightly more readable as the 2 statements have almost identical FROM and WHERE clauses.

DELETE i
FROM
      ( SELECT dup_id = id,
               good_id = MIN(id) OVER (PARTITION BY name, dob, record_num)
        FROM tb1
      )               -- the ids CTE rewritten as a derived table
      AS i
WHERE i.dup_id <> i.good_id ;


Deleting all rows from INNER JOINed 3 different tables I have a query. I would like to delete all selected rows in these 3 different tables
Since I have a lot of INNER joins, I couldn't figure it out.
My goal is to delete everything for these seller_ids.

SELECT *
FROM orders a
 INNER JOIN order_items b ON a.order_id = b.order_id
 INNER JOIN order_item_histories c ON c.order_item_id = b.order_item_id
WHERE a.seller_id IN (1, 3)

Version Postgres 10.3

I tried this but I couldn't succeed.

DELETE
FROM
   USING orders
   USING order_items,
   USING order_item_histories
WHERE orders.order_id = order_items.order_id AND order_items.order_item_id = order_item_histories.order_item_id
AND  orders.seller_id IN (1, 3)

If you have more than one join you could use comma separated USING statements:

DELETE
FROM
      AAA AS a
USING
      BBB AS b,
      CCC AS c
WHERE
      a.id = b.id
  AND a.id = c.id
  AND a.uid = 12345
  AND c.gid = 's434sd4'

Reference


Find database user name from sysprocesses I know that this is a strange requirement but I need to find out database user name(not the login name) from sys.sysprocesses for all the currently running processes. For e.g. select spid,dbid, uid from sysprocesses where uid corresponds to database user in that particular database. So what I want is one more column which can get me database user name from uid. Please let me know if this is possible with just a simple select query.

Logins (on the server) do not automatically correspond to users (in the database). For instance, "sa" does not have its own user in the database, but uses "dbo". This might get you started.

SELECT s.session_id,
       sp.[type], sp.type_desc,
       sp.[name] AS loginName, dp.[name] AS userName,
       sp.is_disabled
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.database_principals AS dp ON s.security_id=dp.[sid]
LEFT JOIN sys.server_principals AS sp ON s.security_id=sp.[sid];

Note: sysprocesses, sysusers, etc are deprecated and will probably stop working in a future version of SQL Server.

I go into a bit more detail on SQL Server security in my blog post.


Recursive query using plpgsql I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.

I have a data table and a table to link the data together:

DATATABLE
-----------
id integer
value text
info text

LINKTABLE
-----------
link integer
parent integer

My thought was to do like in the following function:

CREATE OR REPLACE FUNCTION my_function(itemID integer)
  RETURNS TABLE(id integer, value text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT my_function(A.link) FROM linktable A, datatable B
        WHERE A.parent = B.id AND B.id = itemID) C;

    RETURN QUERY SELECT id, value FROM datatable WHERE id = itemID;
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

But that doesn't work, I get an error in my first query:

  ERROR:  structure of query does not match function result type

My Just-In-Brain compiler detects no problems, so what am I doing wrong here?

You don't need a the function at all, this can be done with a single SQL statement:

with recursive tree as (id, parent) (
    select link as id,
           parent
    from linktable
    where id = itemid

    union all

    select c.link as id,
           c.parent
    from linktable c
      join tree p on p.id = c.parent
)
select dt.id, dt.value
from tree
  join datatable dt on dt.id = tree.id

Please see the manual for an introduction to recursive queries: http://www.postgresql.org/docs/current/static/queries-with.html


Heterogeneous queries error using stored procedure Stored procedure

ALTER    PROCEDURE Proc_DayShift
AS
insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO

The above stored procedure is showing error as

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query
  semantics. Enable these options and then reissue your query.

I added the server1 from server2 using linked server, when I run the query like this

SELECT * FROM SERVER2.MARS.DBO.TABLE1

It's executing and showing results.

When I run the query using stored procedure, it is showing this error message.

How to rectify this issue.  what wrong in my query...?

The message mentions two settings.  The first setting, ansi_nulls, is remembered from when the stored procedure was created.  Make sure it's on when you run create procedure:

set ansi_nulls on
go
create procedure YourProc as ...

The second setting, ansi_warnings, should be enabled on the connection that calls the procedure:

set ansi_warnings on
exec YourProc

Or alternatively, set ansi_warnings inside the procedure:

create procedure YourProc
as
set ansi_warnings on
...

For more information see this MSDN article:

  Stored procedures execute with the SET settings specified at execute
  time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored
  procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the
  setting specified at stored procedure creation time. If used inside a
  stored procedure, any SET setting is ignored.


How does innodb_stats_on_metadata relate to query execution plan stability? The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables."  That is a little vague.  What is an example scenario related to this statement?

Here is something you may find surprising

Pick any large InnoDB table. Let's say the table is mydb.mytable.

Run this

SHOW INDEXES FROM mydb.mytable;

If you run this query repeatedly, you will notice that the Cardinality column changes. That happens because the InnoDB Storage Engine tries to compute (estimate) the Cardinality based on the BTREE indexes and their leaves rather than brute force counting.

Disabling innodb_stats_on_metadata makes the Cardinality stand still to compute a consistent Explain Plan.

I wrote about this before

Can SHOW TABLE STATUS query performance on MySQL be improved - Amazon RDS
Why are these queries slow in some environments and not others? Can we fix their performance?
When are InnoDB table index statistics updated?
MySQL IS NULL / IS NOT NULL Misbehaving?


Force spool to avoid creating temp tables? I have the following query which join pivoted data of several very big tables and it runs very slow. I tried to created the temp table for each pivoting and it runs much faster. Is it a way to force index/table spooling without create temp tables?

select ...
from T1 ....
left join (select ... from (select ... from T2....) pivot (sum(C1) on C2 in (...))) p1
    on ...
left join (select ... from (select ... from T3....) pivot (sum(...) on ... in (...))) p2
    on ...
left join (select ... from (select ... from T4....) pivot (sum(...) on ... in (...))) p3
    on ...

A very similar question was asked and answered on StackOverflow: How can I force a subquery to perform as well as a #temp table?  To summarize,

The most reliable method is simply to use a #temp table and materialize it yourself.
Failing that, see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ... ORDER BY can often encourage the result to be spooled rather than repeatedly re evaluated.  Even if that works however there are no statistics on the spool.
Another option is to use a user-defined function.  Multi-statement functions (as described in How to Share Data between Stored Procedures) appear to force the SQL Server to materialize the results of your subquery.  In addition, they allow you to specify primary keys and indexes on the resulting table to help the query optimizer.  This function can then be used in a select statement as part of your view.  For example:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
BEGIN
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   RETURN
END

CREATE VIEW SalesData As
SELECT * FROM SalesByStore('6380')


SSIS Derived Column Casting to DT_STR Why can't I get this to cast as a DT_STR - the data source is WSTR, but shouldn't this cast make the output DT_STR?

(IPUNSPSC) =="**no_UNSPSC_available**" ? (DT_STR,20,1252)NULL(DT_STR,20,1252) : (DT_STR,20,1252)TRIM(IPUNSPSC)

It's as @RDFozz identified in their comment.

(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))

The ternary operator ? : doesn't behave as one might expect. You casted the two resulting values as DT_STR but that's not where SSIS picks up the metadata. Instead, you need to cast the entire result of the ternary operator to a given type.

The following Biml generates a sample SSIS package with an OLE DB Query to present the IPUNSPSC column and then a Derived Column component to manipulate the data as expected.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="SRC" ConnectionString="Provider=SQLNCLI11;Server=localhost\dev2016;Initial Catalog=tempdb;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="dba_178090">
            <Tasks>
                <Dataflow Name="DFT Demo">
                    <Transformations>
                        <OleDbSource Name="OLESRC Query" ConnectionName="SRC">
                            <DirectInput>SELECT '**no_UNSPSC_available**' AS IPUNSPSC UNION ALL  SELECT '***********************';</DirectInput>
                        </OleDbSource>
                        <DerivedColumns Name="DER Demo">
                            <Columns>
                                <Column Name="Derived" DataType="AnsiString" Length="20" CodePage="1252">(DT_STR,20,1252)((IPUNSPSC) =="no_UNSPSC_available" ? NULL(DT_WSTR,20) : TRIM(IPUNSPSC))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!-- Placeholder transformation -->
                        <DerivedColumns Name="DER Bit Bucket" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>


Check all tables in a schema for X fields? I want to make sure that all of the tables in my schema have fields named "updateddate" and "createddate". Obviously, I could check them manually, but how can I generate a list of tables that are missing these fields? Even better, how can I use the resulting list and generate the SQL needed to create the missing column for that table?

DECLARE @sch SYSNAME = N'my_schema_name';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) + '.' + QUOTENAME(t.name)
  + ' ADD createddate DATETIME
      NOT NULL DEFAULT GETDATE();'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = N'createddate'
  );

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) +'.' + QUOTENAME(t.name)
  + ' ADD updateddate DATETIME;'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = N'updateddate'
  );

PRINT @sql;
--EXEC sys.sp_executesql @sql;

To automate this a little further, you can use a table variable or #temp table (no such thing as an array in SQL Server):

DECLARE @cols TABLE
(
  col SYSNAME,
  ctype SYSNAME,
  def SYSNAME
);

INSERT @cols(col,ctype,def) VALUES
(N'createddate', N'DATETIME', N'NOT NULL DEFAULT GETDATE()'),
(N'updateddate', N'DATETIME', N'NULL'),
(N'somethingelse', N'VARCHAR(32)', N'');

DECLARE @sch SYSNAME = N'my_schema_name';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) + '.' + QUOTENAME(t.name)
  + ' ADD ' + QUOTENAME(cols.col) + ' ' + cols.ctype
  + ' ' + cols.def + ';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS JOIN @cols AS cols
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = cols.col
  );

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that in both cases above, depending on the number of tables that don't have these columns, PRINT might truncate the output it shows you, but this is due to an inherent 8K limitation of PRINT (or less data depending on your settings in SSMS). I can assure you the command it will execute doesn't end abruptly like that.


Is there a means to set the owner of all objects in a PostgreSQL database at the same time? https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created.

Is there a fairly easy way to reset the owner of ALL objects in the database, including the functions? Doing it by hand is highly undesirable.

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair.

Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. Are you sure there are no system functions affected or functions installed by an additional module?
It would also be pointless to change the owner of functions that already belong to the designated owner.

First, check if REASSIGN OWNED could work for you:

  change the ownership of database objects owned by a database role

You have to list all roles to be disowned explicitly. But it also reassigns functions.

To assign all functions (and no other objects) in a given schema to a new owner (optionally regardless of previous owner):

SELECT string_agg('ALTER FUNCTION ' || oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';
-- AND p.relowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

This generates the canonical SQL commands ALTER FUNCTION ... to change all functions (in the specified schema). You can inspect the commands before executing them - one by one or all at once:

ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;
...

I included some commented WHERE clauses you might want to use to filter the results.

The cast to regprocedure produces a valid function name with parameters, double-quoted where necessary, schema - qualified where necessary for the current search_path.

The aggregate function string_agg() requires PostgreSQL 9.0 or later. In older version substitute with array_agg() and array_to_string().

You could put all of this into a DO statement or a function like demonstrated in this related answer:

Drop all functions in a namespace? (Execute generated DDL commands?)

In Postgres 9.5 or later, you can simplify the query using new object identifier types regnamespace and regrole:

SELECT string_agg('ALTER FUNCTION '|| oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc
WHERE  pronamespace = 'public'::regnamespace;
-- AND relowner <> 'foo'::regrole
-- AND proname ~~ 'f_%'


Is there a server option which limits the amount of rows returned by a remote query? I have an issue where a system on our network is making a query (select * from table) which might result in 0 to 70k rows being returned.

For large result sets, say around 50k, the calling system is being notified that a DB error has occurred & the query is terminated (presumably by the server). It's working with smaller result sets. The exact 'tipping point' hasn't yet been identified.

Is there a config setting in SQL Server 2012 which sets a max limit on how many rows will be returned for a query?

You can use OFFSET and FETCH to effectively page through results

e.g.

SELECT * FROM MyTable ORDER BY MyColumn OFFSET 100 FETCH NEXT 100 ROWS ONLY

Helpful article here...

Article About OFFSET FETCH

Though i would look into why it fails to return large result sets 50k isn't a lot in modern data.


SQL Server R2 Standard Edition MAXDOP setting I'm looking for confirmation/guidance on setting MAXDOP in a SQL Server instance with the
following config:

Version      : SQL Server 2008 R2 Standard Edition
Processor    : 2 x AMD Opteron(TM) Processor 6234 = 24 cores
Hyperthreading enabled

For this processor each individual socket has two NUMA nodes, each of which has 6 cores in it. Microsoft’s KB article, Recommendations and guidelines for the “max degree of parallelism” configuration option (http://support.microsoft.com/kb/2806535) recommends that
“For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.” so I would want to set MAXDOP to 6 (the number of cores in one NUMA node.)

However, Microsoft’s documentation on the max degree of parallelism Option http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx tells me that the maximum value that can be used for MAXDOP in SQL Server 2008 R2 Standard Edition is 4.

So, i’m guessing that the version  recommendation of MAXDOP 4 overrides the processor/NUMA recommendation of MAXDOP=6? Anyone else have this configuration and know what happens if I try and set MAXDOP to 6 regardless?

Standard edition will not generate a parallel plan that uses more than 4 processors i.e. MAXDOP 4. Setting MAXDOP to a value higher than this will not alter the limitation imposed by the edition. You get no warning or error when setting the higher value, it will be accepted.


DB server VM's CPU configuration - 1x2Ghz or 2x1Ghz - which is better We want to set up DB server in cloud hosting for MS SQL Server Express edition - OLTP workload for "just" 6 concurent users - mostly short and small transactions.

Hosting company is charging each 1Ghz of CPU. Concerning CPU configuration, would you go 1 x 2Ghz or 2 x 1Ghz (logical cores in VM)?

Similar question is answered here: CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?

My question is a bit different / specific in a way I'm choosing between just one core with higher speed or two with lower speed. So the scale is far different.

My question is a bit different / specific in a way I'm choosing between just one core with higher speed or two with lower speed. So the scale is far different.

Technically, more cores is always better if either the software is multithreaded, or multi-process (workers). As far as the hardware is concerned

More cores means more instructions are processed at the same time.

including instruction fetching and decoding, execution, memory access (cached, or out to bus), writes. You can read more about this on Instruction pipelining
you have a different pipeline for each core.
each core can with a full pipeline do more than one thing at a time. Modern Intel CPUs post-Sandy Bridge have a 14 stage pipeline.

Fewer cores, regardless of speed, mean fewer multi-stage pipelines to fill and fewer things can be done in parallel.

As far as the software side

Threads and processes can be managed by the kernel.
Most databases will run at the very least the WAL-log on another thread then the execution/query engine
Software that is multithreaded, will do very poorly on a single-threaded CPU.

For these reasons, the answer from the hardware and software side is clearly in favor of more cores (so long as your use case is provided for by the operating system and application as subject to Amdahl's law). Microsoft though isn't free, and brings to the table expensive licensing that needs to be factored into the total cost of ownership. This can tie your hands, but plays into the value of the product. As far as licensing requirements, check out this answer by Max.


What is the path of oratab on solaris? What is the path of oratab on solaris?

This is actually a question I google a lot and switching from environment to another, I tend to mix them up and forget.

Today I googled it again and I wanted it in a place where I usually have all the answers.


Counting Grouped records, using start date and end date with NULLs Here is my issue, Source DB is Azure MSSQL - I have a table that lists all the subscriptions sold. The Relevant columns are:
Account Number, Subscription Group, Purchase Date, Cancellation Date
Cancellation Date contains NULL values (for Subscriptions that have yet to be cancelled). An Account may have multiple subscriptions, across multiple subscription groups.
Sample data is:
Account number | Subscription group | Purchase date | Cancellation date |
1 | A | 2/4/2013 | 2/4/2014 |
1 | A | 2/4/2013 | NULL |
1 | B | 2/4/2013 | NULL |
2 | C | 5/7/2018 | 5/8/2020 |
3 | C | 20/3/2020 | NULL |
3 | C | 4/8/2020 | NULL |
Etc.
I am looking to count the number of Subscription Groups for each customer and for each month - so the output would look something like this:
Customer | Count of A | Count of B | Count of C | Month/Year |
1 | 1 | 1 | 0 | 10/2020 |
3 | 0 | 0 | 2 | 10/2020 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |
I can write the query for a given month:
select t.[account number],
COUNT(CASE WHEN t.[Subscription group] = A then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN t.[Subscription group] = B then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN t.[Subscription group] = C then 1 ELSE NULL END) as "Count of C",
'09/2020' as 'Month/Year'
from table t
where (t.[Purchase Date] <= '30/09/2020' and (t.[Cancellation Date] is null or t.[Cancellation Date] > '01/10/2020') )
Group by t.[Account Number], [Month/Year]
However, I'm hoping someone can help me modify the above so it will run for All months (based on MIN from Purchase date and MAX on Cancellation date)

I ended up doing this using a Stored Procedure, creating a Temp Table:
DECLARE @StartDate  DATE,
        @EndDate    DATE;

SELECT   @StartDate = (select min([purchase date]) from Table where [Purchase Date] != '1970-01-01')
         ,@EndDate  = (select max([Cancellation date]) from Table);

;with months (date)
AS
(
    SELECT @startdate
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @enddate
)
select     IDENTITY(int, 1, 1)  as ID,
           [LastDayOfMonth]  = EOMONTH(date),
           [FirstDayOfNextMonth] = DATEADD(DAY, 1, EOMONTH(date)),
           [date] = date
into #tempdatetable
from months
option (maxrecursion 0)

Then doing a Loop through that Table and populating an output table:
Declare @Id int
Declare @ldom date
declare @fdonm date
declare @my date

While (Select Count(*) From #tempdatetable) > 0
Begin

    Select Top 1 @Id = Id,
    @ldom = LastDayOfMonth,
    @fdonm = FirstDayOfNextMonth,
    @my = [date]
    From #tempdatetable

insert into MyOutputTable (Customer,
[Count of A],
[Count of B],
[Count of C],
[Month/Year])
select T.[Customer],
COUNT(CASE WHEN T.[Subscription group] = 1 then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as "Count of C",
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] <= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] > @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable


MySQL 8.0 table with JSON column that uses JSON "merge" operations has 500k rows but analyze table shows 7MM I have a database table that according to TABLE STATUS has 7MM rows, but when I SELECT COUNT(*) it only has 500k rows.

This is a problem because table growth is increasing and we're running low on storage now.

here is schema: MySQL 8.0.15

CREATE TABLE `tasks` (
  `task_id` binary(24) NOT NULL,
  `task` json NOT NULL,
  `task_kryo` mediumblob,
  `task_type` varchar(180) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$.t'))) STORED,
  `created` datetime GENERATED ALWAYS AS (cast(left(json_unquote(json_extract(`task`,_utf8mb4'$._task.timestamp')),19) as datetime)) STORED,
  `last_updated` datetime GENERATED ALWAYS AS (cast(left(json_unquote(json_extract(`task`,_utf8mb4'$._task.latestStatus.timestamp')),19) as datetime)) STORED,
  `latest_status` varchar(180) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$._task.latestStatus.t'))) STORED,
  `marker` binary(24) GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$.marker'))) STORED,
  PRIMARY KEY (`task_id`),
  KEY `task_type_index` (`task_type`),
  KEY `created_index` (`created`),
  KEY `last_updated_index` (`last_updated`),
  KEY `latest_status_index` (`latest_status`),
  KEY `marker_index` (`marker`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

This is MySQL RDS on AWS. We have system backups disabled because this table is entirely disposable data. As a result, there is no binary log for this table. AWS disables that when you disable system backups apparently

My suspicion is that we use JSON "Merge" operations in Update statements, and because binary logging is disabled by AWS (we don't have any backups for this table as it is fully disposable / scratch data) somehow it is implementing the updates as inserts on the table (old records remain but are not deleted).

See
https://mysqlhighavailability.com/efficient-json-replication-in-mysql-8-0/

binlog-row-value-options=PARTIAL_JSON

Also, the MySQL log contains this warning on restart:

2020-05-21T20:02:08.506526Z 0 [Warning] [MY-013103] [Server] When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.

This has more to do with InnoDB

As stated in the documentation to table status

  The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

So COUNT(*) gives you the exact number.


MySQL RDS Error 1062 Normally we can skip 1062 duplicate error  but in rds can we skip multiple 1062 errors in one go.

For example we define slave skip error =1062 in my.cnf and restart slave. but in RDS MYSQL how can we permanently skip 1062 error, alteast for few hours as we have duplicate data in the restore file and unfortunately log position we have is old.

If you cannot use the RDS CLI, set proper DB Parameters or other means to set slave-skip-errors=1062, you are better off just creating a read replica to make a new Slave from the Master then dropping the old Slave that had the 1062 error.

I have mentioned MySQL RDS Read Replicas in the past

May 21, 2013 : Best strategies to have a backup of an RDS MySQL database
Jul 25, 2012 : Scaling Percona datacenters: setup and replication

After creating read replica, you should make sure you are using INSERT IGNORE or INSERT ... ON DUPLICATE KEY commands on the Master to prevent duplicate keys from replicating to Slaves.


SQL Server compact- Query that return 2 constant columns even if query didn't return data Please look at the following simple query:

Select CustomerID
From Customers c
Where  c.CustomerId = "1234"

I want to write similar query that does the following:

Always return 2 columns with constant string instead of CustomerID in this structure:

If value was found then return 2 columns:
Found Value in DB, CustomerId

Else return:
Didn't found value in DB, CustomerId

How can I do that? Especially in Microsoft compact SQL server limited syntax...

Derived tables and Case statements are supported in SQL CE, so you can use this script

Select
case
when s.custs = 0 then 'Didnt found value in DB'
else 'found value in DB'
end
,'customer id'
from
(select COUNT(*) as 'custs' from Customers c Where  c.CustomerId = 1234) s

for reference check what can and what cannot be used in SQL CE


Because the database has to run additional recursive queries the first time it encounters a query, to parse it. It has to check if the given SQL is valid, it has to check the optimizer statistics, etc.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set statistics_level=all;

Session altered.

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  3ghpkw4yp4dzm, child number 0
-------------------------------------
select count(*) from hr.employees

Plan hash value: 3580537945

-------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |              |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |      1 |    107 |    107 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------

14 rows selected.

SQL> select buffer_gets, executions from v$sql where sql_id = '3ghpkw4yp4dzm';

BUFFER_GETS EXECUTIONS
----------- ----------
        220          1

We can clearly see from the plan statistics that it required a single buffer get, yet V$SQL shows 220. But now the query is parsed, if I run it again, buffer gets are increased only by the amount the query really uses:

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL> select buffer_gets, executions from v$sql where sql_id = '3ghpkw4yp4dzm';

BUFFER_GETS EXECUTIONS
----------- ----------
        222          3

If you are interested what additional SQLs the database executes when parsing the query, you can enable SQL tracing and view the trace file:

SQL> select tracefile from v$process where addr =
     (select paddr from v$session where sid = sys_context('userenv', 'sid'));

TRACEFILE
-------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ss18/SS18/trace/SS18_ora_14811.trc

SQL> alter system flush shared_pool;

System altered.

SQL> exec dbms_session.session_trace_enable(true, true);

PL/SQL procedure successfully completed.

SQL> -- run your query here, then view the trace file


Reconfig replSet issue in MongoDB I used to try a replica set demo in same db folder. The replica set name is "test". Then I tried to re-build repl set with different name "repl". After i reconfig, I get error message below:

{
        "ok" : 0,
        "errmsg" : "replSetReconfig should only be run on PRIMARY, but my state is REMOVED; use the \"force\" argument to override",
        "code" : 10107,
        "codeName" : "NotMaster"
}

So I follow the errmsg, and try to run the command below:

rs.reconfig(config,{"force":true})

Now I get following error message:

{
        "ok" : 0,
        "errmsg" : "New and old configurations differ in replica set name; old was test, and new is repl",
        "code" : 103,
        "codeName" : "NewReplicaSetConfigurationIncompatible"
}

too bad! So I use the previous name "test". But I get the error again.

{
        "ok" : 0,
        "errmsg" : "New and old configurations differ in the setting of the arbiterOnly field for member 127.0.0.1:27020
; to make this change, remove then re-add the member",
        "code" : 103,
        "codeName" : "NewReplicaSetConfigurationIncompatible"
}

I just really don't understand, if every configure information has to be consistent with the previous, why do we need to reconfig it?

Someone said you can drop local database, but this operation still doesn't work.

 {
            "ok" : 0,
            "errmsg" : "Cannot drop 'local' database while replication is active",
            "code" : 20,
            "codeName" : "IllegalOperation"
    }

Oh, MGD! I just couldn't understand why it is so difficult to restart a replset in current db folder.

As per MongoDB BOL Here To Reconfigures an existing replica set, overwriting the existing replica set configuration. To run the method, you must connect to the primary of the replica set.

rs.reconfig(configuration, force)

To reconfigure an existing replica set, first retrieve the current configuration with rs.conf(), modify the configuration document as needed, and then pass the modified document to rs.reconfig().

rs.reconfig() provides a wrapper around the  replSetReconfig command.

The force parameter allows a reconfiguration command to be issued to a non-primary node.

  WARNING: Avoid reconfiguring replica sets that contain members of different MongoDB versions as validation rules may differ across
  MongoDB versions.

The rs.reconfig() shell method can trigger the current primary to step down in some situations. When the primary steps down, it forcibly closes all client connections. This is by design. Since it may take a period of time to elect a new primary, schedule reconfiguration changes during maintenance periods to minimize loss of write availability.

{ force: true }

  WARNING:
  Using rs.reconfig() with { force: true } can lead to rollback
  of committed writes. Exercise caution when using this option.

Member Priority and Votes
Changed in MongoDB version 3.2.

Members with priority greater than 0 cannot have 0 votes.

Non-voting members must have priority of 0.

For example A replica set named rs0 has the following configuration:

{
   "_id" : "rs0",
   "version" : 1,
   "protocolVersion" : NumberLong(1),
   "members" : [
      {
         "_id" : 0,
         "host" : "mongodb0.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

         },
         "slaveDelay" : NumberLong(0),
         "votes" : 1
      },
      {
         "_id" : 1,
         "host" : "mongodb1.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

         },
         "slaveDelay" : NumberLong(0),
         "votes" : 1
      },
      {
         "_id" : 2,
         "host" : "mongodb2.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

         },
         "slaveDelay" : NumberLong(0),
         "votes" : 1
      }
   ],
   "settings" : {
      "chainingAllowed" : true,
      "heartbeatIntervalMillis" : 2000,
      "heartbeatTimeoutSecs" : 10,
      "electionTimeoutMillis" : 10000,
      "catchUpTimeoutMillis" : 2000,
      "getLastErrorModes" : {

      },
      "getLastErrorDefaults" : {
         "w" : 1,
         "wtimeout" : 0
      },
      "replicaSetId" : ObjectId("58858acc1f5609ed986b641b")
   }
}

The following sequence of operations updates the members[n].priority of the second member. The operations are issued through a mongo shell connected to the primary.

cfg = rs.conf();
cfg.members[1].priority = 2;
rs.reconfig(cfg);

As the above statement working in such as

The first statement uses the rs.conf() method to retrieve a document
containing the current configuration for the replica set and sets
the document to the local variable cfg.
The second statement sets a members[n].priority value to the second
    document in the members array. For additional settings, see replica
    set configuration settings.

To access the member configuration document in the array, the
statement uses the array index and not the replica set member’s
`members[n]._id` field.

The last statement calls the rs.reconfig() method with the modified
    cfg to initialize this new configuration. Upon successful
    reconfiguration, the replica set configuration will resemble the
    following:

{
   "_id" : "rs0",
   "version" : 2,
   "protocolVersion" : NumberLong(1),
   "members" : [
      {
         "_id" : 0,
         "host" : "mongodb0.example.net:27017",
         "arbiterOnly" : false,
         "buildIndexes" : true,
         "hidden" : false,
         "priority" : 1,
         "tags" : {

     },
     "slaveDelay" : NumberLong(0),
     "votes" : 1
  },
  {
     "_id" : 1,
     "host" : "mongodb1.example.net:27017",
     "arbiterOnly" : false,
     "buildIndexes" : true,
     "hidden" : false,
     "priority" : 2,
     "tags" : {

     },
     "slaveDelay" : NumberLong(0),
     "votes" : 1
  },
  {
     "_id" : 2,
     "host" : "mongodb2.example.net:27017",
     "arbiterOnly" : false,
     "buildIndexes" : true,
     "hidden" : false,
     "priority" : 1,
     "tags" : {

     },
     "slaveDelay" : NumberLong(0),
     "votes" : 1
  }

],
   "settings" : {
      "chainingAllowed" : true,
      "heartbeatIntervalMillis" : 2000,
      "heartbeatTimeoutSecs" : 10,
      "electionTimeoutMillis" : 10000,
      "catchUpTimeoutMillis" : 2000,
      "getLastErrorModes" : {

  },
  "getLastErrorDefaults" : {
     "w" : 1,
     "wtimeout" : 0
  },
  "replicaSetId" : ObjectId("58858acc1f5609ed986b641b")

}
}

  Note : When updating the replica configuration object, access the replica set members in the members array with the array index. The
  array index begins with 0. Do not confuse this index value with the
  value of the members[n]._id field in each document in the members
  array.

For help you can type in mongo shell rs.help().

> rs.help()
        rs.status()                                { replSetGetStatus : 1 } checks repl set status
        rs.initiate()                              { replSetInitiate : null } initiates set with default settings
        rs.initiate(cfg)                           { replSetInitiate : cfg } initiates set with configuration cfg
        rs.conf()                                  get the current configuration object from local.system.replset
        rs.reconfig(cfg)                           updates the configuration of a running replica set with cfg (disconnects)
        rs.add(hostportstr)                        add a new member to the set with default attributes (disconnects)
        rs.add(membercfgobj)                       add a new member to the set with extra attributes (disconnects)
        rs.addArb(hostportstr)                     add a new member which is arbiterOnly:true (disconnects)
        rs.stepDown([stepdownSecs, catchUpSecs])   step down as primary (disconnects)
        rs.syncFrom(hostportstr)                   make a secondary sync from the given member
        rs.freeze(secs)                            make a node ineligible to become primary for the time specified
        rs.remove(hostportstr)                     remove a host from the replica set (disconnects)
        rs.slaveOk()                               allow queries on secondary nodes

        rs.printReplicationInfo()                  check oplog size and time range
        rs.printSlaveReplicationInfo()             check replica set members and replication lag
        db.isMaster()                              check who is primary

        reconfiguration helpers disconnect from the database so the shell will display
        an error, even if the command succeeds.

For your further ref Here and Here


Greater than operator ignoring a nonclustered index? I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column.

When I look at the execution plan for the following query it seeks the nonclustered index.

SELECT TOP 1 *
FROM Subs
where sDate = '2012-04-12'

However when i look at the execution plan for this query it does not seek the nonclustered index.

SELECT TOP 1 *
FROM Subs
where sDate > '2012-04-12'

If I force it to use the index the query works and is quick, but if I do not force it, it takes forever. Why isn't SQL using the index on its own?

Ensure your statistics are updated for your table and indexes.

Current statistics are crucial for determining the correct plan.

In your plan if the "Actual Number of Rows" and the "Estimated Number of Rows" are fairly close then your statistics are ok.

If they need updated try using a FULLSCAN - sql server uses it's own sample size but this can sometimes be too small.


List default column values for all tables in a database I would like to list the default values for all columns in all tables in an Oracle database. The objective is to review the default values, and find any that need updating.

There are hundreds of tables in the database with columns that have default values, so it would be preferable to do this programmatically, rather than manually.

How can I do this?

Check user_tab_cols(all_tab_cols/dba_tab_cols).  view . DATA_DEFAULT column, as its' name implies, holds default value for the column.


Postgres. Generate continuous series for multiple categories I have a table that looks like this:

update_date  start_date   end_date   category_1 category_2    value
2013-03-04   2013-05-11  2013-06-08     a1           b1        23
2013-02-19   2013-03-16  2013-09-21     a2           b2       112
..

Now i want to transform the above table to this format:

date   category_1   category_2   value   update_date

For each pair of (category_1, category_2) I want to have a continuous series such as date is between start_date and end_date for each pair of (category_1, category_2) and keeping the respective value and update_date.

For example, for (a1, b1) I would have:

date       category_1  category_2   value   update_date
2013-05-11    a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
.             a1          b1          23     2013-03-04
2013-06-08    a1          b1          23     2013-03-04

and this table would continue for each pair of (category_1, category_2).

I reckon there'd be duplicates of values in date column but i'm OK with that as long as there are no duplicates of (category_1, category_2) pair.

Anybody knows how to solve this?

Thank you in advance.

You can use generate_series() function:

select
    d.d::date as date,
    t.update_date,
    t.category_1,
    t.category_2,
    t.value
from
    t,
    generate_series(start_date, end_date, interval '1 day') as d(d) ;

It works basically by taking each row from t and producing a set of rows, one for each day from start_date up to (and including) end_date.

Test at dbfiddle.uk


Syntax error in setting unused column I have a table named e_emp3 in Oracle with these fields among others:

FNAME VARCHAR2(20 BYTE)
SAL   NUMBER(8,2)
DEPTNO    NUMBER(4,0)

I need to alter e_emp3 and set unused column(sal) with online keyword to stay dml operations for users on this table. I wrote this code:

alter table e_emp3 set unused(sal) online;

but I got the following error message

Please help me what is problem? When I run this code without online keyword it succeeds. When I add online keyword it fails.

Once you mark a column unusued, there is no way back, just restore from backup.

Dropping Table Columns

  Removing Unused Columns

  The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action
  allowed on unused columns. It physically removes unused columns from
  the table and reclaims disk space.


Failed to install oracle database I'm new to Oracle database. I'm trying to install the 12c version, on Windows 64, but I'm getting the error shown in the picture

I've already searched for this error on Google, but there is no related topics. Does anyone know how to solve this problem please ? Thank you

This could be an issue with a missing group in the Windows Local Groups folder.

Please ensure you have the following groups in your Computer Management (compmgmt.msc). Open up the relevant branches to reach the Groups branch:

Computer Management
    System
        Local Users and Groups
            Groups

Not all groups are required. The groups marked with an asterisk *) should be available in English even though your OS is French (or a different language) as it can cause issues. The groups marked with a +) should be there in any case.

Administrators *)
ORA_ADMADMIN
ORA_ASMDBA
ORA_ASMOPER
ORA_CLIENT_LISTENERS
ora_dba +)
ORA_DBSVCACCTS
ORA_GRID_LISTENERS
ORA_INSTALL
ORA_OPER
ORA_OraDB12Home1_DBA
ORA_OraDB12Home1_OPER
ORA_OraDB12Home1_SYSBACKUP
ORA_OraDB12Home1_SYSDG
ORA_OraDB12Home1_SYSKM
USERS +)

If you can't manually create the groups marked with *) and +) yourself, then you don't have enough privileges and should run the installation with a privileged or escalated ("RunAs Administrator...") account.

Depending on what you wish to achieve, you would also possibly need to add certain Window accounts to the individual Window groups. E.g. you might need to add your_account as a member of the ora_admin group.

Reference Material

Bug 17846528 CONNECTIONS THROUGH LISTENER HANG AFTER 2049 ATTEMPTS ON NON-ENGLISH SERVERS (Oracle Support)
Solution: Create a local "Administrators" group
(Oracle Support Account required to read bug article)


What does "Latest Restore Time" says in AWS RDS(Mysql) instances page I have two RDS MySQL instances and when I look at the instances page, I have seen a field named "Latest Restore Time" and the value for it is some time stamp in IST time zone.

I would like to know what it actually mean. Being the individual server:

What is the need for this to be Restored?
Where it gets restored?

Given the RDS Console tooltip, the Latest Restore Time defines The latest time to which a database can be restored with point-in-time restore. and not the last time the database was restored.

The database can be restored from Amazon RDS Automated Backup tool that can be configured in the Amazon RDS Modify DB Instance page. The default window backup is 7 days, every 30 minutes.


MS Access copy/paste of table fails with message about clipboard size I have an mdb file that has some large tables in it (greater than 65000 rows). I used to not have any problems duplicated the table by copying and pasting but just recently I started getting a message about the size of the clipboard not being able to hold more than 65000 lines.  What's weird is the operation works on another computer with the same version of Access (32bit 2010) and the same table and doesn't report this error message. It just started reporting this message on the "problematic" computer recently, and nothing has changes as far as I know except for Windows automatic updates. Any help appreciated.

What's wrong its trying to copy such amount of data (straight from a table, mind you) to the Clipboard. But since this is Access we're talking it wouldn't shock me that's suggested over Exporting the data safely into a format like .CSV or Even better .SQL
Is funny that you mentioned the system architecture because on first sight I would check that as possible cause.

I would highly suggest changing the approach into something more around this lines: https://support.office.com/en-us/article/Introduction-to-importing-and-exporting-data-08422593-42dd-4e73-bdf1-4c21fc3aa1b0


Duplicate database leaves orphan archivelog files I am cloning the production database to another one, lets say CLONEDB, in a regular basis. I am using rman duplicate database script like:

run {
duplicate target database to CLONEDB
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
SET ....
DB_FILE_NAME_CONVERT=....
LOGFILE
  GROUP 1 (
     ....
  ) SIZE 100M,
....
;
}

Before running the script I am doing an archivelog rman backup of the CLONEDB (it deletes backed up archivelogs) and DROP DATABASE CLONEDB.

While running duplicate database, rman copies datafiles and archivelogs from the source database. For example, I can see that such file has been copied:

input archived log thread=1 sequence=666 ...
output file name=+DYSK/archivelog/2015_01_16/thread_1_seq_666
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Then rman recovers database (via set until scn 666666; recover clone database delete archivelog;), so the archivelog above is applied to the database:

starting media recovery
archived log for thread 1 with sequence 666 is already on disk as file +DYSK/archivelog/2015_01_16/thread_1_seq_666
archived log file name=+DYSK/archivelog/2015_01_16/thread_1_seq_666 thread=1 sequence=666
media recovery complete, elapsed time: 00:00:02
Finished recover at 2015.01.16 16:21:57
Oracle instance started

Everything is OK, database starts up, users are happy. But the archivelog file (in this example thread_1_seq_666) stays on the disk. And because rman changes the database incarnation after duplicate process, this file is never deleted during the backup process, as it belogns to the previous incarnation.

So from time to time I must do the housekeeping and delete old, orphaned archivelogs directly from the disk. Of course I would like to do it automatically, with the duplication script. I could add rm command from asmcmd for the old files, but maybe there is a way to convince rman to do it for me during the duplicate database?

The duplicate database ... from active database is designed to work without any existing backup.
I would expect Oracle to clean up the archivelogs which were used during the clone process.

So I would say this is an unexpected behavior. Try to log a Service Request at Oracle.

I cannot recall if I had to clean up archivelogs after a clone processes. Well, I can say that I never did delete archivelogs after a db clone because I did not check for any. I rarely clone from active database. I normally clone from backup location without a connection to the primary database. I somehow prefer that.

As workaround I would delete them with a script afterwards.


Error restoring SQL differential database files Occasionally I'm having problems restoring DB differentials. I'm getting the error "the differential backup cannot be restored because the database has not been restored to correct earlier state." From researching the issue, it appears that the particular differential I'm having an issue with is out of sequence.

Is there a way to check the database_backup_lsn from the backup file (.bak) I'm getting? I'm getting the full/differentials backup files from a vendor and it's not easily possible for me to query the source db and msdb...

When I try to restore the problematic differential file, I can see some lsn values in the SQL server management studio window. However, I can't seem to understand if I can figure out if the differential is out of sequence. I'm going to paste the lsn information below to see if someone else can figure it out from that info. I just may not be understanding how the lsn's work.

first lsn             last lsn            checkpoint lsn       full lsn            start date finish date
12642000000130300283 12642000000188400001 12642000000130300283 12642000000130300283 9/17/2011 23:53 9/17/2011 23:56
12742000000735900168 12742000000757500001 12742000000735900168 12742000000735900168 9/18/2011 23:24 9/18/2011 23:25
12951000012892000172 12951000012922000001 12951000012892000172 12951000012892000172 9/19/2011 23:27 9/19/2011 23:28
13123000002683100125 13123000003655500001 13123000002683100125 13123000002683100125 9/20/2011 23:26 9/20/2011 23:26

The first record is the full db and subsequent 3 are differentials. The full and the first 2 differentials restore fine. It's the final differential that is not restoring and showing the above message.

thanks.

To check the LSN number from the backup file do:

restore headeronly from disk='g:\backups\name_of_backup_file.bak'

The result set will have a column which will show you the last LSN number. As gbn said, to restore from a diff backup you need a full + last diff backup from the same backup set.


Upgrading to SQL Server 2014 SP3 _ Clarifications help needed We have SQL Server 2014 SP2 GDR with below:

Availability Groups
TDE enabled for structured data
EFS enabled for unstructured data

Now the DBs are with AOAGs and encrypted ,  I am planning to upgrade current SQL Server 2014 SP2 GDR to SQL Server 2014 SP3. Can anyone help me with any issues / pre-requisites or anything that can happen adversely.

Though I am planning first upgrade DEVELOPMENT / STAGING , please share your thoughts as I need to upgrade my PROD . I am little worried as the DBs are with TDE + EFS . Does encryption has any linkage with SP3 upgrade.

As far as I know, Service Packs on same major version ( 2014 ) does not have any impacts on these features.


When shrinking log files what difference does TRUNCATEONLY make? When using DBCC SHRINKFILE (Transact-SQL) to shrink log files does adding TRUNCATEONLY do anything different? if so what?

Scenario:
I have used DBCC LOGINFO and discovered I have too many VLFs, I only have a single .ldf I am not making any changes to the data files. My database is in FULL recovery and I can run t-log backups on demand. I plan to use Fix_VLFs.sql from the tiger team to rebuild my .ldf

When you look at the solutions generated by Fix_VLFs.sql it lists both options for shrinking the number VLFs

USE [MyDB];
DBCC SHRINKFILE (N'MyDB_log', 1, TRUNCATEONLY);

and

DBCC SHRINKFILE (N'MyDB_log', 1);

The solutions are listed for use when the database is in FULL or SIMPLE recovery, respectably.  But I can't see how FULL or SIMPLE would make any difference on if you should use TRUNCATEONLY or not.  Nor Can I see how TRUNCATEONLY would make any difference for any log file shrink.

VLFs are wrap around files (Transaction Log Physical Architecture) as I understand it the original equipment VLFs('0" in CreateLSN) will not be deleted with SHRINKFILE. Additionally only those VLFs later in the LSN chain than the last one with a DBCC LOGINFO 'status' of '2' are available to be deleted.

In other words, the only option is to delete VLFs from the end of the wrap around, you need to work the current VLF (last status 2) around to the front (original equipment) of wrapped file, run the t-log backup and then you can use SHRINKFILE to delete everything except the original equipment VLFs.  This would seem to be the only option without regard for the use of TRUNCATEONLY.

I see no difference when using TRUNCATEONLY when compared to regular SHRINKFILE on the log file. Here's the script I built for testing (2016 dev edition, note the DROP IF EXISTS syntax):

USE master
GO

DROP DATABASE IF EXISTS trunctest
CREATE DATABASE trunctest
 ON  PRIMARY
( NAME = N'trunctest', FILENAME = N'C:\SQLTest\DATA\trunctest.mdf' , SIZE = 8MB , FILEGROWTH = 8MB )
 LOG ON
( NAME = N'trunctest_log', FILENAME = N'C:\SQLTest\LOGS\trunctest_log.ldf' , SIZE = 8MB , FILEGROWTH = 8MB )
GO

ALTER DATABASE trunctest SET RECOVERY SIMPLE
--or use the below two lines if you want to test FULL recovery
--ALTER DATABASE trunctest SET RECOVERY FULL
--BACKUP DATABASE trunctest TO DISK = 'NUL'
GO

USE trunctest
GO

--Log files at creation
DBCC LOGINFO

--Let's make a table and a large transaction
CREATE TABLE fluff
(junk char(8000))

BEGIN TRAN
INSERT fluff
SELECT TOP 1000 REPLICATE('a',8000)
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2

--pre commit
--DBCC LOGINFO

--post commit
COMMIT
--DBCC LOGINFO

--CHECKPOINT to clear inactive VLFs
CHECKPOINT
--Or log backup if testing FULL
--BACKUP LOG trunctest TO DISK = 'NUL'

--DBCC LOGINFO

--Now let's shrink and check
DBCC SHRINKFILE(trunctest_log)
--Run this instead on round 2
--DBCC SHRINKFILE(trunctest_log, TRUNCATEONLY)
GO

SELECT size*8.0/1024 AS LogSizeInMB
FROM sys.database_files
WHERE type = 1

DBCC LOGINFO


query processor ran out of internal resources and could not produce a query plan This is showing up in the logs several times a night.  How do I find the query causing the issue?  SQL Server 2008 R2 Sp1.

Look for queries with very long IN lists, a large number of UNIONs, or a large number of nested subqueries. These are the most common causes of this particular error message in my experience.

Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.

Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:

Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199


How to get the next value of sequence? Could somebody tell what is wrong with obvious query below:

db2 => select next value for schema_name.sequence_name;

As a result I'm getting:

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"schema_name.sequence_name".  Expected tokens may include:  "<table_expr>".
SQLSTATE=42601

You need to select from something. Another option is:

db2 => values next value for schema_name.sequence_name;

As you noted you can also use SYSIBM.SYSDUMMY1 or create a virtual table:

db2 => select next value for schema_name.sequence_name from ( values 1 );


Dropping then adding a table to a subscription with Identity option Not for Replication causes errors We have a Transactional Replication environment that was initialised from a backup that replicates the data for several hundred tables.

When a table is altered, say a new column added, I have created a script that does the following:

Apply table ALTER at the subscriber
Apply table ALTER at the publisher
Drops the subscription
Drops the article
Adds the article again (so it will now include the new column - sp_refreshsubscriptions is called automatically by the sp_addarticle to add it back to the subscription)

In testing I thought this was working but it appears in our production environment that this causes the following replication error:

  Cannot update identity column 'XXX'

for the identity column of the table being altered. Investigation has shown that this is not what it seems; the command that is failing is not an update of data on the row but the addition of the article to the existing subscription.

If I turn Is Identity off for the identity column on the subscriber then the above script works as expected and data replication continues. I can even then turn 'Is Identity' back on after the article is added to the subscription and replication continues. The solution is to turn 'Is Identity' off for all the tables (or before we try to alter them) in our subscriber database but these were all on after replication was set up and are working fine with the 'Not For Replication' setting turned on.

Why would 'Is Identity' being on for an table in the subscription database prevent me from adding the table again after I have dropped it? Can I circumvent my having to turn it off before altering the table using my script?

Thanks

I have found a solution though adding:

@identityrangemanagementoption = N'manual'

when I re-add the article using sp_addarticle, means that the automatically created sps used to replicate the data don't try and update the identity column and thus are created succesfully on the subscriber rather than raising the error.


Procedure completes prematurely after converting comma-delimited value strings to XML I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked permissions, which doesn't seem to be the issue. Then I ran the procedure through Profiler and it seemed to complete after the 'CAST AS XML...CROSS APPLY' statement, which would explain why nothing is returned. I can't figure out why this would be though. Any ideas?

Here is the bulk of the procedure, with a few table variable declarations removed

DECLARE @allprodsandpacks TABLE (
  packageID varchar(20),
  Name varchar(800),
  RN tinyint,
  productID varchar(20)
)

INSERT INTO @allprodsandpacks (packageID,Name,RN,productID)
SELECT  packageID,
        Name,
        RN,

        REPLACE(LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))),'|','') AS productID
        FROM

        (
           SELECT productID as packageID,
           ROW_NUMBER() OVER (PARTITION BY productlist ORDER BY productID) AS RN,
           Name,
           CAST('<XMLRoot><RowData>' + REPLACE(productlist, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
           FROM   TBOnline.dbo.Packages
        ) AS t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

--Profiler registers SP:Completed here!!

INSERT INTO @prods (productID)
SELECT DISTINCT productID
FROM @allprodsandpacks
WHERE RN > 1

INSERT INTO @packs (packageID,Name)
SELECT DISTINCT
  packageID
, Name
FROM @allprodsandpacks
WHERE productID IN (SELECT productID FROM @prods)

SELECT
  packageID
, Name
FROM @packs
ORDER BY Name

It turns out that the problem was not with the procedure itself but with the PHP function calling it. Prior to execution, I had to include

        mssql_query("SET ANSI_WARNINGS ON");
        mssql_query("SET ANSI_PADDING ON");
        mssql_query("SET CONCAT_NULL_YIELDS_NULL ON");

in the function. Setting these in the procedure itself had no effect.

My question, directed at the MS SQL Server community was misguided but I'll leave this here in case anyone else has this problem.