本文共 2095 字，大约阅读时间需要 6 分钟。

????????????????????????????????????SQL??????????????????????????????????------------------?Oracle??????????`users`????????????SQL?????```sqlselect username, default_tablespace from dba_users where username='xxx';

???????

????????????????????????

SELECT     UPPER(F.TABLESPACE_NAME) "tablespace name",    D.TOT_GROOTTE_MB "total storage (MB)",    D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used storage (MB)",    TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "percentage used",    F.TOTAL_BYTES "used storage (MB)",    F.MAX_BYTES "max storage (MB)"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) DWHERE     D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY     4 DESC;

??????

?????????????????????

col tablespace_name format a15col file_id format a5col file_name format a25col total_space format a20SELECT     tablespace_name,     file_id,     file_name,     round(bytes / (1024 * 1024), 0) total_space FROM     dba_data_files ORDER BY     tablespace_name;

???????

????????????

create user test identified by testdefault tablespace user_datatemporary tablespace user_temp;

????

GRANT     CREATE ANY VIEW,    DROP ANY VIEW,    CONNECT,    RESOURCE,    CREATE SESSION,    DBATO test;

???????

??????????????????????????????????????????????

alter session set container=gnnt_pdb;

?????

??????????

create tablespace user_data logging datafile '+DATA/ORCL/DATAFILE/user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

????

create user test identified by testdefault tablespace user_datatemporary tablespace user_temp;

?????

drop user test cascade;

?????

drop tablespace user_data including contents and datafiles cascade constraint;

??SQL????????????????????Oracle??????????