Phill W.
  • Member for 6 years
  • Last seen this week
5 answers
6 votes
1k views
MySQL: Swap ID between two rows
17 votes

Column id is unique and primary key ... Like swap id between rows 3 and 5. You should not do this. The Primary Key of any record should be created/set/generated when the record is first created and ...

View answer
1 answers
4 votes
870 views
How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?
Accepted answer
14 votes

A very common misconception about Databases can be dispelled with this: Database != File When you update a Row in a database, the underlying data file on disk isn't touched at all - at least ...

View answer
3 answers
0 votes
145 views
Log backup vs differential backups
9 votes

We're trying to define a backup policy. You are starting from the wrong end of things. Backups are only the tools that you use to support a Recovery Strategy. Start by working out - in ...

View answer
1 answers
3 votes
702 views
How do I create a new Oracle schema?
Accepted answer
7 votes

I gather that Oracle XE only gives you a single database Correct - as long as you "speak" Oracle-ese. The Container Database is the only "real" Oracle Database instance that you ...

View answer
2 answers
0 votes
483 views
Same query, different result (depending on user's permissions)
7 votes

If two users execute the same sql statement, is it possible that user1 gets different results than user2? Yes, absolutely. Row-Level Access Control allows the Database Administrator to put "...

View answer
1 answers
1 votes
537 views
Unlock user shell script for Oracle
Accepted answer
6 votes

We have so many user ID locks from the front-end users. Good Problem Statement. More common than you might think. We need to provide a shell script to the user which they can run on OS level and ...

View answer
5 answers
12 votes
4k views
How to hide/disable tables without dropping them to check redundancy?
6 votes

Remove the permissions on the table from the Role(s)/ Group(s)/ Account(s) that [might] be using it. If anything blows up, put them back [quickly]. Hint: Using a script to do make these changes ...

View answer
2 answers
7 votes
20k views
How to get psql to prompt for a password with the superuser postgres?
5 votes

why when I log as a normal user it asks for a password and doesn't when i log as superuser? it just doesn't seem safe at all. The small but important detail that you're missing is that nobody ...

View answer
2 answers
0 votes
46 views
Index columns on pre-production?
Accepted answer
5 votes

Do you have to immediately index those columns even before you notice performance issues? Short answer - Yes. Where you have joins, you should also have Primary and Foreign Key Constraints and most ...

View answer
3 answers
0 votes
67 views
How can I make queries secret?
4 votes

The Apache logs show what the Apache Web Server has been asked to do. It generally knows (nor cares) not a jot about SQL. Of any flavour. Please, please, please tell me you're not submitting raw SQL ...

View answer
6 answers
6 votes
2k views
Why is there automatic failover for HA and manual failover for DR?
4 votes

Since 'high availability' is usually planned and 'disaster recovery' is not ... Not all High Availability "events" are planned. If the primary database server crashes at 2am, why not leave ...

View answer
3 answers
1 votes
1k views
Why is the database size larger than sum of table sizes
4 votes

why database size is a way greater than the sum of table size in a database? Databases contain lots of other things besides "just" your data. Indexes are the largest and most obvious. ...

View answer
3 answers
0 votes
118 views
Oracle - Is a separate database more secure than using the same database and a separate schema
Accepted answer
4 votes

Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue. If Management don't want the ...

View answer
3 answers
8 votes
303 views
What factors do we need to consider in order to enable auto-growth settings for my PROD database
4 votes

You've made an impressive start, here, starting to gather raw data that you can monetise and present to the business. "This much data, this much cash each year". To make plans about data usage, ...

View answer
1 answers
0 votes
531 views
Optimizing single-row DELETEs from a table with many FOREIGN KEY references
Accepted answer
4 votes

the situation would greatly improve if I added supporting indexes for all the FK (currently, only 20 of the 60 tables have it) You should always index fields used as keys, foreign or otherwise. This ...

View answer
2 answers
1 votes
287 views
Difference between Hints and Indexes
4 votes

Both are related to performance and response speed. A hint gives the Cost-Based Optimiser (CBO) a "nudge" in a particular direction; an explicit instruction to try and get to the data in the way you ...

View answer
2 answers
1 votes
279 views
What is the best smallest data type size for 19 digit number?
4 votes

I'm using this code to get a mix of Date and Time to use it like a unique ID Is this a small-scale or single-user application? Your code does not guarantee uniqueness. it is possible (albeit highly ...

View answer
2 answers
2 votes
26 views
Why is MySQL Access denied after .bashrc edit?
Accepted answer
3 votes

Never put your password into any script. The accepted way to do this for MySQL is with a configuration file. Use a separate configuration file to hold the password. Protect that file so that you are ...

View answer
2 answers
-1 votes
181 views
Many to Many - Concept
Accepted answer
3 votes

Anything that can exist on its own, without reference to anything else, needs to have its own table, so that gives you two to start with: select * from Sets ; +----+--------------------------------+-...

View answer
2 answers
0 votes
50 views
Advice for database implementation
3 votes

... save information about the children and several pictures and documents ... ... A friend is currently saving the data of over 70 kids in folders and subfolders on her device ... ... We need the ...

View answer
1 answers
0 votes
1k views
Cannot restart or repair mongodb database after the crash (***aborting after invariant() failure)
Accepted answer
3 votes

After an unexpected crash, I can't restart ... the mongodb database Time to exercise your Recovery Strategy, put your Recovery Plan into operation and get your database back from the Backups you've ...

View answer
1 answers
0 votes
40 views
Multiple values for a single column
3 votes

Lots of columns that column similar or repeated things is usually a Bad Idea. Proper Data Normalisation is your Friend. select * from resource ; +----+--------+ | id | name | +----+--------+ | 1 |...

View answer
5 answers
6 votes
1k views
What is the point of outer in a join?
3 votes

from SwitchportMac_import i LEFT OUTER JOIN SwitchportMac sm This returns rows in i which match in sm. Correct. That's exactly what a LEFT [OUTER] JOIN does - it gets you everything in the "...

View answer
2 answers
3 votes
496 views
A confusing thing said in the PostgreSQL manual:
3 votes

I think the technical term for this sort of thing is "Covering your Behind". All it's saying is that upgrades will work in the vast majority of cases but acknowledges that there's an outside chance ...

View answer
3 answers
4 votes
158 views
How do I prevent my clients viewing my database definition when it is installed on their server?
3 votes

Short Answer: you can't. If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure. Without it, they cannot operate. If you ...

View answer
1 answers
0 votes
846 views
RMAN log file with date and name in Oracle 11gR2 in Linux
Accepted answer
3 votes

If I were doing this in the bash shell on Linux, I'd use the date command: $ date '+%y%m%d-%H%M%S' 20191115-111213 $ LOG_PATH="/u01/backup/logs" $ UNIQUE="NAME" $ rman target / LOG="${LOG_PATH}/...

View answer
4 answers
1 votes
773 views
How to terminate all DB usage of a PostgreSQL user
3 votes

pg_cancel_backend takes a process id as its argument. It doesn't care if that comes from you typing it in, or a from a query. That makes it possible, albeit quite risky, to combine these two into ...

View answer
2 answers
1 votes
705 views
Database Table/Column rules
Accepted answer
3 votes

Each table and column name should be meaningful. Hell, yes! Table names should not exceed 27 characters ... Why 27? Oracle [up to 11.2] supports 30, so what are you going to do with the other three?...

View answer
1 answers
1 votes
316 views
PostgreSQL read only user
3 votes

We have around 500 schemas, so granting permission to each schema is difficult. No. It's tedious to do by hand, but it's also a prime candidate for automation, through scripting. ... dynamically ...

View answer
2 answers
1 votes
2k views
How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause
3 votes

I have dates in the format of 06-02-2019 Unwritten Rule of Databases - Dates have no format. Not any that you or I need to worry about, anyway. BTW; When is "06-02-2019"? June 2nd or the 6th of ...

View answer
1
2 3 4 5
14