Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
33
questions with no upvoted or accepted answers
8
votes
0
answers
3k
views
How do I rename a table with minimal downtime in a production postgres database?
I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe.
What would be the ideal way ...
- 181
5
votes
0
answers
594
views
When/how does removing an index cause a table to lock in MySQL?
Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html.
As I understand it, INPLACE means that there is potentially some ...
- 171
3
votes
0
answers
2k
views
Serialized Inventory Tracking
I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select).
in order to keep stock records currently i came up with two options.
Option 01
use ...
- 43
2
votes
0
answers
849
views
mysql - Do DDL statements replicate under Row Based Replication if the Default Database is not set?
In Statement Based Replication the Default Database is of paramount importance.
e.g.
replicate-do-db=dbTwo
USE dbOne;
UPDATE dbTwo.tableTwo . . . .
will fail as dbOne has been set as the default ...
- 2,086
2
votes
0
answers
1k
views
Cannot drop a Postgres table
I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop.
I need to drop a few tables in my Postgres DB. I tried the ...
- 21
1
vote
2
answers
860
views
Is it possible to add a GENERATED COLUMN to a large table without locking/downtime on postgres?
I am trying to add a new column as a generated column to a large table (over millions of rows). This generated column takes in the id column and performs a custom PostgreSQL function and returns it. I ...
- 11
1
vote
1
answer
70
views
mysql optimization for intensive schema-altering queries (DDL)
In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now).
...
- 313
1
vote
1
answer
724
views
lock-ddl-per-table option not working as expected in Xtrabackup
I've recently been getting the following errors on some of my Percona XtraBackup backups:
InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
- 2,086
1
vote
0
answers
524
views
Is it possible to alter normal column to virtual column in Oracle?
ALTER TABLE TAB_NAME ADD TEST_COL VARCHAR2(255);
ALTER TABLE TAB_NAME MODIFY TEST_COL GENERATED ALWAYS as ('some_exp') VIRTUAL;
Second script gives an error:
Error report -
ORA-54026: Real column ...
- 113
1
vote
0
answers
46
views
Noob Questions - Database approach
I have a database of "records" that relate to photos - title, location, text, tags, etc. I am now adding more fields for other media types, videos for example will get an transcript, audio will get ...
- 11
1
vote
1
answer
293
views
How to trigger a function with DDL query?
I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored ...
- 111
1
vote
0
answers
54
views
MSSQL Deadlocking preventing DDL statement in different DB from running
I had a user doing a massive SQL data compare from server A to server B involving database X.
On Server B, I was trying to run a CREATE SYNONYM statement in database Y, which was being entirely ...
- 11
1
vote
0
answers
132
views
MySQL Cluster "ALTER ONLINE TABLE ... STORAGE MEMORY" doubt
I have a MySQL Cluster database where the data is both in-memory and written to disk.
I'm trying to understand the ALTER ONLINE TABLE ... STORAGE MEMORY statement, so that I can use this technique to ...
- 151
1
vote
0
answers
59
views
SQL Server Comparison
I am merging two different versions of a database. I want to add some specific fields from source database to destination, so I used Visual Studio to do that. It does the job quite well but only table ...
- 111
1
vote
0
answers
3k
views
DDL trigger AFTER ALTER ON SCHEMA behavior
Can anyone please tell me what's going on here:
CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA
cols ora_name_list_t;
BEGIN
IF (ora_dict_obj_type = 'TABLE') THEN
select column_name bulk ...
- 11
1
vote
0
answers
220
views
Replication issue - CREATE SELECT alternative?
I've an MySQL 5.1 slave for our BI team.
They need to make some CREATE SELECT with big select queries (several million lines).
As CREATE SELECT is a DDL, if the replication attempts to update some ...
- 3,505
1
vote
2
answers
3k
views
Get postgres partitioned table structure by a select
I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres.
Something like \d <table>.
All I find so far is by doing pgdump.
db=> \d ...
- 513
0
votes
1
answer
49
views
db2look fails to properly order views dumps
I exported an entire DB2-Luw DB's DDLs with db2look in order to prepare for a Postgres migration, however db2look seems to write the DDLs of the view in an incorrect order, so that some vies might ...
- 1
0
votes
0
answers
64
views
what happens on postgres ALTER TABLE when nothing changes?
When doing administration on big production postgres database, it seems that even such ALTER TABLE -ddl queries cannot be safely run, that should be no-operations?
Has anyone looked into Postgres ...
- 31
0
votes
3
answers
237
views
mysql table get lock on adding new field
We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this)
ALTER TABLE `posts_topic` ADD COLUMN `...
0
votes
0
answers
138
views
Is it an option to deploy specific schema with objects - procedures, funtions, views to multiples databases?
We have schema utils which we would like to deploy with database objects (procedures, functions, views) to multiple databases on different servers via Azure Devops Pipeline. Is there any option to ...
- 295
0
votes
1
answer
14
views
What kind of object is made when we make CREATE VIEW, and what sort of commands can be ran on it?
I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and ...
- 11
0
votes
0
answers
101
views
Is it possible to run a query for EVERY DDL change that occurs in a server?
I want to run a query on each and every database that is change in whatsoever DDL change possible.
For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer ...
- 205
0
votes
1
answer
834
views
Oracle generating hash_val from a clob
I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated.
Can this be done perhaps by making the column hash_val a ...
- 105
0
votes
1
answer
261
views
Online schema changes for cloudsql DB
I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.
What I have tried so far:
I tried with pt-online-schema-...
- 348
0
votes
1
answer
227
views
Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions
I have a view in my production database and I want to create same view in my test database.
I tried this commmand:
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
It ...
- 55
0
votes
1
answer
28
views
Appending IDs to duplicate descriptions for groups of data and single row data
I'm in need of some guidance with this problem I'm working on. First I'll need to explain the type of table I'm working with.
The table has a description field for each record. Each record has a ...
0
votes
1
answer
71
views
MySQL Schema Design: One Possible Values Between Multiple Columns
I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables:
...
- 1
0
votes
1
answer
51
views
IS-A relation when defining tables
I already asked this question on SO, prior to realizing that there may have been a more suitable place.
I am trying to implement an IS-A relationship between tables. Say I have a singer and a band ...
- 101
0
votes
1
answer
41
views
How to rapidly compare definitions of many tables of two PostgreSQL databases?
I have two PostgreSQL databases, the one for producing, and the other for testing.
Both of them have a lot of tables with same table names.
Now I need to upgrade the producing system to the new ...
- 305
0
votes
1
answer
146
views
is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra
Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?
- 53
0
votes
1
answer
903
views
Migrating Oracle Views to SQL Server
In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table ...
- 1,933
-1
votes
0
answers
24
views
Generate DELETE statement for multiple related tables
I am currently using PostgreSQL and I have a database with multiple tables that are related by foreign keys.
I need to remove something from the parent table, but I have a lot of dependent child ...
- 99