désert/workarea/ztey

create schema(user)

• 查询gzdt11bim用户(SCHEMA)的信息

conn gzdt11bim
DESC DBA_USERS;
COLUMN USERNAME FORMAT A30
COLUMN DEFAULT_TABLESPACE FORMAT A20
# 查看当前用户的缺省表空间
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where USERNAME='gzdt11bim';

# 查看当前用户的系统权限和表级权限
select * from user_role_privs;
select * from user_sys_privs;

set linesize 300
SELECT upper(f.tablespace_name) "tablespace_name",
       d.Tot_grootte_Mb "tablespace(M)",
       d.Tot_grootte_Mb - f.total_bytes "used(M)",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
       f.total_bytes "free_space(M)",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%"
 FROM      
    (SELECT tablespace_name,
            round(SUM(bytes)/(1024*1024),2) total_bytes,
            round(MAX(bytes)/(1024*1024),2) max_bytes
      FROM sys.dba_free_space
     GROUP BY tablespace_name) f,
    (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
      FROM   sys.dba_data_files dd
      GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name    
ORDER BY 4 DESC
/

CREATE TABLESPACE MERLIN_TEST DATAFILE '/home/oracle/MERLIN_TEST.dbf' size 50M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

create user TEST identified by TEST default tablespace MERLIN_TEST quota unlimited on MERLIN_TEST;