Feed aggregator

How to drop a datafile ?

Tom Kyte - Fri, 2018-11-30 09:06
I have a tablespace with a few datafiles. I have droped lots of tables in this tablespace and i just want to keep only one datafile on this tablespace. I know the name of the only datafile witch contains datas so i no more need the others datafiles...
Categories: DBA Blogs

EXECUTE IMMEDIATE vs DBMS_SQL

Tom Kyte - Fri, 2018-11-30 09:06
We are using StreamServe (http://www.opentext.com/what-we-do/products/customer-experience-management/customer-communications-management/opentext-streamserve) to print documents from our database. This application gives us a severe headache because...
Categories: DBA Blogs

Western Digital Transforms Business Processes with Oracle Cloud

Oracle Press Releases - Fri, 2018-11-30 08:00
Press Release
Western Digital Transforms Business Processes with Oracle Cloud Data infrastructure powerhouse brings together three Fortune 500-size organizations onto Oracle ERP Cloud to deliver greater business intelligence, agility and value to its customers

Redwood Shores, Calif.—Nov 30, 2018

Western Digital Corp (NASDAQ: WDC) selected Oracle Cloud to help modernize its business processes as part of its digital transformation journey. The company chose Oracle Enterprise Resource Planning (ERP) Cloud in May, 2016 to bring together the core business systems of three multi-billion dollar companies – Western Digital, SanDisk and HGST – all with growth in mind. As a result, Western Digital has been able to combine numerous applications, reduce approval times by 70 percent, rationalize suppliers by 50 percent and improve acquisition agility on a global scale.

After acquiring SanDisk and HGST, Western Digital faced the challenge of rationalizing three Fortune 500-size on-premises ERP systems. Western Digital decided it needed a modern suite of best-in-class ERP applications that would enable it to enhance the customer experience and achieve a competitive advantage while improving employee productivity. After careful evaluation, Western Digital selected Oracle ERP Cloud, which has allowed it to seamlessly extend its on-premises applications to the cloud and establish a modern business platform that can seamlessly scale to support its future expansion plans and new technology innovations.

“The process of rationalizing three large legacy systems and associated applications gave us a unique opportunity to drive change, as more than two thirds of our organization would have to undergo change no matter what application we selected. We needed a simple, yet scalable cloud platform that could power our rapidly growing business,” said Steve Phillpott, CIO at Western Digital. “With Oracle Cloud we have been able to consolidate applications, automate key financial workflows and radically improve productivity. Oracle has been the catalyst for change and is pivotal in our digital transformation journey.”

“The technology industry is an incredibly fast-paced and competitive market, where the quickest, most agile players win. Western Digital has done an incredible job bringing three large enterprise organizations onto a single Fortune 100-scale platform to significantly improve efficiency and business agility,” said Rondy Ng, senior vice president, Oracle Applications Development. “With Oracle ERP Cloud, Western Digital will be able to continually take advantage of the latest innovations to gain a competitive advantage and successfully position itself for future growth.”

Western Digital’s adoption of Oracle ERP Cloud builds upon its use of both Oracle on-premises and Oracle Analytics Cloud. With Oracle, Western Digital is able to avoid massive file download times and manual analysis to provide its employees with access to the information they need to make better, faster decisions. Learn more about how Oracle is “empowering Western Digital with Oracle Analytics Cloud.

Contact Info
Bill Rundle
Oracle
650.506.1891
bill.rundle@oracle.com
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Bill Rundle

  • 650.506.1891

Extend a Oracle Access Manager 12.2.1.3 with User Messaging Service

Yann Neuhaus - Fri, 2018-11-30 07:43

After upgrading a Oracle Access Manager from 11.1.2.3 to 12.2.1.3, one of my customer requested from me to configure the Forget Password feature. This feature requires the User Messaging Service that was part of the SOA software in previous versions. Now it is part of the Oracle Access Manager software and the WebLogic Domain can be extended with the UMS services.

Extend WebLogic Domain

[oracle@vm03 ~]$ cd /u01/app/fmw_oim_12213/
[oracle@vm03 fmw_oim_12213]$ ls
cfgtoollogs coherence domain-registry.xml em idm inventory OPatch oracle_common oraInst.loc oui user_projects wlserver
[oracle@vm03 fmw_oim_12213]$ cd oracle_common/common/bin/
[oracle@vm03 bin]$ ./config.sh

Conf1
In the next wizard, we will select the Oracle User Messaging Service Basic template that is a quick start template that defines the managed server, ums_server1, and targets all UMS components to that server.
Conf2
We keep the file store persistence store but it should be placed on a shared drive if it is planned to use a UMS WebLogic cluster.
Conf3
The OPSS was already configured in the OAM WebLogic Domain, there is noting to change in the two next wizards.
Conf4
conf5
In the next wizards we get the JDBC connections from the RCU configuration schema.
conf6
conf7
The UMS repository schema was already installed. The JDBC connections shows correctly and the JDBC connections test went smoothly.
conf8
conf9
We had no keystore configured
conf10
We had to go through the topoligy to configure the UMS server and the UMS cluster.
conf11
In the next wizard, the new ums_server1 appeared listening on port 7003. This is the default for all new WebLogic Servers in the configuration wizard. Best is to change it to the default SOA port that is 8001 (it can help avoiding some troubles with hard coded ports in some applications).
conf12
As this OAM WebLogic Domain was configured with clusters, I had to create an UMS cluster named ums_cluster.
conf13
I did not use server templatres neither dynamic servers. Nothing changed in the next two wizards.
conf14
conf15
Assigned the ums_server1 to the ums_cluster moving it from the right panel to the left panel under the ums_cluster.
conf16
The coherence cluster existed already in the OAM WebLogic Domain and the machine too. Not changes was done in the next two wizards.
conf17
conf18
The ums_server1 was attached to the already existing machine moving it from the left panel to the right panel under the existing machine.
conf19
The deployments and the services were not changed. No actions was applied on the two next wizards.
conf20
conf21
We kept the persistence file store file name as it was proposed by default.
conf22
We checked the summary and clicked the Update button to extend the OAM WebLogic Domain.
conf23
All went smoothly without errors.
conf24
After the Domain has been Extended, it was started. The first time the ums_server1 had to be started manually using the startManagedWebLogic.sh script.

Configure the UMS email driver.

We decided to use mails to send the One Time Pin token to the users requesting to reset their password.

This configuration has to be done through the Fusion Middleware Enterprise Manager Console.
EM1
Select usermessaginserver (ums_server1)
EM2
EM3
At the bottom of the page, look for the Email driver and click the configure button. The next page allows t create a new Email driver and then to configure it.
Em4
EM5
here are the parameters that needs to be set for the UMS to be able to send mails.

  • Name: MailServer
  • Capability: Send
  • Sender Address:
    Check ‘Use Default Sender Address’ and set the default email ddress: EMAIL:admin@dbi-workshop.com
  • Outgoing Mail Server: “to be filed with your email server name”
Usage

Using the Oracle Access Manager One Time Pin rest API, messages are pushed to the User Messaging Service JMS queue and sent by mails using the email driver.

My next blog will explain how to configure the Oracle Access Manager Forget Password feature using the One Time Pin sent by Mail

Cet article Extend a Oracle Access Manager 12.2.1.3 with User Messaging Service est apparu en premier sur Blog dbi services.

Index rebuild bug

Jonathan Lewis - Fri, 2018-11-30 07:02

I tweeted a reference yesterday to a 9 year old article about index rebuilds, and this led me on to look for an item that I thought I’d written on a related topic. I hadn’t written it (so there’s another item on my todo list) but I did discover a draft I’d written a few years ago about an unpleasant side effect relating to rebuilding subpartitions of local indexes on composite partitoned tables. It’s probably the case that no-one will notice they’re suffering from it because it’s a bit of an edge case – but you might want to review the things your system does.

Here’s the scenario: you have a large table that is composite partitioned with roughly 180 daily partitions and 512 subpartitions (per partition). For some strange reason you have a couple of local indexes on the table that have been declared unusable – hoping, perhaps, that no-one ever does anything that makes Oracle decide to rebuild all the unusable bits.

One day you decide to rebuild just one subpartition of one of the indexes that isn’t marked as unusable. You might be planning to rebuild every single subpartition of that index overnight, but you’re going to start with just one to see how long it takes. Something very strange happens – and here’s a simple model to demonstrate:

rem
rem     Script:         index_rebuild_pt_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2015
rem
rem     Last tested
rem             18.3.0.0
rem             12.1.0.1
rem             11.2.0.4
rem

create table interval_hash (
        n1 number,
        n2 number,
        n3 number
)
segment creation immediate
partition by range(n1) interval (1)
subpartition by hash (n2) subpartitions 16 (
        partition p1 values less than (2) 
)
;


begin
        for i in 1 .. 16 loop
                for j in 1..64 loop
                        insert into interval_hash i(n1, n2, n3) values (i, j, j + 64*(i-i));
                end loop;
        end loop;

        commit;
end;
/

create index ih_i1 on interval_hash(n1) local;
create index ih_i2 on interval_hash(n2) local;
create index ih_i3 on interval_hash(n3) local;

alter index ih_i1 unusable;
alter index ih_i2 unusable;

The code creates a table which extends as data arrives to have 16 partitions with 16 subpartitions each – for a total of 256 data segments. After loading the data I’ve created 3 local indexes on the table and made two of them unusable.

After setting up the table and indexes I’ve identified one subpartition of the table by name, enabled tracing, and rebuilt the corresponding subpartition of the index which is currently usable (the same effect appears if I rebuild a partition of one of the unusable indexes, but the phenomenon is slightly more surprising if you rebuild a usable subpartition). Here’s the code for the rebuild:


column max_subp new_value m_subp

select
        max(partition_name) max_subp
from
        user_segments
where
        segment_name = 'INTERVAL_HASH'
;

alter session set events '10046 trace name context forever, level 4';

alter index ih_i3 rebuild subpartition &m_subp;

alter session set events '10046 trace name context off';

Would you expect to find anything interesting in the trace file after running it through tkprof ? Here’s the most frequently executed SQL statement I found when running this test on an instance of Oracle 18.3:


tkprof or18_ora_24939.trc temp sort=execnt

SQL ID: 0yn07bvqs30qj Plan Hash: 866645418

select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg,
  maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg,
  maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg,
   cmpflag_stg, cmplvl_stg,imcflag_stg, ccflag_stg, flags2_stg
from
 deferred_stg$  where obj# =:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    512      0.02       0.03          0          0          0           0
Fetch      512      0.00       0.00          0       1536          0         512
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1025      0.03       0.03          0       1536          0         512

This query runs once for every single subpartition of the two unusable indexes. (There’s another statement that runs once for every partition of the two unusable indexes to provide the object numbers of the subpartitions and that shouldn’t be forgotten). In my example the impact and time to run doesn’t look too bad – but when the numbers climb to a couple of hundred thousand executions before you start to rebuild the first subpartition you might start to worry. Depending on the state of your data dictionary, and how you got to the point where you had so many unusable segments, the time to execute could become large, and you might do most of it all over again for the next subpartition!

You might wonder why anyone would have a couple of unusable indexes. First, many years ago (in Practival Oracle 8i) I pointed out that if you wanted to create a new locally partitioned index you might want to create it unusable and then rebuild each partition in turn – that might not be a good idea any more. (The book also pointed out the requirement to think about sizing the dictionary cache (rowcache)).

Secondly, before the introduction of partial indexing it was possible to emulate the feature manually for local indexes by setting partitions and subpartition unusable and allowing the optimizer to use table expansion to pick the best plan for partitions that had different index partitions still usable.

Finally if you are using the new partial indexing feature of 12.2 where you can set the default characteristic of a partitioned table to “indexing off”, and the default characteristic of an index to “indexing partial”, the partitions of any local index that are not created are deemed to be deferred – but you won’t see the effect in my example unless you modify it to include partial indexes and include an “alter system flush shared pool” just before the rebuild.

Footnote

I’ve found this bug (or something very similar) on MoS: Bug 17335646 : ALTER INDEX IDX REBUILD SUBPARTITION SP VISITS EVERY INDEX AND SUBPARTION. However the bug was recorded against 11.2.0.3 and its status is: “31 – Could Not Reproduce. To Filer”. It does seem to be terribly easy to reproduce, though, provided you have a large number of unusable subpartitions in your indexes – so it’s possible the original bug appeared even when there were no unusable subpartitions (the customer comments about the bug don’t give any suggestion that there might be unusable indexes in place – and it seems unlikely that the 22 indexes mentioned were all unusable).

 

 

 

 

 

Database getting locked during DBMS STATS Execution

Tom Kyte - Thu, 2018-11-29 14:46
Hello Team, Would really appreciate any help in this one. Every day our DBMS Stats job runs at around 10 GMT which is around 4pm CST. We have a table that holds close to 10M records and keeps inserting into the table almost every second. Recent...
Categories: DBA Blogs

How can I show result sets like this?

Tom Kyte - Thu, 2018-11-29 14:46
Hi Tom, I have two tables. One is test and another is test1. Each table has two columns, record_id and c1 and both are varchar2(10). Test content is below <code>record_id c1 ----------------- 1 null 2 3 3 + 4 ...
Categories: DBA Blogs

Identify batch number for given dates.

Tom Kyte - Thu, 2018-11-29 14:46
Hi, I need a sql query to calculate batch_number based on the dates. For the same name and country we need to show same batch number up to 12 months. For next batch we have to start from that date and consider upto 12 months as next batch. Her...
Categories: DBA Blogs

How can I show result set like this?

Tom Kyte - Thu, 2018-11-29 14:46
Hi friends! I tried to use case, pivot, and other ways to return the expected, but I am not successfully. CREATE TABLE T1 (DATE_ID NUMBER(2), NAME VARCHAR2(2), AGE NUMBER(2), ORDER_FLAG NUMBER(2)); INSERT INTO T1 VALUES ('1','A','21','1'); ...
Categories: DBA Blogs

Need to create index for every table before I can execute any query

Tom Kyte - Thu, 2018-11-29 14:46
I'm running an Oracle 12c database with 32gb ram and ~ 1 mln rows by ~ 30 columns. My queries take forever to run if I try to do a join or where without an index. If I create the indexes it finishes in a few seconds. Could this have something to ...
Categories: DBA Blogs

Index Monitoring

Tom Kyte - Thu, 2018-11-29 14:46
Hi All, I know index monitoring in 12.1.0.2 is not that great out of the box. I have a query that runs against the DBA_HIST_SQL_PLAN view, as below. Would this be a suitable way to get index usage? (We have the Diag+Tuning packs available to us) ...
Categories: DBA Blogs

AWS Billing: Set an alarm to a cost threshold

Dietrich Schroff - Thu, 2018-11-29 14:37
Knowing about some details about the costs inside aws and some of the services (especially, that Docker, VPNs and Kubernetes are not included in the free trials)

i set an alarm on my account, to get informed about new costs:

So move to "Cloudwatch" and there you have to choose "Alarms":

 Then "Create Alarm":
 And click on "select metric":
 Inside "select metric" click on "Billing" (at the bottom):
And select "Total Estimated Charge"
Select the Checkbox for USD and then "Select metric"
 After that you have to specify an exceed and an e-mail address:
If this is your first alarm you have to verify your e-mail:
 And if the confirmation is done, you will get:
Now the Dashboard shows:

And i got an e-mail with this alarm:



AWS re:invent 2018 – Day 3

Yann Neuhaus - Thu, 2018-11-29 13:11

On Wednesday morning, this is the big day for AWS re:Invent conference. The day started with a 3 hours keynote from Andy Jassy, CEO of AWS.The keynote covered a lot of different topics: databases, blockchain, machine learning… As most keynotes, it started with some numbers and AWS is still a strong leader on the public Cloud if you look at the market shares, a moment to tackle a bit some known competitors.

AWS market share

Then it’s also the occasion for lots of announces and new services launches. If I have to make a selection, here is a few:

  • AWS Timestream: a new database service focusing on data from time series designed for IoT data that may come from sensors
  • AWS Quantum Ledger Database (QLDB): a new database service to answer some needs where customers were looking at the blockchain
  • AWS Glacier Deep Archive: the goal is to forget about backup to tape with a very competitive price of 1$ per TB (0.00099 $ per GB)
  • AWS Security Hub: to manage security and compliance across an AWS environment

Andy Jassy also announced a competition for machine learning developer AWS DeepRacer league. The goal is to create an autonomous driving model so the AWS DeepRacer (small electric car) finish a lap of a test track the fastest way possible. It’s reinforcement learning with a try and error where the model improves itself using the feedback of previous iterations. For this year developers will have 1 day and the 3 best will compete in a final before Thursday’s morning keynote.

After the keynote, I continued to pick sessions around databases and AWS introductory sessions about the infrastructure.

The one about matching the workload with the right database was very interesting. Basically, Rick Houlihan was explaining that you have to know your knowledge before picking the engine you will use. Most often, the engine is picked because it’s already licensed or because there is already an operational team knowing how to operate it. Also it happens that some people tries to go to the latest or newest trend because of the buzz. According to him, relational databases are not scaling very well and are optimized for storage. That’s true that if you fully normalize, data is not supposed to be duplicated. On the other hand, NoSQL databases can scale a lot but are not very adaptive to ad-hoc queries.

Databases workload

On the architecture part, I focused again on VPC and there are some changes on how you can build your network to work on AWS alongside with your on premise datacenter.

VPC sharing allows now a VPC to span across different AWS account within the same organization. It means you can have several billing accounts but managed by a VPC owner that would be responsible for routing tables and so. It can simplify the maintenance and help network engineers.

The other big point I noted is the new transit gateway (TGW). A transit gateway can be attached to several VPCs and to AWS VPN to link with your datacenter. This is a central hub where you can maintain routing tables for inter-VPC traffic without maintaining a virtual private gateway per VPC.

VPC before TGW VPC with TGW

On Thursday, there will be another keynote from Dr Werner Vogels, CTO of Amazon.com. Let’s see if there will be other announces.

Cet article AWS re:invent 2018 – Day 3 est apparu en premier sur Blog dbi services.

Learn How to Optimise your Oracle Support Experience at the UK Oracle User Group Events

Chris Warticki - Thu, 2018-11-29 11:25

Oracle Support will be speaking at the Apps18 and JDE18 events in Liverpool (Dec 3-5) and we will also be at the Oracle stand.

Whether you are using Oracle on-premises or in the cloud, come along and discover best practices for the support tools that will to help your team be more productive. Find out about the training available as part of your Oracle Premier Support and tips such as how you can reduce the need for creating service requests.

You can also hear the latest news on the Oracle Applications Unlimited products—Oracle E-Business Suite, JD Edwards EnterpriseOne, Peoplesoft, and Siebel—with Oracle Premier Support through at least 2030. Learn about the Applications Unlimited innovations, like ongoing new features you can choose without upgrades. Join us at our sessions or at the Oracle stand to discuss product roadmaps and more.

Hear more at these four sessions:

If you want to demonstrate your own expertise—or learn about using Oracle Support more effectively—consider becoming Oracle Support accredited (login required).

We look forward to meeting you on the Oracle Stand.

Next Generation Restauranteurs Serve Up Stellar Experiences with Oracle

Oracle Press Releases - Thu, 2018-11-29 07:00
Press Release
Next Generation Restauranteurs Serve Up Stellar Experiences with Oracle Simphony Restaurant Management Platform and Oracle MICROS Hardware Help Caractère Deliver Seamless Customer Service

Redwood Shores, Calif.—Nov 29, 2018

Caractère, the first restaurant from husband and wife team Emily Roux and Diego Ferrari, has debuted with the support of Oracle Food and Beverage technology. Based in one of London’s most fashionable neighborhood’s, Notting Hill, Caractere serves up contemporary Italian cuisine, with French culinary inspiration. To ensure the experience is as memorable as the food, Caractère is using a combination of the Simphony Cloud Point of Sale Platform the Oracle MICROS workstation 6 family and the Compact Workstation 310.

New restaurant concepts have a variety of obstacles they must overcome as they create a signature menu that customers want to return to. With Oracle Food and Beverage solutions operators can take comfort in the reliability of the time-tested restaurant management and point-of-sale workstations and focus on expressing their creativity and differentiating their offering.

“With Oracle, the ordering process is seamless. The order is taken and sent straight down to the kitchen. If our guests have any dietary requirements or allergies the system makes it very clear to the kitchen, so there’s no slip ups or confusion which is essential,” said Emily Roux, Owner, Caractère. “The system saves time and energy, enabling our front of the house staff to spend more time on the floor engaging with guests and ensuring they have a positive experience.”

Oracle Food and Beverage Simphony restaurant management platform empowers restauranteurs to streamline operations, increase customer loyalty and elevate profitability with the ability to orchestrate their entire operations on one platform. Combined with the reliability of the Oracle MICROS workstations, both independent restaurants and chains have the trusted system they need to support the business they have today and scale to support their future aspirations.

“Technology plays a crucial role in enabling new restaurants to scale and deliver the consistent service that guests expect,” said Chris Adams, vice president of strategy, Oracle Food and Beverage. “Consistency of experience can define the trajectory for new restaurants and restaurant management technology is critical to ensuring that service and kitchen operations are in sync. Caractère has been an out of the gate success and we look forward to continuing to work with its talented founders to support their growth.”

Caractère purchased Oracle Food and Beverage in August before opening in October 2018.

Contact Info
Matt Torres
Oracle
415-595-1584
matt.torres@oracle.com
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

About Oracle Food and Beverage

Oracle Food and Beverage, formerly MICROS, brings 40 years of experience in providing software and hardware solutions to restaurants, bars, pubs, clubs, coffee shops, cafes, stadiums, and theme parks. Thousands of operators, both large and small, around the world are using Oracle technology to deliver exceptional guest experiences, maximize sales, and reduce running costs.

For more information about Oracle Food and Beverage, please visit www.Oracle.com/Food-Beverage

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Matt Torres

  • 415-595-1584

[Blog] Subprocess in Oracle SOA Suite 12C

Online Apps DBA - Thu, 2018-11-29 05:06

Subprocess is a New Feature introduced in Oracle SOA Suite 12c Visit: https://k21academy.com/soadev22 to learn more about this latest changes in SOA Suite 12c ✔ Subprocess in SOA 12c ✔ Steps to Create an Inline Subprocess ✔ Steps to Create a Standalone Subprocess Subprocess is a New Feature introduced in Oracle SOA Suite 12c Visit: […]

The post [Blog] Subprocess in Oracle SOA Suite 12C appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

explain plan analysis

Tom Kyte - Wed, 2018-11-28 20:26
Hi Team , can you help me to analyse the below execution plan : <code> -------------------------------------------------------------------------------------------------------- | Id | Operation | Name ...
Categories: DBA Blogs

Why this sql causes an internal error in Oracle?

Tom Kyte - Wed, 2018-11-28 20:26
Hi! I'm running this little SQL on 12c, also tried on 11g, and got an internal error on both. I couldn't understand why. Probably a bug, as noted by people in stackoverflow, where I originally asked about: https://stackoverflow.com/questions/53527...
Categories: DBA Blogs

Pluggable database save state -12C

Learn oracle 12c database management - Wed, 2018-11-28 15:40
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;

Pluggable database altered.


SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ WRITE

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

***********************************************
DISCARD PDB SAVED STATE
***********************************************

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN


SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

Categories: DBA Blogs

how to create nested cursor in plsql

Learn oracle 12c database management - Wed, 2018-11-28 15:39




select * from empinfo; 

"ID"    "FNAME"    "LNAME"    "DEPTNO"
1    "arv"    "red"    100
2    "sam"    "kum"    200
3    "tom"    "com"    100
4    "Hef"    "Kom"    200
5    "fam"    "ily"    300



select * from deptinfo;

"ID"    "DEPTNO"    "DEPTNAME"    "MGR"
1    100    "HR"    "Bill"
2    200    "IT"    "Greg"
3    300    "SALES"    "Phil"


-- sqlcode starts here

declare

CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;

CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;

OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('REC1.id:  '||REC1.ID||'  belongs to dept ID : '||REC2.DEPTNO||'  DEPT :'||REC2.DEPTNAME);

END LOOP;
CLOSE CUR2;

end loop;
close CUR1;
end;

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator