DBA Blogs

Loading email content into oracle table

Tom Kyte - Fri, 2019-05-17 21:46
Hi Tom, I have an interesting requirement, I want to load complete emails ( example outlook) in oracle tables. => When a mail content ( preview) is greater than 4000 than store in attachement table with name "long content" and store complete...
Categories: DBA Blogs

Ora-12560: TNS: protocol adapter error

Tom Kyte - Fri, 2019-05-17 21:46
I use a single instance 12.2C 64-bit Oracle database on a window server 2012R2. suddenly this error <i>ORA-12560: TNS:protocol adapter error</i> began to show when I try to enter the sqlplus. whatever I have searched the internet a lot for a soluti...
Categories: DBA Blogs

Manipulate the autogenerated names for types inside packages

Tom Kyte - Fri, 2019-05-17 21:46
Hey, if you create a type inside a package, this type is created in the database with a name like 'SYS_...'. Is there any possibility to affect/influence the auto generated name? Or do i can rename it? And how? Why I asked that? I work a lot w...
Categories: DBA Blogs

exporting packages,function etc. from one user to another.

Tom Kyte - Fri, 2019-05-17 21:46
Hi, For example X user have many packages,functions,procedures etc. And I want to delete some of them after copying to another user (Y). I mean I want to classify packages,functions etc... I can copy-paste by using Procedure Builder. But this way...
Categories: DBA Blogs

SYSDATE behavior in SQL and PL/SQL

Tom Kyte - Fri, 2019-05-17 21:46
Hello, My quess: there are two different SYSDATE functions ? one defined in STANDARD package and another one somewhere ?inside? Oracle. Example: SQL> select * from dual; D - X SQL> select sysdate from user_objects where rownum=1;...
Categories: DBA Blogs

Partner Webcast – Oracle Visual Builder Cloud Service: Web and Mobile App Development for all

Oracle Visual Builder focuses on simplifying development by providing a visual approach to application development and publishing. As a visual and declarative cloud environment it allows developing...

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

PLS-00436: implementation restriction error when using forall in update

Tom Kyte - Fri, 2019-05-10 19:26
I have a object as follows: create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr number,fordergnr number); and I have a a collection as: create or replace type claims_only_collection as table of claims_only; I have a...
Categories: 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

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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs