Insufficient privilege= / as sysdba oracle dba in Window server

In window system when u we getting Insufficient privilege= / as sysdba while connect to database error:

solve:

1.step we have to check first sqlnet.ora is fine or not

even some error repeated menas :

That user not having ora_dba group so we have to add that one.

step:

1. right click my computer --> local users-->user --.> which users(studebt)--->right click that user

and go properties --->click member of- --> check if ora_dba member is no problem but not there mens

click add--->adavance -->find-->ora_dba --->ok

2. once complete 1 step -->connect ur database new cmd if error will come than---> reboot or Restart ur system ----> and than connect ur database it will connect

Thanks:

Kumaresan Nethaji

Index fragmentation in Oracle and how to fixed it.

How to find index is fragmented?

First analyze index 

SQL>analyze index INDEX_NAME validate structure;

Then query INDEX_STATS view

1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.

2. If height is 4 then index should be rebuild.

3. If lf_rows is lower than lf_blks then index should be rebuild.

SQL> column status format a10

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status

----------

21.83%

How to remove index fragmentation?

There are two way to remove fragmentation.

1. index coalesce

2. index rebuild

What is difference between coalesce and rebuild please go through below link for more details

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548

SQL> alter index IDX_OBJ_ID coalesce;

SQL> alter index IDX_OBJ_ID rebuild;

SQL> alter index IDX_OBJ_ID rebuild online;

Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.

SQL> analyze index idx_obj_id validate structure;

Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status

-------

40.85%

SQL> alter index IDX_OBJ_ID rebuild online;

Index altered.

SQL> analyze index idx_obj_id validate structure;

Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status

--------

0%

Schema Refresh using Exp&Imp

Schema Refresh using Exp&Imp:

1. In PROD Database (Source Database name : cbgprod)

1.1   CHECK THE NUMBER OF OBJECTS IN THE SOURCE DATABASE :

    select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PRODAPP' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

------------------------------ ------------------- ------------------

PRODAPP                        INDEX                              240

PRODAPP                        TABLE                              226

PRODAPP                        FUNCTION                             7

PRODAPP                        PROCEDURE                           14

PRODAPP                        LOB                                  3

PRODAPP                        SEQUENCE                             3

PRODAPP                        VIEW                                21

7 rows selected.

1.2 CHECK THE INVALID OBJECTS IN THE SOURCE DATABASE :

    select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER='PRODAPP';

1.3 TAKE EXPORT BACKUP OF PRODAPP SCHEMA FROM THE SOURCE DATABASE :

    exp userid=system/sunlight file=/u98/oradata/cbgprod/exports/cbgprod.exp_PRODAPP_5706.dmp log=/u98/oradata/cbgprod/exports/cbgprod.exp_PRODAPP_5706.log owner=PRODAPP rows=y indexes=y grants=y constraints=y

1.4 TAKE THE REQUIRED INFORMATION FROM THE SOURCE DATABASE :

    set line 1000 
    select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PRODAPP';

USERNAME    PASSWORD              DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE      PROFILE

---------- ---------------------- ---------------------- ------------------------ ------------------------------

PRODAPP     E4F10C7228A9E74A      CBG_DEFAULT             TEMP                     CVISBRAND_CUSTACCT_UNLIMITED

select * from dba_role_privs where grantee in('PRODAPP');

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

PRODAPP                        RESOURCE                       NO  YES

PRODAPP                        CONNECT                        NO  YES

    select * from dba_sys_privs where grantee in('PRODAPP');

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

PRODAPP                        CREATE TABLE                             NO

PRODAPP                        CREATE SESSION                           NO

PRODAPP                        CREATE TRIGGER                           NO

PRODAPP                        CREATE PROCEDURE                         NO

PRODAPP                        CREATE SEQUENCE                          NO

PRODAPP                        UNLIMITED TABLESPACE                     NO

PRODAPP                        SELECT ANY DICTIONARY                    NO

PRODAPP                        CREATE VIEW                              NO

8 rows selected.

  
    select * from dba_ts_quotas where username='PRODAPP';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO

------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---

PDK_IDX                        PRODAPP                          22544384         -1       2752         -1 NO

CBG_SML_IDX01                  PRODAPP                         773849088         -1      94464         -1 NO

CBG_LRG_IDX01                  PRODAPP                          20971520         -1       2560         -1 NO

CBG_SML_DAT01                  PRODAPP                         880803840         -1     107520         -1 NO

CBG_MDM_IDX01                  PRODAPP                        4672454656         -1     570368         -1 NO

CBG_LRG_DAT01                  PRODAPP                          12582912         -1       1536         -1 NO

PDK_DAT                        PRODAPP                          21757952         -1       2656         -1 NO

CBG_DEFAULT                    PRODAPP                          97779712         -1      11936         -1 NO

CBG_MDM_DAT01                  PRODAPP                        3644850176         -1     444928         -1 NO

USERS                          PRODAPP                                 0         -1          0         -1 NO

10 rows selected.

1.4 SEND THE FULL EXPORT BACKUP TO THE TARGET DATABASE SERVER :

    From  /u98/oradata/cbgprod/exports> scp cbgprod.exp_PRODAPP_5706.dmp oracle@e2uscbgprodba01:/u98/oradata/cbgprot/exports/

 **************************Source Database-End************************************* 

 **************************Target Database-Start************************************* 

2. In CBGPROT DEV Database server: (e2uscbgprodba01)


2.1 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :

    SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PRODAPP' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)
------------------------------ ------------------- ------------------
PRODAPP                        INDEX                              271
PRODAPP                        TABLE                              260
PRODAPP                        PROCEDURE                           16
PRODAPP                        FUNCTION                             7
PRODAPP                        SEQUENCE                            21
PRODAPP                        TRIGGER                             18
PRODAPP                        LOB                                 16
PRODAPP                        VIEW                                21

8 rows selected.



2.2 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE :
    select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='PRODAPP';

2.3 TAKE EXPORT BACKUP OF PRODAPP SCHEMA FROM THE TARGET DATABASE :

    exp userid=system/supp0rt file=/u98/oradata/cbgprot/exports/cbgprot.exp_PRODAPP_5706.dmp log=/u98/oradata/cbgprot/exports/cbgprot.exp_PRODAPP_5706.log owner=PRODAPP rows=y indexes=y grants=y constraints=y
2.4 TAKE THE REQUIRED INFORMATION FROM THE TARGET DATABASE :
    set line 1000 
    select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PRODAPP';
    select * from dba_role_privs where grantee in('PRODAPP');
    select * from dba_sys_privs where grantee in('PRODAPP');
    select * from dba_ts_quotas where username='PRODAPP';

SQL>    select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PRODAPP';
    

USERNAME                       PASSWORD                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           PROFILE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
PRODAPP                        E4F10C7228A9E74A               CBG_DEFAULT                    TEMP                           CVISBRAND_CUSTACCT_UNLIMITED

SQL> select * from dba_role_privs where grantee in('PRODAPP');

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
PRODAPP                        RESOURCE                       NO  YES
PRODAPP                        CONNECT                        NO  YES

SQL>    select * from dba_sys_privs where grantee in('PRODAPP');
     
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
PRODAPP                        CREATE TABLE                             NO
PRODAPP                        CREATE SESSION                           NO
PRODAPP                        CREATE TRIGGER                           NO
PRODAPP                        CREATE PROCEDURE                         NO
PRODAPP                        CREATE SEQUENCE                          NO
PRODAPP                        UNLIMITED TABLESPACE                     NO
PRODAPP                        SELECT ANY DICTIONARY                    NO
PRODAPP                        CREATE VIEW                              NO

8 rows selected.

SQL>select * from dba_ts_quotas where username='PRODAPP';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
CBG_LRG_IDX01                  PRODAPP                          20971520         -1       2560         -1 NO
PDK_IDX                        PRODAPP                          23592960         -1       2880         -1 NO
USERS                          PRODAPP                                 0         -1          0         -1 NO
CBG_DEFAULT                    PRODAPP                          74711040         -1       9120         -1 NO
CBG_SML_DAT01                  PRODAPP                         893386752         -1     109056         -1 NO
CBG_MDM_IDX01                  PRODAPP                        4141875200         -1     505600         -1 NO
CBG_SML_IDX01                  PRODAPP                         779091968         -1      95104         -1 NO
CBG_LRG_DAT01                  PRODAPP                          12582912         -1       1536         -1 NO
PDK_DAT                        PRODAPP                          36962304         -1       4512         -1 NO
CBG_MDM_DAT01                  PRODAPP                        3034578944         -1     370432         -1 NO

10 rows selected.

2.5 DROP THE PRODAPP USERS FROM THE TARGET DATABASE :
    drop user PRODAPP cascade;

2.6 CREATE THE PRODAPP USER AS PER THE SOURCE DATABASE :

    create user PRODAPP identified by values 'E4F10C7228A9E74A'
    default tablespace CBG_DEFAULT temporary tablespace TEMP
    profile CVISBRAND_CUSTACCT_UNLIMITED
    quota unlimited on PDK_IDX
    quota unlimited on CBG_SML_IDX01
    quota unlimited on CBG_LRG_IDX01
    quota unlimited on CBG_SML_DAT01
    quota unlimited on CBG_MDM_IDX01
    quota unlimited on CBG_LRG_DAT01
    quota unlimited on CBG_DEFAULT
    quota unlimited on CBG_MDM_DAT01
    quota unlimited on USERS;


2.7 GRANT THE PRIVILAGES TO THE PRODAPP USER AS PER SOURCE DATABASE :
    grant connect,resource to PRODAPP;
    grant CREATE TABLE,CREATE SESSION,CREATE TRIGGER,CREATE SEQUENCE,UNLIMITED TABLESPACE,SELECT ANY DICTIONARY,CREATE VIEW to PRODAPP;

2.8 IMPORT THE DATA TO THE TARGET DATABASE FROM THE SOURCE DATABASE :

    imp userid=system/supp0rt file=/u98/oradata/cbgprot/exports/cbgprod.exp_PRODAPP_5706.dmp log=/u98/oradata/cbgprot/exports/cbgprod.exp_PRODAPP_5706_imp.log fromuser=PRODAPP touser=PRODAPP ignore=y

2.9 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :

    select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PRODAPP' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)
------------------------------ ------------------- ------------------
PRODAPP                        INDEX                              236
PRODAPP                        TABLE                              221
PRODAPP                        FUNCTION                             7
PRODAPP                        PROCEDURE                           14
PRODAPP                        SEQUENCE                             3
PRODAPP                        LOB                                  3
PRODAPP                        VIEW                                21

7 rows selected.



2.10 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE :

    select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='PRODAPP';
2.11 IF ANY INVALID OBJECTS THEN RUN UTLRP.SQL
 
    sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql

***********************************END************************************************