ORACLE的相关概念和SQL命令

曹梦阮
205 阅读8分钟

相关链接

Oracle官方文档

CDB与PDB

CDB与PDB是Oracle 12C引入的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器(CDB)承载多个可插拔数据库(PDB)。

CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。

在ORACLE 12C之前，实例与数据库是一对一或多对一关系(RAC)：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。

而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。

和SQL Server相对照的话，CDB与PDB是不是感觉和SQL SERVER的单实例多数据库架构是一回事。像PDB$SEED可以看成是master、msdb等系统数据库，PDBS可以看成用户创建的数据库。而可插拔的概念与SQL SERVER中的用户数据库的分离、附加其实就是那么一回事。

CDB组件

  • ROOT组件

    • ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User,元数据的一个例子是ORACLE提供的PL/SQL包的源代码，Common User 是指在每个容器中都存在的用户。

  • SEED组件

    • Seed又叫PDB$SEED,这个是你创建PDBS数据库的模板，你不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed. 这个感念，个人感觉非常类似SQL SERVER中的model数据库

  • PDBS

    • CDB中可以有一个或多个PDBS，PDBS向后兼容，可以像以前在数据库中那样操作PDBS，这里指大多数常规操作。

这些组件中的每一个都可以被称为一个容器。因此，ROOT(根)是一个容器，Seed(种子)是一个容器，每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。

Oracle角色说明

  • connect（连接角色）

    • 这种角色下只可以登录Oracle，不可用创建实体，也不可用创建数据库结构，即只能对其他人创建的表中的数据进行操作。

  • resource(资源角色)

    • 该角色可以创建实体，但是不可以创建数据库结构。 可以创建表、序列（sequence）、运算符(operator)、过程(procedure)、触发器(trigger)、索引(index)、类型(type)和簇(cluster)。

  • dba（数据库管理员权限）

    • 该角色拥有系统最高权限，只有DBA才可以创建数据库结构。包括无限制的空间限额和给其他用户授予各种权限的能力，system由dba用户拥有。 对于普通用户来说，授予connect和resource权限即可，只对dba授予connect、resource和dba权限。

Oracle命令

系统相关
  • 查询oracle版本
 select *
 from V$VERSION;
  • 查询oracle的SID
 select instance_name from v$instance;
  • 查询db_name
 select name from v$database;
  • 重启数据库
 shutdown immediate;
 startup;
  • 当前连接数
 select count(*) from v$process;
  • 数据库允许的最大连接数
 select value from v$parameter where name = 'processes';
  • 修改最大连接数:
 alter system set processes = 2000 scope = spfile;
  • 数据库服务器配置tnsnames,方便后期运维操作
 # 添加前最好先备份下,vim tnsnames.ora编辑文件，仔细检查，不要配置错误
 cd $ORACLE_HOME/network/admin/
  • 格式如下
 TESTRAC =
     (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = scan域名)(PORT = 1521))
     (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = TESTRAC )
 )
  • 数据库告警阈值设定查看
 select warning_value, critical_value
 from dba_thresholds
 where metrics_name='Tablespace Space Usage' and object_name is NULL;
  • 数据库当前告警和可用的处理方法
 select reason,SUGGESTED_ACTION
 from dba_outstanding_alerts
 where object_name='CDATA';
  • 查看系统的编码格式
 select userenv('language') from dual;
PDB相关
  • 查看CDB信息
 select name, cdb, open_mode, con_id
 from v$database;
  • 查看PDB实例
 select name, con_id, open_mode
 from V$PDBS;
  • 查看已有pdb的tempfile文件
 select name
 from V$TEMPFILE;
  • 查看已有的PDB的datafile
 select name
 from V$DATAFILE;
  • 创建一个新的PDB
  • 密码大小写是否敏感可以在CDB下查看该参数: show parameter sensitive
  • file_name_convert参数参数格式：/opt/oracle/oradata/${SID}/pdbseed, /opt/oracle/oradata/${SID}/${PDB_Name}
 create pluggable database dev admin user dev identified by 123 file_name_convert =('/opt/oracle/oradata/ORCLCDB/pdbseed','/opt/oracle/oradata/orcl_root_dev');
  • 设置pdb自启动

${pdb} save state是对于某个pdb而言。即你想让哪个pdb随着cdb启动，就设置哪一个。

 alter pluggable database all save state;
 -- 查询自启动设置
 select con_name, instance_name, state from dba_pdb_saved_states;
 -- 取消自启动
 alter pluggable database all discard state;
  • 启动或关闭一个创建好的PDB
 alter pluggable database dev open;
 alter pluggable database pdb1 close;
 -- 启动所有pdb
 alter pluggable database all open;
  • 切换到指定PDB
 alter session set container=pdb1;
  • 查看配置文件default
 -- FAILED_LOGIN_ATTEMPTS --用户失败登录尝试次数
 -- PASSWORD_LIFE_TIME--用户密码生命周期（按规定要求是配置3个月90天）
 -- PASSWORD_VERIFY_FUNCTION--密码校验函数
 -- PASSWORD_GRACE_TIME--密码失效宽容期限(30天的宽容期限)
 select * from dba_profiles;
  • 删除PDB
 -- 关闭所有节点下的指定PDB，PDB处于关闭状态才能删除
 show pdbs；
 -- 首先在所有节点上停止实例（PDB）
 alter pluggable database testrac close immediate;
 -- 单节点执行删除命令
 drop pluggable database testrac including datafiles;
  • 查看profile
 -- 查看profile
 select profile,resource_name,limit from dba_profiles;
 select * from dba_profiles where profile='PASSWORD_UNLIMIT_PROFILE';
  • 创建profile
 -- 创建profile
 CREATE PROFILE "PASSWORD_UNLIMIT_PROFILE" LIMIT
  COMPOSITE_LIMIT UNLIMITED
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  IDLE_TIME UNLIMITED
  CONNECT_TIME UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LIFE_TIME 180
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  INACTIVE_ACCOUNT_TIME UNLIMITED;
  • 修改profile密码过期策略
 -- 修改profile密码过期时间
 ALTER profile PASSWORD_UNLIMIT_PROFILE limit PASSWORD_LIFE_TIME UNLIMITED;
表空间相关
  • 查看OMF配置的db_create_file_dest
 show parameter db_create_file_dest;
  • 查看表空间
 select name from v$tablespace;
 -- 查看表空间大小
 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;
  • 查看表空间下的表
 select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='CDATA';
  • 查看表空间使用情况
 select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
 from dba_free_space
 group by tablespace_name
 -- 查看表空间使用情况(注：若表空间未使用或者占满，sys.sm$ts_used、sys.sm$ts_free可能为空)
 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;
  • 查看单个表空间使用情况
 select df.tablespace_name tablespace, fs.bytes free,
 df.bytes , fs.bytes *100/ df.bytes pct_free
 from dba_data_files df ,dba_free_space fs
 where df.tablespace_name = fs.tablespace_name
 and df.tablespace_name = 'CDATA';
  • 查看表空间是否可扩展
 SELECT T.TABLESPACE_NAME, D.FILE_NAME,  D.AUTOEXTENSIBLE,   D.BYTES,  D.MAXBYTES,   D.STATUS
 FROM DBA_TABLESPACES T,DBA_DATA_FILES D 
 WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
 ORDER BY TABLESPACE_NAME, FILE_NAME;
  • 设置表空间大小和是否可扩展
 //设置表空间大小
 alter database datafile '/opt/oracle/oradata/orclpdb3/ORCL/****/datafile/o1_mf_inventor_k4ykocb9_.dbf' resize 64M;
 //设置表空间是否可扩展s
 alter database datafile '/opt/oracle/oradata/orclpdb3/ORCL/****/datafile/o1_mf_inventor_k4ykocb9_.dbf' autoextend off;
  • 创建表空间
 -- 最基础的创建表空间
 create tablespace 123;
 -- 创建表空间，指定数据文件位置，指定文件大小，扩宽性，等
 -- 创建表空间时需要保证指定的datafile目录是存在的
 -- 参数说明：
 -- datafile '${data file path}'：数据文件路径
 -- size ${size}：数据文件大小
 -- autoextend on next ${size}：下次自动扩展大小
 -- maxsize unlimited：最大尺寸无限制
 create tablespace 表空间 datafile '/opt/oracle/oradata/orcl/数据文件.dbf' size 200m autoextend on next 10m maxsize unlimited;
  • 创建临时表空间
 -- 创建临时表空间，指定数据文件位置，指定文件大小，扩宽性，等
 -- 创建临时表空间时需要保证指定的datafile目录是存在的
 -- 参数说明：
 -- tempfile '${temp data file path}'：数据文件路径
 -- size ${size}：数据文件大小
 -- autoextend on next ${size}：下次自动扩展大小
 -- maxsize unlimited：最大尺寸无限制
 create temporary tablespace 表空间 tempfile '/opt/oracle/oradata/orcl/数据文件.dbf' size 200m autoextend on next 10m maxsize unlimited;
  • 设置表空间自动扩展
 alter database datafile '/opt/oracle/oradata/oradata/orcl/数据文件.dbf' autoextend on;
  • 查看文件位置(表空间等一些文件的位置)
 select name from v$datafile;
  • 查看默认表空间
 select property_name ,property_value from database_properties where property_name like 'DEFAULT_%TABLE%';
  • 设置默认表空间
 alter database default tablespace 123;
 -- 默认临时表空间
 alter database default temporary tablespace 123;
  • 删除表空间
 drop tablespace 123;
 -- 如果表空间有数据，使用以下命令删除
 drop tablespace 123 including contents and datafiles;
表空间权限

建用户的时候通常都可以选择一个default tablespace，但是在没有授权的情况下该用户也无法往该表空间写数据，需要进行授权。

授权有全局授权和通过quota限制两种情况（quota配额可以防止某个用户过多使用某个表空间中的空间）

  • 授予用户全局表空间权限 - 通过命令的方式
 grant unlimited tablespace to username;
  • 授予用户全局表空间权限 - 通过角色的方式
 -- 查看resource角色底下带的权限，没有unlimited tablespace权限
 SELECT * from Dba_Sys_Privs s WHERE s.grantee='RESOURCE';
 -- 把resource角色授予用户
 grant resource to username;
 --查看用户拥有的权限，可以发现自己带上了unlimited tablespace（另外这个时候可以去看dba_ts_quotas，这样授权的用户没有体现出来）
 SELECT * from Dba_Sys_Privs s WHERE s.grantee='USERNAME' ;
  • 授予某个用户完全访问某个表空间权限
 -- 授权
 alter user ${username} quota unlimited on ${tablespace};
 -- 在授予完权限后查看dba_ts_quotas表
 -- max_bytes = -1，代表不受限制
 SELECT * from Dba_Ts_Quotas z WHERE z.username='USERNAME';
  • 授予某个用户有限的访问某个表空间权限
 -- 授权
 ALTER USER username QUOTA 1M ON rcat;
 -- 在授予完权限后查看dba_ts_quotas表
 -- max_bytes = 1M
 SELECT * from Dba_Ts_Quotas z WHERE z.username='USERNAME';
  • 删除用户表空间权限
 -- 全局
 revoke unlimited tablespace from username;
 -- 个别表空间
 ALTER USER username  QUOTA 0 ON rcat
角色相关

Oracle三种标准角色，connect role(连接角色)、resource role(资源角色)、dba role(数据库管理员角色)

  1. connect role：临时用户，特指不需要建表的用户，通常只赋予他们connect role。 connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。 拥有connect role 的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym)、回话(session)和其他 数据的链(link)

  2. resource role：更可靠和正式的数据库用户可以授予resource role。 resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

  3. dba role：dba role拥有所有的系统权限 包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有

  • 查看所有角色
 select * from dba_roles;
  • 查看当前用户被激活的全部角色
 select * from session_roles;
  • 查看当前用户被授予的角色和信息
 select * from user_role_privs;
  • 查看某个用户所拥有的角色
 select * from dba_role_privs where grantee = 'username';
  • 查看某个角色所拥有的权限
 select * from dba_sys_privs where grantee = 'CONNECT';
  • 创建角色

创建的角色可以由表或系统权限或者两者的组合构成

 -- 创建角色
 create role myRole;
 -- 授权角色
 -- 如使myRole获得了在mytable中使用select进行查询的权限
 grant select on mytable to myRole;
 -- 再比如为角色赋予创建会话的权限
 grant create session to myRole;
 -- 删除角色
 drop role myRole;
  • 授予用户角色
 -- 切换到对应的PDB下
 alter session set container=PDB1;
 show pdbs;
 -- 授予dba、resource、connect角色
 grant dba,resource,connect to pdb1;
 -- 收回某个用户的某个角色
 revoke dba,resource,connect from pdb1;
 -- 查看用户角色
 select * from dba_role_privs where grantee = 'PDB1' and granted_role in ('DBA','RESOURCE','CONNECT')
  • 授予角色权限
 -- create seesion 用户登陆会话权限
 -- create table 用户建表权限
 -- create sequence 用户创建序列权限
 -- create view 用户创建视图权限
 -- create procedure 用户创建存储过程权限
 -- create tablespace 用户创建表空间权限
 grant create session,create table to role_name;
  • 撤销角色权限
 revoke create table from role_name;
用户相关
  • 查看所有用户信息
 select * from all_users;
  • 查看当前用户的信息
 select * from user_users;
  • 创建用户，对于普通用户名，用户创建的普通用户名必须以C##（或c##）开头。
 create user c##svc_res identified by 123;
 -- 创建用户并指定表空间
 -- default tablespace ${tablespace}：默认表空间
 -- temporary tablespace ${talbespace}：临时表空间
 create user tssh identified by "XXXXX" default tablespace TSSH_15_DATA_MIN temporary tablespace TEMP;
  • 更改用户密码
 -- 如果密码带有字符需要加上双引号
 alter user pdb identified by 321
  • 查看当前用户
 show user;
  • 删除用户
 -- cascade操作需要谨慎，cascade代表代表着联级删除用户名下所有的表和视图
 drop user c##svctest cascade;
  • 创建一个模式

Oracle是不支持创建自定义模式的，想要创建模式的话只能新建一个用户，每个用户会有一个默认的和用户名相同的模式

 CREATE SCHEMA "svc_test" AUTHORIZATION SYSTEM;
  • 查看当前有哪些用户正在使用数据
 SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
 from v$session a, v$sqlarea b
 where a.sql_address = b.address order by cpu_time/executions desc;
  • 锁定与解锁用户
 alter user itcast account lock;
 alter user itcast account unlock;
  • 设置用户空间
 -- 设置用户的空间配额
 -- quota 容量的意思
 -- unlimited 无限制
 alter user itcast quota unlimited on pdb1;

  • 设置用户失效

    • 旨在要求使用方强制修改密码，提高密码安全性和复杂度，避免由我方运维人员知晓
 alter user testrac password expire;
 select username,account_status from dba_users where oracle_maintained='N';
  • 查看用户所属的profile
 SELECT username,PROFILE FROM dba_users;
  • 查看指定概要文件(default)的密码有效期设置
 SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
  • 修改密码有效期
 -- 影响所有用户
 ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
  • 修改用户所属的profile
 -- 修改用户所属profile
 -- 修改单个用户的密码有效期可以通过新建profile，然后修改用户所属的profile实现
 ALTER user test profile PASSWORD_UNLIMIT_PROFILE;
权限相关
  • 基本权限查询
 --当前用户所拥有的全部权限
 select * from session_privs;
 --当前用户的系统权限
 select * from user_sys_privs;
 --当前用户的对象权限
 select * from user_tab_privs;
 --查询某个用户所拥有的系统权限
 select * from dba_sys_privs where grantee='DBA';
 --查看角色(只能查看登陆用户拥有的角色)所包含的权限
 select * from role_sys_privs;
  • 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
 select * from v$pwfile_users;
  • 查询所有的用户级别权限
 select *
 from system_privilege_map
 order by name;
  • 授予用户权限
 -- create seesion 用户登陆会话权限
 -- create table 用户建表权限
 -- create sequence 用户创建序列权限
 -- create view 用户创建视图权限
 -- create procedure 用户创建存储过程权限
 -- create tablespace 用户创建表空间权限
 -- unlimited teblespace 用户无限表空间使用权限
 grant create session,create table to user_name;
  • 查看Oracle提供的系统权限
 select name from sys.system_privilege_map
  • 查看一个用户的所有系统权限(包含角色的系统权限)
 select privilege from dba_sys_privs where grantee='SCOTT'
 union
 select privilege from dba_sys_privs where grantee in (
     select granted_role from dba_role_privs where grantee = 'SCOTT'
 );
  • 查看当前用户可以访问的所有数据字典的视图
 select * from dict where comments like '%grant%';
  • 查看当前数据库全称
 select * from global_name;
其他设置
  • 查看表字段
 #desc ${table_name}
  • 命令行相关设置
 -- 显示一行多少字符
 show line
 -- 设置一行多少字符
 set linesize 1000
 -- 设置是否显示报表标题
 set hea on
 -- 显示每页行数
 show pages
 -- 设置每页显示行数
 set pages 50
 -- 设置输出结果是否滚动
 set pause on/off
 -- 设置滚动时自定义的提示
 set pause 'Press <Enter> to continue'
 ​
avatar
文章
阅读
粉丝