DBA Blogs

Partner Webcast – Developing Internet of Things Applications with Oracle IoT Cloud Service

Oracle Internet of Things (IoT) Cloud Service is a managed Platform as a Service (PaaS) cloud-based offering that helps you make critical business decisions and strategies by allowing you to connect...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Tried Live SQL

Bobby Durrett's DBA Blog - Thu, 2019-05-09 18:13

Quick post. I tried https://livesql.oracle.com/ for the first time.

Looks like you can try out the latest version of Oracle for free. You can type in SQL statements in your web browser.

There seem to be a boatload of scripts and tutorials to try out. Might be good for someone who is new to SQL.

Bobby

Categories: DBA Blogs

Ace Your Excel Test to Get Your Dream Job

VitalSoftTech - Thu, 2019-05-09 10:29
You aced your first interview and you couldn’t feel better about it – you answered all the questions correctly, made a great impression, and managed to have a professional yet very friendly conversation with someone you might be working with soon. That is when you found out that the next step of the recruitment process […]
Categories: DBA Blogs

Digital Marketing and Its Benefits

VitalSoftTech - Wed, 2019-05-08 10:12
Does your business make use of digital marketing services? Today, a lot of businesses utilize the online world to take their business to a higher level. They engage in digital marketing to share what they can offer. If you do, that’s really good! Now, all you need to do is to grow it or improve […]
Categories: DBA Blogs

Pester script parameter passing not working

Matt Penny - Wed, 2019-05-08 04:22
Problem

I was trying to parameterize a Pester script. The script looked like this:


param (
[string]$ComputerName,
[string]$IPAddress
)

write-dbg "$ComputerName: "
write-dbg "
$IPAddress: "

Describe "$ComputerName is visible" {

It "It is ping-able" {
    {test-connection $ComputerName -count 1} | Should Not Throw

    $(test-connection $ComputerName -count 1 | Measure-Object).count | Should Be 1
}

}

…but passing the parameters wasn’t working.

Solution

The problem was that I was calling the script as follows
$ Invoke-Pester @{PAth = c:\pester\diagnostics\simple\StandardDomainContoller.tests.ps1; Parameters=@{ComputerName = "server1.here.co.uk";IPAddress = "17.6.5.1""}}

…and the Path variable needs quotes:

$ Invoke-Pester @{PAth = 'c:\pester\diagnostics\simple\StandardDomainContoller.tests.ps1'; Parameters=@{ComputerName = "server1.here.co.uk";IPAddress = "17.6.5.1""}}

Categories: DBA Blogs

Partitioning -- 16 : Hybrid Partitioning

Hemant K Chitale - Tue, 2019-05-07 02:55
Oracle 19c introduces Hybrid Partitioning whereby you can have external and internal Partitions co-existing.  External Partitions are on storage (filesystem) outside the database.

Let's say we have a List Partitioned table for the widgets that we manufacture. The table is Partitioned by WIDGET_CLASS_ID, based on an ISO standard.  So all companies that manufacture widgets adopt the same WIDGET_CLASS_ID:

SQL> desc widgets_list
Name Null? Type
----------------------------------------- -------- ----------------------------
WIDGET_CLASS_ID VARCHAR2(5)
WIDGET_ID VARCHAR2(32)
WIDGET_NAME VARCHAR2(32)
WIDGET_DESCRIPTION VARCHAR2(128)

SQL>
SQL> l
1 select table_name, partitioning_type, partition_count
2 from user_part_tables
3* where table_name = 'WIDGETS_LIST'
SQL> /

TABLE_NAME PARTITION PARTITION_COUNT
-------------------------------- --------- ---------------
WIDGETS_LIST LIST 3

SQL>
SQL> l
1 select partition_name,high_value, num_rows
2 from user_tab_partitions
3* where table_name = 'WIDGETS_LIST'
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
---------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119

SQL>


Later, another widget manufacturer that manufactures widgets of CLASS_ID 'X' is acquired.  The WIDGETS_LIST table is in a non-Oracle database and is received as a CSV file.  We accept the CSV file onto a filesystem location :

sh-4.2$ pwd
/home/oracle/ACQUIRED_COMPANY
sh-4.2$ cat AC_Widgets_List.CSV
'X','ABCXX2','The1','cddfdaxx'
'X','XXD2','The2','dda3'
'X','XRC34','The3','ff33355312'
sh-4.2$


So, we have a CSV file "AC_Widgets_List.CSV" listing the widgets manufactured by this company. We want to add it to our WIDGETS_LIST table.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create directory acquired_company as '/home/oracle/ACQUIRED_COMPANY';

Directory created.

SQL> grant read, write on directory acquired_company to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant@ORCLPDB1
Connected.
SQL>
SQL> l
1 alter table widgets_list
2 add external partition attributes (
3 type oracle_loader
4 default directory acquired_company
5 access parameters (
6 fields terminated by ','
7 (widget_class_id, widget_id, widget_name, widget_description)
8 )
9* )
SQL> /

Table altered.

SQL>
SQL> l
1 alter table widgets_list
2 add partition P_ACQ_CO values ('X')
3* external location ('AC_Widgets_List.CSV')
SQL> /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','WIDGETS_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> l
1 select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'WIDGETS_LIST'
4* order by partition_position
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119
P_ACQ_CO 'X' 3

SQL>
SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS WIDGET_ID WIDGET_NAME
------------ -------------------------------- --------------------------------
WIDGET_DESCRIPTION
--------------------------------------------------------------------------------
'X' 'ABCXX2' 'The1'
'cddfdaxx'

'X' 'XXD2' 'The2'
'dda3'

'X' 'XRC34' 'The3'
'ff33355312'


SQL>


The rows in the "AC_Widgets_List.CSV" file are now visible as rows in a *Partition* in our Oracle Table WIDGETS_LIST.
Of course, these being external, cannot be modified by INSERT/UPDATE/DELETE DML.

The External Attribute Type that I used is ORACLE_LOADER to use the SQL Loader libraries on a filesystem file.  Oracle 19c also supports ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE to reference files stored in other types of storage.

Hybrid Partitions are supported with single-level Range and List partitioning methods.  ALTER TABLE to ADD, DROP and RENAME Partitions is supported.

An External Partition can be Exchanged with an External Non-Partitioned Table only.
.
.
UPDATE :  Later, if I update the CSV file (using an external editor) to remove the quotation mark :

sh-4.2$ cat AC_Widgets_List.CSV
X,ABCXX2,The1,cddfdaxx
X,XXD2,The2,dda3
X,XRC34,The3,ff33355312
sh-4.2$

SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS_ID WIDGET_ID WIDGET_NAME
---------------- -------------------------------- --------------------------------
WIDGET_DESCRIPTION
------------------------------------------------------------------------------------
X ABCXX2 The1
cddfdaxx

X XXD2 The2
dda3

X XRC34 The3
ff33355312


SQL>


So, it is possible to edit the External Partition using other methods (here I used "vi" on Linux)
.
.
.

Categories: DBA Blogs

Update statement with outer join

Tom Kyte - Sat, 2019-05-04 08:06
Hi, From the sql script, I would like to understand the difference in the behavior between executions of statement 8 and statement 11. While statement 8 updates the flag_1 to null, statement 11 updates it to 2. What difference it makes when I u...
Categories: DBA Blogs

Pass parameter to where clause in bulk collect statement

Tom Kyte - Sat, 2019-05-04 08:06
Hi I have a basic procedure which bulk collects the results of a select statement into a table array. I then print out one line to show that it has worked.. Code (SQL): <code>CREATE OR REPLACE PROCEDURE use_var IS TYPE r_tab IS TABLE OF msf010%...
Categories: DBA Blogs

CDB Fleet in Oracle Database 18c

Oracle in Action - Fri, 2019-05-03 03:21

RSS content

Oracle database 18c  introduces a new CDB Fleet feature  which allows many CDBs to be managed as one. A CDB fleet is a collection of CDBs and hosted PDBs that you can monitor and manage as one logical CDB from a centralized location.

There are two possible roles within a CDB Fleet:

  • Lead CDB: Only one CDB in the Fleet may be designated as the Lead CDB. The lead CDBis the central location for monitoring and managing all the CDBs in the fleet.
  • Member CDB: The CDBs registered with a lead CDB are called member CDBs. There can be one or more member CDB’s in a CDB fleet.

For every  member CDB,  proxy PDBs for the member CDB and its PDB(s) are automatically created in the lead CDB,.  Consequently, all the member CDBs  and their PDBs are now “visible” in the lead CDB . This enables management and monitoring of the entire estate of PDBs in the fleet, physically distributed across various CDBs, from the lead CDB.

Advantages

Reporting, monitoring, and management of the entire CDB fleet through a single interface:

  • Provides massive scalability of the underlying infrastructure
  • Reduces capital and operational costs
  • Provides greater efficiencies to the business.

Related Links:

‘STUB’ Status In DBA_PDBS

References:

https://docs.oracle.com/cd/E96517_01/newft/database-new-features-guide.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-cdb-fleet.html#GUID-5951E81B-4351-4FA4-9F7B-52D2FEB0428D



Tags:  

Del.icio.us
Digg

Copyright © ORACLE IN ACTION [CDB Fleet in Oracle Database 18c], All Right Reserved. 2019.

The post CDB Fleet in Oracle Database 18c appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

View SYS permissions

Tom Kyte - Thu, 2019-05-02 21:46
A vendor would like me to run a script against a client's database. The script requires 'SYS' user access and makes the following grants: <code>grant select on sys.gv_\$mystat to public;</code> <code>grant select on sys.gv_\$vsession to public;</...
Categories: DBA Blogs

Ways to Name the Output Columns of a Pipelined Table Function

Tom Kyte - Thu, 2019-05-02 21:46
How many ways are there to name the output columns of a pipelined table function? My understanding is the only way is via a RECORD type like this: <code>CREATE PACKAGE blah_blah_blah IS TYPE abc_rec IS RECORD (ID NUMBER, ...
Categories: DBA Blogs

Sequence behavior skipping values

Tom Kyte - Thu, 2019-05-02 21:46
Hello, Ask Tom Team. I have a table in a 2-node RAC with an identity column. The sequence is generated by default, cache 3000 and noorder option. I see something weird. The first insert was id 1, which means that it came from instance 1, then...
Categories: DBA Blogs

Unable to complete finish_redef_table on reference partitioned table

Tom Kyte - Thu, 2019-05-02 21:46
Hi , Could you please have a look at below reference partition scenario Rowcount of order_items is 25 millions: I need to change the partition technique of child table i.r. order_items to DAILY-RANGE INTERVAL partition. Table dont have primary k...
Categories: DBA Blogs

Search on concatenated index values

Tom Kyte - Thu, 2019-05-02 21:46
I am looking into an issue with concatenated index (also known as multi-column, composite or combined index). So, by the current design, I have 2 columns KEY1 and KEY2, which produce my PRIMARY KEY PK1. <code> CREATE TABLE "myTable" ( "...
Categories: DBA Blogs

Error while cloning remote database using dblink: ORA-65345: cannot refresh pluggable database

Tom Kyte - Thu, 2019-05-02 21:46
<code>create pluggable database test3 from test32@ttxtest_link1 parallel 8 refresh mode none create_file_dest ='/u02/app/oracle/oradata/test3/' * ERROR at line 1: ORA-65345: cannot refresh pluggable database ORA-17627: ORA-03135: connection lost ...
Categories: DBA Blogs

DML Operation in PL/SQL Function

Tom Kyte - Thu, 2019-05-02 21:46
Sir, 1) Can I perform DML (Insert, Update, Delete) also into a PL/SQL function.....? 2) Can I call a Trigger Explicitly.
Categories: DBA Blogs

Migrating Oracle database

Tom Kyte - Thu, 2019-05-02 21:46
Hello, Thanks for taking up this question. There is an enterprise java application hosted on a huge (40 TB) Oracle database. Can this Oracle database be migrated to : 1. SQL Server or any other RDBMS? 2. Any No SQL database? I have been as...
Categories: DBA Blogs

When-Tree-Node-Selected question.

Tom Kyte - Thu, 2019-05-02 21:46
I've created a tree and it is populating correctly during the when-new-form-instance trigger, but my question is that when I click the mouse on a tree node, the when-tree-node-selection trigger fires properly and the node is highlighted. But wh...
Categories: DBA Blogs

Partner Webcast – Oracle Mobile Hub Simplifies Enterprise Mobile

Mobile is everywhere and touches every facet of our lives. Oracle Mobile Hub provides an open, comprehensive platform that simplifies development for mobile app developers, backend service...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Installed 19.3 on Linux on VirtualBox

Bobby Durrett's DBA Blog - Thu, 2019-05-02 15:14

I noticed that Oracle database 19.3 was available to download so I installed it on a Linux VM under VirtualBox.

I cloned my base Oracle Linux 7 VM and did a yum -y update to get it up to the latest Linux version.

I installed the preinstall RPM:

yum install oracle-database-preinstall-19c

Created the Oracle home, set some variables, and disabled the firewall.

mkdir -p /home/oracle/product/db/19.0.0
chgrp oinstall /home/oracle/product/db/19.0.0
cd /home/oracle/product/db/19.0.0

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0

[root@ora19 ~]# systemctl stop firewalld
[root@ora19 ~]# systemctl disable firewalld

Edited my .bashrc

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0
export ORAENV_ASK=NO
export ORACLE_SID=orcl
. oraenv

Unzipped the downloaded file LINUX.X64_193000_db_home.zip in my ORACLE_HOME. Then I ran runInstaller from the ORACLE_HOME through MobaXterm. For some reason this decided to put the install window overlapping my two screens so I couldn’t tell what I was typing, and it didn’t allow me to move it. Fun times.

It seemed to hang on the last step, but it eventually finished. The process named ora_mz00_orcl was spinning on the CPU for part of the time that it seemed hung. I guess it was 5 or 10 minutes, but I didn’t time it.

Seems to be up and working:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 2 13:00:59 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu May 02 2019 12:51:54 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Bobby

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs