Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
48
votes
7 answers

How can I verify I'm using SSL to connect to mysql?

I have configured my server to allow SSL, and have modified my client ~/.my.cnf so I use SSL: [client] ssl ssl-cipher=DHE-RSA-AES256-SHA ssl-ca=~/certs/ca-cert.pem When I log in with my client and view the status, it lists a cipher on the SSL…
chris
  • 1,222
  • 5
  • 17
  • 29
48
votes
5 answers

Can I launch a stored procedure and immediately return without waiting for it to finish?

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day. I have a second stored procedure that should be run after the first stored procedure runs since it is based on…
Rachel
  • 8,477
  • 20
  • 50
  • 74
48
votes
2 answers

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding memory settings, I thought it might be a good…
Petr Praus
  • 583
  • 1
  • 5
  • 6
48
votes
2 answers

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old backups and Maintenance Plan files Of the 23…
Onion-Knight
  • 1,099
  • 2
  • 10
  • 15
48
votes
3 answers

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | …
jkj
  • 816
  • 1
  • 6
  • 8
48
votes
5 answers

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a…
bumble_bee_tuna
  • 957
  • 2
  • 11
  • 19
48
votes
2 answers

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to suggestions: The existing table is the master and…
Evan Appleby
  • 1,181
  • 1
  • 9
  • 15
48
votes
2 answers

How to restore a "contained" database?

I recently tried to restore to my local development SQL Server a backup from a network instance. To my surprised I received the following error message: Msg 12824, Level 16, State 1, Line 3 The sp_configure value 'contained database…
David
  • 1,339
  • 1
  • 9
  • 12
47
votes
2 answers

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john if there are records with id 30122 then I have…
SpringLearner
  • 611
  • 1
  • 5
  • 8
47
votes
3 answers

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused nvarchar(max) NULL, CONSTRAINT PK_dbo_Test_TID …
Paul White
  • 83,961
  • 28
  • 402
  • 634
47
votes
7 answers

How to determine if an Oracle table is locked or not?

We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2. Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not?…
Selahattin
  • 611
  • 1
  • 6
  • 6
47
votes
6 answers

PostgreSQL Change owner of all tables under a specific schema

I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner. Is there a way to accomplish that?
Twisted Fate
  • 571
  • 1
  • 4
  • 3
47
votes
5 answers

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking for a quick way to do this. What do you recommend?
Webber
  • 685
  • 1
  • 6
  • 8
47
votes
1 answer

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
MickeyfAgain_BeforeExitOfSO
  • 721
  • 1
  • 6
  • 13
47
votes
1 answer

How can I benchmark a PostgreSQL query?

I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark? I know that timing can be turned on with \timing at the psql prompt, but ideally I'd like to have a…
Franck Dernoncourt
  • 2,083
  • 11
  • 33
  • 52
1 2 3
…
99 100