Oracle DBA 必须掌握的 查询脚本：
0：启动与关闭
1：通过v$parameter数据字段来查询oracle标准数据块的大小
----通过 v$parameter数据字典来查询oracle标准数据块的大小。
SYS@orcl> startup
ORACLE instance started. Total System Global Area 1221992448 bytes
Fixed Size 1344596 bytes
Variable Size 771754924 bytes
Database Buffers 436207616 bytes
Redo Buffers 12685312 bytes
Database mounted.
Database opened.
SYS@orcl> col name format a30;
SYS@orcl> col value format a20;
SYS@orcl> select name,value from v$parameter where name='db_block_size'; NAME VALUE
------------------------------ --------------------
db_block_size 8192 SYS@orcl> show parameter db_block NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
2：通过 dict 查看数据库中数据字典的信息
SYS@orcl> col table_name for a30;
SYS@orcl> col comments for a30;
SYS@orcl> select * from dict; TABLE_NAME COMMENTS
------------------------------ ------------------------------
DBA_CONS_COLUMNS Information about accessible c
olumns in constraint definitio
ns DBA_LOG_GROUP_COLUMNS Information about columns in l
og group definitions DBA_LOBS Description of LOBs contained
in all tables DBA_CATALOG All database Tables, Views, Sy
3 : 通过 v$fixed_view_definition 查看数据库中内部系统表的信息
SYS@orcl> col view_name format a15;
SYS@orcl> col view_definition format a30000;
SYS@orcl> select * from v$fixed_view_definition where rownum<=10; VIEW_NAME VIEW_DEFINITION
----------------------------------------------------------------------------------------------
GV$WAITSTAT select inst_id,decode(indx,1,'data block',2,'sort block',3,'save undo block', 4,
'segment header',5,'save undo header',6,'free list',7,'extent map', 8,'1st level
bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',12,'bitmap index b
lock',13,'file header block',14,'unused', 15,'system undo header',16,'system und
o block', 17,'undo header',18,'undo block'), count,time from x$kcbwait where ind
x!=0
4：通过查询 dba_data_files 数据来了解Oracle系统的数据文件信息
[oracle@localhost ~]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> col file_name format a50;
SYS@orcl> set linesize3000;
SYS@orcl> select
file_name,tablespace_name
from
dba_data_files
where
rownum<=10;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE SYS@orcl>
5：查看 临时文件信息： dba_temp_files 、v$tempfile;
SYS@orcl> select * from v$tempfile; FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 788143 30-NOV-16 3 1 ONLINE READ WRITE 30408704 3712 20971520 8192 /u01/app/oracle/oradata/orcl/temp01.dbf SYS@orcl>
6：获取控制文件信息： v$controlfile 数据字典
SYS@orcl> col name fromat a60;
SYS@orcl> col name format a60;
SYS@orcl> col status format a20;
SYS@orcl> col block_size format a60;
SYS@orcl> col is_recoveris_recover format a60 ;
SYS@orcl> col block_size format a150 ;
SYS@orcl> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
-------------------- ------------------------------------------------------------ --- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl NO ########## 594
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl NO ########## 594
7：查看日志文件信息： v$logfile;
SYS@orcl> col member for a50;
SYS@orcl> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_
---------- -------------------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO SYS@orcl>
8： 查看oacle 系统是否采用归档模式： v$database
9：查询归档日志文件的所在路劲信息： show parameter log_archive_desc
SYS@orcl> set pagesize 30;
SYS@orcl> show parameter log_archive_dest; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SYS@orcl>
10：查询视图v$parameter 进行确定参数的默认值是否修改过
SYS@orcl> col name for a30;
SYS@orcl> col value for a30;
SYS@orcl> select name ,value,ismodified from v$parameter where rownum <=20; NAME VALUE ISMODIFIED
------------------------------ ------------------------------ ----------
lock_name_space FALSE
processes 150 FALSE
sessions 248 FALSE
timed_statistics TRUE FALSE
timed_os_statistics 0 FALSE
resource_limit FALSE FALSE
license_max_sessions 0 FALSE
license_sessions_warning 0 FALSE
cpu_count 4 FALSE
instance_groups FALSE
event FALSE
sga_max_size 1224736768 FALSE
use_large_pages TRUE FALSE
pre_page_sga FALSE FALSE
shared_memory_address 0 FALSE
hi_shared_memory_address 0 FALSE
use_indirect_data_buffers FALSE FALSE
lock_sga FALSE FALSE
processor_group_name FALSE
shared_pool_size 0 FALSE 20 rows selected. SYS@orcl>
11：修改系统参数：
--查看Cursor相关参数
SYS@orcl> show parameter cursor; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50 ---修改相关参数
SYS@orcl> alter system set open_cursors=350 scope=both; System altered. SYS@orcl> alter system set session_cached_cursors=100 scope=spfile; System altered. SYS@orcl> alter system set cursor_space_for_time=true scope=spfile; System altered.
/*
scope 参数值有三个选项。
scope=memory: 只改变当前实例运行，亦即初始化参数改变了只对当前实例有效，当实例重启之后，初始化参数值还原。
scope=spfile：只改变spfile的设置。亦即改变初始化参数文件内容。实例重启后参数生效。
scope=both：既改变实例也改变spfile。需要注意的是，如果修改静态参数，必须指定SPFILE参数（scope=spfile），否则会报错。
*/
12：创建 一个密码文件 其sys 口令为：oracle
13:查询 警告文件位置
SYS@orcl> show parameter backg NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/orc
l/orcl/trace
14：查看 跟踪文件的位置：
SYS@orcl> show parameter user_dump NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/orc
l/orcl/trace
SYS@orcl>
15：查看 并修改共享池大小
SYS@orcl> show parameter shared_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 10905190
shared_pool_size big integer 0
SYS@orcl> alter system set shared_pool_size=30m; System altered. SYS@orcl> show parameter shred_pool
SYS@orcl> show parameter shared_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 10905190
shared_pool_size big integer 32M
SYS@orcl>
SYS@orcl>
16：查看并修改大池的大小；
SYS@orcl> show parameter large_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SYS@orcl> alter system set large_pool_size=16m; System altered. SYS@orcl> show parameter large_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 16M
SYS@orcl>
17：查看Java 池信息
SYS@orcl> show parameter java_pool NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SYS@orcl> alter system set java_pool_size=10m; System altered. SYS@orcl> show parameter java_ NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_jit_enabled boolean TRUE
java_max_sessionspace_size integer 0
java_pool_size big integer 16M
java_soft_sessionspace_limit integer 0
SYS@orcl>
18：显示当前用户进程PGA 信息：
SYS@orcl> show parameter pga; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SYS@orcl>
19:查看当前实例数据库进程
`20： 所有常用字典表
-----基本数据字典及其说明
select * from dba_tablespaces;---关于表空间的信息
select * from dba_ts_quotas ;---所有用户表空间的限额
select * from dba_free_space;--所有表空间中自由分区
select * from dba_segments;--描述数据库中所有段的储存空间；
select * from dba_extents;--数据库中所有分区的信息
select * from dba_tables;---数据库中所有数据表的描述
select * from dba_tab_columns;---所有表、视图 及簇的列
select * from dba_views ;--数据库中所有的视图信息；
select * from synonyms;--关于同义词的信息查询；
select * from dba_sequences;--所有用户序列号信息
select * from dba_constraints;--所有用户表的约束信息；
select * from dba_indexes;--关于数据库索引的描述；
select * from dba_ind_columns;--在所有表及簇上压缩索引的列
select * from dba_triggers;--所有用户的触发器信息
select * from dba_source;--所有用户储存过程的信息；
select * from dba_data_files;--查询关于数据库文件的信息；
select * from dba_tab_privs;--查询关于对象授权的信息
select * from dba_objects;--数据库中所有的对象；
select * from dba_users;--关于数据库中所有用户的信息； ------------常用动态性能视图
select * from v$database ;---描述关于数据库的相关信息
select * from v$datafile;--数据库使用的数据文件信息
select * from v$log;--从控制文件中提取有关重做日志组的信息
select * from v$logfile;--有关实例重置日志组文件名及其位置的信息
select * from v$archived_log;--记录归档日志文件的基本信息
select * from v$archive_dest;--记录归档日志文件的路径信息
select * from v$controlfile ;--描述控制文件的相关信息
select * from v$instance ; ---记录实例的基本信息
select * from v$system_parameter;--显示实例当前有效的参数信息
select * from v$sga;--显示实例的SGA区的大小
select * from v$sgastat;--统计SGA使用情况的信息
select * from v$parameter ;-- 记录初始化参数文件中所有项的值
select * from v$lock ;--通过访问数据库会话，设置对象锁的所有信息
select * from v$session;--有关会话的信息
select * from v$sqltext;--记录SQL语句的信息
select * from v$sql;--记录SQL语句的详细信息
select * from v$bgprocess;--显示后台进程信息；
select * from v$process;--当前进程的信息
21：如何查询oracle数据库中的各种角色
--1. 查询oracle中所有用户信息
select * from dba_users;
--2. 只查询用户和密码
select username,password from dba_users;
--3. 查询当前用户信息
select * from dba_ustats;
--4. 查询用户可以访问的视图文本
select * from dba_varrays;
---5. 查询数据库中所有视图的文本
select * from dba_views;
select distinct tablespace_name from SYS.DBA_FREE_SPACE where tablespace_name like '%RB%'; select * from dba_objects where object_name='JDE900_F0005'; select * from rbods.jde900_f0005;
select * from rbodm.dim_company; ---查看当前用户的缺省表空间
select username,default_tablespace from user_users
---查看当前用户的角色
select * from user_role_privs
---查看当前用户的系统权限和表级权限
select * from user_sys_privs
select * from user_tab_privs
----查看用户下所有的表
select * from user_tables
---显示用户信息(所属表空间)
select default_tablespace,temporary_tablespace from dba_users
--显示当前会话所具有的权限
select * from session_privs
--- 显示指定用户所具有的系统权限
select * from dba_sys_privs
--- 显示特权用户
select * from v$pwfile_users
--- 查看名称包含log字符的表
select object_name,object_id from user_objects where instr(object_name,'log')>0
--- 查看某表的创建时间
select object_name,created from user_objects where object_name='ZW_YINGYEZ'
--- 查看某表的大小
select sum(bytes)/(1024*1024) tablesize from user_segments
where segment_name='ZW_YINGYEZ'
--- 查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0
--- 查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name
--- 查看索引被索引的字段
select * from user_ind_columns where table_name='CB_CHAOBIAOSJ201004'
--- 查看索引的大小
select sum(bytes)/(1024*1024) as indexsize from user_segments
where segment_name=upper('AS_MENUINFO')
--- 查看视图信息
select * from user_views
--- 查看同义词的名称
select * from user_synonyms
-- 查看函数和过程的状态
select object_name,status from user_objects where object_type='FUNCTION'
select object_name,status from user_objects where object_type='PROCEDURE'
--- 查看函数和过程的源代码
select text from all_source where owner=user and name='SF_SPLIT_STRING'
--- 查看表字段
select cname from col where tname='ZW_YINGYEZ'
select column_name from user_tab_columns where table_name='ZW_YINGYEZ' ---查看oracle版本命令：
select * from v$version
22：oracle 增加控制文件的方法：
1： 查看参数文件存放位置，并关闭数据库实例 orcl：
[oracle@localhost orcl]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 23:07:02 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> show parameter control_files; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/orcl/contro
l02.ctl SYS@orcl> select name from v$controlfile; NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl SYS@orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2： 退出sqlplus， 复制文件。
SYS@orcl> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ cd /u01/app/oracle/fast_recovery_area/orcl
[oracle@localhost orcl]$ pwd
/u01/app/oracle/fast_recovery_area/orcl
[oracle@localhost orcl]$ ls
control02.ctl
[oracle@localhost orcl]$ cp control02.ctl control03.ctl
[oracle@localhost orcl]$ ls
control02.ctl control03.ctl
3：登录进入 sqplus ，启动数据库实例进入 nomount状态，并修改控制文件参数
[oracle@localhost ~]$ sqlplus / as sysdba; SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 10 00:14:01 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@orcl> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started. Total System Global Area 1221992448 bytes
Fixed Size 1344596 bytes
Variable Size 771754924 bytes
Database Buffers 436207616 bytes
Redo Buffers 12685312 bytes
SYS@orcl> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl', '/u01/app/oracle/fast_recovery_area/orcl/control03.ctl' scope=spfile; System altered. SYS@orcl> shutdown immediate;
ORA-01507: database not mounted ORACLE instance shut down.
SYS@orcl> startup
ORACLE instance started. Total System Global Area 1221992448 bytes
Fixed Size 1344596 bytes
Variable Size 771754924 bytes
Database Buffers 436207616 bytes
Redo Buffers 12685312 bytes
Database mounted.
Database opened.
SYS@orcl> create pfile from spfile; File created. SYS@orcl> select name from v$controlfile; NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
/u01/app/oracle/fast_recovery_area/orcl/control03.ctl SYS@orcl>
SYS@orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started. Total System Global Area 1221992448 bytes
Fixed Size 1344596 bytes
Variable Size 771754924 bytes
Database Buffers 436207616 bytes
Redo Buffers 12685312 bytes
Database mounted.
Database opened.
SYS@orcl>
23:Oracle 查看表空间的大小及使用情况sql语句
注：本段内容来自： 《 Oracle 查看表空间的大小及使用情况sql语句 》
--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
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
查看表空间使用情况：
--查看表空间使用情况：
SELECT tbs 表空间名,
sum(totalM) 总共大小M,
sum(usedM) 已使用空间M,
sum(remainedM) 剩余空间M,
sum(usedM) / sum(totalM) * 100 已使用百分比,
sum(remainedM) / sum(totalM) * 100 剩余百分比
FROM (SELECT b.file_id ID,
b.tablespace_name tbs,
b.file_name name,
b.bytes / 1024 / 1024 totalM,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 usedM,
sum(nvl(a.bytes, 0) / 1024 / 1024) remainedM,
sum(nvl(a.bytes, 0) / (b.bytes) * 100),
(100 - (sum(nvl(a.bytes, 0)) / (b.bytes) * 100))
FROM dba_free_space a, dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.file_id, b.bytes
ORDER BY b.tablespace_name)
GROUP BY tbs
select b.tablespace_name "表空间",
b.bytes / 1024 / 1024 "大小M",
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 "已使用M",
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) "利用率"
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
--and b.tablespace_name = 'SYSTEM'
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;
oracle 表空间不足解决办法大全
-- 【解决办法-原因一】
--只要将表空间设置为足够大，并设置为自增长即可。
--1、扩展表空间
alterdatabase datafile 'D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF' resize 50m;
-- 注：50m，是表空间大小，可以根据实际需要加大，但最大不得超过32G
--2、自动增长
alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF' autoextend onnext 50m maxsize 500m;
--【解决办法-原因二】
--因为表空间中的数据文件已经足够大（达到32G），所以，这时仅仅增加表空间大小是不行的。 这个时候，我们可以增加该表空间的数据文件，这样表空间的大小即变为64G了。
ALTER TABLESPACE aaa ADD DATAFILE 'E:\APP\ORACLE11GR2\ORADATA\ORCL\aaa_DATA02.DBF' SIZE 32767M;
查看system和sysaux表空间是否是自动扩展的。
SQL>select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files; -- 如果为自动扩展那么AUTOEXTENSIBLE字段的值应为 YES，是否为NO；INCREMENT_BY 这个为每次自动扩展的空间大小。
开启或者关闭 ：表空间 是否 自动扩展
---开启自动扩展功能语法：
alter database datafile '对应的数据文件路径信息' autoextend on;
--关闭自动扩展功能语法：
alter database datafile '对应的数据文件路径信息' autoextend off;
新增 表空间
--oracle增加表空间数据文件大小
--
--1.增加数据文件 ALTER TABLESPACE ljjgbs_east ADD DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' SIZE 50M; --2.新增数据文件，并允许自动增长 ALTER TABLESPACE ljjgbs_east ADD DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 100M; --3.允许存在的数据文件自动增长 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' AUTOEXTEND ON NEXT 5M maxsize unlimited; --4.手动增加存在的数据文件的大小 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' RESIZE 20000M;
创建表空间
create tablespace GFPORTAL
datafile 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\PDBORCL\GFPORTAL.DBF' size 100M --生成数据文件并定义文件大小
autoextend on next 100M maxsize unlimited logging --设置自动扩展
extent management local autoallocate
segment space management auto; --创建 多个表空间文件 create tablespace sync_plus_1 datafile
'/u01/app/oracle/oradata/orcl/sync_plus_1_01.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_02.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_03.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_04.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_05.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_06.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_07.dbf' size 500M autoextend on next 100M
, '/u01/app/oracle/oradata/orcl/sync_plus_1_08.dbf' size 500M autoextend on next 100M maxsize unlimited logging extent management local autoallocate segment space management auto
增加多个表空间
--- 执行该语句给 TEST_SYNC 用户授权，此时 TEST_SYNC 用户就可以登录了。 ----给表空间增加数据文件
ALTER TABLESPACE sync_plus_1 ADD DATAFILE 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_01.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_02.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_03.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_04.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_05.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_06.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_07.dbf' size 500M autoextend on next 100M
, 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_08.dbf' size 500M autoextend on next 100M maxsize unlimited ;
删除表空间
24: 因：审计 表占用了大量资源 导致————》system表空间爆满解决方法
注：本段内容来自于：《 system表空间爆满解决方法 》
注：本段内容来自：《 system表空间用满解决 方法 》
25：oracle删除(释放)数据文件/表空间流程
注：本段内容来自： 《 oracle删除(释放)数据文件/表空间流程 》
-----------------