ORA-03297: file contains used data beyond requested RESIZE value

Cause : Some portion of datafile are using by database Segment which you trying to trim.

Solution : Move / Drop all Segment extends from the region prior that you want to trim.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> SELECT TABLESPACE_NAME
  2  FROM DBA_DATA_FILES
  3  WHERE FILE_NAME ='/u01/app/oracle/oradata/orcln1/tbs_data01.dbf';

TABLESPACE_NAME
------------------------------
TBS_DATA

SQL> SELECT DISTINCT SEGMENT_TYPE,SEGMENT_NAME
  2  FROM DBA_EXTENTS
  3  WHERE TABLESPACE_NAME = 'TBS_DATA';

SEGMENT_TYPE       SEGMENT_NAME
------------------ ---------------------------------------------------------------------------------
TABLE              TEST_RECLAIM_SPACE
INDEX              IND_SERIAL_NO_TEST_RECL

SQL> ALTER TABLE TEST_RECLAIM_SPACE MOVE;

Table altered.

SQL> ALTER INDEX IND_SERIAL_NO_TEST_RECL REBUILD TABLESPACE TBS_DATA;

Index altered.

SQL> purge recyclebin;

Recyclebin purged.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcln1/tbs_data01.dbf' RESIZE 1700M;

Database altered.

SQL>

Filter String only/Number Only from Set of Strings.

--- Number only from set of strings using TRANSLATE

SELECT TRANSLATE(UPPER('342fgs1dfsN'), '1ABCDEFGHIJKLMNOPQRSTUVWXYZ', '1') a FROM DUAL;

--- String only from set of strings using TRANSLATE

SELECT TRANSLATE('342fgs1dfsN', '0123456789', '1') a FROM DUAL;

--- Number only from set of strings using Regular Expressions

SELECT REGEXP_REPLACE('3NASDasdas','[a-zA-Z'']','')  FROM DUAL;

--- String only from set of strings using Regular Expressions

SELECT REGEXP_REPLACE('3NASDasd5345as','[0-9'']','')  FROM DUAL;

How to Mount New Mount Point In Linux

[root@OEL64N2 ~]# mount -t ext4 /dev/sdb1 /u02
mount: wrong fs type, bad option, bad superblock on /dev/sdb1,
       missing codepage or helper program, or other error
       In some cases useful info is found in syslog - try
       dmesg | tail  or so

[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]# df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/sda5               11G   3.3G   6.9G  33% /
tmpfs                  1.2G    91k   1.2G   1% /dev/shm
/dev/sda2              4.3G   143M   3.9G   4% /tmp
/dev/sda1               13G    12G   345k 100% /u01
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#  mkfs.ext3 /dev/sdb1 2>/dev/null
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
1638400 inodes, 6552504 blocks
327625 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
200 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000

Writing inode tables: done
Creating journal (32768 blocks):
done
Writing superblocks and filesystem accounting information:
done

This filesystem will be automatically checked every 30 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#
[root@OEL64N2 ~]#  mount -t ext4 /dev/sdb1 /u02
[root@OEL64N2 ~]#
[root@OEL64N2 ~]# df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/sda5               11G   3.3G   6.9G  33% /
tmpfs                  1.2G    91k   1.2G   1% /dev/shm
/dev/sda2              4.3G   143M   3.9G   4% /tmp
/dev/sda1               13G    12G   345k 100% /u01
/dev/sdb1               27G   181M    25G   1% /u02
[root@OEL64N2 ~]#

Cost is very few but Query Execution time is very High.

Today I find in my database one query is taking long time to execute, after the investigation I have seen Plan Cost is very low but it's taking huge time to execute.

Problem : In table there are two index both are composite Index. One is composite with two column in which value of one column in only one distinct value within 32 Million and another column contain null value in more then 29 Millions row for that Cardinality was more then 29 Millions and it takes too much long time to execute.

Solution: I have remove this index and Plan taking another index to Execute the Query.

Resolve Archivelog gaps in Data Guard.

In my case one of archive log file are not transfer from primary database to standby database for that standby database fall into archivelog gap.

Solution : I have found missed archivelog file in my primary database (If file are not find then need to take rman backup using the current scn number of standby and applied into standby database) and I have transferred it to standby database, but standby database are not able to resolve this gap for that I have registered this archivelog file using the alter database register logfile 'location of missed archivedlog file'. 


Error from alert log file of standby :

CORRUPTION DETECTED: In redo blocks starting at block 444418count 2048 for thread 1 sequence 1497
RFS[1687]: Possible network disconnect with primary database
Sun Sep 13 21:25:19 2015
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 1497-1497
 DBID 2276360779 branch 885908186
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.

DRHOST1$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 13 21:40:37 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter database register logfile '/fra/archivelog/2015_09_13/o1_mf_1_1497_bzbddsjf_.arc';
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Block new session after certain number of session in single schema.

CREATE TABLE SCHEMA_NUMBER_OF_SESSION
(
   USER_NAME           VARCHAR2 (100),
   NUMBER_OF_SESSION   NUMBER (10),
   ALLOWED_SESSION  NUMBER (10)
);

CREATE OR REPLACE TRIGGER TR_LOGON_CHECK
   AFTER LOGON
   ON SCHEMA_NAME.SCHEMA
DECLARE
   V_NUMBER_OF_USER    NUMBER;
   V_ALLOWED_SESSION   NUMBER;
BEGIN
   SELECT NUMBER_OF_SESSION, ALLOWED_SESSION
     INTO V_NUMBER_OF_USER, V_ALLOWED_SESSION
     FROM SCHEMA_NUMBER_OF_SESSION
    WHERE USER_NAME = USER;

   IF V_NUMBER_OF_USER >= V_ALLOWED_SESSION
   THEN
      RAISE_APPLICATION_ERROR (-20100, 'New Session are not allowed.');
   ELSE
      UPDATE SCHEMA_NUMBER_OF_SESSION
         SET NUMBER_OF_SESSION = NUMBER_OF_SESSION + 1
       WHERE USER_NAME = USER;
   END IF;
 
END TR_LOGON_CHECK;


CREATE OR REPLACE TRIGGER TR_LOGOFF_UPDATE
   BEFORE LOGOFF
   ON SCHEMA_NAME.SCHEMA
DECLARE
   V_NUMBER_OF_USER    NUMBER;
   V_ALLOWED_SESSION   NUMBER;
BEGIN
   UPDATE SCHEMA_NUMBER_OF_SESSION
      SET NUMBER_OF_SESSION = NUMBER_OF_SESSION - 1
    WHERE USER_NAME = USER;
END TR_LOGOFF_UPDATE;