October 2009 Archives

You might find the following queries quite handy for your day to day SQL operations:

1. Get the total number of records in a table:

SELECT COUNT(*) FROM TABLE_NAME;

Here are a few  useful user SQL code snippets in Oracle:

1. To create a user

CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

2. Grant user privileges:

GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO username;


3. Grant all privileges:

GRANT all privileges TO username;

4. Delete a user or to delete a user and the associated user schema

drop user username cascade;

This SQL Snippet get a list of all the tables under a user:

select * from user_objects where object_type = 'TABLE';

This SQL Snippet will get you a list of all the sequences in a DB Schema for the user tables

select * from user_sequences

This SQL Snippet will get you a list of all the view in a DB Schema for the user tables

select * from user_views;

Happy Coding :)

If you ever have to find out the version of Oracle using SQL, use the following SQL command and it will spit out all the details.

SQL
 SELECT * FROM V$VERSION;

OUTPUT

BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production                          
CORE    10.2.0.1.0    Production                                        
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production         
NLSRTL Version 10.2.0.1.0 - Production
----------------------------------------------------------------

Happy Coding :)