DBA Blogs
Today’s competitive market demands that stakeholders understand, monitor, and react to rapidly changing conditions. Businesses need flexible, dynamic, and detailed insight – and they need...
We share our skills to maximize your revenue!
DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS
Greetings,
I've seen when "cpu_per_call" limit is reached. ORA-02393 is sent to the SQL Plus. Is there a view that a DBA can query to find out if "ORA-02393: exceeded call limit on CPU usage" occurs to applications using the database since it isn't written to alert log.
Thanks,
John
I have created a MV on UAT server and my MV view using a query which has remote connectivity to PROD and select only rights to these tables
which has millions of rows around 10 lakhs in each table but after calculation output of query is 139-150 rows only. query alone without MViews is taking 60
seconds but when I use CREATE MATERIALIZED VIEW NOCOMPRESS NOLOGGING BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND NEXT null USING DEFAULT
LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE as "query" mview creation happens in one hour and after that refresh time
is 20-30 minutes ? which is surely not acceptable as this data is being used for dashboard with 3 minutes delay which MV should take time to refresh!
I don't have privilege to anything to check on prod DB but on UAT I have sufficient access! I have tried many option but didn't work so please help
me to know what is solution and if no solution what is reason behind this? in addition when my mview refresh it shows in explain plan
" INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO abc". Please help me!
I am really stuck here and tried my hard to get it resolved or finding a reason where I can explain to relevant team! Please help!
1. I have tried create table with same query and it took less than a minute.
2. Insert statement also working fine taking same time.
3. I tried MV view refresh option with atomic_refresh=false as well but it didn't work and actually it will not help!
Please let me know if u have any info required!
Note: My mv view query using prod tables(approx 4 tables) with db link from UAT.Prod server has one separate user which has been given below table rights
select count(*) from abc@prod;
--800000
select count(*) from abc1@prod;
--700000
select count(*) from abc2@prod;
--200000
Hello,
I created a PL/SQL function that returns a list of open balances as a table result, where all amounts are converted to the currency provided as an input parameter:
<code>function my_pkg.my_func (pi_currency in NUMBER default NULL) return amount_tab pipelined; </code>
I created an Oracle REST Data Service with only GET handler:
<code>select * from table(my_pkg.my_func(:to_currency)) ;</code>
I tested it by Advanced REST Client and it is working as expected with an additional header for the to_currency parameter. In APEX I declared a REST Data Source related to the above REST service, then I made an APEX page with IG region based on the above REST source and it is working well as long as I am not trying to provide a parameter, i.e. until to_currency is null. When I try to populate <b>{"to_currency":"USD"}</b> in the External Filter attribute, this causes the application crash. I googled the problem but found nothing.
Is any other standard way to pass the non-column parameter to the GET handler in APEX or I should write my own procedure to call REST service, e.g. by using APEX_EXEC?
Thank you and best regards,
Alex
Hi!
I'm trying to send a post request with json:
<code>
{
"id": 12344444,
"email": "ppppoddddddppp@gmail.com",
"first_name": "",
"last_name": "",
"billing": {
"first_name": "22222",
"last_name": "",
"company": "",
"address_1": "",
"address_2": "",
"city": "",
"postcode": "",
"country": "",
"state": "",
"email": "",
"phone": ""
}
}
</code>
I'm trying to use apex_json to extract information like: ?company? that is in ?billing?
I read the following guide:https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#parsing-json
and it works but not inside ORDS Handler Definition....
I'm trying to use the following code ... but it's not insert the data and return "201":
<code>
DECLARE
l_json_payload clob;
l_blob_body blob := :body;
l_dest_offset integer := 1;
l_src_offset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;
BEGIN
if dbms_lob.getlength(l_blob_body) = 0 then
:status_code := 400; --error
:errmsg := 'Json is empty';
return;
end if;
dbms_lob.createTemporary(lob_loc => l_json_payload ,cache => false);
dbms_lob.converttoclob( dest_lob => l_json_payload
,src_blob => l_blob_body
,amount => dbms_lob.lobmaxsize
,dest_offset => l_dest_offset
,src_offset => l_src_offset
,blob_csid => dbms_lob.default_csid
,lang_context => l_lang_context
,warning => l_warning);
APEX_JSON.parse(l_json_payload);
INSERT INTO ACCOUNTS
(
wp_id ,
name ,
email ,
f_name ,
l_name ,
wp_role ,
wp_username ,
woo_is_paying_customer ,
woo_billing_first_name
)
VALUES
(
:id,
:first_name || ' ' || :last_name,
:email,
:first_name,
:last_name,
:role,
:username,
decode(:is_paying_customer,'false', 'N', 'Y'),
APEX_JSON.get_varchar2(p_path => 'billing.first_name')
);
:status_code := 201; --created
EXCEPTION
WHEN OTHERS THEN
:status_code := 400; --error
:errmsg := SQLERRM;
END;
</code>
updating:
After testing - the problem is in this line:
<code>
l_blob_body blob := :body;
</code>
When I enter it, it does not insert anything into a database
update 2:
after testing...
I realized that it is not possible to combine:
: body and other bind value, so APEX_JSON.get_varchar2 should be used instead (p_path => 'billing.first_name')
So the problem was solved
We have several existing Oracle Directories set up to allow reading CSV files that work fine, and a couple of them work OK to Write new files. I have been trying to add a new Directory definition pointing to a different path and cannot get it to work. I am in a corporate environment where I don't have access to the System accounts and cannot see the instance startup file, and don't have direct access to the Linux operating system, so I don't know what setup has been done for the previous Directories.
One of the existing Directories that works for both read and write is defined as:
<code>CREATE OR REPLACE DIRECTORY RED AS '/red/dev';</code>
for the above directory, the following test code works fine to create an output file:
<code>DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'RED',
filename => 'test.csv',
open_mode => 'w',
max_linesize => 32767);
UTL_FILE.PUT_LINE(v_file, 'A,123');
UTL_FILE.FCLOSE(v_file);
END;
</code>
I want to write some files to a subdirectory under the above path, and have found that Oracle will only allow WRITE to a named-Oracle Directory for security reasons. A new Directory I want to create is defined as:
<code>CREATE OR REPLACE DIRECTORY RED_OUTPUT AS '/red/dev/OUTPUT';</code>
But changing the code above to use RED_OUTPUT as the "location" or directory, results in "ORA-29283: invalid file operation: cannot open file".
The '/red/dev/OUTPUT' directory location exists on the external NAS filesystem and appears to have the same permissions as the parent '/red/dev' directory (as best I can tell by using Windows Explorer to look at the directory security properties).
I have read various posts online indicating things like the Oracle instance must be restarted after defining a new Oracle Directory, or that every path specified by an Oracle Directory must have a separate "mount point" on the Oracle Linux server, but I don't have easy access to do those things. The RED_OUTPUT directory can be currently used to READ an existing file if I copy one to that location using Windows Explorer.
What is likely the issue with not being able to WRITE to this new RED_OUTPUT directory, and are any of these additional steps (restart, mounting, etc) necessary to make this work?
I would like to know how to access date column from a table and use it as date filter for another large volume table..
I have the following query that currently uses a date in the filter criteria and it gets completed in about 10 to 15 minutes.
<code>select a,b,datec, sum(c) from table1 where datec = date '2021-01-12' group by a,b,datec</code>
I'm trying to replace the hard coded date with a date from another table called table2. It's a small table with 1600 rows that just returns latest cycle completion date (one value) which is typically today's date minus one day for most days except for holidays when the cycle doesn't run.table1 is a view and it returns millions of rows.
I tried the following queries in order to get the date value in the filter condition:
<code>select a,b,datec, sum(c) from table1 t1, table2 t2 where t1.datec = t2.pdate and t2.prcnm = 'TC' group by a,b,datec
select a,b,datec, sum(c) from table1 t1 inner join table2 t2 on datec = t2.pdate and t2.prcnm = 'TC' group by a,b,datec
select a,b,datec, sum(c) from table1 t1 where t1.datec = (SELECT t2.date FROM table2 t2 WHERE prcnm = 'TC') group by a,b,datec</code>
I also tried this hint:
<code>select a,b,datec, sum(c) from table1 t1 where t1.datec = (SELECT /*+ PRECOMPUTE_SUBQUERY */ t2.date FROM table2 t2 WHERE prcnm = 'TC') group by a,b,datec</code>
The above queries take too long and eventually fail with this error message - "parallel query server died unexpectedly"
I am not even able to get 10 rows returned when I use the date from table2. I confirmed that table2 returns only one date and not multiple dates.
Can you please help me in understanding why the query works when hard coded date is used, but not when a date from another table is used?
thank you.
good evening,
I have a sql statement with the following information in v$sqlarea
<code>select sql_id, address, hash_value, plan_hash_value from v$sqlarea where sqltext=<string to identify my query>
sql_id |address |hash_value|plan_hash_value
cv65zdurrtfus|00000000FCAA9560|2944187224|3149222761</code>
I remove this object from the shared pool with the command because I want to recompute the exec plan for my sql statement
<code>exec sys.dbms_shared_pool.purge('00000000FCAA9560,2944187224','c');</code>
I redo my previous select statement on v$sqlarea and it retuns 0 row so I'm happy with that.
Then I execute my original sql and last I redo my select statement on v$sqlarea and it returns one row with the same values
<code>sql_id |address |hash_value|plan_hash_value
cv65zdurrtfus|00000000FCAA9560|2944187224|3149222761</code>
I was wondering how identical ids were generated, i was expecting new values even though at the end I have the expected result.
Thanks for your feedback.
Simon
The previous demo of TDE in 19c was for a full Tablespace (converting an existing, non-TDE, Tablespace to an Encrypted Tablespace). Pre-creating a Table with an Encrypted column would be straightforward : CREATE TABLE employees ( emp_id number primary key, first_name varchar2(128), last_name varchar2(128), national_id_no varchar2(18) encrypt, salary number(6) ) tablespace hr_data /
This encrypts the column with the AES encryption algorithm with a 192-bit key length ("AES192"). But what if you want to encrypt an existing, non-encrypted column ? You can use the MODIFY clause. ALTER TABLE employees ( MODIFY (national_id_no encrypt) /
A quick demo : SQL> create tablespace hr_data datafile '/opt/oracle/oradata/HEMANT/HR_DATA.dbf' size 5M;
Tablespace created.
SQL> CREATE TABLE employees ( 2 emp_id number primary key, 3 first_name varchar2(128), 4 last_name varchar2(128), 5 national_id_no varchar2(18), 6 salary number(6) ) 7 tablespace hr_data;
Table created.
SQL> ^C
SQL> insert into employees 2 select rownum, 'Hemant', 'Hemant' || to_char(rownum), dbms_random.string('X',12), 1000 3 from dual 4 connect by level "less than" 21 --- "less than" symbol replaced by string to preserve HTML formatting 5 /
20 rows created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> !sync ; sync
SQL> SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more }|{z HEMANT 3J?5 HR_DATA H4J? AAAAAAAA Hemant Hemant1 LH6RUZRISE11 Hemant Hemant2 DFIN8FZ7B6J0 Hemant Hemant3 PLJ1R2QYRG2C Hemant Hemant4 UT3HB9ALF3B5 Hemant Hemant5 LQMDUTFB2PTM Hemant Hemant6 1IGKV4E78M5J Hemant Hemant7 P9TQAV5BC5EM Hemant Hemant8 V69U6VZWCK26 Hemant Hemant9 EOTOQHOB0F45 Hemant Hemant10 OKMEV89XOQE1 Hemant Hemant11 0D4L77P3YNF0 Hemant Hemant12 CTMCLJSKQW82 Hemant Hemant13 49T0AG7E2Y9X Hemant Hemant14 ODEY2J51D8RH Hemant Hemant15 R1HFMN34MYLH Hemant Hemant16 OXI0LOX161BO Hemant Hemant17 2XL44ZJVABGW Hemant Hemant18 4BIPWVECBWYO Hemant Hemant19 732KA25TZ3KR Hemant Hemant20 NN0X92ES90PH AAAAAAAA
SQL> alter table employees 2 MODIFY (national_id_no encrypt) 3 /
Table altered.
SQL> alter system checkpoint;
System altered.
SQL> !sync ; sync
SQL>
SQL> select emp_id, national_id_no 2 from employees 3 order by 1 4 /
EMP_ID NATIONAL_ID_NO ---------- ------------------ 1 LH6RUZRISE11 2 DFIN8FZ7B6J0 3 PLJ1R2QYRG2C 4 UT3HB9ALF3B5 5 LQMDUTFB2PTM 6 1IGKV4E78M5J 7 P9TQAV5BC5EM 8 V69U6VZWCK26 9 EOTOQHOB0F45 10 OKMEV89XOQE1 11 0D4L77P3YNF0 12 CTMCLJSKQW82 13 49T0AG7E2Y9X 14 ODEY2J51D8RH 15 R1HFMN34MYLH 16 OXI0LOX161BO 17 2XL44ZJVABGW 18 4BIPWVECBWYO 19 732KA25TZ3KR 20 NN0X92ES90PH
20 rows selected.
SQL> SQL> select emp_id, dump(national_id_no) col_dump 2 from employees 3 order by emp_id 4 /
EMP_ID COL_DUMP ---------- ------------------------------------------------------ 1 Typ=1 Len=12: 76,72,54,82,85,90,82,73,83,69,49,49 2 Typ=1 Len=12: 68,70,73,78,56,70,90,55,66,54,74,48 3 Typ=1 Len=12: 80,76,74,49,82,50,81,89,82,71,50,67 4 Typ=1 Len=12: 85,84,51,72,66,57,65,76,70,51,66,53 5 Typ=1 Len=12: 76,81,77,68,85,84,70,66,50,80,84,77 6 Typ=1 Len=12: 49,73,71,75,86,52,69,55,56,77,53,74 7 Typ=1 Len=12: 80,57,84,81,65,86,53,66,67,53,69,77 8 Typ=1 Len=12: 86,54,57,85,54,86,90,87,67,75,50,54 9 Typ=1 Len=12: 69,79,84,79,81,72,79,66,48,70,52,53 10 Typ=1 Len=12: 79,75,77,69,86,56,57,88,79,81,69,49 11 Typ=1 Len=12: 48,68,52,76,55,55,80,51,89,78,70,48 12 Typ=1 Len=12: 67,84,77,67,76,74,83,75,81,87,56,50 13 Typ=1 Len=12: 52,57,84,48,65,71,55,69,50,89,57,88 14 Typ=1 Len=12: 79,68,69,89,50,74,53,49,68,56,82,72 15 Typ=1 Len=12: 82,49,72,70,77,78,51,52,77,89,76,72 16 Typ=1 Len=12: 79,88,73,48,76,79,88,49,54,49,66,79 17 Typ=1 Len=12: 50,88,76,52,52,90,74,86,65,66,71,87 18 Typ=1 Len=12: 52,66,73,80,87,86,69,67,66,87,89,79 19 Typ=1 Len=12: 55,51,50,75,65,50,53,84,90,51,75,82 20 Typ=1 Len=12: 78,78,48,88,57,50,69,83,57,48,80,72
20 rows selected.
SQL>
SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more }|{z HEMANT 3J?5 HR_DATA AAAAAAAA Hemant Hemant204 Hemant Hemant194 Hemant Hemant184 Hemant Hemant174[Q# Hemant Hemant164 Hemant Hemant154 $^?[ Hemant Hemant1448 Hemant Hemant134 Hemant Hemant124 Hemant Hemant114 Hemant Hemant104J Hemant Hemant94 Hemant Hemant84M zCAGp Q(ru Hemant Hemant74 $o7tN Hemant Hemant6418 ( i+W Hemant Hemant54 f(cCL Hemant Hemant44 Hemant Hemant34 Hemant Hemant24 e{_ Hemant Hemant14 Hemant Hemant1 LH6RUZRISE11 Hemant Hemant2 DFIN8FZ7B6J0 Hemant Hemant3 PLJ1R2QYRG2C Hemant Hemant4 UT3HB9ALF3B5 Hemant Hemant5 LQMDUTFB2PTM Hemant Hemant6 1IGKV4E78M5J Hemant Hemant7 P9TQAV5BC5EM Hemant Hemant8 V69U6VZWCK26 Hemant Hemant9 EOTOQHOB0F45 Hemant Hemant10 OKMEV89XOQE1 Hemant Hemant11 0D4L77P3YNF0 Hemant Hemant12 CTMCLJSKQW82 Hemant Hemant13 49T0AG7E2Y9X Hemant Hemant14 ODEY2J51D8RH Hemant Hemant15 R1HFMN34MYLH Hemant Hemant16 OXI0LOX161BO Hemant Hemant17 2XL44ZJVABGW Hemant Hemant18 4BIPWVECBWYO Hemant Hemant19 732KA25TZ3KR Hemant Hemant20 NN0X92ES90PH AAAAAAAA
SQL> select version, version_full from v$instance;
VERSION VERSION_FULL ----------------- ----------------- 19.0.0.0.0 19.3.0.0.0
SQL>
When I insert a new row, the plain-text for this is not present. But the old (20) rows plain-text is still present.
SQL> insert into employees 2 values (21,'HemantNew','HemantNew21','ABCDEFGHIJ88',2000);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> !sync;sync
SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf }|{z HEMANT 3J?5 SJ? HR_DATA UTJ? AAAAAAAA HemantNew HemantNew214S Hemant Hemant204 Hemant Hemant194 Hemant Hemant184 Hemant Hemant174[Q# Hemant Hemant164 Hemant Hemant154 $^?[ Hemant Hemant1448 Hemant Hemant134 Hemant Hemant124 Hemant Hemant114 Hemant Hemant104J Hemant Hemant94 Hemant Hemant84M zCAGp Q(ru Hemant Hemant74 $o7tN Hemant Hemant6418 ( i+W Hemant Hemant54 f(cCL Hemant Hemant44 Hemant Hemant34 Hemant Hemant24 e{_ Hemant Hemant14 Hemant Hemant1 LH6RUZRISE11 Hemant Hemant2 DFIN8FZ7B6J0 Hemant Hemant3 PLJ1R2QYRG2C Hemant Hemant4 UT3HB9ALF3B5 Hemant Hemant5 LQMDUTFB2PTM Hemant Hemant6 1IGKV4E78M5J Hemant Hemant7 P9TQAV5BC5EM Hemant Hemant8 V69U6VZWCK26 Hemant Hemant9 EOTOQHOB0F45 Hemant Hemant10 OKMEV89XOQE1 Hemant Hemant11 0D4L77P3YNF0 Hemant Hemant12 CTMCLJSKQW82 Hemant Hemant13 49T0AG7E2Y9X Hemant Hemant14 ODEY2J51D8RH Hemant Hemant15 R1HFMN34MYLH Hemant Hemant16 OXI0LOX161BO Hemant Hemant17 2XL44ZJVABGW Hemant Hemant18 4BIPWVECBWYO Hemant Hemant19 732KA25TZ3KR Hemant Hemant20 NN0X92ES90PH AAAAAAAA
SQL>
So, it seems that after I ran the MODIFY to encrypt a column, Oracle created new copies of the 20 rows with encrypted values. However, the old plain-text (non-encrypted) values are still present in the datafile.Apparently, those "still present" plain-text representations of the "NATIONAL_ID_NO" column in the datafile are explained in the documentation as :
" Column values encrypted using TDE are stored in the data files in encrypted form. However, these data files may still contain some plaintext fragments, called ghost copies, left over by past data operations on the table. This is similar to finding data on the disk after a file was deleted by the operating system."
You should remove old plaintext fragments that can appear over time. Old plaintext fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, then they might be able to directly access these values in the data file holding the tablespace. To minimize this risk: Create a new tablespace in a new data file. You can use the CREATE TABLESPACE statement to create this tablespace. Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement. Repeat this step for all of the objects in the original tablespace. Drop the original tablespace. You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform-specific utilities. Use platform-specific and file system-specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).
I’ve been waiting a while before posting a series on the various limitations associated with Automatic Indexing, in order to see how the feature matures over time. The following have all been re-tested post 1 January 2021 on the Autonomous ATP Database Cloud service, using Oracle Database version 19.5.0.0.0. In the Oracle Documentation (including […]
In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions […]
Firstly, thank you for all those who contacted me regarding the issue with Oracle Automatic Indexing (AI) having stopped working within the Autonomous ATP Database Cloud service. It appears this issue was indeed widely spread and impacted numerous (if not all) Autonomous ATP Database Cloud services. This was all possibly due to (unpublished) bug […]
Oracle Cloud Infrastructure (OCI) is a set of complementary cloud services that enable you to build and run a wide range of applications and services in a highly available hosted...
We share our skills to maximize your revenue!
Businesses are under increasing pressure to be smarter, faster and more agile at providing services to their clients and customers. Whether for development of external customer-facing services, or...
We share our skills to maximize your revenue!
Who wants to mention in their resume that one of their operation task is to tag the cloud resources? Well I did and mentioned that one of the tools I used for that purpose was Tag Editor. Interviewer was surprised to learn that there was such a thing in AWS which allowed tagging multiple resource at once. I got the job due to this most under-appreciated and largely unknown service.
Tagging is boring but essential. As cloud matures, tagging is fast becoming an integral part of it. In the environments I manage, most of tagging management is automated but there is still a requirement at times for manual bulk tagging and that's where Tag Editor comes very handy. Besides of bulk tagging Tag Editor enables you to search for the resources that you want to tag, and then manage tags for the resources in your search results. There are various other tools available from AWS to ensure tag compliance and management but the reason why I like Tag Editor most is its ease of use and a single pane of window to search resources by tag keys, tag values, region or resource types. It's not as glamorous as AWS Monitron, AWS Proton or AWS Fargate but as useful as any other service is. In our environment, if its not tagged then its not allowed in the cloud. Tag Editor addresses the basics of being in cloud. Get it right, and you are well on your way to well-architected cloud infrastructure.
Oracle LiveLabs allow you try a wide variety of labs and technical workshop for Oracle’s tools and technologies running in Oracle Cloud free of charge and learn a technical skill...
We share our skills to maximize your revenue!
...
We share our skills to maximize your revenue!
Oracle Application Express (APEX) is the world's most popular low-code platform for enterprise apps. Using Oracle APEX, developers can quickly develop and deploy compelling scalable, secure...
We share our skills to maximize your revenue!
Pages
|