If you think you don't need a table then you can drop it with the DROP TABLE table_name clause. It is easy thing to drop but before drop you should know around the consequence of dropping a table.
If you drop a table then before that think about following consequences.
•After dropping table you no longer access the data in it.
•All views and PL/SQL program units dependent on a dropped table remain in tact but they become unusable or invalid.
•All indexes and triggers associated with a table are dropped.
•All synonyms for a dropped table remain, but return an error when used.
•All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects.
Example of This Scenario:
----------------------------------------
1)Look at free space of tablespace user_tbs.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072
2)Now create a table in that tablespace.
SQL> create table test2 tablespace user_tbs as select level a1, level a2 , level a3 from dual connect by level<=10000;
Table created.
3)Let's check the free space of the tablespace now.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98172928
4)Let's drop the table and check again free space.
SQL> drop table test2;
Table dropped.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072
A)To drop a table simply use DROP TABLE .. keyword. To drop test table use,
DROP TABLE TEST;
B)If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:
Example of This Scenario:
-----------------------------
1)Create both parent and child table.
SQL> create table parent(a number primary key);
Table created.
SQL> create table child ( b number references parent);
Table created.
2)Check the Constraints and their type.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006345 R
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='PARENT';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006344 P
3)Now drop parent table produce oracle error.
Now try to drop parent table.
SQL> drop table parent;
drop table parent
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
It raises ORA-02449 because the primary keys in table referenced by foreign keys. So to drop the table you have to drop foreign key constraints of the child table. This is done by
4)To drop parent table you have to include cascade constraints which will drop foreign key constraints.
SQL> drop table parent cascade constraints;
Table dropped.
5)Check the child table and see no constraints there.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
no rows selected
If you check the metadata of child table before dropping the parent table and after dropping parent table you will see table definition is changed.
Before drop I got ,
SQL> select dbms_metadata.get_ddl('TABLE','CHILD') from dual;
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER,
FOREIGN KEY ("B")
REFERENCES "ARJU"."PARENT" ("A") ENABLE
)
After drop parent table I get,
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER
)
C)When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:
SQL>DROP TABLE child PURGE;
Table dropped.
To drop object from recylcebin use PURGE statement.
SQL> purge table parent;
Table purged.
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL
http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html
Showing posts with label Oracle Concepts. Show all posts
Showing posts with label Oracle Concepts. Show all posts
Wednesday, June 11, 2008
Tuesday, May 20, 2008
Default Tablespace in Oracle.
•A default tablespace in oracle is the tablespace which will be used as default tablespace whenever a new user is created that user is implicitly assigned with that tablespace. So if that new user create any objects the objects will be by default created in his default tablespace.
•If default tablespace is not specified when creating a database or when created schema/schema objects, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.
•A default tablespace can be specified one of two ways at the database level:
1) during database creation via the CREATE DATABASE command
or
2) after database creation via the ALTER DATABASE command.
•The default tablespace can only be locally managed.
•In order to see the database default tablespace issue,
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS
•In order to see a particular user default tablespace use,
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
DEFAULT_TABLESPACE
------------------------------
USER_TBS
•In order to set database default tablespace compatible parameter must be greater than 10.0 To see current compatiblity settings issue,
SQL> show parameter compatible
If current compatibility settings is less than 10.0 the following error will come,
SQL> ALTER DATABASE DEFAULT TABLESPACE new_tbs;
*
ERROR at line 1:
ORA-12916: Cannot use default permanent tablespace with this release
•Database default tablespace can't be dropped. if you are going to drop the following error will arise.
SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
In order to drop a database default tablespace assign other tablespace as database default tablespace and then drop.
SQL> alter database default tablespace user_tbs;
Database altered.
SQL> drop tablespace users including contents;
Tablespace dropped.
•If default tablespace is not specified when creating a database or when created schema/schema objects, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.
•A default tablespace can be specified one of two ways at the database level:
1) during database creation via the CREATE DATABASE command
or
2) after database creation via the ALTER DATABASE command.
•The default tablespace can only be locally managed.
•In order to see the database default tablespace issue,
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS
•In order to see a particular user default tablespace use,
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
DEFAULT_TABLESPACE
------------------------------
USER_TBS
•In order to set database default tablespace compatible parameter must be greater than 10.0 To see current compatiblity settings issue,
SQL> show parameter compatible
If current compatibility settings is less than 10.0 the following error will come,
SQL> ALTER DATABASE DEFAULT TABLESPACE new_tbs;
*
ERROR at line 1:
ORA-12916: Cannot use default permanent tablespace with this release
•Database default tablespace can't be dropped. if you are going to drop the following error will arise.
SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
In order to drop a database default tablespace assign other tablespace as database default tablespace and then drop.
SQL> alter database default tablespace user_tbs;
Database altered.
SQL> drop tablespace users including contents;
Tablespace dropped.
About Opening database with the RESETLOGS Option
In case of incomplete recovery using backup control file in order to open the database you must have issue ALTER DATABASE OPEN RESETLOGS option. Now question is what RESETLOGS does? In the following section it is demonstrate what it does.
1)It creates a new incarnation of the database. You can say a new version of database life is produced. To know more about incarnation search within my blog.
2)If the current online redo logs are accessible then archive those, erases the contents of the online redo logs and resets the log sequence number to 1. You can see current log sequence number from V$LOG. SQL> SELECT SEQUENCE#, GROUP# FROM v$log;
3)If online redo log files are not exist then create online redo log files.
4)Re initializes the control file metadata about online redo logs and redo threads.
5)Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.
1)It creates a new incarnation of the database. You can say a new version of database life is produced. To know more about incarnation search within my blog.
2)If the current online redo logs are accessible then archive those, erases the contents of the online redo logs and resets the log sequence number to 1. You can see current log sequence number from V$LOG. SQL> SELECT SEQUENCE#, GROUP# FROM v$log;
3)If online redo log files are not exist then create online redo log files.
4)Re initializes the control file metadata about online redo logs and redo threads.
5)Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.
Saturday, May 10, 2008
Working an object that resides on multiple datafile
An object can span in multiple datafiles within a single tablespace. In this case if I make one datafile offline then the contents within that datafile will be affected, other datafile is online and can be possible to query which may return rows with errors. To illustarte this scenario I will make two datafiles inside a tablespace.
1)Create Tablespace.
SQL> CREATE TABLESPACE TEST_TBS DATAFILE '/oradata2/test_tbs01.dbf' SIZE 100K;
Tablespace created.
2)Add datafile to the Tablespace.
SQL> ALTER TABLESPACE TEST_TBS ADD DATAFILE '/oradata2/test_tbs02.dbf' SIZE 1M;
Tablespace altered.
3)Create Table inside the Tablespace.
SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_TBS AS SELECT LEVEL B1 FROM DUAL CONNECT BY LEVEL<9999;
Table created.
4)Check which datafile this table belong to.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TABLE');
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEST_TBS /oradata2/test_tbs01.dbf
TEST_TBS /oradata2/test_tbs02.dbf
5)Query the table it will be ok.Now make later datafile offline and query from table.
SQL> alter database datafile '/oradata2/test_tbs02.dbf' OFFLINE;
Database altered.
SQL> select * from TEST_TABLE;
1
.
.
3270
ERROR:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'
3270 rows selected.
1)Create Tablespace.
SQL> CREATE TABLESPACE TEST_TBS DATAFILE '/oradata2/test_tbs01.dbf' SIZE 100K;
Tablespace created.
2)Add datafile to the Tablespace.
SQL> ALTER TABLESPACE TEST_TBS ADD DATAFILE '/oradata2/test_tbs02.dbf' SIZE 1M;
Tablespace altered.
3)Create Table inside the Tablespace.
SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_TBS AS SELECT LEVEL B1 FROM DUAL CONNECT BY LEVEL<9999;
Table created.
4)Check which datafile this table belong to.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TABLE');
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEST_TBS /oradata2/test_tbs01.dbf
TEST_TBS /oradata2/test_tbs02.dbf
5)Query the table it will be ok.Now make later datafile offline and query from table.
SQL> alter database datafile '/oradata2/test_tbs02.dbf' OFFLINE;
Database altered.
SQL> select * from TEST_TABLE;
1
.
.
3270
ERROR:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'
3270 rows selected.
Thursday, May 1, 2008
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
In this topic I will try to make you understand the differences between SELECT ANY DICTIONARY privilege, SELECT ANY TABLE privilege and SELECT_CATALOG_ROLE.
Before proceed it is nice if you remember that ,
•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.
•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.
•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.
•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.
•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.
To make the scenario more clear I will demonstrate an example over
1)ARJU schmea table named A. And over two
2)SYS schema objects OBJ$ Table and
3)SYS schema DBA_USERS view.
SQL> select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
OBJECT_TYPE OBJECT_NAME
------------------- --------------------
VIEW DBA_USERS
TABLE OBJ$
Workaround Example:
----------------------
A)Secnario 1:(When O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
------------------
SQL> create user t identified by t;
User created.
SQL> grant create session to t;
Grant succeeded.
Have only Create Session Privilege
-------------------------------------
SQL> conn t/t
Connected.
SQL> select * from user_tables;
no rows selected
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Table Privilege
-----------------------------------
SQL> conn arju/a
Connected.
SQL> grant select any table to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can select Arju schema's obejct but failed on SYS schema objects.
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have select_catalog_role only
---------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select any table from t;
Revoke succeeded.
SQL> grant select_catalog_role to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema Views.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select * from arju.t;
select * from arju.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Dictionary Privilege
-----------------------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select_catalog_role from t;
Revoke succeeded.
SQL> grant select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema objects.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have both SELECT ANY TABLE and SELECT ANY DICTIONARY Privilege
---------------------------------------------------------------------
Both system privileges together allow access to all SYS and non-SYS objects.
SQL> grant select any table , select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
B)Scenario 2:(When O7_DICTIONARY_ACCESSIBILITY is set to TRUE)
----------------------------------------------------------------
Has only SELECT ANY TABLE privilege
-----------------------------------------
User T can now select all SYS and NO-SYS objects.
Related Documents:
---------------------
What is O7_DICTIONARY_ACCESSIBILITY
Before proceed it is nice if you remember that ,
•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.
•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.
•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.
•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.
•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.
To make the scenario more clear I will demonstrate an example over
1)ARJU schmea table named A. And over two
2)SYS schema objects OBJ$ Table and
3)SYS schema DBA_USERS view.
SQL> select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
OBJECT_TYPE OBJECT_NAME
------------------- --------------------
VIEW DBA_USERS
TABLE OBJ$
Workaround Example:
----------------------
A)Secnario 1:(When O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
------------------
SQL> create user t identified by t;
User created.
SQL> grant create session to t;
Grant succeeded.
Have only Create Session Privilege
-------------------------------------
SQL> conn t/t
Connected.
SQL> select * from user_tables;
no rows selected
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Table Privilege
-----------------------------------
SQL> conn arju/a
Connected.
SQL> grant select any table to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can select Arju schema's obejct but failed on SYS schema objects.
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have select_catalog_role only
---------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select any table from t;
Revoke succeeded.
SQL> grant select_catalog_role to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema Views.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select * from arju.t;
select * from arju.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Dictionary Privilege
-----------------------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select_catalog_role from t;
Revoke succeeded.
SQL> grant select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema objects.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have both SELECT ANY TABLE and SELECT ANY DICTIONARY Privilege
---------------------------------------------------------------------
Both system privileges together allow access to all SYS and non-SYS objects.
SQL> grant select any table , select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
B)Scenario 2:(When O7_DICTIONARY_ACCESSIBILITY is set to TRUE)
----------------------------------------------------------------
Has only SELECT ANY TABLE privilege
-----------------------------------------
User T can now select all SYS and NO-SYS objects.
Related Documents:
---------------------
What is O7_DICTIONARY_ACCESSIBILITY
Sunday, April 13, 2008
About Oracle Certifications of 10g
With Oracle database administration Certification track there are three tires.
1)OCA: The first tier is the Oracle 10g Certified Associate (OCA). To obtain OCA certification, you
must pass the 1Z0-042 exam.
2)OCP: The second tier is the Oracle 10g Certified Professional (OCP), which builds on and
requires OCA certification. To obtain OCP certification, you must attend an approved
Oracle University hands-on class and pass the 1Z0-043 exam.
3)OCM: The third and highest tier is the Oracle 10g Certified Master (OCM), which builds on and requires OCP certification.
1)OCA: The first tier is the Oracle 10g Certified Associate (OCA). To obtain OCA certification, you
must pass the 1Z0-042 exam.
2)OCP: The second tier is the Oracle 10g Certified Professional (OCP), which builds on and
requires OCA certification. To obtain OCP certification, you must attend an approved
Oracle University hands-on class and pass the 1Z0-043 exam.
3)OCM: The third and highest tier is the Oracle 10g Certified Master (OCM), which builds on and requires OCP certification.
Wednesday, April 9, 2008
What is your Oracle Database Software Release.
Oracle Corporation periodically releases new version of oracle database software. As many as five numbers may be required to fully identify a release. From oracle you can check or find oracle database version.
How to See which database version I am using:
1)Select * from v$version;
2)SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
TNS for Solaris: 10.2.0.1.0 Production
Release Number Format Description:
-------------------------------------
1)Major Database Release Number: (Here 10)
The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
2)Database Maintenance Release Number:(Here 2)
The second digit represents a maintenance release level. Some new features may also be included.
3)Application Server Release Number:(Here 0)
The third digit reflects the release level of the Oracle Application Server.
4)Component-Specific Release Number: (Here 1)
The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
5)Platform-Specific Release Number : (Here 0)
The fifth digit identifies a platform-specific release. Usually this is a patch set.
How to See which database version I am using:
1)Select * from v$version;
2)SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
TNS for Solaris: 10.2.0.1.0 Production
Release Number Format Description:
-------------------------------------
1)Major Database Release Number: (Here 10)
The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
2)Database Maintenance Release Number:(Here 2)
The second digit represents a maintenance release level. Some new features may also be included.
3)Application Server Release Number:(Here 0)
The third digit reflects the release level of the Oracle Application Server.
4)Component-Specific Release Number: (Here 1)
The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
5)Platform-Specific Release Number : (Here 0)
The fifth digit identifies a platform-specific release. Usually this is a patch set.
Tuesday, April 1, 2008
What are the difference between DDL, DML and DCL commands?
1)Query-select
2)DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object
3)DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency
4)DCL - Data Control Language. Some examples:
* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command
5)TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
6)System Control Statements
These statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It lets users change settings, such as the minimum number of shared servers, kill a session, and perform other tasks.
7)Embedded SQL Statements
These statements used in a procedural language program, such as those used with the Oracle precompilers. Examples include OPEN, CLOSE, FETCH, and EXECUTE.
Related Documents:
------------------------
Difference Between Truncate, Delete, Drop
2)DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object
3)DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency
4)DCL - Data Control Language. Some examples:
* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command
5)TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
6)System Control Statements
These statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It lets users change settings, such as the minimum number of shared servers, kill a session, and perform other tasks.
7)Embedded SQL Statements
These statements used in a procedural language program, such as those used with the Oracle precompilers. Examples include OPEN, CLOSE, FETCH, and EXECUTE.
Related Documents:
------------------------
Difference Between Truncate, Delete, Drop
Subscribe to:
Posts (Atom)