#global temporary tables in oracle

[eswars-tech-world]

Question-81: Explain the difference between a global temporary table and a regular table in Oracle.

Interview Questions on Oracle SQL & PLSQL Development: For more questions like this: Do follow the main blog #oracledatabase #interviewquestions #freshers #beginners #intermediatelevel #experienced #eswarstechworld #oracle #interview #development #sql

A global temporary table and a regular table in Oracle serve different purposes and have distinct characteristics. Regular Table: A regular table in Oracle is a permanent database object that stores data persistently. It is used to hold data that is retained for a longer duration and can be accessed by multiple sessions. Global Temporary Table: A global temporary table is a special type of…

[ocptechnology]

Global Temporary Tables

Global Temporary Tables #oracle #oracledba #oracledatabase

Applications regularly utilize some type of impermanent information store for processes that are too convoluted to finish in a solitary pass. Regularly, these transitory stores are characterized as data set tables or PL/SQL tables. From Oracle 8i ahead, the upkeep and the board of transitory tables can be assigned to the waiter by utilizing Global Temporary Tables. Types of Temporary Tables The…

[sandeep2363]

Explain the plan of SQL query with plan table in Oracle

Explain the plan of SQL query with plan table in Oracle

Explain the plan of SQL query with plan table in Oracle Plan_table is a global temporary table present for all schemas to store the output of EXPLAIN PLAN. For Manually Create the PLAN_TABLE at your own schema level: @?\rdbms\admin\utlxplan.sql Plan table output: SQL> Explain plan for select * from employees; Explained. SQL> SELECT PLAN_TABLE_OUTPUT FROM…

[desaletushki]

Workload Scheduling & Automation Market Worth $3.65 Billion By 2027 | Global and Regional Forecast | Grand View Research, Inc.

The global workload scheduling and automation market size is expected to reach USD 3.65 billion by 2027, registering a CAGR of 6.5%, according to a new study conducted by Grand View Research, Inc. Continued digital transformation and penetration of cloud computing coupled with the growing demand for scalability are expected to drive the market growth. As application development in the IT industry is gradually gaining pace, developers are exploring different means to make the application delivery process more scalable and agile. At the same time, the demand for integrated workload automation is growing in line with the need to accelerate the time from design to delivery. All these factors bode well for market growth. 

Workload scheduling and automation can potentially help enterprises reduce manual errors, improve the execution of batch tasks, and optimizing storage and job scheduling. Workload scheduling and automation solutions can also increase the overall efficiency and IT productivity of the organizations that are serving the business needs of their customers. However, the additional expenditure incurred by organizations on the installation and maintenance of workload scheduling and automation solutions is expected to restrain the growth of the market. On the other hand, the adoption of the Operational Expenditure (OPEX) model of Software as a Service (SaaS) as an alternative to OPEX as a software platform is expected to open new growth opportunities in the market.

Prominent vendors, such as Oracle Corporation, Cisco Systems, and IBM Corporation, play a crucial role in guiding clients through their digital transformation journey amid the growing preference for cloud computing and pervasive network access and the increasing number of tech-savvy users and trading partners. Moreover, advances in the latest technologies, such as Artificial Intelligence (AI) and Machine Learning (ML), are allowing vendors to enhance their Workload Automation (WLA) tools to ensure improved operational and managerial processes, integration capabilities, and overall customer experience. However, factors such as the high costs associated with the management and implementation of these tools are the factors expected to challenge the growth of the market. The market is also expected to confront a temporary period of sluggish growth owing to the outbreak of the COVID-19 pandemic. 

Browse Full Report (Tables & Figures) @ https://www.grandviewresearch.com/industry-analysis/workload-scheduling-automation-market

Contact Us:

Grand View Research, Inc. 201 Spear Street 1100, San Francisco, CA 94105 United States

Phone: 1-415-349-0058 Toll Free: 1-888-202-9519 Email: sales@grandviewresearch.com

[durgeshdbmr]

Content Marketing Software Market Import-Export Analysis for each Region, Type, Product and Application Up to 2028

 The rising technology in Content Marketing Software Market is also depicted in this research report. Factors that are boosting the growth of the market and giving a positive push to thrive in the global market is explained in detail. The study considers the present scenario of the Content Marketing Software market and its market dynamics for the period 2021-2028. It covers a detailed overview of several market growth enablers, restraints, and trends. The report offers both the demand and supply aspects of the market. It profiles and examines leading companies and other prominent ones operating in the market.

Staying on top of market trends & drivers is crucial for decision-makers to hold this emerging opportunity. The study provides information on market trends and development, drivers, capacities, technologies, and the changing investment structure of the Content Marketing Software market. The development scope, feasibility study, market concentration, and maturity analysis are elaborated in this report. Markets addresses all these aspects and provides the latest scoop and detailed eye-opening study on all major & emerging business segments.

Request Sample Copy Here @ https://www.databridgemarketresearch.com/request-a-sample/?dbmr=global-content-marketing-software-market

The report also includes a comprehensive company profile and other useful data such as manufacturing capacity, actual production, product performance, sales, pricing structure, and manufacturing costs of key players. Also Provides a detailed analysis of the competitive environment of the global Content Marketing Software market. The report also includes data on market demand and supply chains and provides details on raw material suppliers, manufacturing equipment, and primary consumers.

The COVID-19 (Corona Virus Disease) is affecting all the aspects across the world, whereas manufacturers are developing various temporary solutions to avoid the corona virus impact on the global Content Marketing Software market. Due to the pandemic, major key players of the market are witnessing the fluctuation in demand, which mainly changes the market trends, potential opportunities, and consumer preferences. Some of the Key Players along with us: Kenscio, Adobe, Contently, Rock Content, Percolate Industries, Inc., Alluresoft, LLC, MINTENT, Uberflip, Oracle, Upland Software, Inc., HubSpot, Inc., PathFactory Inc., Salesforce.com, inc., SnapApp, Skyword, Inc., SCOOP.IT INC., ONESPOT.

Important Points that are covered in the Global Content Marketing Software Market:

Deep analysis of the investment scenario of the global Content Marketing Software market

Information related to the ongoing research and development projects and pipeline research and development projects.

Business overview and business strategies of key players

Impact of COVID-19 pandemic on the growth of the market

Growth map on technology improvement and with an impact on market analysis

Get Download Complete Detailed TOC @ https://www.databridgemarketresearch.com/toc/?dbmr=global-content-marketing-software-market

Strategic Points Covered in Table of Content of Market:

Chapter 1: Introduction, market driving force product Objective of Study and Research Scope the Content Marketing Software Market

Chapter 2: Exclusive Summary - the basic information of the Global Content Marketing Software Market

Chapter 3: Displaying the Market Dynamics- Drivers, Trends and Challenges of the Global Content Marketing Software Market

Chapter 4: Presenting the Global Content Marketing Software Market Factor Analysis Porters Five Forces, Supply/Value Chain, PESTEL analysis, Market Entropy, Patent/Trademark Analysis

Chapter 5: Displaying the by Type, End User and Region 2010-2020

Chapter 6: Evaluating the leading manufacturers of the Global Content Marketing Software Market which consists of its Competitive Landscape, Peer Group Analysis, BCG Matrix & Company Profile

Chapter 7: To evaluate the market by segments, by countries and by manufacturers with revenue share and sales by key countries in these various regions.

Chapter 8 & 9: Displaying the Appendix, Methodology and Data Source

The key questions answered in this report:

What will be the Market Size and Growth Rate in the forecast year?

What are the Key Factors driving market?

What are the Risks and Challenges in front of the market?

Who are the Key Vendors in Content Marketing Software market?

What are the Trending Factors influencing the market shares?

What are the Key Outcomes of Porter’s five forces model?

Which are the Global Opportunities for Expanding the market?

Insightful Information Of this Report@ https://www.databridgemarketresearch.com/reports/global-content-marketing-software-market

About Us:

Data Bridge Market Research set forth itself as an unconventional and neoteric Market research and consulting firm with unparalleled level of resilience and integrated approaches. We are determined to unearth the best market opportunities and foster efficient information for your business to thrive in the market.

Contact:

Data Bridge Market Research

Tel: +1-888-387-2818

Email: Sopan.gedam@databridgemarketresearch.com

Browse Related Reports@

Vehicle Analytics Market

System Integration Market

Data Prep Market

[poetryofchrist]

Will the real rules please stand up?

 One of my pandemic projects is to map the differences between a pointed and an unpointed text of the Hebrew Bible. I am doing this using the very memorable and capable left to right version of the square text called SimHebrew. 

I had 'my own version' of a left-to right abbreviation  in capital letters and with a few punctuation marks for the gutturals aleph and ayin. But I converted to the lower case version developed by Jonathan Orr-Stav. (Such a Latin-letter code is an abbreviation because it takes one byte as opposed to 7 bytes in rendered Unicode, and one byte as opposed to 2 internally. Using a Unicode database is really awkward for me and the technology was new 10 years ago so it was a non-starter.) 

My method of data capture is to use the unpointed Mechon-Mamre text that can be downloaded from their site (one book at a time) and to run it through the SimHebrew converter here. Then to manipulate the text in notepad until I have legitimate insert statements for my database. This is somewhat prone to error but eventually I get a clean script. (I use a combination of Word and Notepad for the necessary global changes.)

After this I create a temporary table that matches: book, chapter, a conversion of the alef-betic verse number to a real verse number, and a word by word Hebrew word in SimHebrew, full pointed text word from the Leningrad codex, the stem code, raw word form, and semantic domain from my database, the word number relative to the start of the verse, and the word id (assigned by an Oracle sequence and connecting to my word table).

I recently added 2 Kings to my data. At first I assigned the wrong chapter numbers - a little oversight. That resulted in over 5000 differences in my calculations. When I fixed the issue, the differences went down to about 230. I then discovered that M-M is treating דִּבְיוֹנִ֖ים (dbivn) which they render as dung of a dove as two words (db ivnh - apparently a poor substitute for salt). Fixing those 4 instances dropped my mismatches by 100. Then word by word I work through the remainder until the differences are accounted for.

My program that calculates the words also suggests a code snippet that will fix any discrepancy (as long as I put it in the right place in the code.) The program as it stands gets 99% of the words right on first pass now. So my predicted SimHebrew Bible is about 99% right. Sorry - for the Bible, that's not good enough.

About this time in the history of the project, I ask, Is there a better way? I know I can finish the way I have begun and my brute force spelling changes are next to 0. I have over a third of the words in my test data now and over 75% of the stems represented. But the code is specific to prefix and suffix and sometimes to particular vowel combinations in the WLC. What are the real rules? 

Native speakers who 'just know' the pronunciation - what are they really doing? Certainly they have retention in memory by word form, by context, and by stem. But can I get the program to discover the shortcuts that people use? And in some ways see what they are doing. (And thereby discover the nature of the evolution of language usage.)

Here's an example: the 31 uses so far of עצר (yxr)

For yxr, the following rules are noted. 

First you see that the holem is rendered as /o/ in lines 1 and 2 and others. This is default, but some words with some exceptions do not render the o. 

Next (line 3) you will note that the qamats is not rendered as /o/ (or vav for rtl Hebrew) but it is in line 4, but the qamats under the second letter of the stem is rendered with prefix /i/ and /t/ in each case without suffix. 

Tsere is rendered as /i/ in line 6 and 12 - but only the tsere under the last letter of the prefix. (I would like to color code these but the new editor absolutely ruins rtl display with embedded color coding.) 

Notice that the final h is dropped in line 19. 

Lines 26 and 27 would have rendered the qamats in the closed syllable as /o/ if the suffix had not been /u/. That's a general rule - but there are exceptions here too and exceptions to the exceptions.

Ref (book: chap: vs: (word within vs)

Stem

Word Form

Morph

Sim Source

Sim Calc.

WLC Word

Domain

Rendering

  2 Chronicles 14:10(30)

yxr

iyxr

i/yxr

-iyxvr

-iyxor

־יַעְצֹר

BOUND

יעצר will coerce

1 Chronicles 29:14(7)

yxr

nyxr

n/yxr

-nyxvr

-nyxor

־נַעְצֹר

BOUND

נעצר contained of

2 Chronicles 13:20(2)

yxr

yxr

yxr

-yxr

-yxr

־עָצַר

BOUND

עצר did coerce

2 Kings 4:24(9)

yxr

tyxr

t/yxr

-tyxvr

-tyxor

־תַּעֲצָר

BOUND

תעצר do detain

2 Chronicles 7:13(2)

yxr (5)

ayxr

a/yxr

ayxvr

ayxor

אֶעֱצֹר

BOUND

אעצר I contain

1 Kings 8:35(1)

yxr

bhyxr

bh/yxr

bhiyxr

bhiyxr

בְּהֵעָצֵר

BOUND

בהעצר when is contained

2 Chronicles 6:26(1)

yxr

bhyxr

bh/yxr

bhiyxr

bhiyxr

בְּהֵעָצֵר

BOUND

בהעצר when are contained

Amos 5:21(6)

yxr

byxrticm

b/yxr\ticm

byxrvticm

byxroticm

בְּעַצְּרֹתֵיכֶם

COVENANT

בעצרתיכם in your conclaves

2 Kings 9:8(9)

yxr

vyxvr

v/yxvr

vyxvr

vyxur

וְעָצוּר

BOUND

ועצור the contained

1 Kings 21:21(11)

yxr (10)

vyxvr

v/yxvr

vyxvr

vyxur

וְעָצוּר

BOUND

ועצור the contained

Proverbs 30:16(2)

yxr

vyxr

v/yxr

vyvxr

vyoxr

וְעֹצֶר

BOUND

ועצר and contained

1 Chronicles 21:22(17)

yxr

vtyxr

vt/yxr

vtiyxr

vtiyxr

וְתֵעָצַר

BOUND

ותעצר that may be contained

2 Kings 17:4(20)

yxr

viyxrhv

vi/yxr\hv

viyxrhv

viyxrhu

וַיַּעַצְרֵהוּ

BOUND

ויעצרהו so detained him

Job 4:2(5)

yxr

vyxr

v/yxr

vyxvr

vyxor

וַעְצֹר

BOUND

ועצר but contain

Psalms 106:30(4)

yxr (15)

vtyxr

vt/yxr

vtiyxr

vtiyxr

וַתֵּעָצַר

BOUND

ותעצר and was contained

Job 12:15(2)

yxr

iyxr

i/yxr

iyxvr

iyxor

יַעְצֹר

BOUND

יעצר he contains

2 Chronicles 2:5(2)

yxr

iyxr

i/yxr

iyxvr

iyxor

יַעֲצָר

BOUND

יעצר contains

1 Kings 18:44(19)

yxr

iyxrch

i/yxr\ch

iyxvrç

iyxorc

יַעַצָרְכָה

BOUND

יעצרכה detain you

2 Chronicles 22:9(27)

yxr

lyxr

l/yxr

lyxvr

lyxor

לַעְצֹר

BOUND

לעצר to coerce

Psalms 107:39(3)

yxr (20)

myxr

m/yxr

myvxr

myoxr

מֵעֹצֶר

BOUND

מעצר through coercion of

Proverbs 25:28(8)

yxr

myxr

m/yxr

myxr

myxr

מַעְצָר

BOUND

מעצר containment

2 Chronicles 7:9(4)

yxr

yxrt

yxr\t

yxrt

yxrt

עֲצָרֶת

COVENANT

עצרת a conclave

Joel 1:14(4)

yxr

yxrh

yxr\h

yxrh

yxrh

עֲצָרָה

COVENANT

עצרה a conclave

Joel 2:15(7)

yxr

yxrh

yxr\h

yxrh

yxrh

עֲצָרָה

COVENANT

עצרה a conclave

2 Kings 10:20(4)

yxr (25)

yxrh

yxr\h

yxrh

yxrh

עֲצָרָה

COVENANT

עצרה a conclave

Job 29:9(2)

yxr

yxrv

yxr\v

yxrv

yxru

עָצְרוּ

BOUND

עצרו contained

2 Chronicles 20:37(19)

yxr

yxrv

yxr\v

yxrv

yxru

עָצְרוּ

BOUND

עצרו could be coerced

2 Kings 14:26(10)

yxr

yxvr

yxvr

yxvr

yxur

עָצוּר

BOUND

עצור coercion

Jeremiah 36:5(7)

yxr

yxvr

yxvr

yxur

yxur

עָצוּר

BOUND

עצור am detained

1 Chronicles 12:1(7)

yxr (30)

yxvr

yxvr

yxvr

yxur

עָצוּר

BOUND

עצור he contained himself

1 Kings 14:10(12)

yxr

yxvr

yxvr

yxvr

yxur

עָצוּר

BOUND

עצור those who are contained

All that is pretty straightforward. But what are the real rules? 

Some of my rules are miles long conditions; strings of stems with prefix and suffix combinations and the occasional appeal to an odd Unicode value. 

The 'rules' are long for when to render hireq as /i/.

200 distinct stems which do not contain a yod are allowed to render hireq as yod without exception.

Another 281 allow hireq as yod on an exception basis.

Only 7 stems containing a yod disallow hireq as yod.

Another 46 disallow it on an exception basis.

Hireq is usually ignored in a closed syllable - but there are exceptions and exceptions by word form to the exceptions (only 8 stems).

Hireq is rendered as /ii/ for several reasons. When the prefix is i, and for tsere, patah, and qamats occasionally. What! these also are rendered as o or v sometimes. Who can know?

Any ideas?

I have been debating whether to design a data table for the rule combinations. I don't want to do it unless I cannot simplify the code. Last time I asked I discovered several simplifications. Looking for more...

Here's the rule for qamats becoming vav (RTL) or /o/ (SIM): I have converted the code to 'English'

Unconditionally for stems ahl, anih, arc, avn, bvw, ctl, grn, iq+n, iqwn, irqym, nvh, rnn, yziali, zvh, 

(+ is my single letter internal abbreviation for tet ט.)

or the stem is +rk and prefix suffix combination in (none, cm) 

or the stem is +hr and prefix suffix combination in (none, t, c, t)

or the stem is abd and prefix suffix combination in (b, n)

or the stem is acl and prefix suffix combination in (l, h, b, nu) 

or the stem is adm and v_prefix = m and the first part of the word is m with a schwa

or the stem is amn and the word form is amn and there is no tsere under the second stem letter

or prefix suffix combination = b, h

or the stem is ark and v_suffix in (ti, vt)

or the stem is bit and v_suffix in (icm)

or the stem is bzz and prefix suffix combination in (i, vm)

or the stem is cvl and prefix suffix combination in (v, clu)

or the stem is csh and prefix suffix combination in (none, u)

or the stem is dbr and prefix suffix combination in (b, c) 

or the stem is gal and prefix suffix combination in (l, c)

or the stem is gbh and prefix suffix combination = l, h

or the stem is gml and prefix suffix combination in (none, h,none, c) 

or the stem is hrh and prefix suffix combination in (vh, tihm)

or the stem is ivn and prefix suffix combination in (none, none)

or the stem is iwr and prefix suffix combination in (none, o, b, o)

or the stem is imn and prefix suffix combination in (m, none)

or the stem is isd and prefix suffix combination in (b, i)

or the stem is knn and not (suffix in (u) and there's a qamats under the second letter) and the domain is not in (PERSON, LOCATION)

or the stem is kq and v_suffix not in (u)

or the stem is kgg and prefix suffix combination in (none, i) 

or the stem is krb and prefix suffix combination in (none, h, l, h) 

or the stem is krm and prefix suffix combination in (none, h, vb, h) 

or the stem is kwc and prefix suffix combination in (none, i) 

or the stem is kzq and prefix suffix combination in (b, h, b, nu) 

or the stem is lcd and prefix suffix combination in (l, h) 

or the stem is mlc and prefix suffix combination in (l, o, b, o, c, o) 

or the stem is mvt and prefix suffix combination in (h, h) 

or the stem is pyl and prefix suffix combination in (c, h) 

or the stem is q+n and prefix suffix combination in (none, i)

or the stem is qdw and v_suffix in (im, iv)

or the stem is qra and prefix suffix combination in (none, nu, none, ic, none, im) 

or the stem is qrk and prefix suffix combination is none, h

or the stem is rb  and prefix suffix combination in (b, none) 

or the stem is sll and prefix suffix combination in (none, vh) 

or the stem is rbb and prefix suffix combination in (v, none)

or the stem is rbb and prefix suffix combination in (hb, none)

or the stem is rkb and prefix suffix combination not in (none, none)

or the stem is rkx and prefix suffix combination in (l, h)

or the stem is tmm and prefix suffix combination in (b, none) and the first two letters of the word are not m

or the stem is wby and prefix suffix combination in (m, h, l, h) 

or the stem is wcb and prefix suffix combination in (b, o) 

or the stem is wcr and prefix suffix combination is l, h

or the stem is wdd and prefix suffix combination in (i, m)

or the stem is wmr and prefix suffix combination in (a, none,vl, h,none, h)

or the stem is wp+ and prefix suffix combination in (none, h) 

or the stem is wr and suffix in (c, rc)

or the stem is wrw and prefix suffix combination in (none, ih) 

or the stem is xrp and prefix suffix combination in (none, h) 

or the stem is ybd and prefix suffix combination in (l, h)

or the stem is yni and prefix suffix combination in (none, nu) 

or the stem is yzz and prefix suffix combination in (none, i) 

or the stem is zcr and prefix suffix combination in (b, nu) 

or the stem is yzr and prefix suffix combination in (l, ni, l, u,none, ni,none, nu)

then

the qamats under the first letter of the stem becomes /o/.

And by the way if the stem is qdw and v_suffix in (im) then the schwa under the first letter becomes /o/!

(or maybe only v)

from Blogger https://ift.tt/360WXl0 via IFTTT

[kuwaiti-kid]

4 Ways That COVID-19 Will Change Your Career Forever

It's no secret that the coronavirus (COVID-19) pandemic has fundamentally changed the way we work. But, there is a very real possibility that these changes may not just be temporary.

Once the pandemic passes, many of these changes could be made permanent.

And, the numbers are proving just how much of our work culture has been moved online. Zoom recorded a whopping 200 million daily users on its popular remote video conferencing platform in the month of March. Traditionally, about 10 million users log in per day.

According to Gallup, more than half of managers said that the coronavirus pandemic has convinced their organization to let staff work from home more often. And, staff is using this remote work opportunity to maintain a more relaxed work culture, build wealth and spend less time in their cars.

4 Ways COVID-19 Will Change Your Career

The pandemic has changed the way we do business in several ways. Here are the top four.

#1: Working from home

The most obvious change is the widespread adoption of the work-from-home culture. Even without the COVID-19 pandemic, work from home opportunities increased by 159% in the last 12 years. And, nine out of 10 remote workers hope to continue working from home for the rest of their careers.

The forced remote work set up in 2020 has shown many companies that maintaining a remote staff has several benefits, like improving employee morale, boosting staff retention, and reducing real estate costs likes office space and associated monthly utilities.

The benefits of remote work are plentiful. According to an SHRM study, nearly 75% of remote employees said that remote work has improved their work/life balance. And, 2 in 3 workers report an increase in productivity during the day by working at home instead of an office.

A report by Flex Jobs found similar numbers. “Since 2013, work-life balance (76%), family (44%), time savings (42%), and commute stress (42%) have been the top four reported reasons people seek flexible work.”

It's true that remote work won't be possible – or even beneficial, for some staff. But, the numbers clearly show that a movement toward working from home is well-received by the majority of workers.

#2: Less business travel

While business travel won't disappear entirely, the pandemic has changed the traveling landscape. Notably, remote video conferencing capabilities like Zoom, Microsoft Teams, and Skype have all revealed the reality of conducting business online rather than through in-person contact, saving time and money on business travel.

Like Zoom, the Microsoft Teams platform has skyrocketed in recent months. Now, Teams sees over 70 million users logging in each and every day, a service that Microsoft released in 2017. Skype has increased in usage by 70% since March 2020.

A survey in Global Business Travel Association, a trade group designed for corporate travel leaders, found that over 90% of businesses have all but eliminated corporate travel. The business foundations that these companies have put into place to help ensure worldwide business continuity will, to some degree, likely remain in place after COVID-19 passes.

#3: Changed office designs

The pandemic has shown the health liability inherent in certain office designs, especially those where desks are facing each other. Tight office configurations keep their staff working in very close proximity, potentially strengthening the hazards of in-office health.

“Companies are going to have to reorient workstations, so people aren't facing one another. That may mean changing where the computer is or where the power source is located”, architect specialist Marc Spector told CNBC.

“Companies are going to have to remove all of the tabling and the seating in conference rooms or other communal areas to give greater flexibility to space distance. Any personal effects at desks or offices need to be removed,” Spector added.

Open floor offices might be on the chopping block, too.

According to the New York Times, many in-person office spaces may begin to include health features like sneeze guards and plexiglass barriers between desks and working areas. Build-in hand sanitizer stations, air filters, and outdoor (rather than indoor) gathering spaces are other ideas being mulled by many organizations.

The Times added that boosting paid sick leave may encourage ill employees to stay home (or work from home) rather than making sick employees feel obligated to work in the office and possibly spread germs.

#4: Global availability of workers

As more business moves online, the physical location of staff will increasingly become less important. This could impact salaries as the demographics of office staff begin to vary wildly between countries and their costs of living.

In addition, the global workforce could change the hiring process. Worldwide staffing options will boost the competitiveness of certain jobs like data entry positions, telephone support, and other roles that do not require specialized skills.

In fact, many companies are actively searching to boost their customer support, sales, and accounting roles even during the pandemic. The reduction in overhead previously imposed by real estate limitations has opened several hiring opportunities that companies like CVS, United Health, Oracle, and Citizens Bank are trying to exploit.

In conclusion, the COVID-19 pandemic has fundamentally changed the world, and these changes may not all be temporary. Many businesses rapidly understand the reality of conducting business online and maintaining a remote staff.

At the very least, the pandemic has heavily impacted office design to improve the health of staff and will continue to influence hiring decisions for years to come.

The post 4 Ways That COVID-19 Will Change Your Career Forever appeared first on Your Money Geek.

from Your Money Geek https://ift.tt/2B0LPqU via IFTTT

[siva3155]

300+ TOP ORACLE Database Interview Questions and Answers

ORACLE Database Interview Questions for freshers experienced :-

1. What Is Oracle? Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. 2. What Is an Oracle Database? An Oracle database is a collection of data treated as a big unit in the database server. 3. What Is an Oracle Instance? Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database. 4. What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files: * Server Parameter Files - Binary version. Persistent. * Initialization Parameter Files - Text version. Not persistent. 5. What Is a Server Parameter File in Oracle? A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. 6. What Is a Initialization Parameter File in Oracle? An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance. 7. What is System Global Area (SGA) in Oracle? The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup. 8. What is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started. 9. What Is a User Account in Oracle? A user account is identified by a user name and defines the user's attributes, including the following: Password for database authentication Privileges and roles Default tablespace for database objects Default temporary tablespace for query processing work space 10. What Is the Relation of a User Account and a Schema in Oracle? User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.

ORACLE Database Interview Questions 11. What Is a User Role in Oracle? A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users. 12. What is a Database Schema in Oracle? A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects. 13. What Is a Database Table in Oracle? A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record. 14. What Is a Table Index in Oracle? Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly. 15. What Is an Oracle Tablespace? An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes. 16. What Is an Oracle Data File? An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace. 17. What Is a Static Data Dictionary in Oracle? Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views: * An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles. * A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed. * A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views. 18. What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them. 19. What Is a Recycle Bin in Oracle? Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop. Recycle bin can be turned on or off in the recyclebin=on/off in your parameter file. 20. What Is SQL*Plus? SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface. 21. What Is Transport Network Substrate (TNS) in Oracle? TNS, Transport Network Substrate, is a foundation technology, built into the Oracle Net foundation layer that works with any standard network transport protocol. 22. What Is Open Database Communication (ODBC) in Oracle? ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management systems. Oracle offers ODBC drivers to allow Windows applications to connect Oracle server through ODBC. 23. What is Oracle Database 10g Express Edition? Based on Oracle Web site: Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for: Developers working on PHP, Java, .NET, and Open Source applications DBAs who need a free, starter database for training and deployment Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge Educational institutions and students who need a free database for their curriculum 24. What Are the Limitations Oracle Database 10g XE? Oracle Database XE is free for runtime usage with the following limitations: Supports up to 4GB of user data (in addition to Oracle system data) Single instance only of Oracle Database XE on any server May be installed on a multiple CPU server, but only executes on one processor in any server May be installed on a server with any amount of memory, but will only use up to 1GB RAM of available memory 25. What Operating Systems Are Supported by Oracle Database 10g XE? Oracle Database 10g Express Edition is available for two types of operating Systems: Linux x86 - Debian, Mandriva, Novell, Red Hat and Ubuntu Microsoft Windows 26. How To Download Oracle Database 10g XE? If you want to download a copy of Oracle Database 10g Express Edition, visit http://www.oracle.com/technology/software/products/database/xe/. If you are using Windows systems, there are downloads available for you: Oracle Database 10g Express Edition (Western European) - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only. Oracle Database 10g Express Edition (Universal) - Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish. Oracle Database 10g Express Client You need to download the universal edition, OracleXEUniv.exe, (216,933,372 bytes) and client package, OracleXEClient.exe (30,943,220 bytes). 27. How To Install Oracle Database 10g XE? To install 10g universal edition, double click, OracleXEUniv.exe, the install wizard starts. It will guide you to finish the installation process. You should take notes about: The SYSTEM password you selecte: atoztarget. Database server port: 1521. Database HTTP port: 8080. MS Transaction Server port: 2030. The directory where 10g XE is installed: oraclexe Hard disk space taken: 1655MB. 28. How To Check Your Oracle Database 10g XE Installation? If you want to check your fresh installation of 10g Express Edition without using any special client programs, you can use a Web browser with this address, http://localhost:8080/apex/. You will see the login page. Enter SYSTEM as the user name, and the password (atoztarget), you selected during the installation to log into the server. Visit different areas on your 10g XE server home page to make sure your server is running OK. You can also get to your 10g XE server home page by going through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Go To Database Home Page 29. How To Shutdown Your 10g XE Server? If you want to shutdown your 10g Express Edition server, go to the Services manager in the control panel. You will a service called OracleServiceXE, which represents your 10g Express Edition server. Select OracleServiceXE, and use the right mouse click to stop this service. This will shutdown your 10g Express Edition server. You can also shutdown your 10g XE server through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Stop Database. 30. How To Start Your 10g XE Server? Go to the Start menu, select All Programs, Oracle Database 10g Express Edition, and Start Database. 31. How Much Memory Your 10g XE Server Is Using? Your 10g XE Server is using about 180MB of memory even there is no users on the server. The server memory usage is displayed on your server home page, if you log in as SYSTEM. 32. How To Start Your 10g XE Server from Command Line? You can start your 10g XE server from command line by: Open a command line window. Change directory to oraclexeapporacleproduct10.2.0serverBIN. Run StartDB.bat. The batch file StartDB.bat contains: net start OracleXETNSListener net start OracleServiceXE @oradim -startup -sid XE -starttype inst > nul 2>&1 33. How To Shutdown Your 10g XE Server from Command Line? You can shutdown your 10g XE server from command line by: Open a command line window. Change directory to oraclexeapporacleproduct10.2.0serverBIN. Run StopDB.bat. The batch file StopDB.bat contains: net stop OracleServiceXE 34. How To Unlock the Sample User Account in Oracle? Your 10g XE server comes with a sample database user account called HR. But this account is locked. You must unlock it before you can use it: Log into the server home page as SYSTEM. Click the Administration icon, and then click Database Users. Click the HR schema icon to display the user information for HR. Enter a new password (hr) for HR, and change the status to Unlocked. Click Alter User to save the changes. Now user account HR is ready to use. 35. How To Change System Global Area (SGA) in Oracle? Your 10g XE server has a default setting for System Global Area (SGA) of 140MB. The SGA size can be changed to a new value depending on how many concurrent sessions connecting to your server. If you are running this server just for yourself to improve your DBA skill, you should change the SGA size to 32MB by: Log into the server home page as SYSTEM. Go to Administration, then Memory. Click Configure SGA. Enter the new memory size: 32 Click Apply Changes to save the changes. Re-start your server. 36. How To Change Program Global Area (PGA) in Oracle? Your 10g XE server has a default setting for Program Global Area (PGA) of 40MB. The PGA size can be changed to a new value depending on how much data a single session should be allocated. If you think your session will be short with a small amount of data, you should change the PGA size to 16MB by: Log into the server home page as SYSTEM. Go to Administration, then Memory. Click Configure PGA. Enter the new memory size: 16 Click Apply Changes to save the changes. Re-start your server. 37. What Happens If You Set the SGA Too Low in Oracle? Let's you made a mistake and changed to SGA to 16MB from the SYSTEM admin home page. When you run the batch file StartDB.bat, it will return a message saying server stated. However, if you try to connect to your server home page: http://localhost:8080/apex/, you will get no response. Why? Your server is running, but the default instance XE was not started. If you go the Control Panel and Services, you will see service OracleServiceXE is listed not in the running status. 38. What To Do If the StartBD.bat Failed to Start the XE Instance? If StartBD.bat failed to start the XE instance, you need to try to start the instance with other approaches to get detail error messages on why the instance can not be started. One good approach to start the default instance is to use SQL*Plus. Here is how to use SQL*Plus to start the default instance in a command window: >cd (OracleXE home directory) >.binstartdb >.binsqlplus Enter user-name: SYSTEM Enter password: atoztarget ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist The first "cd" is to move the current directory the 10g XE home directory. The second command ".binstartdb" is to make sure the TNS listener is running. The third command ".binsqlplus" launches SQL*Plus. The error message "ORA-27101" tells you that there is a memory problem with the default instance. So you can not use the normal login process to the server without a good instance. See other tips on how to log into a server without any instance. 39. How To Login to the Server without an Instance? If your default instance is in trouble, and you can not use the normal login process to reach the server, you can use a special login to log into the server without any instance. Here is how to use SQL*Plus to log in as as a system BDA: >cd (OracleXE home directory) >.binstartdb >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance SQL> show instance instance "local" The trick is to put user name, password and login options in a single string as the user name. "AS SYSDBA" tells the server to not start any instance, and connect the session the idle instance. Log in as SYSDBA is very useful for performing DBA tasks. 40. How To Use "startup" Command to Start Default Instance? If you logged in to the server as a SYSDBA, you start the default instance with the "startup" command. Here is how to start the default instance in SQL*Plus in SYSDBA mode: >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance SQL> show instance instance "local" SQL> startup ORA-00821: Specified value of sga_target 16M is too small, needs to be at least 20M Now the server is telling you more details about the memory problem on your default instance: your SGA setting of 16MB is too small. It must be increased to at least 20MB. 41. Where Are the Settings Stored for Each Instance in Oracle? Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOMEdatabase directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name. 42. What To Do If the Binary SPFile Is Wrong for the Default Instance? Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting. One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance: >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance 43. How To Check the Server Version in Oracle? Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this: >.binsqlplus Enter user-name: SYSTEM/atoztarget AS SYSDBA Connected to an idle instance SQL> COL PRODUCT FORMAT A35 SQL> COL VERSION FORMAT A15 SQL> COL STATUS FORMAT A15 SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCT VERSION STATUS ----------------------------------- ----------- ---------- NLSRTL 10.2.0.1.0 Production Oracle Database 10g Express Edition 10.2.0.1.0 Product PL/SQL 10.2.0.1.0 Production TNS for 32-bit Windows: 10.2.0.1.0 Production 44. Explain What Is SQL*Plus? SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface. SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following: Format, perform calculations on, store, and print from query results Examine table and object definitions Develop and run batch scripts Perform database administration You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page. 45. How To Start the Command-Line SQL*Plus? f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways: 1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this: SQL*Plus: Release 10.2.0.1.0 - Production on Tue ... Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> 2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below: >cd c:oraclexeapporacleproduct10.2.0server >.binsqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue ... Copyright (c) 1982, 2005, Oracle. All rights reserved. 46. How To Get Help at the SQL Prompt? Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run. To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example: SQL> HELP INDEX Enter Help for help. @ COPY PAUSE SHUTDOWN @@ DEFINE PRINT SPOOL / DEL PROMPT SQLPLUS ACCEPT DESCRIBE QUIT START APPEND DISCONNECT RECOVER STARTUP ARCHIVE LOG EDIT REMARK STORE ATTRIBUTE EXECUTE REPFOOTER TIMING BREAK EXIT REPHEADER TTITLE ... COMPUTE LIST SET XQUERY CONNECT PASSWORD SHOW SQL> HELP CONNECT CONNECT ------ 47. What Information Is Needed to Connect SQL*Plus an Oracle Server? If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server: The network hostname, or IP address, of the Oracle server. The network port number where the Oracle server is listening for incoming connections. The name of the target database instance managed by the Oracle server. The name of your user account predefined on in the target database instance. The password of your user account predefined on in the target database instance. 48. What Is a Connect Identifier? A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server. Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition: ggl_XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = www.atoztarget.com) (PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) The above "connect identifier" defines "TNS_XE" with the following connection information: * The network hostname: www.atoztarget.com. * The network port number: 1521. * The name of the target database instance: XE. 49. How To Connect a SQL*Plus Session to an Oracle Server? In order to connect a SQL*Plus session to an Oracle server, you need to: 1. Obtain the connection information from the Oracle server DBA. 2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information. 3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below: >cd c:oraclexeapporacleproduct10.2.0server >.binsqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue ... Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> CONNECT ggl/retneclgg@ggl_XE; Connected. SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------- 05-MAR-06 50. What Happens If You Use a Wrong Connect Identifier? Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier: SQL> CONNECT ggl/retneclgg@WRONG; ERROR: ORA-12154: TNS:could not resolve the connect identifier specified Warning: You are no longer connected to ORACLE. What you need to do in this case: Check the CONNECT command to make sure that the connect identifier is entered correctly. Check the tnsnames.ora file to make sure that the connect identifier is defined correctly. Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier. Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora. 51. What To Do If DBA Lost the SYSTEM Password? If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how: (Terminal server to the Oracle server machine) (Start SQL*Plus) SQL>CONNECT / AS SYSDBA Connected. SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg; User altered. Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method. 52. What Types of Commands Can Be Executed in SQL*Plus? There are 4 types of commands you can run at the SQL*Plus command line prompt: 1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command. 2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.atoztarget.com')" runs a PL/SQL command. SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command. OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine. 53. How To Run SQL Commands in SQL*Plus? If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example: SQL> SELECT 'Welcome!' FROM DUAL; 'WELCOME -------- Welcome! SQL> SELECT 'Welcome to atoztarget.com tutorials!' 2 FROM DUAL 3 ; 'WELCOMETOatoztarget.COMTUTORIALS!' ----------------------------------- Welcome to atoztarget.com tutorials! 54. How To Run PL/SQL Statements in SQL*Plus? If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example: SQL> SET SERVEROUTPUT ON SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to atoztarget!') Welcome to atoztarget! PL/SQL procedure successfully completed. 55. How To Change SQL*Plus System Settings? SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list: * SET AUTOCOMMIT OFF - Turns off the auto-commit feature. * SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output. * SET HEADING OFF - Stops displaying the header line of the query output. * SET LINESIZE 256 - Sets the number of characters per line when displaying the query output. * SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output. * SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output. * SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output. * SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output. * SET TIMING ON - Asks SQL*Plus to display the command execution timing data. * SET WRAP OFF - Turns off the wrapping feature when displaying query output. 56. How To Look at the Current SQL*Plus System Settings? If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise: SQL> SHOW AUTOCOMMIT autocommit OFF SQL> SHOW HEADING heading ON SQL> SHOW LINESIZE linesize 80 SQL> SHOW PAGESIZE pagesize 14 SQL> SHOW FEEDBACK FEEDBACK ON for 6 or more rows SQL> SHOW TIMING timing OFF SQL> SHOW NULL null "" SQL> SHOW ALL appinfo is OFF and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) cmdsep OFF colsep " " compatibility version NATIVE concat "." (hex 2e) copycommit 0 COPYTYPECHECK is ON define "&" (hex 26) describe DEPTH 1 LINENUM OFF INDENT ON echo OFF 57. What Are SQL*Plus Environment Variables? Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables: * ORACLE_HOME - The home directory where your Oracle client application is installed. * PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOMEbin. * SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOMEsqlplusmesg * TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin. 58. How To Generate Query Output in HTML Format? If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example: SQL> connect HR/retneclgg SQL> SET MARKUP HTML ON SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2 FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'; FIRST_NAME LAST_NAME HIRE_DATE John Seo 12-FEB-98 John Russell 01-OCT-96 59. What Is Output Spooling in SQL*Plus? The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file. Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus: * SPOOL fileName - Turning on output spooling with the specified file. * SPOOL OFF - Turning off output spooling and close the spool file. 60. How To Save Query Output to a Local File? Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example: SQL> connect HR/retneclgg SQL> SET HEADING OFF SQL> SET FEEDBACK OFF SQL> SET LINESIZE 1000 SQL> SPOOL tempemployees.lst SQL> SELECT * FROM EMPLOYEES; ...... SQL> SPOOL OFF You should get all records in employees.lst with fixed length fields. 61. What Is Input Buffer in SQL*Plus? Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands: * LIST - Displays the SQL statement (the last executed SQL statement) in the buffer. * RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN. * CLEAR BUFFER - Removes the SQL statement in the buffer. * INPUT line - Adds a new line into the buffer. * APPEND text - Appends more text to the last line in the buffer. * DEL - Deletes one line from the buffer. * CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer. 62. How To Revise and Re-Run the Last SQL Command? If executed a long SQL statement, found a mistake in the statement, and you don't want enter that long statement again, you can use the input buffer commands to the correct last statement and re-run it. The following tutorial exercise gives you a good example: SQL> connect HR/retneclgg SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'; FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%' * ERROR at line 2: ORA-00942: table or view does not exist SQL> LIST 1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%' SQL> CHANGE /EMPLOYEE/EMPLOYEES/ 2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%' SQL> RUN (Query output) SQL> INPUT ORDER BY FIRE_DATE SQL> LIST 1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE 2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%' 3* ORDER BY HIRE_DATE SQL> RUN (Query output) SQL> CLEAR BUFFER buffer cleared SQL> LIST SP2-0223: No lines in SQL buffer. 63. How Run SQL*Plus Commands That Are Stored in a Local File? If you have a group of commands that you need to run them repeatedly every day, you can save those commands in a file (called SQL script file), and using the "@fileName" command to run them in SQL*Plus. If you want to try this, create a file called tempinput.sql with: SELECT 'Welcome to' FROM DUAL; SELECT 'atoztarget.com!' FROM DUAL; Then run the "@" command in SQL*Plus as: SQL> connect HR/retneclgg SQL> @tempinput.sql 'WELCOMETO ---------- Welcome to 'atoztarget.COM -------------- atoztarget.com! 64. How To Use SQL*Plus Built-in Timers? If you don't have a stopwatch/timer and want to measure elapsed periods of time, you can SQL*Plus Built-in Timers with the following commands: * TIMING - Displays number of timers. * TIMING START - Starts a new timer with or without a name. * TIMING SHOW - Shows the current time of the named or not-named timer. * TIMING STOP - Stops the named or not-named timer. The following tutorial exercise shows you a good example of using SQL*Plus built-in timers: SQL> TIMING START timer_1 (some seconds later) SQL> TIMING START timer_2 (some seconds later) SQL> TIMING START timer_3 (some seconds later) SQL> TIMING SHOW timer_1 timing for: timer_2 Elapsed: 00:00:19.43 (some seconds later) SQL> TIMING STOP timer_2 timing for: timer_2 Elapsed: 00:00:36.32 SQL> TIMING 2 timing elements in use 65. What Is Oracle Server Autotrace in Oracle? Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning: * Statement execution path - Shows you the execution loop logic of a DML statement. * Statement execution statistics - Shows you various execution statistics of a DML statement. To turn on the autotrace feature, the Oracle server DBA need to: * Create a special table called PLAN_TABLE. * Create a special security role called PLUSTRACE. * Grant PLUSTRACE role your user account. 66. How To Set Up Autotrace for a User Account? If an Oracle user wants to use the autotrace feature, you can use the tutorial as an example to create the required table PLAN_TABLE, the required security role PLUSTRACE, and grant the role to that user: SQL> CONNECT HR/retneclgg SQL> @oraclexeapporacleproduct10.2.0server RDBMSADMINUTLXPLAN.SQL Table (HR.PLAN_TABLE) created. SQL> CONNECT / AS SYSDBA SQL> @C:oraclexeapporacleproduct10.2.0server SQLPLUSADMINPLUSTRCE.SQL SQL> drop role plustrace; Role (PLUSTRACE) dropped. SQL> create role plustrace; Role (PLUSTRACE) created. SQL> grant plustrace to dba with admin option; Grant succeeded. SQL> GRANT PLUSTRACE TO HR; Grant succeeded. Remember that PLAN_TABLE table must be created under the user schema HR. 67. How To Get Execution Path Reports on Query Statements? If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial exercise bellow shows you a good example: SQL> CONNECT HR/retneclgg SQL> SET AUTOTRACE ON EXPLAIN SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE 2 FROM EMPLOYEES E, JOBS J 3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000; LAST_NAME SALARY JOB_TITLE ----------------- ---------- ----------------------------- King 24000 President Kochhar 17000 Administration Vice President De Haan 17000 Administration Vice President Russell 14000 Sales Manager Partners 13500 Sales Manager Hartstein 13000 Marketing Manager 6 rows selected. 68. How To Get Execution Statistics Reports on Query Statements? If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example: SQL> CONNECT HR/retneclgg SQL> SET AUTOTRACE ON STATISTICS SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE 2 FROM EMPLOYEES E, JOBS J 3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000; LAST_NAME SALARY JOB_TITLE ----------------- ---------- ----------------------------- King 24000 President Kochhar 17000 Administration Vice President De Haan 17000 Administration Vice President Russell 14000 Sales Manager Partners 13500 Sales Manager Hartstein 13000 Marketing Manager 6 rows selected. 69. What Is SQL in Oracle? SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation. 70. How Many Categories of Data Types in Oracle? Oracles supports the following categories of data types: * Oracle Built-in Datatypes. * ANSI, DB2, and SQL/DS Datatypes. * User-Defined Types. * Oracle-Supplied Types. 71. What Are the Oracle Built-in Data Types? There are 20 Oracle built-in data types, divided into 6 groups: * Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2 * Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE * Long and Row Datatypes - LONG, LONG RAW, RAW * Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND * Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE * Row ID Datatypes - ROWID, UROWID 72. What Are the Differences between CHAR and NCHAR in Oracle? Both CHAR and NCHAR are fixed length character data types. But they have the following differences: * CHAR's size is specified in bytes by default. * NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used. * NCHAR stores characters in Unicode. 73. What Are the Differences between CHAR and VARCHAR2 in Oracle? The main differences between CHAR and VARCHAR2 are: * CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length. * VARCHAR2 stores values in variable lengths. Values are not padded with any characters. 74. What Are the Differences between NUMBER and BINARY_FLOAT in Oracle? The main differences between NUMBER and BINARY_FLOAT in Oracle are: * NUMBER stores values as fixed-point numbers using 1 to 22 bytes. * BINARY_FLOAT stores values as single precision floating-point numbers. 75. What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and TIMESTAMP in Oracle are: * DATE stores values as century, year, month, date, hour, minute, and second. * TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds. 76. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND? The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are: * INTERVAL YEAR TO MONTH stores values as time intervals at the month level. * INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level. 77. What Are the Differences between BLOB and CLOB in Oracle? The main differences between BLOB and CLOB in Oracle are: * BLOB stores values as LOB (Large OBject) in bitstreams. * CLOB stores values as LOB (Large OBject) in character steams. 78. What Are the ANSI Data Types Supported in Oracle? The following ANSI data types are supported in Oracle: * CHARACTER(n) / CHAR(n) * CHARACTER VARYING(n) / CHAR VARYING(n) * NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n) * NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n) * NUMERIC(p,s) * DECIMAL(p,s) * INTEGER / INT * SMALLINT * FLOAT * DOUBLE PRECISION * REAL 79. How To Write Text Literals in Oracle? There are several ways to write text literals as shown in the following samples: SELECT 'atoztarget.com' FROM DUAL -- The most common format atoztarget.com SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped It's Sunday! SELECT N'Allo, C''est moi.' FROM DUAL -- National chars Allo, C'est moi. SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter It's Sunday! 80. How To Write Numeric Literals in Oracle? Numeric literals can coded as shown in the following samples: SELECT 255 FROM DUAL -- An integer 255 SELECT -6.34 FROM DUAL -- A regular number -6.34 SELECT 2.14F FROM DUAL -- A single-precision floating point 2.14 SELECT -0.5D FROM DUAL -- A double-precision floating point -0.5 81. How To Write Date and Time Literals in Oracle? Date and time literals can coded as shown in the following samples: SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format 03-OCT-07 SELECT TIMESTAMP '0227-01-31 09:26:50.124' FROM DUAL 31-JAN-07 09.26.50.124000000 AM -- This is ANSI format 82. How To Write Date and Time Interval Literals in Oracle? Date and time interval literals can coded as shown in the following samples: SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH FROM DUAL -- 123 years and 2 months is added to 2002-10-03 03-DEC-25 SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL -- 123 years is added to 2002-10-03 03-OCT-25 SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL -- 299 months years is added to 2002-10-03 03-SEP-27 SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL 04-FEB-97 02.39.00.346000000 PM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL 04-FEB-97 02.38.50.124000000 PM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL 07-MAR-98 02.26.50.124000000 PM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '400' DAY(3) FROM DUAL 07-MAR-98 09.26.50.124000000 AM SELECT TIMESTAMP '1997-01-31 09:26:50.124' + INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL 31-JAN-97 08.39.00.346222200 PM 83. How To Convert Numbers to Characters in Oracle? You can convert numeric values to characters by using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(4123.4570) FROM DUAL 123.457 SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL $4,123.46 SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL -4.12E+03 84. How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the TO_NUMBER() function as shown in the following examples: SELECT TO_NUMBER('4123.4570') FROM DUAL 4123.457 SELECT TO_NUMBER(' $4,123.46','$9,999,999.99') FROM DUAL 4123.46 SELECT TO_NUMBER(' -4.12E+03') FROM DUAL -4120 85. How To Convert Dates to Characters in Oracle? You can convert dates to characters using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL; -- SYSDATE returns the current date 07-MAY-2006 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL; 2006/05/07 SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL; MAY 07, 2006 SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL; May 7, 2006 SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL; SUNDAY, MAY 7, 2006 86. How To Convert Characters to Dates in Oracle? You can convert dates to characters using the TO_DATE() function as shown in the following examples: SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL; 07-MAY-06 SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL; 07-MAY-06 SELECT TO_DATE('MAY 07, 2006', 'MONTH DD, YYYY') FROM DUAL; 07-MAY-06 SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL; 07-MAY-06 SELECT TO_DATE('SUNDAY, MAY 7, 2006', 'fmDAY, MONTH DD, YYYY') FROM DUAL; 07-MAY-06 87. How To Convert Times to Characters in Oracle? You can convert dates to characters using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; 04:49:49 SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL; -- Error: SYSDATE has no fractional seconds SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL; 16:52:57.847000000 SELECT TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL; -- Seconds past midnight 69520 88. How To Convert Characters to Times in Oracle? You can convert dates to characters using the TO_CHAR() function as shown in the following examples: SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') FROM DUAL; -- Default date is the first day of the current month 01-MAY-2006 04:49:49 SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000', 'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9') FROM DUAL; 01-MAY-2006 16:52:57.847000000 SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'), 'DD-MON-YYYY HH24:MI:SS') FROM DUAL; 01-MAY-2006 19:18:40 89. What Is NULL value in Oracle? NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most operations has special rules when NULL is involved. The tutorial exercise below shows you some examples: SET NULL 'NULL'; -- Make sure NULL is displayed SELECT NULL FROM DUAL; N - N U L L SELECT NULL + NULL FROM DUAL; NULL+NULL ---------- NULL SELECT NULL + 7 FROM DUAL; NULL+7 ---------- NULL SELECT NULL * 7 FROM DUAL; NULL*7 ---------- NULL SELECT NULL || 'A' FROM DUAL; N - A SELECT NULL + SYSDATE FROM DUAL; NULL+SYSD --------- NULL 90. How To Use NULL as Conditions in Oracle? If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "" against NULL. The sample script below shows you some good examples: SELECT 'A' IS NULL FROM DUAL; -- Error: Boolean is not data type. -- Boolean can only be used as conditions SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; FALSE SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; FALSE SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Do not use "=" FALSE SELECT CASE WHEN 'A' NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Do not use "" FALSE SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Do not use "=" FALSE 91. How To Concatenate Two Text Values in Oracle? There are two ways to concatenate two text values together: * CONCAT() function. * '||' operation. Here is some examples on how to use them: SELECT 'ggl' || 'Center' || '.com' FROM DUAL; atoztarget.com SELECT CONCAT('atoztarget','.com') FROM DUAL; atoztarget.com 92. How To Increment Dates by 1 in Oracle? If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences: SELECT TO_DATE('30-APR-06') + 1 FROM DUAL; -- Adding 1 day to a date 01-MAY-06 SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06') FROM DUAL; -- Taking date differences 1 SELECT SYSTIMESTAMP + 1 FROM DUAL; -- The number you add is always in days. 08-MAY-06 SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3') FROM DUAL; -- Error: Adding 1 to a timestamp makes it a date. 93. How To Calculate Date and Time Differences in Oracle? If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples: SELECT (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS') - TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS')) YEAR(4) TO MONTH FROM DUAL; -- 109 years and 3 months 109-3 SELECT (TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS') - TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS')) DAY(9) TO SECOND FROM DUAL; -- 39901 days and some seconds 39901 7:26:7.0 SELECT (TO_TIMESTAMP('01-MAY-2006 16:52:57.847', 'DD-MON-YYYY HH24:MI:SS.FF3') - TO_TIMESTAMP('31-JAN-1897 09:26:50.124', 'DD-MON-YYYY HH24:MI:SS.FF3')) YEAR(4) TO MONTH FROM DUAL; -- 109 years and 3 months 109-3 SELECT (TO_TIMESTAMP('01-MAY-2006 16:52:57.847', 'DD-MON-YYYY HH24:MI:SS.FF3') - TO_TIMESTAMP('31-JAN-1897 09:26:50.124','DD-MON-YYYY HH24:MI:SS.FF3')) DAY(9) TO SECOND FROM DUAL; -- 39 94. How To Use IN Conditions in Oracle? An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are given in the script below: SELECT CASE WHEN 3 IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; FALSE SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE 95. How To Use LIKE Conditions in Oracle? LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition: * '_' is used in the pattern to match any one character. * '%' is used in the pattern to match any zero or more characters. * ESCAPE clause is used to provide the escape character in the pattern. The following script provides you some good pattern matching examples: SELECT CASE WHEN 'atoztarget.com' LIKE '%Center%' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN 'atoztarget.com' LIKE '%CENTER%' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; -- Case sensitive by default FALSE SELECT CASE WHEN 'atoztarget.com' LIKE '%Center_com' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN '100% correct' LIKE '100% %' ESCAPE '' THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE 96. How To Use Regular Expression in Pattern Match Conditions in Oracle? If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE(). The following script provides you some good examples: SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '.*ggl.*','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '.*com$','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE SELECT CASE WHEN REGEXP_LIKE ('atoztarget.com', '^F.*','i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE 97. What Are DDL Statements in Oracle? DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements: CREATE - Creating a new database object. ALTER - Altering the definition of an existing data object. DROP - Dropping an existing data object. 98. How To Create a New Table in Oracle? If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table: >.binsqlplus /nolog SQL> connect HR/atoztarget Connected. SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY, subject VARCHAR(80) NOT NULL, description VARCHAR(256) NOT NULL, create_date DATE DEFAULT (sysdate)); Table created. This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR". 99. How To Create a New Table by Selecting Rows from Another Table? Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script: >.binsqlplus /nolog SQL> connect HR/atoztarget Connected. SQL> CREATE TABLE emp_dept_10 2 AS SELECT * FROM employees WHERE department_id=10; Table created. SQL> SELECT first_name, last_name, salary 2 FROM emp_dept_10; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Jennifer Whalen 4400 As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department. This is really a quick and easy way to create a table. 100. How To Add a New Column to an Existing Table in Oracle? If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script: SQL> connect HR/atoztarget Connected. SQL> CREATE TABLE emp_dept_110 2 AS SELECT * FROM employees WHERE department_id=110; Table created. SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER); Table altered. SQL> SELECT first_name, last_name, vacation 2 FROM emp_dept_110; FIRST_NAME LAST_NAME VACATION -------------------- ------------------------- ---------- Shelley Higgins William Gietz This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows. ORACLE Database Questions and Answers Pdf Download Read the full article

[techgoeasy]

How to check temp tablespace in Oracle

How to check temp tablespace in Oracle

Temporary tablespace are used for sorting operation in Oracle.It is also used in join operation. Basically it is a temporary storage and temporary objects are created here. Global temporary tables are also created in this tablespace. This is quite significant tablespace and can pose issues if not managed correctly. Lets see various queries for Management of Temporary tablespace

How to check temp…

[ocptechnology]

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

ORA-14452: attempt to create, alter or drop an index on temporary table already in use #oracle #oracledba #oracledatabase

Sometimes, we face ORA-14452 during drop or alter. SO, in this article, we are going to learn how to solve “ORA-14452: attempt to create” this error. We face this issue if the table is currently used in any other session, and we trying to drop or alter the table. Find Session Details Let’s find in which session the table is in used, using the below query: SQL> set lin 300 pagesize 200 SQL>…

[globalmediacampaign]

MySQL InnoDB Cluster Disaster Recovery contingency via a Group Replication Replica

Just recently, I have been asked to look into what a Disaster Recovery site for InnoDB Cluster would look like. If you’re reading this, then I assume you’re familiar with what MySQL InnoDB Cluster is, and how it is configured, components, etc. Reminder: InnoDB Cluster (Group Replication, Shell & Router) in version 8.0 has had serious improvements from 5.7. Please try it out. So, given that, and given that we want to consider how best to fulfill the need, i.e. create a DR site for our InnoDB Cluster, let’s get started. Basically I’ll be looking at the following scenario: InnoDB Cluster Source site with a Group Replication Disaster Recovery Site.Now, just before we get into the nitty-gritty, here’s the scope. Life is already hard enough, so we want as much automated as possible, so, yes, InnoDB Cluster gets some of that done, but there are other parts we will still have to assume control over, so here’s the idea: Source Site:– InnoDB Cluster x3– Router– Accesses Source (Dynamic) & DR (Static)– Full Automation DR Site:– Group Replication x2– Router– Accesses Source (Dynamic) & DR (Static)– Configured Asynchronous Replication from Primary node. External / LBR 3rd Site:– MySQL Router (static) routing connections to Source Router– Allowing for 2 instance automatic failovers at Source site.– Manually reroute connections to DR site once Source Site outage confirmed or use different Router port.– Redundancy recommended to reduce SPOF. Let’s get it all started then. First things first, if you’re not quite sure what you’re doing, then I highly suggest looking at the following: https://thesubtlepath.com/mysql/innodb-cluster-managing-async-integration/ https://scriptingmysql.wordpress.com/2019/03/29/replicating-data-between-two-mysql-group-replication-sets-using-regular-asynchronous-replication-with-global-transaction-identifiers-gtids (And thanks to Andrew & Tony for their continuous help!) Let’s set up our Source Site 3x CentOS 7 servers. 8.0.20: Server, Shell & Router. :: All servers have had their SELinux, firewalls, ports, /etc/hosts and so on checked and validated, haven’t they? We will want to be using a private IP between all hosts, for obvious reasons.. I downloaded all the MySQL Enterprise rpm’s from https://edelivery.oracle.com and run the following on all 3 servers (getting mystic here: “centos01”, “centos02” & “centos03”): sudo yum install -y mysql-*8.0.20*rpm sudo systemctl start mysqld.servicesudo systemctl enable mysqld.service sudo grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1mysql -uroot -p Once we’re in, we want to control what command get into the binlogs, and the ”alter user’ and create user’ will cause us issues later on, hence “sql_log_bin=OFF” here: SET sql_log_bin = OFF; alter user 'root'@'localhost' identified by 'passwd'; create user 'ic'@'%' identified by 'passwd'; grant all on . to 'ic'@'%' with grant option; flush privileges; SET sql_log_bin = ON; This “ic@%” user will be used throughout, as we just don’t know when and which instance will end up being the source primary and/or a clone so by using this single user setup, I keep things easier for myself. For your production setup, please look deeper into specific privs ‘n’ perms. mysqlsh --uri root@localhost:3306 dba.checkInstanceConfiguration('ic@centos01:3306') dba.configureInstance('ic@centos01:3306'); Say “Y” to all the restarts n changes. On just one of the servers (this will be our Single Primary instance): connect ic@centos01:3306 cluster=dba.createCluster("mycluster") cluster.status() cluster.addInstance("ic@centos02:3306") cluster.addInstance("ic@centos03:3306") cluster.status(); Now, you will see that, although we have installed the binaries and started up the instances on centos02 & centos03, “addInstance” goes and clones our primary. This makes life soooo much easier. HINT: Before cloning, if you need to provision data, do it prior and we kill a couple of proverbial birdies here. Now we have a 3 instance Single Primary InnoDB Cluster. Setting up the local “InnoDB Cluster aware” Router: mkdir -p /opt/mysql/myrouter chown -R mysql:mysql /opt/mysql/myrouter cd /opt/mysql mysqlrouter --bootstrap ic@centos02:3306 -d /opt/mysql/myrouter -u mysql ./myrouter/start.sh Now Router is up and running. Let’s set up our Group Replication Disaster Recovery site now. Here, I’m doing a naughty, and just setting up a 2 node Group Replication group. If you want to see how you should be doing it, then let me reference Tony again: https://scriptingmysql.wordpress.com/2019/03/28/mysql-8-0-group-replication-three-server-installation/ :: Again, ports, firewalls, SELinux, /etc/hosts n similar have been validated again? Please? The environment: 2x Oracle Linux 7 servers. Version 8.0.20: MySQL Server, Router & Shell Do the following on both replica servers (olrep01 & olrep02): sudo yum install -y mysql-8.0.20rpmsudo systemctl start mysqld.servicesudo systemctl enable mysqld.servicesudo grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1mysql -uroot -p SET sql_log_bin = OFF; alter user 'root'@'localhost' identified by 'passwd'; create user 'ic'@'%' identified by 'passwd'; grant all on . to 'ic'@'%' with grant option; flush privileges; SET sql_log_bin = ON; No we have basic servers created, we need to clone the data from the Primary instance in our InnoDB Cluster. This means installing the mysql_clone plugin. On cluster Primary node: SET sql_log_bin = OFF;INSTALL PLUGIN CLONE SONAME "mysql_clone.so";GRANT BACKUP_ADMIN ON . to 'ic'@'%';GRANT SELECT ON performance_schema.* TO 'ic'@'%';GRANT EXECUTE ON . to 'ic'@'%';SET sql_log_bin = ON; Now on each replica: INSTALL PLUGIN CLONE SONAME "mysql_clone.so"; INSTALL PLUGIN group_replication SONAME 'group_replication.so'; SET GLOBAL clone_valid_donor_list = 'centos02:3306'; GRANT CLONE_ADMIN ON . to 'ic'@'%'; # Keep this, if we ever want to clone from any of the replicas GRANT BACKUP_ADMIN ON . to 'ic'@'%'; GRANT SELECT ON performance_schema.* TO 'ic'@'%'; GRANT EXECUTE ON . to 'ic'@'%'; Now to execute the clone operation: set global log_error_verbosity=3; CLONE INSTANCE FROM 'ic'@'centos02':3306 IDENTIFIED BY 'passwd'; View how the clone process is going: select STATE, ERROR_NO, BINLOG_FILE, BINLOG_POSITION, GTID_EXECUTED,CAST(BEGIN_TIME AS DATETIME) as "START TIME",CAST(END_TIME AS DATETIME) as "FINISH TIME",sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))) as DURATIONfrom performance_schema.clone_status G As we’re cloning, we might run into the duplicate UUID issue, so, on both of the replicas, force the server to have a new UUID: rm /var/lib/mysql/auto.cnfsystemctl restart mysqld Setting up the Group Replication config In node 1: vi /etc/my.cnf # GR setup server-id =11 log-bin =mysql-bin gtid-mode =ON enforce-gtid-consistency =TRUE log_slave_updates =ON binlog_checksum =NONE master_info_repository =TABLE relay_log_info_repository =TABLE transaction_write_set_extraction=XXHASH64 plugin_load_add ="group_replication.so" group_replication = FORCE_PLUS_PERMANENT group_replication_bootstrap_group = OFF #group_replication_start_on_boot = ON group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E group_replication_local_address = '10.0.0.41:33061' # Adjust the following according to IP's and numbers of hosts in group: group_replication_group_seeds = '10.0.0.41:33061,10.0.0.42:33061' On 2nd node: server-id =22 log-bin =mysql-bin gtid-mode =ON enforce-gtid-consistency =TRUE log_slave_updates =ON binlog_checksum =NONE master_info_repository =TABLE relay_log_info_repository =TABLE transaction_write_set_extraction=XXHASH64 plugin_load_add ="group_replication.so" group_replication = FORCE_PLUS_PERMANENT group_replication_bootstrap_group = OFF #group_replication_start_on_boot = ON group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E group_replication_local_address = '10.0.0.42:33061' # Adjust the following according to IP's and numbers of hosts in group: group_replication_group_seeds = '10.0.0.41:33061,10.0.0.42:33061' Restart both servers: systemctl restart mysqld Check they’re in a GR group and the plugin is ok: mysql -uroot SELECT * FROM performance_schema.replication_group_members; SELECT * FROM performance_schema.replication_group_membersG select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'G Now to create the recovery replication channel on all servers (although this is for single primary setup, the source could fail and then come back as a Read-Only replica, so we need to set this up): CHANGE MASTER TO MASTER_USER='ic', MASTER_PASSWORD='passwd' FOR CHANNEL 'group_replication_recovery'; On Server 1: SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF; On Server 2: START GROUP_REPLICATION; Check all the servers super_read_only mode and if they’re in a group: select @@super_read_only; SELECT * FROM performance_schema.replication_group_members; Set up Router on one of the replicas bootstrapping against the InnoDB Cluster source: mkdir -p /opt/mysql/myrouterchown -R mysql:mysql /opt/mysql/myroutermysqlrouter --bootstrap ic@centos02:3306 -d /opt/mysql/myrouter -u mysqlcd /opt/mysql/myrouter./start.sh Check connectivity: mysql -uic -P6446 -h olrep01 -N -e "select @@hostname, @@port" mysql -uic -P6446 -h centos02 -N -e "select @@hostname, @@port" Replication config for replicas Setting up replication for the replica from the primary means that we MUST be prepared to script the following for when the primary fails in the group replication setup, or keep the following command at hand, as it isn’t automatic so we’ll need to control this ourselves. Also, albeit a Group Replication group, when the primary fails n the DR site, we will also need to check all connections and sessions to the GR DR site to make sure we can set this up safely. So, from the PRIMARY instance only, execute the following: CHANGE MASTER TO MASTER_HOST = 'olrep01', MASTER_PORT = 6446, MASTER_USER = 'ic', MASTER_PASSWORD = 'passwd', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'idc_gr_replication' ; As you can see, this is replicating via the GR DR site Router that is bootstrapped against the InnoDB Cluster. Hence when the primary on the cluster moves, Router will take us to the new primary without having to re-create replication or similar. Now let’s start it all up: start slave ; And have a look at our InnoDB Cluster source site replicating data to our DR Group Replication site: show slave status for channel 'idc_gr_replication'G Application Connectivity Now we have a HA solution for our data, what about connecting to one and the other. We have 2 routers, each local to each data-saving site, fine. But, if we’re being realistic, we have 1 data entry point, the InnoDB Cluster Primary instance on centos01, and then 4 copies of the data, syncing nicely between a (dedicated? please?) local network to ensure we’re as safe as possible. As you’ll probably have noticed, upon bootstrapping Router on both sides, it chooses the routing_strategy=first_available on the default port 6446 which is fine. And yes, this port can be changed if we want to, so feel free to adjust as required. But, depending on where the application is, and the user entry point, you’ll have a VIP, floating IP or something similar halping to load balance maybe. But here is where we start to ask more questions: What happens when the Source site goes down? eg: Well, there are a number of things to keep in mind. The local Routers will take care of instance failures, and here, I feel obliged to forewarn you all about what happens when we loose 2 out of 3 instances on the source site. Here, the last instance could be configured to have group_replication_exit_state_action=’OFFLINE_MODE’ or ABORT for example. That way, it doesn’t get used as a valid node via Router. Now, once that has happened, i.e. the source site goes down, we need to manually intervene, and make sure that everything is really down, and kick into effect the DR site. Now both Routers on Source & DR site that were bootstrapped to the InnoDB Cluster will fail of course. We are left with the Group Replication DR site. So what do we do here? Well, create an additional Router, configured with a static (non-dynamic) setup, as we don’t have the metadata that the IdC-aware Router had via the mysql_innodb_cluster_metadata schema that gets stored in “dynamic_state=/opt/mysql/myrouter/data/state.json”. What I did was create a new Router process on one of the other servers (instead of centos02 & olrep01, on centos01 & olrep02). Now this isn’t something you’d do in Production as we want Router close to the app, not the MySQL instances. So in effect you could just use a new path ,eg. mkdir -p /opt/mysql/myrouter7001chown -R mysql:mysql /opt/mysql/myrouter7001cd /opt/mysql/myrouter7001 vi mysqlrouter.conf [routing:DR_rw] bind_address=0.0.0.0 bind_port=7001 destinations=olrep01:3306,olrep02:3306 routing_strategy=first-available protocol=classic Once configured, start them up: mysqlrouter --user=mysql --config /opt/mysql/myrouter7001/mysqlrouter.conf & test: mysql -uic -P7001 -h olrep02 -N -e "select @@hostname, @@port" mysql -uic -P7001 -h centos01 -N -e "select @@hostname, @@port" And this will give us back olrep01, then when that fails, olrep02. When the InnoDB Cluster-bootstrapped-Router that listens on 6446 fails, we know to redirect all requests to port 7001 on our production IP’s (in my case, olrep02 & centos01). Summing up, we’ve now got 4 routers running, 2 are InnoDB Cluster aware, listening on 6446, and the static GR DR Routers are listening on 7001. That’s a lot of ports and a long connect string. Maybe we could make this simpler? Ok, well I added yet another Router process, high level. This Router process, again, has a hard-coded static configuration, because we don’t have InnoDB Cluster-aware Routers on both sides, which means having a mysql_innodb_cluster_metadata schema on both sides, fully aware of both InnoDB Clusters, and also fully aware that metadata changes on the source are NOT to be replicated across. Who knows.. maybe in the future… So in my case I created another Router process configuration on centos03: mkdir -p /opt/mysql/myrouter chown -R mysql:mysql /opt/mysql/myrouter cd /opt/mysql/myrouter (You might want to use something different, but I did this. I could have all of these on the same server, using different paths. There isn’t any limit to how many Router processes we have running on the same server. It’s more a “port availability” or should I say “network security” concern) vi mysqlrouter.conf: [routing:Source_rw] bind_address=0.0.0.0 bind_port=8008 destinations=centos02:6446,olrep01:6446 routing_strategy=first-available protocol=classic [routing:DR_rw] bind_address=0.0.0.0 bind_port=8009 destinations=centos01:7001,olrep02:7001 routing_strategy=first-available protocol=classic Just in case I’ve lost you a little, what I’m doing here is providing a Router access point via centos03:8008 to the InnoDB-Cluster-aware Routers on centos02:6446 and olrep01:6446. And then an additional / backup access point to the static Routers on centos01: 7001 and olrep02:7001. The app would then have a connect string similar to “centos03:8008,centos03:8009”. Now, I know to send Source site connections to centos03:8008 always. And when I get errors and notifications from monitoring that this is giving problems, I can use my back up connection to centos03:8009. Side note: I originally setup the single Router process on each side, but with a mix of InnoDB Cluster aware entry and an additional static configuration. This didn’t work well as really, having bootstrapped against the IdC, the dynamic_state entry only allows for the members taken from the mysql_innodb_cluster_metadata schema. So it won’t work. Hence, the additional router processes. But if you think about it, making each Router more modular and dedicated, it makes for a more atomic solution. Albeit a little more to administer. However, maybe instead of this high-level Router process listening on 8008 & 8009, you want to reuse your keepalived solution. Have a look at Lefred’s “MySQL Router HA with Keepalived” solution for that. And there you have it! This was all done on Oracle Cloud Infrastructure compute instances, opening ports internally within the Security List and not much else. https://mysqlmed.wordpress.com/2020/06/11/mysql-innodb-cluster-disaster-recovery-contingency-via-a-group-replication-replica/

[sandeep2363]

Private Temporary Tables in Oracle 18c

Private Temporary Tables in Oracle 18c

Example of using Private Temporary Tables in Oracle 18c Oracle already has a Global temporary table, but a Private temporary table is placed in the memory area but a Global temporary table is in Disk. You can create a private temporary table with a default prefix “ORA$PTT_” which is defined in the parameter private_temp_table_prefix. Global temp table available in all sessions but Private temp…

[erpsoftwarsolution]

Twilight zone Transition period offers a temporary safe harbour

Brexit day will transform the relationship with Europe, shift the UK’s place in the world and herald a new era in international relations. But little to nothing willchangeimmediately. The post-Brexit transition period will pick up where EU membership left off, offering a temporary safe harbour while Brussels and London haggle over their long-termrelationship. During that 11-month spell, the UK will keep many of the benefits and obligations of membership: British nationals will no longer be EU citizens, but they will be able to travel around the union as freely as before; British members of the European Parliament will leave, but the UK will remain a fully integratedpartofthesinglemarket. The realities of Brexit will come sharply into focus as the clock ticks down to the end of this year, when the transition period will expire. In the meantime, migrant workers have an opportunity to navigate the application processesthatwillsafeguardtheirrights for life, and businesses have some time toadaptsupplychains. What happens on February 1? The UK will enter a twilight period during which it will continue to apply and be bound by all EU laws but will be ejected from the EU’s political institutions: no MEPs, no British seat at the EU leaders’ table, no UK voice on the boards of the union’s myriad technical agencies. Britain will have zero say over the EU rules that will still apply for the next 11 months.TheEuropeanCommissionwill have the power to investigate breaches of the bloc’s laws, and the European Court of Justice will have the power to impose fines. The UK will contribute to theEUbudget. UK nationals who have joined the European civil service will have the right to work in it for the rest of their careers. Britain’s diplomatic presence in Brussels, known as the UK Permanent Representation to the EU, will be rebranded and British officials’ access to EU premises and information will be sharplycurtailed. UsedtofreelyroamingintheEU’scorridors of power, British diplomats will now need to apply for permission even to enter the Brussels institutions’ bars andcafés. What does it mean for citizens? British and EU citizens will continue to benefit from free movement during the transitionperiod. After transition, Britons living in the 27 EU member states will have their residency rights safeguarded, subject to completing whatever administrative procedures are imposed by the national government. It is up to each EU country to decide how to carry out that exercise, including whether to create a new type of residence status for UK nationals. EU27 countries have to provide a digital residence document to those with the right toremain. The divorce deal does not grant British expats full freedom of movement rights within the union. It guarantees rights for UK citizens in the EU27 countrywheretheyreside. This means that a British national living in, for example, Portugal will not necessarily be able to move to Poland to take up a new job. But the European Parliament has urged that UK citizens living in the EU should be granted free movementrights. What about EU nationals living in the UK? The 3m EU citizens resident in the UK have until June 2021 to register for Britain’ssettledstatusscheme,whichallows them to stay in the UK with existing rights after Brexit. The scheme is open to people who have been living in the countryforatleastfiveyears. Those who have not been there long enough can apply for a pre-settled status, which grants the right to live and workinBritainforuptofiveyears.Tobe eligible, someone needs to arrive in the UK before the end of the transition period. The picture changes from the beginning of 2021, when Britain becomes a third country. From then, EU citizens coming to the UK may need visas and work permits if they plan to build a life here. Both Britain and the EU have said they want to put in place a visa-waiver schemeforshortstaysofupto90days. What are the implications for business? Free movement of goods will continue during transition. But Britain will no longer be represented in the crucial technical work the EU carries out to decide which products can be sold in its marketandunderwhatconditions. The European Chemicals Agency, for example, has the power to decide whether a new substance is safe for consumers, while the European Banking Authority sets rules that affect compliancecostsforfinancialservices. Brexit day also sets the clock ticking for business. Companies have no idea what awaits them after the end of this year, because the UK and EU have yet to negotiate a new trade deal. Whatever is finally agreed, there will be a hard border for trade in goods between Britain and continental Europe that has not existedfordecades. That puts pressure on business to adjust supply chains, relocate operationsandanalysecosts.

Scotland’s first minister has called on the UK government to help create a post-Brexit “Scottish visa” that would allow easier entry for immigrants willing to commit to living north of the English border. The call from Nicola Sturgeon yesterday reflects concern about the economic and social implications of the end to freedom of movement for EU citizens, with experts saying it could lead to a fall in the Scottish working age population over the next 25 years because of the slowingbirthrate. Politicians in Scotland have been much more positive about the benefits ofimmigrationthaninotherpartsofthe UK. Ms Sturgeon said she wanted a tailored approach for Scotland that would beas“openandflexibleaspossible”. A one-size-fits-all UK system that sought to cut overall immigration would be “pretty disastrous” for Scotland, the firstministersaid. “I hope the UK government will be prepared to work with us to deliver a Scottishvisa,”shesaid. In proposals published yesterday, the Scottish government said the UK could retain overall control of immigration and border controls, but devolve to Edinburgh the power to create a new visa process subject to Home Office identity and security checks. The visa would allow immigrants to live and workinScotland. The Home Office, which yesterday unveiled a “Global Talent” route into the UK for scientists and mathematicians, dismissed the suggestion that influenceovervisasmightbedevolved. “Immigration will remain a reserved matter,” a Home Office spokesman said. “The UK government will introduce a points-based immigration system that works in the interests of the whole of the UnitedKingdom,includingScotland.” UK officials are concerned that devolving or even merely tailoring policy for Scotland or other parts of the UK could massively complicate the immigrationsystem. However, the Scottish government says international experience and the past success of the Scotland-only poststudy work visa show that its proposals arepractical. An independent study last year concluded that Canada’s immigration system showed how policy could be tailored to help Scotland head off a looming crisis over the balance between pensionersandworking-ageScots. Andrew McRae, policy chair of the Federation of Small Businesses in Scotland, said: “The UK government should acknowledge that it is possible and desirable to enable its immigration system to respond to different regions and nations.”

Read Also:

Microsoft ERP Software

Microsoft ERP Provider

Microsoft 365 ERP System

Microsoft 365 ERP Software

Microsoft 365 ERP Provider

Oracle ERP System

Oracle ERP Software

Oracle Cloud ERP System

Oracle EBS finance

Oracle cloud financials

Oracle Fusion Financial Cloud

Oracle ERP Providers

Oracle ERP Partner

ERP Software

ERP  System

Best ERP Provider

Best ERP Software

Best ERP Service

Best ERP Solution

ERP Solution

[stokedevwebsite]

DBA Interview Questions with Answers Part19

Why we look for CHUNKS_FREE space while tracking fragmentation details query? The CHUNK_FREE return the number of chunks of contiguous free space based on dba_free_space table. The motive is to find the largest size chunks of free space within a tableapce. This is because as we know oracle server allocates space for segments in unit of one extent. When the existing extent of segment is full, the server allocates another extent for the segment. In order to do oracle searches free space in the tablespace (contiguous set of data block sufficient to meet the required extent). If sufficient space not found then an error is returned by the oracle server.What is the impact of NLS/Characterset in database?NLS is a National language support and encompasses how to display currency, whenever we use a comma or a dot to separate numbers, how the name of the day is spelled etc.Charactersets are how we store data.  For Example: US7ASCII is a 7bit characterset and WE8ISO8859P18 bit character set. It can store 2 times as many characters as the 7bit characterset. If you try to export from 8 bit characterset database and import into 7bit database then there is chance to loose data in 7bit characterset that have the high bit set and if you try from 7bit to 8bit would not encounter any issues since the 7bit characterset is a subset of the 8bit characterset and can hold more types of characters and can support many countries.Can we perform RMAN level 1 backup without level 0?If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version). If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup. If compatibility is greater than 10.0.0 RMAN copies all block changes since the file was created, and stores the results as level 1 backup.What will happen if ARCHIVE process cannot copy an archive redolog to a mandatory archive log destination?Oracle will continue with cycle to the other online redolog groups until it return to the group that the ARCH process is trying to copy to the mandatory archive log destination. If the mandatory archive log destination copy has not occurred, the database operation will suspend until the copy is successful or the DBA has intervened to perform force log switching.Can you differentiate between HOTBACKUP and RMAN backup?For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. In fact RMAN has a number of advantages over general backup. For more information please check: Benefit of RMAN BackupHow to put Manual/User managed backup in RMAN?In case of recovery catalog, you can put by using catalog command:RMAN> CATALOG START WITH ‘/oraback/backup.ctl’;When you put any SQL statement how oracle responds them internally?First it will check the syntax and semantics in library cache, after that it will created execution plan. If already data in buffer cache (in case of identical query) it will directly return to the client. If not it write the fetch to the database buffer cache after that it will send server and finally server send to the client.Can we use Same target database as Catalog?No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.Differentiate the use of what are PGA and UGA?When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).How do you automatically force the oracle to perform a checkpoint?The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.LOG_CHECKPOINT_TIMEOUT = 3600;  # Every one hourLOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.What is Cluster table in Oracle database?A Cluster is a schema object that contains one or more tables that all have one or more common columns. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT and UPDATE statements that modify cluster key values.Can you differentiate between complete and incomplete recovery?An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.What is difference between RMAN and Traditional Backup?RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode. Check: Benefit of RMAN BackupWhat are bind variables and why are they important?With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.How to recover database without backup?If flash recovery is enabled then we can recover database without having backup? Otherwise we cannot recover database without backup.How to write explicit cursor to avoid oracle exception: no_data_found and too_many_rows?In PL/SQL if you try to write select statement with into clause it may return two exception no_data_found and too_many_rowsto avoid this exception you have to write explicit cursor.Exception Block,When no_data_found// Put your codeWhen_too_many_rows// put your codeWhen others then// put your codeEnd;What are differences between Reference cursor and Normal cursor?Reference cursor gives the address of the location instead of putting item directly. It holds the different type of structures. Normal cursor holds one structure of table.Reference cursor is a dynamic cursor where as normal cursor is static cursor. In dynamic cursor single statement are process multiple select statement dynamically at run time where as in normal cursor we process only one select statement.What is Pipeline view?In case of normal views whenever you call the view it will get data from the base table where as in case of pipeline view if you call the view it will get data from another intermediate view.How would you find the performance issue of SQL queries?– Enable the trace file before running your queries– Then check the trace file using tkprofcreate output file.– According to explain plan check the elapsed time for each query– Then tune them respectively.What is difference between Recovery and Restoring of database?Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state.What are the Jobs of SMON and PMON processes?SMON – System Monitor performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.PMON – Process Monitor failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.When you should rebuild index?In fact in 90% case never. When the data in index is sparse (lot of holes in index, due to delete and updates) and your query is usually ranged based. Also index BLEVEL is one of the key indicators of performance of SQL queries doing index range scan.What is key preserved table?A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.Which of the following is NOT an oracle supported trigger?BeforeDuringAfterInstead ofAnswer: BWhich of the following is NOT true about modifying table column?You can drop a column at any time.You can add a column at any time as long as it is a NULL column.You can increase the number of characters in character columns or number of digits in numeric columns.You can not increase or decrease the number of decimal places.Answer: DHow can you find SQL of the Currently Active Sessions? Compare tables shutdown abort.How do you move table from one tablespace to another tablespace?You can use any of the below method to do this:1.      Export the table, drop the table, create definition of table in new tablespace and then import the data using (imp ignore=y).2.      Create new table in new tablespace then drop the original table and rename temporary table with original table name.CREATE TABLE temp_name TABLESPACE new_tablespace as select * from ‘source_table’;DROP TABLE real_table;RENAME temp_name to real_table;For More Interview Preparation Click on Link:DBA Interview Questions with Answers Part1,  DBA Interview Questions with Answers Part2DBA Interview Questions with Answers Part3, DBA Interview Questions with Answers Part4‎DBA Interview Questions with Answers Part5, DBA Interview Questions with Answers Part6DBA Interview Questions with Answers Part7, DBA Interview Questions with Answers Part8‎DBA Interview Questions with Answers Part9, DBA Interview Questions with Answers Part10DBA Interview Questions with Answers Part11, DBA Interview Questions with Answers Part12DBA interview Questions with Answers Part13, DBA Interview Questions with Answers Part14DBA Interview Questions with Answers Part 15, DBA Interview Questions with Answer Part 16DBA Interview Questions with Answer Part17, Basic SQL Question & Answer SessionDiscussion on SCN and Checkpoint, Discussion: Import/Export Utility?Points to be Remember before Online Interview

[notsadrobotxyz]

Oracle 12c new features?

Oracle database 12c (c for cloud) a multi tenant database management system introduce so many important new capability in so many areas – database consolidation, query optimization, performance tuning, high availability, partitioning, backup and recovery .Pluggable Databases:In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.Consolidation is an important business strategy to reduce the cost of infrastructure and operational expense. In many production database servers, a big portion of CPU cycles go unused. By consolidating many databases into fewer database servers, both the hardware and operational staff can be more effectively utilized.Oracle's new pluggable database feature reduces the risk of consolidation because the DBA can easily plug or unplug an existing database to or from a container database. There is no need to change any code in the application.It is also easy to unplug a database and convert the pluggable database to a traditional database if required. In addition, you can back up and recover pluggable databases independently of the container database; you can also perform a point-in-time recovery of a pluggable database. Further, Resource Manager can be used to control resources consumed by a pluggable database.Optimizer features:Oracle 12c introduces a few useful SQL Optimizer features, and most of these are automatically enabled.It is not uncommon for Optimizer to choose an inefficient execution plan due to incorrect cardinality estimates, invalid statistics, or even stale statistics. This can have dire results. A SQL statement estimated to run for a few seconds might take hours to execute if the chosen execution plan is not optimal.SQL:Identity columns which are auto incremented at the time of insertionSQL> create table emp (emp_id number generated as identity, emp_name varchar);SQL> create table emp (emp_id number generated as identity (start with 1 increment by 1 cache 20 noorder), emp_name varchar;Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K (from 4K).Default on Null (A default value is inserted into the null column).Session private statistics for GTTs (Table and index statistics are held private for each session)UNDO for temporary tables can now managed in TEMP, rather than regular UNDO tablespace.For global temporary tables will not generate UNDO. Reduces contents of regular UNDO allowing better flashback operation.In oracle 12c we are able to make column invisibleSQL> create table ss (column-name column-type invisible); SQL> alter table ss1 modify column-name invisible; SQL> alter table ss1 modify column-name visible;In oracle 12c, No need to shutdown the database for changing Archive log mode.Datapump now allow tuning off redo for the import (only) operation.Now can create duplicate indexes using the same column, in the same order, as an existing index.The truncate command is enhanced with a CASCADE option which allows child record.Oracle 12c allows using DDL inside the SQL statements (PL/SQL inside SQL). Moving and Renaming datafile is now ONLINE, no needs to put datafile in offline.PL/SQL:A role can now be granted to a code unit (PL/SQL Unit Security). Thus one can determine at a very fine grain, which can access a specific unit of code.We can declare PL/SQL functions in the WITH Clause of a select statement.Map Reduce can be run from PL/SQL directly in the database.We can use Booleans values in dynamic PL/SQL. Still no Booleans as database types.ASM:Introduction of Flex ASM, with this feature, database instances uses remote ASM instances. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node (application layer is the typical example of leaf nodes) where they don't require any network heartbeat.Oracle ASM disk scrubbing (Checks for logical data corruptions and repair them automatically.)RMAN:Accidental Drop table, Truncate Table, Wrong scripts human error recovery.RMAN TABLE Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required).Recover or copy files from Standby databases.Restore & Recover individual tables from RMAN backup.Incremental recovery more faster, many of the tasks removed. You can automate the use of incremental backup to bring the standby db in sync.Import from older export files possibilities.Partitioning:Partitioning enhancements, Multiples partition operations in a single DDL.Online move of a partition (without DBMS_REDEFINTIION).Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.Cascade for TRUNCATE and EXCHANGE partition.Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.Patching:Centralized patching - We can test patches on database copies, rolling patches out centrally once testing is complete.Compression: Automated compression with heat map. Optimization can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.Data Guard:1.      Oracle Database 12c introduces a new redo transportation method (Fast Sync redo transport) which omits the acknowledgement to primary of the transaction on the standby.2.      Creating a new type of redo destination (“Far Sync Standby” composed only of the standby control files), the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.3.      Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.4.      Dataguard Broker now supports cascaded standby.5.      Global Temporary Tables can now be used on an Active Guard standby database.New Views/Packages in Oracle 12c Releasedba_pdbs, v$pdbs, cdb_data_files, dbms_pdb, dbms_qopatchUsing DBUA to upgrade the existing database is the simple and quickest method.For step by step details follow the below link: Upgrade Oracle Database 11g to 12c