First i checked..
https://database-heartbeat.com/2021/11/22/calc-storage-adb/
then i do
set echo on termout on trimspool on pages 1000 lines 132
col DIRECTORY_PATH format a70
col directory_name format a20
col name format a20
col file_name format a90
select name,max_size/(1024*1024*1024) "Max Size in GB",total_size/(1024*1024*1024) "Total Size in GB" from v$pdbs;
select file_name,bytes/(1024*1024*1024) "Bytes in GB", maxbytes/(1024*1024*1024) "Maxbytes in GB", autoextensible from dba_data_files;
select file_name,bytes/(1024*1024*1024) "Bytes in GB", maxbytes/(1024*1024*1024) "Maxbytes in GB", autoextensible from dba_temp_files;
select tablespace_name,owner,sum(bytes)/(1024*1024*1024) "Bytes in GB" from dba_segments where tablespace_name like 'DATA%' group by tablespace_name,owner;
select tablespace_name,owner,sum(bytes)/(1024*1024*1024) "Bytes in GB" from dba_segments where tablespace_name like 'DBFS%' group by tablespace_name,owner;
select directory_name,directory_path,owner from all_directories;
-- check files in all directories --
select object_name,bytes/(1024*1024*1024) "Bytes in GB" from dbms_cloud.list_files('LMS_EXPORT');
select object_name,bytes/(1024*1024*1024) "Bytes in GB"from dbms_cloud.list_files('DATA_EXPORT_DIR');
select object_name,bytes/(1024*1024*1024) "Bytes in GB" from dbms_cloud.list_files('SQL_TCB_DIR');
select object_name,bytes/(1024*1024*1024) "Bytes in GB" from dbms_cloud.list_files('DATA_PUMP_DIR');
select total_size, round(total_size/1024/1024/1024,2) as "Bytes in GB" from v$containers;
select total_size/1024/1024/1024 "Bytes in GB" from v$pdbs ;
select sum(bytes/1024/1024/1024) bin_objects from dba_segments where SEGMENT_NAME like '%BIN%' ;
DBFS_DATA needs 13 Gbytes Directories are all empty..
then i read
then i do
PURGE DBA_RECYCLEBIN;
select dbms_stats.get_stats_history_retention from dual;
BEGIN dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE'); END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 0,
interval => 0
);
END;
/
SELECT MIN(SNAP_ID) AS LOWEST_SNAP_ID, MAX(SNAP_ID) AS HIGHEST_SNAP_ID
FROM DBA_HIST_SNAPSHOT;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id => 0, -- Use your actual lowest SNAP_ID
high_snap_id => 9999 -- Use your actual highest SNAP_ID
);
END;
/
begin
DBMS_WORKLOAD_REPOSITORY.PURGE_SQL_DETAILS();
end;
/
begin
DBMS_STATS.PURGE_STATS(sysdate);
end;
/
but DBFS_DATA still 13 Gbytes..
SR Created… interesting , lets see what will be..
best regards tom