AskTom

SQL> EXEC DBMS_REDEFINITION.can_redef_table(’admin’, ‘y’);
BEGIN DBMS_REDEFINITION.can_redef_table(’admin’, ‘y’); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table “admin”.”y” with no primary
key
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 139
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1770
ORA-06512: at line 1

Elapsed: 00:00:00.03

ORA-12089: cannot online redefine table “string”.”string” with no primary key
Cause: An attempt was made to online redefine a table that does not have a primary key defined on it.
Action: Do not attempt to online redefine a table that does not have a primary key defined on it.

Solution:

drop table x;
drop table y;

create table x(id number, name varchar2(20) encrypt);
insert into x values(1,’x');
commit;

alter table x add constraint pk_id primary key(id);

create table Y as
select *
from X where 1=2;

alter table y modify (name encrypt using ‘aes256′);

EXEC DBMS_REDEFINITION.can_redef_table(’admin’, ‘x’);

EXEC DBMS_REDEFINITION.start_redef_table(’admin’, ‘x’, ‘y’);

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
‘ADMIN’,'X’,'Y’,DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/

EXEC DBMS_REDEFINITION.sync_interim_table(’admin’, ‘x’, ‘y’);

EXEC DBMS_REDEFINITION.finish_redef_table(’admin’, ‘x’, ‘y’);

Now, everything is ok:

SQL> select constraint_name from user_constraints where table_name=’X';
CONSTRAINT_NAME
——————————
PK_ID

SQL> select constraint_name from user_constraints where table_name=’Y';
CONSTRAINT_NAME
——————————
TMP$$_PK_ID0

Regards,

Tom

ORA-25153: Temporary Tablespace is Empty

Cause: An attempt was made to use space in a temporary tablespace with no files.
Action: Add files to the tablespace using ADD TEMPFILE command.

If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty.
Use the following statement to add a TEMPFILE to a temporary tablespace:

SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/oradata/temp03.dbf’ SIZE 100M;

Regards,

Tom

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 MEMMORY_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.

Solution
Make sure /dev/shm is properly mounted. You can see it by,
df -h
The output should be similar like

$ df -k
Filesystem            Size  Used Avail Use% Mounted on
…
shmfs                 3G    1000M 1000M  50% /dev/shm

We see here for /dev/shm we have assigned 3G memory. Now if you set MEMORY_TARGET more than 3G then above ORA-845 will arise.
For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 3G then you can mount shared memory to 3G like below.

As a root user,
# mount -t tmpfs shmfs -o size=3g /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:

shmfs /dev/shm tmpfs size=3g 0

Regards,

Tom