Category: SQL | PLSQL
ORA-04091: Table is Mutating, Trigger/Function may not see it
No!
This is not a super-table nor a x-table (X-Men joke, this was awfull, I know… I’m sorry).
ORA-04091: Table "TABLE NAME" is Mutating, Trigger/Function may not see it ORA-06512: em "TRC_INSERT_TABLE", line 14 ORA-04088: error during execution of trigger 'TRC_INSERT_TABLE'
Very interesting. But not hard to understand. The cause is that the trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
In other words, your trying to read a data the you are modifying. The obviously cause an inconsistency, the reason to this error. The data is “mutant”. But the error could be less annoying, right? Oracle and his jokes…
The solution is to rewrite the trigger to not use the table, or, in some situation, you can use an autonomous transaction, to turn it independent. It can be done using the clause PRAGMA AUTONOMOUS_TRANSACTION.
This FAQ can be useful to you: http://www.orafaq.com/wiki/Autonomous_transaction
Matheus.
Purge SYSAUX Tablespace
Your SYSAUX is bigger than the rest of database?
It’s not uncommon to “old” databases, usually bad administrated. Some databases configuration must cause this situation.
The general indication is to review stats and reports retention of objects and database.
But if you need to clean it now, how to do?
1) PURGE_STATS. It’s recommended to execute in smaller steps. Otherwise the RBS tablespace will be blown up.
2) Oracle is sometimes building new extents for SYSAUX stats table in other tablespaces. They will be moved back to the SYSAUX tablespace.
3) The Index rebuild will decrease the size of the indexes. They are mostly larger as the raw data.
4) The Indexes are partly function bases. Therefore it is imported in which order the index rebuild will be done. Otherwise you have to reexecute this steps again and again.
Going practical, I used the follow:
exec DBMS_STATS.PURGE_STATS(SYSDATE-180); exec DBMS_STATS.PURGE_STATS(SYSDATE-160); exec DBMS_STATS.PURGE_STATS(SYSDATE-140); exec DBMS_STATS.PURGE_STATS(SYSDATE-120); exec DBMS_STATS.PURGE_STATS(SYSDATE-100); exec DBMS_STATS.PURGE_STATS(SYSDATE-80); exec DBMS_STATS.PURGE_STATS(SYSDATE-60); exec DBMS_STATS.PURGE_STATS(SYSDATE-40); exec DBMS_STATS.PURGE_STATS(SYSDATE-20); exec DBMS_STATS.PURGE_STATS(SYSDATE-7); alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux; alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux; alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux; alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux; alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux; alter table WRI$_OPTSTAT_OPR move tablespace sysaux; alter table WRH$_OPTIMIZER_ENV move tablespace sysaux; Alter index SYS.I_WRI$_OPTSTAT_IND_ST rebuild TABLESPACE SYSAUX; Alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild TABLESPACE SYSAUX; Alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild TABLESPACE SYSAUX; Alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild TABLESPACE SYSAUX; Alter index SYS.I_WRI$_OPTSTAT_TAB_ST rebuild TABLESPACE SYSAUX; Alter index SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild TABLESPACE SYSAUX; Alter index SYS.I_WRI$_OPTSTAT_OPR_STIME rebuild TABLESPACE SYSAUX;
Matheus.
CREATE MVIEW: ORA-12014: table does not contain a primary key constraint
Ok, you are trying to create a materialized view involving a database link and found a ORA-12014, right?
CREATE MATERIALIZED VIEW &OWNER..MVW_NAME REFRESH FORCE ON DEMAND AS SELECT COL1, COL2, COL3 FROM TABLE@REMOTE_DB; * ERROR at line 1: ORA-12014: table 'TABLE' does not contain a primary key constraint SQL>
It blowed me sometime ago. But it’s not complicated to workaround it, just try to:
CREATE MATERIALIZED VIEW &OWNER..MVW_NAME REFRESH FORCE ON DEMAND AS select * from (SELECT COL1, COL2, COL3 FROM TABLE@REMOTE_DB);
An alternative is to use MV log + WITH ROWID on REMOTE_DB side:
CREATE MATERIALIZED VIEW LOG MVW_LOG_NAME ON TABLE WITH ROWID;
And
CREATE MATERIALIZED VIEW &OWNER..MVW_NAME REFRESH FORCE ON DEMAND WITH ROWID AS SELECT COL1, COL2, COL3 FROM MVW_LOG_NAME@REMOTE_DB;
PS: Make sure username used in remote_db database link has select privileges on MV log. On source db issue:
SELECT LOG_TABLE FROM DBA_MVIEW_LOGS WHERE LOG_OWNER='OWNER' AND MASTER = 'TABLE';
This will give you MV log table name. On target side issue:
SELECT * FROM MVW_LOG_NAME@remote_db;
See ya!
Matheus.
Script: Copy Large Table Through DBLink
To understand the situation:
Task: Need to migrate large database 11.1.0.6 to 12c Multi-Tenant Database with minimum downtime.
To better use the features, reorginize objects and compress data, I decided to migrate the data logically (not physically).
The first option was to migrate schema by schema through datapump with database link. There is no long columns.
Problem1: The database was veeery slow with perfect match to Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp.
workaround: None
Solution: Upgrade to 11.2. (No way).
Other things: Yes, I tried to change the cursor sharing, the estimate from blocks to statistics and all things documented. It doesn’t work.
Ok doke! Let’s use traditional exp/imp tools (with some migration area), right?
Problem2: ORA-12899 on import related to multiblocking x singleblocking charsets.
Solution: https://grepora.com/2015/11/20/charsets-single-byte-vs-multibyte-issue/
Done? Not for all. For some tables, just happened the error:
EXP-00006: internal inconsistency error EXP-00000: Export terminated unsuccessfully
An what Oracle says? “Solution: Use Datapump!”
Well, well… I realized I was going to become by myself…
Ok, so lets create table as select using database link. For most of all, ok…
But, for example, one of the missing tables has 700 million rows (350GB of compressed and no partitioned data).
Just to remmember that DBLink exclude parallel options (always serial).
The solution was to make a McGayver, this way:
1) Creating an aux table (source database):
alter session force parallel query parallel 12; create table SCHEMA_OWNER.AUX_ROWID(ROW_ID,NUM) as select rowid, rownum from SCHEMA_OWNER.TABLE; alter session disable parallel query;
* This table will be used to break the table in chunks.
2) Script run_chunck.sql to run each chunk of data:
DECLARE
counter number;
CURSOR cur_data is
select row_id from (
select row_id, num from SCHEMA_OWNER.AUX_ROWID@SOURCEDB order by num)
where num >= &1
and num <=&2;
BEGIN
counter :=0;
FOR x IN cur_data LOOP
BEGIN
counter := counter +1;
insert into SCHEMA_OWNER.TABLE select * from SCHEMA_OWNER.TABLE@SOURCEDB where rowid = x.row_id;
if counter = 1000 then ---commit every 1000 rows
commit;
counter := 0;
end if;
EXCEPTION
when OTHERS then
dbms_output.put_line('Error ROW_ID: '||x.row_id||sqlerrm);
END;
END LOOP;
COMMIT;
END;
/
exit;
3) Run in a BAT or SH like (my example was made for a bat, with “chunks” of 50 million rows – and commits by every 1k, defined on item 2):
@echo off set /p db="Target Database.: " set /p user="Username.......: " set /p pass="Password..................: " pause START sqlplus %user%/%pass%@%db% @run_chunck.sql 1 2060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 2060054 52060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 52060054 102060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 102060054 152060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 152060054 202060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 202060054 252060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 252060054 302060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 302060054 352060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 352060054 402060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 402060054 452060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 452060054 502060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 502060054 552060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 552060054 602060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 602060054 652060053 START sqlplus %user%/%pass%@%db% @run_chunck.sql 652060054 702060053 -- count(*) from table
Watching the inserts running…
targetdb>@sess User:MATHEUS USERNAME EVENT SQL_ID ---------- ---------- ------------------------- MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink gt3mq5ct7mt6r MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw MATHEUS_BOESING SQL*Net message from client MATHEUS_BOESING SQL*Net message from client MATHEUS_BOESING SQL*Net message to client c7a5tcc3a84k6
After a few (26 hours) the copy was successfully concluded.
Matheus.
Charsets: Single-Byte vs Multibyte Encoding Scheme Issue
Sad history:
IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "SCHEMA"."TABLE"."COLUMN" (actual: 61, maximum: 60)
To understand: It happens when the export/import is being made by different charsets. Usually when the destination is a superset with “multibyting” and the source is a single-byte one. The reason is that as more as the charset is not specific, more bits are used to represent a charcter (c-ç, a-ã, o-õ-ô, for example), this way, the columns that uses as data length byte will be different sized between theese databases.
Of course, as more specific a charset configuration is, much better for performance constraints it’ll be (specially for sequencial reads), because the databases needs to work with less bytes in datasets/datablocks for the same tuples, in a simple way to explain. Otherside, this is a quite specific configuration. The performance issues are mostly related to more simple tunings (sql access plan, indexing, statistics or solution architecture) than this kind of details. But, it’s important to mention if you’re working in a database that is enough well tuned…
For more information, I recommend this (recent) documentation: https://docs.oracle.com/database/121/NLSPG/ch2charset.htm. Please, invest your time to understand the relation between “Single-Byte Encoding Schemes” and “Multibyte Encoding Schemes” in this doc.
The follow image ilustrates in a simple way the difference of byting used to address more characters (a characteristic of supersets):
Ok, doke!
And the solution is…
Let’s summarize the problem first: The char (char, varchar) columns uses more bytes to represent the same characters. So the situations where, in the source, the column was used by the maximum lengh, it “explodes” the column lengh in the destination database with a multibyte encoding scheme.
For consideration, I’m not using datapump (expdp/impdp or impdb with networklink) just because it’s a legacy system with long columns. Datapump doesn’t support this “deprecated” type of data.
So, my solution, for this pontual problem occouring during a migration was to change the data lengh of the char columns from “byte” to “char”. This way, the used metric is the charchain rather than bytesize. Here is my “kludge” for you:
select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' MODIFY '||COLUMN_NAME||' CHAR('||data_length||' CHAR);'
from dba_tab_cols where DATA_TYPE='CHAR' and owner='&SCHEMA'
union all
select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' MODIFY '||COLUMN_NAME||' VARCHAR2('||data_length||' CHAR);'
from dba_tab_cols
where DATA_TYPE='VARCHAR2' and owner='&SCHEMA';
And it works!
Hugs and see ya!
Matheus.
Scheduler Job by Node (RAC Database)
Sometimes you want to run something just in one node of the RAC. Here is an example to do it:
create or replace procedure USER_JOB.PRC_SOMETHING is begin -- do something null; end; /
begin sys.dbms_scheduler.create_job(job_name => 'USER_JOB.JOB_SOMETHING', job_type => 'PLSQL_BLOCK', job_action => 'USER_JOB.PRC_SOMETHING;', start_date => sysdate, repeat_interval => 'Freq=Minutely;Interval=30', end_date => to_date(null), job_class => 'DEFAULT_JOB_CLASS', enabled => true, auto_drop => false, comments => 'Something Job.'); end; /
begin dbms_scheduler.set_attribute(name => 'USER_JOB.JOB_SOMETHING', attribute=>'INSTANCE_ID', value=> 1); end; /
Matheus.
ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION
An unexpected error, right?
SQL> CREATE PROFILE TEST_PROF LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 30 PASSWORD_REUSE_MAX 15 PASSWORD_VERIFY_FUNCTION fnc_validation; CREATE PROFILE TEST_PROF LIMIT * ERROR at line 1: ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION FNC_VALIDATION
That is a simple need. You have to use 3 parameters on function: username varchar2, password varchar2, old_password varchar2.
Matheus.
Lock by DBLink – How to locate the remote session?
And if you identify a lock or other unwanted operation by a DBLink session, how to identify the original session in remote database (origin dabatase)?
The one million answer is simple: by process of v$session. By the way, looks like is easier than find the local process (spid)… Take a look in my example (scripts in the end of post):
dest> @sid Sid:10035 Inst:1 SEQ# EVENT MODULE STATUS SID SERIAL# INST_ID ----- --------- ---------- ---------- ---------- ---------- ---------- 29912 SQL*Net message from client oracle@origin2(TNS V1-V3) INACTIVE 10035 35 1 dest> @spid SPID SID PID PROCESS_FOR_DB_LINK MACHINE LOGON_TIME ------ ---------- ---------- ----------- ----------- ----------- 16188960 10035 882 17302472 origin2 24/08/2015 07:43:40
Now I know the sid 10035 refers to local process 16188960 and the process on origin database is 17302472. What I do what I want if this process:
root@origin2:/oracle/diag/rdbms/origin/origin2/trace>ps -ef |grep 17302472 grid 17302472 1 97 07:42:42 - 5:58 oracleorigin2 (LOCAL=NO) root 24445782 36700580 0 08:05:45 pts/3 0:00 grep 17302472
What include to locae the session in the database by spid, see the sql, and etecetera:
origin> @spid2 Enter value for process: 17302472 SID SERIAL# USERNAME OSUSER PROGRAM STATUS ------- ---------- ----------- ----------- --------------- ---------- 7951 41323 USER_XPTO scheduler_user sqlplus@scheduler_app.domain.net (TNS V1-V3) ACTIVE database2> @sid Sid:7951 Inst: 2 SQL_ID SEQ# EVENT MODULE STATUS SID SERIAL# INST_ID ---------- ----- --------- ------- --------- ----- ------ ---------- 1w1wz2mdunya1 56778 db file sequential read REMOTE_LOAD ACTIVE 7951 41323 2
That’s OK?
Simple isn’t?
The used Scripts (except the “sid”, that is a simple SQL on gv$session):
Get SPID and PROCESS FOR DBLINK from a SID:
# spid: col machine format a30 col process format 999999 select p.spid,b.sid, p.pid, b.process as process_for_db_link, machine, logon_time from v$session b, v$process p where b.paddr=p.addr and sid=&sid /
Get SID from SPID:
#spid2: SELECT s.sid, s.serial#, s.username, s.osuser, s.program, s.status, FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid IN (&process); /
See ya!
Matheus.
DBA_TAB_MODIFICATIONS
Do you know the view “dba_tab_modifications”?
It’s very useful to know what has changed since the last stats gathering of a table and all decision/information that comes with… See the example below..
The only need is to run “dbms_stats.flush_database_monitoring_info” before cheking… take a look:
mydb> create TABLE matheus_boesing.test (nro number);
Table created.
mydb> begin
2 for i in 1..1000 loop
3 insert into matheus_boesing.test values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected
mydb> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
---------------------- -------------- ---------- ---------- ----------
MATHEUS_BOESING test 1000 0 0
mydb> EXEC DBMS_STATS.GATHER_TABLE_STATS('MATHEUS_BOESING','test');
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected
For more information: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4149.htm
Have a nice day! 😀
Matheus.