Pakistan's First Oracle Blog

 In today's data-driven world, businesses rely on robust databases to manage their mission-critical workloads. Oracle Database 23ai Free offers a streamlined experience of this industry-leading database, with resource limits of up to 2 CPUs for foreground processes, 2 GB of RAM, and 12 GB of user data on disk. This free version is designed for ease of use and simple download, making it an ideal starting point for exploring the capabilities of Oracle Database.

A key feature that sets Oracle Database 23ai apart is its AI Vector Search capability. But what exactly are vectors? In simple terms, vectors are mathematical representations of data that capture complex relationships and patterns. They are a way to encode data, such as text, images, or audio, into numerical values that can be easily processed and analyzed by machines. Vectors enable computers to understand the semantic meaning and context of data, allowing for more accurate and efficient searching and analysis.

Vector search takes this concept a step further. It is a technique used to quickly identify similar data points within a vast dataset. Traditional search methods rely on keyword matching or exact phrase searches, but vector search enables more nuanced and intuitive queries. By comparing the vector representations of different data points, vector search can identify patterns and relationships that would be missed by traditional search methods.

Oracle AI Vector Search builds on this technology, introducing a converged database capability that revolutionizes the way businesses interact with their data. 

By storing vectors as a native data type and utilizing vector indexes and SQL functions, AI Vector Search enables fast and simple similarity search queries on both structured and unstructured data. This means that customers can quickly identify similar information across documents, images, and other unstructured data sources. Furthermore, AI Vector Search allows prompts to large language models (LLMs) to be augmented with private business data or domain knowledge, unlocking new possibilities for data-driven insights and decision-making.

With Oracle AI Vector Search, businesses can unlock the full potential of their data, uncovering hidden patterns and relationships that drive innovation and growth. Whether you're working with text, images, or other data types, Oracle Database 23ai's AI Vector Search capability is poised to transform the way you search, analyze, and interact with your data.

 In today's data-driven world, AI needs fuel to power innovative applications. Oracle Database 23ai brings AI directly to your data, making it effortless to develop cutting-edge apps and tackle mission-critical tasks. But what makes this possible? Enter GraphQL, a game-changing query language that's changing the way we interact with data.

GraphQL is an open-source data query and manipulation language developed by Facebook in 2015. It allows clients to specify exactly what data they need, eliminating unnecessary requests and improving performance. GraphQL's declarative nature makes it a perfect fit for modern, data-driven applications. Its history is impressive, with Facebook open-sourcing it in 2015, followed by widespread adoption by tech giants like GitHub, Pinterest, and Airbnb.

GraphQL solves several pain points that have plagued developers for years. By allowing clients to receive only requested data, GraphQL reduces data transfer and minimizes bandwidth usage. This results in improved performance, as fewer requests and optimized data retrieval lead to faster responses. Additionally, GraphQL supports multiple data sources, making integration seamless. Its self-documenting nature and intuitive queries simplify development, making it a favorite among developers.

Traditionally, relational databases like Oracle used SQL for querying. However, SQL can be restrictive, requiring multiple queries to fetch related data. GraphQL changes this by enabling simplified complex queries and real-time data retrieval. This makes it perfect for applications requiring instant updates. Oracle's integration of GraphQL into its database takes this power to the next level, offering native support, optimized queries, and robust security features.

With Oracle Database 23ai and GraphQL, developers can build innovative applications faster and more efficiently. GraphQL's nested queries and relationships make fetching complex data easier, while Oracle's database engine optimizes queries for peak performance. This powerful combination enables developers to focus on building exceptional user experiences.

Imagine querying a movie database to get personalized recommendations. With GraphQL, you can fetch exactly what you need. For example:

These examples illustrate the potential of Oracle Database 23ai and GraphQL. By combining AI-powered data analysis with intuitive querying, developers can unlock new possibilities in application development.

With Oracle Database 23ai and GraphQL, building innovative movie apps is faster, easier, and more powerful than ever.

Hope this helps.

 As an Oracle database administrator, managing sessions is crucial for maintaining performance and availability. This script provides a comprehensive solution for monitoring and cleaning up idle and blocking sessions.

The script identifies blocking sessions exceeding a specified threshold (default: 60 minutes), kills them, and sends notification emails. It also identifies idle sessions exceeding a specified threshold (default: 60 minutes), kills them, and sends notification emails. Key components include session identification using V$SESSION , V$PROCESS , and V$TRANSACTION , threshold settings, notification email functionality using TRACK.SEND_EMAIL , and error handling.

To implement this script, you'll need to declare variables for threshold settings (minutes), notification lists, and other necessary variables. The script then monitors blocking sessions using a FOR loop, killing each blocking session and sending notifications. A similar loop monitors idle sessions.

To maximize the effectiveness of this script, consider the following best practices:

• Schedule the script to run regularly (e.g., every 30 minutes).
• Adjust threshold settings according to your database requirements.
• Monitor notification emails for killed sessions.

 This video shows how to locally install Tencent Hunyuan3D-1 model for 3D generation.

Code:

 As an Oracle database administrator, you've likely encountered errors that make your heart skip a beat. One such error is ORA-00240: control file enqueue held for more than 120 seconds. But before you panic, let's break down what this error means and how to address it.

This error occurs when the control file enqueue is held for an extended period (over 120 seconds). The control file is a critical component of the Oracle database, managing database structure and integrity.

If you see this error occasionally, and your instance remains up and running, it's likely a fleeting glitch. Ignore it and move on.

However, if:

• The error occurs frequently
• Your instance hangs or crashes
• Performance is severely impacted

You need to be worried about it.

In my experience, ORA-00240 can be triggered by:

• High session counts conflicting with OS ulimits
• Shared pool latch contention (as noted in some MOS documents)
• Bugs in the Oracle software (resolvable with PSUs or one-off patches)

You should be checking:

• Check alert logs for frequency and patterns.
• Verify OS ulimits are adequately set.
• Monitor shared pool latch contention using

Don't panic over occasional ORA-00240 errors. However, frequent occurrences warrant immediate attention. By understanding the causes and taking proactive steps, you'll minimize downtime and ensure your Oracle database runs smoothly.

I still get bit anxious when I receive this ORA-1652 error in production databases but its a hard nut to crack. Encountering ORA-1652 errors can be frustrating, especially when dealing with temporary segment usage. To quickly identify the root cause, use the following query to analyze temporary segment allocation:

To further investigate:

• Real-Time Session Monitoring: Use Oracle Enterprise Manager (EM) or query V$SESSION to identify active sessions consuming temporary space.
• Temporary Segment Usage: Query V$TEMPSEG_USAGE to analyze temporary segment allocation.
• v$tempseg_usage: Examine this view to identify temporary segment usage patterns.

Key Views to Analyze

• dba_hist_active_sess_history: Historical session data
• V$SESSION: Real-time session information
• V$TEMPSEG_USAGE: Temporary segment usage details
• v$tempseg_usage: Temporary segment usage patterns

Common Causes of ORA-1652

• Insufficient temporary tablespace
• Large sorting or joining operations
• Inefficient SQL queries
• Incorrect indexing

Best Practices

• Regularly monitor temporary segment usage
• Optimize SQL queries to reduce temporary space allocation
• Ensure sufficient temporary tablespace allocation
• Consider partitioning large tables

By using these queries and views, you'll quickly identify the causes of ORA-1652 errors and take corrective action to optimize your database performance.  

 As an Oracle database administrator or developer, running heavy, long-running, and critical production queries can be nerve-wracking, especially in cloud environments. One crucial aspect is estimating the query execution time to plan and manage resources effectively. In this post, we'll explore a valuable query that provides an approximate ETA (Estimated Time of Arrival) for parallel queries on large datasets.

Knowing the ETA helps:

• Plan resource allocation and utilization
• Manage expectations and prioritize tasks
• Identify potential performance bottlenecks
• Optimize queries for better performance

ETA Query:

This query joins three dynamic performance views:

• gv$px_session (parallel execution sessions)
• gv$px_process (parallel execution processes)
• gv$session_longops (long-running operations)

It calculates:

• eta_min: estimated time to completion in minutes
• pct_done: percentage of work completed

To customize the query:

• Filter by specific username or operation name
• Add additional columns for more detailed information
• Use gv$session instead of gv$px_session for non-parallel queries

By using this query, you'll gain valuable insights into your critical production queries and make informed decisions about resource allocation and optimization.

 As a developer, connecting to databases can be a hassle. But what if you could leverage a serverless platform to streamline your connections? Enter Oracle Functions, a fully managed, scalable, and on-demand functions-as-a-service platform built on Oracle Cloud Infrastructure (OCI). In this post, we'll explore how to connect to an Oracle database using Oracle Functions and Python.

With Oracle Functions, you can focus on writing code, not managing infrastructure. Its serverless architecture eliminates administrative tasks, allowing you to:

• Scale effortlessly
• Pay only for executed functions
• Enjoy enterprise-grade security

To connect to your Oracle database, you'll need:

• cx_Oracle library (install using pip install cx_Oracle)
• Oracle database credentials (username, password, host, and service name)

Here's an example Python function to get you started:

When using Oracle Functions to connect to your database:

• Ensure your Oracle database is accessible from your Oracle Functions environment.
• Store sensitive credentials securely using Oracle Cloud Infrastructure's Vault service.
• Optimize your queries for performance.

By using Oracle Functions and Python, you can simplify your Oracle database connections and focus on building scalable applications.

 This video is a step-by-step tutorial to fine-tune or do full training of a voice model F5-TTS and E2-TTS on your own custom voice dataset locally. 

 This video shows how to locally install F5-TTS and E2-TTS models on Windows easily with Pinokio, which are fairytaler that fakes fluent and faithful speech with flow matching.

As an Oracle database administrator, I've come to appreciate the power of supplemental logging, especially when migrating databases or tables from one location to another. In this post, we'll explore what supplemental logging is, how it works, and provide practical examples to get you started.

According to Oracle documentation, supplemental logging records additional columns in redo log files, which is essential for redo-based applications. This process ensures that rows can be uniquely identified, making database migration and recovery more efficient.

When supplemental logging is enabled, redo logs contain extra columns from tables, including:

• Primary key columns (if defined)
• Unique index columns (if no primary key exists)
• All columns (if no primary key or unique index exists)

The good news is that supplemental logging doesn't impact your Oracle instance's performance.

To enable supplemental logging, use the following commands:

Scenario 1: Table with Primary Key

ALTER TABLE my_table ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Scenario 2: Table with Unique Index (no Primary Key)

Identify the first unique index in alphabetical order and create a supplemental log group on its columns.

Scenario 3: Table with No Primary Key or Unique Index

ALTER TABLE my_table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

When migrating large tables, consider the following best practices:

• Limit initial load to avoid overhead on the Oracle instance.
• Migrate during non-business hours to minimize impact.
• Break down large tables into smaller tasks.

By using supplemental logging, you'll streamline your Oracle database migration process and ensure a smoother transition.

Suppose you're migrating a table customers with a primary key customer_id. To enable supplemental logging, run:

ALTER TABLE customers ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

This simple command ensures that the customer_id column is included in the redo logs, making it easier to identify and recover rows during migration.

I hope this helps.

 Whenever I need to play with any AI model for free or for fraction of cost in cloud, my first preference is Oracle cloud's free tier for AI. You can also take advantage of it. 

Oracle Cloud is offering a range of AI services for free, allowing developers and data scientists to experiment and innovate without incurring costs. These services provide a comprehensive platform for building, training, and deploying machine learning models, as well as performing advanced text, speech, and image analysis.

At the heart of Oracle Cloud's AI offerings is its Machine Learning service, which provides up to 4,700 free hours. This service enables users to build, train, deploy, and manage machine learning models collaboratively, with scalability and power. Additionally, users can leverage digital assistant capabilities for up to 51 hours, allowing them to interact with natural language conversations and automate tasks without managing multiple apps and websites.

Beyond machine learning, Oracle Cloud's AI services include OCI Language for sophisticated text analysis at scale, OCI Speech for automatic speech recognition, OCI Vision for deep-learning-based image analysis, and OCI Document Understanding for extracting text, tables, and data from documents via APIs and CLI tools. These services are available with tiered pricing based on use cases, but users can experiment with them for free within the specified limits.

To support AI and machine learning workloads, Oracle Cloud also provides free compute and storage resources. This includes two Always Free AMD-based compute VMs with 1/8 OCPU and 1 GB memory each, up to 4,500 hours of compute for OCI Kubernetes Engine, and up to 500 GB of storage for Docker-based OCI Container Registry.

With Oracle Cloud's free AI services, developers and data scientists can develop and deploy machine learning models, explore advanced AI capabilities, and build innovative applications without incurring costs. Whether you're a seasoned AI expert or just starting to explore the possibilities of artificial intelligence, Oracle Cloud's free AI services provide an ideal platform to get started. Sign up for an Oracle Cloud account today and start playing with AI.

 This video shows how to install and use Whisper large-v3-turbo in free google colab for transcription in gradio which is a finetuned version of a pruned Whisper large-v3. 

Code:

 Oracle Database offers a comprehensive implementation of Resource Description Framework (RDF), seamlessly integrating semantic data management with robust enterprise capabilities. This converged database combines the benefits of RDF with Oracle's renowned features:

• Transactional support for data integrity
• High-performance querying and data processing
• Advanced security measures for data protection
• Reliability and scalability for mission-critical applications

What sets Oracle apart is its tight integration of RDF with SQL. This unique convergence enables:

• RDF databases to be accessed and queried like traditional SQL databases
• Seamless interoperability between semantic and relational data models

Retrieval-Augmented Generation (RAG) is a paradigm-shifting approach in natural language processing (NLP) and artificial intelligence (AI) that combines the strengths of retrieval and generation models. Traditional language models rely solely on generating text based on learned patterns, often lacking specific knowledge or context. RAG addresses this limitation by integrating external knowledge sources into the generation process. This hybrid approach involves two primary components: a retriever and a generator. The retriever fetches relevant information from vast knowledge bases or databases, while the generator uses this retrieved information to create contextually accurate and informative text.

RAG enables AI models to access and leverage vast amounts of external knowledge, making them more informative, accurate, and contextually relevant. This technology has far-reaching implications for various applications, including question answering, text summarization, chatbots, and language translation. By bridging the gap between knowledge retrieval and text generation, RAG significantly enhances the capabilities of AI systems, allowing them to provide more precise and informative responses.

Knowledge Graphs (KGs) are structured representations of knowledge that organize and store information in the form of entities, attributes, and relationships. Inspired by semantic networks and graph theory, KGs provide a robust framework for AI systems to reason, infer, and retrieve knowledge. A Knowledge Graph typically consists of nodes (entities) interconnected by edges (relationships), which can represent various types of associations, such as hierarchical, causal, or semantic relationships.

Knowledge Graphs serve as a foundation for various AI applications, including question answering, recommendation systems, and natural language processing.

Example:

Assume we have two tables: Author and Publication.

Using SQL/PGQ, define the explicit relationships as follows:

CREATE PROPERTY GRAPH research_network_pg

VERTEX TABLES (

  author

    KEY (id)

    LABEL author

    PROPERTIES ALL COLUMNS,

  publication

    KEY (id)

    LABEL publication

    PROPERTIES ALL COLUMNS

)

EDGE TABLES (

  author as writes_publication

    KEY (id)

    SOURCE KEY (id) REFERENCES author (id)

    DESTINATION KEY (written_by) REFERENCES publication (id)

    LABEL writes

    PROPERTIES ALL COLUMNS

);

Querying the Graph:

Retrieve authors and their publication titles:

SELECT 'Author' AS label, t.*

FROM   GRAPH_TABLE (research_network_pg

  MATCH

  (a IS author) -[c IS writes]-> (p IS publication)

  COLUMNS (a.name AS author_name, p.title AS publication_title)

) t

ORDER BY 1;

In this example:

• Authors and publications are nodes (vertexes).
• The "writes" relationship connects authors to their publications (edges).
• The query retrieves authors and their corresponding publication titles. 

 This video is a step-by-step tutorial on implementing Agentic RAG with Llama3.2 3B model on your own dataset locally.

Code:

 When I started my Oracle DBA journey in early 2000s, one of the best advice I received from Oracle's community forums was to make sure to understand underlying Oracle's database architecture. One of the corner-stone of this architecture was Oracle's Background processes. If you are an Oracle DBA, and you don't know what these background processes are, then you are not a DBA. 

Oracle Database's robust performance relies on a network of background processes working harmoniously behind the scenes. These processes, automatically initiated when the database instance starts, ensure seamless operation and optimal performance for multiple users. The presence of specific background processes depends on the database features in use.

In this blog post, I am trying to explain the difference between PMON and PMAN background processes in as simple words as possible. A standard database configuration includes mandatory background processes that run by default in a read/write database instance. These processes are initiated with a minimally configured initialization parameter file. PMON and PMAN are 2 of those mandatory processes.

PMON: The Process Sentinel

The Process Monitor (PMON) serves as a vigilant guardian, periodically scanning for abnormally terminated processes. Upon detection, PMON delegates cleanup tasks to the Cleanup Main Process (CLMN), which orchestrates the termination and session cleanup. This hierarchical approach ensures efficient process management. As an operating system process, PMON operates independently of threads. Its reach extends beyond database instances to Oracle Automatic Storage Management (ASM) and ASM Proxy instances.

PMAN: The Background Process Maestro

The Process Manager (PMAN) expertly oversees a suite of background processes, including:

• Shared servers
• Pooled servers
• Job queue processes

PMAN dynamically monitors, spawns, and terminates these processes as needed, ensuring optimal resource allocation. Its responsibilities encompass:

• Dispatcher and shared server processes
• Connection broker and pooled server processes for database resident connection pools
• Job queue processes
• Restartable background processes

 This video is a step-by-step tutorial to learn RAG in an easy way with LlamaIndex on your own data in free google colab.

Code:

This video shows how to locally install Rope Pearl Live on Windows for for live swapping using Webcam and for live streaming the swapped videos using virtual camera.

Code:

 This video is a step-by-step tutorial to use Online DPO to fine-tune a model locally on custom dataset. ODPO is a new alignment method from DeepMind to boost the performance of LLMs.

Code:

 This video shows how to locally install MiniG model which is trained on a synthesis dataset of over 120 million entries and has 1M token context window. It deals with both text and images.