Create MVs via DB links

[julian]

I have an interesting problem. I did spend some hours searching for help on Metalink, in vain.

I have many MVs in an 8.1.7 database fast refreshed from another 8.1.7 database (on another node). The process has been working fine for almost half a year.

Since last week on all 4 machines (DEV, TEST, QA and PROD) we get the same error when we try to recreate some of the views:

Look at the following:

Starting the session:

SQL> connect nokia/****@qa_ecare;
Connected.
SQL> create snapshot ALARM
2 PCTFREE 10 PCTUSED 40 MAXTRANS 255
3 STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
5 TABLESPACE info_data
6 BUILD IMMEDIATE
7 USING INDEX TABLESPACE info_index
8 PCTFREE 10 INITRANS 2 MAXTRANS 255
9 STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
10 FREELISTS 1 FREELIST GROUPS 1)
11 REFRESH fast WITH PRIMARY KEY
12 START WITH trunc(sysdate) + 05/24
13 NEXT trunc(sysdate+1) + 05/24
14 AS
15 SELECT * FROM portal.ALARM@info_essadb2 WHERE STATE='APPROVED' and PRODUCT in (720);

Materialized view created.

So far so good.

Now:

SQL> drop materialized view alarm;

Materialized view dropped.

SQL> create snapshot ALARM
2 PCTFREE 10 PCTUSED 40 MAXTRANS 255
3 STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
5 TABLESPACE info_data
6 BUILD IMMEDIATE
7 USING INDEX TABLESPACE info_index
8 PCTFREE 10 INITRANS 2 MAXTRANS 255
9 STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
10 FREELISTS 1 FREELIST GROUPS 1)
11 REFRESH fast WITH PRIMARY KEY
12 START WITH trunc(sysdate) + 05/24
13 NEXT trunc(sysdate+1) + 05/24
14 AS
15 SELECT * FROM portal.ALARM@info_essadb2 WHERE STATE='APPROVED' and PRODUCT in (720);
SELECT * FROM portal.ALARM@info_essadb2 WHERE STATE='APPROVED' and PRODUCT in (720)
*
ERROR at line 15:
ORA-04030: out of process memory when trying to allocate bytes (,)
ORA-02063: preceding line from INFO_ESSADB2

I have no explanation yet for this.

When I run the create MV statements for small tables then all is fine. The problem comes when there is more data in the tables. The one in questions contains CLOBs but if I limit the where conditions to fetch few rows, I can create the MV.

I have no access to the physical machine on the master node but I can access the DB there.

Have you ever hot such a problem?

[rohitsn]

Hi,
Check the ulimit on the O/S .Give the following on the Unix prompt to check the resources.
#ulimit -a

Increase all the resources to unlimited and then try it out.


Regards,
Rohit Nirkhe,oracle/Apps DBA,OCP 8i
oracle-support@indiatimes.com

[julian]

Originally posted by rohitsn
Hi,
Check the ulimit on the O/S .Give the following on the Unix prompt to check the resources.
#ulimit -a

Increase all the resources to unlimited and then try it out.


Regards,
Rohit Nirkhe,oracle/Apps DBA,OCP 8i
oracle-support@indiatimes.com

Thanks a lot! I have no access to the physical machine on the master node but I will ask the UNIX admin there to do that. Why do you think it is up to ulimit?

[Orca777]

Hi,
I got out of memory-errors when i used high sort_area_size with parallel query.
You said that all four databaes have this problem?
Could it be that there was applied a patch?
Not all patches for Orcale-server increase the Oracle-Version-number.

If you use 9i probably your pga_...target.. will be to small.
ulimit for oracle-user should be for the memory unlimited.
Orca

[julian]

Thanks, we set to unlimited, same thing. Metalink Support says that's an 8.1.7.4.0 bug and there is no patch for that.