This error comes when the oracle service ‘OracleServiceORCL’ gets
stopped in the PC or Server. Here I have shown you 2 methods to solve
it.
Method 1 – Turn on the service from windows services 1. Open run window by pressing Cntl+R or by searching Run from start Menu. Now enter the ‘Services.msc’ and press enter.
2. Now locate the service ‘OracleServiceORCL’ from the service list, Right clink on the service and click start.
3. Once the status is shown as Running. The error would have gone now.
Method 2 – Start the service from Command Prompt 1. Open command prompt in Administrator Mode 2. Execute the below commands, set oracle_sid=ORCL net start oracleserviceORCL 3. It will show the message service started successfully. The error would have gone now.
A join is a query that combines rows from two or more tables, views, or
materialized views. Oracle Database performs a join whenever multiple
tables appear in the FROM clause of the query. The select list of the
query can select any columns from any of these tables. If any two of
these tables have a column name in common, then you must qualify all
references to these columns throughout the query with table names to
avoid ambiguity.
Oracle supports inner join, left join, right join, full outer join and cross join.
You can join a table to itself to query hierarchical data using an inner
join, left join, or right join. This kind of join is known as
self-join.
Setting up sample tables
We created two new tables with the same structure for the demonstration
using Sample-tables.sql script. The SQL script is under followed.
-- Create BALLOON_A table
CREATE TABLE BALLOON_A (
ID INT PRIMARY KEY,
COLOR VARCHAR2 (100) NOT NULL
);
-- Create BALLOON_B table
CREATE TABLE BALLOON_B (
ID INT PRIMARY KEY,
COLOR VARCHAR2 (100) NOT NULL
);
-- Insert data into BALLOON_A table
INSERT INTO BALLOON_A (ID, COLOR)
VALUES (1, 'Red');
INSERT INTO BALLOON_A (ID, COLOR)
VALUES (2, 'Green');
INSERT INTO BALLOON_A (ID, COLOR)
VALUES (3, 'Blue');
INSERT INTO BALLOON_A (ID, COLOR)
VALUES (4, 'Purple');
-- Insert data into BALLOON_B table
INSERT INTO BALLOON_B (ID, COLOR)
VALUES (1, 'Green');
INSERT INTO BALLOON_B (ID, COLOR)
VALUES (2, 'Red');
INSERT INTO BALLOON_B (ID, COLOR)
VALUES (3, 'Cyan');
INSERT INTO BALLOON_B (ID, COLOR)
VALUES (4, 'Brown');
Both the tables have some common colors such as Red and Green. Let’s
call the BALLOON_A the left table and BALLOON_B the right table.
Oracle inner join
The inner join selects records that have matching values in both tables.
The following statement joins the left table to the right table using the values in the color column.
SELECT
A.ID ID_A,
A.COLOR COLOR_A,
B.ID ID_B,
B.COLOR COLOR_B
FROM
BALLOON_A A
INNER JOIN BALLOON_B B ON A.COLOR = B.COLOR;
ID_A COLOR_A ID_B COLOR_B
---- ------- ---- -------
2 Green 1 Green
1 Red 2 Red
2 rows selected.
The above SQL query joins both tables and returns rows from the left
table that match with the rows from the right table as per the selected
criteria in on clause.
Oracle left join
The left join returns all records from the left table (BALLOON_A), and
the matched records from the right table (BALLOON_B). The result is NULL
from the right side, if there is no match.
The following statement joins the left table with the right table using a left join.
SELECT
A.ID ID_A,
A.COLOR COLOR_A,
B.ID ID_B,
B.COLOR COLOR_B
FROM
BALLOON_A A
LEFT JOIN BALLOON_B B ON A.COLOR = B.COLOR;
ID_A COLOR_A ID_B COLOR_B
---- ------- ---- -------
2 Green 1 Green
1 Red 2 Red
3 Blue
4 Purple
4 rows selected.
The above SQL query returns all rows from the left table with the
matching rows if available from the right table. If there is no matching
row found from the right table, the left join will have null values for
the columns of the right table.
Oracle right join
The right join returns all records from the right table (BALLOON_B), and
the matched records from the left table (BALLOON_A). The result is NULL
from the left side when there is no match.
The following example use right join to join the left table to the right table.
SELECT
A.ID ID_A,
A.COLOR COLOR_A,
B.ID ID_B,
B.COLOR COLOR_B
FROM
BALLOON_A A
RIGHT JOIN BALLOON_B B ON A.COLOR = B.COLOR;
ID_A COLOR_A ID_B COLOR_B
---- ------- ---- -------
1 Red 2 Red
2 Green 1 Green
4 Brown
3 Cyan
4 rows selected.
The above sql query returns all rows from the right table with the
matching rows if available from the left table. If there is no matching
row found from the left table, the right join will have null values for
the columns of the left table.
Oracle full outer join
The full outer join returns a result set that contains all rows from
both left and right tables, with the matching rows from both sides where
available.
The following example shows the full outer join of the left and right tables.
SELECT
A.ID ID_A,
A.COLOR COLOR_A,
B.ID ID_B,
B.COLOR COLOR_B
FROM
BALLOON_A A
FULL OUTER JOIN BALLOON_B B ON A.COLOR = B.COLOR;
ID_A COLOR_A ID_B COLOR_B
---- ------- ---- -------
2 Green 1 Green
1 Red 2 Red
3 Cyan
4 Brown
3 Blue
4 Purple
6 rows selected.
The above SQL query returns all rows from the right and left table with the matching or not matching rows.
The set operators are used to combine the results of two component
queries into a single result. Queries containing set operators are
called compound queries.
Setting up sample tables
We created two new tables departments and employees for the
demonstration using Sample-tables.sql script. The SQL script is under
followed.
-- Create DEPARTMENTS table
CREATE TABLE DEPARTMENTS (
DEPARTMENT_ID NUMBER(2) CONSTRAINT DEPARTMENTS_PK PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(14),
LOCATION VARCHAR2(13)
);
-- Create EMPLOYEES table
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(4) CONSTRAINT EMPLOYEES_PK PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(10),
JOB VARCHAR2(9),
MANAGER_ID NUMBER(4),
HIREDATE DATE,
SALARY NUMBER(7,2),
COMMISSION NUMBER(7,2),
DEPARTMENT_ID NUMBER(2) CONSTRAINT EMP_DEPARTMENT_ID_FK REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);
-- Insert data into DEPARTMENTS table
INSERT INTO DEPARTMENTS VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPARTMENTS VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPARTMENTS VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPARTMENTS VALUES (40,'OPERATIONS','BOSTON');
-- Insert data into EMPLOYEES table
INSERT INTO EMPLOYEES VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMPLOYEES VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMPLOYEES VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMPLOYEES VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMPLOYEES VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMPLOYEES VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMPLOYEES VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMPLOYEES VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMPLOYEES VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMPLOYEES VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMPLOYEES VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMPLOYEES VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMPLOYEES VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMPLOYEES VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
UNION
The UNION set operator returns all distinct rows selected by either query. That means any duplicate rows will be removed.
In the example below, notice there is only a single row each for departments 20 and 30, rather than two each.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30
UNION
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
4 rows selected.
UNION ALL
The UNION ALL set operator returns all rows selected by either query.
That means any duplicates will remain in the final result set.
In the example below, notice there are two rows each for departments 20 and 30.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30
UNION ALL
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
20 RESEARCH
20 RESEARCH
30 SALES
30 SALES
40 OPERATIONS
6 rows selected.
INTERSECT
The INTERSECT set operator returns all distinct rows selected by both
queries. That means only those rows common to both queries will be
present in the final result set.
In the example below, notice there is one row each for departments 20
and 30, as both these appear in the result sets for their respective
queries.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30
INTERSECT
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
20 RESEARCH
30 SALES
2 rows selected.
MINUS
The MINUS set operator returns all distinct rows selected by the first
query but not the second. This is functionally equivalent to the ANSI
set operator EXCEPT DISTINCT.
In the example below, the first query would return departments 10, 20,
30, but departments 20 and 30 are removed because they are returned by
the second query. This leaves a single rows for department 10.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30
MINUS
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID >= 20
ORDER BY 1;
DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
10 ACCOUNTING
1 row selected.
Note: The ORDER BY clause is applied to all rows returned in the final
result set. Columns in the ORDER BY clause can be referenced by column
names or column aliases present in the first query of the statement, as
these carry through to the final result set. Typically, you will see
people use the column position as it is less confusing when the data is
sourced from different locations for each query block.
SELECT EMPLOYEE_ID, EMPLOYEE_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10
UNION ALL
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID >= 20
ORDER BY EMPLOYEE_ID;
EMPLOYEE_ID EMPLOYEE_NAME
----------- --------------
20 RESEARCH
30 SALES
40 OPERATIONS
7782 CLARK
7839 KING
7934 MILLER
6 rows selected.
Using PERL script how can we process
large log files easily. In this example i will explain you how we can
process Oracle times ten in memory log files.We can make simple
modification for that script and we can use to parse large files like
size 5 GB. We can use this script for process Mysql, Sql, Psql and all
other database also.
Before starting writing script i will explain you what is Times Ten DB.
TimesTen In-Memory Database
Oracle TimesTen In-Memory Database (TimesTen) is a full-featured, memory-optimized, relational database
with persistence and recoverability. It provides applications with the
instant responsiveness and very high throughput required by
database-intensive applications. Deployed in the application tier,
TimesTen operates on databases that fit entirely in physical memory
(RAM). Applications access the TimesTen database using standard SQL
interfaces. For customers with existing application data residing on the
Oracle Database, TimesTen is deployed as an in-memory cache database
with automatic data synchronization between TimesTen and the Oracle
Database.
It increase speed of data access and performance for accessing data from db.
We can use the following places to boost the speed, performance
and delay latency in application. How is TimesTen used by real-life
customers? TimesTen is deployed by
thousands of customers spanning industries in communications, financial
services, web applications, travel logistics, gaming, and more.
Script Code for process
By default times ten db create a log file with the names of
Keyagent.log
We can use this script to ptocess other db [mysql , oracle , psql .. etc].
package AgentParser;
use 5.22.1;
use strict;
use warnings;
use Errstate::Return;
require Exporter;
our @ISA = qw(Exporter);
our %EXPORT_TAGS = ( 'all' => [ qw() ] );
our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );
our @EXPORT = qw(Agentlog);
our $VERSION = '0.01';
BEGIN
{
if(! -d "/var/lib/perl_lib/" )
{
print "\nDependent directory /var/lib/perl_lib/ not found!!!\n\n";
exit 1;
}
unshift(@INC,"/var/lib/perl_lib");
}
my ($LogFile,$outfile,@stringgrep);
my $loglevel = "DEBUG";
my $logger = Return->new();
$logger->setlogfile($LogFile,'a');
$logger->disable_console();
sub _shiftlog($)
{
$LogFile = shift || "Keyagent.log";
return $LogFile;
}
sub _shiftout($)
{
$outfile = shift || "Agentlog_Count.txt";
return $outfile;
}
sub CleanUP($)
{
my $Flag = shift || '1';
$logger->closelog();
exit($Flag);
}
sub untaring($)
{
$logger->info("untaring() started...");
my $file = shift || 'agent.log.1';
if($file !~ m/gz/)
{
print "$file - $!\n";
$logger->error("GUNZIP:file $file not available");
CleanUP(1);
}
system("gunzip $file");
$file=~s/\.gz//;
if($? ne 0)
{
$logger->error("GUNZIP: not working");
CleanUP(1);
}
$logger->info("untaring() Completed...");
parsing($file);
}
sub parsing($)
{
my $c = 0;
$logger->info("parsing() started...");
my $parsefile = shift || 'agent.log.1';
if(! -e $parsefile)
{
print "$parsefile - $!\n";
CleanUP(1);
}
open(OUTFILE,">>$outfile") or die("FILE:not able to open file $!\n");
my @array = `grep -oiP '(\\d{4}\-\\d{2}\-\\d{2})' $parsefile | head -n1 && grep -oiP '\\d{2}\:\\d{2}\:\\d{2}' $parsefile | head -n1`;
my @end=`grep -oiP '(\\d{4}\-\\d{2}\-\\d{2})' $parsefile | head -n1 && grep -oiP '\\d{2}\:\\d{2}\:\\d{2}' $parsefile | tail -n1`;
chomp(@end,@array,$parsefile);
print OUTFILE "\n---------FILE: $parsefile---------------\n";
print OUTFILE "LOG START TIME: $array[0] $array[1]\nLOG END TIME: $end[0] $end[1]\n";
foreach my $key (@stringgrep)
{
my $count = `grep -o "$key" $parsefile |wc -l`;
if($count > 0)
{
print OUTFILE "\nKEYWORD: \"$key\"\nNO.Of.Counts:$count";
my @pattern=`grep "$key" $parsefile`;
LP:if(!@pattern)
{
$logger->error("KEYWORD: \"$key\" not exist in file: \[$parsefile\]");
next;
}
print OUTFILE @pattern;
}
else
{
$c++;
goto LP;
}
}
print OUTFILE "$c LINES NOT MATCHED CHECK LOG FILE 'Keyagent.log' \n" if($c);
$logger->info("Parsing() Completed...");
$logger->info("----------------------\n");
print "INFO: Parsing Completed check logfile: Keyagent.log and output file Agentlog_Count.txt\n";
}
sub Agentlog
{
_shiftlog(@_);
_shiftout(@_);
opendir(DIR, ".") || die "can't opendir $!";
my @dirmatch = grep { /agent(\d+)*(\.)*(log)*(\d+)*(\.)*(gz)*|agent(\.)*(log)*(\.)*(\d+)*(\.)*(gz)*/ && -f "/home/kodiak/$_" } readdir(DIR);
closedir DIR;
@stringgrep=("Starting NuoDB agent","Running as Broker", "Setting up Raft server","Raft server started","Converting to Leader","Peer joined","Node joined","Peer left","Node left","Node state changed to ACTIVE","Node state changed to RUNNING","has become active", "was previously inactive for","agent is shutting down","ShutdownHook called","has become inactive");
foreach my $filename (@dirmatch)
{
chomp($filename)||$filename=~s/\n//;
$filename = $filename =~ m/\.gz/ ? untaring($filename) : parsing($filename);
}
}
1;
__END__
=head1 NAME
AgentParser - To Parse the Timesten Default Agentlog files under Linux/Unix system.
=head1 SYNOPSIS
use Timten::AgentParser;
$array=AgentParser->Agentlog("Logfile","outputfile");
=head1 DESCRIPTION
To Parse the Timesten Default Agentlog files under Linux/Unix system.
=head2 EXPORT
use Timten::AgentParser;
$array=AgentParser->Agentlog("Logfile","outputfile");
=head1 SEE ALSO
=head1 AUTHOR
Name :Kaavannan, K<br>
E-Mail: kaavannaniisc@gmail.com
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2016 by Kaavannan K
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.22.1 or,
at your option, any later version of Perl 5 you may have available.
=cut
