Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [ddl]

Data Definition Language; defining database structures but not their contents.

35 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8 votes
0 answers
4k 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 ...
Ragesh's user avatar
  • 181
5 votes
0 answers
755 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 ...
sandinmyjoints's user avatar
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 ...
eric's user avatar
  • 143
2 votes
0 answers
894 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 ...
IGGt's user avatar
  • 2,156
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 ...
Anandhi's user avatar
  • 21
1 vote
1 answer
80 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). ...
BogdanSorlea's user avatar
1 vote
1 answer
855 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 ...
IGGt's user avatar
  • 2,156
1 vote
0 answers
635 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 ...
Raushan's user avatar
  • 113
1 vote
0 answers
47 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 ...
Jamie Dunbar's user avatar
1 vote
1 answer
327 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 ...
Fayiz Musthafa's user avatar
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 ...
ccrook's user avatar
  • 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 ...
nulll's user avatar
  • 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 ...
Kirk's user avatar
  • 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 ...
OraNewUser's user avatar
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 ...
Maxime Fouilleul's user avatar
1 vote
2 answers
5k 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 ...
Nir's user avatar
  • 533
0 votes
1 answer
58 views

How to update rows in GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP without changing timestamp

On Db2 11.5 on Linux I have (simplified example) the following table: CREATE TABLE ADMIN.TAB1 ( ID INTEGER NOT NULL, COL_TEXT CHAR(10), DATE_TIME TIMESTAMP NOT NULL GENERATED ...
folow's user avatar
  • 357
0 votes
0 answers
13 views

How to display the identifier after the execution of a DDL statement?

Postgresql (the command line tool psql) reports this after each DDL statement: DROP SCHEMA CREATE SCHEMA SET CREATE TABLE CREATE FUNCTION CREATE TABLE CREATE TABLE CREATE INDEX CREATE INDEX CREATE ...
ceving's user avatar
  • 339
0 votes
0 answers
109 views

How to get last executed statement on a particular table in Oracle?

Trying to identify what ghost process is inserting records into a particular table. Is there a way to get the exact query?
Dhaval Mohandas's user avatar
0 votes
0 answers
49 views

Rename table taking too long on MySql 8

We recently upgraded from MySql 5.7 to 8 and have run into this issue. We have a rename table operation that runs every minute. It used to take a fraction of a second, but now it takes over 6.0 ...
Faheem's user avatar
  • 101
0 votes
1 answer
72 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 ...
Nic Can's user avatar
0 votes
0 answers
79 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 ...
anttik's user avatar
  • 31
0 votes
3 answers
409 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 `...
NARESH KUMAR TELI's user avatar
0 votes
0 answers
174 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 ...
adam.g's user avatar
  • 355
0 votes
1 answer
15 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 ...
Sasha1001's user avatar
0 votes
0 answers
117 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 ...
Ali EXE's user avatar
  • 215
0 votes
1 answer
1k 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 ...
Pugzly's user avatar
  • 105
0 votes
1 answer
315 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-...
Shiwangini's user avatar
0 votes
1 answer
254 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 ...
jrdba's user avatar
  • 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 ...
ineedasickname's user avatar
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: ...
bspwjh's user avatar
  • 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 ...
Airwavezx's user avatar
  • 101
0 votes
1 answer
48 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 ...
Leon's user avatar
  • 357
0 votes
1 answer
159 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?
Gary Sharpe's user avatar
0 votes
1 answer
1k 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 ...
MattyZDBA's user avatar
  • 1,933