ORA-01466: unable to read data - table definition has changed

ORA-01466: unable to read data - table definition has changed

01466, 00000, "unable to read data - table definition has changed"
// *Cause: Query parsed after tbl (or index) change, and executed
//         w/old snapshot
// *Action: commit (or rollback) transaction, and re-execute

While selecing a table for a specific point of time i faced the error.

15:23:07 SQL> SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS')
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


Reason:- There could be few reasons behind it.
1. DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for
the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs.

2.You need to have the time of your client (where you run sqlplus) set to a later (or same) value than the time of your database server.Else such error could generate.
3. This could be caused by a long running snapshot. Try committing or rolling-back all outstanding transactions and try again.
4.It also could happen if the table is newly created .


Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.

expdp throwing ORA-04031 (stream pool) error

Summary:-
Today while taking backup of a schema i faced a problem in expdp and the job was not done
successfully.Here is the error
UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj#,type#,ctime,mtim...","SQLA","tmp")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

Solution:-
Datapump jobs (either expdp/impdp), when initiated, will use buffer queues in order to
transfer the data directly from the master table to the dump file. Instead of creating new
buffer queue, datapump operations will try to use the existing queues in stream pool memory
area.

So i increased the memory_max_size and resolved the error.
Previously it was having sga_max_target=396M and hence was not under Automatic memory management
feature(11g).
so i created a pfile from the spfile.
And change the value in pfile like this
SQL> create pfile from spfile;
$ vi initprim.ora
#*.sga_max_size=629145600
*.memory_target=629145600


#Here we enabled memory_target parameter by removing sga_max_size from the pfile.This will let
oracle automatically tune the sga and pga size.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
SQL> alter database open;
SQL> create spfile from pfile;

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.
Action: Try the command after a few minutes or enter the command without
the NOWAIT keyword.


Example:
SQL> alter table emp add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


How to avoid the ORA-00054:
    - Execute DDL at off-peak hours, when database is idle.
    - Execute DDL in maintenance window.
    - Find and Kill the session that is preventing the exclusive lock.


Other Solutions:

Solution 1:
In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to
become available, simply specify how long you would like it to wait:

SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 3 (for 10g):
DECLARE
 MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))';
 IN_USE_EXCEPTION EXCEPTION;
 PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);
BEGIN
 WHILE TRUE LOOP
  BEGIN
   EXECUTE IMMEDIATE MYSQL;
   EXIT;
  EXCEPTION
   WHEN IN_USE_EXCEPTION THEN
    NULL;
  END;
  DBMS_LOCK.SLEEP(1);
 END LOOP;
END;


Solution 4:

Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';


Step 2: kill that session using
alter system kill session 'sid,serial#' immediate;

ORA-00027 cannot kill current session

How to kill session:
1. identify which session to kill using following query:
SQL>  select
  s.sid,
  s.serial#,
  spid,
  trim(s.machine) machine,
  trim(s.module) module,
  status
 from
  v$session s,
  v$process p
 where
  paddr=addr
  and module is not null
 order by 1,2

      SID    SERIAL# SPID                 MACHINE              MODULE                                           STATUS
---------- ---------- -------------------- -------------------- ------------------------------------------------ --------------------
         1          5 2311                 server1.soumya.com   sqlplus@server1.soumya.com (TNS V1-V3)           ACTIVE
        29          1 2323                 server1.soumya.com   STREAMS                                          ACTIVE
        35         14 2457                 server1.soumya.com   sqlplus@server1.soumya.com (TNS V1-V3)           INACTIVE
        37         16 2450                 server1.soumya.com   KTSJ                                             ACTIVE
        40          2 2359                 server1.soumya.com   STREAMS                                          ACTIVE
        44         27 2448                 server1.soumya.com   KTSJ                                             ACTIVE
        47          2 2361                 server1.soumya.com   STREAMS                                          ACTIVE

2. Killing a session
SQL> ALTER SYSTEM KILL SESSION '35,14' immediate;

System altered.

ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

How to increase PROCESSES initialization parameter:

1.Login as sysdba
sqlplus / as sysdba

2. Check Current Setting of Parameters:-

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     247
shared_server_sessions               integer


SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150

SQL> show parameter transactions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
transactions                         integer     271
transactions_per_rollback_segment    integer     5

3.If you are planning to increase "sessions" parameter you should also plan to increase
"processes and "transactions" parameters.

A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1

4. These paramters can't be modified in memory. You have to modify the spfile only
(scope=spfile) and restart the instance.
SQL> alter system set processes=400 scope=spfile;

System altered.

SQL> alter system set sessions=445 scope=spfile;

System altered.

SQL> alter system set transactions=490 scope=spfile;

System altered.


Done..

ORA-00000: normal, successful completion

ORA-00000: normal, successful completion

This error may occur in many circumstances you need to check environment variables:
$ORACLE_HOME
$ORACLE_BASE
$ORACLE_SID
also check /etc/hosts file it must contains correct hostname and ip.
example:

[oracle@oel6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 1 9:12:17 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-00000: normal, successful completion
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-00000: normal, successful completion


After that i checked environment variables:
[oracle@server1 ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@server1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@server1 ~]$ echo $ORACLE_SID
prim

Then i checked /etc/hosts
cat /etc/hosts
#192.168.2.102   server1.soumya.com      server1

So i found the issue. The hostname and ip was commented inside /etc/hosts file.

Then i started up the database and everything was right.
SQL> startup
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             331352056 bytes
Database Buffers           75497472 bytes
Redo Buffers                4308992 bytes
Database mounted.
Database opened.


P.S.Generally ORA-00000 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days

Cause: The user's account is about to about to expire within 7days  and the password needs
to be changed.
Action: Change the password.

Solutions:-
sqlplus soumya/soumya
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 2 08:52:52 2015
ERROR:
ORA-28002: the password will expire within 7 days
1. Simply change the password to avoid it temporary :-

SQL> password
Changing password for SOUMYA
Old password:
New password:
Retype new password:
Password changed

2. Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently:-

SQL> SELECT PROFILE FROM dba_users WHERE username = 'SOUMYA';

PROFILE
------------------------------
DEFAULT

SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------

60


SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.


SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------
UNLIMITED

You may notice that even after setting the password expiry to unlimited you are still getting the “ERROR: ORA-28002: the password will expire” message. I’m guessing this is due to additional processes that Oracle does in the background for checking password age etc. So this forces us to “reset” the password to it’s current value to remove the error.

SQL> alter user SYSTEM identified by "password";(PASSWORD OF SOUMYA)

User altered.

ORA-00845: MEMORY_TARGET not supported on this system

ORA-00845: MEMORY_TARGET not supported on this system
Problem Description:-
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occurs whenever you try to start your database using startup command, shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

olution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or df -Th
The output should be similar like
$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             8.7G  4.7G  3.6G  57% /
shmfs                 1G    512M 512M  50% /dev/shm


We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=20g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:
vi /etc/fstab
shmfs /dev/shm tmpfs size=20g 0 0

:wq

#alter system set memory_target=20G scope=spfile;
and then bounce the database to affect the changes.