Script for Dropping all objects from Schema

Guys,Use below script to drop all objects from schema but be careful when you are firing below command any schema,Check whether you are executing following script in correct schema.


Begin
For c1 in(select a.object_name object_name,a.object_type object_type from user_objects a where a.object_type not in('LOB','INDEX','TABLE PARTITION','TRIGGER','INDEX PARTITION','PACKAGE BODY','JOB',
'JAVA CLASS') And upper(object_name) = object_name)

LOOP
IF C1.object_type ='TABLE' THEN
Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name||' CASCADE CONSTRAINTS ';
ELSIF C1.object_type ='JAVA SOURCE' THEN
  Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name;
ELSIF C1.object_type ='TYPE' THEN
  Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name||' FORCE'; 
ELSE
Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name;
END IF;
End Loop;
Exception
When Others then
Dbms_Output.put_line('Msg '||SQLERRM );
End;


Don't forget to purge the recyclebin,

Execute below command:

SQL> Purge Recyclebin;

Recyclebin is same as our windows recycle bin which will store all deleted files.


If you dont want to move dropping items to recycle bin then you can make it off for particular session using below command.

SQL> ALTER SESSION SET RECYCLEBIN=OFF;

(Or ) --For On

SQL > ALTER SYSTEM SET RECYCLEBIN= ON;

Below command is for entire Database

SQL > ALTER SYSTEM SET RECYCLEBIN= OFF;

(or)

SQL > ALTER SESSION SET RECYCLEBIN= OFF;

You can cross check whether recycle bin is On/Off as of now using below query.

SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

(Or) 

SQL > SHOW PARAMETER RECYCLEBIN;

and you can query recyclebin to check dropped objects.

SQL> Select * from Recyclebin;

Objecttypes which will be stored in recyclebin:

 

Before/After purging recyclebin:

And use below  program to drop Oracle JOBS from your database.

 

Script For compiling invalid objects

Hi Frds,You may face a situation to compile invalid objects but it would be difficult to compiling one  by one object.

To do in single step,we can use following anonymous block.

1) 
Begin
 For C1 IN(select a.object_name,a.object_type from User_Objects a where  status='INVALID')
LOOP
IF C1.OBJECT_TYPE='PACKAGE BODY' THEN

EXECUTE IMMEDIATE 'ALTER PACKAGE '||C1.object_name||' COMPILE BODY';
ELSE

EXECUTE IMMEDIATE 'ALTER '||C1.OBJECT_TYPE||' '||C1.object_name||' COMPILE';

End If;
End Loop;
Exception
When Others then

Dbms_Output.put_line('Msg '||SQLERRM);
End;  


Above script can be modified to compile specific object type i.e like synonym,view,package etc by using where clause.

If still don't want to execute above script then you can use below utility provided by Oracle to compile full schema.

2) 

SQL > exec dbms_utility.compile_schema(schema => 'Schema Name');




Below one is one more simple method to compile full schema.

3)

SQL> utl_recomp.recomp_parallel('20',<schema name>);

How to connect through SQL Developer

In earlier post ,we have seen how to install SQL developer and now lets check out how can we add connection/schema to SQL developer.


Open SQL developer and click on new connection as shown below.

now pop up window ill open to ask you for details as below.

here mainly you have to concentrate on
1) username and password ==> These can be get it from DBA .
2) SID==> it means you need to mention your database name
3) Host name ==> Let it be local host if  database is installed in your machine otherwise you have provide IP address of where database is installed.

Now you can do testing of your connection by clicking on "Test" and you can see status .
If its success then you can click on connect and use it for your work.

How to Install Oracle SQL Developer

Hi Guys,


After installing Oracle Database ,we search for tools like TOAD,PLSQL developer etc which can be used to work with database but you have to pay for it orelse you can get trail version.

By default Oracle will provide Oracle SQL developer which can be found as below.

Click on SQL developer and then it will ask for java path as below.

Java path can be found from your oracle installed path itlself,it would be similar to below path...

C:\app\Venky\product\11.2.0\dbhome_1\jdk\bin

In above path you have search for java.exe file and provide that path as below.

once you click on "ok" ,it ill be installed on your machine for your use and you can connect to database using this tool.

Group Functions

GROUP FUNCTIONS

a)    Sum
b)    Avg
c)    Max
d)    Min
e)    Count

Group functions will be applied on all the rows but produces single output.

a) SUM

  This will give the sum of the values of the specified column.

     Syntax: sum (column)

     Ex:   SQL> select sum(sal) from emp;

  SUM(SAL)
   ----------
     38600

b) AVG

     This will give the average of the values of the specified column.

     Syntax: avg (column)

     Ex:     SQL> select avg(sal) from emp;

   AVG(SAL)
   ---------------
   2757.14286

c) MAX

     This will give the maximum of the values of the specified column.

     Syntax: max (column)

     Ex:   SQL> select max(sal) from emp;

    MAX(SAL)
   ----------
     5000

d) MIN

     This will give the minimum of the values of the specified column.

     Syntax: min (column)

     Ex:    SQL> select min(sal) from emp;

   MIN(SAL)
   ----------
     500

e) COUNT

     This will give the count of the values of the specified column.

     Syntax: count (column)

     Ex:    SQL> select count(sal),count(*) from emp;

COUNT(SAL)               COUNT(*)
--------------         ------------
        14                   14

Conversion and Miscellaneous Functions

MISCELLANEOUS FUNCTIONS

a)    Uid
b)    User
c)    Vsize
d)    Rank
e)    Dense_rank

a) UID

     This will returns the integer value corresponding to the user currently logged in.

     Ex: SQL> select uid from dual;

       UID
    ----------
       319

b) USER

     This will returns the login’s user name.

     Ex:  SQL> select user from dual;


USER
--------
RAM

c) VSIZE

     This will returns the number of bytes in the expression.

     Ex:    SQL> select vsize(123), vsize('computer'), vsize('12-jan-90') from dual;

VSIZE(123)         VSIZE('COMPUTER')        VSIZE('12-JAN-90')
-------------  -----------------------  ----------------------
         3                     8                          9

d) RANK

     This will give the non-sequential ranking.

     Ex:    SQL> select rownum,sal from (select sal from emp order by sal desc);
    ROWNUM        SAL
    ---------- ----------
         1          5000
         2          3000
         3          3000
         4          2975
         5          2850
         6          2450
         7          1600
         8          1500
         9          1300
        10          1250
        11          1250

     SQL> select rank(2975) within group(order by sal desc) from emp;

RANK(2975)WITHINGROUP(ORDERBYSALDESC)
---------------------------------------------------------
                                    4

d) DENSE_RANK

     This will give the sequential ranking.
    
Ex:    SQL> select dense_rank(2975) within group(order by sal desc) from emp;

DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)
-----------------------------------------------------------------
                                          3

CONVERSION FUNCTIONS

a)    Bin_to_num
b)    Chartorowid
c)    Rowidtochar
d)    To_number
e)    To_char
f)    To_date

a) BIN_TO_NUM

     This will convert the binary value to its numerical equivalent.

     Syntax: bin_to_num( binary_bits)

     Ex:    SQL> select bin_to_num(1,1,0) from dual;

BIN_TO_NUM(1,1,0)
------------------------
                6

1)    If all the bits are zero then it produces zero.
2)    If all the bits are null then it produces an error.

b) CHARTOROWID

     This will convert a character string to act like an internal oracle row identifier or rowid.

c) ROWIDTOCHAR

    This will convert an internal oracle row identifier or rowid to character string.

d) TO_NUMBER

    This will convert a char or varchar2 to number.

e) TO_CHAR

    This will convert a number or date to character string.

f) TO_DATE

    This will convert a number, char or varchar2 to a date.