* List each individual tablespace size:<\/p>\n
\r\nSET PAGESIZE 100\r\nCOLUMN size_in_mb FORMAT 999999\r\nSELECT tablespace_name, Sum(bytes)\/1024\/1024 AS size_in_mb\r\nFROM dba_segments\r\nGROUP BY tablespace_name\r\nORDER BY tablespace_name;\r\n<\/pre>\n* List tablespace usage for each user.
\n* Set min_size_mb to limit the minimal tablespace usage to list.<\/p>\n
\r\nSET SERVEROUT ON\r\n\r\nDECLARE\r\n -- Define a record type to hold tablespace information\r\n TYPE tblspc_info_record_type IS RECORD (\r\n tblspc_name dba_segments.tablespace_name%TYPE, \r\n tblspc_size number(6));\r\n\r\n -- Define a varray type to hold tablepsaces\r\n TYPE tblspc_infos_varray_type IS VARRAY(1000) \r\n of tblspc_info_record_type;\r\n\r\n -- Define a varray type to hold user names\r\n TYPE usr_names_varray_type IS VARRAY(1000) \r\n of dba_users.username%TYPE;\r\n\r\n tblspc_infos tblspc_infos_varray_type;\r\n usr_names usr_names_varray_type;\r\n tmp_usr_name dba_users.username%TYPE;\r\n min_size_mb number(6) := 500;\r\nBEGIN\r\n SELECT username \r\n BULK COLLECT INTO usr_names\r\n FROM dba_users ORDER BY username ASC;\r\n \r\n tmp_usr_name := '';\r\n FOR i IN 1 .. usr_names.count LOOP\r\n IF tmp_usr_name != usr_names(i) THEN\r\n DBMS_OUTPUT.PUT(CHR(10));\r\n DBMS_OUTPUT.PUT_LINE('========= User name: ' \r\n || usr_names(i) || '============');\r\n END IF;\r\n tmp_usr_name := usr_names(i);\r\n\r\n SELECT tablespace_name, Sum(bytes)\/1024\/1024\r\n BULK COLLECT INTO tblspc_infos\r\n FROM dba_segments\r\n WHERE owner = Upper(usr_names(i))\r\n GROUP BY tablespace_name\r\n ORDER BY tablespace_name;\r\n\r\n FOR j IN 1 .. tblspc_infos.count LOOP\r\n IF tblspc_infos(j).tblspc_size > min_size_mb THEN\r\n DBMS_OUTPUT.PUT_LINE('Consumes tablespace ' \r\n || tblspc_infos(j).tblspc_name \r\n || ' size: '\r\n || tblspc_infos(j).tblspc_size || ' MB');\r\n END IF;\r\n END LOOP;\r\n END LOOP;\r\nEND;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"* List each individual tablespace size: SET PAGESIZE 100 COLUMN size_in_mb FORMAT 999999 SELECT tablespace_name, Sum(bytes)\/1024\/1024 AS size_in_mb FROM dba_segments GROUP BY tablespace_name ORDER BY tablespace_name; * List tablespace usage for each user. * Set min_size_mb to limit the minimal … Continue reading