DBA Blogs

To say that Oracle GoldenGate has undergone a transformation over the last seven year is an understatement.  Back in 2017, […]

The post Retrieving and understanding a response file for Oracle GoldenGate Deployment configuration appeared first on DBASolved.

 

Will this service be available for Exacs?

Hi Tom, This is probably super simple once you show me how, but I haven't been able to find the answer in the documentation. How do I call an external SQL script from within PL/SQL? I know I can embed the actual SQL text inside the PL/SQL block, but I would rather keep the SQLs in a separate file to be called externally when I need to outside the PL/SQL code. E.g. BEGIN -- Check for some condition. -- if condition true then -- execute foo.sql END; / In SQL*PLUS, we execute external scripts using one @, e.g. @foo.sql. And within a script, we execute other scripts with two @, e.g. @@bar.sql. Not sure how to do the same within a PL/SQL block. Thanks, -Peter

Dear Team, I have a logging table with the following structure: <code> SQL> desc t Name Null? Type ----------------------------------------------------- -------- ------------------------------------ LOG_ID NUMBER TRACEID VARCHAR2(250 CHAR) TYPE VARCHAR2(250 CHAR) NODE VARCHAR2(250 CHAR) URL VARCHAR2(4000 CHAR) TOKEN VARCHAR2(4000 CHAR) METHOD VARCHAR2(250 CHAR) TIMESTAMPREQ VARCHAR2(100 CHAR) BODY CLOB RESPONSE CLOB TIMESTAMPRES VARCHAR2(100 CHAR) REC_DATE DATE </code> In addition to other indexes, there is one index on columns (Trunc(Rec_Date), Type, Node, Method). I try to get min or max of trunc(rec_date) but surprisingly it doesn't uses the index min/max optimization. Below is the command and trace file output: <code> SELECT min(trunc(rec_date)) FROM t where trunc(rec_date) is not null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.09 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 94.40 567.28 256176 507776 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 94.43 567.38 256176 507777 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 242 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=507776 pr=256176 pw=0 time=0 us starts=567286694) 4176565 4176565 4176565 TABLE ACCESS FULL T (cr=507776 pr=256176 pw=0 time=86468 us starts=709057596 cost=86468 size=34748199 card=3860911) SELECT min(trunc(rec_date)) FROM t where rec_date is not null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.84 17.41 5 259917 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.84 17.41 5 259918 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 242 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=259917 pr=5 pw=0 time=0 us starts=17415568) 4184139 4184139 4184139 TABLE ACCESS FULL T (cr=259917 pr=5 pw=0 time=86242 us starts=106246737 cost=86242 size=34748199 card=3860911) </code> Can you please help me understanding why t...

I have a view, cust.viewname, that references a table in a different schema I'll call "data". According to Oracle documentation for CREATE VIEW, the optional clause BEQUEATH DEFINER causes the view to run using the permissions of the view owner. This is the default if not specified. The view schema has SELECT privs to a table in the data schema, but not WITH GRANT OPTION. The view owner has access to the data table, but when I try to create the view, it throws <b>ORA-01720: grant option does not exist for 'DATA.TABLENAME'</b> <code>CREATE OR REPLACE VIEW cust.viewname BEQUEATH DEFINER AS SELECT field1, field2, ..., fieldN FROM data.tablename;</code> If the view operates as the owner, and the view owner has access to the referenced objects, then why should grants to objects in other schemas require WITH GRANT OPTION? Is there a way around WITH GRANT OPTION? Our cloud provider refuses to allow WITH GRANT OPTION, but we have an ongoing need to create views in CUST that reference objects in other schemas and will be used by users other than CUST. Thanks in advance.

Hello, I need some guidance on using Oracle's Identity Access Manager and an OAuth Provider in an ASP.NET Core/Framework application? Could you please point me in the right direction, either examples or docs. Thanks.

Tom, Could you please tell me if there are any other important differences, advantages with DBMS_STATS over ANALYZE other than the points listed below. 1. DBMS_STATS can be done in parallel 2. Monitoring can be done and stale statistics can be collected for changed rows using DBMS_STATS. Thanks, Suresh

I was deleting some dump file inside oracle user, accidentally I hit rm -rf /* , then permission denied error came, after that i am unable to login db, all db's property file deleted, now i am login to oracle user its coming link -bash-4.2$. inside oracle home only trace file is there other's are deleted, please help me to recover.

I recently had a need to see some table data at regular 10 minute intervals throughout the day. I thought I would be able to use the following query: <code>WITH TIMES(TIME) AS ( SELECT TRUNC(SYSDATE) TIME FROM DUAL UNION ALL SELECT TIME+ INTERVAL ?10? MINUTE FROM TIMES WHERE TIME < SYSDATE ? INTERVAL '10' MINUTE ) SELECT TIMES.TIME,T.* FROM TIMES CROSS JOIN AS_OF(TBL,TIMES.TIME) T</code> Making use of a SQL_MACRO function called AS_OF that parameterizes the timestamp part of an AS OF TIMESTAMP flashback query: <code>CREATE FUNCTION AS_OF (p_tab in DBMS_TF.TABLE_T, p_time in DATE) RETURN VARCHAR2 SQL_MACRO IS BEGIN RETURN `SELECT * FROM p_tab AS OF TIMESTAMP(p_time)?; END AS_OF; /</code> But the AS_OF function throws an error ORA-00984: column not allowed here. It was my understanding that p_time would be treated like a bind variable, but that doesn't appear to be the case here. Could you clarify what's going on with non-table parameters to SQL_MACRO functions behind the scenes, and help me understand why this error gets thrown?

Hello Ive installed an Instant Client 21 local copy , but i cant find the mkstore and orapki modules in any of the offered packages... Which package should provide them ? Ive decompressed all of the below, but none have them Basic JDBC SDK sqltools The best i could find is the following info https://ogobrecht.com/posts/2020-07-29-how-to-use-mkstore-and-orapki-with-oracle-instant-client/ Objective is to create a token to avoid needing to provide password to a module/shell script that will perform some oracle automation

It's a constant discussion. I work with a lot of back end developer and They maintain that firing DML statements from their applications is the same as using calls to procedures or functions. I insist that there are reasons of practicality, security, performance, etc. but I need the opinion of someone authorized as you are. Please I need your opinion. Thank you very much for sharing so much knowledge. Regards MIGUEL DE BELLIZ

Hello, We have created 5 new tables ( Table_A - around 80K records, Table_B- around 15K records, Table_C - around 35K records, Table_D- around 55K records, and Table_E- around 130K records) These tables have been used in a view using a UNION ( The view had another set of tables from which it would fetch the data and now these new tables have been unioned) We find that soon after the data load, it takes a very long time to fetch result from the view and the application is causing a timeout.. We figured that this might be due to the Oracle statistics getting generated at later point, so we generated the stats manually soon after the data load, still the same problem. Indexes are in place. Can anyone please point out what might be the issue here? or any inputs on how we can figure this out is appreciated.

My problem is the following: I have created a procedure, in which I assign roles to users. For example: <code> SET serveroutput ON; declare vsqlsql varchar(360); permiso varchar(360):='PROBA'; BEGIN vsqlsql:='GRANT '||UPPER(permiso)||' TO ueueue001'; EXECUTE IMMEDIATE(vsqlsql); END; / --DROP PROCEDURE ROL; create or replace PROCEDURE ROL (permiso in VARCHAR2) AS vsqlsql varchar(360); BEGIN vsqlsql:='GRANT '||UPPER(permiso)||' TO ueueue001'; EXECUTE IMMEDIATE(vsqlsql); END; / exec ROL('PROBA'); select * from dba_role_privs where grantee='UEUEUE001' AND GRANTED_ROLE='PROBA'; </code> Everything is correct, until I execute the procedure and it tells me: <code> Procedimiento PL/SQL terminado correctamente. Procedure ROL compilado Error que empieza en la linea: 28 del comando : BEGIN ROL('PROBA'); END; Informe de error - ORA-01924: el rol 'PROBA' no se ha otorgado o no existe ORA-06512: en "IVAN.ROL", linea 8 ORA-06512: en linea 1 01924. 00000 - "role '%s' not granted or does not exist" *Cause: Either the role was not granted to the user, or the role did not exist. *Action: Create the role or grant the role to the user and retry the operation. GRANTEE GRANT ADM DEL DEF COM -------------------------------------------------------------------------------------------------------------------------------- ----- --- --- --- --- UEUEUE001 PROBA NO NO NO NO </code> When I execute the block the role is assigned correctly, but when I call the procedure it tells me that the role does not exist. Why?

I got an error when running a Form. ORA-01654: unable to extend index SYSTEM.ARAID_INVOICE_SEQ_ID by 512 in tablespace ar_idx When I check the database, I found the following Size Used Free Tablespace in MG. in MG. in MG. -------------------- ------ ------ ------ ------ ------ AR_IDX 250 204 (82%) 46 (18%) INDEX_NAME TABLESPACE INITIAL_EX NEXT_EXT Max Use M-U MAX_SIZE USED_SIZE ------------------------------ ---------- ---------- ---------- ---- ---- ---- ------------ ------------ ARAID_INVOICE_SEQ_ID AR_IDX 15360K 1024K 249 3 246 263.00M 17.01M Segments that cannot extend =========================== Seg OWNER Name Tablespace NEXTEXT LARGEST --------------- ------------------------------ --------------- --------- --------- SYSTEM ARID_SALES_RECAP AR_IDX 1054720 819200 SYSTEM ARID_REVENUE_CENTRE 1054720 819200 SYSTEM ARID_CODE_YEAR 1054720 819200 SYSTEM ARID_ACCOUNT_NO 1054720 819200 SYSTEM ARAID_CODE_YEAR 1054720 819200 SYSTEM ARAID_INVOICE_SEQ_ID 1054720 819200 SYSTEM ARAID_SALES_RECAP 1054720 819200 SYSTEM PK_CMSTMP_BATCH 1048576 819200 SYSTEM IDX_ARID_MC 1054720 819200 It seems that there is plenty of space for the indexes. What is the reason for the error? Best Regards

Hi, Greetings. I would like to apply data redaction policy on a table to accomplish the following business requirement. The table contains PII filed SSN. The security(Data Redaction Policy) should be defined as below. 1. Person with Manager Role can see all the parts of SSN.( No redaction). 2. Person with Supervisor Role can see the last 4 digits SSN.( partial redaction). 3. Person with call center representative role can see nothing.( Full redaction). From looking through the documentation and examples out in the web world, I understand we can apply redaction policy like a switch on for particualr set of roles and off for the rest of the roles. Any suggestions and Guidance on how we can implement the above security in place would be highly appreciated. Thank you so much for your time. Sincerely CT

Good morning Tom TDE and generating a new Master Key - what happens in the background I have seen an answer where you are explaining that issuing a new Master Key does not have an impact on the table (or data) being encrypted as such. I understand that. In my understanding there are 2 keys involved - Table Key and the Master Key. The Table Key is actually used for encryption of the tables and not the Master Key. The Master Key is used to encrypt the Table Key and this encrypted Table Key is then stored in the Oracle data dictionary. So when one ReKey (set a new Master Key), in my understanding what happens is that the encrypted Table Key must first be decrypted with the existing Master Key, after which this decrypted "open" Table Key is encrypted with the newly generated Master Key. For example: Current Master Key(CMK) : w5du8xs Table Key (TK) : tktktktk (actually a random string) Encrypting TK with MK : w5du8xs (enc) tktktktk -> a8j4n3gh which is stored in Oracle data dictionary So when generating a new Master Key (nMK), Oracle must first decrypt a8j4n3gh with the CMK (w5du8xs) to get the TK of tktktktk, after which the new Master Key (nMK) of for example zxcvbn275 is used to encrypt the Table Key and this new encrypted value gets stored again. So IF above mentioned understanding is correct, does that mean that one will not be able to generate a new Master Key if for some "funny" reason (it will not be funny!), the wallet has been damaged and Oracle cannot get hold of the current Master Key? Or also stated otherwise, to generate a new Master key, there must be an existing activated Master Key. Thanks for allowing me the opportunity to ask.

Hello- Required to report room numbers for every period of the day the student is in (like a sort of pivot format). I have the query that pulls the required information: -- <code> Select s.schoolid scid, s.student_number stnu , s.lastfirst na, cc.expression, sec.room rm , s.grade_level grl from students s, sections sec, cc cc where s.id = cc.studentid and cc.sectionid = sec.id and sec.schoolid = 87 and cc.termid = 3200 </code> Here is the sample of 5 student records returned by the above: <code>SCID STNU NA EXPRESSION RM GRL 87 100 AAA 3-5(A-E) E003 12 87 100 AAA 9(A-E) Patti12 12 87 100 AAA 6(A-E) E207B 12 87 100 AAA 8(A-E) E002B 12 87 100 AAA 9(A-E) PattiAll 12 87 100 AAA 7(A-E) ECaf 12 87 100 AAA 2(A-E) EGym 12 87 100 AAA 1(A-E) E002B 12 87 101 BBB 9(A-E) MularzAll 12 87 101 BBB 1(A-E) E201 12 87 101 BBB 7-8(A-E) 12 87 101 BBB 2(A-E) EGym 12 87 101 BBB 6(A-E) ECaf 12 87 101 BBB 3-5(A-E) E003 12 87 102 CCC 8(A-E) 12 87 102 CCC 2(A-E) E200 12 87 102 CCC 6(A-E) E209 12 87 102 CCC 1(A-E) EGym 12 87 102 CCC 7(A-E) ECaf 12 87 102 CCC 9(A-E) 12 87 102 CCC 3-5(A-E) E008 12 87 103 DDD 3-5(A-E) E106B 12 87 103 DDD 2(A-E) E207A 12 87 103 DDD 8(A-E) 12 87 103 DDD 6(A-E) E209 12 87 103 DDD 1(A-E) EGym 12 87 103 DDD 7(A-E) ECaf 12 87 103 DDD 9(A-E) 12 87 104 EEE 1(A-E) EGym 12 87 104 EEE 8(A-E) E208A 12 87 104 EEE 6(A-E) E207B 12 87 104 EEE 7(A-E) E306 12 87 104 EEE 2(A-E) E208A 12 87 104 EEE 5(A-E) ECaf 12 87 104 EEE 3(A-E) E110 12 87 104 EEE 4(A-E) E001B 12</code> Expect to have an out put like so: <code>STNU NA GRL Per 1 Per 2 Per 3 Per 4 Per 5 Per 6 Per 7 Per 8 Per 9 Per 10 Per 11 Per 12 100 AAA 12 E002B EGym E003 E003 E003 E207B ECaf E002B PattiAll 101 BBB 12 E201 EGym E003 E003 E003 ECaf MularzAll 102 CCC 12 EGym E200 E008 E008 E008 E209 ECaf 103 DDD 12 EGym E207A E106B E106B E106B E209 ECaf 104 EEE 12 EGym E208A E110 E001B ECaf E207B E306 E208A </code> A student can attend a max of 12 periods in a day. Student can attend a two period class (Ex:7-8(A-E)) or a three period class (Ex:3-5(A-E)) or a single period class (Ex: 1(A-E)) Thanks for any assistance! Venkat --=========== <code>create table t ( SCID number (4) , STNU number(8), NA varchar2(30), EXPRESSION varchar2(10), RM varchar2(10), GRL number(2) ) ;</code> <code>insert into t values ( 87,100,'AAA','3-5(A-E)','E003',12); insert into t values ( 87,100,'AAA','9(A-E)','Patti12',12); insert into t values ( 87,100,'AAA','6(A-E)','E207B',12); insert into t values ( 87,100,'AAA','8(A-E)','E002B',12); insert into t values ( 87,100,'AAA','9(A-E)','PattiAll',12); insert into t values ( 87,100,'AAA','7(A-E)','ECaf',12); insert into t values ( 87,100,'AAA','2(A-E)','EGym',12); insert into t values ( 87,100,'AAA','1(A-E)','E002B',12); insert into t values ( 87,101,'BBB','9(A-E)','MularzAll',12); insert into t values ( 87,101,'BBB','1(A-E)','E201',12); insert into t values ( 87,101,'BBB','7-8(A-E)','',12); insert into t values ( 87,101,'BBB','2(A-E)','EGym',12); insert into t values ( 87,101,'BBB','6(A-E)','ECaf',12); insert into t values ( 87,101,'BBB','3-5(A-E)','E003',12); insert into t values ( 87,102,'CCC','8(A-E)','',12); insert into t values ( 87,102,'CCC','2(A-E)','E200',12); insert into t values ( 87,102,'CCC','6(A-E)','E209',12); insert into t values ( 87,102,'CCC','1(A-E)','EGym',12); insert into t values ( 87,102,'CCC','7(A-E)','ECaf',12); insert into t values ( 87,102,'CCC','9(A-E)','',12); insert into t values ( 87,102,'CCC','3-5(A-E)','E008',12); insert into t values ( 87,103,'DDD','3-5(A-E)','E106B',12); insert into t values ( 87,103,'DDD','2(A-E)','E207A',12); insert into t values ( 87,103,'DDD','8(A-E)','',12); insert into t values ( 87,10...

Hello Could you please help me with the following issue I'm developing a procedure that gets and processes large blob data from a table. It is much slower in my dev database 19.16 in comparison with a dev database 11.2.0.4. I noticed in the trace file that 'direct path read' takes much longer time in 19.16 (ela field). Do you know where the timing difference come from? The both databases have the same SGA. They're placed on the virtual machines with same computing resources that use the same SSD disk. 11.2.0.4: <code>WAIT #3061052452: nam='direct path read' ela=3 file number=2 first dba=16173 block cnt=1 obj#=3862810 tim=1665396576739512</code> 19.16: <code>WAIT #139622124454344: nam='direct path read' ela=105 file number=44 first dba=194052 block cnt=1 obj#=3635694 tim=167978375852</code> Here is the test case that I used to generate the trace files: <code>CREATE OR REPLACE DIRECTORY BLOB_DIR AS '/tmp'; CREATE TABLE test_blob_tab ( id NUMBER, blob_data BLOB ); DECLARE l_bfile BFILE; l_blob BLOB; l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; BEGIN INSERT INTO test_blob_tab (id, blob_data) VALUES (1, empty_blob()) RETURN blob_data INTO l_blob; l_bfile := BFILENAME('BLOB_DIR', 'myzipfile.zip');--the zip file size is 109MB DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); DBMS_LOB.loadblobfromfile ( dest_lob => l_blob, src_bfile => l_bfile, amount => DBMS_LOB.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset); DBMS_LOB.fileclose(l_bfile); COMMIT; END; / declare l_blob blob; begin for i in 1..10000 loop for c in (select dbms_lob.substr(tbt.blob_data,2000,i) blob_data from test_blob_tab tbt) loop l_blob:=c.blob_data; end loop; end loop; end; / </code> Here are the DBMS_METADATA.GET_DDL on table test_blob_tab for both environments 11.2.0.4 database: <code> "CREATE TABLE "APPS"."TEST_BLOB_TAB" ( "ID" NUMBER, "BLOB_DATA" BLOB ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "APPS_TS_TX_DATA" LOB ("BLOB_DATA") STORE AS BASICFILE ( TABLESPACE "APPS_TS_TX_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING ) " </code> 19.16 database: <code> "CREATE TABLE "APPS"."TEST_BLOB_TAB" ( "ID" NUMBER, "BLOB_DATA" BLOB ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "APPS_TS_TX_DATA" LOB ("BLOB_DATA") STORE AS SECUREFILE ( TABLESPACE "APPS_TS_TX_DATA" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES ) "</code> Thank you, Alex

 If you are wondering what is the difference between K8s and K9s then it's not a big deal. Its not that someone has added another letter or another node to Kubernetes. Here is K9s vs K8s difference explained:

K8s is just an abbreviation of Kubernetes. It's simply counting the 8 letters between k and s of word Kubernetes. 

K9s is is a terminal-based user interface which makes it easier to manage K8s.

That's all there is to it.

K9s is simply a Kubernetes CLI To Manage Your Clusters In Style. It's open source as well and its repo is here.

Kubernetes standard definition goes something like this, "Kubernetes is an open-source container orchestration system for automating software deployment, scaling, and management."

And K9s is simply yet another K8s tool. Someone was joking at social media that due to inflation k8s is now k9s. K9s is actually pretty cool though. k9s is straight up one of the best tools to use alongside a cluster. 

As per K9s repo, K9s provides a terminal UI to interact with your Kubernetes clusters. The aim of this project is to make it easier to navigate, observe and manage your applications in the wild. K9s continually watches Kubernetes for changes and offers subsequent commands to interact with your observed resources.

The installation of K9s is pretty straight forward but make sure that you do the pre-requisites properly. K9s uses 256 colors terminal mode. On `Nix system make sure TERM is set accordingly. In order to issue manifest edit commands make sure your EDITOR env is set. K9s prefers recent kubernetes versions ie 1.16+. 

Enjoy.