SQL: DROP USER, TABLE, COLUMN, DATA

DROP USER

Purpose
Use the DROP USER statement to remove a database user and optionally remove the user's objects.

When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.
Caution: Do not attempt to drop the users SYS or SYSTEM. Doing so will corrupt your database.
Prerequisites
You must have the DROP USER system privilege.
Syntax

drop_user::=
Description of drop_user.gif follows
user
Specify the user to be dropped. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.

CASCADE 
Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.
  • If the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
  • If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.
DROP TBALE[ORACLE SQL]
drop table table_name;
drop table table_name cascade constraints;
drop table table_name purge;

The drop table command moves a table into the recycle bin unless purge was also specified.

purge
Normally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped. However, if the purge modifier is specified as well, the table is unrecoverably (entirely) dropped from the database.

cascade constraints
Deletes all foreign keys that reference the table to be dropped, then drops the table.


Dropping Columns

Now you can mark a column as unused (logical delete) or delete it completely (physical delete).

Logical Delete

On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it.
ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following.
ALTER TABLE table_name DROP UNUSED COLUMNS;
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.

Physical Delete

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns.
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);
Dropping a column from a table will cause all unused columns in that table to be dropped at the same time.

DELETE Statement

Description

The Oracle DELETE statement is used to delete a single record or multiple records from a table in Oracle.

Syntax

The syntax for the DELETE statement in Oracle/PLSQL is:
DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table
The table that you wish to delete records from.
WHERE conditions
Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, then all records from the table will be deleted.

Comments

Post a Comment

Popular posts from this blog

gsutil Vs Storage Transfer Service Vs Transfer Appliance

When transferring data from an on-premiss location, use gsutil When transferring data from another cloud storage provider, use storage transfer service gsutil is a Python application that lets you access Cloud Storage from the command line. You can use gsutil to do a wide range of bucket and object management tasks, including: Creating and deleting buckets. Uploading, downloading, and deleting objects. Listing buckets and objects. Moving, copying, and renaming objects. Editing object and bucket ACLs. gsutil performs all operations, including uploads and downloads, using HTTPS and transport-layer security (TLS). For a complete list of guides to completing tasks with gsutil, see  Cloud Storage How-to Guides . Storage Transfer Service is a product that enables you to: Move or backup data to a Cloud Storage bucket either from other cloud storage providers or from your  on-premises  storage. Move data from one Cloud Storage bucket to another, so that it is available to differe...
Read more

SQL basic interview question

Image
Question #1) What is SQL? Structured Query Language is a database tool which is used to create and access database to support software application. Question #2) What are tables in SQL? The table is a collection of record and its information at a single view. Question #3) What are different types of statements supported by SQL? There are 3 types of SQL statements 1) DDL (Data Definition Language):  It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop. Some of the DDL Commands are listed below CREATE : It is used for creating the table. 1 CREATE   TABLE  table_name 2 column_name1 data_type( size ), 3 column_name2 data_type( size ), 4 column_name3 data_type( size ), ALTER:  The ALTER table is used for modifying the existing table object in the database. ALTER TABLE table_name ADD column_name datatype OR ALTER TABLE table_name DROP ...
Read more