1
vote
1answer
597 views

Oracle change initialization parameter with “deferred” keyword

The description of deferred and scope keyword is listed below (extracted from Oracle documentation). The meaning of them gives me an indication that deferred can just use the memory scope. Is my ...
5
votes
2answers
3k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

Modify OWNER on all tables simultaneously in Postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ...
2
votes
1answer
398 views

Does mysqlhotcopy block SELECT statements from different clients?

I would like to create a backup of different MyISAM tables on a server using mysqlhotcopy. These tables are about 6GB in size. I would like to know, does mysqlhotcopy block users from SELECTing ...
5
votes
1answer
545 views

If an 'after' DDL trigger causes an error, is the DDL rolled back?

It has been suggested that DDL is logically performed something like this: begin COMMIT; perform any appropriate pre-DDL trigger code; do the ddl; perform any appropriate post-DDL ...
3
votes
2answers
1k views

Temporarily disable checking of constraints

I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index ...
2
votes
1answer
100 views

How to insert fields to different tables at once?

5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT. TEMP: TempID, Email, Name, Product1, Product2, Date, Amount PAYMENT: PaymentID, Status, Amount, Date CUSTOMER: CustomerID, Email, ...
3
votes
3answers
9k views

INSERT INTO without duplicates

I want to do this INSERT INTO AdminAccounts (Name) SELECT Name FROM Matrix But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.
3
votes
2answers
557 views

Performance gain by moving a large table from a database and referencing with a synonym

We have SQL Server 2008 R2 database which is currently about 80Gb. About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own ...
1
vote
2answers
1k views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
1
vote
1answer
1k views

How to set up triggers in MySQL for copying a field

I have an in-house application that I've put together, and I'm VERY new to SQL in general. I have a table that contains most of our data. Let's pretend it looks like this. Matrix |Name | ...
1
vote
1answer
220 views

Create a trigger that compares a field, with a field in other table

I have 3 databases, (One principal, one Mirror and a witness) and I'm trying to make this system the most fault tolerant as possible. So I was thinking in doing some extra codding to protect me ...
3
votes
2answers
5k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
3
votes
1answer
2k views

Installing 10g client next to 11g database on same computer

I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit I want to import data from warehouse which has Oracle 10.2.0.4.0 installed. When I try to import impdp usr/pass@WAREHOUSE TABLES=TABLE ...
0
votes
1answer
1k views

Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should ...
11
votes
3answers
9k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK ...
3
votes
1answer
2k views

How to make MySQL replication reliable?

Master version: 5.5.13-1 Slave version: 5.5.14-1 Binary log format: MIXED My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. ...
1
vote
1answer
411 views

View metadata refresh in MySQL

Like other RDBMS, view meta data is stored at view creation time. From CREATE VIEW in the MySQL docs The view definition is “frozen” at creation time, so changes to the underlying tables ...
1
vote
3answers
297 views

One to one relationship or another approach?

I need some addition thoughts on how to approach a database design I'm working on. The issue is I have three related items... * Clan (has many games) * Game (belongs to a clan, has many teams) * ...
2
votes
2answers
5k views

“Could NOT find Curses” error when installing MySQL from source

I am installing MySQL Server from the source distribution which I downloaded from: http://dev.mysql.com/downloads/mysql/#downloads. I have downloaded the file (mysql-5.5.19.tar.gz). I am using ...
4
votes
1answer
920 views

Not all members in a Windows group have permissions

I ran into a bit of an odd situation. Let's say I have a domain group [DMN\Group] which has been created as a login on my server and a user in the database. One of the members in that group is not ...
1
vote
2answers
4k views

How to upgrade a SQL 2000 Database to SQL 2005 - need confirmation?

I need to confirm the process for upgrading a standard SQL 2000 database to SQL 2005. As I understand it, there are at least 2 methods for upgrading. Back\Restore and Detach\Attach DB. In my case, I ...
2
votes
1answer
168 views

Migrate Data and/or Schema from DBv1 to DBv2

I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null ...
2
votes
3answers
552 views

File name as primary key?

I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key. For instance... "someimage.png" as the ...
1
vote
1answer
1k views

Partial restore to a different database

I have a database with 3 filegroups: PRIMARY, AP, and GL. I did a full database backup. I did a partial restore of just the PRIMARY and AP files successfully using this command: -- Partial Backup ...
0
votes
1answer
116 views

What privileges does the SQL Azure Migration Wizard need to copy a SQL Azure database?

Currently have 2 users in our SQL Azure subscription. One is the god login created when setting up the server, other is very limited account for our www app (has db_datareader and db_datawriter). The ...
1
vote
1answer
1k views

Partial Backup / Restore Sql Server Simple Recovery

I have a scenario where we are using SQL Server 2008 R2 with FileStream. I have partitioned the table where our files live into multiple filegroups. What I want to be able to do is backup our ...
3
votes
1answer
1k views

Restoring SQL Server Database Command Line Error

I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The ...
4
votes
2answers
5k views

PostgreSQL: Query for location of global tablespace?

I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides. As all our clients are on the same host as the database ...
1
vote
2answers
5k views

Deleting duplicates with group by and count

What is the fastest method to convert the following query: SELECT COUNT(*) as c FROM tbl_fields WHERE fieldnotes IS NULL GROUP BY fieldno,fieldserial,id,fielddate,fieldsid HAVING COUNT(*) > 1; ...
3
votes
3answers
1k views

SQL Database size doesn't match with the total table sizes in the database

I have a database that is size about 104GB. When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces. Why the DB size is too large?
1
vote
2answers
71 views

How can I return the number of values in each row which match a criterion?

I have a table "trial_volts" like this in an oracle database (I think it's 10g): trial_index NUMBER voltage_1 NUMBER voltage_2 NUMBER voltage_3 NUMBER voltage_4 NUMBER voltage_5 NUMBER And I want to ...
1
vote
1answer
546 views

Partial backup and restore using filegroups

I have a single database that I put all tables into with multiple schemas for the different products I have. I know I can't backup and restore just one schema, but if I move all tables for a schema ...
0
votes
1answer
239 views

Remote RESTORE of database through SSMS

How can I RESTORE a database through SQL Server Management Studio by connecting "remotely" to it. If I know the "IP address" of the target machine what else do I need to know? If the port number is ...
0
votes
1answer
88 views

Remote update/restore of database through SSMS

How can I update or restore a database through SQL Server Management Studio by connecting "remotely" to it? If I know the "IP address" of machine, what else do I need to do? If a port number is ...
3
votes
2answers
894 views

SQL Server Deadlock Log Entries

We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222. In the resulting logs we're seeing the following line repeatedly: "Log Viewer could not ...
3
votes
1answer
2k views

What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0. When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
7
votes
2answers
2k views

MySQL deadlock - cannot restart normally?

MySQL version 5.5.13-1 A snippet from the SHOW ENGINE INNODB STATUS\G: LATEST DETECTED DEADLOCK ------------------------ 111218 10:22:34 *** (1) TRANSACTION: TRANSACTION 1318D95B, ACTIVE 0 sec ...
2
votes
2answers
326 views

How do I set a geography point to be the North Pole?

I tried to execute this query on my SQL Server 2008 R2 instance: UPDATE ITEMS SET GEOG=geography::STPointFromText('POINT(0 90)', 4326); SQL Server returned the following error: ...
10
votes
3answers
14k views

Automating failover in PostgreSQL 9.1

How does one setup two identical servers for automatic failover in PostgreSQL 9.1. OS Centos 5 PostgreSQL 9.1 compiled from source The postgres user account exists on both machines and has a ...
4
votes
1answer
14k views

Create dump file using Oracle SQL Developer

I think I'm missing a simple point, but I couldn't find a way to use Oracle export (exp/expdp) and Oracle import (ipm/impdp) with Oracle SQL Developer (version 1.5.5)? Is there a way for it? If ...
1
vote
1answer
1k views

Isn't a tab (char(9) ) whitespace in the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3)?

If there is any sane reason to ignore trailing blanks in varchar comparisons, why doesn't that apply to tab-characters too ? On SQL Server I get SELECT case when '' = ' ' then 'true' else ...
2
votes
1answer
236 views

'packet too large' error with packet smaller than max_allowed_packet

Some time ago we started storing serialized data in our MySQL database and immediately ran into the well known 'packet too large' error. We increased max_allowed_packet to 64M and all was well. This ...
18
votes
2answers
4k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
1
vote
1answer
1k views

64 bit Postgres 9.1 Limitions on Windows 2008 R2?

When Deploying Postgres on Windows Server 2008 R2: Are there any limitations on windows that don't exist on Linux? Does the postgres scale in the same way on windows as it does on Linux? Is ...
2
votes
1answer
2k views

MySQL Config for Large MyISAM tables

I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get ...
2
votes
2answers
178 views

Count() versus keeping a column with a total count [duplicate]

Possible Duplicate: Storing vs calculating aggregate values Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos ...
8
votes
5answers
15k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
2
votes
3answers
272 views

Does this database table violate normalization rules?

I’m working with Access 2010. I’m developing a database for a public art program – we paint large scale murals on walls. The database tracks blank walls in the city as potential sites for murals. It ...
3
votes
2answers
2k views

How to restore SQL Server database just by dbName and physical path

I have several database backups and I want to restore all of them to a server. Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and ...
2
votes
1answer
254 views

What's the purpose of /*!*/; in mysql binlogs?

The title pretty much sums up the extent of my question. They seem to appear after every statement. Sometimes on a new line following. At other times, at the end before the ;. What's the point?

15 30 50 per page