查看表空间物理路径
select * from dba_data_files;
查看当前用户所在的表空间
select username,default_tablespace from user_users;
创建表空间
create tablespace dzda_user_data
logging
datafile 'D:\app\Administrator\oradata\orcl\dzda_user_data.dbf'
size 50m
autoextend on
next 50m maxsize unlimited
extent management local;
创建临时表空间
create temporary tablespace dzda_temp_data
tempfile 'D:\app\Administrator\oradata\orcl\dzda_temp_data.dbf'
size 50m
autoextend on
next 50m maxsize unlimited
extent management local;
扩容表空间
ALTER TABLESPACE dzda_user_data ADD DATAFILE 'C:\app\Administrator\oradata\orcl\dzda_user_data_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
ALTER TABLESPACE 表空间名称 add datafile 表空间存放路径 size 表空间大小 autoextend on next 增长的大小 maxsize 空间最大值(如果不限制空间最大值就用unlimited)
创建概要文件
create profile dzda_profile limit
failed_login_attempts 3
password_life_time 180
password_reuse_time 60
password_grace_time 30;
查看数据库表空间等使用情况
SELECT a.tablespace_name "表空间名",
total "表空间大小(byte)",
free "表空间剩余大小(byte)",
(total - free) "表空间使用大小(byte)",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
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;