I have a partition table under oracle11G, which occupies a space larger are two blob fields,
Partitioned by the month, usually keep two months of partitions,
Recent volume is larger, table in the table space for new data files, causing the asm disk group space, multiple alarm,
Last month, now treatment method is to remove the partition
See table space dba_free_space has extra 2 t,
But the data file how to delete? Asm disk group and left more than 300 G, there are some hidden dangers,

CodePudding user response:

Have a look at the shrink command

CodePudding user response:

Consult https://www.linuxidc.com/Linux/2017-02/141115.htm

CodePudding user response:

I this problem is not high water level problem,
Whole partition were deleted, there is no problem of high water level,
Just released after partition to delete a lot of space, due to the double 12 volume is big, so take up the space is too big,
Subsequent will not need so much space,
The asm disk group with more than 300 G, I want to change the partition table is part of the table Spaces of data file is deleted, the space back to the asm disk group,
But the alter in tablespace XXXXX drop datafile 'XXXXXXXXXX'
Error, said data file is being used, and view the segments, the data file is assigned,
But it should be empty,
How to solve the problem?

CodePudding user response:

reference weixin_37650796 reply: 3/f

I this problem is not high water level problem,
Whole partition were deleted, there is no problem of high water level,
Just released after partition to delete a lot of space, due to the double 12 volume is big, so take up the space is too big,
Subsequent will not need so much space,
The asm disk group with more than 300 G, I want to change the partition table is part of the table Spaces of data file is deleted, the space back to the asm disk group,
But the alter in tablespace XXXXX drop datafile 'XXXXXXXXXX'
Error, said data file is being used, and view the segments, the data file is assigned,
But it should be empty,
How to solve the problem?

Try data files offline

CodePudding user response:

Should truncate partition to delete seemed to immediately release the resources,
You whether these data files and other schema objects, such as the index,

CodePudding user response:

Each partition using a separate table space, should be able to direct the drop table space,

Before the next add partition, building a table space, separate for the partition, add a partition to specify the tablespace,

CodePudding user response:

Two methods
1, export data, shrinkage table data files, to import the data again,
2, create a new table space, and then move to the new table space, and then delete the original table space,

CodePudding user response:

Actually you deleted partitions, the corresponding data file will narrow