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 说明： ORA-01012:not logged on的解决办法 这种情况一般是shutdown数据库后有多余的进程 ps aux查看linux下所有进程，找到Oracle相关进程，依次执行 kill -9 PID 杀进程，然后在Oracle环境下再次执行startup;即可启动

在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: ORACLEHOME/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: ORACLEHOME/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; 先将job_queue_processes=0： alter system set job_queue_processes=0; 然后再执行，请根据实际情况定义数值，不宜过大： 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;

JOB不好使的问题记录

ps -ef | grep cjq

正常情况下可以查看到下图的状态，如果只有第二行的grep说明JOB没启动

解决办法就是将数据库重启，重启后就好使了