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

learn more… | top users | synonyms

2
votes
0answers
11 views

Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?

I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ...
0
votes
2answers
15 views

Is there a way in PostgreSQL to set an default owner for all future create table statements in a session

We have a program that transfers cobol data (index files) to a database. Until now we transferred the data to an oracle database. In the beginning of the program I logged in with user sys and ...
-1
votes
0answers
7 views

Getting error 1005(HY000) cant create table in MYsql [on hold]

I tried to create the below three tables: CREATE TABLE category ( cat_id INT NOT NULL AUTO_INCREMENT, cat_name varchar(100), PRIMARY KEY(cat_id), UNIQUE(cat_name) ); create table product ( ...
0
votes
1answer
300 views

Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly. But today, when I executed the same script, it threw ...
19
votes
2answers
12k views

Best way to populate a new column in a large table?

We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
1
vote
1answer
35 views

SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is: SQL Error: ORA-12991: column is reference ...
1
vote
1answer
304 views

All users are having NO Privileges in phpMyAdmin

By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one ...
2
votes
3answers
942 views

exporting DDL of all views Oracle

How can I go about exporting DDL of all my schema views in single .sql file ? I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case. I know how to ...
0
votes
1answer
33 views

mysql diff and add diff records into new database

I have two database on two different hosts: host A has database A and table A and host B has database B and table B and now "host A/database A/table A" table A contains records with primary key ...
1
vote
1answer
53 views

Create a table based on a table depending on the values in the initial table column

I have a table like: +----+--------+----------------+ | id | name | address | +----+--------+----------------+ | 1 | Ivan | Moscow,Moscow | | 2 | Petr | Samara,Samara | | 3 | Grigor ...
2
votes
2answers
80 views

How do I get the DDL used to create function based index?

I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it. DROP TABLE Fbi_tab; CREATE TABLE Fbi_tab ( a INTEGER, b INTEGER, c ...
1
vote
2answers
33 views

How to determine if a column already exists in a MySQL table before altering the table to add it?

I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql ...
2
votes
2answers
71 views

Adding a new primary key ID column to an existing table

I want to add a new column to a table. The table is already populated with data. I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it. I ...
6
votes
1answer
682 views

ERWIN create and use database

I'm trying to generate physical database from my logical model using ERWIN 7. All is well in the script that is generated automatically, there is only the problem that the model is created in the ...
8
votes
1answer
90 views

Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?

Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler. create proc spFoo @param bit as begin if @param = 0 begin select * ...
3
votes
1answer
51 views

“TRUNCATE doesn't run ON DELETE trigger” true or false

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL? I this answer I found a sentence as given below "The fact ...
2
votes
4answers
201 views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
-2
votes
1answer
47 views

Extract ID String - PostgreSQL 9.2 [closed]

I've got this directory path: /userfiles/account/5554/attachment/26687981/file/2790000/main.with_name/photo.jpg /userfiles/account/(accountid)/(filecontext)/(ownerid)/(variation)/(filename) -- ...
5
votes
4answers
12k views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two ...
2
votes
1answer
57 views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
7
votes
4answers
934 views

DDL contention on TempDB

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
1
vote
0answers
27 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 ...
12
votes
1answer
5k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
4
votes
1answer
185 views

What's the difference between data definition language and data description language?

Are they the same thing? Which one does DDL stand for? Here's what my understanding is, but could potentially be incorrect: Data definition language: Contains names and types and field length etc. of ...
2
votes
1answer
36 views

help in correcting the syntax error of the below create table with as with multiple select

Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table. create table newsample AS ( ...
2
votes
1answer
60 views

Create a function from another function

Is it possible to create a new function from another plpgsql function? Something like this: CREATE OR REPLACE FUNCTION func_test() RETURNS VOID AS $BODY$ BEGIN CREATE OR REPLACE FUNCTION ...
0
votes
1answer
37 views

Trigger to drop table only if there are no records

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
0
votes
0answers
43 views

Need to find DDLs that were executed in a week in Oracle 11g

Is there any way to find the list of DDLs that were executed in a period of time in a particular schema? It should contain all types of DDLs that were executed on any of the tables of the schema. ...
2
votes
1answer
182 views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
3
votes
1answer
135 views

Trigger actions on database creation or attach

On our dev environment we need to run a bunch of actions when some databases are created or attached. Actions like cleaning up some tables, reseeding some others, changing emails, etc. I was trying to ...
4
votes
0answers
78 views

PostgreSQL: Add or drop column under load give wrong number of columns errors

I have a heavily used PostgreSQL database. Sometimes I need to add/remove columns, preferably without any service interruptions. I follow the safe operations list from ...
3
votes
2answers
180 views

How to capture sp_refreshsqlmodule event

My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change ...
0
votes
1answer
452 views

How to quickly increase column size in Oracle?

In our Oracle 11 database we have a table with more that 100 million rows and we would like to increase size of one column (not used in any index). I tried to execute command alter table shipment ...
3
votes
1answer
107 views

Reclaim disk space from dropped column without downtime

I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used. The docs suggest doing a table rewriting ...
1
vote
1answer
85 views

Get the DDL for all indexes - Oracle

I have about 5 schemas in my oracle 12c database for which I need the DDL for all the indexes for each of the schemas. DDL I have checked it on how to get the code from it, but for all the indexes and ...
3
votes
2answers
223 views

Use composite type to create new table

In ORDBMS databases I have seen that complex types can be used: create type name as( ...) And I can references those types when creating a new table, like: create table example (row_name ...
11
votes
3answers
313 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
15
votes
2answers
734 views

How to Drop Table with Non-Printable Character on its name

I have a legacy table with a name containing non-printable characters (CHAR(31), to be specific). The non-printable character is beside a underscore, and I've discovered that the shortcut ...
-4
votes
1answer
67 views

how to create a synonym in Oracle? [closed]

some one tell me the basic syntax for creating synonym for the table employees create synonym for emp for app.employees while creating it shows error ORA-00995: missing or invalid synonym ...
1
vote
1answer
131 views

Determining the specific command type in a DDL trigger

Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is? I have a DDL trigger on a database that will be fired ...
4
votes
3answers
395 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
18
votes
2answers
566 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
0
votes
1answer
52 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
1
vote
1answer
33 views

PgTap, unit test a foreign key is deferrable

I am using PgTap to unit test a Postgres (v9.4) database structure in a continuous migration process. It works great. Here is my question: « Is there a way to control that a foreign key constraint is ...
6
votes
1answer
16k views

Add DEFAULT to existing column

In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?
1
vote
1answer
568 views

How to track only DDL statements executed in MySQL

Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements. I had gone through the ...
5
votes
2answers
2k views

Postgres DDL and DML best practices and coding standards [closed]

Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend. I am looking for something similar to what Google has for programming. ...
10
votes
2answers
5k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
8
votes
2answers
5k views

Why is truncate DDL?

I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ? ...
2
votes
1answer
352 views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...