The discipline of Operational and Architectural Management of Database Systems.

learn more… | top users | synonyms

3
votes
1answer
504 views

How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option --single-...
3
votes
4answers
2k views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of roles/...
3
votes
1answer
79 views

Administration of MySQL Users

What is the best practice for keeping and maintaining MySQL users? Say for example I have 5 programmers and a PHP application connecting to a database. Should I create 6 different users 1 for each ...
3
votes
1answer
2k views

Master and Slave having same id's

I am working as a remote DBA for some of the production servers. There was replication from Master to Slave. There is something changed in the scenario they have made one of the Slaves as master ...
3
votes
1answer
761 views

Pgpool-II: sequences advanced by 1 after PITR recovery

I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4 In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes ...
3
votes
1answer
51 views

help in the initial configuration of postgreSQL9.6

I recently installed postgreSQL9.6 version on windows 7 64 bit machine. During the installation process, I was asked to key in the super user password and the locale of the database (I chose default ...
3
votes
1answer
190 views

Can a Table Lock eliminate a deadlock or make it worse Oracle

We have a situation in which an Oracle Database is having deadlocks issues. I said that perhaps we should lock into some table locks, and a guys response to me was "Unless I am misunderstanding ...
3
votes
1answer
173 views

Create Database and LCK_M_S

We are experiencing issues when creating a database in the SSMS GUI in that sometimes it times out complaining that it could not get an exclusive lock on the Model database. Other times the database ...
3
votes
1answer
2k views

How to create Postgres DB with case insensitive collation

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and ...
3
votes
1answer
158 views

How to directly query other user tables

I am trying to learn Oracle database and I am having difficulties on importing a backup file with impdp command. I googled deeply but couldn't find answers to my problems. What have I done until now ...
3
votes
1answer
8k views

Error Code: 1133. Can't find any matching row in the user table

Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this: mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password'; Query OK, 0 ...
3
votes
1answer
205 views

How to make PostgreSQL default_tablespace work properly?

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'? I made the following script, with it's output following, to show the problem I am having : #!/bin/bash # export OWNER=...
3
votes
1answer
1k views

Why is the “The package can not be saved to the file system since its protection level is server storage” error?

When I execute dtutil /sourceserver localhost /SQL "\Maintenance Plans\MaintenancePlan_daily" /copy file;.\MaintenancePlan_daily.dtsx from the answer to my question "Any way to import-export-...
3
votes
1answer
131 views

SQL Server database schema (and likely, some data changes) - how to auto-distribute over many database instances

Our development involves a SQL Server database (also might be Oracle or Postgres later) and we would sometimes make database schema changes or some other interventions in database. What solutions ...
3
votes
1answer
116 views

Does avg_query for PgBouncer include update and inserts?

Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage: avg_query Average query duration in ...
3
votes
2answers
8k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
3
votes
1answer
1k views

MySQL specific database configuration file

In MySQL's configuration file I've globally disabled autocommit as so. [mysqld] autocommit=0 I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the ...
3
votes
1answer
9k views

Is it possible to do a datapump import with a different user than did the export?

I have one db dump below is the log file for that dump Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** ...
3
votes
1answer
5k views

Cannot browse for Backup files using SSMS [duplicate]

I'm facing a strange problem. When I try to backup or restore any database using a SQL Server user (not sa) that belongs only to "public" server role, i have the following error when i click the ...
3
votes
0answers
60 views

When do MySQL DBA need root access

For a MySQL DBA, what operations require root access. I know that we need to be root for installation, starting and stopping services. With DBA’s not having root access, is there a way to perform ...
3
votes
0answers
127 views

How can I generated a list of all DB2 Objects including Last Modified Date of object on AS400? [closed]

I am going through an audit, of an AS400 application. AS400 has a DB2 database, and I need to generate a list of all DB2 Objects/Tables including their last modified date. Can anyone help me? Thanks
3
votes
0answers
80 views

PostgreSQL Streaming Replication Tools - Windows

Is there any clusterware available for PostgreSQL clusters running on Windows OS? I'm specifically interested in automatic failover and STONITH functionality. I've done a fairly extensive Google ...
3
votes
0answers
81 views

Need a way to prevent log shipping from kicking users out

We have SQL Server 2012. A service we use requires us to use log shipping, however we are tired of having users being kicked offline every 15 minutes. Are there any creative ways to get around this (...
3
votes
1answer
37 views

SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table? Table exists on Database A (SQL ...
3
votes
0answers
17k views

Importing a different character set export dump into another DB instance

I am trying to import a given dump file using oracle imp utility. However; I am facing the following issues. C:\Users\AD>imp DB_USER/*****@dev1 file=C:\test\db42312.dmp log=C:\test\import2.log ...
3
votes
0answers
1k views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: ...
3
votes
0answers
586 views

How to properly configure pgBouncer transaction level pooling?

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
3
votes
1answer
2k views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
3
votes
0answers
677 views

SQL Developer Data Modeller and Data Dictionary question

I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please? I can connect SQL Developer to Oracle XE's data dictionary without any problem, ...
2
votes
3answers
290 views

How to validate and upgrade my knowledge of SQL Server with MCSA [closed]

My story is that I am working now almost 10 years as an administrator, I deal with SQL Server and other IT stuff, most of my knowledge is self taught by reading books, dealings with problems and ...
2
votes
2answers
22k views

Database running out of space

My database has 16MB of space left. I used to just truncate as I was taught but I found these links that advise against truncating: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-...
2
votes
2answers
708 views

Generalisation in databases

I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c. Is it better to ...
2
votes
3answers
1k views

Oracle 12c in-memory option

I've been told that there's an oracle in-memory option for 12c and that it uses columnar compression to get some great query speeds. I'd like to leverage it for some business intelligence stuff. I've ...
2
votes
3answers
280 views

Reasons to Backup (or not backup) QA and Dev Databases

Pretty simple question: is it standard/best practice to backup dev and QA instances of the database? Assume that the schema information is kept in some form of source control.
2
votes
2answers
102 views

which option is better for not slow down the performance of queries?

I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
2
votes
1answer
307 views

Do you feel like DBA/DBD Is a “secure Field” still? [closed]

I noticed recently alot of Database jobs/Development have kind been pushed onto general developers, not to mention with NoSQL Solutions (Which IMO are probably a little easier for Non-DBA's to throw ...
2
votes
1answer
208 views

Plan cache behaviour

Can someone please explain me why the following is no getting cached use AdventureWorks2014 go DECLARE @id INT=43865 SELECT sp.[BusinessEntityID] , so.salesorderid FROM sales.salesorderheader so JOIN ...
2
votes
1answer
974 views

Create a sub query for sum data as a new column in SQL Server

Suppose that I have a table named tblTemp which has this data: | ID | AMOUNT | ---------------- | 1 | 10 | | 1-1 | 20 | | 1-2 | 30 | | 1-3 | 40 | | 2 | 50 | | 3 | 60 | ...
2
votes
1answer
2k views

Solve conflict between affinity mask and I/O affinity mask?

I executed this on one of our SQL Server machines: EXEC sp_configure 'show advanced options', 1 And then executed RECONFIGURE. This presented me with the following error message: The affinity ...
2
votes
2answers
6k views

How to deny drop permission for a table?

Is there a way to deny drop permissions for a specific table from a user or role?
2
votes
2answers
7k views

Help with SQL Server Error “ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'”

So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB ...
2
votes
2answers
559 views

How can I prevent users from overwriting databases when restoring?

I don't want users to restore (with overwrite) on some specific databases. How can I inhibit from restoring over these databases?
2
votes
1answer
6k views

DBF file exist while user has been dropped?

I am new to Oracle. I have dropped some users from my database using: DROP USER username cascade; The issue is that the .dbf file still exists in the $ORACLE_HOME/dbs/oradata/ directory, which is ...
2
votes
1answer
4k views

How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

LOB_DATA : varbinary(max),varchar(max), image , text columns. I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different ...
2
votes
2answers
668 views

Duplicate login details per user in error log

Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However I have a scenario that I need help with: For some reason ...
2
votes
2answers
71 views

Give every user 3 default 'bookmark' entries

I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too. But, there's a new requirement now: every user gets 3 unmutable bookmarks. ...
2
votes
1answer
2k views

Is DBCC SHOWFILESTATS an accurate way to see unused space?

We have a few tables each with near 100 million rows of data. A co-worker has these tables setup with partitions based on a date field. Our disk space is starting to run low and knowing that ...
2
votes
1answer
139 views

SQL Server - Data and Log Separation

We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is ...
2
votes
1answer
183 views

Will a primary key be added as a clustered index?

I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables. If I perform an alter table to assign a primary key will this ...
2
votes
2answers
398 views

Database structure for a system with multisite

The system I'm working is structured as below. Given that I'm planning to use Joomla as the base. a(www.a.com),b(www.b.com),c(www.c.com) are search portals which allows user to to search for ...