Drop Database in Oracle 10g

     It has become easier to drop a database from Oracle 10g. Use the DROP DATABASE command to drop the database. It removes the datafiles,redologs, controlfiles and init parameter files.

$ sqlplus / as sysdba

SQL> Shutdown immediate;

SQL> Startup Mount Exclusive Restrict;

SQL> Drop Database;

FIND THE FREE SPACE IN ORACLE

Select TABLESPACE_NAME,

sum (BYTES) Total_free_space,

max (BYTES) largest_free_extent

From dba_free_space

Group by TABLESPACE_NAME

How to grant all privileges in Oracle

SQL>


SQL> sho user


USER is "SYSTEM"


SQL> select * from system_privilege_map


-- Create a new user


SQL> create user siva identified by sivag;


User created.


SQL> -- connect as siva and grant all privileges to siva


SQL> connect siva / sivag


Connected.


SQL> grant all privileges to siva;


Grant succeeded.

How to grant all privileges in Oracle

Extracting META information from Oracle

List TABLEs
Here's the query that will return the names of the tables defined in the current database:


SELECT table_name  FROM user_tables;
-- or
SELECT table_name FROM tabs;


List VIEWs
Here's the query that will return the names of the VIEWs defined in the current database:

SELECT view_name FROM user_views;


List users
NB: you'll need DBA access rights to execute the following query:

SELECT username FROM dba_users;

Differences between the COMMIT and ROLLBACK commands

 
COMMIT and ROLLBACK commands is significant because committing is always faster than rolling back.
What happens when a change is made to data in a table?Redo log entries are written, storing log entries for data that is about to be changed.
The database is changed, modifying the current value of data in tables.
Rollback entries are created, storing a list of rollback entries to be applied in case a transaction is rolled back.
What happens when a COMMIT command is executed?
Rollback entries are deleted. Changes are already made. Nothing else is required.
What happens when a ROLLBACK command is executed?
Rollback entries are written to the redo logs, namely “undo” redo log entries.
Rollback is applied to the database undoing previously made changes.
Rollback entries are deleted.

JOINS IN ORACLE

Cross-join or Cartesian product.
Merges all data selected from both tables into a single result set.
Inner join.
Combines rows from both tables using matching column
names and column values. The result set includes only rows that
match.
Outer join.
Selects rows from both tables as with an inner join but
including rows from one or both tables that do not have matching
rows in the other table. Missing values are replaced with null values.
Left outer join.
All rows from the left table plus all matching rows from the right table. Column values from the right table are
replaced with null values when the matching right-side row does not exist in the left-side table.
Right outer join.
All rows from the table on the right plus matching rows from the left table, the opposite of the left outer join.
Full outer join.
All rows from both tables, with null values replacing missing values.
Self-join.
This joins a table to itself.
Equi-joins, anti-joins, and range joins.An equi-join combines table data based on equality (=), an anti-join matches data based on inequality (!=, <> or NOT), and a range join compares data using a range of values (<, > or BETWEEN).
Mutable and complex joins.
A mutable join is a join of more than two tables. A complex join is a mutable join with added filtering.