Tag: PL sql

AWS Aurora Database migration and Best Practices

Posted on by HQW

The information on the AWS Aurora and DataMigration info from Oracle is shared in the linked in vlog: LinkedIn | YouTube | Krishna Sarabu | Joseph DiCaro

There are several blogs supplied by AWS for self-migration and many vendors listed by AWS who can help in the migration of the database.

A few notes are mentioned below:

  • Scalability
  • Amazon DMS- Data Migration Service
  • AWS- Schema Conversion tool – Assessment report in csv – helps to create reports
  • Oracle to AWS Aurora database migration support.
  • DMS Trouble shooting blogs!

Best Practices:

  • Table spaces
  • Index Types: Hash, B-Tree, GIN, GiST, BRIN, SP-GiST, partial, expression
  • Data types: 64 base types
  • Dual Table – Do not mock dual table- use view if necessary
  • Nulls- PostgreSQL and Oracle use nulls differently
  • Sequences – in PostgreSQL- each session has its own cache
  • Exceptions
  • Design guidelines

SQL -Oracle- PL/SQL -Introduction

Posted on by HQW

What is Oracle: RDMS owned by Oracle Corporation, which has different version for its respective customers and community.

  1. Enterprise
  2. Standard
  3. Express
  4. Oracle Lite
  5. Personal Edition

Lets see few interesting ingo below:

TABLE CONSTRAINTS – use don Create table and alter table – there are 3 constraints – Primary Key, foreign Key, Check. The column constraints are

Create table –

CREATE TABLE schema_name.table_name (
column_1 data_type column_constraint,
column_2 data_type column_constraint,
…
table_constraint
);

Ex: kindly relate/compare the texts and see! below tb.userInfo is the table name and the Column names are id_no, Firstname and the Last name respectively

CREATE TABLE tb.userInfo (
id_no NUMBER GENERATED BY DEFAULT AS IDENTITY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
PRIMARY KEY(id_no));

Alter Table:

ALTER TABLE table_name
ADD column_name type constraint;
Ex:
ALTER TABLE tb.userInfo(
ADD COLUMN Address VARCHAR(300) NOT NULL
);

to validate if the above code works, it lists all the column info of the specified table.

Desc tb.userInfo

Create Views

Oracle Clauses

Oracle Operators

Oracle Advance

Oracle Joins

Advanced Oracle