[b]linux下创建oracle用户表空间[/b]
[b]相关表空间操作[/b]
1 查看表空间路径
select * from dba_data_files;
2、创建用户表空间:
CREATE TABLESPACE NOTIFYDB DATAFILE '/oracle/oradata/test/notifydb.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
3、创建用户,指定密码和上边创建的用户表空间
CREATE USER hc_notify IDENTIFIED BY hc_password DEFAULT TABLESPACE NOTIFYDB;
4、赋予权限
grant connect,resource to hc_notify;
grant unlimited tablespace to hc_notify;
grant create database link to hc_notify;
grant select any sequence,create materialized view to hc_notify;
[b]相关表空间操作[/b]
1 查看表空间路径
select * from dba_data_files;
2 修改表空间大小
ALTER TABLESPACE MAXDATA ADD DATAFILE '/oradata/XX/MAXDATA02.DBF' SIZE 1000M
3 查看所有表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
4 未使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
5 查看当前用户每个表占用空间的大小
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name