1
vote
1answer
3k views

Local package won't compile when remote package recompiles. (Oracle)

We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package. When something on A changes that requires the package on A to do an implicit ...
6
votes
1answer
1k views

Using sys.diana and sys.pidl to parse PL/SQL

It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
16
votes
6answers
15k views

Oracle Creating ER Diagrams and Data Dictionaries

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
36
votes
11answers
5k views

How can I track database dependencies?

As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods ...
3
votes
2answers
879 views

Why can't I use newid() in a user-defined table-valued function?

I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this? I have an alternate solution that suits my needs, so I'm not looking for ways to ...
5
votes
3answers
12k views

Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions: Attempting to view or run ...
20
votes
3answers
12k views

What is the best way to get a random ordering?

I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How ...
2
votes
1answer
1k views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II?

Two weeks ago I asked "How do I configure Windows XP to start the postgresql demon after each boot/reboot?", and BenV helped me out with his answer: Make sure the service is set to start ...
3
votes
3answers
578 views

Storing a large number of small size entries in MySQL

I have a database that stores readings from numerous gauges. Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 ...
3
votes
2answers
34k views

Expired archived logs not deleted from RMAN catalogue

If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE ...
9
votes
2answers
22k views

How do I determine if a table exists in a SQL Server database in SQL Server 2008?

How do I determine if a table exists in a SQL Server database in SQL Server 2008?
21
votes
1answer
23k views

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
8
votes
1answer
2k views

Unexplained InnoDB timeouts

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos ...
2
votes
2answers
2k views

What is the best way to filter or otherwise manipulate the results returned by a stored procedure?

Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is ...
8
votes
6answers
5k views

Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios

From what I can tell, there are three possible ways of backing up your SQL Server database Full backup Differential backup Log Shipping What are the pros and cons of each strategy and in what ...
26
votes
7answers
15k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
11
votes
2answers
20k views

InnoDB create table error: “Row size too large”

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
27
votes
3answers
20k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
3
votes
3answers
2k views

Estimate large database size and speed

First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following : +----------+------------------+------+-----+---------+-------+ | Field | ...
13
votes
5answers
16k views

Why do MySQL threads often show “freeing items” status when the query cache is disabled?

When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state. The MySQL manual suggests that at least part of the reason for a thread to be in ...
10
votes
3answers
6k views

MySQL data type for 128 bit integers

I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers. Right now, I'm using binary(16) but that involves a lot of conversion ...
1
vote
5answers
5k views

How to restore database using old full backup and current log file

What is happened? My client has lost the hard disk. He has to restore database. He has the full backup of database 15 days old. He has ldf (log) file of this database. He has NOT mdf file (it was ...
1
vote
1answer
1k views

Query Linked Access database on network share via SQL Job Agent

I have an MS Access database on a shared network location which I've added as a linked server to my SQL Server instance. I'm authenticated on the network location via the server's main Windows login. ...
2
votes
1answer
3k views

What is the corresponding MySQL TimeStamp in MS SQL Server?

I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?
3
votes
2answers
430 views

What's the best way to track Updated By, Updated On

In MS SQL Server, what's the best way to update an Updated By, Updated On set of fields on a table? I've seen it done it triggers, in the code. etc. The upside I've seen to triggers is that it all ...
2
votes
2answers
390 views

Will this Database design logic work and what should be used?

A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com What he essentially wants to do is ...
6
votes
3answers
4k views

Changed max_allowed_packet and still receiving 'Packet Too Large' error

I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
3
votes
2answers
1k views

MySQL Table Crashed Needs Repair, but Doesn't seem to Repair?

I am getting the error: Table 'feed_data' is marked as crashed and should be repaired So I run REPAIR TABLE feed_data (repeatedly) and get the following result: ...
4
votes
3answers
1k views

Detect Index Coalesce

Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes. Note: I am not referring to the COALESCE ...
5
votes
2answers
1k views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...
6
votes
4answers
5k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
2
votes
1answer
389 views

MySQL Error 10048 on nested Delphi Queries

I'm having a problem with MySQL (Server 5.1.11) interfacing with a Delphi application The problem occurs when trying to use the application to do a bulk export to Excel. During the application ...
14
votes
2answers
8k views

What is a Clustered Index?

I need a short explaining of clustered index. What is a clustered index? What are best practices for using the clustered index?
4
votes
6answers
989 views

Optimize this SQL query for performance

I'm trying to optimize the following statement: 'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) +'/00000' +CAST(@number+1 AS VARCHAR) The statement produces a value ...
6
votes
2answers
2k views

EXP-00085 warnings while doing full backup

During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings [...] . exporting post-schema procedural objects and actions EXP-00008: ORACLE error 6502 encountered ...
14
votes
4answers
3k views

Why won't some DBMS's allow rollback for certain DDL statements?

Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow ...
14
votes
2answers
30k views

Rebuilding the transaction log

We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried: Detaching and reattaching the database; and Undeleting the transaction ...
5
votes
2answers
7k views

Calling SQL Loader From SQL Plus

I was wondering if there was a way I can execute a SQL Loader script from SQL Plus. We are using Oracle 10g.
2
votes
1answer
2k views

What does TBSCAN GENROW mean in Explain Plan?

In a DB2 Explain Plan, what does TBSCAN GENROW (line 5 below) mean? SELECT STATEMENT ( Estimated Costs = 1.811E+03 [timerons] ) 1 RETURN 2 NLJOIN 3 [O] TBSCAN 4 SORT ...
2
votes
2answers
1k views

Connect a linked server to MS Reporting Service?

I've got a MYSQL server setup as a linked server in MS SQL server 2008. Can someone tell me if it is possible to generate reports from this server? How do I connect it to Reporting Services?
5
votes
2answers
5k views

Error Querying MySQL server linked to SQL Server 2008

I've created a linked server in MS SQL Server 2008 to a remote MySQL server. When I try to query any tables, I get an error: .tablename. contains no columns that can be selected or the current user ...
17
votes
7answers
2k views

What is a good way to store a large number of columns?

I have a problem deciding how to store this data in my database. Any suggestions on the best way to do it? I don't know a hell of a lot about databases, I might add. I have data coming in formatted ...
9
votes
2answers
381 views

Shouldn't SQL Server Support RANGE?

As someone who develops websites and does a bit of SQL Server, it seems a no brainer to me that SQL Server should support a simple clause that indicates the results should only include a specific ...
21
votes
4answers
9k views

Using SQL Profiler on a database that's in production

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems. But I've always used it on my development ...
9
votes
3answers
2k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
9
votes
3answers
19k views

Is there any difference between Rem and — comments for SQL*Plus?

On SQL*Plus prompt, both Rem and -- qualify as comment indicators: Rem this is a comment -- this is also a comment create table emp ( id number primary key, name cvarchar2(40)); Is there any ...
18
votes
2answers
25k views

What are database statistics, and how can I benefit from them?

I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
10
votes
9answers
14k views

What tools are there for data masking? (MySQL, Linux) [closed]

I'm looking for (ideally free, open-source) data masking tools. Do any such exist? Note: this related question deals with tools for generating test data, but in this question I'm more interested in ...
11
votes
1answer
1k views

BLOBs or references in PostgreSQL

I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files ...
5
votes
5answers
2k views

What is the best use of memory in MySQL?

I have a MySql server with 2Gb of memory. What's the best configuration to use then with best results? How can I "distribute" this memory fine to MySql server?

15 30 50 per page