Oracle新建用户和创建表空间

--查看表空间利用率

SELECT a.tablespace_name "表空间名",

Round(total / 1024 / 1024 / 1024, 2) "表空间大小",

Round(free / 1024 / 1024 / 1024, 2) "表空间剩余大小",

Round((total - free) / 1024 / 1024 / 1024, 2) "表空间使用大小",

Round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, Sum(bytes) free

FROM DBA_FREE_SPACE

GROUP BY tablespace_name) a,

(SELECT tablespace_name, Sum(bytes) total

FROM DBA_DATA_FILES

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

order by 5 desc;

--给系统表空间扩容

alter database datafile '/oracle/app/oradata/mytablespace/my_01.dbf' resize 256M;

--新建新的表空间

CREATE TABLESPACE TBS_XYZX DATAFILE '/data/oracle/oradata/dtc/xyzx01.dbf' SIZE 20480M;

--给表空间扩大大小

alter tablespace TBS_XYZX add datafile '/data/oracle/oradata/dtc/xyzx02.dbf' size 20480m;

--创建新用户

CREATE USER xyzx IDENTIFIED BY "pass$123" DEFAULT TABLESPACE TBS_XYZX;

--授权

GRANT CONNECT,RESOURCE TO xyzx;

grant select on xyzx.nce_gr_grjbxx_js to username;