Oracle 23ai scripts Tips
Some tips for oracle 23ai.
new-developer-role
set serveroutput on format wrapped;
DECLARE
procedure printRolePrivileges(
p_role in varchar2,
p_spaces_to_indent in number) IS
v_child_roles DBMS_SQL.VARCHAR2_TABLE;
v_system_privs DBMS_SQL.VARCHAR2_TABLE;
v_table_privs DBMS_SQL.VARCHAR2_TABLE;
v_indent_spaces varchar2(2048);
BEGIN
-- Indentation for nested privileges via granted roles.
for space in 1..p_spaces_to_indent LOOP
v_indent_spaces := v_indent_spaces || ' ';
end LOOP;
-- Get the system privileges granted to p_role
select PRIVILEGE bulk collect into v_system_privs
from DBA_SYS_PRIVS
where GRANTEE = p_role
order by PRIVILEGE;
-- Print the system privileges granted to p_role
for privind in 1..v_system_privs.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
v_indent_spaces || 'System priv: ' || v_system_privs(privind));
END LOOP;
-- Get the object privileges granted to p_role
select PRIVILEGE || ' ' || OWNER || '.' || TABLE_NAME
bulk collect into v_table_privs
from DBA_TAB_PRIVS
where GRANTEE = p_role
order by TABLE_NAME asc;
-- Print the object privileges granted to p_role
for tabprivind in 1..v_table_privs.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
v_indent_spaces || 'Object priv: ' || v_table_privs(tabprivind));
END LOOP;
-- get all roles granted to p_role
select GRANTED_ROLE bulk collect into v_child_roles
from DBA_ROLE_PRIVS
where GRANTEE = p_role
order by GRANTED_ROLE asc;
-- Print all roles granted to p_role and handle child roles recursively.
for roleind in 1..v_child_roles.COUNT LOOP
-- Print child role
DBMS_OUTPUT.PUT_LINE(
v_indent_spaces || 'Role priv: ' || v_child_roles(roleind));
-- Print privileges for the child role recursively. Pass 2 additional
-- spaces to illustrate these privileges belong to a child role.
printRolePrivileges(v_child_roles(roleind), p_spaces_to_indent + 2);
END LOOP;
EXCEPTION
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Got exception: ' || SQLERRM );
END printRolePrivileges;
BEGIN
printRolePrivileges('DB_DEVELOPER_ROLE', 0);
END;
/
Oracle Database 23ai - new-developer-role
Have a good work&life! 2024/07 via LinHong
Oracle 19c Linux 8 Install Tips
Some tips for oracle 19c in Linux 8 install.
PRVF-5311 When “cluvfy comp healthcheck” Or “cluvfy comp olr -allnodes” (Doc ID 2882152.1)
GI home needs to be patched with latest GI RU or scp tool needs a modification to workaround the issue.
Follow the below workaround in note: INS-06006 GI RunInstaller Fails If OpenSSH Is Upgraded to 8.x(Doc ID 2555697.1):
(if your unix administrator allows it)
Before installation, as root user: (please change the path if the location of your "scp" is not the same with below)
# Rename the original scp.
mv /usr/bin/scp /usr/bin/scp.orig
# Create a new file </usr/bin/scp>.
vi /usr/bin/scp
# Add the below line to the new created file </usr/bin/scp>.
/usr/bin/scp.orig -T $*
# Change the file permission.
chmod 555 /usr/bin/scp
After installation:
mv /usr/bin/scp.orig /usr/bin/scp
DBCA reported PRVG-11368 for SCAN verification (Doc ID 2898418.1)
srvctl conig scan
srvctl config scan_listener
host scan-ip-hostname
Bug 29529394 - DBCA/NETCA FAIL TO VERIFY SSH CONNECTIVITY [INS-06005] UNABLE TO GET SSH CONNECTIVITY DETAILS (Doc ID 29529394.8)
Issue occurred due to new OS version of OL8/RHEL8
The fix for 29529394 is first included in:
20.1.0
19.7.0.0.200414 (Apr 2020) OCW Release Update Revision(OCW RU)
RHEL8- DBCA fails with “[INS-06005] Unable to get SSH connectivity details” (Doc ID 3017836.1)
Set the environment variable as CV_ASSUME_DISTID=OEL7.9 and then launch DBCA again to create the database it will now be able to find the OS release version .
ORA-27106: System Pages Not Available To Allocate Memory (Doc ID 2254167.1)
[root@x10m01 ~]# cp /etc/sysctl.conf /etc/sysctl.conf_20240902
[root@x10m01 ~]# vi /etc/sysctl.conf
[root@x10m01 ~]# sysctl -p
vm.nr_hugepages = 156000
[root@x10m01 ~]# ssh x10m02
[root@x10m02 ~]# cp /etc/sysctl.conf /etc/sysctl.conf_20240902
[root@x10m02 ~]# vi /etc/sysctl.conf
[root@x10m02 ~]# sysctl -p
vm.nr_hugepages = 156000
[root@x10m02 ~]#
========================================
309 (GB) * 1024 / 2 (MB) ≒ 158720 (310)
309 : Total (GB)
2 : PageSize => 2048(KB)
========================================
Oracle Database 19c Installation On Oracle Linux 8 (OL8)
Have a good work&life! 2024/07 via LinHong
Oracle 23ai Linux 9 Install Tips
Some tips for oracle 23ai install.
Oracle 23c Shrink Bigfile tablespace Tips
Oracle database 23ai onward we can use the DBMS_SPACE package to shrink a bigfile tablespace to reclaim unused space.
Oracle 23c ock-free reservation Tips
Lock-Free Reservation这项特性可用于实现更细粒度的并发控制。 它的本质是相对于传统的行锁,能以更细的粒度(即列值级别)进行锁定,从而减少锁争用,提高并发性能。
Oracle 23c SSB setting Tips
A small data pump export file for the SSB schema that can be used on a laptop
Oracle 23c SQL Diagnostic Report Tips
SQL Diagnostic Report
DBMS_SQLDIAG has a new function called REPORT_SQL
Oracle 23c set user password limit Tips
There is below errors while set simple password for test user.
ORA-28003: The password chosen did not meet the required complexity rules set
by your organization.
ORA-20000: password length less than 9 characters
Oracle 23c dbcs basic Tips
Some info
Oracle 23c create acfs in dbcs Tips
Create ACFS for oracle user.
Oracle CONTROL_FILE_RECORD_KEEP_TIME Tips
CONTROL_FILE_RECORD_KEEP_TIME
CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused.
Note:This parameter applies only to records in the control file that are circularly reusable (such as archive log records and various backup records). It does not apply to records such as data file, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.
Oracle NLS_DATE_FORMAT Tips
Setting NLS_DATE_FORMAT in the shell environment variable using setenv or export command is not effective. When querying dates from SQL*Plus, it does not reflect the NLS_DATE_FORMAT.
% setenv NLS_DATE_FORMAT 'YYYYMMDD'
% sqlplus / as sysdba
SQL> select sysdate from dual;
SYSDATE
---------
17-SEP-18