Home > Enterprise >  Can't access dictionary views in Oracle Database 19 with system user
Can't access dictionary views in Oracle Database 19 with system user

Time:02-16

i have a problem where i can not access the dictionary views like ALL_TABLES, ALL_USERS etc.

I have an Azure-VM with the Oracle Database 19.3.0.0 EE - Gen1 image installed.

How did i setup the machine:

  1. set ORACLE_BASE, ORACLE_HOME, ORACLE_SID environment Variables
  2. created dir /u01/app/oracle/oradata
    /u01/app/oracle/admin/DBname/adump
    /u01/app/oracle/fast_recovery_area
  3. created following /u01/app/oracle/product/19.0.0/dbhome_1/dbs/initDBName.ora
    db_name='<DBName>'
    memory_target=1G
    processes = 150
    audit_file_dest='$ORACLE_BASE/admin/<DBName>/adump'
    audit_trail ='db'
    db_block_size=8192
    db_domain=''
    db_recovery_file_dest='$ORACLE_BASE/fast_recovery_area'
    db_recovery_file_dest_size=2G
    diagnostic_dest='$ORACLE_BASE'
    dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
    open_cursors=300
    remote_login_passwordfile='EXCLUSIVE'
    undo_management=AUTO
    undo_tablespace='undotbs'
    # You may want to ensure that control files are created on separate physical
    # devices
    control_files = (ora_control1)
    compatible ='11.2.0'
  1. i ran the sqlplus commands
    sqlplus / as sysdba
    startup nomount;
  1. created the Database with following command:
    CREATE DATABASE <DBName>
    USER SYS IDENTIFIED BY <Password>
    USER SYSTEM IDENTIFIED BY <Password>
    LOGFILE GROUP 1 ('/u01/logs/redo01a.log','/u01/logs/redo01b.log') SIZE 100M,
           GROUP 2 ('/u01/logs/redo02a.log','/u01/logs/redo02b.log') SIZE 100M,
           GROUP 3 ('/u01/logs/redo03a.log','/u01/logs/redo03b.log') SIZE 100M
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE '/u01/app/oracle/oradata/<DBName>/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    SYSAUX DATAFILE '/u01/app/oracle/oradata/<DBName>/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/<DBName>/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/<DBName>/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
    UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/<DBName>/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
    USER_DATA TABLESPACE usertbs
      DATAFILE '/u01/app/oracle/oradata/<DBName>/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

And now i want to access the ALL_USERS View but get this error:

ORA-00942: table or view does not exist

Same happens with all other dictionary tables.

CodePudding user response:

as @AlexPoole wrote i had to run the sql scripts as described in step 11

  • Related