1
vote
1answer
2k views

PostgreSQL user permissions

I have a PostgreSQL 9.3 database with the PostGIS extension backing a web application and GeoServer. I have created three users. A read only user for accessing the data in the reference data schema. ...
4
votes
1answer
1k views

Replace MySQL table with data from csv

I have a CSV file on about 200 MB/1 mill rows, which is updated every night. I need the values from this file available in a MySQL table. I get this working by using LOAD DATA INFILE filepath REPLACE ...
0
votes
1answer
210 views

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

When a stored procedure begins with 4 create or replace procedure user0.import 5 is 6 f1 utl_file.file_type; compiler throws an error Error(6,6): PLS-00201: identifier 'UTL_FILE' must be declared....
0
votes
0answers
566 views

ClassNotFoundException when trying to deconfig Oracle Enterprise Manager

The Enterprise Manager for my Oracle 11.2 installation doesn't work anymore as described in How can I change the connection descriptor used by Oracle Enterprise Manager? and I tried to re-configure it ...
0
votes
2answers
49 views

Combine tables only if no results

We have a table FOO that has a huge amount of data in it, most of which is only used infrequently and by (slow) user-interaction. We would like to move all data that is more than 12 months old to an ...
1
vote
3answers
234 views

Creating a copy of primary database and attaching the copy database in the same sql instance

I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012. As I don't have SQL Server 2012 or supporting OS, I ...
6
votes
2answers
9k views

PostgreSQL High Availability/Scalability using HAProxy and PGBouncer

I have multiple PostgreSQL servers for a web application. Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication). I use PGBouncer for connection pooling: ...
5
votes
1answer
649 views

Is Oracle's dbms_utility.get_hash_value algorithm documented do reverse engineered anywhere?

I'm working with an existing database where passwords are hashed like this: DBMS_UTILITY.GET_HASH_VALUE(the_username || the_password, 1000000000, 1073741824) I know that this is bad for various ...
1
vote
2answers
628 views

Indexing varchar column to search for first letter

I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character. The query to optimize is: SELECT oggetto.id_oggetto, titolo, anno, ...
1
vote
3answers
107 views

Make column default a query

Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
0
votes
1answer
2k views

ORA-12899 when inserting from sqlplus, but ok with jdbc

Inserting 'ééééé' into a column defined as varchar(5) runs fine from squirrel sql client (jdbc). But doing the same insert from sqlplus fails with ORA-12899 (too long value). NLS_CHARACTERSET is set ...
0
votes
0answers
2k views

Oracle Transaction recovery: lock conflict caught and ignored

In alert log there are a lot of "Transaction recovery: lock conflict caught and ignored" statements. I've found some information, that this error is caused when there is an active dead transaction. ...
1
vote
1answer
2k views

'Tempdb' is full warning message in SQL Server 2005

I am using SQL Server 2005. it's a running plant, Everyday large number of data posted into SQL Server 2005 SELECT TOP 5000 [T_idx], [T_TAG], [T_Date] FROM [house].[dbo].[total] ORDER BY T_Date ...
8
votes
3answers
9k views

ALTER DATABASE statement not allowed within multi-statement transaction

I've downloaded the AdventureWorks based In-memory sample from here, and followed all the steps described in the accompanying doc. However, when I try to run the script in SQL Server Management Studio,...
0
votes
1answer
27 views

Store last elements in a dedicated table

i'm thinking about something. Consider a table "messages" which stores messages between member. This table can potentially billions messages. Looking for all message from a message would become a long ...
0
votes
0answers
709 views

Postgres Backup on linux server [closed]

I am trying to take backup of postgres from InstallAnyWhere which is installer for linux and windows machine. I am calling pg_dump from Java process builder as command line below cmdLine: [./pg_dump,...
6
votes
2answers
9k views

mysql multi threading for a single query

I have big db of size 8GB and having 9 million contacts in this db. When we try to retrieve all contacts, it takes nearly 1 or 2 hour (in some cases result in timeout) to retreive all contacts for the ...
0
votes
0answers
155 views

Oracle 11g Rel 2 installation on windows 2008 Server

I am SQLServer guy but due to some reason I need to create a VM of windows 2008 R2 standard server and want to install Oracle 11g R2 64bit. I have installed a loop back adapter and gave IP : 10.10.10....
1
vote
0answers
4k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
0
votes
1answer
106 views

Config settings in MySQL so that it saves data on disk monthwise/yearwise

I have billions of rows in my MySQL table. So when I do a select for a specific date range , it takes minutes to return results, or sometimes even hangs up. Is there any config settings in MySQL so ...
0
votes
2answers
868 views

Scripting SQL Server into multiple SQL scripts [closed]

I have a SQL Sever table with 2GB of data. I can create the script for the file. But the script file is not opening. I want to split that script file into multiple script files.
0
votes
0answers
879 views

Database creation fails with ORA-12560

I installed Oracle 11g R2 PS4 on SunOS 5.10 sparc machine. When i try to create a new database using the Database Creation Assistant tool (dbca) i get the popup message ORA-12560: TNS:protocol ...
8
votes
4answers
18k views

Making sense of INNODB buffer pool stats

After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. ...
0
votes
2answers
182 views

Does Postgres support stored column labels that can be queried?

I'm not a DBA but seem to recall that there was a SQL database that stored column labels (preferred column headings that can be printed on reports). Does Postgres support labels such as these?
0
votes
1answer
102 views

How can I efficiently search for all records from a given month, or a given year?

We have a couple queries that involve datetime fields. In particular, we are checking for things like SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1 SELECT field1, field2, ...
2
votes
1answer
372 views

How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that it does not exhibit any race conditions, cause any PK violation, or acquire any over sized locks. ...
-1
votes
1answer
161 views

Making data from one schema available to another schema

I need to make data that is currently in one schema available to a different user in the same Oracle database. Does this require that we replicate data from one schema to another? Or is there a ...
0
votes
2answers
470 views

Return items not found in database in Oracle

I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number. However, the names given are not necessarily exactly as they appear in the ...
1
vote
2answers
8k views

How can I determine the maximum possible number of threads?

What factors determine the maximum possible number of threads mysqld will create? I only care about consideration for mysql and not limitations that may be imposed by the operating system. Further, I ...
5
votes
1answer
1k views

Fastest way to get current user's OID in Postgres?

I am thinking of storing the user's OID in the "owner" column of a table, so I don't have to do anything if they change their username. I understand one can use pg_has_role() with OIDs, so that's ...
4
votes
1answer
285 views

Relation between multiple hierarchical taxonomies linked to a single entity

I am working on an online test application which supports multiple choice questions. I have created schema for taxonomy (classification) system to be used with questions. taxonomy(id, name, slug, ...
1
vote
0answers
730 views

SQL Server: Principal DB becomes unavailable after problem with mirror DB, even with Set Partner Safety Off?

We've got a very simple mirroring setup here: just a principal DB and mirror DB, no witness, running in high-performance mode with SET PARTNER SAFETY OFF. This problem has happened to us twice now: ...
4
votes
2answers
226 views

removal of GUIDS used for all PK's, FKs & Clustered indexes

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of ...
1
vote
5answers
138 views

More efficient way to write multiple “AND”?

Anyone know a way to write this in a different manner ? SELECT tableA.NO FROM tableA WHERE tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =62) AND tableA.NO IN (SELECT GRI_NO FROM tableBB ...
0
votes
1answer
75 views

MongoDB replication to local hard drive

MonogDB's dynamic schema has made me thinking about replacing MySQL. I eventually want to use MongoDB as the default for all new app developments. But this project runs on only 1 dedicated server (...
4
votes
1answer
574 views

Two different databases on the same RAC cluster

We have an Oracle 11gR2 RAC cluster with 4 nodes. 2 nodes are dedicated to one database that's devoted one application and 2 nodes are dedicated to a completely separate database that serves a ...
1
vote
2answers
186 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
1
vote
2answers
7k views

Select all data group by 1 column and functioned by another

I have the following table in SQL Server 2012 : ID int priority int type int other data columns I want to query the table for the following result : grouped by 'type' get the id of top 1 max ...
2
votes
2answers
619 views

Reducing deadlocks with update query hint or other method

I'm getting 20-30 deadlocks per day on the same table. (2 mil rows) Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg) Total datatype size per row = 114 bytes We have multiple ...
0
votes
1answer
3k views

Get memory usage of mysql query during runtime

Is possible to obtain used memory in a running query? Something like: mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql before run query mysql> SELECT id FROM table WHERE id&...
2
votes
1answer
49k views

Float datatype with 2 digits after decimal point

In SQL server float datatype does not display trailing zeros after decimal point declare @num as float set @num=5.20 select @num will dispaly only 5.2 But i want it to display as 5.20 After a ...
2
votes
1answer
4k views

Delete data from huge InnoDB table from MySQL Master and Slaves

We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year ...
2
votes
1answer
108 views

MySQL : Till when MySQL retains binary logs?

I have multiple queries : Till when does MySQL retain the binary logs ? What is the default setting ? When does it start logging ? What does the number of the binary log file name suggest for ...
5
votes
1answer
7k views

Using a linked server with OPENQUERY in a database project

I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only ...
0
votes
0answers
30 views

MySQL: why provide size of a datatype in table definition? [duplicate]

Below is an example table definition: CREATE TABLE IF NOT EXISTS `mytable` ( `id` MEDIUMINT(5) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL DEFAULT '', `content` text ...
10
votes
5answers
33k views

“Can't find file mysql/plugin.frm” when starting up MySQL

When I start my MySQL server, I get this error: /usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create ...
2
votes
1answer
1k views

Running parallel delete statements

Problem: I have a massive table - occupies a whopping 9.5 TBs. I need to delete around 5 mil rows from it. The best method I have come up with deletes 1 million records in 2 hours! Since I can not run ...
1
vote
0answers
34 views

SQL Replication - Data not showing

Good Morning, I have recently setup SQL replication and changed some data in one of the tables on the primary table. This data however has not been replicated to the subscription server. On the ...
3
votes
3answers
904 views

ALTER TABLE SWITCH fails with indexed view

I'm having issues with ALTER TABLE SWITCH between a staging table and a target table which is schema-bound to an indexed view. When I issue the SWITCH statement, e.g ALTER TABLE dbo....
1
vote
1answer
610 views

How can I find out if any cluster member is offline in mongodb cluster?

I have a mongodb cluster with 1 master machine(running mongos and config service) and 3 slave machines(used as data store).But now,I find that one of 3 machines (192.168.10.11) is down because I can ...

15 30 50 per page