Questions tagged [drop-table]
This tag is for the SQL command DROP TABLE
42
questions
0
votes
1answer
445 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
504 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?
1
vote
1answer
537 views
drop table causes ORA-08103 with another one
In my process I have 2 tables.
target table, load table.
The target table holds the current data, and a lot of queries are fired against it.
When new data coming we use a FULL LOAD and the goal ...
5
votes
1answer
9k views
Drop table taking too long
I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time.
It's been running for 2 days now. I suspect the rollback function to be the ...
1
vote
2answers
249 views
Replacing MYSQL table with data from another table - no downtime
I have two tables with identical structures:
Table1 and Table2
id INT PRIMARY KEY,
a TINYINT,
b TINYINT,
c TINYINT
Table1 is actively being used by queries from other sessions; Table2 is not ...
0
votes
1answer
39 views
Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?
If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?
1
vote
1answer
2k views
Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario
I know it is often asking question. But I can't find answer for my scenario with specific condition.
We use MySQL 5.5
We have always(I am sure)
innodb_file_per_table = ON
option in our mysql ...
48
votes
4answers
101k views
How to check foreign keys related to a table
How to see foreign keys related to a table in MySql?
Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this:
Error Code: 1217. Cannot delete or ...
2
votes
1answer
184 views
Proper Way to Remove Mysql Tables
I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?.
Removing the .frm files ...
0
votes
0answers
13 views
DDL grant for truncate/drop privilege on table to a user
I am trying to provide drop/truncate privilege on certain table to a certain user.
I know that i cant grant him like 'DROP ANY TABLE' to user, is there any DDL grant for this type of issue?
1
vote
1answer
79 views
mysql workbench - Couldn't find option to remove DROP TABLE IF EXIST
Recently I've been looking into export options to find an option to remove:
DROP TABLE IF EXISTS and replace it with CREATE TABLE IF NOT EXISTS.
I've seen on other MySQL clients like HeidiSQL or ...
0
votes
1answer
53 views
Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration?
We are trying to create global temp table using
Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) )
ON COMMIT Delete Rows;
and trying to drop it at the end manually but ...
0
votes
1answer
35 views
How to avoid DROP TABLE for a specific MySQL table
I have used a Wordpress plugin that for some unknown reason is always deleting a database table (cerber_files).
The plugin's author is not giving support for this issue.
I think in order to solve ...
0
votes
1answer
89 views
Dropping a table/column automatically drops views using it - how to warn instead?
If I have a view that uses a column, and I remove that column/table, the view is dropped automatically.
Is there a setting I can change such that it instead prevents the removal of the column/table, ...
1
vote
0answers
253 views
Azure SQL DB: Can't create table because it already exists, can't drop or write to table because it doesn't exist
I have an Azure SQL DB database that I am copying data into from a source database using SSIS. My SSIS package for one table fails because it can't write to or create a specific table name (it is ...
0
votes
2answers
2k views
Speed difference between Drop table and Truncate table in Postgres
I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm ...
a_horse_with_no_name
54k10 gold badges106 silver badges136 bronze badges
2
votes
0answers
61 views
Is it possible to drop inheritance from the parent table (top down)?
Our system uses inheritance on two levels:
master
child_level_1
child_level_2
child_level_2
child_level_2
child_level_1
child_level_2
child_level_2
child_level_1
child_level_2
The idea was to ...
4
votes
3answers
3k views
How can I drop a Memory Optimized Table?
Running SQL Server Standard 2016 SP2-CU1. I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them?
Running:
DROP TABLE [REF].[...
7
votes
4answers
21k views
Shrink database after drop table?
I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I ...
3
votes
1answer
7k views
Is it possible to force drop of mysql tables with FK?
For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table.
Is there a short way to drop a table, ...
1
vote
1answer
1k views
'DROP TABLE mysql.failover_console;' replicates to slaves, but 'Use mysql; DROP TABLE failover_console;' does not
I am currently looking to setup automatic failover for my MySQL 5.6.21 Master on CentOS 6.6. I have mysqlfailover set up in my lab, and it is working well enough. My question is not about that, but ...
8
votes
4answers
27k views
Difference between DROP and TRUNCATE
What does the TRUNCATE do differently to drop?
I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ?...
4
votes
2answers
57k views
How to undo DROP TABLE statement?
I’ve accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.
I know that SQL Server ...
1
vote
1answer
1k views
How to drop table with all reference to this table
I want to drop a table, and this table has be many table's foreign key.
So I want to drop all those reference (just refernece, not other table) within the drop table process.
Is there any simple SQL,...
5
votes
2answers
706 views
Postgresql - how to securely delete the storage freed when a table is dropped?
For a system with sensitive information stored in a PostgreSQL 9.5 database, in which data stored in a table that is deleted must be securely deleted (like shred does to files), and where the system ...
2
votes
3answers
2k views
Can a TRUNCATE or DROP table ever be very time consuming?
A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day.
It was a top candidate ...
1
vote
2answers
2k views
How do I CASCADE DROP a table being referenced in MariaDB?
If I have a table that references another one
CREATE TABLE foo ( x int PRIMARY KEY );
CREATE TABLE bar (
x int,
CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x)
);
DROP TABLE foo;
ERROR 1451 (...
1
vote
1answer
151 views
PostgreSQL drop view in case of unknown whether materialized or not
I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW.
DROP VIEW IF EXISTS myview;
throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I ...
1
vote
1answer
598 views
Speedup DROP huge MySQL replicated table
I've a huge InnoDB table customer_users_old (150G) that it's not in use anymore in the database, it was renamed from customer_users.
My setup has a Master-Slave replication of MySQL 5.5 with ...
5
votes
1answer
642 views
Check table before delete a constraint
In SQL Server I used the following code to drop a table with a constraint:
IF OBJECT_ID('EMPLOYEES') IS NOT NULL
BEGIN
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK
DROP TABLE ...
1
vote
3answers
7k views
How to safely recreate a MS SQL table that has other objects depending on it?
I have a table in A Microsoft SQL 2012 database, called Courses. It was "upconverted" from an originally SQL 2000 database, upconverted to SQL 2008 database, and then upconverted again to 2012 (You ...
17
votes
6answers
79k views
How to drop multiple tables with common prefix in one query?
I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query?
something like that table names:
LG_001_01_STLINE,
LG_001_02_STFICHE
4
votes
2answers
10k views
DROP TABLE … CASCADE does not drop any dependent table
I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My ...
3
votes
1answer
1k views
SQL Server 'ignores' drop table command [duplicate]
I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the ...
0
votes
1answer
2k views
Why can I drop table when logged in as not owner?
I read that only owner can drop table and no other user may be granted to do so.
I created a table with owner postgres and then drop it as another user. What privileges should I revoke to disallow it?...
1
vote
1answer
777 views
Is it possible to drop tablespace CASCADE in MySQL?
I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query:
DROP TABESPACE ...
3
votes
1answer
5k views
What is preventing DROP TABLE from removing a Postgres table? [duplicate]
I have a postgres database named aarons_db in ubuntu linux. I'm trying to delete a table called pnt within aarons_db. To do this, I am using the following command:
DROP TABLE pnt
Unfortunately, this ...
1
vote
1answer
1k views
Drop all tables from database command apart from named few
I have been looking for a command to drop all tables from a single database apart from a few tables without doing something like this:
DROP table table1, table2, table3
and ignoring the tables I do ...
6
votes
4answers
11k views
SQL Server - Alter table vs drop and create
Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
2
votes
1answer
3k views
Fastest way to disable indices and drop constraints and rebuild SQL Server table
Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps ...
1
vote
0answers
21 views
Delete datafile from parttioned table
I'm used SQL Server 2014.
My big table partitioned by date column and each partition has own datafile.
I want delete oldest partition data and data file too.
How to do it?
5
votes
3answers
2k views
Drop Tables with Bigger size
We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that ...
