oracle相关操作

---------表空间使用占比
select f.tablespace_name,d.total_byte “total_byte(M)”,(d.total_byte-f.free_byte) “used_byte(M)”,
f.free_byte “free_byte(M)”,round((1-f.free_byte/d.total_byte)100,2) “used(%)”,round(f.maxbyte/(10241024),2) “maxbyte(M)”
from (select tablespace_name,round(sum(bytes)/(10241024),2) free_byte ,max(bytes) maxbyte from dba_free_space
group by tablespace_name) f,
(select tablespace_name,round(sum(bytes)/(10241024),2) total_byte from dba_data_files
group by tablespace_name) d
where f.tablespace_name=d.tablespace_name
order by f.tablespace_name;

–查询表空间对应的数据文件

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

当单个数据文件超过32G应增加数据文件

–用户和表空间对应关系

select username,default_tablespace from dba_users;

--------------新增查询权限用户
create user xxxx identified by xxxx;
grant select any table to xxxx;
grant select any dictionary to xxxx;
grant create session to xxxx;

create user occ2019 identified by occ2019;
grant select any table to occ2019;
grant select any dictionary to occ2019;
grant create session to occ2019;

select name from v$datafile
Select * FROM DBA_DATA_FILES;

–查询表空间对应的数据文件

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

–用户和表空间对应关系

select username,default_tablespace from dba_users;

增加表空间数据文件(dbf文件设置了32G默认自增长。当表空间使用到30G后需要增加表空间数据文件！！！目前一月5G的数据量)
alter tablespace xxxxxxx add datafile;
alter tablespace xxxxxxx add datafile;

---

1.查看是否开启OMF,如果db_create_file_dest参数为空，说明未启用OMF
show parameter db_create_file_d
2.启用OMF
alter system set db_create_file_dest=’/u01/datafile’;
3.创建表空间（创建完成立即生成第一个datafile，初始化是100M,自动扩展最大32G）
create tablespace xxxxx;
4.查询数据文件的使用率以及占用的磁盘空间
select a.file_name,a.bytes/1024/1024 “TOTAL(M)”,

b.sb/1024/1024 “FREE(M)”,100*

b.sb/a.bytes “FREE%” from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b where a.file_id=b.file_id order by a.file_name;
5.当第一个datafile剩余50M左右需要手动添加datafile
alter tablespace xxxxxxx add datafile;

-------------查询表空间大小及已使用大小—
select tablespace_name,
max_gb,
used_gb,
round(100 * used_gb / max_gb) pct_used
from (select a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),
2) used_gb,
round(a.maxbytes / power(2, 30), 2) max_gb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
‘YES’,
f.maxbytes,
‘NO’,
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name tablespace_name,
round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,
round(sum(decode(f.autoextensible,
‘YES’,
f.maxbytes,
‘NO’,
f.bytes)) / power(2, 30),
2) max_gb
from v$tem{p}_{s}pac{e}_{h}eaderh,v$temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name)
order by 4;

---

---

----用户对应表空间物理文件路径及大小
select a.TABLESPACE_NAME,a.FILE_ID,a.FILE_NAME,(a.BYTES / 1024 / 1024) as “FILE_SIZE(MB)”,b.tb_size as “TABLESPACE_SIZE(MB)”,((a.BYTES / 1024 / 1024) - b.tb_size) as “RELEASE_SIZE” from dba_data_files a,(select TABLESPACE_NAME,round(max(BLOCK_ID) * 16384 / 1024 / 1024) tb_size from dba_extents group by TABLESPACE_NAME) b where a.TABLESPACE_NAME = b.TABLESPACE_NAME order by a.FILE_ID;

---

select username,default_tablespace,temporary_tablespace from dba_users
where (default_tablespace=‘SYSTEM’ or temporary_tablespace=‘SYSTEM’) and username not in?? (‘SYSTEM’,‘SYS’);

设置系统表空间自增长
alter database datafile ‘/u01/app/oracle/oradata/oracle/system02.dbf’ autoextend on next 5000M maxsize unlimited;

oracle可管理的最大数据块为2的22次方个，而根据单个数据块大小大小的不同，其最大容量也是不同的。对于OLTP应用，数据块的大小通常为8K，这样，算下来，单个数据文件的大小最大为(2^22)*8K=32G.

---

oracle如何查看当前有哪些用户连接到数据库

可以执行以下语句：
select username,serial#, sid from v$session; —查询用户会话
alter system kill session 'serial#, sid ';—删除相关用户会话

建议以后台登陆删除用户会话
1、查询oracle的连接数
select count(*) from v$session;2、查询oracle的并发连接数selectcount(\ast )fromv$session where status=‘ACTIVE’;
3、查看不同用户的连接数
select username,count(username) from v$sessionwhereusernameisnotnullgroupbyusername;4、查看所有用户：select\ast fromal{l}_{u}sers;5、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：select\ast fromdb{a}_{s}y{s}_{p}rivs;select\ast fromuse{r}_{s}y{s}_{p}rivs;6、查看角色(只能查看登陆用户拥有的角色)所包含的权限select\ast fromrol{e}_{s}y{s}_{p}rivs;7、查看用户对象权限：select\ast fromdb{a}_{t}a{b}_{p}rivs;select\ast fromal{l}_{t}a{b}_{p}rivs;select\ast fromuse{r}_{t}a{b}_{p}rivs;8、查看所有角色：select\ast fromdb{a}_{r}oles;9、查看用户或角色所拥有的角色：select\ast fromdb{a}_{r}ol{e}_{p}rivs;select\ast fromuse{r}_{r}ol{e}_{p}rivs;10、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)select\ast fromV$PWFILE_USERS;

修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;

查看游标数量
Select * from v$open_cursor Where user_name=’’

查询数据库允许的最大连接数：
select value from v$parameter where name = ‘processes’;
或者：show parameter processes;

查询数据库允许的最大游标数：
select value from v$parameter where name = ‘open_cursors’

查看oracle版本
select banner from sys.v_$version;

按降序显示用户"SYSTEM"为每个会话打开的游标数
select o.sid, osuser, machine, count(*) num_curs from v$ope{n}_{c}ursoro,v$session s where user_name = ‘SYSTEM’ and o.sid=s.sid group by o.sid, osuser, machine order by num_curs desc;

—查询用户表大小MB
select Segment_Name,Sum(bytes)/1024/1024 From User_Extents where TABLESPACE_NAME=‘xxxxx’ Group By Segment_Name
select * From User_Extents
—查询SYS_LOB对应表
select owner, table_name, column_name, segment_name, index_name
from dba_lobs where segment_name=‘SYS_LOB0000074348C00006$$’

整理自网络方便使用、备查