Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL (Oracle)
PL/SQL [message #454436] Wed, 05 May 2010 10:18 Go to next message
indupriyav
Messages: 10
Registered: April 2010
Junior Member
Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.

In my project every table has an associated MLOG$ table for it.

For eg.

CREATE TABLE MLOG$_TEST
(
ID VARCHAR2(64 BYTE),
SNAPTIME$$ DATE,
DMLTYPE$$ VARCHAR2(1 BYTE),
OLD_NEW$$ VARCHAR2(1 BYTE),
CHANGE_VECTOR$$ RAW(255)
)

So is MLOG$_TEST oracle's internal table.

Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.

So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesnt free much space.

If this is oracle internal table is it advisable to delete from MLOG$ tables too.

My oracle database is mounted on the Linux server.

If I delete bulk records in tables from which path I can see how much space is freed.

Thanks!!!

Report message to a moderator

Re: PL/SQL [message #454437 is a reply to message #454436] Wed, 05 May 2010 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
So deleting old records in a database doesnt free much space.

DELETE does not return space to tablespace, it keeps it inside the table block for reuse.

Quote:
If this is oracle internal table is it advisable to delete from MLOG$ tables too.

Surely not, let Oracle manages the mview logs (which are these kind of tables) when mviews are refreshed.

Quote:
If I delete bulk records in tables from which path I can see how much space is freed.

Use DBMS_SPACE.SPACE_USAGE procedure before and after to compare.

Regards
Michel

Report message to a moderator

Re: PL/SQL [message #454439 is a reply to message #454436] Wed, 05 May 2010 10:24 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Suggest you read up on materialized view logs (and materialized views generally) in the documentation.

Report message to a moderator

Re: PL/SQL [message #454489 is a reply to message #454437] Thu, 06 May 2010 01:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
DELETE does not return space to tablespace, it keeps it inside the table block for reuse.


Didn't know this.
Thanks Michel sir.

regards,
Delna

Report message to a moderator

Re: PL/SQL [message #454588 is a reply to message #454436] Thu, 06 May 2010 06:38 Go to previous messageGo to next message
indupriyav
Messages: 10
Registered: April 2010
Junior Member
Hi Michael,

I deleted old data in my database. There was a concern by a user that the space in the table did'nt decrease instead increased. He checked that in Toad-Database-Admin-Tablespace.

So I believe per your statement there wont be change in tablespace.

We thought an entry of deleted records being made in MLOG$ wont free up much space.

Generally I was asked to disable the MLOG$ during deletion of old records. How can I do that?

I have experince in mview created instead of normal views. But the MLOG$ associated with each table seemed to be different.

Thanks,
Priya

Report message to a moderator

Re: PL/SQL [message #454589 is a reply to message #454436] Thu, 06 May 2010 06:40 Go to previous messageGo to next message
indupriyav
Messages: 10
Registered: April 2010
Junior Member
DBMS_SPACE.SPACE_USAGE is expecting a lot of parameters.

Report message to a moderator

Re: PL/SQL [message #454592 is a reply to message #454589] Thu, 06 May 2010 06:51 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
indupriyav wrote on Thu, 06 May 2010 12:40
DBMS_SPACE.SPACE_USAGE is expecting a lot of parameters.


So?

Report message to a moderator

Re: PL/SQL [message #454597 is a reply to message #454589] Thu, 06 May 2010 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe a link to the documentation will help you:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#CACBDJIH

Regards
Michel

Report message to a moderator

Re: PL/SQL [message #454599 is a reply to message #454597] Thu, 06 May 2010 07:30 Go to previous messageGo to next message
indupriyav
Messages: 10
Registered: April 2010
Junior Member
the link http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#CACBDJIH seems to be easy to undertand.

Anything on how to disable a Mview.

Report message to a moderator

Re: PL/SQL [message #454604 is a reply to message #454599] Thu, 06 May 2010 07:58 Go to previous message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What do you mean by "disable"?
Maybe Database SQL Reference will help you (only if you read it).

Regards
Michel

Report message to a moderator

Previous Topic: date to number conversion
Next Topic: difference between sybase raise error and rasie_application_error in oracle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Apr 19 01:45:43 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.