All Questions

0
votes
1answer
20 views

Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as ...
0
votes
0answers
7 views

Limiting the number of CPUs used in SQL Server 2012 Enterprise

The platform under consideration is SQL Server 2012 Ent. SP3. We are in the process of migrating a SQL Server environment to VM but highly feel the server was over spec'd to start. We want the ...
19
votes
5answers
5k views

Is ROLLBACK a fast operation?

Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
0
votes
0answers
8 views

How to change the expiration date of binlog files?

I have a server with Ubuntu 18.04 and MySQL 8.0.11 I have binlog files in /var/lib/mysql that make several GO and my server will soon saturate. How to change the expiration date of binlog files ? ...
1
vote
0answers
3 views

PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

This is my remote function: CREATE OR REPLACE FUNCTION public._test1() RETURNS record LANGUAGE plpgsql AS $function$ DECLARE rec record; BEGIN select 1,2 into rec; return rec; END $function$; ...
1
vote
0answers
16 views
+50

How to handle schema changes in source systems when running an ETL?

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.
0
votes
0answers
11 views

MySQL throws error while secure_installation

I am installing MySQL using link: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-centos-7 But after installing step 3 for secure_installation sudo mysql_secure_installation. ...
3
votes
0answers
41 views

Solving supertype-subtype relationship without sacrificing data consistency in a relational database

Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem. Here is an example using common requirements: An online store sells ...
0
votes
0answers
43 views

How do I add a directory, or bulk, of users to Microsoft SQL Server permissions

I have a front end program in Visual Basic that is using SQL Server as a "back end". The only way I can get permission for login to use the SQL server is by adding each name or computer individually ...
1
vote
1answer
62 views

Query Index question

I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the ...
0
votes
0answers
8 views

SQL Server Threshold of Write per X when considering Indexes?

I am not entirely sure if this is even a question worth asking. However, I was curious as if there was some sort of standard, or specifically a metric, that some more seasoned DBA's use to gauge ...
0
votes
2answers
54 views

What are the disadvantages of using surrogate keys? [closed]

I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value. I know some advantages, for ...
4
votes
0answers
83 views

Database design for ~ 2.7B rows per year / 26.000 data points every five minutes

The current situation We're currently looking into a new product that will send device data back to us to interpret. These are the numbers we're looking at: Devices will most likely send data every ...
0
votes
0answers
2 views

AWS DynamoDB : On-Demand vs Provisioned capacity cost comparison

Is there any calculator available which does the cost comparison of On-Demand vs Provisioned capacity? Need to know at what point, it becomes cost effective to switch to provisioned capacity from on-...
0
votes
0answers
10 views

Generate PostgreSQL schema using generator like an ORM (like ActiveRecord or Ecto etc)

I'm wondering if there is a software (a generator, a web page, an executable, a command line, etc.) that allows me to generate a PostgreSQL scheme since I'm not an expert and I wouldn't know where to ...
0
votes
1answer
21 views

Case Statement for on same month

i have a CASE statement, which classify my Customer-Revenue and set Flag, when Revenue <> 0 is. But my problem i should search also same month record on my case statement. Logic : if two records (...
2
votes
1answer
95 views

Linked server issues and administration

We have around 15 linked servers in each of 2 servers and application is querying the views and stored procedures using these linked servers. I would like to know Is there a way to find from SQL(...
0
votes
0answers
10 views

Modelling a school timetable database

I'm working on a design for a database for school timetable and I need some help. So, basically, the database is made up of tables like teacher, student, subject, TimePeriod, and Classroom. I would ...
1
vote
2answers
76 views

Mapping a 1:1:N ternary relationship to a relational schema

I have a question about mapping (conceptual) n-ary relationships to (logical) relations. Assume we have three strong entity types: Member, Equipment, and Time Slot, and a relationship named Reserves ...
39
votes
4answers
67k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks ...
2
votes
1answer
43 views

Databse design for a part/subassembly list

We work with tracking the fabrication of large assemblies and sub-assemblies with a large flat Excel files. Usually separate spreadsheets for estimating material costs, fabrication costs, keeping ...
0
votes
1answer
20 views

Grant CREATE SCHEMA (only) to users

Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth). The public space is where I ...
0
votes
0answers
17 views

Find “Mystery processes” on a MS SQL Sever environment? (SSIS, databases, etc)

I inherited a collection of Instances/ MS SQL Databases/ SSIS scripts on a server. I am now the interim "accidental DBA." I thought I was familiar with everything there, but apparently not. It's all ...
0
votes
0answers
7 views

SSMS is crashing and SSMS Toolbar

I'm new in SQL Server and i have installed SQL Server on my system for testing purpose. Issue 1: I want to convert the name of all the toolbar in menu tab to icon. I have no clue from where does it ...
0
votes
1answer
14 views

Limiting Common Table Recursion

I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code. SELECT id,MK, pid,AFI FROM M A WHERE LEVEL <= 2 -- limiting iterations ...
0
votes
0answers
6 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
0
votes
1answer
86 views

MYSQL Group Replication

I have setup a Master-Master-Slave replication where say A B and C, I want to replicate data from A to B (which is working) and B to C (also working) and from A to C which is not working with two ...
0
votes
1answer
9 views

use SQLcl with EZconnect and multiple hostnames (ADDRESS_LIST=)

this is our classic tnsnames.ora test1= (DESCRIPTION= (CONNECT_TIMEOUT=4) (TRANSPORT_CONNECT_TIMEOUT=3) (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=example1....
0
votes
0answers
11 views

InnoDB full of data dead

I have a DB in server that gives this error after filling the database with 12GB of data 2019-03-22 07:50:05 18705 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it....
0
votes
0answers
18 views

Retrieving value from JSON by key returns NULL

Please help with understanding why function returns NULL? CREATE OR REPLACE FUNCTION evaluate_score(input_data anyelement, hkey VARCHAR(32)) RETURNS float AS $final_score$ DECLARE final_score ...
3
votes
4answers
4k views

Stored procedure working when run manually, not running in SQL Agent Job

When I execute a Stored Procedure through a SQL Agent Job, I get the following error message: Executed as user: NT AUTHORITY\SYSTEM. XML parsing: line 10, character 33, unexpected end of input ...
0
votes
0answers
12 views

SSRS Report - 'Go to URL' not working. Link does nothing

I have a report that I moved from one location to another. The report has several 'Go to URL' links and the location of the files has changed from the original report. The files I am trying to link ...
5
votes
2answers
9k views

How can I check if connection to Sql Server is encrypted?

In Sql Server I can Force Encrytion in protocols settings. If I leave that set to No any client can still require encryption. Can I somehow see what connections to the database are using encryption?
0
votes
0answers
7 views

How to eliminate for loop in a nested aggregate problem

I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
0
votes
1answer
19 views

My disk is full of binlog files

I just realized that my server is growing by 2 GB per day, which is not normal. I looked at my folder and the folder / var / lib / mysql reached 26 GB Normally it should be about 4 GB A month ago I ...
1
vote
0answers
14 views

PG4Admin “local variable 'debugger_version' ”?

My Postgres version is 9.6, running on Windows 7 Professional. I have just upgraded to pgAdmin 4.2.3 since I was not able to get the debugger to work at all with my previous pgAdmin. I installed ...
0
votes
1answer
28 views

Deleting multiple tables in a single delete query in Mysql

I have a task to delete records from multiple tables (say 4 or more). I came to know that I can do the delete in a single MySQL query as follows DELETE table1,table2,table3,table4 FROM table1 ...
5
votes
2answers
125 views

How can I export Query Store data?

I have Query Store (QS) running on a SQL 2017 instance. Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window. While most queries and reports return ...
1
vote
2answers
24 views

Run shell script on Oracle 11g

I have an oracle database: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0 Production" TNS for Linux: Version 11.2.0.1.0 - ...
1
vote
2answers
88 views

Query execution time is random, need to identify the root cause

I have a query like below select * from table1 where column2=123 and column3='B' Now the issue faced by us is that this query is executing very slowing sometimes as seen in our performance tracking ...
0
votes
1answer
11 views

Use data from Mysql to ElasticSearch with Logstash

I'm using logstash for use my mysql database in ElasticSearch My conf is the next. input { jdbc { jdbc_connection_string => "jdbc:mysql://[ip]:3306/nextline_dev" jdbc_user =&...
0
votes
0answers
9 views

Dividend reinvestment into shares query

I'm gonna get straight to the problem: Image dividend reinvestment as this scenario: You buy 10 shares of AAPL on 2018-01-01 for $10 -- AAPL pays $1 / per share dividend on 2018-02-01 -> you get $10 ...
2
votes
1answer
31 views

Logon Trigger Unexpected Result

I have only server logon trigger and it is being used to prevent logins coming from an application/server combination. It works appropriately for that application/server combination and gives the ...
0
votes
0answers
24 views

How to join these queries?

So I currently have this query: SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer) FROM reserveringen r, voorstelling v WHERE r.Voorstellingsnummer = v.Voorstellingsnummer GROUP BY r....
2
votes
1answer
101 views

How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts

I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD. I couln'd find any valuable information so far unfortunately. ...
0
votes
1answer
102 views

SQL Server 2016 migration to Azure

Trying to migrate SQL Server to Azure, MS Data Migration tool indicates need to delete SSIS. Using advice from Stackflow and others, go into programs and features and SSIS is not listed as a component....
5
votes
2answers
56 views

Database Encryption scan for database <name> was aborted. Reissue ALTER DB to resume the scan

I disabled TDE on a database and once completed I dropped the DB certificate for it. Later I performed a manual failover to node2 and ran steps to enable TDE once again. However encryption state is ...
0
votes
0answers
17 views

I have one table for users, one for a collection of games. My users can own one or many games. How do i structure that in my database?

Since i can't store lists, do i add a field in my users table called 'owned games' and then just store a long string like "game1, game2..."? Or do i add another table called something like "ownership"...
1
vote
1answer
23 views

How to change SQL Server master key password?

I created a master key, then a certificate and encrypted my database. CREATE MASTER KEY ENCRYPTION BY PASSWORD='A Very Strong Password'; GO CREATE CERTIFICATE foo WITH SUBJECT='Database Encryption ...
10
votes
2answers
2k views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...

15 30 50 per page