1.查看某个用户对应的表空间和datafile select t1.username,t2.tablespace_name,t2.file_name,t1.temporary_tablespace ,t3.file_name from dba_users t1 left join dba_data_files t2 on t1.default_tablespace = t2.tablespace_name left join dba_temp_fi
1.查看某个用户对应的表空间和datafile
select t1.username,t2.tablespace_name,t2.file_name,t1.temporary_tablespace ,t3.file_name
from dba_users t1
left join
dba_data_files t2
on t1.default_tablespace = t2.tablespace_name
left join
dba_temp_files t3
on t1.temporary_tablespace = t3.tablespace_name
where
lower(t1.username) in
('lbi_sys_ptcl','lbi_ods_ptcl','lbi_ods_ptcl','lbi_edm_ptcl','lbi_ls_ptcl','lbi_dm_ptcl','lbi_dim_ptcl')
2.产看表空间信息：
（1）一般表空间查询
select * from dba_data_files t where t.tablespace_name in (
'tbs_dim_ptcl','tbs_ls_ptcl', 'tbs_ods_ptcl', 'tbs_dm_ptcl', 'tbs_edm_ptcl', 'tbs_sys_ptcl' );
（2）临时表空间查询
select * from dba_temp_files t where t.tablespace_name in ('tbs_temp_ptcl');
3.创建表空间
(1)一般表空间
create tablespace tbs_dw_ym
nologging
datafile '/opt/oracle/oradata/ym_tbs/tbs_dw_ym.dbf' size 50m
extent management local segment space management auto;
--extent management:区管理
--local segment space management :本地段空间管理
--auto 自动管理,一般默认情况就是,如果想改为手动管理：manual
(2)临时表空间
create
temporary tablespace tbs_ym_temp
tempfile '/opt/oracle/oradata/ym_tbs/tbs_ym_temp.dbf' size 50m
reuse autoextend on next 640k maxsize 1000m;
--reuse :重新运用,可以加可以不加