DBA Blogs

Nightly process slowing down.

Tom Kyte - Thu, 2019-11-21 11:50
Hi. We have a process that runs every night that is beginning to slow down and we need some help to find the resources to analyse the problem. In our setup, unfortunately both transaction schemas and warehousing (statistics) schemas are kept on...
Categories: DBA Blogs

Golden Signals for performance

Tom Kyte - Thu, 2019-11-21 11:50
Hello Tom I am a member of an site reliability team (SRE) and we are trying to develop SRE "golden signals" for an Oracle 11g/12c database. These signal are: 1) Throughput 2) Latency 3) Response Time 4) Error rate (not sure about this one...
Categories: DBA Blogs

Difference between DBRM and IORM

Tom Kyte - Thu, 2019-11-21 11:50
Dear Sir, Please help me to know below points. 1)difference between DBRM and IORM? 2)difference between ACFS,ADVM and DBFS? Thanks Pradeep
Categories: DBA Blogs

SQL*Loader-503 in 19c for UNC names

Tom Kyte - Thu, 2019-11-21 11:50
Hi TOM, next Month we are migrating from Oracle 12.1.0.2 to Oracle 19, so I installed the new Oracle Client 19c on my PC. Using SQLLDR with (all in one line) sqlldr userid=user/passwd@server control=\\server\share$\folder\controlfile.ini ...
Categories: DBA Blogs

future of dba

Tom Kyte - Thu, 2019-11-21 11:50
Dear, I am Core Oracle DBA. I do not have experience in RAC,Golden Gate. Sometimes I worried about future of oracle dba in upcoming years in India for all industry like medium and large company. Can you give some recommendation or new technology n...
Categories: DBA Blogs

questions about sampling

Tom Kyte - Thu, 2019-11-21 11:50
<b>a. how does sampling work internally ?</b> meaning: how is it determined by Oracle which blocks are read by a sample-query and which not ? <code>create table t as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5; cre...
Categories: DBA Blogs

Local temporary tablespace

Tom Kyte - Thu, 2019-11-21 11:50
Since Oracle 12.2, Oracle has introduced a new feature of local temporary tablespace. The documentation does not provide any information on the pros and cons of this new feature. Can you please provide some information on why this feature was introdu...
Categories: DBA Blogs

Excel Average Function – A Step By Step Tutorial

VitalSoftTech - Wed, 2019-11-13 10:33

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.

Categories: DBA Blogs

Oracle Apex Social Sign in

Kubilay Çilkara - Wed, 2019-11-13 03:27
In this post I want to show you how I used the Oracle Apex Social Sign in feature for my Oracle Apex app. Try it by visiting my web app beachmap.info.




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:




Categories: DBA Blogs

Basic Replication -- 11 : Indexes on a Materialized View

Hemant K Chitale - Tue, 2019-11-12 08:46
A Materialized View is actually also a physical Table (by the same name) that is created and maintained to store the rows that the MV query is supposed to present.

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



Categories: DBA Blogs

Joining two tables with time range

Tom Kyte - Mon, 2019-11-11 11:49
Dear AskTom-Team! I wonder whether it is possible to join two tables that have time ranges. E.g a table 'firmname' holds the name of a firm with two columns from_year and to_year that define the years the name is valid. Table 'address' holds the a...
Categories: DBA Blogs

TDE Encryption of local Oracle databases. KEK hosted on cloud service?

Tom Kyte - Mon, 2019-11-11 11:49
Hi, We want to encrypt some on-premise Oracle databases. If possible, we would like to avoid to use a physical HSM or to contract with a third party HSM cloud provider. Is this possible to store the KEK's in GCP or Azure, and to interface our lo...
Categories: DBA Blogs

Migration from oracle 6i and 11g to Oracle APEX

Tom Kyte - Mon, 2019-11-11 11:49
Dear all, i have oracle forms that are built on 6i and 11g and i want to migrate them to oracle apex, is it possible to create an application based on the oracle 6i form migrated, if so can you provide me with a document or a video showing the pro...
Categories: DBA Blogs

One way Encryption where no one can decrypt

Tom Kyte - Mon, 2019-11-11 11:49
Hi Tom, Kindly suggest some one way encryption for the data in table,where no one can decrypt it? Read few articles about this and not satisfied. Kindly help.
Categories: DBA Blogs

Connection retry when database services failover

Tom Kyte - Mon, 2019-11-11 11:49
Hello, Ask Tom, Team. I have a two-node RAC running a database service with one preferred instance and one available instance. It is supposed that RAC automatically failovers the services to available when preferred instance crashes. 1. Will ...
Categories: DBA Blogs

Best Practice using database services

Tom Kyte - Mon, 2019-11-11 11:49
Hello, Ask Tom Team. We have a two-node RAC running a database. The app connects to the database using scan name (best practice), so we are not using vip to connect to database directly. The tnsnames.ora is: DBPROD = (DESCRIPTION = (ADD...
Categories: DBA Blogs

Impact of Index Monitoring in production server

Tom Kyte - Mon, 2019-11-11 11:49
Hi, we are planning to change global index to local index for partitioned table in production. Since we need to drop an index and create it again and we don't know the time it will take, we decided to monitor the index which are being used by the app...
Categories: DBA Blogs

Generating DDL in Oracle SQL Developer

Tom Kyte - Mon, 2019-11-11 11:49
Dear AskTom-Team! Is there a possibility in the Oracle Developer to suppress duplicate DDL code? For example when generating the DDL from my relational model the foreign key constraint is generated twice, i.e. for both tables that are involved in th...
Categories: DBA Blogs

Oracle Database Views and Tables for Oracle GoldenGate

DBASolved - Fri, 2019-11-08 16:40

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.

Categories: DBA Blogs

Clustered Indexes

Tom Kyte - Fri, 2019-11-08 02:48
Is clustered Index is faster than Non-clustered Index?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs