oracle 查看表空间有查看sqlplus进程

col tablespace_name format a10;
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used
from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;

lsof|grep /home| grep sqlplus

oracle表空间管理和用户管理
--查看表空间和数据文件

select file_name,tablespace_name,autoextensible from dba_data_files;

--数据表空间

CREATE TABLESPACE USER_DATA
LOGGING
DATAFILE 'D:/ORACLE/ORADATA/ORCL/test.DBF' SIZE 50m REUSE ,
'c:/USERS01112.DBF' SIZE 50m REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL

--临时表空间

CREATE TEMPORARY
TABLESPACE USER_DATA_TEMP TEMPFILE 'D:/TEMP0111.DBF'
SIZE 50M REUSE AUTOEXTEND
ON NEXT 1024K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K

--增加数据文件

ALTER TABLESPACE USER_DATA
ADDDATAFILE 'c:/USERS01113.DBF' SIZE 50M;

ALTER TABLESPACE USER_DATA
ADDDATAFILE 'c:/USERS01114.DBF' SIZE 50M
AUTOEXTEND ON
;

--删除表空间

DROP TABLESPACE USER_DATA INCLUDING CONTENTS;

--修改数据文件大小
ALTER DATABASE
DATAFILE 'c:/USERS01113.DBF' RESIZE 40M;

--创建用户、赋予权限

CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY USER_DATA
DEFAULT
TABLESPACE USER_DATA TEMPORARY
TABLESPACE USER_DATA ACCOUNT UNLOCK;

GRANT CONNECT TO USER_DATA;
GRANT RESOURCE TO USER_DATA;

--把表移到另一个表空间
ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;

--创建索引

CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME);

CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME) TABLESPACE TABLESPACENAME;

--重新建立索引

ALTER INDEX INDEXNAME REBUILD TABLESPACE TABLESPACE;

select * from v$tempfile
select * from dba_temp_files