tablespace

select * from
(select file_name,blocks,tablespace_name from dba_data_files ) allspace
left join
(select tablespace_name
,file_id
,count(*) "pieces"
,max(blocks) "MAXIMUM"
,min(blocks) "MINIMUM"
,avg(blocks) "AVERAGE"
,sum(blocks) "TOTAL"
,sum(bytes) "free"
from dba_free_space
group by
tablespace_name,file_id) freespace
on allspace.tablespace_name = freespace.tablespace_name


select USERNAME,DEFAULT_TABLESPACE from dba_users where default_tablespace = 'USERS'


select tablespace_name
,sum(bytes) free_space
from dba_free_space
group by tablespace_name


select * from tabs where tablespace_name!='SYSTEM'

select * from user_tables

select * From all_objects where object_type='TABLE' AND OWNER='SYS'


create or replace procedure grantTo(privilege in varchar2, owner in varchar2, toWho in varchar2) is

type table_name_type is table of all_tables.table_name%type
index by binary_integer;
table_name table_name_type;
sSqlStr varchar2(300);

begin
DBMS_OUTPUT.PUT_LINE('owner:' || owner);
table_name.delete;
sSqlStr := 'SELECT table_name FROM ALL_TABLES WHERE owner = : owner';
DBMS_OUTPUT.PUT_LINE('sSqlStr:' || sSqlStr);

Execute Immediate sSqlStr bulk collect into table_name using owner;

DBMS_OUTPUT.PUT_LINE('table_name.COUNT:' ||table_name.COUNT);
for i in table_name.first..table_name.last loop
sSqlStr := 'grant ' || privilege || ' on ' || owner || '.' || table_name(i) || ' to ' || toWho;
Execute Immediate sSqlStr;
end loop;

DBMS_OUTPUT.PUT_LINE('finished');

end grantTo;