6
votes
1answer
286 views

What is “SAP on Oracle”

OK, total newb question. What is "SAP on Oracle", exactly. This link is talking about how SAP can run on Oracle (I think). I'm not understanding how this is working. My understanding: SAP is a ...
10
votes
3answers
16k views

Notification on long-running query or deadlock in SQL Server 2008 R2?

I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on ...
7
votes
4answers
3k views

Determine when a PostgreSQL database was last changed

I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed? Instead of using pg_dumpall, I'd ...
2
votes
1answer
852 views

MySQL Master/Slave Master/Master setup?

I currently have 1 mysql server running and want to install 2 seperate mysql VPS'es. Maybe one master for inserts/updates and one slave for selects. What is the best setup for this if I have the ...
9
votes
1answer
5k views

Is there such thing as Custom Data Types?

Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to ...
2
votes
2answers
3k views

Indexing for group by + SUM query on mysql

I have a table with the following schema: CREATE TABLE `wordtrend` ( `oid` bigint(20) NOT NULL AUTO_INCREMENT, `monitorId` bigint(20) DEFAULT NULL, `nGram` varchar(255) COLLATE ...
6
votes
4answers
227 views

Dynamic contacts database

I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories ...
2
votes
1answer
4k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
3
votes
3answers
4k views

Want to order on two columns but not the usual way

I have a table as below ID userID Date 1 2273 22/08/2011 2 2274 24/08/2011 3 2275 26/08/2011 4 2273 26/08/2011 5 2273 26/08/2011 6 2271 26/08/2011 And want result as ...
2
votes
3answers
377 views

VLDB - SQL SERVER versus VFoxPro

I must create an DBF for an application which will collect data from 5 millions sensors on every minute, so I will have 5 millions records every minute. I was thinking to use SQL server because I ...
2
votes
2answers
505 views

Full Name search query

We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please. We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and ...
1
vote
2answers
1k views

Replication error on SQL Server 2008

I'm doing merge replication between two SQL Server 2008 machine. But, when I attempt to do any modification on subscriber system, I get the following error. The merge process could not enumerate ...
7
votes
4answers
366 views

What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.] Question 1 What ...
1
vote
1answer
828 views

Running query against SQL Server with FreeTDS's bsqldb returns blank output

Here is the query I ran select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max)) from Pages This worked with tsql but when run ...
4
votes
1answer
1k views

Foreign Key constraint on fixed value field

Note: I am a developer... I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign ...
5
votes
3answers
11k views

Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn. First, I do this: select max(ora_rowscn) from ...
0
votes
2answers
471 views

FBI on function with concatenated string

I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") When trying ...
2
votes
1answer
448 views

Oracle Database Block Size

One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 ...
2
votes
1answer
268 views

Shortest Query to Combine Count and Unique (Oracle)

I have the following query which works for me: select count(*) from ( select UNIQUE col1, col2, col3 from my_table where cond1 = 'A' and cond2 = 'B' and cond3 = 'C' ) Is it possible ...
3
votes
5answers
2k views

MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned

I have a slow query log item with the following: Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739 No one in my group can explain it besides a guess about packet loss. Is there a ...
2
votes
1answer
712 views

Trusted Constraints and NOT FOR REPLICATION

Version and Build: SQL Server 2005 SP4 (9.0.5000) As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a ...
3
votes
1answer
79 views

Can you control what tags are used in an XML column?

We allow our developers to use XML columns to store data. We want to provide standard names to XML tags. As a DBA, can we prevent the users from adding XML tags that are not predefined?
4
votes
1answer
636 views

T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem. Here's the scenario: Users can set up service instructions to be attached to a work order ...
3
votes
2answers
717 views

Oracle 10g Storage Migration

We need to migrate the storage of our production database. What is the appropriate method of doing so and what specific/generic precautions do we need to take?? Database Configuration: Volume Manager ...
3
votes
2answers
2k views

Oracle 10g SGA sizing Help

Help in sizing oracle 10g database sga We've an Oracle 10g r2 database, size = 4.5TBASM is used to manage the storage. It uses "External Redundancy" option on "Raw Device" (i.e. no os file system ...
8
votes
2answers
6k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
2
votes
1answer
500 views

Can you trigger an action when a view is created?

Is it possible to create a trigger that fires when a view is created on a user schema? I want to create a meta data table which will hold all the views and a listing containing what they are supposed ...
7
votes
1answer
8k views

mysqldump with INSERT … ON DUPLICATE

I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate ...
1
vote
1answer
629 views

Issue with SQL Server 2008 replication

I'm doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error. The Merge Agent failed because the schema of the article ...
2
votes
1answer
452 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
5
votes
3answers
257 views

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
3
votes
1answer
9k views

How do you tell which host is close to being blocked in MySQL?

I've a two part question: 1.In cases were this error comes up: PDOException: SQLSTATE[HY000] [1129] Host `hostname` is blocked because of many connection errors; unblock with `mysqladmin ...
11
votes
1answer
3k views

Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
6
votes
2answers
4k views

SQL Server: how to avoid sysadmin rights but give adequate rights

I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g. Add and modify server logins Add and ...
4
votes
1answer
2k views

MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists

Here's a minimal case for my database scheme: CREATE TABLE `test` ( `id` int(11) NOT NULL, `name` varchar(22) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT ...
33
votes
4answers
26k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
3
votes
1answer
1k views

View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL ...
3
votes
4answers
673 views

Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?" We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
3
votes
3answers
147 views

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted? For instance, I'm splitting payments and charges to their own tables from a previously unified ...
5
votes
1answer
472 views

MySQL Replace — Query or in Shell?

I have numerous tables with some funky character replacement going on. I found some information on running an update to replace these, but it's in numerous tables and numerous fields. update ...
2
votes
1answer
10k views

How to display a number with fixed number of digits before the decimal?

I am looking for result 0123.56 and the following query should have worked. SQL> select to_number(123.56,'0999.99') from dual; select to_number(123.56,'0999.99') from dual * ERROR ...
9
votes
1answer
2k views

Amount of data per node in Neo4j

I need to store substantial amounts of data per node in Neo4j. The data is Unicode chunks of text. Actually not every node will have big chunks, but many of them will. I waded through the ...
2
votes
3answers
22k views

SQL Server How to Set up Maintenance plan to shrink log

So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL". When it is to FULL and I perfrom ...
3
votes
1answer
1k views

oracle failsafe - how to start instance in nomount

Using Oracle Failsafe, how can a instance be started up in nomount mode? If the instance is shutdown via sqlplus, the windows cluster manager just starts it up again. If the instance is shutdown via ...
3
votes
1answer
254 views

Is SQL Server Replication transactionally 'safe' across all published tables?

We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
2
votes
3answers
7k views

Clear all tables with one DELETE

Is it possible to clear all tables with one request? I have tried the following with no result. DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as ...
6
votes
1answer
570 views

Application Settings in Database

In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all ...
7
votes
4answers
196 views

SQL Server: what is the reason to favor customized database roles over fixed ones?

This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over ...
4
votes
1answer
2k views

SQL Server 2008 R2 reindex job reorganise getting stuck

I am currently experiencing this job getting stuck in my nightly maintenance solution - ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled] REORGANIZE WITH ...
54
votes
5answers
21k views

When to use TINYINT over INT?

In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data. For example, it's ...

15 30 50 per page