DBA Blogs
SQL*Loader-503 in 19c for UNC names
future of dba
questions about sampling
Local temporary tablespace
Is Your Print Screen not Working? We are here to help!
Print Screen is a hotkey present on your keyboard that helps you take the snapshot of your desktop screen and save it on the computer. All you have to do is press this button, open Paint, press ‘Ctrl+V,’ crop the image to your setting and Voila! Computers make everything we do more comfortable, including the […]
The post Is Your Print Screen not Working? We are here to help! appeared first on VitalSoftTech.
Installing Oracle GoldenGate Microservices – Part 2 of 2
In the my last post, I showed you how to install the binaries for Oracle GoldenGate Microservices. Then at the end of the post, I mentioned that you will need to run Oracle GoldenGate Configuration Assistant to finish the configuration of Oracle GoldenGate Microservices. Let’s take a look at how to do this. After installing […]
The post Installing Oracle GoldenGate Microservices – Part 2 of 2 appeared first on DBASolved.
Installing Oracle GoldenGate Microservices – Part 1 of 2
Recently I was exchanging emails with a few community members about Oracle GoldenGate Microservices. One of them commented on that Oracle GoldenGate Microservices was difficult to install; at the time, I was puzzled. The reason I was puzzled was due to the install process for Oracle GoldenGate hasn’t changed since Oracle GoldenGate 12.1 came out […]
The post Installing Oracle GoldenGate Microservices – Part 1 of 2 appeared first on DBASolved.
5 Ways to Read Deleted Comments from the Reddit Archive
Have you ever wondered how you can restore Reddit’s deleted posts from the Reddit archive? Here’s a short guide about how to see deleted Reddit posts! If you’re a Reddit aficionado, someone who is fond of reading long, fascinating threads about different topics, then you’ve probably come across deleted comments at one point or another. […]
The post 5 Ways to Read Deleted Comments from the Reddit Archive appeared first on VitalSoftTech.
Excel Average Function – A Step By Step Tutorial
Calculating average when you only have a few entries in your data is one thing but having to do the same with hundreds of data entries is a whole another story. Even using a calculator for finding the average of this many numbers can be highly time-consuming and to be honest, quite frustrating. After all, […]
The post Excel Average Function – A Step By Step Tutorial appeared first on VitalSoftTech.
Oracle Apex Social Sign in
Oracle Apex Social Sign in gives you the ability to use oAuth2 to authenticate and sign in users to your Oracle Apex apps using social media like Google, Facebook and others.
Google and Facebook are the prominent authentication methods currently available, others will probably follow. Social sign in is easy to use, you don't need to code, all you have to do is to obtain project credentials from say Google and then pass them to the Oracle Apex framework and put the sign in button to the page which will require authentication and the flow will kick in. I would say at most a 3 step operation. Step by step instructions are available in the blog posts below.
Further reading:
- http://dgielis.blogspot.com/2018/06/facebook-google-and-custom.html
- http://www.grassroots-oracle.com/2019/01/social-sign-in-authentication-scheme.html
Basic Replication -- 11 : Indexes on a Materialized View
Since it is also a Table, you can build custom Indexes on it.
Here, my Source Table has an Index on OBJECT_ID :
SQL> create table source_table_1
2 as select object_id, owner, object_name
3 from dba_objects
4 where object_id is not null
5 /
Table created.
SQL> alter table source_table_1
2 add constraint source_table_1_pk
3 primary key (object_id)
4 /
Table altered.
SQL> create materialized view log on source_table_1;
Materialized view log created.
SQL>
I then build Materialized View with an additional Index on it :
SQL> create materialized view mv_1
2 refresh fast on demand
3 as select object_id as obj_id, owner as obj_owner, object_name as obj_name
4 from source_table_1
5 /
Materialized view created.
SQL> create index mv_1_ndx_on_owner
2 on mv_1 (obj_owner)
3 /
Index created.
SQL>
Let's see if this Index is usable.
SQL> exec dbms_stats.gather_table_stats('','MV_1');
PL/SQL procedure successfully completed.
SQL> explain plan for
2 select obj_owner, count(*)
3 from mv_1
4 where obj_owner like 'H%'
5 group by obj_owner
6 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2523122927
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 15 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 2 | 10 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MV_1_NDX_ON_OWNER | 5943 | 29715 | 15 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJ_OWNER" LIKE 'H%')
filter("OBJ_OWNER" LIKE 'H%')
Note how this Materialized View has a column called "OBJ_OWNER" (while the Source Table column is called "OWNER") and the Index ("MV_1_NDX_ON_OWNER") on this column is used.
You would have also noted that you can run DBMS_STATS.GATHER_TABLE_STATS on a Materialized View and it's Indexes.
However, it is NOT a good idea to define your own Unique Indexes (including Primary Key) on a Materialized View. During the course of a Refresh, the MV may not be consistent and the Unique constraint may be violated. See Oracle Support Document # 67424.1
Joining two tables with time range
TDE Encryption of local Oracle databases. KEK hosted on cloud service?
Migration from oracle 6i and 11g to Oracle APEX
One way Encryption where no one can decrypt
Connection retry when database services failover
Best Practice using database services
Impact of Index Monitoring in production server
Generating DDL in Oracle SQL Developer
Oracle Database Views and Tables for Oracle GoldenGate
Oracle GoldenGate for Oracle over the last few releases have been moving towards an integrated architecture. This means that there is more views and tables within the Oracle Database that support Oracle GoldenGate. You can quickly find these views and tables by using a where clause with a filter for GoldenGate: select * from all_views […]
The post Oracle Database Views and Tables for Oracle GoldenGate appeared first on DBASolved.
Pages
