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

learn more… | top users | synonyms

1
vote
1answer
20 views

Change column from NOT LOGGED to LOGGED

I have a database DAT1 running on DB2 version 10.5. I want to enable logging for all CLOB and BLOB columns of my table TAB1. The table was initially created with those columns as NOT LOGGED but now I ...
1
vote
2answers
64 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
6
votes
1answer
51 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
25 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
vote
1answer
41 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 ...
2
votes
1answer
57 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
90 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
38 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
97 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 ...
0
votes
1answer
35 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 ...
8
votes
1answer
92 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 * ...
4
votes
1answer
58 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
1answer
48 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) -- ...
1
vote
0answers
28 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 ...
2
votes
1answer
71 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 ...
4
votes
1answer
192 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
37 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
65 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
40 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
45 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
220 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
138 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 ...
2
votes
4answers
222 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 ...
3
votes
2answers
204 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 ...
4
votes
0answers
80 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
1answer
115 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
101 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
243 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 ...
15
votes
2answers
736 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
69 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
139 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 ...
11
votes
3answers
346 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 | ...
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
34 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 ...
2
votes
1answer
379 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 ...
1
vote
1answer
94 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
4
votes
3answers
149 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
3
votes
1answer
535 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
0
votes
1answer
85 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 ...
4
votes
3answers
443 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, ...
3
votes
1answer
79 views

Circular dependency in schema or boolean with CHECK?

Let's say I have the following tables: create table repositories ( id serial primary key, name varchar(255) not null ); create table remotes ( id serial primary key, url varchar(255) ...
1
vote
1answer
54 views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
-2
votes
1answer
131 views

Generate create table DDL from any arbitrary select query? [closed]

Is there a way, given any arbitrary select query like: select myfavoritenum, myfavoritetxt from tablewithmanycolumns and magically receive a create table statement: create table justmyfavorites ( ...
0
votes
1answer
44 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 ...
0
votes
0answers
49 views

Slave executing queries with different charset

I need to change a database charset because of this issue and I solved my issue in staging environment with the SQL command ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE ...
0
votes
1answer
68 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
2answers
202 views

Transaction has set autocommit = 0, but still the update happens before I commit

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of ...
1
vote
1answer
336 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 ...
0
votes
1answer
504 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 ...
1
vote
2answers
442 views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...