3
votes
2answers
580 views

How do I use window functions (“over”) together with crosstab in Postgres?

Table: application_app applied_date | applied_class | id -------------------------------+---------------+----- 2013-03-06 15:59:49.22774-05 | BOSFAL13 | 13 2013-05-12 ...
2
votes
1answer
875 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
1
vote
1answer
601 views

SQL Server Data Transactional Replication of huge data, locking down table

We have setup data transactional replication from Server-A(SQL Server 2008 R2) to Server-B(SQL Server 2008 R2). We have a SQL Job scheduled to run everyday on Server-A, which executes single UPDATE ...
2
votes
2answers
57 views

best approach to keys, if data from separate DBs to be presented in same UI

Suppose we are creating databases containing information about monkeys, and a user interface to present monkey information to users. We have two separate databases. The schemas are identical (and are ...
1
vote
3answers
480 views

Problems while connecting to a newly installed PostgreSQL 9.2 on same box as 9.1

I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed. I have ...
1
vote
1answer
2k views

Feature SQL Server 2012 - Filetable - The location of the file can not be opened

When trying to access the files available on the virtual directory created for FileTable, you receive the following error message: "The location of the file can not be opened. Access is not ...
2
votes
1answer
271 views

ER relation with unique key

I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs: A student does not have to be a member of any team. A student could be a ...
0
votes
1answer
69 views

Copy all data to slave before mySQL replication connect

I try to set up mySQL replication, however I have question before I connect 2 servers Q1. Should I import all data from master into slave before I connect 2 servers? ex. if My db have millions of ...
1
vote
1answer
584 views

Export a subset of data, from mysql production database, to make a test database [duplicate]

I have a mysql production database, with multiple tables. Tables are related with foreign key constraints. I want to make a small test database, by collecting/exporting only a small subset of data ...
0
votes
1answer
690 views

Out-of-Range and Overflow Handling for float values

am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3) Currently if the user enter the value above 999999.000 MYSQL ...
0
votes
1answer
1k views

How to migrate old Oracle ASM disks

We have EMC CLARiiON disks that are near their end of support, so we need to move Oracle ASM disks to a new sets disks. We are trying to minimize the database down time, so I am trying to find ways ...
2
votes
1answer
5k views

Amazon RDS - Can't connect to MySQL database server?

So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. ...
0
votes
1answer
261 views

imp paused mid import

OK, I'm new to Oracle administration but I'm trying to import a previous dump (no pun intended) which was split into two files. The import is happening from an Oracle 9i to 11gR2 on Windows I used ...
0
votes
2answers
106 views

Foreign key for rows added later

I'm using a queue to crawl pages and track links between them. I have a table called page with a primary key called page_id. I have a table called links with two columns: link_from, link_to The ...
1
vote
0answers
269 views

Backup of msdb stuck in rollback

A native full backup of msdb on a SQL Server 2005 Enterprise instance (9.0.4053) deployed on a two-node Win2K3 cluster (Microsoft Windows NT 5.2 (3790)) is currently stuck in rollback. The DB is in ...
2
votes
2answers
288 views

Does RMAN in Oracle only copy the archive logs? What happens to the active redo log in case of failure?

If RMAN copy copies the archived redo logs when they are full, what happens to the current redo log in the event of a failure (hardware failure, etc.)? Isn't there always an active redo log that is ...
0
votes
2answers
334 views

How to fetch file path from a folder through PL/SQL

We upload files on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible. If possible, please suggest what ...
2
votes
3answers
122 views

Are these indexes redundant?

I have two indexes on a table that will contain roughly 10-20 million records at a given moment. Index 1 CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC] ON [dbo].[LogWindow] ( ...
0
votes
1answer
71 views

Extract data for all groups in a single SELECT statement

MySQL 5. Table: schedule Columns: id int(10) UN PK AI startdate date groupid smallint(5) UN The following statement extracts the Schedule ID for a given group (the schedule with the latest date ...
0
votes
1answer
416 views

Incremental MySQL restore using binlogs

I've tried to restore a MySQL database using incremental binlogs and mysqlbinlog tool but I received the following message: ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to ...
0
votes
2answers
253 views

Query Takes more time in SQL Server 2008

I have a table in SQL Server 2008 with 553,386 rows and every day it is increasing by about 400 rows. Now when I full scan of the table (Select * from) it is taking 23 sec. Is there any solution to ...
-2
votes
1answer
34 views

Is there any way to know all the tables(and/or views, triggers, functions) present in the Oracle 9i database?

I want to know this because, I forgot the name of the table I created Using Oracle 9i Thanking you for your time.
5
votes
1answer
773 views

Why does @@dbts increase after backup/restore?

I have a SQL Server 2005 database, containing some tables which have a Timestamp (or RowVersion) column. I need to know the value of the current timestamp, so I use the following query: SELECT ...
1
vote
1answer
2k views

Can I use a Percona Xtrabackup backup to restore a single table?

I've been playing around with the Xtrabackup tool for a month now, and I got to admit : it is really powerful ! I'm still testing it to backup database servers between 5 and 150 GB and the ...
6
votes
2answers
2k views

Slow SQL Server Database (approx 2TB) - high CPU due to index fragmentation?

I've posted this over at ServerFault but didn't have much luck getting a response. I have a fairly large SQL Server database (~2TB). Most of the data is in one table (~6 billion rows). Two weeks ago ...
0
votes
0answers
34 views

Accessing Database from Slave server

I have an application running in one server eg: My Server's Ip is 192.168.10.10, and I have the MySQL database server in the same machine. The application connects database server as localhost. ...
1
vote
1answer
2k views

MySQL cluster 5.6 doesn't convert invalid TIMESTAMP value to '0000-00-00 00:00:00'?

According to the document: Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'). but it doesn't ...
3
votes
1answer
495 views

How to store static list of hashes in MySQL effectively?

Problem: I have a list of pairs (md5_hash, id). I want to store the data in MySQL and I want to make queries like this SELECT id FROM table WHERE md5_hash = <some_hash> The number of pairs ...
5
votes
2answers
4k views

Stored procedures, binlog and SUPER privilege

I tried to create this function on Amazon RDS for MySQL: CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT READS SQL DATA BEGIN DECLARE _id, _parentId INT; SELECT zoneId INTO _parentId; ...
0
votes
1answer
117 views

Database mirroring is limited to an original database

Database mirroring is limited to an original database and how many mirror copies?
0
votes
2answers
99 views

Rebuilding index

I have a transaction database and one of nightly job is to rebuild indexes that followed by a log backup and then shrink the log. Is this safe to do so ? Thanks Antony
0
votes
0answers
71 views

Postgresql. Proxying queries and log them

Is there a way of logging of user queries in Postgresql than writing logs on the server side? A proxy or something like that. Pgbouncer don't logging queries, tsung-recorder log them into xml without ...
0
votes
0answers
4k views

Multi user messaging database design

I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement. It basically consists of secure messaging system with those abilities : One ...
3
votes
1answer
927 views

Doing a point in time restore with CDC enabled; Possible?

I discovered this week, the hard way, that the database restore options NORECOVERY and KEEP_CDC are mutually exclusive. So, this begs the question. How do you do a database restore, keeping CDC ...
1
vote
1answer
8k views

Mysql: update query with subquery

Hello i need to perform the following query Query update Taxonomy set sourceId = ( select id from TaxonomyMapping a where a.oldId = ( select cm.en_ID from TaxonomyMapping ta ...
1
vote
1answer
335 views

Recover oracle 10g database from old data files

I have an Oracle 10g database. Unfortunately, our machine and system crashed and all the backups and Oracle archive logs, but we have old image copies of our data files and one of the control file ...
0
votes
0answers
145 views

How do I debug an Oracle Linux Diskspace Issue?

I have about 429GB Diskspace allocated to this VM. I see that only 179GB is being used. Now when I try to add a 128GB file I get an I/O error saying I do not have any space left. I need to be able ...
6
votes
6answers
1k views

Move SQL Server 2008 database from machine A to B with no downtime

I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by ...
0
votes
0answers
283 views

The remote copy of database “<DBNAME>” is not related to the local copy of the database

I'm trying to join a secondary DB to my SQL Server 2012 Availability Group following this article: Join a Secondary Database to an Availability Group (SQL Server) When I attempt this the local SQL ...
2
votes
1answer
144 views

Is there any way to use different InnoDB settings for different databases on the same server?

From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used ...
0
votes
2answers
262 views

Query to return fields of distinct values per key

In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list ...
3
votes
1answer
1k views

Selecting the highest value in a column?

I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
2
votes
2answers
2k views

Oracle's UTL_FILE when connected locally or through listener has different behaviour

We have a procedure which creates a CSV file with UTL_FILE. (env is Oracle 11.2.0.1 and RHEL 6.4) Until now, we were storing that CSV file into system oracle (the oracle owner) user home ...
3
votes
2answers
637 views

E/R diagram for sports league database

I am attempting to create a data model for a sports league There are two entities in my domain. The first entity is a Manager and the second is a Team. A Manager has personnel id first name last ...
1
vote
0answers
478 views

bulk insert not working in stored proc when using exec [closed]

We have a SQL Server 2005 database with a stored procedure that needs to do a bulk insert. The query in the stored procedure is defined as such: declare @query nvarchar(max) set @query = N'bulk ...
8
votes
2answers
5k views

Composite Primary Key efficiency as a Foreign Key

I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the ...
3
votes
1answer
2k views

How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

I have a function that includes: SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS This is useful, for example, to remove accents in French; for example: ...
3
votes
2answers
671 views

continuously extending a table in SQL Server

We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table) This cause a lot of page splits. ...
5
votes
1answer
1k views

TPS too high in PostgreSQL

I created a table: CREATE TABLE foo ( id integer primary key, bar integer NOT NULL ); and inserted one row: INSERT INTO foo (id, bar) VALUES (1, 1); and ran a pgbench of the following ...
4
votes
3answers
552 views

Initializing Transactional Replication From two Different Database Backup

I have two different large Database (200GB for each) which I want to replicate their data to a central database (3rd DB). I followed this link and it was very useful for a single database. But my ...

15 30 50 per page