DBA Blogs
How to activate the auto-index feature in Oracle 19c Enterprise Edition?
To activate on PDB send me the error:
<code>BEGIN dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT'); END;
Informe de error -
ORA-40216: funcion no soportada
ORA-06512: en "SYS.DBMS_SYS_ERROR", linea 79
ORA-06512: en "SYS.DBMS_AUTO_INDEX_INTERNAL", linea 10967
ORA-06512: en "SYS.DBMS_AUTO_INDEX", linea 301
ORA-06512: en linea 1
40216. 00000 - "feature not supported"
*Cause: The feature was not supported in the API.
*Action: Modify the code to avoid usage of the feature.</code>
Hi,
I have created an application that is tracking a set of processes that are being run from a master table. When a process is completed, a flag in a column name PROC_EXEC_STATUS changes from N to Y for each process. I want my oracle apex application to refresh when the flag for the each individual processes in the table is updated. I have used javascript but then because the processes do not have a defined running time, it just continues to refresh and sometimes is not in sync with the process running time. I am on apex version 24.2.
I have PL/SQL package with a function that returns a list of item numbers. This package/function is repeatedly called by online with multiple users; passing parameters to the function to use within the SQL statement(s). A new string (short list of item types) will now be passed to the function to narrow down the item numbers being returned. My question: is it better to use a Collection or a Global Temporary Table to insert the item types into, which will then be used in the where clause of the SQL statement to select the item numbers.
<u></u><u><b>We are building a system where:</b></u>
- Application users connect using different DB users
- They call DBMS_SCHEDULER.CREATE_JOB to create jobs
- Jobs are always expected to execute in the application user(APPUSER) schema
- We call external C++ functions via LIBRARY objects
- We use VPD policies and also log changes based on SESSION_USER
However, we observe that jobs run with SESSION_USER = job_creator, which is not always the same as application user.
<b><u>Question:</u></b>
<b>How can we ensure that DBMS_SCHEDULER jobs always run with SESSION_USER = application user, regardless of who initiates the creation?</b>
<u><b>Reproducible test case:</b></u>
Tested on Oracle 19.25 (Non-CDB installation) on RHEL 9.5.
- APPUSER: Application schema and owner of all used objects
- DBUSER: One of the application/database users (there are many such users with different DB names)
Steps to reproduce:
<u>1. Execute as SYS:</u>
<code>
create user appuser identified by ***;
grant create session to appuser;
grant create library to appuser;
grant create procedure to appuser;
GRANT CREATE ANY TABLE TO appuser;
grant create sequence to appuser;
alter user appuser quota unlimited on USERS;
create user dbuser identified by ***;
grant create session to dbuser;
grant create job to dbuser;
grant create any job to dbuser;
</code>
<u>2. Execute as appuser:</u>
<code>
CREATE OR REPLACE LIBRARY extproc_userinfo_lib AS '/app/bin/libgetuser.so';
drop function loguserinfo;
CREATE OR REPLACE FUNCTION appuser.LogUserInfo
RETURN BINARY_INTEGER
AS
LANGUAGE C
LIBRARY extproc_userinfo_lib
NAME "LogUserInfo"
WITH CONTEXT
PARAMETERS (
CONTEXT,
RETURN int
);
grant execute on appuser.LogUserInfo to public;
drop table appuser.applog;
create table appuser.applog (
id number generated always as identity primary key
,message varchar2(300)--,
,log_time timestamp default systimestamp
);
select * from appuser.applog order by log_time desc;
</code>
<u>3. Login as OS user oracle to Linux(I use RHEL 9.5) and create a new file /app/bin/getuser.cpp with the following contents.
It insert both SESSION_USER and CURRENT_USER to table APPLOG:
</u>
<code>
#include <oci.h>
#include <cstring>
#include <cstdio>
typedef struct OCIExtProcContext OCIExtProcContext;
#define OCIEXTPROC_SUCCESS 0
extern "C" int LogUserInfo(OCIExtProcContext* ctx)
{
OCIEnv* envhp = nullptr;
OCIError* errhp = nullptr;
OCISvcCtx* svchp = nullptr;
OCIStmt* stmthp = nullptr;
const char* query = "insert into appuser.applog(message) "
"SELECT 'SESSION_USER:' || SYS_CONTEXT('USERENV','SESSION_USER') || "
"'; CURRENT_USER:' || SYS_CONTEXT('USERENV','CURRENT_USER') "
"FROM dual";
if (OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp) != OCIEXTPROC_SUCCESS)
return -1;
if (OCIHandleAlloc(envhp, (void**)&stmthp, OCI_HTYPE_STMT, 0, NULL) != O...
Introduction As a Data Architect, creating clear and effective diagrams is crucial for communicating and documenting software and data architectures. The C4 model, with its focus on abstraction-first design—a principle I firmly believe is the backbone of software engineering—immediately caught my interest. To explore this further, I recently began experimenting with C4 modeling using Structurizr DSL, (DSL=Domain Specific Language) VSCode, and popular diagramming tools like PlantUML and Mermaid. I used Cairo and Graphviz in the past but these newer libraries require less tinkering. Here’s a look at my journey and the insights I gained along the way while trying the diagram as code approach. Why C4 Models? The C4 model is a powerful way to describe software systems at four distinct levels of abstraction—Context, Containers, Components, and Code—often referred to as the "4 Cs." Its simplicity, scalability, and developer-friendly approach make it a perfect fit for both new (greenfield) and existing (brownfield) projects.
Since I prefer to avoid cloud-based tools for richer experience and control, initially I set up a local environment using VSCode and Docker on my trusty old but fast Ubuntu laptop. This way, I can create clear, maintainable diagrams while keeping everything offline and efficient. Looking at it again, I decided that even Docker is an overkill. I decided Vscode is enough to code and diagram. My Setup I took a quick look at the Structurizr DSL Python wrapper, but I also skipped it—I wanted to dive straight into the native DSL syntax and see my diagrams render with minimal overhead. After all, treating diagrams as code means I can version-control them like any other project, keeping everything clean and reproducible.
While I could have spun up Structurizr Lite in a Docker container (because who doesn’t love local, self-hosted solutions?), I went lighter—just VSCode extensions to get the job done. My philosophy? Minimum viable effort for maximum results. No unnecessary layers, no cloud dependencies, just code and diagrams, the way it should be. They integrate seamlessly with wiki platforms (like Confluence, Notion, or GitLab/GitHub Wikis) and Git repositories, allowing you to embed dynamic, version-controlled diagrams directly in your documentation. Tools in Action - Structurizr DSL: Writing diagrams as code in DSL in vscode and for better previews run their server on localhost
- VSCode: With extensions for PlantUML and Mermaid, I could preview diagrams instantly in vscode.
- PlantUML & Mermaid: Both tools integrated seamlessly with VSCode via extensions, though I found Mermaid’s syntax more intuitive for quick sketches and wiki integration. Mermaid has its own markup.
Outcomes I successfully created Context, Container, and Component diagrams for a sample imaginary project. The ability to generate diagrams locally ensured full control and flexibility, no SaaS. Here are two examples of what I built: Figure 1: Output from Structurizr server running on localhost:8080 in docker with code on the left generating the C4 model diagram on the right
Figure 2: Output from Mermaid vscode extension showing Mermaid code on the left generating the diagram on the right Final Thoughts I find the C4 model and tools like PlantUML and Mermaid are a game-changer for architecture documentation—it shifts the process from static, manual diagrams to code-driven, version-controlled clarity. By leveraging Structurizr DSL in VSCode and pairing it with Mermaid/PlantUML, I’ve crafted a workflow that’s both flexible and precise, giving me full control over how my systems are visualized.
There’s something deeply satisfying about coding your diagrams—no more wrestling with drag-and-drop tools or misaligned Bézier curves. Just clean, maintainable DSL and instant visual feedback. I’m officially done with joining rectangles by hand; from now on, it’s code all the way.
We start getting alerts about a tablespace users that it was getting full, tablespace users contains 3 datafiles of 32GB each, but we found out that in one of the tables in tablespace users it has a Lob(67 GB), so we decided to moved that Lob to have it owns tablespace. Now we want to skink users tablespace and reduced to one datafile instead of 3. how we can do that?
We are trying to moved all db objects in those datafiles, to a new tablespace, but the problem seems like when we try to move the above table that contains the lob column doing the
alter table APEX_UNISOURCE.UOS_DOCUMENTS move ONLINE TABLESPACE USERS_NEW;
but seems like is moving also the lob column to the new tablespace.
How we can move that table excluding lob column?
I have a problem in defining shared folders in VMVirtualBox.
As there ist no category for that it seams that this is the wrong place.
So where can i send the problem that seems to be a bug?
With Always Free, there is no support. How do I get DBMS_CRYPTO grant? I tried using STANDARD_HASH, but it says STANDARD_HASH must be declared. So is there a way to get this access preferably to DBMS_CRYPTO? If not is there an alternative?
Refer to object-relational-developers-guide / Column Object Storage in Relational Tables
If
?The null indicators for an object and its embedded object attributes occupy one bit each. Thus, an object with n embedded object attributes (including objects at all levels of nesting) has a storage overhead of CEIL(n/8) bytes.?
And
?Since the null indicator is one byte in size, the overhead of null information for each row of the relational table is one for each object column of relational table.?
Then
where can I check that null indicator length is changed for an object column(in an relational table) with more than 8 attributes or where storage is changed ?because I didn?t see any changes in data length column for object column through all_tab_columns.
??? ?????? ?????? ??? ??? ?? ??????? APEX? ???? ?? ????? ???? ????? ??????? ????? ?????? ?? ?? ???????
Hi Team,
From performance perspective, which is a better filter IN or NOT IN?
I've installed Oracle Secure Backup using osbws_install.jar on a couple of hosts and it works fine on all of them (i.e. I can list, add and delete credentials in the wallet without being prompted for password).
But on one particular host, after installing, when I try to list the credentials using mkstore, it prompts for wallet password, even though there has been no password set for this. There isn't any parameter defined in sqlnet.ora for wallet directory, either.
Any idea why it may be behaving this way?
<code>$ mkstore -wrl <location of cwallet.sso> -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: </code>
I need to report the database sizing to our management.
Following are the questions:
1) Total space allocated and used for each Database instance (We have 14 each for 3 applications)
2) How is the growth pattern over the last few years. Year to Year change.
My answer is
1) Run the following query in each instance
SELECT 'ALLOCATED ', SUM(bytes) / 1024 / 1024 / 1024 AS "DB_SIZE-GB" FROM dba_data_files
UNION
SELECT 'USED ', SUM(bytes)/1024/1024/1024 from dba_segments;
2) Store this info in a user defined Table and insert a row with the date once a month, starting today.
So I can provide some growth pattern in a year or couple of years from now.
Can you help improve this in any way, since it is going to be sent to the Management.
I have been working on a project to password protect the files generated by Oracle forms and reports. Since this will only be for internal users, I have used PDFUSER, PDFOWNER utilities given by Oracle.
The next tasks is to password protect the files generated by UTL_FILE. I tried to create job which zip the file and password protect it, however the idea was rejected citing performance issues because currently the files are being generated in txt and excel format.
<code>BEGIN
dbms_scheduler.create_job(
job_name => 'TEST_PASS_ZIP',
job_type => 'EXECUTABLE',
job_action => '/bin/sh',
number_of_arguments => 1,
enabled => FALSE -- Test run only
);
dbms_scheduler.set_job_argument_value(
job_name => 'TEST_PASS_ZIP',
argument_position => 1,
argument_value => 'zip -P MySecret123 /win/Class2/SysGenRpt/FIN/testfile_password.zip /win/Class2/SysGenRpt/FIN/testfile_password.txt'
);
dbms_scheduler.enable('TEST_PASS_ZIP');
END;
/</code>
I found that using https://github.com/antonscheffer/as_zip, we can zip the file. However, I am seeking answer where I can password protect the file at the time of file generation and user can simply open the file by providing the password. Not applying DBMS_CRYPTO as user cannot decrypt the file.
I need oracle 8i enterprise edition
and Devloper 6i
can u teel me the links for them to download
I need them very much ...
Pages
|
