3
votes
1answer
1k views

How to change temporary directory for pg_dump?

I'm running out of space from my harddrive which has a 18GB PostgreSQl databse. When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run out ...
2
votes
1answer
1k views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
4
votes
1answer
376 views

Large difference in disk space used by partitions

I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each. ...
0
votes
1answer
319 views

Flatten recursive hierarchy

I have the following recursive query: SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path FROM user_group_nodes START WITH parent_id = '2892107544AA2278166C1AA8D123E761' CONNECT BY parent_id = PRIOR ...
3
votes
1answer
169 views

join syntax / style performance consideration

Edit: I've added a follow up question based on comments by @ypercube about semantic equivalence and my findings of the query behavior changes when you have a mix of outer/inner nested joins. We ...
1
vote
2answers
100 views

SQL Server 2000 optimzation job hasn't run for a while

Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database. We turned it ...
0
votes
1answer
624 views

MySQL: all stored procedures deleted?

Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be ...
2
votes
4answers
148 views

Benefits of droping a temporary table

What are the benefits (if any) of dropping a temporary table in SQL Server? eg: DROP TABLE #temp; Since temporary tables are automatically dropped by the server this statement would seem to be ...
0
votes
1answer
3k views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val ...
0
votes
2answers
306 views

Database design for holding read/unread content state

In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: ...
1
vote
1answer
1k views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
0
votes
2answers
884 views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
0
votes
1answer
839 views

Calculating time spent on query cache lock

How can I calculate the total time my MySQL database is spending Waiting for query cache lock ? I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
2
votes
1answer
213 views

How efficiently does iSeries DB2 use index with WHERE <>?

MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries. I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects ...
0
votes
0answers
344 views

Stored procedure to replace a string in an entire database

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is: BEGIN declare t, c ...
1
vote
2answers
2k views

Protect SQL Server from hackers

Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me. EXEC sp_readerrorlog 0, 1, ...
1
vote
1answer
887 views

Add a column contains a photo

I installed PostgreSQL 9.1 under Ubuntu 12.10 And now I have a table called property I want to add a field that can be used to store photo(link to the photo or import a photo into a column?) But I ...
6
votes
1answer
341 views

Minimal Logging Conditions in SQL

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about ...
0
votes
1answer
86 views

How can I design a database that easily lets me recreate historical snapshots

I'm designing a database for an accounting system. In the system there are companies, and each company has many contracts. Each contract gives a company access to products, data types, or both. ...
0
votes
1answer
1k views

configure: error: SQLite3 library not found

I'm getting the below configure error while doing the install the Zabbix reporting tool on Linux. i have tried both Versions(zabbix-2.0.9,zabbix-1.8.17), but getting same issue. Log: checking for ...
1
vote
3answers
548 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, ...
1
vote
1answer
894 views

Can't restore Azure bacpac into localdb with SqlPackage

I have made a backup of my SQL Azure in a bacpac file and when I try to import the bacpac in localdb it always fail. Here is the command I use: PS C:\Program Files (x86)\Microsoft SQL ...
1
vote
1answer
840 views

How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating ...
2
votes
1answer
64 views

Table Relationship cardinality

I am designing a database for my application, I got issue in implementing the following logic. A user may be a Vendor and also a Buyer. I have created a table for USER and created an another table ...
0
votes
0answers
77 views

set up proper role/group acces on databases and schemes

On my postgresql server I'm struggeling / finding the best practice for a proper role/group setup. I have a number of clients on one machine; each client has its own role: client1; client2;client3 ...
8
votes
5answers
9k views

Freeing Unused Space SQL Server Table

I have a table in SQL Server Express with a lot of unused space. I need to free up space in the database. | NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | ...
4
votes
2answers
4k views

GROUP BY with MAX versus just MAX

I am a programmer, dealing with a big table which the following scheme: UpdateTime, PK, datetime, notnull Name, PK, char(14), notnull TheData, float There is a clustered index on Name, UpdateTime ...
3
votes
1answer
1k views

Number of ISA relationship triangles in an Entity Relationship Diagram

The following two diagrams are nearly identical. However, in one, there are two ISA triangles from the Entity "Movie" and in the other there is just one. What is the difference in meaning?
0
votes
1answer
2k views

Howto disable Postgres listening on TCP?

Simply to avoid many problems in the first place I do not want my postgres server program to accept/listen to anything from any network (i.e. TCP/IP 4/6) connections. My setup is a Postgres 9.1 on ...
5
votes
3answers
2k views

Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)?

There is a project for lab. The requirement to save the lab data (raw data and related infomation ) for research for years. So the data must be saved for years, but the data is so big, every raw ...
0
votes
2answers
83 views

How can I replace constants 5,6 & 8 in sql instead of in condition

In the following query, I would like to make the values in the IN dynamic. SELECT DISTINCT party_id INTO #tmp_new_part FROM #party_approval WHERE party_approval_type_id in (5,6,8)
1
vote
1answer
816 views

DB Mirroring in async/High Performance mode: Available in SQL Server 2008 Standard Edition?

This should be easy, but the MS docs are not clear. The docs: http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.100%29.aspx Indicate that in Standard edition, the only supported mirroring is ...
0
votes
2answers
681 views

Copy SQL Server database to same server & decrypt by symmetric key return null

I have a SQL Server 2012 database that I backed up and restored to the same server and instance. When I attempt to use DecryptByKey it always returns null. Since it is the same server, do I need to ...
0
votes
1answer
120 views

Nullable One To Many w/Join Table - Indexing

I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables users user_id (pk) user_name user_dob ... ...
0
votes
1answer
644 views

mysql crashed, after start again, the new data lost, only old records of some day ago

I got a server restart, the mysql server seems crashed. After reboot, mysql database filled of old data for bug tracker. Do you know how to fix this? mysql error log as below 131027 15:29:19 ...
0
votes
1answer
482 views

MongoDB shell version error (Server has startup warnings)

I installed MongoDB under Ubuntu server and when inter to MongoDB shell i got this error and warnings. MongoDB shell version: 2.4.7 connecting to: test Server has startup warnings: Sun Oct 27 ...
1
vote
1answer
4k views

Not able to login on MySQL in AWS RDS using root

I am trying to connect my MySQL RDS from EC2. Under security groups I have made sure permissions are in place. $ mysql -h xxx.xyz.ap-southeast-1.rds.amazonaws.com -P 3306 -p Enter password: I am ...
1
vote
2answers
384 views

What is the meaning of “sga_max_size= # internally adjusted”

I used following command to get backup of my spfile. CREATE PFILE='c:/test_init.ora' FROM MEMORY; it has following lines related to memory. memory_max_target=12G memory_target=8G ...
0
votes
1answer
7k views

How to connect an oracle DB with MySQL workbench?

I am a bit confused: I want to connect an Oracle DB with the application "MySQL Workbench". In order its from Oracle as well I throught that this is possible. But until now Im not able to do so. With ...
4
votes
1answer
2k views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...
1
vote
1answer
595 views

There is no export as SQL option

As CMS I use Wordpress. I want to export a whole category (windows 8) so I run the SQL command : SELECT * FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = ...
2
votes
2answers
545 views

Sql server - Rebuilding Indexes - Does it affect foreign keys?

i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the ...
0
votes
3answers
12k views

I am getting error ORA-03113 at the time of startup database.

I am getting error ORA-03113 at the time of startup database. I removed some old archive log files as per solution I got on google. But still I am unable to startup db. But, I am able to connect other ...
1
vote
1answer
27 views

Change propagation among connections

If I update a table using one connection and issue a query in another connection immediately after that, is there a chance that I can't see the changes even if it's extremely unlikely?
2
votes
4answers
426 views

SQL Server: Database integration and synchronization

I have n SQL Server databases with the same data schema. On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n ...
0
votes
1answer
105 views

Should I use backup compression for databases where all data is already compressed?

In SQL server with enterprise edition, there are databases with all the tables/indexes compressed. Does it still make sense to do compressed backup?
1
vote
1answer
2k views

MS SQL Server accepts non-SSL connections even with Force Encryption enabled on the server side

I am forcing encryption on my MS SQL server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track? Here are my detailed steps : makecert -r -pe ...
3
votes
1answer
583 views

Mysql slow select from a view

I would appreciate an approach with this problem, I have a lottery system in mysql, this system receives a lot of transaction per second.. a lot!. I need to check the total amount for each the number ...
2
votes
3answers
2k views

A way to reference the ID in a multi insert transaction? (postgres)

Assuming table "entity.eid" is auto incrementing, I want to be able to reference the autoincrement value assigned later in the same transaction. The way I have been doing this is by doing multiple ...
-4
votes
1answer
310 views

T- SQL Query which can give all the Performance Monitoring information of a single database in SQL Server 2008 R2 [closed]

Urgently need a T-SQL query which can give all the performance monitoring information like database size, database physical path etc, log size, log path, spaced used, space free, number of ...

15 30 50 per page