Feed aggregator

Hello i just wanted to ask you a question. Can you please provide me some good online oracle books, tutorials or courses of the dba technologies like RMAN, Golden Gates, Exadata, Data Guard, Data pump and RAC? thanks.

I am normally using "<code>SELECT DBMS_METADATA.GET_DDL('USER', 'User_Name') FROM DUAL;</code>" to get the hashed password ( which I use to re-create the user as it was ), with SYSTEM user. This unfortunately does not work for users which have password_versions = 10G ( I am using Oracle 19 but I think this happens with 12 as well ) The hashed password in that case is stored in a column of USER$ sys table that SYSTEM user is not allowed to see However I observed that I can use export datapump to dump the user definition and then import datapump to get a sql file that has the hashed password, even with SYSTEM user ( but it is a long way ). So how can SYSTEM user get the hashed password? I would like a simpler way to get a script to re-create a user Regards Mauro

Well hello i just wanted to ask you when you have 100000000 rows of data and the best partitioning strategy is to use partition by range with subpartition by hash (having a column that is date type called hired and in the range we have YEAR(hired) and in the hash we have MONTH(hired)) how we will know the number of the partitions we will have to create in the table so we can have the best performance when we query data? thanks.If you don't understand my question i can provide the code if you ask me thanks.

Hi Tom, I was learning Secure Application Roles and created an example to test it. Everything worked fine to actually create and enable the role, but I'm getting an error disabling the role (actively disabling it, not just letting the end of session do it). First I'll paste a portion of the script output showing the error, and then I'll paste the .sql script itself that can be reproduced. === secure_role_example.txt === MYDBA@ORCL > create package body secure_role_pkg as 2 procedure enable_role is 3 begin 4 -- security check here 5 if 1 = 1 then 6 dbms_session.set_role('secure_role'); 7 end if; 8 end; 9 10 procedure disable_role is 11 begin 12 dbms_session.set_role('all except secure_role'); 13 end; 14 end; 15 / Package body created. MYDBA@ORCL > show errors No errors. MYDBA@ORCL > MYDBA@ORCL > grant execute on secure_role_pkg to public; Grant succeeded. A@ORCL > A@ORCL > exec mydba.secure_role_pkg.enable_role; PL/SQL procedure successfully completed. A@ORCL > A@ORCL > select * from session_roles; ROLE ------------------------------ SECURE_ROLE A@ORCL > A@ORCL > exec mydba.secure_role_pkg.disable_role; BEGIN mydba.secure_role_pkg.disable_role; END; * ERROR at line 1: ORA-01919: role 'SECURE_ROLE' does not exist ORA-06512: at "SYS.DBMS_SESSION", line 124 ORA-06512: at "MYDBA.SECURE_ROLE_PKG", line 12 ORA-06512: at line 1 A@ORCL > A@ORCL > select * from session_roles; ROLE ------------------------------ SECURE_ROLE === secure_role_example.sql === -- secure_role_example.sql -- Run this logged in as the mydba user, who has dba role. -- This example creates a secure application role, which is a role that is -- tied to and can only be set by a specific invokers rights package. This -- allows you to procedurally enable a role for a user's session based on -- criteria you define, and have that role contain all the privs needed to -- execute a set of packages to run a particular application. spool secure_role_example.txt; set echo on; connect mydba/orcl; create role secure_role identified using mydba.secure_role_pkg; create table secure_table (a int, b int); create package secure_app as procedure do_stuff; procedure display_stuff; end; / show errors create package body secure_app as procedure do_stuff is begin insert into secure_table values (1, 1); commit; end; procedure display_stuff is l_count number; begin select count(*) into l_count from secure_table; dbms_output.put_line(l_count); end; end; / show errors grant execute on secure_app to secure_role; create package secure_role_pkg authid current_user as procedure enable_role; procedure disable_role; end; / show...

Well hello i just wanted to ask you a question. Before 1.5 years i installed 23ai version and i downloaded sql developer. Before 1 month i opened my sql developer and i saw that my hr user is locked so i had to connect to sys user and then i pressed the following code -> <code>ALTER SESSION SET CONTAINER = FREEPDB1; ALTER USER hr ACCOUNT UNLOCK; -- or ALTER USER hr IDENTIFIED BY ** (password: hr) ACCOUNT UNLOCK; </code> My question is from the time that i unlocked the user hr so i can connect again when the account will lock again? is there any query so i can see the remaining time that when is finished it will lock again the hr user? thanks.

Hello, We have a Java process that calls OR stored procedure via JPA. This procedure was initially declared outside of a package, and everything worked fine, but when we moved it into a package, we encountered date conversion problems related to the incorrectly initialized Oracle NLS_DATE_FORMAT variable. When our call is made via a Windows SQL client, NLS_DATE_FORMAT has the value 'DD/MM/YYYY'. However, when the call is made via Java Process, this variable has the value 'DD-MON-RR' => This causes problems in the procedure where date conversions are sometimes used without specifying the format, and Oracle then uses the NLS_DATE_FORMAT variable. This can lead to a TO_DATE(27/02/2026) operation when the session format is 'DD-MON-RR'... and therefore results in the Oracle error -1843 "not a valid month". When the stored procedure was outside of a package, we forced this format at the beginning of our procedure with the command: execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT=''dd/mm/yyyy'''; But this doesn't work when the procedure is inside a package because it is apparently compiled and cached before the ALTER SESSION operation is performed... The code is below: <code> CREATE OR REPLACE PACKAGE GRASE_WSDATEBUTOIR_PG is /* Traitement effectue par le package : Appel procedure de recuperation date butoire pour ws sme avantage --*/ -- -- C O N S T A N T E S P U B L I Q U E S -- ============================================================================ P_CST_module CONSTANT STD_desc_PG.P_ST_module DEFAULT 'GRASE_WSDATEBUTOIR_PG'; P_CST_fonction_adm CONSTANT STD_desc_PG.P_ST_fonction_adm DEFAULT 'SERVICE'; P_CST_version CONSTANT STD_desc_PG.P_ST_version DEFAULT '26.2.0.01'; P_CST_version_date CONSTANT DATE DEFAULT '23/02/2026'; -- ============================================================================ --+Nom : proc_ws_datebutoir_pr --+traitement : dans le cadre du ws sme avantage (creation montant) --+Parametres : I_idpersonn IN co.graseuvp_pg.P_ST_identifiant_personne, -- I_dtjour IN DATE, -- I_dttraitement IN DATE, --+Description : recupere la date butoire necessaire a la creation occ rrx1tmtavtg -- **************************************************************************** PROCEDURE ws_datebutoir_pr ( I_idpersonn IN co.graseuvp_pg.P_ST_identifiant_personne, I_cdsynretveu IN co.graseuvp_pg.P_ST_code_synthese, I_termapayer IN co.graseuvp_pg.P_ST_code_terme_a_payer, O_dtbutoir OUT co.graseuvp_pg.P_ST_date_butoire, O_dtbutoir_gen OUT co.graseuvp_pg.P_ST_date_butoire, O_dtbutoir_pts OUT co.graseuvp_pg.P_ST_date_butoire, O_dtbutoir_cg OUT co.graseuvp_pg.P_ST_date_butoire, O_code_retour OUT RRX1TERRPGM.coderetour%TYPE, O_module OUT RRX1TERRPGM.module%TYPE, O_errproc OUT RRX1TERRPGM.errproc%TYPE, O_sqltra...

We don't seem to be able memoptimize a table for read when there are triggers on it (this seems to be undocumented however ?). But: we can add the triggers later on without problem and the table continues to be memoptimized for read. Question: WHY ? Is it a bug ? It is a bit annoying because when trying to take advantage of this feature we first have to drop triggers on the table and then re-create them. <code> SQL> select banner from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production SQL> drop table t; Table dropped. SQL> create table t( i int primary key, v varchar2(1000)) segment creation immediate; Table created. SQL> create trigger tr_t before insert on t for each row begin null; end tr_t; 2 / Trigger created. SQL> alter table t MEMOPTIMIZE for read; alter table t MEMOPTIMIZE for read * ERROR at line 1: ORA-62181: The MEMOPTIMIZE FOR READ feature is not supported on this table. SQL> drop trigger tr_t; Trigger dropped. SQL> alter table t MEMOPTIMIZE for read; Table altered. SQL> create trigger tr_t before insert on t for each row begin null; end tr_t; 2 / Trigger created. SQL> select MEMOPTIMIZE_READ from user_tables where 'T'=table_name 2 / MEMOPTIM -------- ENABLED SQL> </code>

I run local tests with Sparrow to compare DeepSeek OCR2 and dots.ocr (by RedNote), both run on MLX-VLM in FP16 precision. Dots.ocr consistently beats DeepSeek OCR2 in accuracy, but DeepSeek OCR2 deliveres much better inference performance.

Questions and answers about the new deduction for qualified overtime compensation

One, Big, Beautiful Bill: How to take advantage of no tax on tips and overtime

I published a blog post back in 2024 where i used PFCLScan Version 2024 to scan various databases from 11g to 23c/ai. The blog is Compare the Database Security of Oracle Database 11g, 12c, 18c, 19c, 21c and 23c/ai and....[Read More]

Posted by Pete On 25/02/26 At 09:59 AM

We have just released a new version of PFCLScan at the end of last week. This is version 2025. PFCLScan is our database security tool for the Oracle database. There are a lot of changes and fixes to this version....[Read More]

Posted by Pete On 02/02/26 At 02:43 PM

<b>My goal</b> is to create a fast refreshable materialized view (MV) that holds all the combinations of records in specific related tables. So also the 'incomplete combinations'. So the query that is needed contains a full outer join. <b>First a disclaimer about using LiveSQL.</b> I tried but got the next message: "Not Accepting New Users Please use our V2 product, Oracle Free SQL. Free SQL allows you to learn and share SQL using the latest version of the Oracle database for free! https://freesql.com/" In this text I lay out a complete case with alle create- and insert-statements needed (I hope it is sufficient). <b>Update on this disclaimer (20260204):</b> I started working on freesql (in stead of livesql) and I start to understand the meaning of it. Small problem: <b>the first statement creating a materialized view fails because of insufficient priviliges.</b> This happened in freesql db-version 19! <b>Update on this disclaimer (20260206):</b> I posted this in the freesql forum (https://forums.oracle.com/ords/apexds/post/ora-01031-insufficient-privileges-when-i-try-to-create-a-ma-4031). The answer was: "REFRESH FAST ON COMMIT isn?t currently supported in FreeSQL, which is why you?re seeing an ORA-01031. I?ve logged a ticket with the dev team to look into supporting this going forward!" I also tried it in freesql db-version 23: no problem. Even further: The whole problem with creating materialized view logs with the clause "commit scn" is gone and the materialized views are correctly fast refreshed on commit! As long as I do not have more information, to me it looks like REFRESH FAST ON COMMIT in combination with 'commit scn' in the create materialized view log doesn't work well in version 19c. I posted this on january 5th, I look forward to an answer. <b>Update on this disclaimer (20260209):</b> Next reaction on my question in the freesql-forum: In Oracle 19c, creating a materialized view with REFRESH FAST ON COMMIT requires the ON COMMIT REFRESH system privilege. That privilege isn?t currently granted in the FreeSQL 19c environment, which is why you see ORA-01031 there. This is why it works for you in FreeSQL 23ai. I?ve logged a ticket for us to review this difference and look into supporting this more consistently going forward. Thanks again for pointing this out! <b>Conclusion</b>: It is (still) not possible to demonstrate this question in freesql. </end disclaimer> <b>How to achieve my goal</b> It's not possible to code a full outer join directly into a MV, that I know. So my plan is this: 1) Every left or right outer join concerning the aforementioned tables is put into a MV with the (+)-notation. 2) These MV's can be used in next MV's. 3) The full outer join (the last step in the example I will give) is implemented as: a) a MV holding the left outer join b) a MV holding the right outer join c) a MV that will 'union all' these two MV's together d) a MV that picks out the unique rec...

I?m using a Polymorphic Table Function (PTF) and adding a new column TIMESTAMP_CALCULATED of type TIMESTAMP with fractional seconds scale 9. In FETCH_ROWS I populate it with a constant value: 2026-01-01 01:01:01.123456789 However, when selecting the PTF output, the value appears rounded to 6 fractional digits, e.g.: 2026-01-01T01:01:01.123457Z (rounded to microseconds) Additionally, formatting it with: <code> to_char(TIMESTAMP_CALCULATED,'YYYY-MM-DD HH24:MI:SS.FF9') </code> produces: 2026-01-01 01:01:01.000000000 This is unexpected: even if the value was rounded to 6 digits, TO_CHAR(...FF9) should not become all zeros. Reproducible test case (minimal script) <code> CREATE TABLE TIMESTAMP_INPUT(ID INTEGER, TIMESTAMP_VALUE TIMESTAMP(9)); CREATE OR REPLACE PACKAGE TIMESTAMP_PTF AS FUNCTION describe (tab IN OUT DBMS_TF.table_t) RETURN DBMS_TF.describe_t; PROCEDURE fetch_rows; FUNCTION my_ptf(tab IN TABLE) RETURN TABLE PIPELINED ROW POLYMORPHIC USING TIMESTAMP_PTF; END; / CREATE OR REPLACE PACKAGE BODY TIMESTAMP_PTF AS FUNCTION describe (tab IN OUT DBMS_TF.table_t) RETURN DBMS_TF.describe_t IS new_cols dbms_tf.columns_new_t; BEGIN new_cols(1) := dbms_tf.column_metadata_t(name => 'TIMESTAMP_CALCULATED', type => dbms_tf.type_timestamp, scale => 9); RETURN dbms_tf.describe_t(new_columns => new_cols); END describe; PROCEDURE fetch_rows as v_rowset dbms_tf.row_set_t; v_vals_rowset dbms_tf.row_set_t; v_row_count binary_integer; v_col_count binary_integer; begin dbms_tf.get_row_set(v_rowset, v_row_count, v_col_count); for i in 1..v_row_count loop v_vals_rowset(1).tab_timestamp(i) := TO_TIMESTAMP('2026-01-01 01:01:01.123456789','YYYY-MM-DD HH24:MI:SS.FF9'); end loop; dbms_tf.put_row_set(v_vals_rowset); end; END; / DELETE TIMESTAMP_INPUT; INSERT INTO TIMESTAMP_INPUT(ID, TIMESTAMP_VALUE) VALUES(1, TO_TIMESTAMP('2026-01-01 01:01:01.123456789','YYYY-MM-DD HH24:MI:SS.FF9')); INSERT INTO TIMESTAMP_INPUT(ID, TIMESTAMP_VALUE) VALUES(2, TO_TIMESTAMP('2026-01-02 02:02:02.123456789','YYYY-MM-DD HH24:MI:SS.FF9')); INSERT INTO TIMESTAMP_INPUT(ID, TIMESTAMP_VALUE) VALUES(3, TO_TIMESTAMP('2026-01-03 03:03:03.123456789','YYYY-MM-DD HH24:MI:SS.FF9')); COMMIT; ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9'; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM'; WITH BASE_DATA AS( SELECT ID, TIMESTAMP_VALUE FROM TIMESTAMP_INPUT ) SELECT id, to_char(TIMESTAMP_VALUE,'YYYY-MM-DD HH24:MI:SS.FF9') TIMESTAMP_VALUE_STR, DUMP(TIMESTAMP_VALUE) dump_TIMESTAMP_VALUE, TIMESTAMP_VALUE, to_char(TIMESTAMP_CALCULATED,'YYYY-MM-DD HH24:MI:SS.FF9') TIMESTAMP_CALCULATED_STR, DUMP(TIMESTAMP_CALCULATED) DUMP_TIMESTAMP_CALCULATED, TIMESTAMP_CALCULATED FROM TIMESTAMP_PTF.my_ptf(BASE_DATA) pf; drop table TIMESTAMP_INPUT; drop package TI...

Hi all, I have a hierarchy between levels in my_hierarchy table (between id_lvl and id_lvl_sup). Each level (lvl) may have items (id_item), and each item has a category (id_item_sup). These data is stored in my_items table. Now I want to build a report based on the my_view view. In rows, we have id_lvl1 and id_lvl2. In columns, we have id_item_sup (1st level) and id_item (2nd level). In cells, we have the total of items for each id_lvl2. I want to transform the definition of my_view to "normalize" the number of items that appears in each id_item_sup. For example, id_item_sup = 2001, we have 4 items. <code> ID_H ID_ITEM_SUP ORD_ITEM_SUP NB_ITEM_PER_ITEM_SUP ---------- ----------- ------------ -------------------- 1000 1002 1002 11 1000 2001 2001 4 1000 2100 2100 5 1000 2200 2200 40 1000 3001 3001 25 1000 3500 3500 13 1000 4001 4001 1 1000 6001 6001 9 1000 7001 7001 3 1000 8001 8001 9 1000 0</code> I want to "padd" each id_item_sup to reach 10 items (p_nb_item_per_grp column in the view). The padding consists of adding items with lib_item = '----' at the last of existing items. <code> ID_H ID_ITEM_SUP ORD_ITEM_SUP ID_ITEM LIB_I ORD_ITEM NB ---------- ----------- ------------ ---------- ----- ---------- ---------- 1000 2001 2001 2002 2002 2002 47 1000 2001 2001 2003 2003 2003 501 1000 2001 2001 2004 2004 2004 3 1000 2001 2001 2007 2007 2007 3 1000 2001 2001 9999 ---- 9999 0 1000 2001 2001 9999 ---- 9999 0 1000 2001 2001 9999 ---- 9999 0 1000 2001 2001 9999 ---- 9999 0 1000 2001 2001 9999 ---- 9999 0 1000 2001 2001 9999 ---- 9999 0</code> Then we have two (02) cases : - number of items < 10, so we pad items to reach 10 items ; - number of items >= 10, so we create blocks of 10 items. Then, in all the blocks different from the first block we add " next" to lib_item_sup. For example, number of items is 11, so we create two (02) blocks : - The 1st one, with 10 items - The 2nd one, with one (01) item padded with 9 items with lib '----', and we concatenate " next" to lib_item_sup Another example, number of i...

Hi Tom, I got a Web Form that is able to display data to users and read user input back into the database. It is very important to be able to read user input back into the database. This is why I did not use out-of-the-box solutions like "owa_utils.read SQL into table". However, I need a ton of parameters to work with, and as the project grows, I would like to modularize and carry fewer parameters between the procedures. ChatGPT told me that context variables would be nice, and I could read them via owa_utils. However, I did not get this running. Do you have any solution that allows me to modularize and clean my code? Within the FreeSQL link, there is a smaller example of what I am currently doing. This code works in my schema to run a page that is hosted on my server, except for the page link. I only need to replace the g_page_link with my actual server and schema names, and the code will work on my schema. Unfortunately, I do not know how to generalize this Pagelinke with FreeSQL. I also do not know a server that I could call from there. To make that clear again: I need help with managing user input, the moment I split my current procedure into a lot of smaller procedures. I would like to avoid passing 20+ in-out parameters between multiple procedures. Can you help me? Best, Peter