ORA-48913 Writing into trace file failed

Alert :   Non critical error ORA-48913 caught while writing to trace file "/opt/oracle/XXXXX
             /diag/rdbms/nptmp/XXXXX/incident/incdir_288464/XXXXX_ora_23179_i288464.trc"
              Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached.

Cause :

                show parameter        max_dump_file_size
                ---------------------------------
                max_dump_file_size        10240

                We are getting this alert in alert.log because when oracle reaches the limit of max 
                dump/trace file size, and its resized to write all content in trace/dump file.
                supported values of this parameter in byes, and special string "UNLIMITED" there is no    
                upper limit of trace file size

Solution : Simply increase the values of max_dump_file_size parameters as per your enviornment  
                 and storage availability.

                SQL> alter system set max_dump_file_size=18240 ;   ( fixed the issued in my case)

                or

                SQL> alter system set max_dump_file_size=UNLIMITED ;
                         ( write dump/trace file as per OS max size limit).


                Hope this article will help you for solving the issue.


=========== Use knowledge to help others not to defect    ============================

ORA-02266: unique/primary keys in table referenced by enabled foreign keys and ORA-02297: cannot disable constraint (MIS_OWNER.USR_PK) - dependencies exist

Alert/error :- When I tried to truncate the table/clear the table data, Oracle not allowed me to 
                      cleared the table data, because whenever we creates primary/unique key constraint, 
                      oracle internally linked column data with ROWID, hence oracle not allow to  
                     remove  table data. however if we disable link between column data and ROWID, then
                    it will allow us to truncate the table.




Workaround :-


SQL> >> truncate table MIS_OWNER.MIS_USERS;
truncate table MIS_OWNER.MIS_USERS
                        *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,status ,TABLE_NAME from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

CONSTRAINT_NAME                C STATUS   TABLE_NAME
------------------------------ - -------- ------------------------------
SYS_C0024627                   C ENABLED  MIS_USERS
SYS_C0024626                   C ENABLED  MIS_USERS
SYS_C0024625                   C ENABLED  MIS_USERS
SYS_C0024624                   C ENABLED  MIS_USERS
SYS_C0024623                   C ENABLED  MIS_USERS
SYS_C0024622                   C ENABLED  MIS_USERS
SYS_C0024621                   C ENABLED  MIS_USERS
SYS_C0024620                   C ENABLED  MIS_USERS
AVCON_MIS_E_NEW_F_000          C ENABLED  MIS_USERS
USR_UK                         U ENABLED  MIS_USERS
USR_PK                         P ENABLED  MIS_USERS

11 rows selected.

SQL> select ' alter table ' ||owner||'.'||TABLE_NAME|| ' disable constraint '||CONSTRAINT_NAME || ';' from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024627;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024626;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024625;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024624;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024623;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024622;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024621;
 alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024620;
 alter table MIS_OWNER.MIS_USERS disable constraint AVCON_MIS_E_NEW_F_000;
 alter table MIS_OWNER.MIS_USERS disable constraint USR_UK;
 alter table MIS_OWNER.MIS_USERS disable constraint USR_PK;

11 rows selected.


 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024627;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024626;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024625;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024624;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024623;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024622;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024621;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint SYS_C0024620;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint AVCON_MIS_E_NEW_F_000;
 SQL> alter table MIS_OWNER.MIS_USERS disable constraint USR_UK;

 SQL>  alter table MIS_OWNER.MIS_USERS disable constraint USR_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (MIS_OWNER.USR_PK) - dependencies exist

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,status ,TABLE_NAME from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

CONSTRAINT_NAME                C STATUS   TABLE_NAME
------------------------------ - -------- ------------------------------
SYS_C0024627                   C DISABLED MIS_USERS
SYS_C0024626                   C DISABLED MIS_USERS
SYS_C0024625                   C DISABLED MIS_USERS
SYS_C0024624                   C DISABLED MIS_USERS
SYS_C0024623                   C DISABLED MIS_USERS
SYS_C0024622                   C DISABLED MIS_USERS
SYS_C0024621                   C DISABLED MIS_USERS
SYS_C0024620                   C DISABLED MIS_USERS
AVCON_MIS_E_NEW_F_000          C DISABLED MIS_USERS
USR_UK                         U DISABLED MIS_USERS
USR_PK                         P ENABLED  MIS_USERS

11 rows selected.

SQL> truncate table MIS_OWNER.MIS_USERS;
truncate table MIS_OWNER.MIS_USERS
                        *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> alter table MIS_OWNER.MIS_USERS disable constraint USR_PK cascade;

Table altered.


SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,status ,TABLE_NAME from dba_constraints where TABLE_NAME='MIS_USERS' and owner='MIS_OWNER';

CONSTRAINT_NAME                C STATUS   TABLE_NAME
------------------------------ - -------- ------------------------------
SYS_C0024627                   C DISABLED MIS_USERS
SYS_C0024626                   C DISABLED MIS_USERS
SYS_C0024625                   C DISABLED MIS_USERS
SYS_C0024624                   C DISABLED MIS_USERS
SYS_C0024623                   C DISABLED MIS_USERS
SYS_C0024622                   C DISABLED MIS_USERS
SYS_C0024621                   C DISABLED MIS_USERS
SYS_C0024620                   C DISABLED MIS_USERS
AVCON_MIS_E_NEW_F_000          C DISABLED MIS_USERS
USR_UK                         U DISABLED MIS_USERS
USR_PK                         P DISABLED MIS_USERS

11 rows selected.

SQL> truncate table MIS_OWNER.MIS_USERS;

Table truncated.

========================= THANK YOU  ===================================

Listing user Granted Privileges

I had written blogs, when i received request from user and he wanted to know all granted rights/privileges to DB user.
For this purpose there are few DBA views ( DBA_TAB_PRIVS,DBA_ROLE_PRIVS ,DBA_SYS_PRIVS) from which we can query and get desire outputs.
supporting view explanation :-
-----------------------
DBA_USERS      ----------->    List info about all the users in the database.
DBA_TAB_PRIVS  ----------->    Lists the object privileges granted to users.
DBA_ROLE_PRIVS ----------->    Lists roles that are granted/assigned to a either another role or a user.
DBA_SYS_PRIVS  ----------->    Lists system privileges that are granted/assigned to a either another role or a user.


For the instance  :-  

Way 1 :-
select dbms_metadata.get_ddl( 'USER', 'DB_ADMIN' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'DB_ADMIN' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'DB_ADMIN' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'DB_ADMIN' ) || '/' from dual

Way 2 :-


Granted objects privs
----------------------
SQL> select GRANTEE,PRIVILEGE, TABLE_NAME from dba_tab_privs where grantee='LABUSER';

GRANTEE                        PRIVILEGE                                TABLE_NAME
====================================================================================
LBACSYS                        QUERY REWRITE                            EXPDEPACT$
LBACSYS                        DEBUG                                    EXPDEPACT$
LBACSYS                        FLASHBACK                                EXPDEPACT$
LBACSYS                        INDEX                                    EXPDEPACT$
LBACSYS                        DELETE                                   EXPPKGACT$
LBACSYS                        INDEX                                    EXPPKGACT$
LBACSYS                        INSERT                                   EXPPKGACT$
LBACSYS                        SELECT                                   EXPPKGACT$
LBACSYS                        UPDATE                                   EXPPKGACT$
LBACSYS                        FLASHBACK                                EXPPKGACT$
LBACSYS                        ALTER                                    EXPPKGACT$
LBACSYS                        REFERENCES                               EXPPKGACT$
LBACSYS                        ON COMMIT REFRESH                        EXPPKGACT$
LBACSYS                        QUERY REWRITE                            EXPPKGACT$
LBACSYS                        DEBUG                                    EXPPKGACT$


Granted roles
----------------
SQL> select grantee, granted_role from dba_role_privs where grantee='LABUSER';

GRANTEE                        GRANTED_ROLE
===================================================
DB_ADMIN                       SELECT_CATALOG_ROLE
DB_ADMIN                       CONNECT
DB_ADMIN                       DBA
DB_ADMIN                       DBA
DB_ADMIN                       CONNECT

System Privs
==================
SQL> select grantee, PRIVILEGE from dba_sys_privs where grantee='LABUSER';
GRANTEE         PRIVILEGE
=============================
DB_ADMIN     CREATE SESSION
DB_ADMIN     SELECT ANY DICTIONARY
DB_ADMIN     SELECT ANY TABLE
DMSYS         ALTER SESSION
DMSYS         ALTER SYSTEM
DMSYS         CREATE JOB
DMSYS         CREATE LIBRARY




====================== THANK YOU =====================================