DBA Blogs

Create Type by using %type columns

Tom Kyte - Wed, 2017-07-12 13:26
Hi TOM, I want to write an extract utility, which will get data from selected columns of multiple tables so planning to use pipeline function which will return a ORACLE TYPE. To create type, I would like give reference of column type from source...
Categories: DBA Blogs

Difference between stale object result from *_tab_statistics and gather_schema_stat with "LIST STALE"

Tom Kyte - Wed, 2017-07-12 13:26
I am trying to find all stale objects. As I understand there are two ways and both should return same result. Before starting I first did a flush monitoring <code> begin dbms_stats.flush_database_monitoring_info; end; / </...
Categories: DBA Blogs

Pivot with total

Tom Kyte - Wed, 2017-07-12 13:26
<code>create table ticket1 (ticketid number, tcktname varchar2(10), status varchar2(10) ); INSERT INTO ticket1 VALUES (101,'bug','open'); INSERT INTO ticket1 VALUES (102,'bug','close'); INSERT ...
Categories: DBA Blogs

Summarizing data over time - by time interval

Tom Kyte - Wed, 2017-07-12 13:26
Hello I have an application that gathers and stores data over time. Because of the applications reliance on the network and other functions the data is gathered at irregular intervals. example table TimeStamp Object Value --------- ...
Categories: DBA Blogs

SQL over PL/SQL

Tom Kyte - Wed, 2017-07-12 13:26
Hi Team, Could you please have a look at below scenario: I have 3 tables: select * from tab_login_details; select * from tab_request; select * from tab_access; Basically i need output as below: FK_TB_LOGIN_MASTER FK_TB_COMPANY_DETAILS FL...
Categories: DBA Blogs

1000 Column Limit populating a collection (ORA-00939)

Tom Kyte - Wed, 2017-07-12 13:26
Hi, I have a need to work with a collection of composite data type with more than 1000 columns in it. Here is the sample code below for collection with composite data type of 2 columns. <code> CREATE OR REPLACE TYPE obj_typ1 AS OBJECT (col...
Categories: DBA Blogs

Log Buffer #516: A Carnival of the Vanities for DBAs

Pythian Group - Wed, 2017-07-12 11:02

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

12.2 New Feature: the FLEX ASM disk group part 2

Oracle ASM in Azure corruption – follow up

Set-based processing

ADF 12c BC Proxy User DB Connection and Save Point Error

Enabling A Modern Analytics Platform

SQL Server:

Batch SSIS pkg execution from Business Intelligence Development Studio

Find Database Connection Leaks in Your Application

Troubleshooting CPU Performance on VMware

SQLskills Wait Types Library now shows SentryOne data

PowerShell Tool Time: The Tool Framework

MySQL:

Installing Zabbix into Azure using a MySQL PaaS

Streaming Global Cyber Attack Analytics with Tableau and Python

Thread Statistics and High Memory Usage

On slave_parallel_workers and the logical clock

RDS / Aurora OS monitoring with Monyog v8.1.0

Categories: DBA Blogs

Is there a possibility to use db_link dynamically without using cursor and execute immediate?

Tom Kyte - Tue, 2017-07-11 19:06
Hi, I would like to know if I am able to implement db_link dynamically without using cursor or execute immediate? I have 2 tables stored in different location which are accessible via db_link. These 2 tables are identical in structure and the data c...
Categories: DBA Blogs

How to use db_link dinamically

Tom Kyte - Tue, 2017-07-11 19:06
Hi Tom, Hope everything is O.K. for you ...... You know, I am extracting segment_name information for several databases and I am inserting information in a repository table. I am using next cursor to look for every database: FOR x in (SEL...
Categories: DBA Blogs

SP2-0308: cannot close spool file

Tom Kyte - Tue, 2017-07-11 19:06
After run a script for check my db tablespaces, the error log is SP2-0308: cannot close spool file, how to solve the problem? + + date +%Y%m%d today=20170710 + . /export/home/oracle/.profile + 1> /dev/null 2>& 1 + sqlplus -s /as sysdba + 0<< ...
Categories: DBA Blogs

Get previous non-null value of a column

Tom Kyte - Tue, 2017-07-11 19:06
Hi Team, i have requirement like this , can you help me on this <code>col1 col2 wk6 1 wk5 null wk4 3 wk3 null wk2 null wk1 5</code> i need o/p like below . whenever null value will come it should take it ...
Categories: DBA Blogs

How to use bind variables

Tom Kyte - Tue, 2017-07-11 00:46
I am trying to use bind variables for the 1st time. I have a part of the code here from a test stored procedure- Declare x number; y number; z number; a date; b date; c number; d number; e number; execute immediate 'insert...
Categories: DBA Blogs

Build single SQL for multiple condition

Tom Kyte - Tue, 2017-07-11 00:46
Hi Connor/Chris, Can you pleas help to build a query based on below inputs and condition: table structe is shared in liveSQL as well. <code>DROP TABLE TEST_LOGIN_MASTER; DROP TABLE TEST_LOGIN_REQUEST; DROP TABLE TEST_LOGIN_ACCESS; CREAT...
Categories: DBA Blogs

12c MultiTenant Posts -- 6 : Partial (aka Subset) Cloning of PDB

Hemant K Chitale - Mon, 2017-07-10 09:27
Note : This is a 12.2 feature.

Normally, if you clone a PDB, you'd get a full copy with all the tablespaces.  Now, in 12.2, you can exclude non-essential tablespaces by specifying USER TABLESPACES -- those that you want cloned.  (SYSTEM, SYSAUX and Local UNDO will certainly be cloned).

Let me start with the "NEWPDB" PDB (that I've used in previous examples) that has one more schema and tablespace:

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:52:55 2017

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

Last Successful login time: Mon Jul 10 2017 11:04:00 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> col owner format a8
SQL> col segment_name format a30
SQL> col tablespace_name format a8
SQL> select owner, segment_name, tablespace_name
2 from dba_segments
3 where tablespace_name like '%DATA'
4 order by 1,2
5 /

OWNER SEGMENT_NAME TABLESPA
-------- ------------------------------ --------
HEMANT BIN$UVb24iaCIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT BIN$UVb24iaIIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT HKC_STORE_FILE MYDATA
HEMANT I MYDATA
HEMANT OBJ_LIST MYDATA
HEMANT SYS_IL0000073525C00003$$ MYDATA
HEMANT SYS_IL0000073532C00003$$ MYDATA
HEMANT SYS_IL0000073535C00003$$ MYDATA
HEMANT SYS_LOB0000073525C00003$$ MYDATA
HEMANT SYS_LOB0000073532C00003$$ MYDATA
HEMANT SYS_LOB0000073535C00003$$ MYDATA
HEMANT T MYDATA
HR EMPLOYEES HRDATA

13 rows selected.

SQL>
SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
1 Hemant Chitale
06-JUL-17 1 15000 hemant@mydomain.com


SQL>


Besides, the HEMANT objects in the MYDATA tablespace, I now have HR owning an EMPLOYEES table in the HRDATA tablespace.

Now, I want to clone the NEWPDB tablespace but want to exclude HR data.

First, I set a target location for the datafiles.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:57:33 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter db_create_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter session set db_create_file_dest='/u02/oradata';

Session altered.

SQL>


Next, I create my Partial (or SubSet) Clone PDB:

SQL> create pluggable database NONHR from NEWPDB user_tablespaces=('MYDATA');

Pluggable database created.

SQL>
SQL> select con_id, file#, name
2 from v$datafile
3 order by 1,2
4 /

CON_ID FILE#
---------- ----------
NAME
--------------------------------------------------------------------------------
1 1
/u01/app/oracle/oradata/orcl12c/system01.dbf

1 3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

1 7
/u01/app/oracle/oradata/orcl12c/users01.dbf

1 15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

2 5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

2 6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

2 8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

3 9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

3 10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

3 11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

3 12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

3 13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf

3 14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

4 16
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

4 17
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

4 18
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1

4 19
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

4 20
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

5 21
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

5 22
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

5 23
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf

5 24
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf


22 rows selected.

SQL>
SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /

CON_ID
----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
2
PDB$SEED
READ ONLY

3
ORCL
READ WRITE

4
NEWPDB
READ WRITE

5
NONHR
MOUNTED


SQL>
SQL> alter pluggable database nonhr open;

Pluggable database altered.

SQL>


I can identify the new PDB "NONHR" as CON_ID=5.
Note that in the CREATE PLUGGABLE DATABASE command with the USER_TABLESPACES clause, I can also specify either of COPY, NOCOPY, MOVE, NO DATA or even SNAPSHOT COPY.  This is the simplest Subset Clone that is a Copy with Data.

Let's create the TNSNAMES.ORA entry for NONHR:

NONHR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nonhr)
)
)


Let's now connect to NONHR and confirm its contents.

SQL> connect system/oracle@NONHR
Connected.
SQL> show con_id

CON_ID
------------------------------
5
SQL> show con_name

CON_NAME
------------------------------
NONHR

SQL> select tablespace_name
2 from dba_tablespaces
3 order by 1
4 /

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'HRDATA'
3 /

no rows selected

SQL> select owner, segment_name, segment_type
2 from dba_segments
3 where tablespace_name = 'HRDATA'
4 /

no rows selected

SQL>
SQL> select tablespace_name, file_name
2 from dba_data_files
3 order by 1
4 /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
MYDATA
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf

SYSAUX
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

SYSTEM
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

UNDOTBS1
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf


SQL>
SQL> select segment_name, segment_type
2 from dba_segments
3 where owner = 'HR'
4 /

no rows selected

SQL> select username
2 from dba_users
3 where username = 'HR'
4 /

USERNAME
--------------------------------------------------------------------------------
HR

SQL>
SQL> select object_name, object_type
2 from dba_objects
3 where owner = 'HR'
4 /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
EMPLOYEES
TABLE


SQL>
SQL> select owner, segment_name
2 from dba_segments
3 where tablespace_name = 'MYDATA'
4 /

OWNER SEGMENT_NAME
-------- ------------------------------
HEMANT BIN$UVb24iaCIE/gUwEAAH/WaQ==$0
HEMANT BIN$UVb24iaIIE/gUwEAAH/WaQ==$0
HEMANT HKC_STORE_FILE
HEMANT I
HEMANT OBJ_LIST
HEMANT SYS_IL0000073525C00003$$
HEMANT SYS_IL0000073532C00003$$
HEMANT SYS_IL0000073535C00003$$
HEMANT SYS_LOB0000073525C00003$$
HEMANT SYS_LOB0000073532C00003$$
HEMANT SYS_LOB0000073535C00003$$
HEMANT T

12 rows selected.

SQL>
SQL> select count(*) from hemant.obj_list;

COUNT(*)
----------
145282

SQL>


So, what has been copied to the NONHR PDB?  The HRDATA Tablespace, but not the DataFile.  The HR User and Table (definition only, no data), but not the Segment.
However, for the MYDATA Tablespace that was identified as a USER_TABLESPACE in the CREATE PLUGGABLE DATABASE statement, the Tablespace, Datafile, User, Table and Segment have all been copied.

Therefore, NONHR does not have the HR data!   I can drop the User and Tablespace.

SQL> drop tablespace hrdata including contents;

Tablespace dropped.

SQL> drop user hr;

User dropped.

SQL>


However, HR is still present in NEWPDB where NONHR was cloned from:

SQL> connect system/oracle@NEWPDB
Connected.
SQL> select owner, segment_name
2 from dba_segments
3 where tablespace_name = 'HRDATA'
4 /

OWNER SEGMENT_NAME
-------- ------------------------------
HR EMPLOYEES

SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
1 Hemant Chitale
06-JUL-17 1 15000 hemant@mydomain.com


SQL> show con_id

CON_ID
------------------------------
4
SQL> show con_name

CON_NAME
------------------------------
NEWPDB
SQL>
SQL> select tablespace_name, file_name
2 from dba_data_files
3 order by 1
4 /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
HRDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

MYDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SYSAUX
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

SYSTEM
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

UNDOTBS1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1


SQL>


So, 12.2 introduces the ability to create a clone PDB database that is a SubSet (i.e. selected User Tablespaces data) of an existing PDB.

(Note : NEWPDB is in /u03 where it was moved from /u02 earlier as a Relocated Database while NONHR is in /u02 where it was created with OMF based on DB_CREATE_FILE_DEST).
.
.
.

Categories: DBA Blogs

Convert plsql output into xml format

Tom Kyte - Mon, 2017-07-10 06:26
Hi AskTOM Team, <b> I have the below code, I got the desired output. But I want to convert the output into XML format. Please send the code for covert xml format</b> The output of the below code is table datas, I want to convert the datas int...
Categories: DBA Blogs

How to use spool command to save sql script resultset to a file ,and filename include 'date format' ?

Tom Kyte - Mon, 2017-07-10 06:26
Hi, my sql script file is, <code> SET heading ON SET echo OFF SET feedback OFF SET trimspool ON SET newpage NONE SET verify OFF SET define OFF SET termout OFF SET timing OFF ...
Categories: DBA Blogs

Find references to utl_mail

Tom Kyte - Mon, 2017-07-10 06:26
I want to find stored procedure name in my database where in stored procedure have UTL_MAIL package can u guide me please
Categories: DBA Blogs

single column to multi rows based on repetition value

Tom Kyte - Mon, 2017-07-10 06:26
hi i have a sample scenario,i have a table called cust which is like below <code>select * from cust; name m1 m2 m3 ravi maruti honda toyota john honda toyota null</code> i want the output like below <...
Categories: DBA Blogs

12.2 Introduction to Real-Time Materialized Views (The View)

Richard Foote - Mon, 2017-07-10 02:23
Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which […]
Categories: DBA Blogs

regexp_replace

Tom Kyte - Sat, 2017-07-08 17:46
Hi Tom, Waiting since long, lucky today to get a chance. Need your expertise in following: I have a table with following structure and data: DROP table vn_rep; CREATE TABLE vn_rep ( ID NUMBER(5) NOT NULL,...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs