#get ddl 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/

[asquaretechnologies]

Business Analytics Course In Kochi

Who is a Business Analyst?

A Business Analyst is responsible for acquiring and documenting business requirements, analyzing data and processes, and enabling communication among stakeholders to ensure that projects and initiatives fulfill business objectives and function successfully.

Opportunity for a business analyst

Business analysts have good job possibilities in a variety of industries, where they may use their abilities to promote data-driven decision-making, process improvement, and organizational strategic success.

Industries looking for business analysts

Finance and Banking

Healthcare

Information Technology

Retail

Manufacturing

Consulting

Telecommunications

Government

Energy and Utilities

Insurance

Transportation and Logistics

E-commerce

Pharmaceuticals

Hospitality

Education

Media and Entertainment

Aerospace and Defense

Automotive

Real Estate

Non-Profit

How to Become a Business Analyst

Educational Background: Bachelor’s Degree

Join a job-oriented Business Analytics Course

Asquare Job-oriented Business Analytics Course

Asquare Technologies course structure helps all the candidates to understand Business Analytics in an easy manner with lots of practical expertise a candidate will gain at the end of our Course.

The course module starts with the below topics,

MICROSOFT EXCEL FUNDAMENTALS

We are starting the course with Fundamental Excel Concepts for a 1-week program to make sure that all the candidates get hands-on  Excel basics, to begin with the analytics skills.

Microsoft Excel is a versatile and powerful tool that offers a wide range of benefits for data management, analysis, and decision-making, making it essential software for professionals in various fields.

ORACLE SQL BASICS

Once the candidates are fine with Excel Fundamentals, we will be starting with 2 weeks of Oracle SQL Fundamentals with DBMS (Database Management system) concepts to understand all the DDL, DML, and TCL Commands a developer should know.

This module will give a candidate a basic idea about a database, fundamentals on why SQL Language is important and when it should be used, and so on. If a candidate is really interested in learning more about Oracle SQL, we will be sharing some more references and finally, a small assignment will be completed by each candidate at the end of the SQL Course.

BUSINESS INTELLIGENCE WITH MICROSOFT POWER BI

Now starts the real game of Analytics with good visualization skills each candidate can showcase with a knowledge-sharing session on Business Intelligence Concepts / Data warehouse concepts. Every person willing to switch their career to Analytics should be aware of all the Warehouse concepts before starting with Microsoft Power BI or Tableau Data visualization sessions. Once the DWH Concepts are clear we will start with the basics of Power BI sessions with Excel as the data source and go ahead with more advanced Excel data, Different types of visuals, and covering all the other concepts a Power BI Developer should be clear about. DAX knowledge is important for a developer to create new measures based on the business requirements. Going forward use AI visuals, Bookmarks, slicers, security, Data refresh Dashboard creation, and so on. The Power BI Training will give a candidate a thorough hands-on experience, and interactive sessions from our experts. Once the training is over all the candidates are supposed to submit an individual project presentation on the respective business domain each candidate is interested in.

SALESFORCE TABLEAU 

Once the candidate is done with the Power BI Project, we are stepping into another interesting Visualization tool Tableau which has more visualization capabilities and can handle more volume of data compared to Power BI. It is better to have more than one BI Tools knowledge for a job seeker to get a job in a quicker manner either as a Power BI developer or as a Tableau Developer. Once a candidate is good in Power BI can easily adapt to Tableau. The way a candidate completes a Power BI project a similar approach is followed at the end Tableau course to do an individual project in Tableau and a review session is conducted by our experts with a rating to evaluate each candidate.

Why Asquare Technologies?

✅ISO 9001:2015 Certified ✅Affiliated To SKILL INDIA (NSDC) ✅Training By Experienced Professionals ✅Live Interactive Sessions ✅Mentorship for soft skills improvement ✅100% Placement Assistance ✅Project work and Review from Experts ✅Mock Interview program from Industry Experts ✅Internship Program for Live Project Experience

Certifications : 1. NSDC Certificate after Successful completion of Training 2. Completion Certificate from Asquare Technologies. 3. Internship Completion Certificate.

[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…

[ocptechnology]

How to Get DB Link DDL in oracle

How to Get DB Link DDL in oracle #oracle #oracledba #oracledatabase

how to get ddl for db_link in oracle with password, export db_link with password, drop db link, dbms_metadata.get_ddl dblink, get public db link ddl in oracle, drop db link in oracle. Viewing Information About Database Links Oracle has three data dictionary views listed below, which help us to find the information about DB links. View NamePurposeDBA_DB_LINKSShow the list of all DB Links from…

[tenologin]

What are SQL and PL/SQL, their uses, benefits, and drawbacks?

An Introduction to PL/SQL

Procedural Language Extensions to the Structured Query Language are known as PL/SQL. In relational database management systems, SQL is a popular language for data querying and updating (RDBMS).

 PL/SQL enhances the SQL language with numerous procedural constructs to address some of SQL's drawbacks. A more comprehensive programming language option is also provided by PL/SQL for creating mission-critical applications on Oracle databases.

A well-organised and legible programming language is PL/SQL. Its constructions unambiguously convey the code's intention. PL/SQL is also an easy language to learn.

A standardised and portable language for creating Oracle databases is PL/SQL. You can easily port a software that is written to run on an Oracle database to another Oracle database that is compatible.

What is SQL?

Relational databases can be created, maintained, and retrieved using the robust non-procedural database language known as Structured Query Language (SQL) (a type of database that provides and stores data that is related to each other). It was developed by IBM in the 1970s and enables users to communicate with different database management systems according to their accessibility.

SQL commands are classified into six types. These are their names:

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Control Language (DCL)

Transaction Control Language (TCL)

Data Query Language (DDQL)

Advantages sql 

There are several benefits to structured query language, some of which are described below:

Without having to create a substantial amount of code, managing database systems is fairly easy using ordinary SQL.

SQL databases based on well-established databases. NoSQL databases don't adhere to any rules.

Programs that run on computers, servers, laptops, and even some mobile phones can use SQL.

Using this domain language, you can instantly get complex question answers from databases.

Using the SQL language, users can design various views of database architecture and databases for various users.

Disadvantages of sql

SQL requires training on a large database.

An expert user or programmer can handle SQL.

It takes up some space, requiring an extra memory location for each record.

It is a platform-specific language.

It is a command-based programming language.

What is pl/sql ? 

Oracle The PL/SQL extension for SQL combines the processing power of a procedural language with the data manipulation capabilities of SQL to produce extremely strong SQL queries. By enhancing the security, portability, and robustness of the database, PL/SQL makes sure that SQL commands are processed efficiently.

Using PL/SQL, you may instruct the compiler on both "what to do" and "how to do it" using SQL and its procedural approach.

By using loops, conditions, and object-oriented ideas, it allows programmers more control than other database languages. PL/SQL stands for "Procedural Language Extensions to SQL," in its full form.

What is PL/SQL Developer?

Programmers can combine procedural statements and the power of SQL with PL/SQL, a procedural language (code written in the form of a set of instructions). PL/SQL stands for Procedural Language Extensions to SQL. Triggers, functions, and procedures that increase an operation's functionality while lowering traffic are all executed simultaneously in PL/SQL.

It was developed in the 1990s by Oracle Corporation to build server pages and web applications, and it has features like abstraction and error handling.

Basic PL/SQL Commands

PL/SQL is a procedural (code written in the form of a series of instructions) language that allows programmers to combine the power of SQL with procedural statements. Procedural Language extensions to SQL are abbreviated as PL/SQL. 

In PL/SQL, all statements are executed at once, including triggers, functions, and procedures that improve the functionality of an operation while reducing traffic.

Oracle Corporation created it in the 1990s to build server pages and web applications, and it includes features such as abstraction and error handling.

Data Insertion: Using the 'insert' command in this PL/SQL command, data can be inserted into any table. This command will require the table name, column name, and column values. This statement can insert values into any base table. Table names and values are required. Also, the keyword "values" should be used.

Data Update: Using the 'update' statement, data in a table can be easily updated. It can change the value of any column in a table. It requires the table name, column name, and value as input and updates the data.

Selecting data: To retrieve specific data, it must be retrieved from a specific database table. This is made possible by Pl/SQL by the ‘select’ statement.

Exception handling: Use this PL/SQL command to detect error conditions. It intelligently handles errors and allows the user to send appropriate user-defined messages.

The Key Differences Between SQL and PL/SQL

Structural Query Language, or SQL, was created to work with relational databases. It is a declarative language that emphasises specifics. The Procedural Language/Structured Query Language (PL/SQL), on the other hand, makes use of SQL as its database. It is a programming language that is application-oriented.

While PL/SQL has variables, constraints, data types, and other features, SQL does not.

In PL/SQL, code blocks including functions, triggers, variables, control structures (for loop, while), and conditional statements (if...then...else) are written instead of the DDL and DML used in SQL to create queries and commands.

Only one action or query can be run simultaneously in SQL. However, PL/SQL allows for the simultaneous execution of multiple actions or an entire block of close statements. Thus, network traffic is decreased.

Features & Advantages of PL/SQL

Because SQL is processed in batches as opposed to as individual statements, performance is improved.

Efficacy is high.

Integration with SQL is tight.

Full Portability

The principles of object-oriented programming benefit from strict security.

Scalability and administration

Supports creating web applications

encourages the creation of server sites

Disadvantages of PL/SQL

In PL/SQL, stored procedures use a lot of memory and don't have debugging capabilities.

Any modification to the underlying database requires an equivalent modification to the presentation layer.

Because it does not totally divide the duties of back-end developers and front-end developers, it is challenging to separate HTML creation from PL/SQL development.

Conclusion

Using procedures, functions, control structures, cursors, and triggers, PL/SQL executes all of SQL's operations on massive amounts of data. It is essentially SQL with some procedural capabilities added. The necessary data is specified by SQL, but not how it can be obtained. PL/SQL is useful in this situation. we can now handle sophisticated SQL issues that were previously a pain in the neck thanks to the invention of PL/SQL. These days, data is the new oil, and data management is more crucial than ever. As a result, PL/SQL is essential for the operation of many enterprises. Knowledge of PL/SQL and SQL can be quite beneficial.

[rayhanoreilly-blog]

Oracle 1Z0-447 Certification Aspects

* Exam Title: Oracle GoldenGate 12c Implementation Essentials * Exam Code: 1Z0-447 * Exam Price: $245.00 More on exam pricing * Format: Multiple-Choice * Duration:Two hours * Number of Questions: 72 * Passing Score: 69% * Validated Against: Exam has become validated against Oracle GoldenGate 12c. * 1Z0-447 Practice Test: https://www.dbexam.com/1z0-447-oracle-goldengate-12c-implementation-essentials * 1Z0-447 sample questions: https://www.dbexam.com/sample-questions/oracle-1z0-447-certification-sample-questions-and-answers Oracle GoldenGate 12c Certified Implementation Specialist Certification Overview The Oracle GoldenGate 12c Essentials (1Zx-xxx) exam is ideal for people who use a strong foundation and expertise in selling or implementing oracle GoldenGate 12c solutions. This certification exam covers topics like: Oracle Goldengate 12c Architecture; Oracle GoldenGate 12c Parametres; Oracle Goldengate 12c Mapping and Transformation Overview and more. Up-to-date training and field experience are suggested. The Oracle GoldenGate 12c Implementation Specialist certification recognizes OPN members as OPN Certified Specialists. This certification differentiates OPN members available by giving an aggressive edge through proven expertise. This certification helps the OPN member’s partner organization entitled to the Oracle GoldenGate 12c.

* Get More Detail About Oracle 1Z0-447 Certification: https://oracle-exam-guide.blogspot.com/2019/05/how-to-score-best-in-1z0-447.html Oracle 1Z0-447 Certification Exam Topics * Oracle GoldenGate (OGG) Overview * Describe OGG functional overview and customary topologies * Describe OGG Veridata and Management Pack functionality * Describe the gap between real-time data integration replication files Manipulation Language (DML) replication * Install and Configure OGG * Download and Install OGG, and differentiate between various installers (zip, OUI, tar) * Synchronize source and target databases with the Initial Load * Prepare database for OGG CDC and view databases with OGG schema check script * Configure OGG Replication component parameter files * Configure the OGG Command Interface to generate OGG processes * Describe how you can identify and resolve issues in heterogeneous replication, and offer appropriate solutions * Configure OGG utilities * Mapping and Transformation Overview * Implement use cases for transformation functions * Implement macros * Managing and Monitoring Oracle GoldenGate * Manage OGG command information security * Implement and troubleshoot OGG Monitoring * Explain the configuration and management of the Enterprise Manager 12c plug-in * Implement and troubleshoot OGG Veridata * Architecture Overview * Describe OGG components * Create both forms of Capture systems for Oracle database * Create the three forms of Replicat processes * Explain the real difference between an Extract and Pump, and local and remote trails * Configure OGG's process recovery mechanism * Parameters * Describe and compare GLOBALS versus MANAGER parameters * Create solutions using component parameters for replication requirements * Install OGG parameters * Explain and identify parameters specific for non-Oracle databases * Configuration Options * Describe OGG configuration options (Data Definition Language (DDL), compression and encryption options) * Configure OGG event actions based on use cases * Troubleshoot conflict detection and backbone * Configure Integrated Capture, Replicat, and deployment options Sign up for Oracle 1Z0-447 Certification exam Sign up for Oracle 1Z0-447 Certification exam with Pearson VUE and buy test with all the voucher you purchase from Oracle University or which has a bank card applied during exam registration. To learn more about oracle goldengate certification webpage: check.

[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.

[vgselvam]

Flex Fresher Jobs 2023 | Fresher | Direct Link

Flex Fresher Jobs 2023 Off Campus Jobs Walkin Drive and Recruitment Eligibility, Careers, Salary, Syllabus, Exam Pattern, Selection Process: Flex Company will be seeking candidates for graduate positions Off campuses. Candidates who have successful in BE/B.Tech, ME/M.Tech, MCA, Any Degree branches are eligible to apply. Here you can find the Flex address, the date of interview, details on eligibility and interview agenda, as well as the application process information, and application procedure are provided below

Job Description For Flex Fresher Jobs 2023

Run the production environment by monitoring availability and taking a holistic view of system health

Build software and systems to manage platform infrastructure and applications

Improve reliability, quality, and time-to-market of our suite of software solutions

Measure and optimize system performance, with an eye toward pushing our capabilities forward, getting ahead of customer needs, and innovating to continually improve

Provide primary operational support and engineering for multiple projects

Skills Required For Flex Fresher Jobs 2023

Has solid software development basis.

Good problem-solving skills

Ability to quickly learn new tools and adapt to new technologies

Strong skills writing automation scripts with PowerShell, Linux Scripting (ksh/bash) and Python.

Strong skills in SQL DDL and administering applications running on relational databases such as MsSQL, MySQL, Oracle

Experience on system administration using ansible: playbooks, templates, roles, modules.

Experience developing shell scripts/code in both Windows and Unix environments

Experience in administering applications on AWS and other cloud infrastructure

Experience in administering utilities on Windows and Unix environments including printer management/libraries/java

Adopt and understand project management and coding best practices.

Experience using Jenkins & development collaboration tools like Gitlab/GitHub

Desirable experience with elastic & Kibana

Desirable experience with incident response solutions like Service-Now

Fluent in English and Excellent communication skills.

CLICK HERE TO APPLY

[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.

[oraclerider]

How to get tablespace DDL in oracle

To get the tablespace DDL (Data Definition Language) in Oracle, you can use the DBMS_METADATA package. “tablespace DDL in oracle” Get DDL for specific tablespace Step 1: To get tablespace ddl in oracle first connect to your Oracle database using a tool such as SQL*Plus or SQL Developer. SQL> sqlplus / as sysdba Step 2: Execute the following SQL statement to set the output format for the DDL…

[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…

[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.

[jasonmatthews-blog1]

It Industry

As IT industry is blooming now a days, so the demand for IT professionals is inclining day by day. Lots of people with Non -IT background also wants to step into world of IT professionals because of financial and mental satisfaction. Estimated every year these jobs are doubling and it will keep on increasing in future because in today's world we are totally technology dependent even for our day to day activities.

From our mobile phones to our vehicle everything is software, range is quite vast. According to the survey IT industry is and will be the most profitable domain to step into or start with.

Question arises how to get into IT jobs without any prior knowledge or with minimal IT understanding. Good news is there are many areas which one can explore to get entry into IT field easily and quickly. Not only one can get into it easily with minimal or no IT Knowledge but one can earn handsome salary too.  

There are majorly four to five sectors involved in order to develop and execute any software successfully.

First is Business Analyst (BA) who is responsible for gathering all requirements from client ,how he (client) wish his software to look and behave. This is high level role and responsibilities of BA. This is also one of the domains in which anyone with IT or no IT Background can step in. One can opt for Business analyst course from professionals who can guide them practically. As a Business Analyst one should be good in communicating and understanding all functional and non functional requirements, methodologies followed to carry out development of project. Choosing best Business analyst course where you are trained on updated and current market trends is the key to success.

Second is Developer who is responsible for actually building the software by following the requirements collected by Business analyst. This person should have good programming knowledge as it includes coding and designing. One should be specialized in any of the programming language knowledge(Java, Dot Net, PHP, C# .Net, Python, ruby etc) in order  to become developer. Its difficult to pull this job with no IT Background or knowledge or minimal IT Knowledge. This designation requires in depth training and project experience. This field is not for quick and easy entry in IT industry it requires time. Salary package is really good for developers and Jobs can be permanent or contract based.

Third is Database Administrator (DBA) who is responsible for backend/database of the software where actual data is being stored. As a DBA one should know how to secure database, how to take backups and recover data from different applications or software. Also he is responsible for giving permissions to other users till which level they can access data. In order to get into it one should have great working knowledge of databases like Oracle, SQL Server, DB2 etc. With minimal IT knowledge one can understand and get trained on it. First step can be, to learn SQL as it is considered to be building block for learning databases. There are many SQL training courses but recommended is with practical knowledge of JOINS, SUBQUERIES , CLAUSES,CONSTRAINTS, OBJECTS, DDL,DML, DCL etc.

Fourth is Software Testers (QA) who is responsible for checking whether software is being build according to Clients requirements or not. Testing is to check quality of software and to find all possible bugs in it. This field is totally IT independent which means it does not require any prior IT Knowledge.

One can easily and quickly step into IT by learning software testing. Software testing can be manual as well automated. In manual, one will manually check whether software is looking and behaving according to clients requirements or not whereas in automation , software is tested automatically. Job scope of software testers is increasing dramatically day by day and is considered to be best way to step  into IT industry. With effective software testing course or best QA training one can easily and quickly become software tester. Salary package is good for manual testers  and even better for automation testers. One should have good hands on expertise  in HP ALM, Jira, SQL, HP UFT and with blend of selenium testing , one can enhance their chances of getting their dream job as software testers. Selenium training course should include training on any programming language(Java, Python, C#.net, Ruby etc.) , training of element locators and basic to advance concepts of Selenium, Frameworks etc.

 Mainly above mentioned areas are pillars of any IT Company and with practical training on any of them one can step into IT field.

Easy and quick domains are Software Testing, Business Analyst for one who wishes to enter with no IT Knowledge.

With Minimal IT Knowledge you can get into Database administration job, but it needs time and expertise to get job

Lastly in order to fetch Developers job one should be very much experienced in their respective languages.

Contact:

Phone: 289-499-4040

Address: 20A-284 Orenda Road, Brampton, ON, L6T4X6

Send questions and inquiries through our contact form:

http://www.roicians.com/contact-us/

Like us on Facebook:   https://www.facebook.com/roicians/

Connect with us on Instagram: Roicians

[sandeep2363]

Generate DDL script of Schema with EXPDP/IMPDP in Oracle

Generate DDL script of Schema with EXPDP/IMPDP in Oracle

Get DDL of all objects in one script of Schema in Oracle EXPDP the schema with METADATA_ONLY option C:\Users\ORACLE>expdp directory=dbbackup dumpfile=hr_metadata.dmp content=metadata_only Export: Release 18.0.0.0.0 - Production on Wed Feb 2 22:15:37 2022 Version 18.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: hr@pdb1 Password: Connected to:…

[riatraininginstitute]

Advance Java Training Institute In Bangalore

Advanced JAVA Training in Marathahalli, Bangalore by RIA institute offers students an innovative way to learn Advanced JAVA in Bangalore. With experienced Advanced JAVA professional trainers and advanced lab Facilities to practice Advanced JAVA, students can complete Advanced JAVA training on real time scenario. Our Advanced JAVA Training institute in Bangalore is rated as one of the Best Advanced JAVA Learning Centre on Google.

Training methodology used for conducting Advanced JAVA course includes ease of understanding Advanced JAVA concepts, latest examples in Advanced JAVA classes and real time practical exposure. This ensures that students opting for Advanced JAVA training in Marathahalli, Bangalore get value for money. Our Advanced JAVA course content is structured to cover all concepts under Advanced JAVA Training.

Advantages of Studying Advanced JAVA with Us :

Advanced JAVA Hands on experience

Project support on Advanced JAVA

Real time exposure on latest Advanced JAVA modules

New JAVA Training methodologies

Lab facilities and guidance

Advanced JAVA Trainers at RIA Institute :

8+ Years of experience on Advanced JAVA Projects

Has worked on multiple real-time Advanced JAVA scenarios

Worked as Top Advanced JAVA Consultant in MNC’s across the globe

Trained over 100 students over the years

Certified Advanced JAVA industry professional

Strong Knowledge of Advanced JAVA Theory and Practical’s

Advanced Java Course Content :

Introduction to Enterprise Edition

Distributed Multitier Applications

J2EE Containers

Web Services Support

Packaging Applications

J2EE 1.4 APIs

SQL

Oracle 10g XE installation

SQL-Basics(DDL,DML,DQL)

SQL-joins

Advance Java Training Institute In Bangalore | Orcale [sql&pl\sql] Training Institute In Bangalore | Dot Net Training Institute In Bangalore | Software Testing Training Institute In Bangalore | Tally Erp 9 Training Institute In Bangalore | Basic Of Computer Training Institute In Bangalore | Ms Access Training Institute In Bangalore | Sap Mm Training Institute In Bangalore | French Language Training Institute In Bangalore | Sap Sd Training Institute In Bangalore | Ui Developer Training Institute In Bangalore | Sap Hr Training Institute In Bangalore | Sap Abap Training Institute In Bangalore | Data Structure Training Institute In Bangalore | C Programme Training Institute In Bangalore | Ccna Training Institute In Bangalore | Mcsa Training Institute In Bangalore | Python Training Institute in Bangalore | Advance Excel+vba Training Institute in Bangalore | Selenium Training Institute in Bangalore | Sap Fico Training Institute in Bangalore | German Language Training Institute In Bangalore | Spoken English Training Institute In Bangalore | Ielts Training Institute In Bangalore

[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/