linux環境重啟Oracle 19c資料庫

1.切換到Oracle環境：su - oracle
2.查看監聽狀態：lsnrctl status
3.lsnrctl stop
4.以DBA權限登錄
sqlplus /nolog
conn /as sysdba
5.SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
6.lsnrctl status
lsnrctl start
sqlplus /nolog
conn /as sysdba
startup
7.服務器重啟時，pdb默認是不啟動的，先將監聽啟動，然后需要在超級管理下執行alter pluggable database ORCLPDB1 open; 啟動pdb

在PDB模式下創建表空間

切換到PDB
alter session set container=ORCLPDB1;
創建表空間
create tablespace XXXX datafile
‘/opt/oracle/oradata/xxx.dbf’ size 10M autoextend on next 100M maxsize 34359721984 ;
增加表空間檔案
alter tablespace XXXX add datafile
‘/opt/oracle/oradata/xxx.dbf’ size 10M autoextend on next 100M maxsize 34359721984 ;

在PDB模式下新建用戶

說明：用戶名在sql陳述句中一定要大寫，否則創建后無法登錄，大寫的用戶名在實際登錄時可以小寫，
alter session set container=ORCLPDB1;
CREATE USER “XXXX” IDENTIFIED BY “xxxx” DEFAULT TABLESPACE “XXXX” TEMPORARY TABLESPACE “TEMP”;

GRANT “PDB_DBA” TO “XXXX” WITH ADMIN OPTION;

ALTER USER “XXXX” DEFAULT ROLE “PDB_DBA”;

ALTER USER “XXXX” QUOTA UNLIMITED ON “SJGC”;

GRANT CREATE PUBLIC DATABASE LINK, CREATE ANY PROCEDURE, CREATE TABLE, CREATE ANY TABLE, SELECT ANY TABLE, CREATE ANY VIEW TO “XXXX” WITH ADMIN OPTION;

GRANT ALTER USER TO “XXXX”
權限賦予：
grant create job to XXXX;
grant manage scheduler to XXXX;
grant create any table to XXXX;
grant create any view to XXXX;
grant create any procedure to XXXX;
grant connect,resource to XXXX;
grant unlimited tablespace to XXXX;
grant create tablespace to XXXX;
grant connect to XXXX;
grant resource to XXXX;
grant alter system to XXXX;
grant create any table to XXXX;
grant create any view to XXXX;
grant create database link to XXXX;
grant create external job to XXXX;
grant create job to XXXX;
grant create table to XXXX;
grant create view to XXXX;
grant debug any procedure to XXXX;
grant debug connect session to XXXX;
grant unlimited tablespace to XXXX;
grant create any sequence to XXXX;

修改資料庫時間格式

1. vi .bash_profile
2. 按i進入編輯模式
   PATH= P A T H : PATH: PATH:HOME/bin
   export PATH
   export ORACLE_HOME=/usr/lib/oracle/19.8/client64
   export TNS_ADMIN=/usr/lib/oracle/19.8/client64
   export TNS=/usr/lib/oracle/19.8/client64
   export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
   export LD_LIBRARY_PATH= O R A C L E H O M E / l i b : ORACLE_HOME/lib: ORACLEH?OME/lib:LD_LIBRARY_PATH
   PATH= P A T H : PATH: PATH:HOME/bin: O R A C L E H O M E / b i n : ORACLE_HOME/bin: ORACLEH?OME/bin:ORACLE_HOME/lib
   export NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’
3. source .bash_profile

定義DBMS_JOB

declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job,
WHAT => ‘INSCL;’,
NEXT_DATE => to_date(‘2021-06-09 01:00:00’,‘yyyy-MM-dd hh24:mi:ss’),
INTERVAL => ‘TRUNC(sysdate+1)+1/24’
);
commit;
end;

JOBS調度作業

JOBS調度作業不好使
查看job行程數show parameter job_queue_processes;
alter system set job_queue_processes=50;

查看JOB情況

用戶名大寫
select t.job_name,
t.job_type,
t.JOB_ACTION,
t.job_class,
t.schedule_type,
t.enabled,
t.state
from dba_scheduler_job_roles t
where t.owner = ‘XXXX’
order by t.enabled;