How to pivot rows into columns MySQL Been looking to pivot rows (progress_check) into columns check 1, check 2 etc...
No sum or totals required, just to display the results hopefully...
Can anyone help, Thanks Ad

GROUP BY and using MAX or SUM is the most used standard pivot way.

SELECT
   results.sims_id
 , results.subject
 , MAX(CASE WHEN results.progress_check = "C1" THEN results.result END) "C1"
 , MAX(CASE WHEN results.progress_check = "C2" THEN results.result END) "C2"
 , MAX(CASE WHEN results.progress_check = "C3" THEN results.result END) "C3"
FROM
 results
GROUP BY
   results.sims_id
 , results.subject
ORDER BY
    results.sims_id ASC
  , results.subject ASC

Result

sims_id  subject  C1      C2      C3
-------  -------  ------  ------  --------
   1111  Art      C       B       (NULL)
   1111  English  6       5       (NULL)
   1111  History  B       C       (NULL)
   1111  maths    8       8       (NULL)
   1111  science  A       B       (NULL)
   2222  Art      (NULL)  A       (NULL)
   2222  English  6               (NULL)
   2222  ICT      A       B       (NULL)
   2222  maths    7       6       (NULL)
   2222  science  A       A*      (NULL)

see demo http://sqlfiddle.com/#!9/0be1f2/1


How can I select rows from a hierarchical query with the lowest level? I have a hierarchical query in Oracle 11gR2 that returns something like this:

Parent (Level 1)

Child (Level 2)

Grandchild (Level 3)

Child (Level 2)

Grandchild (Level 3)
Grandchild (Level 3)

Child (Level 2)

The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)

I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.

If you have an hierarchical query that produces the whole tree under the root node, that also has a level column computed, you can wrap it in a derived table or cte and use the window aggregate:

WITH query AS
  ( SELECT <columns list>, level
  -- your query here
  ) ,
cte AS
  ( SELECT <columns list>, level,
           MIN(the_level) OVER () AS min_level
    FROM query
    WHERE <conditions>
  )
SELECT *
FROM cte
WHERE min_level = level ;


Instance Startup Modes & Shutdown Modes So, I know that there are 3 modes of startup modes available for the instance which is NOMOUNT, MOUNT, and OPEN.
What I want to know is in NOMOUNT mode it is stated that the instance started but still not associated with the Database then Who activates the instance?  Is it the database itself or is it something else?

And If I use the command shutdown then is the instance only that got shutdown or is the database too got shutdown?

Thanks in advance!

Think of it this way:

the database is the set of physical files, on disk. It's a completely passive "thing". It doesn't "do" anything on its own - it's just data.
the instance is the software/processes (with its memory) that manages the database. It's the active part. It's what clients connect to, it's what processes SQL, reads data, updates it, maintains the transaction logs, etc.

When you start with nomount, you start the instance (start a few processes, create the memory areas), but don't actually go look at the database. You'll have something in memory ready to mount a database, or do the few maintenance things you can do in that state (like creating a database), but that's all. The database is completely inaccessible at that point.

When you mount, the control file (which contains a description of the database's files) is read, but the database is still not open. I.e. you can't access the data itself.

Only you finally open the database can clients start interacting with the database.

Things get a bit more complicated with RAC, but the principle is the same: the database is only the set of files. The instances are what allow you to interact with the database.

Shutdown progresses in the opposite direction. For a normal, non-RAC shutdown, the instance flushes all the in-memory data that's not yet saved in the database, and closes the database files. Clients can no longer access the data.
Then the instance dismounts the database, closing the control files. At this point you're left with pretty much the same thing as with an instance in nomount - a set of processes and memory that can't really do much.
The final step is the actual shutdown: the memory areas are released back to the OS and the processes die, which is the end of that instance.

The database doesn't ever do anything. The instances are the only active parts of the system.

For a more in-depth view, see Oracle Database Instance. It has an overview of the startup and shutdown sequence, and other essential information.


Design DB for users with different information fields? Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for example for CS and economic and medicine we may have:

CS:

ID:123
Name:Ali
Lname:Alipour
projects:p1, p3, p6
programming Skills:C[5 star],Java[5 star],JS[3 star],etc.
Research Interest: Network

Medicine faculty:

ID:456
Name:Jafar
Lname:Jafarson
Hospital experience:hospital1 (2 year), Hopital2(1 month),...
Research Interest: Human body

Economic faculty:

ID:789
Name:Sadegh
Lname:Alipour
Company: Company1(company_name, add, tel,etc), Company2(company_name, add, tel,etc)
Research Interest: online currencies

We also may have students in the system:

ID:st_123
Name:mahdi
Lname:Mahdiyar
Major:CS
reg_date:2013.09.09

My first guess was to make a user_table and place all the common fields in it and then make field specific table for each major, CS_faculty_table, Medicine_faculty_table, Economic_faculty_table and student_table. Then thought about inheritance and polymorphism is OO programming language and if it is available in DB area. Then I saw accepted answer of this question that says you can have different fields inside a JSON file and store it in your DB as a BLOB field!

I'm new to DB filed and don't have enough experience in designing DBs so I wanted to know what's the best approach in this kind of situations?

I want a general answer. I don't know if the DB differs or not, and I don't care to change my DB to another open-source DB (sql or no-sql). I had PostgreSQL in mind.

You did not mention whether this was an OLTP or Decision Support solution; lets assume OLTP.

Basically, you are asking about superclass (the set of common attributes) and subclass (the set of different attributes) by some classification.

In your example you have indicated that the sub-class can have many values. When the number of sub-classes are known and static, you could model it as follows:

The DDL for the User table is as follows:

CREATE TABLE User (
UserId INTEGER DEFAULT nextval('UserId_seq')  NOT NULL,
UserName CHARACTER VARYING(40)  NOT NULL,
UserLname CHARACTER VARYING(256),
UserType CHARACTER VARYING(10),
CONSTRAINT PK_User PRIMARY KEY (UserId));

The UserType attribute is new and is used to identify which subclass you are storing for a given user.

For your CS Subclass example

insert into user (UserName, UserLname, UserType)
       values  ('Ali', 'Alipour', 'CS');

-- > Assume that the assigned UserId is = 1 for the purposes of this example

insert into cs_user_projects (user_id, projectName)
       values  (1, 'p1');
insert into cs_user_projects (user_id, projectName)
       values  (1, 'p3');
insert into cs_user_projects (user_id, projectName)
       values  (1, 'p6');
       ...

Now the application would know that it had to go to the CS_USER tables to get the information for CS users as follows:

select UserName
     , UserLname
     , ProjectName
  from user u
  join cs_user_projects p
    on u.userid = p.userid
 where u.UserType = 'CS';

If you want multiple sub-class information (User Programming Skills), you have to join to that sub-class table as well.

This is a straight forward, simple design; however a potential drawback is that it is a  static design. If you wish to represent another sub-class (Engineers, Nurses, ...); then the sub-class tables must be added and the application changed to handle the new sub-class use cases.

In order to allow the model to take on new sub-classes without a change in the data model; you must think of the sub-class as a data object and model the relationship in the following way:

The User table (super-class) stays the same and you still have a classification described by UserType; but now you have an Attrib table to hold the values for each sub-class of user.

As an attribute can belong to many users and a user can have many attributes, the relationship between users and attributes is called a many-to-many relationship. This is resolved by addition of the UserAttrib table with primary keys from each parent table. The Attribute Role is provided for context of the relationship. Let's see how this would load for your CS example again:

    -- identical to before
    insert into user (UserName, UserLname, UserType)
       values  ('Ali', 'Alipour', 'CS');

    -- Load up the different projects
    insert into Attrib ('p1');
    insert into Attrib ('p2');
    insert into Attrib ('p3');
    insert into Attrib ('p4');
    insert into Attrib ('p5');
    insert into Attrib ('p6');

    -- Assign only the appropriate projects to Ali
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p1',1,'PROJECT');
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p3',1,'PROJECT');
    insert into userattrib( AttribValue, Userid, AttribRole)
       values ('p6',1,'PROJECT');

To query our little data model as before:

select UserName
     , UserLname
     , AttribValue Project
  from user1 u
  join UserAttrib a
    on u.UserId = a.UserId
 where a.AttribRole = 'PROJECT'
   and u.UserType   = 'CS';

Note that to return multiple attributes (e.g. also return User Programming Skills say), you would have to join to the UserAttrib table again. The benefit in this design is that if a new sub-class comes along (Engineers who specialize in Electrical, Chemical, Mechanical, ... sub-classifications). Then you would simply add data for these classifications and assignments as above without having to change the data-model to do so.

These are the classical, general solutions to super-class/sub-class design for ALL Databases that you mention in your question.

In the Postgres Database case, you should investigate the INHERITS clause of CREATE TABLE.

It allows a table (sub-class) to inherit the parent (super-class) table definition. In addition, you can alter the child (sub-class) tables to add additional attributes to achieve your desired effect of tables which would match on a sub-class by sub-class basis. Note that this implementation method is very similar to the first method we discussed (static, known sub-classes) and would not apply in all design criteria (e.g. dynamic and/or swiftly changing sub-classes).


How to convert hex chars to byte array in mysql Got sha1 checksum in hexadecimal format(40 characters).
Need help to convert hex chars to byte array in mysql.

Thanks.

There is no such thing as an "array" in MySQL.

Perhaps you want UNHEX(SHA1("stuff")) ?  That will provide a 20 bytes, and could be stored, for example, into a BINARY(20) column.


Create a new schema in a particular database I have a Postgres server with multiple databases (e.g DB1, DB2, DB3)

I need to create a schema sch1 in only DB2. How can I do that?

I have tried connect DB2, but it is not working

Lets say the database we will be using called test, then we have to connect to the database first then create schema, example below

\connect test;
CREATE SCHEMA sch1;


Why an object became invalid in oracle databse. There are some invalid views and functions in my database.

I am not sure what changes caused these objects to became invalid since many people are working on the same database.

How can I see which change caused the view to become invalid?

In short, no.

You can however use the DBA_DEPENDENCIES view to check which objects the views and functions depend on, then query DBA_OBJECTS to check the last_ddl_time for each of these dependant objects - that should give you an indication of which objects have changed and caused the view to be invalid as a result.

It's quite normal for objects to become invalid in development databases when columns are added to tables etc.


I want SQL query for select multiple columns into one I have one single table and in that table there are three columns company1, company2, company3.
Below is my table data :
Company1    Company2    Company3
ABC
Test1       Test3   Test5
Test2       Test4   Test6
testing     testing2

And I want to combine these columns into single column with serial number like below :
SrNo    CompanyName
1       ABC
2       Test1
3       Test2
4       testing
5       Test3
6       Test4
7       testing2
8       Test5
9       Test6

One way to get the desired result is with UNION ALL of a SELECT query for each company column. Assign the serial number value in order by source column and company name using ROW_NUMBER in the outer SELECT.
INSERT INTO #Company(Company1, Company2, Company3)
VALUES
    ('ABC', NULL, NULL)
    ,('Test1', 'Test3', 'Test5')
    ,('Test2', 'Test4', 'Test6')
    ,('testing', 'testing2', NULL);

SELECT
      ROW_NUMBER() OVER(ORDER BY SourceColumn, CompanyName) AS SrNo
    , CompanyName
FROM (
    SELECT 1, Company1
    FROM #Company
    WHERE Company1 IS NOT NULL
    UNION ALL
    SELECT 2, Company2
    FROM #Company
    WHERE Company2 IS NOT NULL
    UNION ALL
    SELECT 3, Company3
    FROM #Company
    WHERE Company3 IS NOT NULL
    ) AS Companies(SourceColumn, CompanyName)
ORDER BY SrNo;
GO


Question on turning on GTID for MySQL master and slaves I came across the interesting article by Facebook's Engineering team where they were able to deploy GTID in MySQL at a large scale : https://code.facebook.com/posts/1542273532669494/lessons-from-deploying-mysql-gtid-at-scale/

Their idea to enable GTID is to keep GTID off on the master, and on on all the slaves, and then later promote one of the slaves as the new master and then turning on GTID on the original master :

"We worked around this problem in fb-mysql by relaxing the constraint that gtid_mode must match between master and replicas. We permit a gtid_mode=ON replica to have a gtid_mode=OFF master, and we suppress assignment of GTIDs to transactions on servers with read_only=ON (i.e., replicas). This permits a high-availability deployment strategy as follows, for each replica set:

On each replica, one at a time, restart MySQL to enable gtid_mode. Afterward, we are in a state where every replica has gtid_mode=ON, but the master still has gtid_mode=OFF.
Perform a master promotion as normal, repointing the replicas and original master to a new master. The original master's replication will intentionally break when started, since it still has gtid_mode=OFF.
Restart the original master to enable gtid_mode. It will now be able to replicate from the new master, and the entire replica set now has gtid_mode=ON. "

From what I understand about GTID in 5.6, either all the machines in your cluster need to have GTID on, or they need to have GTID off. So is Facebook able to have a GTID-ON master and GTID-OFF slaves because they have specially modified their MySQL to do so? This would otherwise not be supported under a normal MySQL 5.6 release right?

Thank you

Right, it's modded.

  “We worked around this problem in fb-mysql by relaxing the constraint...”

Note that fb-mysql is a link to https://github.com/facebook/mysql-5.6/ ... which is a Facebook fork branch  of MySQL Server 5.6.


Designing a database of Recipes Task

I need database design to record Recipes.

Recipes can contain several Ingredients and several ordered HowToSteps.
Each HowToStep can be split into ordered HowToSections.
A single recipe can contain a mix of several (ordered) HowToStep and (ordered) HowToSection elements.

My Solution

Table HowToSection with FK to the table HowToStep with FK to the table Recipe.
Table Ingredient with FK to the table Recipe.

Issue

I have no idea how to handle a business rule which stipulates that

"A single recipe can contain a mix of several (ordered) HowToStep and (ordered) HowToSection elements".

You'd have a:

RecipeSteps table containing, recipe_id, stepnumber for order, and a HowToStep ref

And:

RecipeSections table containing, recipe_id, sectionnumber for order, and a HowToSection ref

The stepnumber and sectionnumber need to be non overlapping (per recipe_id for the pulling in the steps/sections in an order.


Firebird-Time Interval- between 2 Dates I try to select the period of employment for the members in a Company.

Example:
Date of join: 01.01.2001
Today: 27.02.2014
Now, I want this:
13 years, 1 month and 27 days

I have a Statement which works on MSSQL, but I cant translate it to firebird-SQL.

Could anybody help me?

Thx

Erik

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance!
Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that
just
will
not
lose).

Thanks and hello again!
--Rob

I never had a plan-- Anonyman


Can FOR XML PATH return NULL for an empty resultset? I would think this query

select null for xml path('foo')

would return NULL, but actuality

<foo/ >

is returned. Is there any way to achieve this behavior?

To clarify, the behavior I would expect to see is demonstrated in this example

SELECT
'foo' WHERE 1=0
for xml path('Outer'), type

This returns a different result and I'm not understanding why.

SELECT
(SELECT 'foo' WHERE 1=0)
for xml path('Outer'), type

My real world query is an xml subquery, but the example is valid either way.

SELECT
(SELECT 'foo' WHERE 1=0 FOR XML PATH ('Inner'),type)
for xml path('Outer'), type

This query works in the simplest case, but does not allow adding attributes to the root element, which is required in my case.

SELECT 'foo' WHERE 1=0 FOR XML PATH ('Inner'),root('outer'),type

Check out the value(method). From MSDN:

  You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.

Code:

DECLARE @variable_xml XML
DECLARE @variable_varchar VARCHAR(20)
SET @variable_xml = 'select NULL for xml path(''foo'')'
SET @variable_varchar = @variable_xml.value('(/Root)[1]',
                           'varchar')
SELECT  @variable_varchar

Output:
NULL

EDIT:

I may have misunderstood the question. Please comment and I will update or delete my answer if its incorrect.


How to connect using PDB userid on Oracle 18c XE? I have created a PDB on Oracle 18c XE using following SQL command

create pluggable database virlux
admin user test1admin identified by test1pwd
file_name_convert = ('/pdbseed/', '/test1/')
;

Now, I try to connect directly to this PDB using new Userid using following command

sqlplus test1admin/test1pwd@virlux

and Oracle answer by error ORA-12541: no listener

I have then tried with SYSTEM userid with following command

sqlplus SYSTEM/syspwd@virlux

and I have obtained same error number !

What happens ?

I have set ORACLE_HOME and SET_ADMIN variables correctly.

My tnsnames.ora file found in %TNS_ADMIN% folder contains following lines

# tnsnames.ora Network Configuration File: D:\Oracle\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

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

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

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

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

I have just added VIRLUX in this file.

I have also stopped and started all Oracle services using net use commands.

The listener.ora file contains following lines

# listener.ora Network Configuration File: D:\Oracle\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\Oracle\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

When I start LSNRCTL STATUS command, I obtain

I have finally found a solution to my problem and I will explain to help other users in same case.

I have no problem to connect to XE instance of Oracle Database using following sqlplus command

sqlplus SYSTEM/password@XE

My first problem is that PDB is not always open and every time that I stop and start DB services, I must restart PDB.

To avoid this, I have executed following SQL command

alter PLUGGABLE DATABASE ALL OPEN;
alter PLUGGABLE DATABASE ALL SAVE STATE;

as explained on asktom.oracle.com

Now every time I stop and restart the Oracle database services, the VIRLUX PDB is correctly started but following sqlplus command continue to return an Oracle error that now is ORA-01017.

sqlplus test1admin/test1pwd@virlux

SQL*Plus: Release 18.0.0.0.0 - Production on Mar. Janv. 14 08:41:41 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
ERROR: ORA-01017: invalid username/password; Logon denied

But, if I enter following command, I'm connected to Oracle PDB !

sqlplus test1admin/test1pwd@localhost/virlux

I think that old well known sqlplus connection command don't work for PDB and that the only solution is to prefix PDB name by hostname that has been defined in TNSNAMES.ORA file.

What is surprising is that a command that return an error's message saying that User/password are invalid is corrected without changind password !

This solution works now well on my PC on Windows Home 10.


Can I Configure Enterprise Manager Cloud Control on Standard Edition Is it possible to configure enterprise manager cloud control on Oracle 12c Standard edition?
Will it administer multiple databases (Non-CDB)?
What about administering databases on different servers?

Is it possible to configure enterprise manager cloud control on Oracle 12c Standard edition?

Infrastructure Repository Databases

  The infrastructure repositories for RMAN, Oracle Enterprise Manager
  Cloud Control, AWR Warehouse, and Global Data Services Catalog can be
  shared in one database, or deployed in separate databases. Enterprise
  Edition must be used for the infrastructure repository database(s).

Will it administer multiple databases (Non-CDB)?

Yes, that is the point of a central monitoring environment.

What about administering databases on different servers?

Yes, same as above.


Which databases can meet these replication requirements? I am doing research on database architectures, and I am looking for which software can meet my requirements.

To try to explain the requirements simply:

You have a master database server. This may make a one or a few identical replicates for distance purposes - such as one for US and another for Europe. These masters would have identical clones locally for failover.
This master then does one-way slave propagation to many read-only servers via VPN. These servers serve-up all data reads to a LAN at that server's location, serving a few hundred clients at each location. Ideally, the read-only slave DB could mirror locally for failover.
All writes are made to the master server(s) and then make their way back to the slaves. The application this architecture serves is far more read intensive than write.
The slaves require intelligent granularity. The replication must not only be table-specific per site, but also must be row-specific in some tables. For example, the master server would have a monolithic client table with all clients across all sites. The client table should replicate in part to the slaves, containing the same columns however only having those clients that have purchased from that local branch. We do not want the local branch to have access to all clients.

__

Though relatively novice in database administration, I have done a bit of research thus far. It seems Postgresql is quite a positive choice for a database, and the closest replication middleware I can find to fit these requirements is PgPool-II. I do not, however, know if it will meet all of these requirements easily and in a way that will scale.

What, if any, major database such as PostgreSQL or MSSQL, is capable of satisfying the above requirements? Does it do this natively? And if not natively, what middleware for that database is capable of handling it?

Having done this type of project before, here's some of the practical gotchas to look out for:

"2. This master then does one-way slave propagation to up to 200 read-only servers via VPN."

Think carefully about the network traffic required here. In the simplest terms, if the insert/update/delete load on the database takes up 1/200th of a network cable's throughput, and if your master server only has one network connection, it's going to be saturated. (In practical terms, it'd be saturated long before that since you also need to accommodate query loads and backups.)

Next, think about the logging requirements. Every transaction has to be logged, and the logged changes get sent to the read-only servers as they happen. If any one read-only server drops offline, then the master has to retain all of the logs until the read-only server comes back online and consumes the changes. In a high-volume change environment like you're describing, this can easily be a higher size than the original data set itself (since some records can get updated over and over, or get deleted.) You need the ability to recognize when a replica has gotten farther behind than it can easily catch up, and then the ability to reinitialize that replica from backup/restore rather than replication synchronization.

Since you want to be able to fail over from one master to another, then every master would need to be aware of the replication synchronization state of every read-only replica.

With these limitations, you can see why you usually want to separate the work of distribution out onto other servers. You want masters handling writes, but then just batching off the changes to a set of distributor servers that are responsible for tracking replication synchronization states across many read-only servers. (This is how Microsoft SQL Server's transactional replication works, and I would imagine most other DBMS's have similar distributor architectures available as well.)


Postgres latency issues during memory compaction on EC2 (Ubuntu 14.04) We've recently upgraded our EC2 instance that hosts our Postgres database to an i2.8xlarge with 244GB of memory (this is to utilise the large amounts of ephemeral storage it comes with). Since upgrading, we've been having some issues with latency in Postgres that appear to be due to memory compaction that's occurring in the Linux kernel.

We're using PostgreSQL 9.3 on a recent Ubuntu 14.04 kernel running the following (hopefully relevant subset of) config:

max_connections = 1000
effective_cache_size = '220GB'
shared_buffers = '24GB'
work_mem = '25MB'
maintenance_work_mem = '1024MB'
fsync = off
full_page_writes = on
synchronous_commit = off

We have transparent huge pages completely disabled on this server (/sys/kernel/mm/transparent_hugepage/enabled and /sys/kernel/mm/transparent_hugepage/defrag are both set to never and /sys/kernel/mm/transparent_hugepage/khugepaged/defrag is set to 0) and we're fairly sure that we're not seeing any issues as a result of THP because the thp_* stats and nr_anon_transparent_hugepages stat in /proc/vmstat never increment.

Our issue is that we see constant memory compaction (failure and success) events in /proc/vmstat (all the stats under compact_* incrementing frequently) and some of these cause pretty severe stalls that get worse over time (presumably as memory fragmentation gets worse) and impact on our application. We're tracking the stats from /sys/kernel/debug/extfrag/unusable_index and often see a flurry of movement between the different page orders when we see stall-causing events.

We're wondering whether this is just some combination of Postgres version, Linux kernel version and having to deal with a large amount of memory (as obviously most of the memory usage is file cache, so Linux might be doing things with that that Postgres isn't happy about), but haven't been able to come up with anything other than assuming a more recent version of Postgres (9.4 or 9.5) might avoid the issue altogether for some reason.

$ uname -a
Linux db-04 3.13.0-91-generic #138-Ubuntu SMP Fri Jun 24 17:00:34 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
$ dpkg -l postgresql-9.3
postgresql-9.3     9.3.13-1.pgdg14.04+1

We also tried reducing the effective_cache_size on the instance to 160GB to see if we could reduce memory pressure but that didn't change much (and mostly seemed to make the stalling worse).

Just wondering if memory stalls on Postgres is something that's been raised before or that people have experience with?

As dezso mentioned in the question comments, this did seem to be an issue with (possibly more recent versions of the) 3.13 kernel in Ubuntu Trusty - we switched to the Xenial HWE 4.4 kernel in Trusty and the problem seems to have gone away and compaction stalls are now very small and don't interfere.


MYSQL Get Month before When I use interval 1 I got an error in my SQL syntax. I want to get month now -1 to have a month before.

SELECT
      catalog.code,
      catalog.supplier,
      catalog.created_at
FROM  catalog
WHERE MONTH(catalog.created_at) = month(NOW()) - INTERVAL 1
AND   YEAR(catalog.created_at) = year(NOW())

Kindly try the following code :

SELECT

            catalog.code,
            catalog.supplier,
            catalog.created_at

       FROM catalog
        WHERE MONTH(catalog.created_at) = month(NOW()- INTERVAL 1 MONTH)
        AND YEAR(catalog.created_at) = year(NOW());

Tried and tested in SQLFiddle: http://sqlfiddle.com/#!9/2f8d3/1/0
Hope this helps !!
Regards.


How do I enable READ-only (passive) mode in a particular RDBMS? As most of you have noticed all StackExchange sites have been under maintenance/recovery a few days ago so they switched on READ-only mode. From their blog I know they used to use MS SQL Server 2008, and recently have moved on to SQL 2012.

The question is how do you switch READ-only mode in a specific RDBMS, thus keep the service up during maintenance?

Since I use CUBRID RDBMS most of the time, I know they provide an ACCESS_MODE switch in CUBRID Broker middleware (cubrid_broker.conf) which allows DBAs to dynamically turn a READ/WRITE-enabled service to READ-only (+ ...) and vice versa. This is especially useful in an HA environment.

In MySQL as I know, even if you turn read_only=1 in my.cnf/my.ini, users with SUPER and replication privileges still can write to a read only server.

I wonder how this is done in other relational databases. How does SO do this?

I'm going to attempt to answer this question for Oracle.

Historically Oracle has never supported read-only databases.

It's possible to make a tablespace read-only: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces006.htm

Individual tables can be made read-only: http://www.oracle-base.com/articles/11g/read-only-tables-11gr1.php

Or it's possible to make a select-only role and assign that to the user who logs in rather than the normal readwrite-role.


How to choose which backup files to restore when identical timestamps exist for diff and log backups I can see from an earlier question that log file backup may be performed simultaneously with either full or differential data file backups.

Imagine for a moment that full backups run during a window of time that doesn't overlap with any other backup. However differential and log backups may overlap.

If, for example, this produces a differential backup at 11am on a given day, but also produces a log file backup at 11am on the same day, how do you decide which files to restore?

For example:

01:00 AM = full backup
10:00 AM = differential backup
10:00 AM = log backup
10:15 AM = log backup
10:30 AM = log backup
10:45 AM = log backup
11:00 AM = differential backup
11:00 AM = log backup
11:05 AM = disaster

After restoring the full backup, do you restore the second differential backup and the last log backup? Or do you restore the first backup and all the log backups (even though the 10AM log backup really has the same issue as the 11AM backup - coinciding with the differential backup in each case).

I can see another question here for which some answers suggest third-party scripts to restore backup file headers in order to determine sequence numbers - do we really have to resort to header restores to determine which files are able to be restored in sequence?

Edit (19/12/18): I think my original question was worded poorly, so the answers it has received are varied. The key part I wanted to understand was actually mentioned by Jonathan in a comment on Sean's answer, and Jerry mentioned it too - that the restore sequence will handle the scenario if the log file contains information about data changes that have already been accounted for by the differential backup restore. (Sorry I didn't word the question well!) Sean's answer gives lots of additional info that is also helpful to understanding the restore sequence, so I can't pick an individual answer here, but I will upvote everything that helped with clarifications. Thanks.

After restoring the full backup, do you restore the second differential backup and the last log backup?

This is going to depend, but assuming:

The source server is offline and the data files cannot be accessed
The most up-to-date copy of the data is what is asked to be restored
All backup files are successfully accessible

Then you'd want to restore the 1:00 AM full backup, then the 11:00 AM differential, and there will most likely be some additional information in the 11:00 AM log backup depending on the length of time the differential and log backups took.

  Or do you restore the first backup and all the log backups (even though the 10AM log backup really has the same issue as the 11AM backup - coinciding with the differential backup in each case).

I'm not sure what you mean about the 10:00 AM log backup having "the same issue" as the 11:00 AM log backup. There isn't an issue at all.

The whole point of a differential backup is to apply the changed extents and then keep just enough transactional information to make everything consistent. This greatly differs from a log backup which is the actual log records. They serve two different functions and you don't need differential backups in a restore to the latest point in time if you're in the full recovery model but you do need log backups to do so.

  … do we really have to resort to header restores to determine which files are able to be restored in sequence?

You don't have to "resort to header restores" if:

You have an up-to-date copy of MSDB and can query the metadata out of it
Some other 3rd party application does the metadata shuffle for you
A human sits in front of a screen using SSMS to load up all the files and let the restore wizard figure it out by utilizing the backup header metadata
You keep some other metadata repository that is synchronized with your DR area

If, however, none of those (not complete, just examples) are the case then... yes, you'll need some way of figuring out which files go in which order. Since the easiest way to figure it out is to ask the backup what metadata it holds, that's what you'll find most people doing.


The current security context cannot be reverted Msg 15199, Level 16, State 1, Line 29 T he current security context
  cannot be reverted.

  Please switch to the original database where Execute As was called
  and try it again.

execute as login='juno'
REVERT

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT  [THE_SERVER]= @@SERVERNAME
        ,[DB_NAME] =DB_NAME()
        ,[@USER]=@USER
        ,[SUSER_SNAME()]=SUSER_SNAME()
        ,[SYSTEM_USER]=SYSTEM_USER
        ,[USER_NAME()]=USER_NAME()
        ,[CURRENT_USER]=CURRENT_USER
        ,[ORIGINAL_LOGIN()]=ORIGINAL_LOGIN()
        ,[USER]=USER
        ,[SESSION_USER]=SESSION_USER

What if I can't remember the original database I was when I executed the execute as login?

Is there a way to find out where I was?

There is no way to find it out, but you can try all the databases accessible by that login, i.e. under impersonated login execute the following code:

select 'use ' + name + '; revert;'
from sys.databases
where HAS_DBACCESS(name) = 1;

This way you find all the databases from which you could impersonate this login. Then you copy the output to query window and try it row by row until success.

But the easiest way to return yourself is just to change connection (right click in query window)


Microsoft Access 2003 vs SQL Server 2005 Express There is an Access 2003 database that each "corps" has to manage themselves containing personal, training and material data. It's written very poorly. Besides that, the server only runs Windows 2000, so SQL Server 2005 Express is the highest that it can run. What advantages would I see upgrading it to SQL Server? Speed, reliability and ease of upgrading would be a plus. If it can handle multiple users at the same plus, that'd be the best Plus right now

Few things to consider with using SQL Express vs Access 2003:

SQL Server is limited to 1 CPU
SQL Server is limited to 1 Gig RAM
SQL Server is limited 4 Gig database file (2 Gig on Access)
Creating front end logic for users will take a longer time than Access 2003
Using SQL more reliable as you can do point in time recovery

More comparisons among the two in this link from ZDNET (UK)


How to free the unused space for a table This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.

I use SQL Server 2014 Express edition with 10GB limit of database size (not a filegroup size, database size).

I crawled news, and inserted HTML into a table. Table's schema is:

Id bigint identity(1, 1) primary key,
Url varchar(250) not null,
OriginalHtml nvarchar(max),
...

Database ran out of size and I received insufficient disk space

Of course shrinking database and filegroup didn't help. DBCC SHRINKDATABASE didn't help. So I wrote a simple application to read each record, strip out some unwanted parts of the OriginalHtml like head section and aside and footer to keep the main body only and I now see this image when getting report of disk usage by top tables:

As I understand this picture the unused space is now like 50 percent of total size. That is, now I have 5GBs unused space. But I can't reclaim it.
Rebuilding indexes didn't help. The truncateonly option won't help because as I understood no record is deleted, only the size of each record is reduced.

I'm stuck at this point. Please help, what should I do?

Clustered Index is on column Id.

This is the result of EXECUTE sys.sp_spaceused @objname = N'dbo.Articles', @updateusage = 'true';

name        rows     reserved     data        index_size   unused
----------- -------- ------------ ----------- ------------ -----------
Articles    112258   8079784 KB   5199840 KB  13360 KB     2866584 KB

All things being equal, it should be enough to compact the large object (LOB) column OriginalHTML. You don't specify the clustered index name in the question, so:
ALTER INDEX ALL
ON dbo.Articles
REORGANIZE
WITH (LOB_COMPACTION = ON);

See ALTER INDEX (Transact-SQL)
If you have the clustered index name (not just the clustered column(s)), replace the ALL above with that name.
The LOB_COMPACTION option defaults to ON, but there's no harm in being explicit. You may need to run the REORGANIZE repeatedly to finish reclaiming all the unused space.
Unfortunately, the way LOB data is organized and the way LOB compaction is implemented means this method may not always be able to reclaim all the unused space, no matter how many times you run it. It can also be very slow.
You could also try the method in the related Q & A Freeing Unused Space SQL Server Table
If, for whatever reason, the above does not work for you, export the data to a file, truncate the table, then reload it. There are several methods to achieve that, for example the bcp utility.
Example
The following creates a table with 10,000 wide rows:
CREATE TABLE dbo.Test
(
    c1 bigint IDENTITY NOT NULL,
    c2 nvarchar(max) NOT NULL,

    CONSTRAINT PK_dbo_Test
        PRIMARY KEY CLUSTERED (c1)
);

-- Load 10,000 wide rows
INSERT dbo.Test WITH (TABLOCKX)
    (c2)
SELECT TOP (10000)
    REPLICATE(CONVERT(nvarchar(max), 'X'), 50000)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2;

We can see the space usage using the sys.dm_db_index_physical_stats DMV:
SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.index_depth,
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Test', N'U'),
    1,
    NULL,
    'DETAILED'
) AS DDIPS
WHERE
    DDIPS.alloc_unit_type_desc = N'LOB_DATA';

We now update the LOB content to a smaller size (but one that still requires off-row storage):
-- Change LOB data to a smaller value (that will not move in-row)
UPDATE dbo.Test WITH (TABLOCKX)
SET c2 = REPLICATE(CONVERT(nvarchar(max), 'Y'), 5000);

Notice that some space has been reclaimed, but the remaining pages are much less full than they were.
We can compact the LOB space using:
ALTER INDEX PK_dbo_Test ON dbo.Test
REORGANIZE
WITH (LOB_COMPACTION = ON);

This results in some compaction and space savings, but it is not perfect. Running the compaction again may or may not improve the situation. In my test, it did not, no matter how many times I re-ran it.
Export, truncate, reload
One way to do this entirely from Management Studio involves using xp_cmdshell to export the table data to a file. If xp_cmdshell is not currently enabled, the following will do that:
-- Enable xp_cmdshell if necessary
EXECUTE sys.sp_configure
    @configname = 'show advanced options',
    @configvalue = 1;

RECONFIGURE;

EXECUTE sys.sp_configure
    @configname = 'xp_cmdshell',
    @configvalue = 1;

RECONFIGURE;

Now we can perform the export:
-- Export table
EXECUTE sys.xp_cmdshell
    'bcp Sandpit.dbo.Test out c:\temp\Test.bcp -n -S .\SQL2017 -T';

Note you will need to change the path and -S server name, and possibly provide login credentials.
How we can truncate the table, and reload it using BULK INSERT:
-- Truncate
TRUNCATE TABLE dbo.Test;

-- Switch to BULK_LOGGED recovery model if currently set to FULL
-- Bulk load
BULK INSERT dbo.Test
FROM 'c:\temp\Test.bcp'
WITH
(
    DATAFILETYPE = 'widenative',
    ORDER (c1),
    TABLOCK,
    KEEPIDENTITY
);

The final step is to reset the identity seed:
-- Check and reseed identity
DBCC CHECKIDENT('dbo.Test', RESEED);

This sequence of operations is typically faster than LOB compaction, and should always produce optimal results:

The above is not quite as efficient as it could be due to a long-standing bug: BULK INSERT with IDENTITY column creates query plan with SORT. The workaround listed there is effective, but I would only bother with it if the table is very large.
Don't forget to delete the temporary file used to hold the exported data.
You are of course free to use whichever bulk export/import approach is most convenient to you. It is not required to use xp_cmdshell or bcp.
Additional notes:

FILLFACTOR only applies to index pages. It does not affect off-row LOB storage (which is not stored on index pages).

Row and page compression are not available for off-row storage.

As an alternative, you can compress and decompress data explicitly using the COMPRESS and DECOMPRESS functions available from SQL Server 2016.
An option for those using SQL Server 2014 (which is the case here) or older (down to SQL Server 2005) to get the same compression functionality provided by the COMPRESS and DECOMPRESS built-in functions is to use SQLCLR. Pre-built functions that do just this are available in the Free version of SQL# written by Solomon Rutzky. The Util_GZip and Util_GUnzip functions should be equivalent to COMPRESS and DECOMPRESS, respectively. And, anyone using SQL Server 2012 or newer should make sure that the server running SQL Server is updated with .NET Framework version 4.5 or newer so that the much improved compression algorithm will be used.


SQL Server 2008 onward - Globalization with Date Time My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own culture.

There are some Date, Time and DateTime columns in database. Should I store them:

based on SQL Server Date/Time?
based on Windows Date/Time?
based on Windows Date/Time converted to UTC?

I think I do not need to look into this globalization part at all as the application is standalone and databases are never going to be shared. Data will stay at same machine only, it will not be moved to central location. So, second option is best suited I guess.

I read this question and its answer. DateTimeOffset looks odd; it is generally used for row versioning. I am not sure if it is needed here. That answer is not even accepted; not sure if it really helped OP.

With stand-alone systems which will never be merged using the local system clock's time has appeal. There is no need for conversion going into nor coming from the database. What you see is what you get. No matter how you access the DB, whether application, reports or ad hoc query the results need no post-processing.

However .. some places observer daylight saving. This means that once (?) a year there will be an hour-long gap in the data. And once (?) a year there will be an hour-long time reversal where, say, sorting on identity column and sorting on a datetime column will produce different ordering.

Now, we don't know your application. We don't know how important any of this could be, or what other work-arounds you might think up, or whether a crazy hour here or there is a problem or not, or any of the other factors that might come into play. But if it is then UTC or time offset is the way to go.


Difference in MYSQL Master Slave DISK SPACE I am using percona_mysql_5.6, both master and slave are in sync as terms of replication and table wise records are also same. However there is a difference is the disk space consumed. The slave is consuming relatively more space than master. Even the mysql.conf is same for both master and slave. Any idea why this might happen or any leads that i can check?
Server            Name           Engine Version Row_format  Rows    Avg Data_length Max Index_length    Data_free
Master            MasterTable       InnoDB  10  Compact 148087092   320 47392686080     0   31006982144 6291456
Slave(10.52.146.189)    SlaveTable  InnoDB  10  Compact 214554824   476 102218530816    0   40179859456 5242880

The tables on the Primary and the Replica are maintained separately.  All InnoDB tables are B+Trees.

A block might need splitting on one but not the other.
A long-running SELECT on one might lock rows in such a way that there are more block splits.
Small blocks may get recombined, but not necessarily with the same frequency, etc, on both machine.
Were all the same ALTERs done on both machines?
Which columns of SHOW TABLE STATUS show the most difference?

If it is a few percent difference, the above things might be the reason.
If it is 50%, something strange is going on; we need more details.  For starters, which one is larger.


How to add a new target in Oracle Enterprise Manager 12 C I just installed Oracle Enterprise Manager 12 C Grid Control from a ready made image that Oracle provides and now I am trying to add targets to it (monitoring targets i mean).

It might be a stupid question but how can such a thing be done ? I don't seem to find any tutorials on this and the Oracle docs are not that clear.

Well this task i am afraid it can not be described here, i mean it could but is to much to write and i will not re-invent the wheel.:)
 So i recommend you follow this article How to add a new target on OEM , i wrote this article with this purpose(help guys like you ! kkkkk ).
 i might get a down vote for my short answer ! but for those intrested  -- fell free to read the article !! And thare a lot's of them on Oracle Enterprise Manager 12 C .


Oracle Tablespace not using 100% of available space About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally.  I check on the space available with this query:

SELECT
    df.tablespace_name TABLESPACE_NAME,
    totalusedspace USED_MB,
    (df.totalspace - tu.totalusedspace) FREE_MB,
    df.totalspace TOTAL_MB,
    ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) PCT_FREE,
    ROUND(100 * ( (df.MaxMegBytes-df.TotalSpace)/df.MaxMegBytes)) PCT_ROOM
FROM
(SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace,
    ROUND(SUM(maxbytes) /(1024*1024)) MaxMegBytes
    FROM dba_data_files
    WHERE tablespace_name like 'MY_TABLESPACES%'
    GROUP BY tablespace_name) df,
(SELECT
    ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
    FROM dba_segments
    GROUP BY tablespace_name) tu
WHERE
    df.tablespace_name = tu.tablespace_name and df.totalspace <>0
ORDER BY
    df.tablespace_name;

and it reports:

TABLESPACE_NAME                   USED_MB    FREE_MB   TOTAL_MB   PCT_FREE   PCT_ROOM
------------------------------ ---------- ---------- ---------- ---------- ----------
MOB_BIGDATA3                     47486837    1543307   49030144          3          6

When we were first adding data to this tablespace, the total MB was creeping up and the Percent Free was creeping down, except we are now stuck at 6%.  The Free MB can go to zero and the percent room stays at 6%.

I should add that the other tablespaces have zero percent room.

Could anybody suggest what to do to use this last bit of space?

Update:
It just happens that in my case, there is no need to sum the values since there is only one per tablespace.  With this query:

SELECT
    tablespace_name,
    bytes                   TotalSpace,
    maxbytes                MaxBytes,
    maxbytes -bytes    room,
    100*(maxbytes -bytes)/maxbytes percent_Room
    FROM dba_data_files
    WHERE tablespace_name like 'MY_TABL%';

I just got:

TABLESPACE_NAME              TOTALSPACE             MAXBYTES                ROOM PERCENT_ROOM
------------------ -------------------- -------------------- ------------------- ------------
MY_TABLESPA3         51,411,832,274,944   54,975,581,388,800   3,563,749,113,856      6.48242

I do not see any issue other than using an unnecessary an incorrect query for monitoring instead of DBA_TABLESPACE_USAGE_METRICS and possibly misunderstanding its output.

PCT_FREE is the ratio of the free space compared to the allocated size.

PCT_ROOM is the ratio of the free space compared to the.

When PCT_FREE reaches 0, that means all your datafiles are full of data up to their allocated size. The autoextensible datafiles in the tablespace can still grow 6% up to maxbytes. At this point, if more data is inserted, PCT_FREE will remain 0 and PCT_ROOM will start decreasing. But keep in mind, 1% here is 490 GB, so it could take a while for PCT_ROOM to move from 6 to 5.

The query is incorrect, because a datafile that is not autoextensible will have its maxbytes value at 0. This part simply does not account for that:

ROUND(SUM(maxbytes) /(1024*1024)) MaxMegBytes

It should be:

ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /(1024*1024)) MaxMegBytes

With non-autoextensible datafiles, the above query returns inaccurate results, it may even return negative values.

If the concept of autoextensible datafiles is confusing, just simply preallocate all your datafiles to the maximum size. That way PCT_ROOM will be 0, and PCT_FREE will represent the actual free space percentage. You can even disable autoextend on the datafiles after doing this, but for that, you need to fix the query as I have point out above.

Or just simply throw all this out and use DBA_TABLESPACE_USAGE_METRICS. Also, apply the latest patches as this view does not work a 100% correctly without them.


SQL Server 2008 R2 Restore constantly hanging I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the progress of where the restore is at, and it hangs at various times. One time it may complete 56%, the next time 64%, the next time 17% and so on. It does not stop at consistent times. And when I say that it hangs, it just stops writing. As far as SQL Server is concerned, the restore is still running though. So its like it still doing its thing, but nothing occurs. If you leave the restore running without killing the process it will run forever. I have let it run for 12 hours before with no progress

Eventually, I can get the restore to complete after trying again over and over, and eventually it will go one time and finish. But it takes what should only be an hour, and turns it into 5 hours of constantly retrying.

So here is some information on the server -

It is a Virtual Machine,

Windows Server 2008 R2 Standard,
SQL Server 2008 R2 sp2 Standard,
32 GB RAM,
4 Proc
Here are the database sizes that are the problem. While not really, they are not overly large either.

Database A - 71 GB,
Database B - 11 GB,
Database C - 27 GB

Here is what I have done:

I have checked all the logs (SQL Server and Windows) to look for any errors or any messages that looked suspicious. There is nothing.

I have tried to run the restores at nearly every hour of the day, but it does not matter, it will hang at any point.

I have tried dropping the databases\deleting the .mdf & .ldf files and recreating the databases and then restoring, still get the issue

I have traced the restore to see if anything is occurring. Nothing.

I have made sure all connections where killed before starting the restore. Also, have placed the databases in single user mode before starting the restore.

I have no problems restoring the exact same backups on other servers, so no issue with the backups

Storage guys looked at the datastore\LUN and checked NAR files and said everything looked good.

They also migrated the vmdk to another LUN, but we still got the issue.

Storage guys sent the NAR files from the Array to EMC for examination and they say no issues with the storage

VMWare guys looked at the Virtual Machine and saw no issue with it.

I also disabled all the Anti-virus software and then tried it again, still will hang

I have ran the restores using the GUI, writing out the script, and using custom scripts, same result

Ran a repair on SQL Server

Nothing unusual when looking at the Wait Stats. Shows same thing as you would see when doing a successful restore.

Any thoughts on anywhere else to look or possible reasons here. Or any tracking or counters that you can recommend to pinpoint the issue.

My thoughts are an issue with storage or the server, but the Ops guys are adamant there is no issue with either of those.

Also, rebuilding this server at this time is NOT an option. Even though it is Test, it is part of a farm of servers that makes up a Claims environment. We will eventually rebuild this entire environment later this year, but it is a large undertaking.

The next step is getting on the phone with Microsoft, but waiting on the Ops guys to take one more look. So thought I would check here.

Thanks for your help!

Results of requested waitstat script -

I think I have found the issue.

The backups are stored our data domain, so when performing the restore we select the backups from this location. I think there is some type of connection issue between this server and the data domain. Because when I move the backup files over to the server and restore from the server directly, I cannot reproduce the issue and the restores are probably 10x faster.I set a continuous restore of a database (11 GB) that has been running for about 25 minutes and has completed the restore successfully 23 times with no hanging

My thinking is that the connection issue probably lies somewhere with our test server still. Because we use the Data Domain for all of our production backups (although it is changing) and have no problems restoring elsewhere, including the databases I have been working with. Not to mention, we have automated nightly restores on other servers that have never failed.

I think this at least pinpoints the issue. I will look into where the connection issue is and work with the server\networking guys to see if we can resolve it.

Thanks you guys for your quick assistance


Include all records in between two dates I have a table with the columns ID, start_time, and end_time.
I would like to extract all the records that are active during a date range.
  ID | start_time |  end_time
 ----|------------|------------
   1 | 2019-07-22 | 2019-07-25
   2 | 2019-07-20 | 2019-07-25
   3 | 2019-07-20 | 2019-07-21
   4 | 2019-07-20 | 2019-07-25
   5 | 2019-07-22 | 2019-07-22
   6 | 2019-07-25 | 2019-07-25
   7 | 2019-07-26 | 2019-07-27

Example Query parameters

start: 2019-07-22
end: 2019-07-25

Expected results (5 rows):
 ID | start_time |  end_time
----|------------|------------
  1 | 2019-07-22 | 2019-07-25
  2 | 2019-07-20 | 2019-07-25
  4 | 2019-07-20 | 2019-07-25
  5 | 2019-07-22 | 2019-07-22
  6 | 2019-07-25 | 2019-07-25

the row containing ID 7 is omitted, because it is not active in the specified date range
I want to include the records with start_time 2019-07-20 because they are actually active on July 22.
If I use the following:
where start_time >= '2019-07-22' and end_time <= '2019-07-25'

...then I am excluding the records starting on July 20.
How can I include those records?

So if you're trying to find all active/valid records in a time frame, and you are using intervals, then you must use the following criteria:

End date was on/after the start of the window
Start date was on/before the start of the window

This is working on the assumption that a record is still effective as of the end date, as indicated in your example.
So your query becomes:
SELECT
  Id
 ,start_time
 ,end_time
FROM
  <your table>
WHERE
  start_time <= '2019-07-25'
    AND end_time >= '2019-07-22'

A simple truth table helps verify:
Id  | Start_Time  | End_Time    | Start <= 2019-07-25 | End >= 2019-07-22
1   | 2019-07-22  | 2019-07-25  | Y                   | Y
2   | 2019-07-20  | 2019-07-25  | Y                   | Y
3   | 2019-07-20  | 2019-07-21  | Y                   | N
4   | 2019-07-20  | 2019-07-25  | Y                   | Y
5   | 2019-07-22  | 2019-07-22  | Y                   | Y
6   | 2019-07-25  | 2019-07-25  | Y                   | Y
7   | 2019-07-26  | 2019-07-27  | N                   | Y

Other notes:
Those are dates, not times.  Column should be Start_Date/End_Date or Start_Datetime/End_Datetime if you are include hours/minutes/seconds (you can abbreviate with Dt/Dtm if you wish).
From comment:

I assume that if I want to filter only for the startdate, I can do
WHERE end_time >= '2019-07-22 00:00:00' But I am not sure about query
only for the enddate. What is the correct condition? WHERE start_time
<= '2019-07-25 23:59:59' OR WHERE '2019-07-25 00:00:00' BETWEEN
start_time AND end_time

So here we need to be careful with what question is actually be asked, and what answer the query is providing.
The statement WHERE end_time >= '2019-07-22 00:00:00' is the same as saying: any records that end OR start on/after 2019-07-22 00:00:00.
The inverse of that statement would be any records that start AND end before 2019-07-22 00:00:00, which is WHERE end_time < '2019-07-22 00:00:00'.
So if your question is actually "Which records end or start on/before 2019-07-22 00:00:00 the condition is WHERE start_time <= 2019-07-22 00:00:00


MongoDB replica set - Error in heartbeat request I have created mongo db replica set in my local as below:

    mongod --port 27021 --dbpath /cm1/mongodb/rs0-1 --replSet rs0 --smallfiles --oplogSize 128.

I have started all 3 (1 Primary, 2 secondary) instances with mongo --port 27022.

Replica set is working and data is flowing from Primary to secondaries.

If I shutdown one of the mongodb instances the remaining 2 instances are throwing errors.
Logs as below:

2017-07-25T13:01:24.904+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:24.904+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:24.906+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused
2017-07-25T13:01:24.906+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Dropping all pooled connections to localhost:27021 due to failed operation on a connection
2017-07-25T13:01:24.906+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:24.906+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:24.907+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused
2017-07-25T13:01:24.907+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Dropping all pooled connections to localhost:27021 due to failed operation on a connection
2017-07-25T13:01:24.907+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:26.908+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:26.910+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused
2017-07-25T13:01:26.910+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Dropping all pooled connections to localhost:27021 due to failed operation on a connection
2017-07-25T13:01:26.910+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:26.910+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:26.911+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused

Is there something missing  for replica while shutdown the instance?

Actually no! If you shut down one node from your replica set, other nodes, of course, start complaining that they cannot connect that missing node. All nodes are always "talking" to each other.

And error message tells that, that missing node was in the port 27021.


How to completely remove EnterpriseDB Installation of Postgresql on Ubuntu? All,

I'm trying to install/run PostgreSQL 9.3, but am receiving a multitude of errors which seem to point to a failed installation of postgresql from the enterpriseDB package.

For example, trying to start the server with this command:

sudo /etc/init.d/postgresql-9.3 restart (or start)

...yields the following:

Restarting PostgreSQL 9.3:
-su: /opt/PostgreSQL/9.3/bin/pg_ctl: No such file or directory
PostgreSQL 9.3 did not start in a timely fashion, please see /opt/PostgreSQL/9.3/data/pg_log/startup.log for details

Except there is no /opt/PostgreSQL/ folder, and I've been told that creation of that was attempted during the EnterpriseDB installation...why the start command is trying to go there, I do not know!

I've started another thread which gave me a very temporary solution, but that doesn't seem to help after I do a restart of my machine.

So the question, how do I completely remove the EnterpriseDB failed installation?

I've tried these commands, but the issue still lingers:

sudo apt-get autoremove postgresql-9.3-postgis pgadmin3
sudo apt-get --purge remove postgresql\*

Any thoughts or recommendations are welcome!

Thank you!

-m

In Feb 2009, Scahin_S  wrote in [1] to use the uninstall-postgresql binary.  In the same thread Sachin_S also documented a manual alternative for uninstalling 8.3 is

/opt/PostgreSQL/8.3/installer/server/removeshortcuts.sh /opt/PostgreSQL/8.3 8.3
/etc/init.d postgresql-8.3 stop
rm -rf /opt/PostgreSQL
rm /etc/postgres-reg.ini
rm -rf /etc/init.d/postgresql-8.3
userdel postgres

if /etc/ld.so.conf exists, edit it and remove /opt/PostgreSQL/8.3/lib
if present.

if /etc/ld.so.conf.d exists:

rm /etc/ld.so.conf.d/postgresql-8.3.conf

1 - "Uninstall Postgres" thread
Enterprise DB "PostgreSQL Installers for Windows, Linux and OS X" forum
http://forums.enterprisedb.com/posts/list/1437.page


What are current values in PG admin configuration editor for postgresql.conf I am tuning a postgresql.conf file and here is what is shown in PG admin configuration editor

What does the current value corresponds to?
For the shared buffers (red line), what is the value there?

Current value is what the currently active database is using.

many of those settings require the database to be restarted (or reboot) before they take effect.


What's the meaning of the Collation Code (blank, 90, 100, 110) in SQL Server 2012 During the installation of SQL Server 2012, in the Collation tab, i can choose between French_CI_AS and French_100_CI_AS.

What do they mean and which one is newer? When there's no Code, is it newer or older than the 100?

The one without the code is a version 90 (SQL Server 2005) collation, used by previous versions of Windows / SQL Server. The collation including 100 is newer (SQL Server 2008).

There's a bit of an overview of the differences in the SQL Server 2008 documentation, here, however it doesn't go into great detail.

If you're developing a new application, you may find that sorting is slightly more logical (particularly for unusual characters) with the 100 version. However, if you've got any other SQL Servers running with French_CI_AS, it might be better to keep it consistent with them.


How to find the stored procedure which executed an event? I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of this stored procedure.

DECLARE @path NVARCHAR(260);

SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName like N'%usp_GetPendingConfiguration%'
ORDER BY StartTime DESC;

Is there a way that I can find which stored procedure or event dropped this stored procedure? Please advice.

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where that is being executed from.

However, this does not mean that DDL Triggers are not the way to figure this out. Rather than merely capturing the SQL and trying to infer the source, since this action is unwanted (and likely breaks whatever code calls the Stored Procedure that is being deleted), it should simply be disallowed. You can capture the DROP PROCEDURE event using a DDL Trigger, and then check which procedure is being deleted via the XML returned from the EVENTDATA() function. If the Stored Procedure being deleted is the one in question, then execute something along the lines of:

RAISERROR('Ah ha! Caught you red-handed (whatever that means). No DROP for you!', 16, 1);
ROLLBACK;

Doing this:

will prevent the stored procedure from being dropped (which is the desired outcome anyway).
will identify the source(s) of the DROP query. There could be more than one source, especially if procedure calls are nested. This method will bubble the error up so that whoever, or whatever, is originating the call, will see an error message and likely alert you since their action is not completing successfully.
will not prevent any other object from being dropped.

The following is a more complete example, including the ability to also log the event, just in case, since it can at least give some insight into which person or process is doing this, and how often:

CREATE TRIGGER [PreventDropGetPendingConfiguration]
ON DATABASE
FOR DROP_PROCEDURE
AS
  SET NOCOUNT ON;

  IF (EVENTDATA().value(N'(EVENT_INSTANCE/ObjectName/text())[1]', 'sysname')
        = N'usp_GetPendingConfiguration')
  BEGIN

    -- store values in variables as ROLLBACK will erase EVENTDATA()
    DECLARE @EventTime DATETIME,
            @LoginName sysname, -- lower-case for case-sensitive servers
            @UserName sysname, -- lower-case for case-sensitive servers
            @CommandText NVARCHAR(MAX),
            @SPID INT;

    DECLARE @InputBuffer TABLE
    (
      EventType NVARCHAR(30),
      [Parameters] SMALLINT,
      EventInfo NVARCHAR(4000)
    );

    SELECT @EventTime =
                 EVENTDATA().value(N'(EVENT_INSTANCE/PostTime/text())[1]', 'DATETIME'),
           @LoginName =
                 EVENTDATA().value(N'(EVENT_INSTANCE/LoginName/text())[1]', 'sysname'),
           @UserName =
                 EVENTDATA().value(N'(EVENT_INSTANCE/UserName/text())[1]', 'sysname'),
           @CommandText =
                EVENTDATA().value(N'(EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
                'NVARCHAR(MAX)'),
           @SPID = EVENTDATA().value(N'(EVENT_INSTANCE/SPID/text())[1]', 'INT');

    -- RollBack now else logging will also get Rolled Back ;-)
    ROLLBACK;

    IF (OBJECT_ID(N'dbo.LoggyLog') IS NULL)
    BEGIN
      CREATE TABLE dbo.LoggyLog
      (
        LoggyLogID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
        EventTime DATETIME NOT NULL,
        LoginName sysname, -- lower-case for case-sensitive servers
        UserName sysname, -- lower-case for case-sensitive servers
        CommandText NVARCHAR(MAX) NOT NULL,
        SPID INT NOT NULL,
        EventInfo NVARCHAR(4000) NULL
    );
    END;

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'DBCC INPUTBUFFER ( ' + CONVERT(NVARCHAR(10), @SPID)
               + N' ) WITH NO_INFOMSGS;';

    INSERT INTO @InputBuffer (EventType, [Parameters], EventInfo)
      EXEC(@SQL);

    INSERT INTO dbo.LoggyLog (EventTime, LoginName, UserName, CommandText,
                              SPID, EventInfo)
      SELECT  @EventTime, @LoginName, @UserName, @CommandText, @SPID, tmp.EventInfo
      FROM    @InputBuffer tmp;

    RAISERROR('Ah ha! Caught you red-handed (whatever that means **). No DROP for you!',
              16, 1);
  END;

GO

Any attempt to drop this Stored Procedure will receive the following error:

  Msg 50000, Level 16, State 1, Procedure PreventDropProcedure, Line 7
  Ah ha! Caught you red-handed (whatever that means). No DROP for you!
  Msg 3609, Level 16, State 2, Line 1
  The transaction ended in the trigger. The batch has been aborted.

The reason I used DBCC INPUTBUFFER instead of sys.dm_exec_sql_text is that sys.dm_exec_sql_text returns the current query being executed. If sys.dm_exec_sql_text is queried natively within the Trigger itself, you end up with the CREATE TRIGGER... statement. If that DMV is queried in Dynamic SQL or a sub-stored procedure call, then you would get those particular queries and not even the CREATE TRIGGER that they were called from. All of that is useless.

In contrast, DBCC INPUTBUFFER reports the first batch (not just the current query) in the chain, and that can at least be used to trace any number of subsequent calls that lead to the DROP call.

Also, given that this only happens sometimes, it is possible that someone forgot a GO in a release script that was doing a CREATE PROCEDURE just before the DROP PROCEDURE was called, and accidentally made the DROP query part of the Stored Procedure that was being created (this happens more often with GRANT EXECUTE statements since they typically follow CREATE PROCEDURE statements). This can happen due to something like the following being in a release script:

CREATE PROCEDURE dbo.ProcName
AS
...

-- missing GO !!!!

IF (OBJECT_ID(N'dbo.ProcGettingDropped') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.ProcGettingDropped;
END;
GO -- this GO terminates the CREATE PROCEDURE statement

You can search for occurrences of this in the database containing the Stored Procedure that is getting dropped by running the following query:

SELECT OBJECT_NAME([object_id]) AS [ObjectName], *
FROM   sys.sql_modules
WHERE  [definition] LIKE N'%DROP%';

** etymology of red handed (thanks to @MartinSmith)


Trying to run a procedure, error says result consist more than one line There is an officecode and employeeNumber in the employees table, which references city in office table.

I want to create a stored procedure setRelocationFee to set the relocation fee for a given employee. If the employee’s office is in San Francisco, the relocation fee is $10000; if the employee’s office is in Boston, the relocation fee is $8000; if the employee’s office is in London, the relocation fee is $20000; if the employee works in other offices, the relocation fee is $15000.

I used these codes to test the procedure, and it gives me error saying result consist more than one line:

set @empID = 1501;  call setRelocationFee(@empID, @Rfee);   select @empID, @Rfee;

DELIMITER |

CREATE PROCEDURE setRelocationFee(IN empID INT(20), OUT Rfee INT(20)) BEGIN
    DECLARE relocation varchar(50) default 0;
    SELECT city INTO relocation
    FROM employees LEFT JOIN offices ON
    employeeNumber = empID;

    CASE relocation
    WHEN 'San Francisco' THEN SET Rfee = '$10000';
    WHEN 'Boston' THEN SET Rfee = '$8000';
    WHEN 'London' THEN SET Rfee = '$20000';

    ELSE
    SET Rfee = '$15000';
    END CASE;  END |

I don't understand why does you want to implement this in the SP form, but if, then (a variant with minimal edition)

DELIMITER |;

CREATE PROCEDURE setRelocationFee(IN empID INT(20), OUT Rfee INT(20))
BEGIN
    DECLARE relocation varchar(50) default 0;
    SELECT city INTO relocation
    FROM employees
    LEFT JOIN offices ON employeeNumber = empID;

    SET Rfee = CASE relocation
               WHEN 'San Francisco' THEN '$10000'
               WHEN 'Boston'        THEN '$8000'
               WHEN 'London'        THEN '$20000'
               ELSE                      '$15000'
               END ;
END |;

DELIMITER ;


DB choice for a geodata application I'm writing a client-server application where geotagged photos are main content.

Backend is based on ASP.NET Core tech stack.

Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

I'm not really sure if i need relational DB for such a simple task.
Also i'd love to see search by distance from point made by DB not by my code.
Good integration with .NET Core is a great plus.

I've worked with SQL Server from MSFT, but it seems a bit overweight for this simple task.

Could you guys with some experience in area, advise a DB for my task?

PostgreSQL

Go check out the book PostGIS in Action Second Eddition.

PostGIS has everything you're requesting.

  Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

CREATE TABLE photos (
  photoid  serial        PRIMARY KEY,
  path     text,
  tags     text[],
  location geography,
  meta     jsonb
);

INSERT INTO photos(path,tags,location, meta)
VALUES
  (
    '/foo/asdf.jpg',
    ARRAY['person', 'america', 'male'],
    ST_MakePoint(5.493,3.2948),
    '{"CAMERA":"NIKON"}'::jsonb
   );

SELECT *
FROM photos
WHERE tags @> ARRAY['person'];

SELECT *
FROM photos
WHERE ST_DWithin(ST_MakePoint(0,0)::geography, location, 10000);


Query with group by having count 0 I am working on application for managing football league as my homework. I want filter list of players by number of matches they have played in league. For example user can enter count > 0 and count < 5.

I wrote query which works fine when count is bigger than zero. However, in some cases user may want include players that weren't in any matches, yet. For example:

SELECT p.PLAYER_ID, p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM FROM player p
 LEFT JOIN player_in_squad psq ON p.PLAYER_ID = psq.PLAYER_ID
 LEFT JOIN squad sq ON sq.SQUAD_ID = psq.SQUAD_ID
 LEFT JOIN squad_match sqm ON sqm.SQUAD_ID = psq.SQUAD_ID
 LEFT JOIN match m ON m.MATCH_ID = sqm.MATCH_ID
     WHERE sqm.HOME = 'N'
     AND m.LEAGUE_YEAR = 1
     AND m.LEAGUE_TYPE_ID = 301
    GROUP BY p.PLAYER_ID,  p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM
    HAVING COUNT(*) < 2;

In this query only count > 0 is returned. So I have created manual condition which is detecting if user wants include count = 0 in his filtered list and then I add this to beginning of the query:

SELECT p.PLAYER_ID, p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM FROM player p INTERSECT

... but I don't like this workaround solution and I suppose neither my teacher will.

I think there must be better approach. Any ideas? Thy

Having clause can only exclude rows which exist in data - in your case only possibility to get count(*)=0 means having no matching rows - so nothing to return in the first place.

You probably want to count existing matches in the left join - so COUNT(m.MATCH_ID) will only count rows where match_id is not null.

Edit: One more thing I noticed - you have lot of conditions in WHERE which touch left-joined tables - this will actually cancell the effect of left join, because ie "m.LEAGUE_YEAR = 1" is false when no matchng row exists and so league_year is null - you want to move those conditions to ON as "join-cond AND additionalCont1 AND ..." for each table (m and sqm in your case) - then it will get the chance to actually give you some of those players without a match.


How to implement database mirroring using SQL on two geographically separate servers I need to implement database mirroring on two geographically separate servers.

I have been tasked with ensuring that there should be a backup of the databases that is not more than 1 day behind the live databases.

This is a link of a sketch of what I currently have installed on the servers and how the servers are connected via a WAN. My question is, given my current configuration.

What do I need in order to make mirroring possible? Essentially, what do I need to purchase or upgrade in order to make mirroring possible. Thanks in advance.

For mirroring the requirements are as follows:

Install same version of WINDOWS server and SQL SERVER`
Set same password with same username on windows as well as sql server`
Check the port whether 1433 port is available or not,It is sql server default port.
Check whether 5022, 5023 port opened or not(It is sql server mirroring port.)
After this you can set up mirroring.

Mirroring can be done on same version and edition of sql server.
In case of enterprise edition you can use full features of database mirroring. We can take snapshot backup of database and verify from secondary end.

And in case of standard edition mirroring can be set up but to verify on secondary side mirroring must be broken.


Select where next row's value is greater I have a road_insp table.

  create table road_insp
   (
    insp_id int,
    road_id int,
    insp_date date,
    condition number(38,2)
   ) ;

insert into road_insp (insp_id, road_id, insp_date, condition) values (1,1,to_date('01-JAN-01','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (2,1,to_date('01-JAN-04','DD-MON-YY'),09);
insert into road_insp (insp_id, road_id, insp_date, condition) values (3,1,to_date('01-JAN-07','DD-MON-YY'),08);
insert into road_insp (insp_id, road_id, insp_date, condition) values (4,1,to_date('01-JAN-10','DD-MON-YY'),06);

insert into road_insp (insp_id, road_id, insp_date, condition) values (5,2,to_date('01-JAN-02','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (6,2,to_date('01-JAN-05','DD-MON-YY'),08);

insert into road_insp (insp_id, road_id, insp_date, condition) values (7,4,to_date('01-JAN-03','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (8,4,to_date('01-JAN-06','DD-MON-YY'),12);
insert into road_insp (insp_id, road_id, insp_date, condition) values (9,4,to_date('01-JAN-09','DD-MON-YY'),08);

insert into road_insp (insp_id, road_id, insp_date, condition) values (10,5,to_date('01-JAN-01','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (11,5,to_date('01-JAN-03','DD-MON-YY'),09);
insert into road_insp (insp_id, road_id, insp_date, condition) values (12,5,to_date('01-JAN-06','DD-MON-YY'),08);
insert into road_insp (insp_id, road_id, insp_date, condition) values (13,5,to_date('01-JAN-09','DD-MON-YY'),07);
insert into road_insp (insp_id, road_id, insp_date, condition) values (14,5,to_date('01-JAN-12','DD-MON-YY'),06);
insert into road_insp (insp_id, road_id, insp_date, condition) values (15,5,to_date('01-JAN-15','DD-MON-YY'),05);
insert into road_insp (insp_id, road_id, insp_date, condition) values (16,5,to_date('01-JAN-18','DD-MON-YY'),20);

select
    *
from
    road_insp
order by
    road_id,
    insp_date

   INSP_ID    ROAD_ID   INSP_DATE  CONDITION
---------- ----------   --------- ----------
         1          1   01-JAN-01         10
         2          1   01-JAN-04          9
         3          1   01-JAN-07          8
         4          1   01-JAN-10          6

         5          2   01-JAN-02         10
         6          2   01-JAN-05          8

         7          4   01-JAN-03         10
         8          4   01-JAN-06         12 <-error
         9          4   01-JAN-09          8

        10          5   01-JAN-01         10
        11          5   01-JAN-03          9
        12          5   01-JAN-06          8
        13          5   01-JAN-09          7
        14          5   01-JAN-12          6
        15          5   01-JAN-15          5
        16          5   01-JAN-18         20 <-error

For each road, I want to know if the condition ever goes UP over time. The idea being, roads don't just magically improve in condition.

If the condition of the road ever goes up, then the culprit inspection should be flagged.

How can I analyze this table to find anomalies (conditions that go up)?

Example:

      INSP_ID   ROAD_ID     COND_ERROR_DATE  COND_ERROR
    ---------- ----------   ---------------- ----------
            8           4   01-JAN-06                12
           16           5   01-JAN-18                20

select
  insp_id, road_id, insp_date as cond_error_date, condition as cond_error
from
(
  select
    insp_id, road_id, insp_date, condition,
    lag(condition) over (partition by road_id order by insp_date) as condition_before
  from
    road_insp
)
where condition > condition_before;

   INSP_ID    ROAD_ID COND_ERRO COND_ERROR
---------- ---------- --------- ----------
         8          4 01-JAN-06         12
        16          5 01-JAN-18         20


Dynamic file name for file import I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found.

DECLARE @fileName varchar(200),
    @sql varchar(max);

SET @fileName = 'C:\file.csv';

SET @sql = 'SELECT *
    INTO #import
    FROM OPENROWSET(BULK ''' + @fileName + ''',
    FORMATFILE=''C:\format.xml'',
    FIRSTROW = 2) AS a';

EXEC(@sql);

SELECT * INTO #stage
FROM #import;

The result

(27823 row(s) affected)
Msg 208, Level 16, State 0, Line 29
Invalid object name '#tmtImport'.

Use ##import as your table. Global temp table will last for the life of the connection. The #import tables goes out of scope after the exec


Equivalent of include clause while creating non clustered index in sybase? I searched on creating non clustered index with included columns for sybase database. But I don't think "include" is allowed while creating index. Is there any equivalent in sybase?

No. Sybase doesn't have any equivalent to the INCLUDE statement.


How can extract the table schema from just the .frm file? I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine.

I have read a fair number of tutorials today on how to do this, but each time it seems to fail or I end up having to reinstall mysql because mysql hangs or crashes. I have tried the following:

Create a different database
Create table with same name in that database
Replace file
Stop/Start engine
Recover from the .frm file

I have tried in various orders and combinations.

Is there any external tool that can extract the schema from the .frm file? I can see the column names if I open up the file. I had a look but I can't seem to find anything that will enable me to do this.

Thanks in advance.

I have answered questions like this before

Dec 21, 2011 : Table compression in InnoDB?
Feb 03, 2012 : How to recover MySQL table structure from FRM files
Apr 23, 2012 : restore table from .frm and .ibd file?

I have referred to a blog from Chris Calendar on connecting .ibd files back to the data dictionary.

If you do not have the .ibd along with .frm, chances are MySQL was running with innodb_file_per_table off. Please refer to the Feb 03, 2012 post on that. If this is Linux, make sure you run

cd /var/lib/mysql
chown -R mysql:mysql *

before starting mysql.

Give it a Try !!!

SUPPLEMENTARY INFO

Percona's mysqlperformanceblog.com has information on this from over 4 years ago
MySQL Utility Team has the tool mysqlfrm.

At the bottom of that last blog it says:

  Ok, I'm Hooked! Where Can I Get It?

  The .frm reader utility is part of the new release-1.3.0 Alpha
  available as a separate download at
  http://dev.mysql.com/downloads/tools/utilities/. Simply choose the
  platform repository or source repository and download it.

  We welcome your comments and hope that this utility will help expand
  your diagnosis and recovery toolkit.

UPDATE 2013-06-11 14:05 EDT

I have good news. I installed the Windows version of the MySQL Utilities. I tested the mysqlfrm program from the command line. I tested getting the .frm info from the user.frm in the mysql schema. Here is what I got:

C:\MySQL_5.6.10\data\mysql>mysqlfrm --server=root:rootpassword@localhost mysql:user.frm --port=3307
# Source on localhost: ... connected.
# Starting the spawned server on port 3307 ... done.
# Reading .frm files
#
# Reading the user.frm file.
#
# CREATE statement for user.frm:
#

CREATE TABLE `mysql`.`user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

#...done.

C:\MySQL_5.6.10\data\mysql>

It really works. The database is running on my desktop computer using port 3306. I had to give 3307 as a port to run a spawned process.

I could move the .frm from a Linux box to the Windows machine and do the same.

Give it a Try !!!


Does SQL Server optimize two ">" operations on same column Can SQL Server optimize the following:
DECLARE @id INT;
DECLARE @d1 DATE;
SELECT a, b
FROM t
WHERE id_column = @id
AND date_column >= CAST(CURRENT_TIMESTAMP AS DATE)
AND date_column >= @d1

Notice that there are two > operators on the same column. Assuming that today is 2020-12-23 and d1 could be either 2020-12-20 or 2020-12-27, I was hoping that SQL Server would automatically use greater of the two dates... 2020-12-23 in the first case and 2020-12-27 in the latter. But I am not sure.

I tested it on a table in my database that has a date column.
https://www.brentozar.com/pastetheplan/?id=r1V9bJ-av

Looking at the query plan, in SQL Server 2019 Dev edition, what SQL did was, it scanned the current timestamp and the date specified and created a date range. Then it scanned my table to find the rows in that range (none).
At first I thought it was searching for a range between the two dates.

but when looking at the logical reads, I think what it did was search between "CAST(CURRENT_TIMESTAMP AS DATE)" and "NOW" which is a few hours. So I think SQL did exactly what you expect it to do, which is optimize the ">" operation on the same column.
 Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Furthermore, if I change the query to do an "OR" instead of an "AND", I get 84 logical reads instead of 4 and many more rows returned on the index seek. Which indicates to me that SQL Server 2019 is smart enough to optimize the query with two ">" on the same column.
https://www.brentozar.com/pastetheplan/?id=BkbGf1Wpw
Now that might not apply all the time depending on the complexity of your query or if you have many more conditions in your where clause. Testing it in your environment is the key.