#get ddl of table in oracle

[enationstechnologyltd]

A handy online table structure design software tool, softfactory, to share with you

The online table structure design software tool needs to be straightforward, eliminating confusing settings, simplifying complexities, and prioritizing practicality. It’s easy to get started, and all of these features can be found in softfactory.

Softfactory is a browser-based online table structure design software tool that leverages artificial intelligence to improve efficiency. It can generate CRUD code and push it to development tools. It’s free for everyone to use, ensuring smooth operations in your database.

Having said so much, let’s get to the point.

Simple Workbench

Don’t underestimate our simple workbench; it is comprehensive and powerful, enabling everyone to quickly get started and grasp the key points.

Popular Database Settings

When creating a new project, softfactory can adjust database settings:

Database type: Supports MySQL, Oracle, MariaDB, PostgreSQL, SQL Server, SQLite, etc., and more database types will be supported in the future. You can choose the database character set and database encoding settings, and even if you make mistakes, you can modify them.

Visual Data Tools

Softfactory integrates three practical tools:

Table designers can use AI to build tables or do it manually. Once the table structure design is completed, CRUD code can be generated by AI and pushed to your local development tools. Table structure generation, firstly, you can obtain the table’s DDL and copy it in the preview window and designer. Secondly, you can generate CRUD code based on the table structure, generate the specified code language, and push the code to the local development tool through the development tool plugin. Diagrams can automatically sort and arrange tables dragged in via foreign keys. Tables without foreign key relationships can also be manually connected and deleted.

Import Existing Structure

If you already have a database but lack a database model, you can use reverse engineering to import the database into the project.

Open Existing Project

You don’t have to start designing the database from scratch. The program allows you to import tables, and the types of table files that can be imported include DDL, PDManer, PowerDesigner.

Arrange Objects in a Few Minutes

Imported objects are pre-arranged, considering their relationships. However, you can freely arrange them to make the project more readable.

Sharing

Users can invite members by providing the team member’s email or sharing the link to invite and share access privileges. Assemble a team, easily share projects, collaborate with multiple team members, communicate in real time, and cooperate seamlessly.

Softfactory, a collision of AI and databases, is setting sail to help programmers liberate their hands. We will continue to dig into the innovative application potential of AI, explore the stage of new AI directions, and continue to promote the development of AI in broader application areas.

If you also like softfactory, welcome to visit: https://www.softfactory.cloud/

[ocptechnology]

How To Get Ddl Of All Tablespaces In Oracle

How To Get Ddl Of All Tablespaces In Oracle

Hello Friends, In this article we are going to show you how to get ddl of all tablespaces in oracle. Get DDL of All Tablespaces Oracle Use below query to get ddl of all tablespaces in oracle. SQL>set heading off; SQL>set echo off; SQL>Set pages 999; SQL>set long 90000; SQL>spool ddl_of_tablesspace.sql SQL>select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces…

[oraclerider]

How to Generate table DDL

in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL.

Hi, in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL. You also read below articles: DB LINK DDL GET TABLESPACE DDLGET ROLE DDL Get more oracle scritps How do you get DDL of a table in Oracle? In real time environment some times we need to DDL of an existing table. We can perfo this activity with the help of dbms_metadata.get_ddl…

[qupitstechsolutions]

MySQL

What Is Mysql

=My is the daughter’s name of the MySQL’s co-founder, Monty Widenius. =MySQL is a database management system that allows you to manage relational databases. =It is open source software backed by Oracle. =Even though MySQL is open source software, you can buy a commercial license version from Oracle to get a premium support services. =MySQL can run on various platforms UNIX, Linux, Windows, etc. You can install it in a server or even in a desktop.

MySQL Database

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −

MySQL is released under an open-source license. So you have nothing to pay to use it.

MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.

MySQL uses a standard form of the well-known SQL data language.

MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.

MySQL works very quickly and works well even with large data sets.

MySQL is very friendly to PHP, the most appreciated language for web development.

MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

DDL(Data Definition language) =Create =Drop =Alter =Truncate

DML(Data Manipulation language) =Insert =update =Delete

DDL

=we can create & modify the structure.

=We can drop the entire data.

=We can't Retrieve data.

=Truncate we can drop the entire data only not affect the structure.

DML

=We can't modify the structure.

=We can delete the entire and particular data.

=We can retrieve the data using rollback command.

[computingpostcom]

MariaDB is a popular open source relational databases, developed by MySQL developers. Unlike MySQL, MariaDB was meant to be totally free for use. Its development puts a lot of considerations on performance and stability. MariaDB is also mostly preferred for being robust and scalable with new storage engines. MariaDB 10.6 also comes with a number of improvements from version 10.5 and some additional features. mariaDB has different tools and plugins that make it widely applicable. It is the default database in most Linux distributions. This guide presents a step-by-step installation of MariaDB 10.6 on Debian 11/10/9 from MariaDB APT repository. MariaDB 10.6 is the current stable version of MariaDB and comes with a number of improvements and new features as discussed below: New Features of MariaDB 10.6 Ignored Indexes – These are indexes that are visible and maintained but not used by the optimizer sys schema supported– This is a “system” database containing views and procedures for investigating performance problems. SKIP LOCKED – Locked tables are skipped from being updated or selected. JSON_TABLE() – can create a JSON table that can be used as a subquery from a JSON document. OFFSET…FETCH…[WITH TIES] – WITH TIES is an optional clause that adds extra functionality. Example as used Oracle compatibility – There are ongoing works in making MariaDB compatible with OracleDB with some Oracle Syntaxes and functions already added. Improvements in MariaDB 10.6 Some of the improvements on MariaDB 10.6 from its predecessors include: Atomic DDL – CREATE, ALTER, DROP and RENAME are atomic and crash safe. If MariaDB server crashes while processing any of these operations, the change will either e done completely or not done at all. InnoDB improvements – First insert to an empty table is faster. Also writes to temporary tables are avoided.Faster implicit and explicit temporary tables. Improvements in Galera. Ability to enable encrypted connections between two nodes without downtime. Also added flags to specify if galera controversial compatible features should be enabled. Clean up to remove unsupported features such as TukoDB Engine, Cassandra Engine, some InnoDB variables and some innodb_checksum_algorithm. Installing MariaDB 10.6 on Debian 11/10/9 Follow the next steps to install MariaDB 10.6 on your latest Debian Servers Step 1: System Upgrade It is always recommend to have the latest system packages before installation to avoid dependency issues. Run the below commands: sudo apt update sudo apt upgrade sudo reboot Step 2: Install required Packages Next, install necessary packages as below: sudo apt-get install curl software-properties-common dirmngr Step 3: Add MariaDB Signing Key and APT Repository Run the below commands respectively to import MariaDB signing key and add MariaDB APT repository For Debian 11 (Bullseye): curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup sudo bash mariadb_repo_setup --os-type=debian --os-version=buster --mariadb-server-version=10.6 wget http://ftp.us.debian.org/debian/pool/main/r/readline5/libreadline5_5.2+dfsg-3+b13_amd64.deb sudo dpkg -i libreadline5_5.2+dfsg-3+b13_amd64.deb For Debian 10 (Buster): curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup sudo bash mariadb_repo_setup --mariadb-server-version=10.6 For Debian 9 “Stretch” curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup sudo bash mariadb_repo_setup --mariadb-server-version=10.6 Step 4: Install MariaDB on Debian 11/10/9 Next, update packages and install MariaDB server sudo apt-get update sudo apt-get install mariadb-server mariadb-client Proceed to install MariaDB packages and all its dependencies: .... The following NEW packages will be installed: galera-4 gawk libcgi-fast-perl libcgi-pm-perl libclone-perl libdaxctl1 libdbd-mariadb-perl libdbi-perl libencode-locale-perl libfcgi-bin libfcgi-perl libfcgi0ldbl

libgdbm-compat4 libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3 libmpfr6 libndctl6 libperl5.32 libpmem1 libsigsegv2 libterm-readkey-perl libtimedate-perl liburi-perl lsof mariadb-client mariadb-client-10.6 mariadb-client-core-10.6 mariadb-common mariadb-server mariadb-server-10.6 mariadb-server-core-10.6 mysql-common perl perl-modules-5.32 rsync 0 upgraded, 41 newly installed, 0 to remove and 0 not upgraded. Need to get 37.5 MB of archives. After this operation, 265 MB of additional disk space will be used. Do you want to continue? [Y/n] y Step 5: Start and Enable MariaDB Now run the following commands to start MariaDB and enable it to automatically start on system reboot sudo systemctl start mariadb sudo systemctl enable mariadb Confirm that MariaDB is running $ sudo systemctl status mariadb ● mariadb.service - MariaDB 10.6.3 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor pres> Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Wed 2021-07-28 23:03:14 EAT; 8min ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Step 6: Secure MariaDB Installation Once MariaDB is successfully installed, proceed to secure it as below: $ sudo mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n] Y Enabled successfully! Reloading privilege tables.. ... Success! You already have your root account protected, so you can safely answer 'n'. Change the root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! Step 7: Check MariaDB Version To check MariaDB version, we need to login to MariaDB as below. Use -p if you have set the root password. $ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 10.6.3-MariaDB-1:10.6.3+maria~buster mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Then run the following MySQL command to check MariaDB version MariaDB [(none)]> SELECT VERSION(); +----------------------------------------+ | VERSION() | +----------------------------------------+ | 10.6.4-MariaDB-1:10.6.4+maria~bullseye | +----------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> Step 8: MariaDB Basic Commands To get started with basic commands, we can check to create a database, users and tables. MariaDB Create Database To create a database in MariaDB, login as shown above and run the below command. #Create a new database CREATE DATABASE testdb; Query OK, 1 row affected (0.000 sec) #If the database with the same exists CREATE DATABASE testdb; ERROR 1007 (HY000): Can't create database 'testdb'; database exists #Create a database if already exits CREATE OR REPLACE DATABASE testdb; Query OK, 2 rows affected (0.009 sec) #First check if a database exists CREATE DATABASE IF NOT EXISTS testdb; Query OK, 1 row affected, 1 warning (0.000 sec) # Check Databases MariaDB SHOW DATABASES; MariaDB add User and Grant Privileges To create a user and grant privileges; #Create user mariadb CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; #Grant all privileges to the user GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; #Grant privileges to a specific database GRANT ALL PRIVILEGES ON 'DB1'.* TO 'user1'@'localhost'; #Remember to refresh the privileges FLUSH privileges; #To check user grants in MariaDB SHOW GRANTS FOR 'myuser'@'locahost'; Create a Table and Add Data MariaDB Once you have created a database, you can create table and add data into it CREATE TABLE employees (id INT, name VARCHAR(20), email VARCHAR(20)); INSERT INTO employees (id,name,email) VALUES(01,"lorna","lorna@example.com") MariaDB Clean up To completely remove MariaDB, run the following commands. sudo apt purge mariadb-server sudo rm -rf /var/lib/mysql/ That’s it. You have successfully installed MariaDB 10.6 on Debian 11/10/9. It is a simple and straightforward installation. I hope the guide has been helpful.

[swarnalata31techiio]

What is PostgreSQL? The PostgreSQL database system is an advanced, enterprise-class, and open-source relational database system.In addition to SQL, PostgreSQL supports JSON querying as well as non-relational queries. What Makes PostgreSQL Stand Out? PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature, even before Oracle. PostgreSQL is a general-purpose object-relational database management system. It allows us to add custom functions developed using different programming languages such as C/C++, Java, etc. Procedural Languages Support PostgreSQL supports four standard procedural languages, which allows the users to write their own code in any of the languages and it can be executed by PostgreSQL database server. DDL - data definition language Data definition language (DDL) is used the following commands: Data type declaration CREATE - creates a new database, table, index or procedure. ALTER - modifies an existing database object. DROP - deletes an existing database, table, index or procedure. TRUNCATE TABLE - empties a table for reuse. DML - data manipulation language Data manipulation language is used to work with tables and their contents as in:

INSERT - inserts new rows in a table. UPDATE - updates existing rows in the table. DELETE - deletes rows from a table. SELECT - the main method for querying data from the database. Advantages of PostgreSQL Below are the main advantages/benefits of PostgreSQL: PostgreSQL can run dynamic websites and web apps as a LAMP stack option. PostgreSQL’s write-ahead logging makes it a highly fault-tolerant database. PostgreSQL source code is freely available under an open source license. Low maintenance and administration for both embedded and enterprise use of PostgreSQL. Disadvantages of PostgreSQL Below are the disadvantages/limitations of PostgreSQL: Postgres is not owned by one organization. So, it has had trouble getting its name out there despite being fully featured and comparable to other DBMS systems Changes made for speed improvement requires more work than MySQL as PostgreSQL focuses on compatibility Many open source apps support MySQL, but may not support PostgreSQL On performance metrics, it is slower than MySQL.

[globalmediacampaign]

Migrating user-defined types from Oracle to PostgreSQL

Migrating from commercial databases to open source is a multistage process with different technologies, starting from assessment, data migration, data validation, and cutover. One of the key aspects for any heterogenous database migration is data type conversion. In this post, we show you a step-by-step approach to migrate user-defined types (UDT) from Oracle to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL. We also provide an overview of custom operators to use in SQL queries to access tables with UDT in PostgreSQL. Migrating UDT from Oracle to Aurora PostgreSQL or Amazon RDS for PostgreSQL isn’t always straightforward, especially with UDT member functions. UDT defined in Oracle and PostgreSQL store structured business data in its natural form and work efficiently with applications using object-oriented programming techniques. UDT in Oracle can have both the data structure and the methods that operate on that data within the relational model. Though similar, the approaches to implement UDT in Oracle and PostgreSQL with member functions have subtle differences. Overview At a high level, migrating tables with UDT from Oracle to PostgreSQL involves following steps: Converting UDT – You can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another. Unlike PostgreSQL, user-defined types in Oracle allow PL/SQL-based member functions to be a part of UDT. Because PostgreSQL doesn’t support member functions in UDT, you need to handle them separately during UDT conversion. Migrating data from tables with UDT – AWS Database Migration Service (AWS DMS) helps you migrate data from Oracle databases to Aurora PostgreSQL and Amazon RDS for PostgreSQL. However, as of this writing, AWS DMS doesn’t support UDT. This post explains using the open-source tool Ora2pg to migrate tables with UDT from Oracle to PostgreSQL. Prerequisites Before getting started, you must have the following prerequisites: The AWS SCT installed on a local desktop or an Amazon Elastic Compute Cloud (Amazon EC2) instance. For instructions, see Installing, verifying, and updating the AWS SCT. Ora2pg installed and set up on an EC2 instance. For instructions, see the Ora2pg installation guide. Ora2pg is an open-source tool distributed via GPLv3 license. EC2 instances used for Ora2pg and the AWS SCT should have connectivity to the Oracle source and PostgreSQL target databases.  Dataset This post uses a sample dataset of a sporting event ticket management system. For this use case, the table DIM_SPORT_LOCATION_SEATS with event location seating details has been modified to include location_t as a UDT. location_t has information of sporting event locations and seating capacity. Oracle UDT location_t The UDT location_t has attributes describing sporting event location details, including an argument-based member function to compare current seating capacity of the location with expected occupancy for a sporting event. The function takes expected occupancy for the event as an argument and compares it to current seating capacity of the event location. It returns t if the sporting event location has enough seating capacity for the event, and f otherwise. See the following code: create or replace type location_t as object ( LOCATION_NAME VARCHAR2 (60 ) , LOCATION_CITY VARCHAR2 (60 ), LOCATION_SEATING_CAPACITY NUMBER (7) , LOCATION_LEVELS NUMBER (1) , LOCATION_SECTIONS NUMBER (4) , MEMBER FUNCTION COMPARE_SEATING_CAPACITY(capacity in number) RETURN VARCHAR2 ); / create or replace type body location_t is MEMBER FUNCTION COMPARE_SEATING_CAPACITY(capacity in number) RETURN VARCHAR2 is seat_capacity_1 number ; seat_capacity_2 number ; begin if ( LOCATION_SEATING_CAPACITY is null ) then seat_capacity_1 := 0; else seat_capacity_1 := LOCATION_SEATING_CAPACITY; end if; if ( capacity is null ) then seat_capacity_2 := 0; else seat_capacity_2 := capacity; end if; if seat_capacity_1 >= seat_capacity_2 then return 't'; else return 'f'; end if; end COMPARE_SEATING_CAPACITY; end; / Oracle table DIM_SPORT_LOCATION_SEATS The following code shows the DDL for DIM_SPORT_LOCATION_SEATS table with UDT location_t in Oracle: CREATE TABLE DIM_SPORT_LOCATION_SEATS ( SPORT_LOCATION_SEAT_ID NUMBER NOT NULL , SPORT_LOCATION_ID NUMBER (3) NOT NULL , LOCATION location_t, SEAT_LEVEL NUMBER (1) NOT NULL , SEAT_SECTION VARCHAR2 (15) NOT NULL , SEAT_ROW VARCHAR2 (10 BYTE) NOT NULL , SEAT_NO VARCHAR2 (10 BYTE) NOT NULL , SEAT_TYPE VARCHAR2 (15 BYTE) , SEAT_TYPE_DESCRIPTION VARCHAR2 (120 BYTE) , RELATIVE_QUANTITY NUMBER (2) ) ; Converting UDT Let’s start with the DDL conversion of location_t and the table DIM_SPORT_LOCATION_SEATS from Oracle to PostgreSQL. You can use the AWS SCT to convert your existing database schema from Oracle to PostgreSQL. Because the target PostgreSQL database doesn’t support member functions in UDT, the AWS SCT ignores the member function during UDT conversion from Oracle to PostgreSQL. In PostgreSQL, we can create functions in PL/pgSQL with operators to have similar functionality as Oracle UDT does with member functions. For this sample dataset, we can convert location_t, to PostgreSQL using the AWS SCT. The AWS SCT doesn’t convert the DDL of member functions for location_t from Oracle to PostgreSQL. The following screenshot shows our SQL code. PostgreSQL UDT location_t The AWS SCT converts LOCATION_LEVELS and LOCATION_SECTIONS from the location_t UDT to SMALLINT for Postgres optimizations based on schema mapping rules. See the following code: create TYPE location_t as ( LOCATION_NAME CHARACTER VARYING(60) , LOCATION_CITY CHARACTER VARYING(60) , LOCATION_SEATING_CAPACITY INTEGER , LOCATION_LEVELS SMALLINT , LOCATION_SECTIONS SMALLINT ); For more information about schema mappings, see Creating mapping rules in the AWS SCT. Because PostgreSQL doesn’t support member functions in UDT, the AWS SCT ignores them while converting the DDL from Oracle to PostgreSQL. You need to write a PL/pgSQL function separately. In order to write a separate entity, you may need to add additional UDT object parameters to the member function. For our use case, the member function compare_seating_capacity is rewritten as a separate PL/pgSQL function. The return data type for this function is bool instead of varchar2 (in Oracle), because PostgreSQL provides a bool data type for true or false. See the following code: CREATE or REPLACE FUNCTION COMPARE_SEATING_CAPACITY (event_loc_1 location_t,event_loc_2 integer) RETURNS bool AS $$ declare seat_capacity_1 integer; seat_capacity_2 integer ; begin if ( event_loc_1.LOCATION_SEATING_CAPACITY is null ) then seat_capacity_1 = 0 ; else seat_capacity_1 = event_loc_1.LOCATION_SEATING_CAPACITY; end if; if ( event_loc_2 is null ) then seat_capacity_2 = 0 ; else seat_capacity_2 = event_loc_2 ; end if; if seat_capacity_1 >= seat_capacity_2 then return true; else return false; end if; end; $$ LANGUAGE plpgsql; The UDT conversion is complete yielding the PL/pgSQL function and the UDT in PostgreSQL. You can now create the DDL for tables using this UDT in the PostgreSQL target database using the AWS SCT, as shown in the following screenshot. In the next section, we dive into migrating data from tables containing UDT from Oracle to PostgreSQL. Migrating data from tables with UDT In this section, we use the open-source tool Ora2pg to perform a full load of the DIM_SPORT_LOCATION_SEATS table with UDT from Oracle to PostgreSQL. To install and set up Ora2pg on an EC2 instance, see the Ora2pg installation guide. After installing Ora2pg, you can test connectivity with the Oracle source and PostgreSQL target databases. To test the Oracle connection, see the following code: -bash-4.2$ cd $ORACLE_HOME/network/admin -bash-4.2$ echo "oratest=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oratest.xxxxxxx.us-west-2.rds.amazonaws.com )(PORT =1526))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = UDTTEST)))" >> tnsnames.ora -bash-4.2$ sqlplus username/password@oratest SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 05:05:35 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> To test the Aurora PG connection, see the following code: -bash-4.2$ psql -h pgtest.xxxxxxxx.us-west-2.rds.amazonaws.com -p 5436 -d postgres master Password for user master: psql (9.2.24, server 11.6) WARNING: psql version 9.2, server version 11.0. Some psql features might not work. SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256) Type "help" for help. postgres=> You use a configuration file to migrate data from Oracle to PostgreSQL with Ora2pg. The following is the configuration file used for this sample dataset. Ora2pg has many options to copy and export different object types. In this example, we use COPY to migrate tables with UDT: -bash-4.2$ cat ora2pg_for_copy.conf ORACLE_HOME /usr/lib/oracle/11.2/client64 ORACLE_DSN dbi:Oracle:sid=oratest ORACLE_USER master ORACLE_PWD xxxxxxx DEBUG 1 EXPORT_SCHEMA 1 SCHEMA dms_sample CREATE_SCHEMA 0 COMPILE_SCHEMA 0 PG_SCHEMA TYPE COPY PG_DSN dbi:Pg:dbname=postgres;host=pgtest.xxxxxxxxx.us-west-2.rds.amazonaws.com;port=5436 PG_USER master PG_PWD xxxxxxxx ALLOW DIM_SPORT_LOCATION_SEATS BZIP2 DATA_LIMIT 400 BLOB_LIMIT 100 LONGREADLEN6285312 LOG_ON_ERROR PARALLEL_TABLES 1 DROP_INDEXES 1 WITH_OID 1 FILE_PER_TABLE The configuration file has the following notable settings: SCHEMA – Sets the list of schemas to be exported as part of data migration. ALLOW – Provides a list of objects to migrate. Object names could be space- or comma-separated. You can also use regex like DIM_* to include all objects starting with DIM_ in the dms_sample schema. DROP_INDEXES – Improves data migration performance by dropping indexes before data load and recreating them in the target database post-data migration. TYPE – Provides an export type for data migration. For our use case, we’re migrating data to the target table using COPY statements. This parameter can only have a single value. For more information about the available options in Ora2pg to migrate data from Oracle to PostgreSQL, see the Ora2pg documentation. In the following code, we migrate the DIM_SPORT_LOCATION_SEATS table from Oracle to PostgreSQL using the configuration file created previously: -bash-4.2$ ora2pg -c ora2pg_for_copy.conf -d Ora2Pg version: 18.1 Trying to connect to database: dbi:Oracle:sid=oratest Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Retrieving table information... [1] Scanning table DIM_SPORT_LOCATION_SEATS (2 rows)... Trying to connect to database: dbi:Oracle:sid=oratest Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Retrieving partitions information... Dropping indexes of table DIM_SPORT_LOCATION_SEATS... Looking how to retrieve data from DIM_SPORT_LOCATION_SEATS... Data type LOCATION_T is not native, searching on custom types. Found Type: LOCATION_T Looking inside custom type LOCATION_T to extract values... Fetching all data from DIM_SPORT_LOCATION_SEATS tuples... Dumping data from table DIM_SPORT_LOCATION_SEATS into PostgreSQL... Setting client_encoding to UTF8... Disabling synchronous commit when writing to PostgreSQL... DEBUG: Formatting bulk of 400 data for PostgreSQL. DEBUG: Creating output for 400 tuples DEBUG: Sending COPY bulk output directly to PostgreSQL backend Extracted records from table DIM_SPORT_LOCATION_SEATS: total_records = 2 (avg: 2 recs/sec) [========================>] 2/2 total rows (100.0%) - (1 sec., avg: 2 recs/sec). Restoring indexes of table DIM_SPORT_LOCATION_SEATS... Restarting sequences The data from the DIM_SPORT_LOCATION_SEATS table with UDT is now migrated to PostgreSQL. Setting search_path in PostgreSQL allows dms_sample to be the schema searched for objects referenced in SQL statements in this database session, without qualifying them with the schema name. See the following code: postgres=> set search_path=dms_sample; SET postgres=> select sport_location_seat_id,location,seat_level,seat_section,seat_row,seat_no from DIM_SPORT_LOCATION_SEATS; sport_location_seat_id | location | seat_level | seat_section | seat_row | seat_no ------------------------+----------------------------+------------+--------------+----------+--------- 1 | (Germany,Munich,75024,2,3) | 3 | S | 2 | S-8 1 | (Germany,Berlin,74475,2,3) | 3 | S | 2 | S-8 (2 rows) Querying UDT in PostgreSQL Now that both the DDL and data for the table DIM_SPORT_LOCATION_SEATS are migrated to PostgreSQL, we can query the UDT using the newly created PL/pgSQL functions. Querying Oracle with the UDT member function The following code is an example of a SQL query to determine if any stadiums in Germany have a seating capacity of more than 75,000 people. The dataset provides seating capacity information of stadiums in Berlin and Munich: SQL> select t.location.LOCATION_CITY CITY,t.LOCATION.COMPARE_SEATING_CAPACITY(75000) SEATS_AVAILABLE from DIM_SPORT_LOCATION_SEATS t where t.location.LOCATION_NAME='Germany'; CITY SEATS_AVAILABLE --------------------------------- ---------------- Munich t Berlin f The result of this SQL query shows that a stadium in Munich has sufficient seating capacity. However, the event location in Berlin doesn’t have enough seating capacity to host a sporting event of 75,000 people. Querying PG with the PL/pgSQL function The following code is the rewritten query in PostgreSQL, which uses the PL/pgSQL function COMPARE_SEATING_CAPACITY to show the same results: postgres=> select (location).LOCATION_CITY,COMPARE_SEATING_CAPACITY(location,75000) from DIM_SPORT_LOCATION_SEATS where (location).LOCATION_NAME='Germany'; location_city | compare_seating_capacity ---------------+-------------------------- Munich | t Berlin | f (2 rows) Using operators You can also use PostgreSQL operators to simplify the previous query. Every operator is a call to an underlying function. PostgreSQL provides a large number of built-in operators for system types. For example, the built-in integer = operator has the underlying function as int4eq(int,int) for two integers. You can invoke built-in operators using the operator name or its underlying function. The following queries get sport location IDs with only two levels using the = operator and its built-in function int4eq: postgres=> select sport_location_id,(location).location_levels from DIM_SPORT_LOCATION_SEATS where (location).location_levels = 2; sport_location_id | location_levels -------------------+----------------- 2 | 2 3 | 2 (2 rows) postgres=> select sport_location_id,(location).location_levels from DIM_SPORT_LOCATION_SEATS where int4eq((location).location_levels,2); sport_location_id | location_levels -------------------+----------------- 2 | 2 3 | 2 (2 rows) You can use operators to simplify the SQL query that finds stadiums in Germany with a seating capacity of more than 75,000 people. As shown in the following code, the operator >= takes the UDT location_t as the left argument and integer as the right argument to call the compare_seating_capacity function. The COMMUTATOR clause, if provided, names an operator that is the commutator of the operator being defined. Operator X is the commutator of operator Y if (a X b) equals (b Y a) for all possible input values of a and b. In this case, <= acts as commutator to the operator >=. It’s critical to provide commutator information for operators that are used in indexes and join clauses because this allows the query optimizer to flip such a clause for different plan types. CREATE OPERATOR >= ( LEFTARG = location_t, RIGHTARG = integer, PROCEDURE = COMPARE_SEATING_CAPACITY, COMMUTATOR = <= ); The following PostgreSQL query with an operator shows the same results as the Oracle query with the UDT member function: postgres=> select (location).LOCATION_CITY CITY,(location).LOCATION_SEATING_CAPACITY >=75000 from DIM_SPORT_LOCATION_SEATS where (location).LOCATION_NAME='Germany'; city | ?column? --------+---------- Munich | t Berlin | f (2 rows) You can also use the operator >= in the where clause with UDT location_t, just like any other comparison operator. With the help of the user-defined operator >= defined earlier, the SQL query takes the location_t data type as the left argument and integer as the right argument. The following SQL query returns cities in Germany where seating capacity is more than 75,000. postgres=> select (location).LOCATION_CITY from DIM_SPORT_LOCATION_SEATS where (location).LOCATION_NAME='Germany' and location >=75000; location_city --------------- Munich (1 row) Conclusion This post showed you a solution to convert and migrate UDT with member functions from Oracle to PostgreSQL and how to use operators in queries with UDT in PostgreSQL. We hope that you find this post helpful. For more information about moving your Oracle workload to Amazon RDS for PostgreSQL or Aurora PostgreSQL, see Oracle Database 11g/12c To Amazon Aurora with PostgreSQL Compatibility (9.6.x) Migration Playbook. As always, AWS welcomes feedback. If you have any comments or questions on this post, please share them in the comments. About the Authors Manuj Malik is a Senior Data Lab Solutions Architect at Amazon Web Services. Manuj helps customers architect and build databases and data analytics solutions to accelerate their path to production as part of AWS Data Lab. He has an expertise in database migration projects and works with customers to provide guidance and technical assistance on database services, helping them improve the value of their solutions when using AWS.     Devika Singh is a Solutions Architect at Amazon Web Services. Devika has expertise in database migrations to AWS and as part of AWS Data Lab, works with customers to design and build solutions in databases, data and analytics platforms. https://aws.amazon.com/blogs/database/migrating-user-defined-types-from-oracle-to-postgresql/

[crunchlearn]

SQL Intro

What's SQL?

SQL was developed by IBM at 70's to his or her main frame platform. Several decades after SQL became standardized with the American National Standards Institute (ANSI-SQL) and also International Organization for Standardization (ISO-SQL). Based on ANSI SQL is pronounced"es queue el", but a lot of database and software programmers together with background in MS SqlServer declare it"sequel".

What's RDBMS?

A Relational Database Management System can be an item of computer software employed to store and manage data in database items called tables. A relational database is a tabular data architecture arranged in rows and columns. The dining table columns known as dining table areas possess unique titles and distinct features specifying the pillar type, default value, indicators and a lot of other column faculties. The rows of this database are the real data entrances.

Most famous SQL RDBMS

Nearly all commercial database vendors are suffering from their own proprietary SQL extension predicated on ANSI-SQL conventional. As an instance the SQL version employed by MS SqlServer is popularly known as transact sql or only TSQL, '' The Oracle's variant is known as PL/SQL (short for Procedural Language/SQL), also ms-access usage Jet-SQL.

So what can you do using SQL?

O SQL questions are utilised to recover data in database tables. The SQL questions utilize the SELECT SQL key word that's an element of this Data Query Language (DQL). When we've got a table called"Orders" and you also need to select all entrances in which the sequence value is higher than $100 dictated by the sequence value, you are able to certainly do it together with the next SQL SELECT question:

FROM Orders

ORDER BY Order-value;

The FROM SQL clause specifies in that table(s) we have been regaining data. The ORDER BY clause specifies which the returned data needed to be arrange by the order-value column.

O You are able to control data stored in relational database tables, utilizing the INSERT, UPDATE and DELETE SQL key words. These three SQL controls are a part of this Data Manipulation Language (DML).

-- To add information in to a table known as"Orders" you can utilize a SQL statement comparable to this one below:

-- To alter information into a table you can use a statement such as this:

UPDATE Orders

SET order-value = 199.99

-- To delete info out of database use a statement such as the one below:

DELETE Orders

O You are able to make, alter or delete database items (example of database items are database tables, views, stored procedures, etc.), utilizing the CREATE, ALTER and DROP SQL key words. These 3 SQL key words are a part of this Data Definition Language (DDL). As an instance to make table"Orders" You Should Use the following SQL statement:

CREATE Orders

ProductID INT,

CustomerID I D,

OrderDate DATE,

Order-value Currency

O it is possible to get a grip on database items rights utilizing the GRANT and REVOKE key words, area of this Data Control Language (DCL). For instance to Permit the consumer "User1" to automatically Choose information from table"Orders" You Should Use the following SQL statement:

Now every program professional needs atleast a basic comprehension of how SQL works. If you're new to SQL, then you are feeling confused and overwhelmed initially, however as you advance you may quickly realize just how successful and refined SQL is.

[ocptechnology]

How to Check Last Password Change History?

How to Check Last Password Change History? #oracledba #oracle #shripaldba #ocptechnology

In this article, we are going to learn how to check past password change history in the oracle database. Using the below commands you can check the password change history step by step. Read: What is control file and datafile in Oracle? When was my Oracle password last changed? The below query will show you the timestamp of last password change. Here i’m checking SCOTT user password change…

[siva3155]

300+ TOP Oracle Database Backup & Recovery Interview Questions and Answers

Oracle Database Backup and Recovery Interview Questions for freshers experienced :-

1. What is an Oracle database Partial Backup? A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down. 2. What is an Oracle database Full Backup? A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter. 3. What is the difference between oracle media recovery and crash recovery? Media recovery is a process to recover database from backup when physical disk failure occure. cash recovery is a automated process take care by oracle when instance failure occure. 4. What is db_recovery_file_dest in oracle? When do you need to set this value? Give me the steps to perform the point in time recovery with a backup which is taken before the reset logs of the db? Tell me about the steps required to enable the RMAN backup for a target database? In oracle db_recovery_file_dest specifies a default location of flash recovery area which contains multiplexed current control files, online redo logs as well as archived logs, Rman backups,flash back logs. db_recovery_file_dest_size should be specified as well. 5. What is Restricted Mode of Instance Startup in Oracle? To Enable Restricted Session Alter system enable restricted session; To Disable Restricted Session Alter system disable restricted session; To Start the Database in Restricted Mode STARTUP RESTRICT By starting Instance in restricted mode it will not allow all users to access and only users with restriction privilege will be allowed to access. This will be done time of make some data changes so that no users should be allowed to access data on time of changes happening 6. What is the difference between recovery and restoring of the oracle database? Here is a scenario to understand Restore & Recovery Sunday 10pm: Database is backed up. and is running fine. Monday 11am: Went down / crashed due to some reason. To bring up the database, we have 2 options: Simple Restore: copying files from backup taken sunday night and open the database. Here, we loose all the changes that are done since sunday night. Restore and Recovery: Copying files from backup taken sunday night and applying all the archivelog and redo log files to bring up the database to the point of failure. Here you dont loose the changes done until monday 11 am. Restore: copying files from the backup overwriting the existing database files Recovery: applying the changes to the database till point of failure. these changes are recorded in online redolog and archivelog (which are the backups of redolog) files. 7. What are the different tools available for hot backups in Oracle? Is it preferable to take it manually all the time or it depends on the size of the database? A hot backup can be done by either RMAN,User Managed Backups by puting tablespace in backup mode my OEM which does the same as the user managed backup.But the Backup depends upon the size of the database you are using . if the database size in TB the RMAN backup will take more than 10 hours to complete and if the database is critical you can' wait for long to go for so long in this case their are special backup techniques which are given by vendors like TIVOLI and Netbackup they provide BC Vol backup called Business content Volumn Sync which copies a snapshot of the primary data to another place and backsup the database from one SAN to another with in 15 min for 2 TB of database and is the preferable method for big companies. 8. What do you mean by Oracle MEDIA RECOVERY? When physical disk fail, physical database file corrupt then media recovery required 9. What is the disk migration? what is the steps involved in oracle disk migration? Disk migration is noting but, migration of data from one OS dependent database to another Dependent database. The steps involved in this are first go to your target database and export all your data into flat files next in the destination database during the installation of the database, it asks for data source ,instead of giving the data of the oracle provided , give the path of the flat file you exported previously . 10. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode in Oracle? Database in archivelog mode chance to take hot backup and no data loss in recovery. you can use RMAN for backup and recovery .disadvantage is poor ferformance, and more chance to crash disc.

Oracle Database Backup & Recovery Interview Questions 11. Why more redos are generated when the oracle database is in begin backup mode? During begin backup mode datafile headers get freezed and as a result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo generated and more log switch and in turn more archive logs. 12. What is the use of FULL option in EXP command? A flag to indicate whether full database export should be performed. 13. What is the use of OWNER option in EXP command? List of table accounts should be exported. 14. What is the use of TABLES option in EXP command? List of tables should be exported. 15. What is the use of RECORD LENGTH option in EXP command? Record length in bytes. 16. What is the use of INCTYPE option in EXP command? Type export should be performed COMPLETE, CUMULATIVE, INCREMENTAL. 17. What is the use of RECORD option in EXP command? For Incremental exports, the flag indirect whether a record will be stores data dictionary tables recording the export. 18. What is the use of PARFILE option in EXP command? Name of the parameter file to be passed for export. 19. What is the use of ANALYSE option in EXP command? A flag to indicate whether statistical information about the exported objects should be written to export dump file. 20. What is the use of CONSISTENT option in EXP command? A flag to indicate whether a read consistent version of all the exported objects should be maintained. 21. What is use of LOG (Ver 7) option in EXP command? The name of the file which log of the export will be written. 22. What is the use of FILE option in IMP command? The name of the file from which import should be performed. 23. What is the use of SHOW option in IMP command? A flag to indicate whether file content should be displayed or not. 24. What is the use of IGNORE option in IMP command? A flag to indicate whether the import should ignore errors encounter when issuing CREATE commands. 25. What is the use of GRANT option in IMP command? A flag to indicate whether grants on database objects will be imported. 26. What is the use of INDEXES option in IMP command? A flag to indicate whether import should import index on tables or not. 27. What is the use of ROWS option in IMP command? A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed. 28. What are the different methods of backing up oracle database? Logical Backups Cold Backups Hot Backups (Archive log) 29. What is a logical backup? Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup. 30. What is cold backup? What are the elements of it? Cold backup is taking backup of all physical files after normal shutdown of database. We need to take. All Data files. All Control files. All on-line redo log files. The init.ora file (Optional) 31. What are the different kinds of export backups? Full backup - Complete database Incremental backup - Only affected tables from last incremental date/full backup date. Cumulative backup - Only affected table from the last cumulative date/full backup date. 32. What is hot backup and how it can be taken? Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. On control file. 33. What is the use of FILE option in EXP command? To give the export file name. 34. What is the use of COMPRESS option in EXP command? Flag to indicate whether export should compress fragmented segments into single extents. 35. What is the use of GRANT option in EXP command? A flag to indicate whether grants on database objects will be exported or not. Value is 'Y' or 'N'. 36. What is the use of INDEXES option in EXP command? A flag to indicate whether indexes on tables will be exported. 37. What is the use of ROWS option in EXP command? Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created. 38. What is the use of CONSTRAINTS option in EXP command? A flag to indicate whether constraints on table need to be exported. Oracle Database Backup and Recovery Questions and Answers Pdf Download Read the full article

[kdeven57-blog]

This SQL tutorial will help you learn SQL basics, so you can become a successful SQL developer. You will find out what are the SQL commands, syntax, data types, operators, creation & dropping of tables, inserting and selecting query. Through this tutorial you will learn SQL for working with a relational database. Learn SQL from Intellipaat SQL training and fast-track your career.

SQL Tutorial for Beginners

This SQL Tutorial for Beginners is a complete package for how to learn SQL online. In this SQL tutorial , you will learn SQL programming to get a clear idea of what Structured Query Language is and how you deploy SQL to work with a relational database system. So, a structured query language is a language that is used to operate the relational databases. Some of the major ways in which SQL is used in conjunction with a relational database is for the purposes of storing, retrieving and manipulating of data stored in a relational database.

Check out Intellipaat’s blog to get a fair understanding of SQL Optimization Techniques!

Here we have the list of topics if you want to jump right into a specific one:

What is SQL?

Why is SQL programming so widely used?

Features of SQL

Applications of SQL

Why should you learn SQL Programming?

Watch this SQL Tutorial for Beginners video:

https://www.youtube.com/watch?v=JTDK6r1GuUU&feature=youtu.be

What is SQL?

To learn SQL Online, first let us compare SQL with NoSQL in the below table:

Comparison criteriaSQLNoSQL

Type of databaseRelationalNon-relational

How is data storedStructured data in tablesUnstructured data in JSON files

Suitability for OLTP systemsExcellentAverage

Database compliance

ACID properties

CAP Theorem

The language to communicate with the relational database is the SQL or Structured Query Language. SQL programming helps to operate the relational databases and derive information from it.

Some of the operations that the SQL does include creation of database, fetching, modifying, updating and deleting the rows along with storing, manipulating and retrieving data within the relational database. SQL programming is an ANSI standard language but there are a lot of versions of SQL in usage as well.

Why is SQL programming so widely used?

Structured Query Language or SQL programming is used so extensively for the following reasons.

SQL lets you access any data within the relational database

You can describe the data in the database using SQL

Using SQL you can manipulate the data with the relational database

SQL can embed within other languages through SQL modules & libraries

SQL lets you easily create and drop databases and tables

SQL allows you to create views, functions and stored procedures in databases

Using SQL you can set permissions on procedures, tables and views.

Wish to crack SQL job interviews? Intellipaat’s Top SQL Interview Questions are meant only for you!

Get interview-ready with this SQL Interview Questions: https://intellipaat.com/blog/interview-question/sql-interview-questions/

Features of SQL

Here in this section of the SQL tutorial for beginners, we list some of the top features of SQL that make it so ubiquitous when it comes to managing relational databases.

SQL is very simple and easy to learn language

SQL is versatile as it works with database systems from Oracle, IBM, Microsoft, etc.

SQL is an ANSI and ISO standard language for database creation and manipulation

SQL has a well-defined structure as it uses long established standards

SQL is very fast in retrieving large amounts of data very efficiently

SQL lets you manage databases without knowing lot of coding.

Applications of SQL

Here in this section of the SQL tutorial we will learn SQL applications that make it so important in a data-driven world where managing huge databases is the norm of the day.

SQL is used as a Data Definition Language (DDL) meaning you can independently create a database, define its structure, use it and then discard it when you are done with it

SQL is also used as a Data Manipulation Language (DML) which means you can use it for maintaining an already existing database. SQL is a powerful language for entering data, modifying data and extracting data with regard to a database

SQL is also deployed as a Data Control Language (DCL) which specifies how you can protect your database against corruption and misuse.

SQL is extensively used as a Client/Server language to connect the front-end with the back-end thus supporting the client/server architecture

SQL can also be used in the three-tier architecture of a client, an application server and a database that defines the Internet architecture.

Still have queries? Come to Intellipaat’s SQL Community, clarify all your doubts, and excel in your career!

Why should you learn SQL?

Today regardless of the relational databases systems by major corporations like Oracle, IBM, Microsoft and others, the one thing that is common to them is the Structured Query Language or SQL.

Originally published at www.intellipaat.com on August 12, 2019

[fql-us]

Fauna Query Language for SQL users | Fauna Documentation

Fauna Query Language for SQL users This section describes a number of common structured query language (SQL) queries and their Fauna Query Language (FQL) equivalents. While it is almost impossible to provide an exhaustive comparison of every variation of all SQL commands, we provide a very basic comparison of the most used DDL (Data Definition Language) and DML (Data Manipulation Language) queries. As you gain more experience with the Fauna Query language, the ease and power of its syntax should become evident. Complex queries that are difficult, or even impossible, in SQL can be composed very easily in FQL. One very important difference between the two is that FQL is not a declarative language as SQL. Hence the actual path of execution needs to be provided for each query — FQL requires the developer to specify an index in most queries. Conceptual equivalents Relational FaunaDB Schema Database Table Class Row Instance Index/Materialized Views Index In these examples below, we use two tables, dept (departments) and emp (Employees) to compare basic operations in both query languages. These tables have been extensively used in Oracle documentation to explain various SQL basics. SQL> DESC emp Name Null? Type ----------------------------------------- -------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> DESC dept Name Null? Type ----------------------------------------- -------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) ZIP NUMBER Data definition language CREATE DATABASE In some relational databases, like MySQL, a database can be created with: CREATE DATABASE employees; CREATE DATABASE Fauna is a multi-tenant database and databases can be created like a nested tree. CreateDatabase({name: "employees"}) CREATE TABLE CREATE TABLE dept( deptno NUMBER(2,0), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT pk_dept PRIMARY KEY (deptno) ) CREATE CLASS CreateClass({name: "dept"}); FaunaDB doesn’t enforce the structure of a class at the time of creation. However, if we know that every instance of this class should have a deptno field, we can create a unique index on the deptno field which emulates a relational database’s primary key. CreateIndex({ name: "dept_by_deptno", source: Class("dept"), terms: [{ field: [ "data", "deptno" ] }], unique: true }) ALTER TABLE ADD COLUMN ALTER TABLE dept ADD (zip NUMBER); No direct correlation As instances do not have a predefined schema, there is no straightforward equivalent to adding a term (equivalent to a column) to all instances without any values. The Fauna equivalent would be to run Update on the instance. Update( Ref(Class("dept"), "224507299921658368"), { data: { zip: 10001 } } ) TRUNCATE TABLE In SQL, truncate removes all records, but preserves the structure of the table. TRUNCATE TABLE dept; DELETE INSTANCES In FQL, the equivalent would be to delete all records from the table. Map( Paginate( Match(Index("all_depts")) ), Lambda("X", Delete(Var("X"))) ) The all_depts index is a class index that indexes the entire class. CreateIndex({ name: "all_depts", source: Class("dept") }) DROP TABLE DROP TABLE dept; DELETE CLASSES and INDEXES The Delete command can be used to remove tables and indexes. Unlike in SQL, dropping a table doesn’t remove the underlying indexes automatically. The indexes need to be removed manually. Delete a Class Delete(Class("dept")) Delete an Index Delete(Index("all_depts")) Data manipulation language INSERT RECORD INSERT INTO dept (deptno, dname, loc) VALUES (10, "ACCOUNTING", "NEW YORK"); CREATE INSTANCE Create( Class("dept"), { data: { "deptno": 10, "dname": "ACCOUNTING", "loc": "NEW YORK" } } ) UPDATE UPDATE dept SET loc = "AUSTIN" WHERE deptno = 10; UPDATE Update( Select("ref", Get( Match(Index("dept_by_deptno"), 10) ) ), { data: { loc: "AUSTIN" } } ) Running the Replace command on an entire document is also a form of Update. This is similar to a Delete followed by an Insert. Replace( Ref(Class("dept"), "224572974137606656"), { data: { "deptno": 10, "dname": "ACCOUNTING", "loc": "AUSTIN" } } ) DELETE DELETE FROM dept WHERE deptno = 10; DELETE You can use the reference as the key to delete a specific record. Delete( Ref(Class("dept"), "224532222499095041") ) Alternatively, you can delete a record based on a specific index column. Delete( Select( "ref", Get( Match(Index("dept_by_deptno"), 10) ) ) ) Query SELECT: ALL ROWS SELECT * FROM dept; GET ALL INSTANCES Just like in relational databases, selecting all instances from a class results in a full scan. In SQL, the server automatically selects the appropriate indexes based on the specified columns. In FaunaDB, indexes must be specified explicitly. You need a class index to run a full scan: CreateIndex({ name: "all_depts", source: Class("dept") }) Once the index is in place, run the query below. Map( Paginate( Match(Index("all_depts")) ), Lambda("X", Get(Var("X"))) ) SELECT: Based on a single Parameter SELECT * FROM dept WHERE deptno = 10; GET: Based on a single Parameter We can use the unique index we created earlier to enforce the primary key. Map( Paginate( Match(Index("dept_by_deptno"), 10) ), Lambda("X", Get(Var("X"))) ) SELECT: Based on a single Parameter with a NOT SELECT * FROM dept WHERE deptno != 10; GET: Based on a single Parameter with a NOT Unlike SQL, we create this list as a difference between two indexes, the class index and the unique index on the deptno. Map( Paginate( Difference( Match(Index("all_depts")), Match(Index("dept_by_deptno"), 10) ) ), Lambda("x", Get(Var("x"))) ) SELECT: Based on a condition SELECT * FROM emp WHERE sal > 2000 GET: Based on a condition In order to accomplish this, we need an index on the sal term along with the Refs that point to the location of each instance. CreateIndex({ name: "emp_by_sal", source: Class("emp"), values: [ {field: ["data", "sal"]}, {field: ["ref"]} ], }) After the index has built, we can get the results with: Map( Paginate( Match(Index("emp_by_sal")), { after: 2000 } ), Lambda("x", Get(Select(1, Var("x")))) ) Observe the Lambda function. The Select command gets the Ref fields, and then passes them to the Get command. Alternatively, we could have used Lambda(["sal", "ref"], Get(Var("ref"))) as we know the index returns two different values. SELECT: GROUP BY Query to select the maximum salary by department SELECT MAX(sal), deptno FROM emp GROUP BY deptno; GET: Grouped instances FQL can accomplish such queries using two indexes, the first on deptno and the second on deptno and salary. CreateIndex({ name: "emp_by_deptno", source: Class("emp"), values: [{ field: ["data","deptno"] }] }) CreateIndex({ name: "deptno_by_sal", source: Class("emp"), terms: [{ field: ["data","deptno"] }], values: [{ field: ["data","sal"] }] }) The second index deptno_by_sal stores the values sorted by sal within each deptno group. Since Get() returns the first element that matches the index, it returns the maximum value of sal. Map( Paginate( Distinct( Match(Index("emp_by_deptno")) ) ), gid => Get( Match(Index("deptno_by_sal"), Gid) ) ) EQUI-JOIN two tables SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = "SALES"; GET instances joined by two classes We need two indexes to accomplish this join in FQL. When we JOIN two classes, the value of one index is joined to the term of another index. Index #1 (Class: Dept, Term: dname, Value: deptno) CreateIndex({ name: "dept_by_name", source: Class("dept"), terms: [{ field: ["data", "dname"] }], values: [{ field: ["data","deptno"] }] }) Index #2 (Class: emp, Term: deptno) CreateIndex({ name: "emp_by_deptno", source: Class("emp"), terms: [{ field: ["data","deptno"] }] }) Query Map( Paginate( Join( Match(Index("dept_by_name"), "SALES"), Index("emp_by_deptno") ) ), Lambda("X", Get(Var("X"))) ) Stay tuned for another set of examples in the near future. If you are looking for a specific example, feel free to email me at deba@fauna.com.

[learninglcom]

Complete Oracle SQL Masterclass: Go from zero to Hero in SQL

Complete Oracle SQL Masterclass: Go from zero to Hero in SQL

Learn SQL by example and take your career to the next level What you'll learn : Write basic and advanced SQL Queries for any Relational Database Management System Use SQL in Python 3 programs Add SQL to your resume Prepare for interviews involving SQL Work with SQL and any Relational Database Confidently Requirements : A computer with Microsoft Windows and Internet Connection Willingness and enthusiasm to learn, drive to take your career to the next level Description : Become an expert in SQL and learn one of employer's most requested skills of 21st Century! SQL is used in many job roles including Database Administration, Data Science, Performance Engineering, and Web Development. A person with expert level knowledge of SQL can earn minimum $100000 (That's five zeroes after 5) per year. This is the most comprehensive, yet straight-forward course for the Structured Query Language (SQL) on Udemy! Whether you have never used databases before, already know basics of SQL, or want to learn about the advanced features of SQL, this course is for you! In this course I will teach you SQL with Oracle.  With over 100 lectures and more than 19 hours of video this comprehensive course leaves no stone unturned to make you an expert in the SQL. This course will teach you SQL in a very practical manner, with every lecture comes a full SQL programming video and a corresponding PDF that has SQL code! Learn in whatever manner is the best for you! We will start by helping you get Oracle XE and SQL Developer installed on your Windows computer. We cover a wide variety of topics, including: Basics of RDBMS and SQL Getting Started with SQL Installation of Oracle and SQL Developer The WHERE Clause Single ROW Functions Group Functions Joining data from multiple tables DML Statements and Transactions DDLs, Tables, and Data Dictionary Constraints Views Synonyms and Indexes Set Operations Sequences Schemas, Roles, and User Access Control Advanced Topics in Oracle SQL Python 3 and SQL Additional Resources and Tools MySQL RDBMS and SQL for Data Science Bonus Section You will get lifetime access to over 100 lectures plus corresponding SQL code PDFs for the lectures!  This course comes with a 30 day money back guarantee! If you are not satisfied in any way, you'll get your money back. So what are you waiting for? Learn SQL in a way that will advance your career and increase your knowledge, all in a fun and practical way! Who this course is for : Programmers and Developers Testers and QAs Data Scientists and Engineers Database Administrators Analysts Managers and Sales People People preparing for Oracle and other database related certifications Anyone who genuinely wants to learn SQL Download Torrent Read the full article

[computingpostcom]

MariaDB is one of the most used open source relational databases. It is developed by MySQL developers but made to be totally free unlike MySQL. The development of MariaDB focuses on stability and performance. The other reasons why MariaDB is preferred is it being robust and scalable, new storage engines and comes with various tools and plugins that make it widely applicable. It is the default database in most Linux distributions. In this guide, we are going to look at how to install MariaDB 10.6 on Ubuntu 20.04 and Ubuntu 18.04 from MariaDB APT repository. Features of MariaDB 10.6 MariaDB 10.6 is the current stable version of MariaDB and comes with a number of new features as discussed below: Ignored Indexes – These are indexes that are visible and maintained but not used by the optimizer sys schema supported- This is a “system” database containing views and procedures for investigating performance problems. SKIP LOCKED – Locked tables are skipped from being updated or selected. JSON_TABLE() – can create a JSON table that can be used as a subquery from a JSON document. OFFSET…FETCH…[WITH TIES] – WITH TIES is an optional clause that adds extra functionality. Example as used Oracle compatibility – There are ongoing works in making MariaDB compatible with OracleDB with some Oracle Syntaxes and functions already added. Some of the improvements on MariaDB 10.6 from its predecessors include: Atomic DDL – CREATE, ALTER, DROP and RENAME are atomic and crash safe. If MariaDB server crashes while processing any of these operations, the change will either e done completely or not done at all. InnoDB improvements – First insert to an empty table is faster. Also writes to temporary tables are avoided.Faster implicit and explicit temporary tables. Improvements in Galera. Ability to enable encrypted connections between two nodes without downtime. Also added flags to specify if galera controversial compatible features should be enabled. Clean up to remove unsupported features such as TukoDB Engine, Cassandra Engine, some InnoDB variables and some innodb_checksum_algorithm. Step 1: System upgrade As usual, we begin by running system upgrades to ensure that we are performing our installations on the latest packages. After updates, reboot your server. sudo apt update sudo apt upgrade -y sudo reboot Step 2: Install Required Packages Next, we are going to install software-properties-common package sudo apt install software-properties-common -y Step 3: Import MariaDB GPG Key and Add MariaDB APT Repository With the below commands respectively, we are going to add MariaDB signing key and add MariaDB APT repository: curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup sudo bash mariadb_repo_setup --mariadb-server-version=10.6 You’ll get an output with contents like the ones shown below: [info] Checking for script prerequisites. [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list [info] Adding trusted package signing keys... [info] Running apt-get update... [info] Done adding trusted package signing keys Step 4: Install MariaDB 10.6 on Ubuntu 20.04|18.04 Once MariaDB key and APT repositories are added, update the packages and proceed to install MariaDB 10.6 on Ubuntu 20.04|18.04: sudo apt update sudo apt install mariadb-server mariadb-client Step 5: Secure MariaDB Installation Once MariaDB is installed, run the below MySQL script to secure MariaDB $ sudo mariadb-secure-installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody

can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n] Y Enabled successfully! Reloading privilege tables.. ... Success! You already have your root account protected, so you can safely answer 'n'. Change the root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! Step 6: Confirm Mariadb Status MariaDB server should be automatically started. Check status as below: $ systemctl status mariadb ● mariadb.service - MariaDB 10.6.4 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Tue 2021-10-19 11:37:25 UTC; 9s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 3023 (mariadbd) Status: "Taking your SQL requests now..." Tasks: 14 (limit: 4703) CGroup: /system.slice/mariadb.service └─3023 /usr/sbin/mariadbd Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: performance_schema Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: sys Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: Phase 6/7: Checking and upgrading tables Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: Processing databases Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: information_schema Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: performance_schema Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: sys Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: sys.sys_config OK Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: Phase 7/7: Running 'FLUSH PRIVILEGES' Oct 19 11:37:29 ubuntu /etc/mysql/debian-start[3046]: OK root@ubuntu:~# Step 7: Enable MariaDB to Start on Server Reboot Run the below command to enable MariaDB to automatically start when server is rebooted sudo systemctl enable mariadb Step 8: Check MariaDB Version To confirm the installed version, we need to first login to MySQL as below. $ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 10.6.3-MariaDB-1:10.6.3+maria~focal mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Now run the following command to check MariaDB version MariaDB [(none)]> SELECT VERSION(); +--------------------------------------+

| VERSION() | +--------------------------------------+ | 10.6.4-MariaDB-1:10.6.4+maria~bionic | +--------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> QUIT Bye Step 9: MariaDB Basic Usage Next, we are going to see some of the MariaDB Dababase basic usage such creating a database, users and so on. MariaDB Create Database As seen above, MariaDB uses MySQL syntax. To create a database, you first need to login to mariadb as shown above then run the below command to create a database. #Create a new database MariaDB [(none)]> CREATE DATABASE db1; Query OK, 1 row affected (0.000 sec) #If the database with the same exists CREATE DATABASE db1; ERROR 1007 (HY000): Can't create database 'db1'; database exists #Create a database if already exits MariaDB [(none)]> CREATE OR REPLACE DATABASE db1; Query OK, 2 rows affected (0.009 sec) #First check if a database exists MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS db1; Query OK, 1 row affected, 1 warning (0.000 sec) # Check Databases MariaDB MariaDB [(none)]> SHOW DATABASES; MariaDB add user and Grant Privileges To create a user and grant privileges; #Create user mariadb MariaDB [(none)]> CREATE USER 'db1user'@'localhost' IDENTIFIED BY 'mypassword'; #Grant privileges to a specific database MariaDB [(none)]> GRANT ALL PRIVILEGES ON db1.* TO 'db1user'@'localhost'; #Remember to refresh the privileges MariaDB [(none)]> FLUSH privileges; #To check user grants in MariaDB MariaDB [(none)]> SHOW GRANTS FOR 'db1user'@'localhost'; Create a Table and Add Data MariaDB Once you have created a database, you can create table and add data into it MariaDB [(none)]> USE db1; MariaDB [(none)]> CREATE TABLE employees (id INT, name VARCHAR(20), email VARCHAR(20)); MariaDB [(none)]> INSERT INTO employees (id,name,email) VALUES(01,"User1","user1@example.com"); MariaDB Clean up If you want to completely clean MariaDB installation, you can remove and do a re-install. sudo apt purge mariadb-server sudo rm -rf /var/lib/mysql/ This has been a guide n how to install MariaDB 10.6 on Ubuntu 20.04 and Ubuntu 18.04. It is a short and easy installation and I hope that this guide as been helpful.

[lovehappystudentenemyblog-blog]

Get Trained on IBM DB2 DBA Online by 15+ Years of experienced Professionals - IQ Online Training

IBM DB2 DBA:

DB2 DBA is RDBMS (Relational Database Management System) product from IBM which will provide the number of different OS (Operating System) platform. As according to the IBM, it leads in term of the Database market share and their Performances.

Although those there products are offered for a Personal Computer Operating Systems and UNIX-Based Systems, as the DB2 DBA trails the Oracle's Database product in the Microsoft Access and the UNIX-based system in Windows systems.

                     Features:

·         As the entire user has the authority to create Table in Database or Table Spaces as an implicitly created by Database.

·         If a Table Space implicitly is created, and you not specify the IN clause in CREATE TABLE statement, DB2 implicitly create database to which a table space will be assigned.

·         The DB2 implicitly use existing implicitly Created Database for the Table either uses an Created Database.

·         DB2 DBA Database will be a set of structure which includes collection of the Tables, and the Table Spaces and the associated index which it reside. Define the Database using an CREATE DATABASE statement.

                  Short Notes on DB2 DBA:

·         The Excessive number of Table and Table Spaces in database which can decrease the Manageability and the Performance issues. Reduce the number of Tables and Table Spaces in a Database, improve performance, minimize maintenance, increase concurrency and decrease the log volume concurrency.

·         Also grant the authorization for accessing all Data as it is Single Unit. Which Authorized to Access the Data stored in different Databases?

·         By Collecting the Data in single Database allow starting or stopping access as all the data in one operation.

·         As an single Database contain the data which is associated in one application or with a group of related applications.

          Highlights of IQ Online Training:

·         IQ ONLINE TRAINING is a premier online training portal with an decade of rich experience in the web based online training and as it has established one of dominant online training portal in the world.

·         IQ ONLINE TRAINING offers you LIVE FREE DEMO, Live Webinar, support interview question and answer.

·         IQ ONLINE TRAINING with a 12+ years of experience, Certified Trainers, and Provides Job assistance training.

To register for our IT training courses in USA

Please call us on +1 904-304-2519 or

Send an email to info@iqtrainings.com

Website: http://www.iqonlinetraining.com/

         http://www.iqonlinetraining.com/ibm-db2-online-training/

           Applications:

·         One of important feature of supporting Big Data DBA DB2 environment is RPN (Relative Page Numbering) for Range-Partitioned Table Spaces.

·         Either use an Existing Range-Partitioned table space which can changed to an RPN PAGE NUM RELATIVE with ALTER TABLESPACE, which follow by Online REORG of entire spaces or create a New RPN Range-Partitioned Table Spaces.

·         RPN table spaces have the ability to create large number of partition in sizes. The maximum Partition Size is nearly about 1TB.

·         As RPN Table Spaces allows storing LOT of data, and reaching the capacity of existing Table Space. And Of course, as it require a expanded RID, which increase from 5 Bytes to 7 Bytes.

·         It impact the DDL for mapping Table Online REORG Utilities. RPN Table Spaces improve the availability, but size is not the issue. It also specified the RPN Table Space for DSSIZE at partition level.

[tsworman]

Oracle Views and ORA-04027

Achievement unlocked! A new Oracle Error message appears: ORA-04027: self-deadlock during automatic validation for object PB

Wait what? Let me google that quick... Not helpful and amusingly none of the results when I did it were from Oracle, despite it being their error number.

How did we get here? Two developers running different code with shared dependencies individually and working fine. One day (after 15 years) they ran it at the same time by chance and noticed it just sat their doing nothing. If one developer killed their job, the others finished. If they wait eventually one times out with the error message above. ORA-04027.

Let’s show how this was triggered

Table tA - A dimension table.

Table tB1 - A second dimension table.

Table tB2 - A second table related to the second dimension.

View vB - A View that is a union of tB1 and tB2.

Table tF - A fact table.

Package pA - Inserts data into tables each insert calls a function (fA) from pB.

Package pB - A package of get functions that do simple selects from dimension tables. One function is fA and selects from table tA. One function is fB and selects from view vB.

package pC - Saves and restores indexes on table tB1 and inserts new rows to it.

Package pI - Procedures to save indexes (pS) and restore indexes (pR) on tables. pS - Get DDL for index, write to table, and issue a DML to drop the index from a table. pR - Gets indexes from tables and creates them on the table.

OKAY! So with the stage set, let’s get started!

Developer 1 call his procedure from pA to load table F. It takes about 10 minutes to truncate the table and perform an insert. This insert is calling fA from pB for every row.

Developer 2 starts his package pC. It saves the indexes off of table tB1 in prep to load it. The process hangs right there and Developer 1 and Developer 2 are left stranded.

Developer 2 wasn’t doing anything to the tables that were used by developer 1. They were separate tables. Developer 2′s code didn’t alter the underlying tables which would have caused the package to go invalid. All Developer 2 did was drop an index, try to add data and then restore the index.

As I said before, Developer 1 killing his job causes Developer 2′s process to finish. Developer 2 killing his job causes Developer 1′s process to finish.

Going invalid - One of the developers noticed that when he ran his code at the same time as the other developer, that vB would go invalid and so would pB. This didn’t occur when they ran it one at a time.

Odd. We went through the code and everything looked okay at first glance. No DDL’s changing the tables. Nothing that would make me think any object would be invalidated. We wondered if unlocking stats, gathering them and re-locking would cause the invalidation? No it does not. 

We stepped through the section by section then line by line and narrowed it down to the drop index statement from package pI. When the index was dropped it was invalidating the view and the package that depended on the view. That is odd to me. The view isn’t really dependent on the index unless it’s an IoT table or maybe a clob. If a view is invalidated on next run it would re-compile.

To the Oracle docs on Drop Index.. LINK

“Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.”

Wait, what? It still doesn’t make sense to me that a view is invalidated on dropping an index. That might explain our problem more.

The dead-lock - At Last! The Oracle Speaks!

After some time leaving both developers code hanging in wait state we’d produce the error:  ORA-04027: self-deadlock during automatic validation for object PB

When Developer 2 dropped the index on tB1 it invalidated vB which was used by package pB. Package pB was also invalidated.

Developer 1 who was running his insert using a different function dependent on a valid table from pB paused. Developer 1′s package tried to automatically recompile the package.

Developer 2′s code who was also dependent on pB (just in a totally different procedure than the one running) would also try to recompile the package. No big deal normally, one of them would win and the other would resume. So for 15 years we never encountered this problem.

It was only when this one function ran that happened to be dependent on a view vB instead of directly on a table like tB, that we got a self-deadlock. 

Remember that Developer 2′s package would see that pB was invalid and would try to recompile. Our assumption (based on observation of wait state/pin lock timing) is that when it fails, it then tries to recompile objects it depends on and moves to working on vB. 

Developer 1′s package also noticed that pB was invalidated during it’s run and would try to recompile. Usually this would result in a busy resource/pin lock timeout sort of deal. In this case since Developer 2 switched to validating vB, Developer 1′s application would then try to validated pB and they both get stuck. So it’s not really a true self-deadlock. The deadlock is caused caused during automatic re-validation of the view vB which is being done to try and validate package pB. It’s really the other developer trying to validate pB at the same time that stops the first process from getting a lock on the package again. 

The timing is important since whoever’s process tried to revalidate first is going to be the one to time out first and get the ORA-04027 error. The other user’s process will just eventually complete when it becomes the sole user trying to compile package pB.

Ugh.

If you eliminate the view here you don’t see this problem as tables do not go invalid on dropping an index. I still don’t understand why every view (even simple ones) would invalidate on dropping an index, except for lazy programmers. It’s good that Oracle have at least explained it away in the documentation. It’s no longer a bug if we want it fixed, it’s a feature request!