ORA-01031: insufficient privileges raised during materialized view creation on another schema

Scenario: ORA-01031: insufficient privileges raised during the creation of materialized view (MVIEW) on another schema by the SYSTEM user. CREATE MATERIALIZED VIEW kaysar.mview_test BUILD IMMEDIATE AS (SELECT * from kaysar.test); [Error] Execution (4: 26): ORA-01031: insufficient privileges Investigation: The SYSTEM already has the privilege of CREATE_MATERIALIZED_VIEW to create the MVIEW on another schema but failed…

ORA-39142: incompatible version number 5.1 in dump file

Error: Experienced the below error during the import of the export dump file to the Oracle 12.1. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 5.1 in…

Connect to MariaDB from Oracle and perform DML on remote MariaDB table

Background: In enterprises most cases their database infrastructures or applications are built on different databases, sometimes data needs to be transferred or synced from one database to another heterogenous database. Oracle supports heterogenous connectivity to popular databases like MySQL, SQL Server, MariaDB, DB2, Sybase and more on the list. Here we will try to establish…

ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_****

Error: Errors in file /u01/app/oracle/diag/rdbms/bpay/bpay/trace/bpay_j000_24499.trc: ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_586″ ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at “SYS.DBMS_STATS”, line 47207 ORA-06512: at “SYS.DBMS_STATS_ADVISOR”, line 882 ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 20059 ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 22201 ORA-06512: at “SYS.DBMS_STATS”, line 47197 Cause: It’s a known bug, the advisory…

Oracle table partitioning: why, when and how

Why partitioning: Enhances the performance, manageability, and availability. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces. When to partition a table: Tables larger than 2gb size. Historical data Contents must be stored in different storage Avoid index maintenance when data is removed Index maintenance on parts of data like rebuilding…

Enabling tracing for oracle LISTENER

Background: For the oracle errors like “ORA-12170: TNS:Connect timeout occurred”, “ORA-12535: TNS:operation timed out”, “ORA-12560: TNS:protocol adapter error”, “TNS-00505: Operation timed out” and like others related to oracle listener it is useful of tracing the listener activities to dig down further. Workaround: Connect to the listener control utility from the command line or shell. LSNRCTL…

Oracle-Error: opiodr aborting process unknown ospid ( ) as a result of ORA-609

Background: After shutdown and restarted the oracle instance by an executive, client can’t connect to database instance though database is up and running and listener service is up. OS: Windows Server 2012 x64, Oracle: 11g R2, Data Guard Configured Workaround: From the alert_SID.log file found the below error raised after shutdown and consecutive startup and when…

Database design migration from SQL Server to Oracle by Enterprise Architect

Background: One of my development project initially was on SQL Server as backend database. But consistently facing problem of ‘ODBC failed to connect’ throughout a year. The database was the SQL Server XE. Convinced to transfer my database to Oracle as it is very reliable on ODBC connection. So intended to initiate a database design migration from…

Oracle Error: No listener – LISTENER failing to start upon windows startup

Case: Today morning on my development machine (Windows 10 ) I can’t login to the oracle schema with toad or from my application showing Oracle Error : No listener. Upon further investigation found that listener is not starting Automatically though its service configured to run automatically. Workaround: To resolve the “No Listener”, restarted workstation several times after changes…

Oracle Tutorial: Using single-row or multiple-row functions to customize output

This Oracle Tutorial: Using single-row or multiple-row functions contains the lessons mentioned below: Types of SQL Functions Single Row Functions Number Functions Character Functions Date Functions Conversion Functions General Functions Multiple Row/Aggregate Functions Restricting Group results With HAVING Clause Nesting Group Functions Download: Using single-row or multiple-row functions to customize output

Oracle Tutorial: SQL SELECT Statements

I had to arrange training session over Database Management System to my colleagues which used oracle as backend. This “Oracle Tutorial: SQL SELECT Statements” is about basics of DBMS, SQL and SELECT statements. Includes: Types of SQL statement Arithmetic Operators and Precedence Null Values Column Alias Concatenation Operator/Literal Character String Duplicate Rows/DISTINCT keyword Download: SQL Select…