oracle数据库日常管理
/*
why not 维护
*/
-- 数据库管理
$ lsnrctl status -- 监听状态
$ lsnrctl start -- 启动监听
$ lsnrctl stop -- 停止监听
$ sqlplus / as sysdba -- 使用sys用户s连接库
startup; -- 启动数据库实例
shutdown; -- 关闭数据库实例
SHUTDOWN IMMEDIATE -- 立即关闭数据库
-- 1、注意账户锁定、密码锁定
ALTER PROFILE default LIMIT password_life_time UNLIMITED; -- 密码永不过期
alter system set deferred_segment_creation=FALSE; -- 新建的空表给分配段
-- 2、数据文件数量限制
alter system set db_files=1024 scope=spfile;
-- 3、开启审计 注意 系统表空间
-- 全局
show user -- 查看当前用户
select username from dba_users; -- 所有用户
archive log list -- 归档相关信息
select service_id, name,pdb from v$services; -- 查看service情况
select INSTANCE_NAME from v$instance; -- 实例名
select * from v$version -- 版本信息,核心版本信息
select userenv('language') from dual; -- 服务端字符集
show pdbs -- 显示所有pdb 和状态
show con_name; -- 显示当前pdb数据库
select name,cdb from v$database; -- 检查db是否为cdb
alter session set container=CDB1PDB; -- 切换到指定pdb数据库
ALTER PLUGGABLE DATABASE cdb1pdb OPEN; -- 开启
ALTER PLUGGABLE DATABASE cdb1pdb CLOSE; -- 关闭
create pluggable database cdb1hf admin user hf identified by hf file_name_convert=('/u01/app/oracle/oradata/cdb1/pdbseed/','/data/hf/'); -- 使用seed模板创建pdb
ALTER PROFILE default LIMIT password_life_time UNLIMITED; --密码永不过期
alter system set deferred_segment_creation=FALSE; --新建的空表给分配段
alter system set audit_sys_operations=true scope=spfile; --sys用户审计
scope=spfile -- 仅仅更改spfile里面的记载,不更改内存,也就是不立即生效,而是等下次数据库启动生效有一些参数只允许用这种方法更改
scope=memory -- 仅仅更改内存,不改spfile。也就是下次启动就失效了
scope=both -- 内存和spfile都更改,不指定scope参数,等同于scope=both
-- 调整Oracle的内存参数 asmm
show parameter sga
alter system set sga_max_size=819200M scope=spfile;
alter system set sga_target=819200M scope=spfile;
set serveroutput on; -- 输出PL/SQL块的执行结果
select * from gv$resource_limit; -- 查看process和sessions,更方便
select * from v$sgainfo;
alter system set processes=1500 scope=spfile; -- sessions的参数是processes派生的不需要单独调整
-- 创建表空间(在12c版本下需要在每一个pdb中建立表空间,否则用户无法创建)
CREATE TABLESPACE ESENSOFT DATAFILE '/home/oracle/esensoft.dbf' size 40m autoextend on next 50m maxsize 20480m EXTENT MANAGEMENT LOCAL;
-- 指定表空间名、指定表空间文件位置、开启表空间自增长、每次增长50兆、最大为20480兆
-- 删除用户下所有
DROP USER cmd CASCADE;
-- 删除表空间及其数据文件
DROP TABLESPACE cmd INCLUDING CONTENTS AND DATAFILES;
-- 临时表空间
-- 临时表空间数据文件信息
select * from dba_temp_files
select tablespace_name,file_name,bytes / 1024 / 1024 file_size,autoextensible from dba_temp_files
-- 临时表空间free空间信息,需要参考上条命令信息
SELECT TABLESPACE_NAME, FREE_SPACE / 1024 / 1024 / 1024 AS "FREE SPACE(G)" FROM DBA_TEMP_FREE_SPACE
-- WHERE TABLESPACE_NAME = TEMP --'&tablespace_name';
select * from V$TEMP_EXTENT_POOL
-- 创建新的临时表空间
CREATE TEMPORARY TABLESPACE CDM_TEMP
TEMPFILE '/u01/app/oracle/oradata/ORCL/cdm_temp01.dbf' SIZE 100M AUTOEXTEND ON;
-- 数据文件自动扩展
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORCL/cdm_temp01.dbf' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
-- 添加新的数据文件
ALTER TABLESPACE CDM_TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/cdm_temp02.dbf' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
-- 切换用户的临时表空间
ALTER USER AH_SJPT_CDM TEMPORARY TABLESPACE CDM_TEMP;
-- 表空间
-- 自增序号
select 'alter tablespace vmaccount add datafile ''/u01/app/oracle/oradata/ORCL/vmaccount_'||rownum||'.dbf'' size 100M autoextend on next 40m;' from dba_data_files
create tablespace huangshan_ods datafile '/data/huangshan_ods01.dbf' size 100M autoextend on next 40m;
alter tablespace huangshan_ods add datafile '/data/huanshan_ods02.dbf' size 100m autoextend on next 40m;
ALTER DATABASE DATAFILE '/data/huanshan_ods02.dbf' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces; -- 查看表空间列表
select * from v$tablespace; -- 表空间信息
select file_name,tablespace_name from dba_data_files; -- 表空间数据文件所处位置
select tablespace_name,file_name,bytes from DBA_DATA_FILES;
select default_tablespace from dba_users where username='SHOW'; -- 指定用户默认表空间
drop tablespace HFGAOXINQU_ODS including contents; -- 删除表空间及其内容
drop tablespace HFGAOXINQU_ODS including contents; -- 删除表空间及其内容
drop tablespace ANHUI_ODS including contents and datafiles; -- 删除表空间及其数据文件
drop tablespace ANHUI_ODS_NEW including contents and datafiles;
alter database datafile '/home/oracle/esensoft.dbf' offline drop -- 删除数据文件和表空间的关系
select name, bytes/1024/1024/1024 as G from v$datafile -- 数据文件大小
show parameter db_block_size -- 显示默认数据块大小
show parameter db_files; -- 查看数据文件上限
alter system set db_files=1024 scope=spfile; -- 修改数据文件上限,修改参数文件
-- SYSAUX 表空间
-- 清理审计记录 SYSAUX ORA-01652
begin
dbms_audit_mgmt.clean_audit_trail(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => FALSE);
end;
-- 查看SYSAUX表空间信息-V$SYSAUX_OCCUPANTS
select OCCUPANT_NAME, SCHEMA_NAME,MOVE_PROCEDURE ,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;
-- 数据文件使用情况
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tablespace_name'
alter table OE_DOCTOR move tablespace FENGYANGXIAN_ODS_NEW; -- 数据表切换表空间
select a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小G",
free / (1024 * 1024 * 1024) "表空间剩余大小G",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小G"
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;
-- 用户
create user test identified by Gxrj2020 default tablespace PREDATABASE; -- 创建用户
alter user test quota unlimited on PREDATABASE; -- 授予用户使用指定表空间
alter user ah_sjpt_cdm identified by ah_sjpt_cdm; -- 修改用户密码
alter user sys identified by ahjyadmin2021;
grant dba to c##show; -- 授予用户dba权限
alter user sys identified by ahjy2021; -- 修改用户口令
drop user XXXX cascade; -- 删除用户,及其对象
drop user ANHUI_ODS_NEW cascade;
SELECT username, account_status FROM dba_users; -- 查询用户状态（密码过期 锁定）
alter user FENGYANGXIAN_ODS account unlock; -- 解锁指定用户
select * from dba_users t where t.username like '%ODS'; -- 查看所有用户信息
select table_name from user_tables; -- 查看当前用户下所有表信息 num_rows字段代表表内数据量
-- 用户密码过期被锁定 根据实际情况修改密码
ORA-28001: the password has expired
ORA-28000: the account is locked
select * from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
alter user c##mh_new identified by 123456;
alter user c##mh_new account unlock;
-- 删除用户
select sid,serial# from v$session where username='AH_ZHIKONG';
alter system kill session '11090,29827';
drop user ah_zhikong cascade;
-- 批量删除一个用户的session
declare
begin
for temp in (select username, sid, serial#
from v$session s
where s.USERNAME = 'QMJK109_ODS_340000') loop
dbms_output.put_line('alter system kill session ''' ||
to_char(temp.sid) || ',' || to_char(temp.serial#) ||
''';');
end loop;
end;
ORA-28040 -- 更新驱动一样的
# sqlnet.ora.rac01 Network Configuration File: /u01/app/12.2.0.1/grid/network/admin/sqlnet.ora.rac01
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
-- 修改Oracle的用户名：
-- 1、用sysdba角色账号进入,然后查询有哪些用户：
SELECT * FROM user$
-- 2、找到需要修改的用户（user#字段是唯一标识）
SELECT * FROM user$ WHERE user#=241
-- 3、修改需要更改的用户名
UPDATE USER$ SET NAME='FEIDONG_ODS_NEW' WHERE user#=241;
COMMIT;
-- 4、强制刷新
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH SHARED_POOL;
-- 5、再将新的用户名对应的密码修改下（否则无法登录）
ALTER USER FEIDONG_ODS_NEW IDENTIFIED BY FEIDONG_ODS_NEW2021;
-- JDBC
-- SID
jdbc:oracle:thin:@host:port:SID
-- ServiceName
jdbc:oracle:thin:@//host:port/service_name
-- TNSName
jdbc:oracle:thin:@TNSName
-- 表
truncate table OT_JBGW_DIABETES_FOLLOWUP; -- 删除表内所有数据(不可回滚)
alter table ot_jbgw_hyper_followup rename column JBGW_HYP_FOLLOWUP_007 to OT_JBGW_HYP_FOLLOWUP_007; -- 修改字段名
select * from v$logfile -- 日志文件信息
select * from dba_data_files; -- 数据文件信息
select * from v$controlfile; -- 控制文件信息
select con_id,name from v$containers; -- pdb信息
select name from v$database; -- 查看全局数据库名
select instance_name from v$instance; -- 查看数据库实例名
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; -- 数据库当前时间
analyze table my_table compute statistics for table for all indexes for all columns; -- 分析指定表字段和索引
analyze table my_table delete statistics; -- 删除指定表分析数据
-- 添加新字段替换旧的字段,在数据没办法更新时使用
alter table OUTPATIENT_PRESCRIPTION add PERFORM_DATE_1 date
alter table OUTPATIENT_PRESCRIPTION rename column PERFORM_DATE to PERFORM_DATE_2;
alter table OUTPATIENT_PRESCRIPTION rename column PERFORM_DATE_1 to PERFORM_DATE;
-- 指定表大小
SELECT segment_name AS TABLENAME,
BYTES B,
BYTES / 1024 KB,
BYTES / 1024 / 1024 MB,
ROUND(BYTES / 1024 / 1024 / 1024, 2) GB
FROM user_segments
where segment_name like 'OT_JTYS_P%'
-- 块大小
SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size'
-- 更新统计信息 收集表对象的统计信息
SELECT ' CALL DBMS_STATS.GATHER_TABLE_STATS(''VMACCOUNT'', ''' || TABLE_NAME || ''');'
FROM DBA_TABLES TT
WHERE TT.OWNER = 'VMACCOUNT'
-- 计算表对象占用的空间信息
SELECT OWNER OWNER_NAME
,TABLE_NAME TABLE_NAME
,TABLESPACE_NAME TABLESPACE_NAME
,BLOCKS ACTUAL_BLOCKS
,BLOCKS + EMPTY_BLOCKS TOTAL_BLOCKS
,ROUND((BLOCKS + EMPTY_BLOCKS) * 8192/(1024*1024))
"TABLE_SIZE[MB]"
FROM DBA_TABLES
WHERE OWNER='VMACCOUNT'
-- AND TABLE_NAME=&TABLE_NAME
-- 主键
alter table OT_JTYS_SIGN
add constraint SP_NO_PK primary key (OT_JTYS_SIGN_NO, OT_JTYS_SP_NO)
alter table OT_JTYS_SIGN
drop constraint PK_OT_JTYS_SIGN cascade;
--
all_col_comments
all_tab_cols -- 字段
-- 查看某张表的主键（全库） user_constraints（单用户）
select * from dba_constraints dc where dc.CONSTRAINT_TYPE = 'P' and dc.TABLE_NAME = 'OT_JTYS_SI'
-- 索引
-- 重建索引
select 'alter index ' || index_name || ' rebuild tablespace AH_ZHIKONG; '
from user_indexes
-- 分配给一个表的物理空间数量,而不管空间是否被使用
select segment_name, SUM(round(BYTES / 1024 / 1024, 2)) || 'M'
from user_segments
GROUP BY segment_name;
-- 实际使用的空间
-- 先统计分析表
select 'analyze table ' || TABLE_NAME || ' compute statistics; '
from user_tables;
select t.TABLE_NAME,
round(t.num_rows * t.avg_row_len / 1024 / 1024, 2) "实际大小(M)"
from user_tables t
-- Analyze
-- 全分析
analyze table my_table compute statistics for table for all indexes for all columns;
-- 指定表
analyze table my_table compute statistics for table;
-- 指定所有字段
analyze table my_table compute statistics for all columns;
-- 指定有索引的字段
analyze table my_table compute statistics for all indexed columns;
-- 指定索引
analyze table my_table compute statistics for all indexes;
-- 删除分析数据
-- 行移动
-- 更改分区表的分区键值,意味着要删除记录并重新插入一条 新的记录,这会引起记录（Record）的移动,记录的Rowid会改变,相关索引需要进行维护
select 'alter table ' || t.TABLE_NAME || ' enable row movement;'
from user_tables t
-- 权限
-- procedures创建运行权限
GRANT CREATE ANY PROCEDURE TO MONKEY; -- 創建,查看,替換的權限
GRANT EXECUTE ANY PROCEDURE TO MONKEY; -- 執行和查看的權限
GRANT ALTER ANY PROCEDURE TO MONKEY; -- 編譯的權限
GRANT DEBUG ANY PROCEDURE TO MONKEY; -- 查看和調試的權限
GRANT CREATE ANY TYPE TO cd_new_01;
GRANT EXECUTE ANY TYPE TO cd_new_01;
GRANT ALTER ANY TYPE TO cd_new_01;
-- 查自己的角色
select * from user_role_privs;
-- 查自己拥有哪些系统权限
select * from session_privs;
-- 查自己可以访问对象的权限
select * from user_tab_privs;
-- 同义词
-- 创建同义词
GRANT CREATE SYNONYM TO cd_new_01;
GRANT CREATE ANY SYNONYM TO cd_new_01;
-- 批量创建同义词
select 'create or replace synonym ' || TABLE_NAME || ' for ' || OWNER || '.' ||
TABLE_NAME || ' ;'
from user_tab_privs
group by OWNER, TABLE_NAME
-- 查看创建的别名
select * from user_synonyms t;
select * from all_synonyms t where t.OWNER = 'HUAIBEI_ODS_1';
-- 查询无效对象
-- 删除无效同义词
SELECT A.OWNER,
A.OBJECT_NAME,
A.OBJECT_TYPE,
A.CREATED,
A.LAST_DDL_TIME,
A.STATUS,
A.TIMESTAMP,
SYSDATE AS "查詢時間",
'drop synonym ' || OWNER || '.' || OBJECT_NAME || ';' as ddl_sql
FROM DBA_OBJECTS a
WHERE A.STATUS = 'INVALID'
and owner = 'TONGGUANQU_ODS_1'
-- 删除同义词
select 'drop synonym ' || TABLE_NAME || ';'
from user_tab_privs tt
where tt.TABLE_NAME like '%TEMP%'
group by TABLE_NAME
select 'drop synonym TONGGUANQU_ODS_1.' || SYNONYM_NAME || ';'
from dba_synonyms t
where not exists
(select 1 from dim_objects t1 where t.TABLE_NAME = t1.objects_name)
and t.OWNER = 'TONGGUANQU_ODS_1';
/*
oracle plsql 上对package 包执行重新编译时,超时无响应：(4021)time-out occurred while waiting to lock object
（1）存储过程编译没完成,或者编译失败时,oracle 数据库会自动把相应的存储过程锁起来；
（2）DBA_DDL_LOCKS视图中记录存储过程、表结构、视图、包等数据库对象锁的信息；
（3）V$ACCESS视图显示数据库中当前锁定的对象及访问它们的会话 。
*/
-- https://www.cnblogs.com/wsn-only/p/11196444.html
-- 查询锁定的包对应的SID号
select * from v$access t where t.OWNER = 'AH_SJPT_CDM';
select * from dba_ddl_locks t1 where t1.name = 'PKG_JTYS';
-- 根据SID查出会话信息
select SID, SERIAL# from v$session where sid in ('7503');
alter system kill session '7503,55467'
-- RMAN mount状态或归档模式 下运行
> rman target sys/oracle@10.10.27.120:1521/
RMAN> backup database format 'c:\data\bak_%U'; -- #全库备份
RMAN> backup tablespace feidong_ods format 'c:\data\bak_ts_%U'; -- #指定表空间
RMAN> backup current controlfile format 'c:\data\bak_cf_%U'; -- #备份控制文件
RMAN> configure controlfile autoautobackup off|on; -- #控制文件自动备份设置
RMAN> list backup of database; -- #查看全库备份 list:列出备份信息
RMAN> list backup of tablespace feidong_ods; -- #查看关于指定表空间的备份
RMAN> list backup of controlfile; -- #查看控制文件备份
RMAN> delete backupset 1; -- #删除备份 noprompt不提示 删除备份记录及其物理文件
RMAN> delete backup; -- #删除所有备份
RMAN> delete obsolete; -- #删除过期备份
RMAN> delete expired backup; -- #删除无效备份
RMAN> report need backup; -- #显示需要备份的文件
RMAN> report need backup tablespace feidong_ods; -- #检测指定表空间是否需要备份
RMAN> report obsolete; -- #查看过期备份
RMAN>
RMAN>
RMAN>
RMAN>
RMAN>show all; -- 显示recovery manager默认配置
/u01/app/oracle/product/12.2.0.1/db_1/dbs/ -- 实例初始化参数文件
cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
cp init.ora initcdb1.ora
common user -- 通用用户 以c##开头
local user -- 本地用户 在pdb中创建的用户
-- 每个pdb都是一个独立的单元,有自己的local user、表空间、数据文件,每个local user用户只能访问自己的pdb
-- 添加监听程序的参数（）
vim /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
-- awr报告
exec dbms_workload_repository.create_snapshot();
-- 隔几分钟后再执行一次,生成俩快照。
-- 这个间隔时间越长约好,越能说明问题。
sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql
-- oracle高水位回收
-- 查看表实际使用的数据块数
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) ZS
FROM OT_JBGW_PERSON;
alter table OT_JBGW_PERSON enable row movement;
alter table OT_JBGW_PERSON shrink space; --ORA-10631:SHRINK clause should not be specified for this object SOLUTION:
-- 该错误是由于您要收缩的对象上具有“基于函数”的索引而导致的。具有基于函数的索引的对象不能缩小。要回收空间,您需要在缩小对象*之后删除并读取基于函数的索引。
alter table OT_JBGW_PERSON disable row movement;
begin
for i in (SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable
FROM dba_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM dba_indexes
WHERE index_type LIKE ‘FUN%’
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND obj.tablespace_name = upper(‘&1’) and NVL(idx.cnt,0) < 1)
loop
execute immediate ‘alter table ‘||i.owner||’.’||i.table_name||’ enable row movement’;
execute immediate ‘alter table ‘||i.owner||’.’||i.table_name||’ shrink space’;
execute immediate ‘alter table ‘||i.owner||’.’||i.table_name||’ disable row movement’;
end loop;
end;
-- ####### 创建用户 CRATE USER ######
CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace | TEMPORARY TABLESPACE temp_tablespace]
PROFILE profile
QUOTA [intager K|M] | PASSWORD EXPIRE
ACCOUNT LOCK|UNLOCK
-- 指定默认表空间 指定默认临时表空间 指定用户资源文件 指定配额选项默认无限制 将密码置为过期登录时必须修改 锁定或解锁账号
-- ####### 修改用户 ALTER USER ######
ALTER USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace | TEMPORARY TABLESPACE temp_tablespace]
PROFILE profile
QUOTA [intager K|M] | PASSWORD EXPIRE
ACCOUNT LOCK|UNLOCK
-- 修改用户密码
ALTER USER user_name IDENTIFIED BY password;
GRANT CONNECT TO user_name IDENTIFIED BY password;
-- ###### 删除用户 DROP USER ######
DROP USER user_name [CASCADE]
-- 用户在数据库中创建了对象,必须指定cascade参数,将用户在数据库中创建的对象全部删除。被删除的用户不能处于连接的状态
-- ##### 管理用户会话 #####
-- 1、使用字典视图监视用户会话信息
select sid,serial#,username,status,logon_time,machine
from v$session
where username is not null;
-- 2、终止用户会话
alter system kill session 'SID,SERIAL#';
alter system kill session '8713,6928';
-- SID和SERIAL#能够标识唯一会话信息
-- ############## 权限 ##############
-- 系统权限
-- 1、oracle中的系统权限
-- 对整个oracle系统的操作权限 连接数据库、创建管理表或视图
-- 系统权限一般由数据库管理员赋予用户,并允许用户将被授予的权限赋予其它用户
select * from system_privilege_map; -- 查看全部系统权限列表
-- 2、授予系统权限
-- 一般授予权限由DBA完成的。其它用户必须有 CREATE ANY PRIVILEGE 系统权限
GRANT SYSTEM_PRIV [system_priv,...] TO {PUBLIC|role|user} [,{PUBLIC|role|user}]
[WITH ADMIN OPTION];
-- SYSTEM_PRIV指定系统权限,多个权限之间用逗号分隔 public全部用户 role角色 user指定用户
-- 3、显示系统权限
select * from dba_sys_privs where grantee='TEST'; -- 检索某个用户或角色拥有的系统权限
SELECT * FROM DBA_SYS_PRIVS T WHERE T.PRIVILEGE = 'CREATE TABLE' -- 检索某项权限分布的用户
select * from system_privilege_map;
select * from session_privs; -- 查看当前用户拥有的系统权限
select * from user_role_privs; -- 查看当前用户拥有的角色
-- 4、回收系统权限
REVOKE SYSTEM_PRIV[,SYSTEM_PRIV] FROM {PUBLIC | ROLE | USER} [,{user|role|public}]
revoke create table from show; -- 回收来自show用户的建表权限
-- 对象权限
-- 1、对象权限的分类
table alter delete index insert reference select update
view delete insert select update
dierctory rread
function execute
procedure execute
package execute
sequence alter select
-- 多种权限组合组合在一起可以用ALL权限,表示对该对象的全部权限
-- 2、授予对象权限
-- 对象权限是由对象的拥有者为其它用户授权,非对象的拥有者不得向其它用户 ,获权用户可以对对象进行相应操作
-- DBA用户可以把任何对象权限授予其它用户
GRANT object_privilege | ALL ON <schema.>object_name
TO {user_name | role_name | PUBLIC }
[WITH GRANT OPTION];
grant all on test.admisson_record_24h to show; -- 授予此表对象的所有对象权限
-- 3、显示对象权限
select * from dba_tab_privs where grantee='SHOW'; -- 指定用户或对象的全部对象权限
-- 4、对象权限的回收
REVOKE {object_priv [,object_priv] | ALL }
ON [schema.]object
FROM {user|role|PUBLIC}
-- 授权者只能从自己授权的用户哪里回收权限,被授权的用户基于之前权限创建的 过程、视图 将变为无效
-- 回收对象权限时,经过传递获得权限的用户将会被影响
-- ########## 角色 ############
-- 将一组相关权限授予某个角色,一组权限的集合
-- 系统预定义角色
-- 1、CONNECT
ALTER SESSION 修改会话
CREATE CLUSTER 建立聚簇
CREATE DATABASE LINK 建立数据库连接
CREATE SEQUENCE 建立序列
CREATE SESSION 建立会话
CREATE SYNONYM 建立同义词
CREATE VIEW 建立视图
CREATE MATERIALIZED VIEW 物化视图
CREATE TABLE 建立表
create procedure 建立存储过程
----------------------------------------------- 控制用户权限 -----------------------------------------------
---- 用户的系统权限
-- 一般开发权限需求
create session
create table
create view
create sequence
create procedure
-- 创建表空间
create tablespace jreey datafile 'c:\data\jreey01.dbf' size 1G autoextend on next 100M;
create user jreey identified by jreey;
-- 指定用户使用指定的表空间并限制使用容量 --限额
alter user jreey quota 100m on jreey;
-- 对容量使用不作限制
alter user jreey quota unlimited on jreey;
-- 给予用户建立会话的权限
grant create session to jreey;
-- 给予用户建表权限
grant create table to jreey;
---- 角色
-- 创建角色
create role manager
-- 为角色添加权限
grant create session to manager;
grant create table, create view to manager;
-- 将角色赋予用户
grant manager to jreey,tom;
-- 查看角色
select * from user_role_privs;
---- 对象权限
-- 不同的对象具有不同的对象权限
-- 对象的拥有者拥有全部权限
-- 对象的拥有者可以向外分配权限
-- 分配对象权限
grant select,update on employees to tom,jreey;
grant select,update on employees to public;
-- 使被授予用户具有分配权限的权利
grant select on employees to jreey with grant option;
---- 收回对象权限
revoke select on employees from jreey;
-- 官方文档
-- 数据库存储管理员指南 asm11g
https://docs.oracle.com/cd/B28359_01/server.111/b31107/preface.htm#OSTMG94043
http://10.110.0.129:9090/passport/
-- 创建用户
create tablespace jxkh_bass datafile '+DATA/ORCL/DATAFILE/jxkh_bass01.dbf' size 100m autoextend on next 40m;
create user jxkh_bass identified by jxkh_bass default tablespace jxkh_bass;
grant connect to jxkh_bass;
grant create session to jxkh_bass;
grant create table to jxkh_bass;
grant create view to jxkh_bass;
grant create sequence to jxkh_bass;
grant create procedure to jxkh_bass;
grant create synonym to jxkh_bass;
alter user jxkh_bass quota unlimited on jxkh_bass;
-- 回收用户
select sid,serial# from v$session where username='AH_ZHIKONG';
alter system kill session '11090,29827';
drop user ehr_dsq cascade;
-- DBMS_JOBS
-- 创建新的数据库任务
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(JOB => job, /*自动生成JOB_ID*/
WHAT => 'PR_CDM_DATE_GW;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => TRUNC(sysdate + 1) + 1 / 24, /*初次执行时间-下一个3分钟*/
INTERVAL => 'TRUNC(sysdate+1)+1/24' /*每隔1分钟执行一次*/);
commit;
end;
declare
v_sql varchar2(1000);
cursor c_jobs is
select job from user_jobs;
/*批量删除jobs*/
begin
for aa in c_jobs loop
select 'begin
dbms_job.remove(' || JOB || ');
end;'
into v_sql
from user_jobs
where job = aa.job;
execute immediate v_sql;
end loop;
commit;
end;
--
BEGIN
DBMS_JOB.BROKEN(808, FALSE); -- 开启job定时
DBMS_JOB.BROKEN(808, TRUE); -- 关闭job定时
END;
BEGIN
DBMS_JOB.NEXT_DATE(813,TO_DATE('2022/04/06 10:00:00','yyyy-MM-dd hh24:mi:ss')); -- 修改下次执行时间
END;
-- 手动sql调用job (直接调用job可以忽略开始时间)
begin
DBMS_JOB.RUN(813); /*40 job的id*/
end;
SYS.USER_JOBS is 'All jobs owned by this user';
JOB is 'Identifier of job. Neither import/export nor repeated executions change it.'; -- 作业是作业的“标识符”。无论是导入/导出还是重复执行都不会改变它。
LOG_USER is 'USER who was logged in when the job was submitted'; -- LOG_USER是“提交作业时登录的用户”；
PRIV_USER is 'USER whose default privileges apply to this job'; -- PRIV_USER是“默认权限适用于此作业的用户”；
SCHEMA_USER is 'select * from bar means select * from schema_user.bar '; -- SCHEMA_USER为“select*from bar”表示从SCHEMA_USER.bar中选择*；
LAST_DATE is 'Date that this job last successfully executed'; -- LAST_DATE是“此作业上次成功执行的日期”；
LAST_SEC is 'Same as LAST_DATE. This is when the last successful execution started.'; -- “最后一秒”与“最后一天”相同。这是最后一次成功执行开始的时间。“；
THIS_DATE is 'Date that this job started executing (usually null if not executing)'; -- 此_日期是“此作业开始执行的日期（如果未执行,则通常为空）”；
THIS_SEC is 'Same as THIS_DATE. This is when the last successful execution started.'; -- 本节与本节日期相同。这是最后一次成功执行开始的时间。“；
NEXT_DATE is 'Date that this job will next be executed'; --- 下一个_日期是“下一次执行此作业的日期”；
NEXT_SEC is 'Same as NEXT_DATE. The job becomes due for execution at this time.'; -- 下一秒与下一个日期相同。此时该作业将到期执行。“；
TOTAL_TIME is 'Total wallclock time spent by the system on this job, in seconds'; -- TOTAL_TIME是“系统在此作业上花费的总挂钟时间,以秒为单位”；
BROKEN is 'If Y, no attempt is being made to run this job. See dbms_jobq.broken(job).'; -- Breaked是“如果是,则未尝试运行此作业”。参见dbms_jobq.breaked（作业）
INTERVAL is 'A date function, evaluated at the start of execution, becomes next NEXT_DATE'; -- 间隔是“一个日期函数,在执行开始时计算,成为下一个_日期”；
FAILURES is 'How many times has this job started and failed since its last success?'; -- FAILURES是“自上次成功以来,此作业启动和失败了多少次？”；
WHAT is 'Body of the anonymous PL/SQL block that this job executes'; -- 什么是“此作业执行的匿名PL/SQL块体”；
NLS_ENV is 'alter session parameters describing the NLS environment of the job'; -- NLS_ENV是“改变描述作业NLS环境的会话参数”；
MISC_ENV is 'a versioned raw maintained by the kernel, for other session parameters'; -- MISC_ENV是“内核维护的用于其他会话参数的版本化原始数据”；
INSTANCE is 'Instance number restricted to run the job'; -- 实例是“限制运行作业的实例号”
-- DBMS_job
dba_jobs 表中字段含义：
JOB 任务的唯一标识码
LOG_USER 提交任务的用户
PRIV_USER 赋予任务权限的用户
SCHEMA_USER 对用户作语法分析的用户模式
LAST_DATE 最后一次成功执行任务的时间
LAST_SEC 最后一次成功执行任务的时间的时分秒
THIS_DATE 正在执行的任务的开始时间,若没有则为空
THIS_SEC 正在执行的任务的开始时间的时分秒,若没有则为空
NEXT_DATE 下一次执行定时任务的时间
NEXT_SEC 下一次执行定时任务的时间的时分秒
TOTAL_TIME 执行当前任务所需要的时间,单位：秒
BROKEN 标志参数,Y表示任务中断,以后不会再运行
INTERTAL 计算下一次执行定时任务的时间表达式
FAILURES 当前定时任务执行失败的总次数
WHAT 执行任务的PL/SQL代码块
NLS_ENV 任务执行的NLS会话设置
MISC_ENV 定时任务运行的其他一些参数设置
INSTANCE 标识当前任务运行是否受限,0 没有受限
select t.JOB,
t.LOG_USER,
t.PRIV_USER,
t.SCHEMA_USER,
to_char(t.LAST_DATE, 'yyyy-mm-dd hh24:mi:ss') as "成功执行时间",
t.LAST_SEC as "成功执行时分秒",
to_char(t.THIS_DATE, 'yyyy-mm-dd hh24:mi:ss') as "正在执行的任务的开始时间",
t.THIS_SEC as "正在执行时分秒",
to_char(t.NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss') as "下一次执行任务时间",
t.NEXT_SEC as "下一次时分秒",
t.TOTAL_TIME,
t.BROKEN as "任务有效性",
t.INTERVAL,
t.FAILURES as "失败的总次数",
t.WHAT,
t.nls_env,
t.MISC_ENV,
t.INSTANCE
from dba_jobs t
ora-28040
-- 没有匹配的验证协议
$ORACLE_HOME/network/admin/sqlnet.ora
