Monday, July 30, 2018
Tuesday, July 3, 2018
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#';
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm
Monday, June 11, 2018
Different ways to get dumpfile version - EXPDP
Lets suppose we have a dumpfile and we have no clue from which database or atleast which version of database it is exported. (assume we don’t have a log file)
We got a request to import it to other database. As per the compatibility matrix you can import only to higher versions and for this we need to know current dumpfile version.
In this situation, the following methods will help you….
Method # 1
+++++++
For classic export dump files on Unix systems, you can use below command
$ cat <dump_file_name> | head | strings
Method # 2
+++++++
From Oracle 10g, you have another way to do this. We can use DBMS_DATAPUMP.GET_DUMPFILE_INFO package to read the dumpfile header where this information will be stored. For this we need to use pre-defined stored procedure.
Here is the procedure script and other details, refer to MOS doc 462488.1
CONNECT system/manager
CREATE OR REPLACE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir = directory object where dump file can be found
-- p_file = simple filename of export dump file (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 (etc.)
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(23) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Dump file details:
-- ==================
-- For Oracle10g Release 2 and higher:
-- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1;
-- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2;
-- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3;
-- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4;
-- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5;
-- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6;
-- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7;
-- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8;
-- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9;
-- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10;
-- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11;
-- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12;
-- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13;
-- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
-- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15;
-- For Oracle11gR1:
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16;
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
-- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18;
-- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19;
-- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20;
-- For Oracle11gR2:
-- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21;
-- dbms_datapump.KU$_DFHDR_ENCRIPTION_MODE CONSTANT NUMBER := 22;
-- For Oracle12cR1:
-- dbms_datapump.KU$_DFHDR_COMPRESSION_ALG CONSTANT NUMBER := 23;
-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22;
-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 23;
-- Show header output info:
-- ========================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 18-DEC-2013');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('. ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('. ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);
-- Retrieve Export dumpfile details:
-- =================================
SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);
dbms_datapump.get_dumpfile_info(
filename => p_file, directory => UPPER(p_dir),
info_table => v_info_table, filetype => v_filetype);
var_values.EXTEND(23);
FOR i in 1 .. 23 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype >= 1 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));
dbms_output.put_line('...Export Version................: ' || var_values(15));
ELSIF v_filetype = 1 OR v_filetype = 3 THEN
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
'4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Password and Wallet)',
'5', '5 (Wallet)',
var_values(22)) INTO var_values(22) FROM dual;
SELECT DECODE(var_values(23),
'2', '2 (None)',
'3', '3 (Basic)',
'4', '4 (Low)',
'5', '5 (Medium)',
'6', '6 (High)',
var_values(23)) INTO var_values(23) FROM dual;
IF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));
dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));
END IF;
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...Job Name......................: ' || var_values(8));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
ELSE
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (External Table dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
END IF;
dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));
dbms_output.put_line('...Max Items Code (Info Items)...: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/
Sample Output
SET serveroutput on SIZE 1000000
We got a request to import it to other database. As per the compatibility matrix you can import only to higher versions and for this we need to know current dumpfile version.
In this situation, the following methods will help you….
Method # 1
+++++++
For classic export dump files on Unix systems, you can use below command
$ cat <dump_file_name> | head | strings
Method # 2
+++++++
From Oracle 10g, you have another way to do this. We can use DBMS_DATAPUMP.GET_DUMPFILE_INFO package to read the dumpfile header where this information will be stored. For this we need to use pre-defined stored procedure.
Here is the procedure script and other details, refer to MOS doc 462488.1
CONNECT system/manager
CREATE OR REPLACE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir = directory object where dump file can be found
-- p_file = simple filename of export dump file (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 (etc.)
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(23) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Dump file details:
-- ==================
-- For Oracle10g Release 2 and higher:
-- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1;
-- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2;
-- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3;
-- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4;
-- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5;
-- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6;
-- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7;
-- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8;
-- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9;
-- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10;
-- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11;
-- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12;
-- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13;
-- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
-- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15;
-- For Oracle11gR1:
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16;
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
-- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18;
-- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19;
-- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20;
-- For Oracle11gR2:
-- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21;
-- dbms_datapump.KU$_DFHDR_ENCRIPTION_MODE CONSTANT NUMBER := 22;
-- For Oracle12cR1:
-- dbms_datapump.KU$_DFHDR_COMPRESSION_ALG CONSTANT NUMBER := 23;
-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22;
-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 23;
-- Show header output info:
-- ========================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 18-DEC-2013');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('. ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('. ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);
-- Retrieve Export dumpfile details:
-- =================================
SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);
dbms_datapump.get_dumpfile_info(
filename => p_file, directory => UPPER(p_dir),
info_table => v_info_table, filetype => v_filetype);
var_values.EXTEND(23);
FOR i in 1 .. 23 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype >= 1 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));
dbms_output.put_line('...Export Version................: ' || var_values(15));
ELSIF v_filetype = 1 OR v_filetype = 3 THEN
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
'4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Password and Wallet)',
'5', '5 (Wallet)',
var_values(22)) INTO var_values(22) FROM dual;
SELECT DECODE(var_values(23),
'2', '2 (None)',
'3', '3 (Basic)',
'4', '4 (Low)',
'5', '5 (Medium)',
'6', '6 (High)',
var_values(23)) INTO var_values(23) FROM dual;
IF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));
dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));
END IF;
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...Job Name......................: ' || var_values(8));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
ELSE
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (External Table dumpfile)');
dbms_output.put_line(v_separator);
dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
dbms_output.put_line('...Creation Date.................: ' || var_values(6));
dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
END IF;
dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
END IF;
dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
END IF;
END IF;
dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));
dbms_output.put_line('...Max Items Code (Info Items)...: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/
Sample Output
SET serveroutput on SIZE 1000000
Friday, June 8, 2018
ORA-02374, ORA-12899, ORA-02372 during Data Pump expdp/impdp from 12c WE8MSWIN1252 into 12c AL32UTF8
PROBLEM
During a data pump expdp/impdp from 12c WE8MSWIN1252 into 12c AL32UTF8, I received several errors in the data pump import log file:
07-JUN-18 17:00:12.953: ORA-02374: conversion error loading table "XXX"."ABC"
07-JUN-18 17:00:12.953: ORA-12899: value too large for column ABC_NDC (actual: 22, maximum: 11)
07-JUN-18 17:00:12.953: ORA-02372: data for row: ABC_NDC : 0X'9F9F9F9F9F9F9F9F9F9F9F'
07-JUN-18 22:46:02.663: KUP-11007: conversion error loading table "XXX"."ABC"
07-JUN-18 22:46:02.663: ORA-12899: value too large for column ABC_TOB (actual: 5, maximum: 4)
07-JUN-18 22:46:02.663: KUP-11009: data for row: ABC_TOB : 0X'303136A0'
CAUSE
The cause of these errors is related to the fact that I am migrating data from a database with a single-byte character set (WE8MSWIN1252) into one with a multi-byte character set (AL32UTF8). This means that some single-byte characters will be “expanded” into multi-byte characters, and if the column was already filled completely, ORA-12899 will be raised, showing the maximum allowed column value and the actual value needed to fit all the characters into the multi-byte column.
There are several solutions to this problem: increase the size of the source column or truncate the data before the import, stick to the same character set, pre-create the tables with modified column sizes in the source database before the import
If you have a lot of rows with conversion errors in your import log file, there’s another solution. This requires the installation of the Database Character Set Scanner utility (csscan) into your source database.
Csscan is installed by running the csminst.sql script under $ORACLE_HOME/rdbms/admin. This script will create a user “csmig”. It’s a good idea to first modify the following line so the user csmig doesn’t write into the SYSTEM tablespace:
alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
In my case, I replaced SYSTEM by SYSAUX.
Let’s install csscan into our source database:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 8 11:28:41 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> @csminst.sql
After this you should check the csminst.log file for errors.
Now that csscan is installed, you can use it to check specific schemas or tables for data conversion problems during the migration to another character set. I executed it as follows (you will need the password of the SYS user):
$ csscan \”sys as sysdba\” LOG=/tmp/csscan.log USER=XXX CAPTURE=Y TOCHAR=AL32UTF8 ARRAY=1024000 PROCESS=3
This will analyze all character data stored in the tables of the schema "VSVW2LO112_1801". The script will create 3 log files. The log file with the extension “.err” has the list of all affected rows with their corresponding ROWID’s. You can count the affected rows from the log file using the UNIX “grep” command:
$ grep -i “exceed column size” /tmp/csscan.log.err|wc -l
38
So, in my case, 38 rows from the "" schema will have conversion problems during the migration to the AL32UTF8 character set.
You can remove csscan by dropping the user “csmig”. More information regarding csscan can be found on My Oracle Support, please see document ID 1297961.1.
select VALUE, ISDEFAULT from v$parameter where NAME='nls_length_semantics';
VALUE ISDEFAULT
--------------- --------------------
BYTE TRUE
select VALUE from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
---------------
WE8MSWIN1252
During a data pump expdp/impdp from 12c WE8MSWIN1252 into 12c AL32UTF8, I received several errors in the data pump import log file:
07-JUN-18 17:00:12.953: ORA-02374: conversion error loading table "XXX"."ABC"
07-JUN-18 17:00:12.953: ORA-12899: value too large for column ABC_NDC (actual: 22, maximum: 11)
07-JUN-18 17:00:12.953: ORA-02372: data for row: ABC_NDC : 0X'9F9F9F9F9F9F9F9F9F9F9F'
07-JUN-18 22:46:02.663: KUP-11007: conversion error loading table "XXX"."ABC"
07-JUN-18 22:46:02.663: ORA-12899: value too large for column ABC_TOB (actual: 5, maximum: 4)
07-JUN-18 22:46:02.663: KUP-11009: data for row: ABC_TOB : 0X'303136A0'
CAUSE
The cause of these errors is related to the fact that I am migrating data from a database with a single-byte character set (WE8MSWIN1252) into one with a multi-byte character set (AL32UTF8). This means that some single-byte characters will be “expanded” into multi-byte characters, and if the column was already filled completely, ORA-12899 will be raised, showing the maximum allowed column value and the actual value needed to fit all the characters into the multi-byte column.
There are several solutions to this problem: increase the size of the source column or truncate the data before the import, stick to the same character set, pre-create the tables with modified column sizes in the source database before the import
If you have a lot of rows with conversion errors in your import log file, there’s another solution. This requires the installation of the Database Character Set Scanner utility (csscan) into your source database.
Csscan is installed by running the csminst.sql script under $ORACLE_HOME/rdbms/admin. This script will create a user “csmig”. It’s a good idea to first modify the following line so the user csmig doesn’t write into the SYSTEM tablespace:
alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
In my case, I replaced SYSTEM by SYSAUX.
Let’s install csscan into our source database:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 8 11:28:41 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected.
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> @csminst.sql
After this you should check the csminst.log file for errors.
Now that csscan is installed, you can use it to check specific schemas or tables for data conversion problems during the migration to another character set. I executed it as follows (you will need the password of the SYS user):
$ csscan \”sys as sysdba\” LOG=/tmp/csscan.log USER=XXX CAPTURE=Y TOCHAR=AL32UTF8 ARRAY=1024000 PROCESS=3
This will analyze all character data stored in the tables of the schema "VSVW2LO112_1801". The script will create 3 log files. The log file with the extension “.err” has the list of all affected rows with their corresponding ROWID’s. You can count the affected rows from the log file using the UNIX “grep” command:
$ grep -i “exceed column size” /tmp/csscan.log.err|wc -l
38
So, in my case, 38 rows from the "" schema will have conversion problems during the migration to the AL32UTF8 character set.
You can remove csscan by dropping the user “csmig”. More information regarding csscan can be found on My Oracle Support, please see document ID 1297961.1.
select VALUE, ISDEFAULT from v$parameter where NAME='nls_length_semantics';
VALUE ISDEFAULT
--------------- --------------------
BYTE TRUE
select VALUE from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
---------------
WE8MSWIN1252
Wednesday, June 6, 2018
DataPump Export (EXPDP) Fails With Error ORA-39826
SYMPTOM
06-JUN-18 09:27:43.735: ORA-31693: Table data object "XXX"."LOC_PK_ID" failed to load/unload and is being skipped due to error:
06-JUN-18 09:27:43.735: ORA-31693: Table data object "XXX"."LOC_PK_ID" failed to load/unload and is being skipped due to error:
x
x
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym (XXX.LOC_PK_ID) could not be resolved.
06-JUN-18 09:27:43.742: ORA-31693: Table data object "XXX"."PL_PK_ID" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym (XXX.PL_PK_ID) could not be resolved.
CAUSE
Export backup has completed and backup was validated. The below error was because of table was dropped during the run-time of EXPDP export that’s why doesn't find the table at the time of exporting that table.
SOLUTION
Please check whether the objects reported in the error message exist or not. The example below is related to the object mentioned in above error message:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STAUS, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OWNER = 'XXX' AND OBJECT_NAME IN ('LOC_PK_ID');
no row selected
If the query returns no rows, then you can expect the ORA-39826 is raised. You should make sure that the object exists when it is exported and it is not removed during the runtime of export. Dropping an object during the master control table of DataPump export is created and filled but before the actual table contents are exported, will result in these type of error.
Wednesday, October 4, 2017
How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g/12c
1. Create Temporary Tablespace Temp
create temporary tablespace temp2 tempfile '/mnt/mnt04/oradata/temp01.dbf'size 2000M;
2. Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3. Make sure No sessions are using your Old Temp (TEMP) tablespace
a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
USERNAME SESSION_NUM SESSION_ADDR
-----------------------------------------------------------------------------
SYS 45684 0000000CE2EA7CC8
b. Find Session ID from V$SESSION:
If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;
OR
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
c. Kill Session:
Provide above inputs to following query, and kill session’s.
SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';
For example:
SQL> alter system kill session '633,45684';
4. Drop TEMP tablespace
drop tablespace TEMP including contents and datafiles;
5. Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/mnt/mnt04/oradata/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
6. Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
7. Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.
create temporary tablespace temp2 tempfile '/mnt/mnt04/oradata/temp01.dbf'size 2000M;
2. Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3. Make sure No sessions are using your Old Temp (TEMP) tablespace
a. Find Session Number from V$SORT_USAGE:
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
USERNAME SESSION_NUM SESSION_ADDR
-----------------------------------------------------------------------------
SYS 45684 0000000CE2EA7CC8
b. Find Session ID from V$SESSION:
If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;
OR
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
c. Kill Session:
Provide above inputs to following query, and kill session’s.
SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';
For example:
SQL> alter system kill session '633,45684';
4. Drop TEMP tablespace
drop tablespace TEMP including contents and datafiles;
5. Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/mnt/mnt04/oradata/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
6. Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
7. Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.
Monday, October 2, 2017
Cleaning Oracle SYSAUX Tablespace Usage - SYSAUX tablespace is 97.51%
Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script
${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.
OR
select
*
from
(select
owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg
from
dba_segments
where tablespace_name = 'SYSAUX'
order by
blocks desc);
In my case, below 1 occupying most of the space :-
1. SM/AWRSM/AWR — It refers to Automatic Workload Repository.Data in this section is retained for a certain amount of time (default 8 days). Setting can be checked through DBA_HIST_WR_CONTROL.
Retrieve the oldest and latest AWR snapshot
SELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)UNIONSELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)/Now use the dbms_workload_repository package to remove the AWR snapshots.
BEGINdbms_workload_repository.drop_snapshot_range(low_snap_id => 7521, high_snap_id=>7888);END;/Speed up ‘removal’ of old AWR reports
@#$%^&*()_ removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.If none of the above suits as everything is set proper then consider clean up and rebuild AWR repository to clear all the space.SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql
Reference :-WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)
Subscribe to:
Comments (Atom)