Tablespace resize

[Former Member]

Dear All,

We have copies PRD clients to our test system 3 times. We have created different client each time.

As tablespace's segment space allocation is AUTO, tablespace size is increased.

Now 2 out of 3 clients are not required so I have deleted.

Now is there any process thru which I can deallocate extents so resize tablespace.

Regards,

Nirav

[Former Member]

Hi Nirav,

In order to reclaim the space to OS, you should reogranize the tablespace. You can find the information on the Note 646681 - Reorganizing tables with BRSPACE.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkum,

Thanks for you reply.

I have run BRSPACE for tablespace PSAPSR3.

Now the free space is 6GB.

But still I am not able to decrease the size of datafile.

I have check free space in each datafile of PSAPSR3 in dba_free_space view.

But from SQL level I am not able to decrease even 10m of size.

Thanks and Regards,

Nirav

[Former Member]

Hi Nirav,

>> Now the free space is 6GB.

What about the previous free space, before the reorganization?

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

Thanks for quick reply.

It was 15 mb.

Regards,

Nirav

[Former Member]

Hi Nirav,

At this stage, I recommend you apply the steps in the 646681 - Reorganizing tables with BRSPACE, title "Remark 14". I highly recommend you to have an backup before the operation. During the operation, you will create an auxiliary tablespace in order to store existing data. After the respective operation, you need to drop old tablespace which will drop datafiles, as well. Then, you'll create a new tablespace with the enough freespace and import exported data from aux ts to the new one. At the next step, you will drop aux ts. At the end of this operation, you will have a new tablespace and recover the space to the OS. It is important that you should be careful when you are configuring new tablespace such as datafile size, max size.

As I mentioned above, you can find the procedure in the note.

I hope that it is clear.

Best regards,

Orkun Gedik

[Former Member]

Dear Orkun,

Sorry for late reply.

As I want to reclaim physical space I have started activity on test machine. After sucessful complition I will start on QAS i.e. quality server. I have restore backup of my quality server to test machine.

I have done a below steps of activity (as per note 646681 - point number 14):

1. create a auxiliary TS :

brspace -u / -f tscreate -t PSAPSR3 -s 100 -a yes -m 1024 -i 10 -d both -l PSAPSR3_AUX

2. Using following command move follwing tables which are belonging to PSAPSR3 to PSAPSR3_AUX

SDBAH,SDBAD,DBAML,DBATL,MLICHECK,TGORA,IGORA,TSORA,TAORA,

IAORA,SVERS,DD02L,DD09L,DDNTT,DDART,DARTT or SAPLIKEY

brspace -u / -f tbreorg -t <table_list> -n PSAPSR3_AUX -- from above list which are from PSAPSR3 TS.

3. Export all tables from PSAPSR3.

brspace -u / -f tbexport -s PSAPSR3 -t "*" -u /dumpdir

There are two files created in /dumpdir/segyshgg.edd

total size of both the files are arount 36 gb

4. Delete the TS PSAPSR3

brspace -u / -f tsdrop -t PSAPSR3 -f

5. create tablespace again with following command

brspace -u / -f tscreate -t PSAPSR3 -l PSAPSR3_AUX

6. Import the data

brspace -u / -f tbimport

It throws the error relating to allocate extents. So I have resize the PSAPSR3 tablespace by adding datafiles. Now the tablespace size

is 74GB.

After that I have again run the following command

brspace -u / -f tbimport

Now import started since last 11 hours. I have started yesteday night. Today morning when I check on my screen,

import output shows the following on the screen. I think import will be failed.

Because after thease many hours my tablespace size is as it is. I have created with 74 GB and at present free space is 72 GB.

************************************************************************

Error of import

************************************************************************

ora-00001IMP-00019: row rejected due to ORACLE error 1

IMP-00003: ORACLE error 1 encountered

ORA-00001: unique constraint (SAPSR3./BEV3/CHCPT_F~0) violated

Column : 382

Column : 0001

Column : 0A

Column : CHRA

Column : 100062

Column : TMANFORD

Column :

Column : /BEV3/CHABRSTZ_P

Column : BUCHDT_AB

Column : O

IMP-00019: row rejected due to ORACLE error 1

IMP-00003: ORACLE error 1 encountered

ORA-00001: unique constraint (SAPSR3./BEV3/CHCPT_F~0) violated

Column : 382

Column : 0001

Column : 0A

Column : CHRA

Column : 100062

Column : TMANFORD

Column :

Column : /BEV3/CHABRSTZ_P

Column : BUCHDT_BIS

Column : O

IMP-00019: row rejected due to ORACLE error 1

IMP-00003: ORACLE error 1 encountered

ORA-00001: unique constraint (SAPSR3./BEV3/CHCPT_F~0) violated

Column : 382

Column : 0001

Column : 0A

Column : CHV1

Column : 100720

Column : TMANFORD

Column :

Column : /BEV3/CHABRSTZ_P

Column : BRCHDT_AB

Column : O

IMP-00019: row rejected due to ORACLE error 1

IMP-00003: ORACLE error 1 encountered

ORA-00001: unique constraint (SAPSR3./BEV3/CHCPT_F~0) violated

************************************************************************

Regards,

Nirav

[Former Member]

Hi Nirav,

It would be good to patch the brtools at the site. Then evaluate the options step by step.

Best regards,

Orkun Gedik

[Former Member]

Thanks for your reply.

brspace version is BRSPACE 7.00 (24).

My kernel patch is 291.

Is it required to patch?

regards,

Nirav

[Former Member]

Hi,

>> brspace version is BRSPACE 7.00 (24).

Latest version is 52. It would be better to download first. Then, check the other possibilities.

Best regards,

Orkun Gedik

[former_member213250]

Hi

I would say, to refer to SAP Note : 12471 for BRTOOLs version.

Better be on BRTOOLs 7.20 version

Regards

Venkat

[Former Member]

Dear Orkun,

Thanks,

I have done this activity with version 52.

Lastly I have received message that import is complited with warnings. There are certain Ztables for which import gives "already exist" message.

Is there any way to come to know that all the objects realavant to SAP standard and Z tables are imported successfuly.

Thanks and Regards,

Nirav