Increase open_cursors later

[teceP]

Since we do not have enough privilege after executing initial scripts, how can we update the open_cursors value?

For an content import, I want to execute following command: "alter system set open_cursors = 1000 scope=both;" And change it back to a lower value after import has finished.

If I execute this command in a initial script on db startup, the log says open_cursors=300 anyways:

sh-4.4$ grep -rnw . -e "open_cursors" | grep "log"
./diag/rdbms/xe/XE/trace/alert_XE.log:92:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:323:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:597:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:1160:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:1681:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:2096:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:3108:  open_cursors             = 300
./diag/rdbms/xe/XE/trace/alert_XE.log:3352:ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;
./diag/rdbms/xe/XE/alert/log.xml:516: <txt>  open_cursors             = 300
./diag/rdbms/xe/XE/alert/log.xml:1768: <txt>  open_cursors             = 300
./diag/rdbms/xe/XE/alert/log.xml:3144: <txt>  open_cursors             = 300
./diag/rdbms/xe/XE/alert/log.xml:5825: <txt>  open_cursors             = 300
./diag/rdbms/xe/XE/alert/log.xml:8617: <txt>  open_cursors             = 300
./diag/rdbms/xe/XE/alert/log.xml:10827: <txt>  open_cursors             = 300
./diag/rdbms/xe/XE/alert/log.xml:15983: <txt>  open_cursors             = 300

Iam also not able to proof if the value has been taken over with other commands like "SELECT value FROM v$parameter WHERE name = 'open_cursors';", because of insufficent privileges.

Any suggestion?

[gvenzl]

Hi @teceP,

Thanks for using these images!

With which user are you trying to increase open_cursors? The administrative accounts have the passwords that you specify with -e ORACLE_PASSWORD=, so you can definitely use these to set the parameter.

Could you share the script and the output that sets open_cursors?

Also note that in the output above, there is no more reference to open_cursors = 300 after the ALTER SYSTEM command. The output below that command is all from the log.xml file but no longer from the alert_XE.log file.

[teceP]

Thanks for your response!

Its working now but I guess I can use any string I want as password:

sh-4.4$ sqlplus system as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Sep 5 14:58:27 2022
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password: (abc1)

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> ALTER SYSTEM SET open_cursors = 1234 SCOPE=BOTH;

System altered.

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE
--------------------------------------------------------------------------------
1234

SQL> exit
Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
sh-4.4$ sqlplus system as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Sep 5 14:58:46 2022
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password: (qwertz)

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> ALTER SYSTEM SET open_cursors = 4321 SCOPE=BOTH;

System altered.

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE
--------------------------------------------------------------------------------
4321

In my helmcharts, I defined actually a complete different password. My Dev-Envs:

            - name: APP_USER
              value: CONTENT
            - name: APP_USER_PASSWORD
              value: CONTENT
            - name: ORACLE_PASSWORD
              value: CONTENT
            - name: ORACLE_DATABASE
              value: PTS

Any ideas why I have access with any password i use? Iam using gvenzl/oracle-xe:18-slim.

[gvenzl]

Hi @teceP,

Yes, because you are using as sysdba which is a system privilege allowing any OS user in the right group to log into the database.

See this post for more explanation on the matter. If you remove as sysdba you will see that now the username/password needs to match.

Hope this helps!