Blog

max_dump_file_size in 12c

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert log).

ModifiableALTER SESSION, ALTER SYSTEM
  • A numeric value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
  • A numeric value followed by a K or M or G suffix specifies the file size in kilobytes, megabytes, or gigabytes.
  • The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.

The trace file can be split into a maximum of 5 segments, and the size of each segment will typically be 1/5th of the trace file limit.

Truncate a Table of Other User

In order to truncate a table of other user, the DROP ANY TABLE system privilege is required. Without granting this powerful privilege, instead, a procedure is created and granted to the user who can truncate other user’s tables.

In this example, user B is able to truncate table TEST of user A without needing “DROP ANY TABLE” system privilege.

1) In schema A, create a procedure called “truncate_tab”:

SQL>show user

USERA

SQL>create or replace procedure truncate_tab (tab_name IN varchar2) 
as 
begin 
   execute immediate 'truncate table '||tab_name ; 
end; 
/

2) Grant execute on procedure truncate_tab to user B:

SQL>show user

USERA

SQL>grant execute on a.truncate_tab to B;

Grant succeeded.

3) Truncate table A.TEST by user A:

SQL>show user

USERB

SQL> exec a.truncate_tab('TEST');

PL/SQL procedure successfully completed.

Gather Fixed Object Stats Hangs on X$DURABLE_SHARDED_SUBS

SYMPTOM

On 12.1.0.2 database, try to gather fixed table statistics with below command, it hangs at object “X$DURABLE_SHARDED_SUBS”.  Also hangs at select couint(*) from X$DURABLE_SHARDED_SUBS.

SQL>EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()

SQL>select count(*) from X$DURABLE_SHARDED_SUBS;

CAUSES

It is a bug for this issue, it is supposed to be fixed in 12.2.0.X.

WORKAROUND

Lock the statistics of this fixed table:

SQL>exec dbms_stats.lock_table_stats('SYS','X$DURABLE_SHARDED_SUBS');

How do I change my Oracle user password ?

c:\>
C:\>sqlplus testuser/Password@//ractest-scan.ractest.local:1521/service

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> password

Changing password for TESTUSER
Old password:
New password:
Retype new password:
Password changed

SQL>

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database

Oracle database instance hung due to recovery area full causing “Archival Stopped Error”.

Check alert.log with below ORA errors:

— Alert.log

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that 
           are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************

Check recovery area view , which says a lot of free space available:

SQL> set pagesize 120
SQL> set linesize 120
SQL> select * from v$recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED  PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-----------  ------------------- ------------------------- ---------------
...
..
ARCHIVED LOG               33.5                         0.8            248
BACKUP PIECE                0                           0                0
...
..
.
7 rows selected.

Check the database resetlog date and time:

SQL> alter session set nls_date_format='YYYYMMDD-HH24:MI:SS';

Session altered.

SQL> select CREATED,RESETLOGS_TIME,PRIOR_RESETLOGS_TIME,
            CONTROLFILE_TIME,VERSION_TIME from v$database;

CREATED           RESETLOGS_TIME    PRIOR_RESETLOGS_T CONTROLFILE_TIME  VERSION_TIME
----------------- ----------------- ----------------- ----------------- -----------------
20180817-11:05:10 20180817-11:05:43 20161009-20:55:02 20180821-15:19:21 20180817-11:05:10

CAUSE

There are archivelogs in recovery area from old databases, which were refreshed or restored with different DB ID, manually delete them, then everything is fine:

ASMCMD> ls -ltr
Type  Redund  Striped  Time             Sys  Name

                                        ...                                        ...
                                        ..
                                        .
                                        Y    2018_08_14/
                                        Y    2018_08_15/
                                        Y    2018_08_16/
           RESETLOGS DATE      ----->   Y    2018_08_17/
                                        Y    2018_08_18/
                                        Y    2018_08_19/
                                        Y    2018_08_20/