Category: BASICS

Categories
19c database BASICS

How to forward a Program Display from a Remote Linux Server using X11

Introduction

Software is usually installed via command-line tools. It can be automated, scheduled, and repeated with minimal effort. However, for quick testing and demo purposing, or when you are new to some products, you might prefer discovering the new options using a graphical interface. For example, when installing Oracle Grid Infrastructure, Oracle Database, or Oracle Database Client. Tip: when you need an Oracle Database and quickly get started, just provision one on Oracle Cloud, fully automated.

This blog post provides a step-by-step guide on enabling X11 on your Linux server and forwarding the program display to your local Windows machine.

The Environment

  • Oracle Linux VM on Oracle Cloud.
  • Local Windows machine with PuTTY as SSH client and Xming as X Window server.

Preparation

Step 1: Install PuTTY and Xming

Download and install PuTTY and Xming on your local Windows machine.

Step 2: Install the required packages on the Linux server

On your Linux server, install the xorg-x11-xauth package:

sudo yum install xorg-x11-xauth -y

Step 3: Configure X11 forwarding on the Linux server

On your Linux server, make sure that /etc/ssh/sshd_config file contains the following option:

sudo vi /etc/ssh/sshd_config
...
X11Forwarding yes
...

If you made changes to the sshd_config file, restart the sshd service:

sudo systemctl stop sshd.service
sudo systemctl start sshd.service
sudo systemctl enable sshd.service

Connection

Step 4: Configure and start Xming

Configure and start Xming via the provided XLaunch utility. Search for the XLunch application and start it. Keep the defaults, click Next, then Finish:

Step 5: Establish an SSH connection via PuTTY

In your PuTTY session, enable the X11 forwarding checkbox and establish the connection:

Step 6: Test display forwarding

To test the display forwarding, install and start the xclock application:

sudo yum install xclock -y
xclock

The clock graphical application will be shown on your Windows machine:

Xming will show the currently 1 client connected.

Troubleshooting

If you are still getting the “Can’t open display” error message. Restart your PuTTY session. When the session opens, you might see the “file /home/opc/.Xauthority does not exist” message. Nevertheless, display forwarding will work now. The message does not appear anymore when you start a new session again.

 

Categories
19c database BASICS

EXTERNAL TABLE

INTRODUCTION:

  • In this blog, we are going to create the external table
DESIGNATION:
  • This is easy to make and easy to insert the data quickly.
  • This is much faster than SQL * LOADER.
  • In this type of table, we cannot use a DML statement.
  • Bulk data can be easily inserted into the table.
OVERALL STEPS:

Step 1: Go to make a directory at the OS level

Step 2: After making the directory

Go to the directory, we have to create a text file and add the necessary values.

Step 3: We have to notify the location of the directory.

Step 4: Go to database, conn sys user

We have to permit for creation of a directory in that user by using the sys

Step 5: Create the directory and mention the location of the OS.

Step 6: Conn sys user,

We have to permit reading and writing to create a directory by using sys.

Step 7: Create the table and give the external format

Step 8: View the table

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Categories
19c database BASICS

Database link

Database link:

A database links allows an Oracle client to access two or more databases as one 
logical database via a connection between the database servers. 
Database links are actually entries in a data dictionary table that define 
a communication path from one Oracle database to another remote database. 
In order to access the database link a user must be connected to the 
database that contains the data dictionary table that defines the database link

Types Of Database Links

Private and Public

Private Link

To create a private database link, you must have the CREATE DATABASE LINK 
system privilege.
To create a public database link, you must have the CREATBE PUBLIC DATAASE LINK 
system privilege.
Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Private database link to a user in a remote database

SQL> CREATE DATABASE LINK orcl_remote
   CONNECT TO scott IDENTIFIED BY tiger
   USING 'orcl'; 

Database link created.

Private database link to a user in a remote database, with the full connection string

SQL> CREATE DATABASE LINK scott_remote
   CONNECT TO apple IDENTIFIED BY apple
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=orcl))
          )';

Database link created.

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Categories
19c database BASICS

Oracle Materialized View

Oracle Materialized View

A materialized view in Oracle is a database object that contains the results of a 
query.They are local copies of data located remotely, or are used to create summary 
tables based on aggregations of a table’s data.

A materialized view can query tables, views, and other materialized views.
A materialized view, or snapshot as they were previously known, is a table segment 
whose contents are periodically refreshed based on a query, either against a local 
or remote table.

Syntax

CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE] AS SELECT …;

Methods

BUILD 

IMMEDIATE : The materialized view is populated immediately.This option is default one.
DEFERRED : The materialized view is populated on the first requested refresh.

Refresh Types

  • FAST: A fast refresh is attempted only there is a change in the base table. If materialized view logs are not present against the source tables in advance, the creation fails. To maintain the history of change in the base table, it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in the source database in the same schema as the master table. Refresh fast will perform refresh according to the changes that occurred in the master table.
  • COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
  • FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.
ON COMMIT : The refresh is triggered by a committed data change in one of the 
dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
GRANT CREATE MATERIALIZED VIEW TO INDIA;
Grant succeeded.

GRANT CREATE DATABASE LINK TO INDIA;
Grant succeeded.

Creating Database link to point remote database 

SQL> CREATE DATABASE LINK PROD_LINK CONNECT TO ph IDENTIFIED BY ph USING 'orcl';

Database link created.

COMPLETE REFRESH

Creating Materialized View

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK;

Materialized view created.
SQL> select * from emp;

        NO NAME                     SALARY
---------- -------------------- ----------
         1 apple                     20000
         2 orange                    23000
         3 mango                     32000

Table Data present in Remote database

SQL> insert into emp values (6,'ice',30000);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAA          1 apple                     20000
AAAR36AABAAAbURAAB          2 orange                    23000
AAAR36AABAAAbURAAC          3 mango                     32000

Manually using complete refresh using the DBMS_VIEW package

SQL>  execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAD          1 apple                     20000
AAAR36AABAAAbURAAE          2 orange                    23000
AAAR36AABAAAbURAAF          3 mango                     32000
AAAR36AABAAAbURAAG          6 ice                       30000

DEFERRED

Initially drop the existing materialized view

SQL> drop MATERIALIZED VIEW dept_mv;

Materialized view dropped.

Materialized view creation using DEFERRED option

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD DEFERRED REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK;

Materialized view created.


SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

QL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR39AABAAAbURAAA          1 apple                     20000
AAAR39AABAAAbURAAB          2 orange                    23000
AAAR39AABAAAbURAAC          3 mango                     32000
AAAR39AABAAAbURAAD          6 ice                       30000

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Categories
BASICS LINUX

Linux File Compress commands

File Compress commands 

Linux we have two types of compress commands

  1. GZIP
  2. GUNZIP

To reduce the file size by using compress commands

gzip – This command used to compress the file size

[oracle@oracletest scripts]$ gzip ramkumar.txt
[oracle@oracletest scripts]$ ls -l
total 24
-rwxrwxrwx. 1 oracle oracle 72 Oct 21 01:49 ramkumar.txt.gz
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh

Gunzip – This command is user to unzip the original file size

[oracle@oracletest scripts]$ gunzip ramkumar.txt.gz
[oracle@oracletest scripts]$ ls -l
total 24
-rwxrwxrwx. 1 oracle oracle 42 Oct 21 01:49 ramkumar.txt
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8