How to delete invalid users with large space under database users

This article introduces how to delete invalid users who occupy a large amount of space under the database users, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. View the corresponding size of the tablespace under the database

SELECT dbf.tablespace_name

Dbf.totalspace "Total amount (M)"

Total number of blocks of dbf.totalblocks AS bai

Dfs.freespace "Total surplus (M)"

Dfs.freeblocks "number of blocks remaining"

(dfs.freespace / dbf.totalspace) * 100 "idle du percentage"

FROM (SELECT t.tablespace_name

SUM (t.bytes) / 1024 / 1024 totalspace

SUM (t.blocks) totalblocks

FROM dba_data_files t

GROUP BY t.tablespace_name) dbf

(SELECT tt.tablespace_name

SUM (tt.bytes) / 1024 / 1024 freespace

SUM (tt.blocks) freeblocks

FROM dba_free_space tt

GROUP BY tt.tablespace_name) dfs

WHERE TRIM (dbf.tablespace_name) = TRIM (dfs.tablespace_name)

two。 View users under each tablespace

Select distinct owner from dba_segments where tablespace_name = 'USERS'

3. Delete invalid user free space

Drop user TOPSEC_XIAN001 CASCADE

On how to delete database users occupy a large amount of space under the invalid users to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.