All Questions

7
votes
2answers
1k views

High Disk IO, How to mitigate?

I'm a programmer, not a DBA. Be gentle :) Overview InnoDB, MySQL mod_perl script, persistent connections script called every 20 seconds by thousands of users Problem High Disk IO (presumably ...
8
votes
1answer
46k views

Postgres client copy (\copy) command doesn't have access to a temporary table?

I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a ...
5
votes
2answers
6k views

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should ...
2
votes
3answers
3k views

Manual failover error, not in correct state

i'm doing some tests to build one database with one mirror database and one witness. so far, i'm failing in the first test, I want to kill the witness, then the Main database, and then put the Mirror ...
2
votes
5answers
827 views

Help optimizing query in the MySQL slow-query log

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
11
votes
1answer
300 views

Migrating from a physical database to a cloud database

What are the implications of moving a physical database created in Microsoft SQL server 2008R2, that makes use of advanced features like Full-Text Search on varchar(MAX) FILESTREAM BLOBS to SQL Azure? ...
-1
votes
1answer
1k views

difference between oracle 11.2.0.1 to 11.2.0.2

In my environment Oracle 11g version 11.2.0.1 was installed and it was working ok. Now I am considering upgrading to version 11.2.0.2. What is the main difference between 11.2.0.1 and 11.2.0.2.
5
votes
3answers
7k views

Postgres Server access restricted to only one database from list of databases

I am trying on the server side to restrict a user connecting on LAN to seeing and accessing only 1 database from the list of databases I have in my system. How can I achieve this? I have tried host ...
34
votes
2answers
24k views

Timeseries: SQL or NoSQL?

I don't care about the general differences between SQL and NoSQL (or their traditional differences). I am currently looking at altering the storage of our internal time series. They all contain ...
1
vote
1answer
2k views

MySQL Very Slow for REALLY Simple Operations

Something appears to be wrong with my MySQL even with very, very simple operations on almost empty (~100) tables E.g. DROP TABLE IF EXISTS hookup_gender takes 10+ seconds mysql> show processlist; ...
1
vote
1answer
937 views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...
1
vote
2answers
2k views

Cannot Attach Database to SQL Server Management Studio 2008 R2

I installed SQL Server 2008 R2 just now and wanted to attach my previous databases. Attaching a Database gives an error. Attached is the image of the error. Any Suggestions and help will be ...
7
votes
2answers
4k views

Recommended model for application level event logging

I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption. Below is an oversimplified model of the tables and their ...
1
vote
1answer
1k views

ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal

I have had invalid public synonyms in my database. Some of them references to ordinary schema objects and others to sys schema objects. I compiled all these public synonyms by the following command: ...
9
votes
2answers
9k views

Monitor MySQL activity per database?

What is the best way to monitor activity on each database of my server ? I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it. I ...
3
votes
2answers
217 views

Could someone recommend a db management tool that supports SQLite, MySQL, PostgreSQL, and is web-based?

Could someone recommend a db management tool that supports SQLite, MySQL, PostgreSQL, and is web-based? By 'web-based' I just mean it can be run in a browser locally or remotely.
6
votes
1answer
175 views

Replication problems after server upgrade

In transactional replication - first 8000 rows of snapshot copies to subscriber snapshot then acts like it completed successfully. Publication table has 21 million rows. Snapshot says completed ...
2
votes
2answers
768 views

How can I create new login in SSMS 2008?

When I attempt to create new login in MSSQL Server 2008, I get the following errors: I have tried everything I know, but I can't get it to work. What should I do and how I can solve these issues?
2
votes
2answers
351 views

State of the art self healing and self protection in DBMS

At the moment I'm writing a paper and I need some input about the state of the art of self healing and self protection in DBMSs. Does somebody read some interesting articles or books about this topics ...
10
votes
3answers
2k views

SQLITE: A problem of tags and products

I'm searching for a way to create a query to do the following: Let's consider 3 tables: products: List of products tags: list of tags tag_ties: table used to associate a tag to a product Let's ...
8
votes
4answers
2k views

What advantages does in-memory OLAP have over traditional systems with significant memory?

Do in-memory OLAP engines have advantages over the traditional OLAP engines backed by enough RAM to contain the entire cube(s)? For example, if I use a MOLAP engine (SSAS) and GB / TB of RAM where ...
42
votes
7answers
13k views

Why should I use Visual Studio 2010 over SSMS for my database development?

Visual Studio 2010 introduces database projects and a whole array of related features that supposedly facilitate database development. I've used SQL Server Management Studio (SSMS) for many years to ...
2
votes
2answers
758 views

What reason is there to create a physical table for a report output?

I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
4
votes
1answer
1k views

In a two-server dataguard setup, can I store the recovery catalog in the same database?

We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, ...
3
votes
1answer
1k views

Configuring MySQL slave to use SSL

I am trying to set up a MySQL slave to use replication via SSL. The my.cnf file contains the following: server-id = 1012 master-host = host master-port = port master-user = user master-password = ...
2
votes
2answers
1k views

Rebuilding Index - sort in temp db

What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)? Where else does sorting take place?
2
votes
1answer
588 views

schema shows through standard client connection but not through ODBC connection?

Hoping you can help! The Gist When I log into an Oracle 11g DB using SQL Developer or DBVisualizer, I can see schemas and execute queries against them. However, when I use the same credentials to ...
1
vote
2answers
679 views

How do I add a constraint after I've truncated a table?

I am writing a script. The purpose of this script is to Get all foreign key constraints on a database. Truncate a table. Re-Add the foreign key constraints that were removed. I'm having trouble ...
32
votes
4answers
27k views

Difference between MongoDB's find and findone calls

I am working on a project and I am unsure if there is a difference between the way the find cursor works and the way the findOne cursor works. Is findOne just a wrapper for find().limit(1)? I was ...
1
vote
1answer
1k views

How to prevent big performance impact during index creation

I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is ...
1
vote
1answer
9k views

Select column from ALL tables

I have a sqlite3 database with many tables which have same columns. It's a dictionary database, and each table name is starting word letter. I would like to list single column from all tables in ...
3
votes
3answers
9k views

MySQL “server has gone away” using SQLyog

I'm trying to copy a database from a server to my local machine, I've installed Wamp 2.2, mysql 5.5.16, but during copying db I m getting "mysql server has gone" 2006, I've tried to fix it by changing ...
3
votes
2answers
16k views

How to monitor backup jobs on Oracle 11g?

Background: I'm trying to monitor that backups run as expected on several databases. Zenoss is used to monitor most other stuff. A couple of KPIs are already monitored using Zenoss in combination ...
2
votes
2answers
3k views

how to reduce transaction log size automatically? [duplicate]

Possible Duplicate: Why Does the SQL Server Transaction Log Keep Growing or Run Out of Space? how can i reduce the size of Transaction log programatically and automatically, because no one knows ...
6
votes
2answers
955 views

Get NULL statistics for a column in MS SQL Server

In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column? Same information is available in Oracle and PostgreSQL, and I heard that MS ...
2
votes
0answers
4k views

Convert the Highvalue of user_partition in varchar2

I have a requirement where I would like to use the HIGH_VALUE field of user_partition table as a Varchar2 or TimeStamp value and I would like to get it used as a Function in SQL Query. Something like ...
1
vote
2answers
2k views

Oracle XE 11.2 Export / Import Charset

I created a database in oracle XE 11.2 that uses charset AL32UTF8 that I now want to export. When I perform the export, the resulting data appears in WE8MSWIN1252 character set and AL16UTF16 NCHAR ...
1
vote
1answer
315 views

SQL 2000: change collation of model database only- not master

One of our production SQL 2000 server has windows collation for master and msdb but SQl collation for model and tempdb. We are trying to have the same setup on a test environment which at the moment ...
5
votes
1answer
659 views

SQL 2008 Merge Replication and table-level data compression

What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the ...
3
votes
1answer
403 views

Connect tables in a cube based on historical data

So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me. Imagine a setup described by the following: CREATE TABLE [dbo].[Orders] ( ...
9
votes
3answers
3k views

Query best possible matches and order them

I'm trying to write a query along these lines: select * from tbl where col1 = 1 and col2 = 2 and col3 = 3 order by ... ; I want first all the results where all 3 WHERE ...
1
vote
2answers
15k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
4
votes
3answers
119 views

Is it much more expensive to use procedures that get lists and deriving a count in the application vs. a separate stored procedure for the count?

I would like to know if returning a count is much more expensive than returning a dataset in regard to processing performance? I assume there is a bandwidth increase in delivery to the application ...
1
vote
1answer
145 views

Use of '@' '.' and 'public operator/keywords in Oracle database?

I got into a weird SQL query-patch which looks for @ . and public keywords. I was wondering what is the purpose of these operators/keywords? I suspect that it might be looking for SQL injection or ...
10
votes
1answer
21k views

How can I monitor the call count per stored procedure per timespan?

In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
9
votes
5answers
4k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
3
votes
1answer
2k views

sp_whoisactive keeps returning create procedure statements

When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists. See the following example: ...
11
votes
2answers
467 views

To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run (...
1
vote
1answer
9k views

Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

I have upgraded MySQL version to 5.5.16 After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed. In those functions, cursors are there, but ...
6
votes
2answers
13k views

RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept?

I have the following situation: Backups of DB1(located on srv1 server) database are taken everyday at 1:00 AM. I took backed up files(datafile,archivelog,controlfile,spfile) of 24/10/2011 and ...

15 30 50 per page