• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

linux环境处理Oracle 19c一些问题记录

互联网 diligentman 3天前 7次浏览

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:

    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;
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;


程序员灯塔
转载请注明原文链接:linux环境处理Oracle 19c一些问题记录
喜欢 (0)