Oracle 表空间查询和修改空间,修改字符集为ZHS16GBK
查询空间
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
linux下修改空间,或navicat里面执行都可以
alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' resize 2524M;
oracle 修改字符集为ZHS16GBK
[root@bogon ~]# sqlplus /nolog
SQL> conn sys/password as sysdba
Connected.
将数据库启动到RESTRICTED模式下做字符集更改:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 768294912 bytes
Fixed Size 2230448 bytes
Variable Size 566232912 bytes
Database Buffers 197132288 bytes
Redo Buffers 2699264 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.
SQL> alter database open;
Database altered.
执行修改命令
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
Database altered.
重启
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 768294912 bytes
Fixed Size 2230448 bytes
Variable Size 566232912 bytes
Database Buffers 197132288 bytes
Redo Buffers 2699264 bytes
Database mounted.
Database opened.
over