查表空间使用率,切换用户表空间
1 查询表空间使用率
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
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
2 扩展表空间
select t.* from sys.dba_data_files t where t.tablespace_name = 'JXYTH_ZP'
alter tablespace JXYTH_ZP add datafile '+DATADG/orcl/datafile/jxyth_zp02.ora' SIZE 2G;
原文路径: https://blog.csdn.net/weixin_42649624/article/details/90740667
3 查询表空间下的用户有哪些
select distinct owner from dba_segments where tablespace_name ='&TABLE_NAME' ;
切换用户默认表空间?
--1 关闭用户的tomcat
--2 查询还有哪些连接并kill 掉
select sid,serial# from v$session where username='JXYTH_TEST';
--alter system kill session '150,9019';
--3 备份数据库
--4 查询表空间位置
select t.* from sys.dba_data_files t where t.tablespace_name = 'JXYTH'
--5 创建表空间
CREATE TABLESPACE xsc
LOGGING
DATAFILE 'D:\ORACLE11G\ORADATA\ORCL\xsc_.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
ALTER DATABASE DEFAULT TABLESPACE xsc;
---默认表空间查询方式
select a.property_name, a.property_value from database_properties a where a.property_name like '%DEFAULT%';
---用户默认表空间查询方式
select username,default_tablespace from dba_users where username='JXYTH_TEST';
--6 设置用户为默认表空间
alter database default tablespace xsc;
--7 设置用户的默认表空间为
alter user JXYTH_TEST default tablespace xsc;
--查询行数多的表
select * from user_tables t where t.NUM_ROWS is not null order by t.NUM_ROWS desc
PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:
原因是
必须使用动态的语句, DDL 语句,在存储过程中, 需要改为动态语句 :
https://blog.csdn.net/liuao107329/article/details/54135348