USER

Schema DDL
-------------
 set pagesize 0 
SET LONG 9999999
select dbms_metadata.get_ddl('USER','&USERNAME') FROM DUAL;

Schema Status
-------------
select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='&username';

select username,tablespace_name,decode(max_bytes,-1,'unlimited',ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA in MB" from dba_ts_quotas where  username='&username';

select grantee,granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "GRANTED ROLE" from dba_role_privs where grantee='&username';

select grantee,privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "PRIVILEGE" from dba_sys_privs where grantee='&username';

select owner,sum(bytes)/1024/1024/1024 "SIZE in GB" from dba_segments where owner='&owner';

select owner,object_type,count(*) from dba_objects where owner='&owner' group by owner, object_type;


All Users passwords backup
--------------------------------
spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users;  ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off
/
All tables owned by a user
--------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select    owner
,       tablespace_name
,       segment_name
,       segment_type
,       PARTITION_NAME
,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from    dba_segments
where    owner like '&user'
and    segment_type = 'TABLE'
group    by segment_name
order     by ceil(sum(bytes) / 1024 / 1024) desc
/

All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select    owner
,       tablespace_name
,       segment_name
,       segment_type
,       PARTITION_NAME
,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from    dba_segments
where    tablespace_name like '&tablespace_name'
group    by segment_name
order     by ceil(sum(bytes) / 1024 / 1024) desc
/

Total space allocated by Owner:
-------------------------------
set pages 9999 lines 300
col    owner format a15
col    segment_name format a30
col    segment_type format a15
col     tablespace_name format a20
col    mb format 999,999,999
select  owner
,    segment_name
,    segment_type
,       tablespace_name
,    mb
from    (
    select    owner
    ,    segment_name
    ,    segment_type
        ,       tablespace_name
    ,    bytes / 1024 / 1024 "SIZE in MB"
    from    dba_segments
    order    by bytes desc
    )
/

schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "size MB"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/