oracle运维基础操作

创建临时表空间

create temporary tablespace biaom_tmp
tempfile '/lujing/biaom_tmp.dbf'
size 100m 
autoextend on 
next 100m 
maxsize 30720m 
extent management local;

创建表空间

create tablespace biaom
logging datafile '/lujing/biaom.dbf' 
size 100m 
autoextend on 
next 100m 
maxsize 30720m 
extent management local; 

创建用户

create user yonghum
identified by mima
default tablespace biaom
temporary tablespace biaom_tmp

授权

grant create session to yonghum; 赋予create session的权限，即登录的权限
grant unlimited tablespace to yonghum;赋予用户使用表空间的权限
grant create table,create view,create trigger, create sequence,create procedure to yonghum;赋予创建表，视图，触发器，序列，过程的权限
grant connect to yonghum;赋予连接数据库的权限
grant dba to yonghum;赋予DBA的权限

删除表空间及物理文件

drop tablespace biaom including contents and datafiles;

查看表空间使用率

SELECT a.tablespace_name "表空间名称", 
total / (1024 * 1024) "表空间大小(M)", 
free / (1024 * 1024) "表空间剩余大小(M)", 
(total - free) / (1024 * 1024 ) "表空间使用大小(M)", 
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 t.TABLESPACE_NAME,t.FILE_NAME,t.AUTOEXTENSIBLE from dba_data_files t;

增加表空间

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle_db/oracle/oradata/fjflyldb/system02.dbf' SIZE 16G AUTOEXTEND ON NEXT 100M MAXSIZE 10240M;

查看表空间的名字及文件所在位置

select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from sys.dba_data_files
 order by tablespace_name；

查询表空间信息

select username,default_tablespace,t.* from dba_users t

查看总消耗时间最多的前10条SQL语句

select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

查看CPU消耗时间最多的前10条SQL语句

select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

查看消耗磁盘读取最多的前10条SQL语句

select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

本文地址：https://blog.csdn.net/weixin_43467062/article/details/110952225