The oracle database table space is not enough, want to according to delete add constraints to delete some data, but everyone knows the delete will not release the table space, what good method? Import and export of don't feel real, one is that the data is too large, the second is system run in real time, also must have influence, the delete operation I've been doing, then millions of millions of delete records, but the space is not released,

CodePudding user response:

1, partial deletion submitted
2, do table compression
3, the log switch
4, the whole table or press the partition to delete, don't use the delete, truncate

CodePudding user response:

The system did not run all day of the rest of the space can be used?
Best method is to add a disk, the new data file

CodePudding user response:

 
- after 10 g, can shrink table, note: after the shrinking, manually collecting statistical information 
- give you write a contract table example 
Connected to the Oracle Database 11 g Enterprise Edition Release 11.2.0.1.0 
Connected as oracle @ ORALAB 

SQL> 
SQL> Create table test (int id, name char (1000)); 
The Table created 
SQL> Insert into test select rownum, rownum from dual connect by rownum & lt;=10000; 
10000 rows inserted 
SQL> - the initial size of the table 
SQL> The select bytes/1024/1024 from user_segments where segment_name='TEST'; 
BYTES/1024/1024 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
12 
SQL> - after deleting data, table size 
SQL> Delete the test where the mod (id, 5) & gt; 1; 
6000 rows does 
SQL> The select bytes/1024/1024 from user_segments where segment_name='TEST'; 
BYTES/1024/1024 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
12 
SQL> The alter table test enable row movement; 
Table altered 
SQL> The alter table test the shrink space; 
Table altered 
SQL> - contraction after the table, the table size 
SQL> The select bytes/1024/1024 from user_segments where segment_name='TEST'; 
BYTES/1024/1024 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
4.625 
SQL> Drop table test purge; 
Table dropped 

SQL> 

CodePudding user response:

1, increase the space
2, table space migration
3, table compression
4, operating on the production library these contents are offline business, best backup

CodePudding user response:

Delete is not high water level of three floor gives you methods
But as a production base you this way is impossible to long-term
Add dish!

CodePudding user response:

This is bad, make you add disk calculate, or we'll accept a fragment, too much trouble!