1 Table in the Oracle Database
1) User Tables:
a Are a collection of tables created and maintained by the user
b Contain user information
2) Data Dictionary
a is a collection of table created and maintained by the Oracle Server
b Contain database information
2 Querying the Data Dictionary
1)see the names of the table owned by the user
select table_name from user_tables;
2) view distinct object types ownered by the user
select distinct object_type from user_object;
3) view tables ,view ,synonyms and sequences owned by the user
select * from user_catalog
3 Creating a Table by Ussing a Subquery Syntax
create table tt3
as
select * from authors
4 Teh alter table Statement
1) Add a new column
alter table tt2
add(fname varchar2(20) default 'unkonown',
address varchar2(30) null);
2)Modigying a Column's data type size and default value
alter table dept80
modigy (last_name varchr2(30))
A change to thee default value affects onlly subsequent insertion to the table
3) drop a column
alter table dept80
drop column job_id;
The set unseed Option
a you use the set unused optoin to mark one or more columns as unused
b you use the drop unused colimns options to remove the columns that are marked as
as unused
alter table tt2
set unused colun fnamel;
alter table table
drop unused columns
5 Dropping a Table
1) All data and structure in the table is deleted
2) Any pending transaction are committed
3) All indexes are dropped
4) You cannot roll back the drop table statement
6 Changing the Name of an Object
rename dept to detail_dept;
you must be the owner of the object
7 Truncate a Table
Remove all rows from the table
release the storage space used by that table
you cannot rollback row when using truncate
alternatly ,you can remove row by using delete statement