oracle常用sql1
需积分: 0 77 浏览量
2022-08-08
19:32:26
上传
评论
收藏 17KB DOCX 举报
--查看现有表空间信息
SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,round(bytes/(1024*1024),0)
total_space FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;
--查看所有用户的正式表空间和临时表空间
SELECT username,default_tablespace,temporary_tablespace from dba_users
ORDER BY username;
--查看表空间剩余
SELECT tablespace_name,count(*) as extends,round(sum(bytes)/1024/1024,2)
as MB ,sum(blocks)as blocks from dba_free_space group by tablespace_name;
--查询表空间总量
SELECT tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files
group by tablespace_name ORDER BY tablespace_name;
--查询表空间使用率
SELECT total.tablespace_name,round(total.MB-free.MB,2) as
used_MB,round((1-free.MB/total.MB)*100,2) as used_pct
FROM (SELECT tablespace_name,sum(bytes)/1024/1024 as MB from
dba_free_space group by tablespace_name) free,
(SELECT tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files
group by tablespace_name) total
WHERE free.tablespace_name=total.tablespace_name;
--查看所有表或指定表的表空间信息
SELECT table_name,tablespace_name from user_tables where
TABLE_NAME='SYS_LOG'
--查询指定约束的详细信息
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME='SYS_C0012893';
--删除表空间
--1、重启 oracle
--1、登录 20 服务器
--2、用 dba 用户登录
sqlplus /'as sysdba'
--3、执行命令，关闭 oracle
shutdown immediate
--4、重启 oracle
startup
--2、删除临时表空间
alter tablespace PORTAL_TEMP offline;
drop tablespace PORTAL_TEMP including contents and datafiles;
--3、删除临正式空间
alter tablespace PORTAL_DATA offline;
drop tablespace PORTAL_DATA including contents and datafiles;
--4、删除用户
评论0