Table space full

[Former Member]

Hi,

In BI 7, DB02 Tablespace overview shows PSAPSR3 %90 Full

I Deleted data from DSO but still shows same %90 full...

why its not efect even I remove lot of data from DSO

Even I treid DB02OLD and refresh

any idea ?

[Former Member]

Hi,

Yes ofcourse, OS space is countable.

As already mentioned, Please check with ur BI guy for knowing about PSA tables(if u delete old PSA than u will get much space ) history and check the size of the tables.

2nd for re organization of the database is very important task of the DB.

please cehck the below notes. : 646681 - Reorganizing tables with BRSPACE, 541538 - FAQ Reorganization.

I have done above thing to get more space on my BI system as still i dont have the space in OS level.

With Regards,

CSK

[Former Member]

Hi,

From a DBA perspective ...

There is a highwater mark that the database keep tracks of, this does not move if you do a delete, it will only move if you do a truncate of tables or do export/import. This is the 90% that you are seeing.

If you are in Oracle10g and above, you can try and purge the recycle bin, if you do not know how to do this, ask your Oracle DBA to do so or the DBA can execute the command to shrink the database object.

These options are not available in BR*Tools

[Former Member]

Use dba_free_space data dictionary view to make out the free space available withing the tablespace.

Once u delete huge amount of data using delete command.

Do execute alter table <name> move; this will reorg anize the table and help in reclaiming space.

But remember once u do the above the indexes are based on rowids and rowids since changed because of move will invalidate or make the indexes unusable so make sure u rebuild all the indexes of the said table

alter index <name> rebuild online;

Regards

Kausik

[Former Member]

Hi,

PSAPSR3 got 90% full means- you can increase the size bye 2 ways,

1. Add datafiles through BRTOOLS/ BASIS admin need to do.

2.in BI u can find the PSA tables(normally raw data). this data(old) need to be deleted.

check with BI guys, they will give the details of the PSA tables and u / BASIS can identify the size of the data.

same can be deleted by after getting businees approval.

another option is re organizing the tables.

I hope this will help you.

With Regards,

CSK

[Former Member]

thanks you so much CSK,

I personally check all PSA temp. table, all of them empty..

I know I can increase table space but I have to look at OS leve space too... I do n't have much space in OS level...

and what does it mean re-originazed and how ?

[markus_doehr2]

What do you see if you use brtools?

The allocated space in the tablespaces is not reduced, means, the files on OS level won't decrease.

Markus

[rakesh_ranjan]

Hi,

An extent (space on disk) is allocated to a table or index until you

- drop the table

- truncate the table

Deleting the data (rows) from the tables does not free up those blocks from table but just put those empty blocks into free list so that they can be used for insert/update into that table in future. Delete does not alter the high water mark. if you do full table scan (select * from table <table_name>;) it will scan all those blocks even though most of these blocks are empty.

Thats why you are still seeing 90% used.

In order reclaim the free up space after massive, apart from droping/truncating the table you have other options like Shrink or Move. I would suggest if table size is small you use create a copy of the table (Table1_copy) with usable data (data not to be deleted) and truncate the original table (table1) and re-insert the data into original table (table1) from copy of table (table1_copy) .

1. CREATE TABLE Table1_Copy as SELECT * from Table1 where condition1;

2. TRUNCATE Table1;

3. INSERT INTO Table1 SELECT * from Table1_Copy

4. DROP TABLE Table1_Copy

Make sure you rebuild the indexes after this.

Regards,

Rakesh

[Former Member]

thanks you so much for detail information....

but PSAPSR3 is table name space, there are so many tables are included this PSAPSR3, how do move all data ?

about index thats true it will full all blocks...

what is the easy way to indexes tables... I was trying through DB02OLD is that ok ?

please verify

[former_member204746]

always check through BRSPACE. do not fully really on DB02... especially for real-time data.

to move data, check SAP note 646681.