DBA Blogs

Moving Constraints from one table to another

Tom Kyte - Wed, 2016-06-29 00:06
Hi asktom team, I want to convert a NON - PARTITIONED table to LIST - partitioned table. So to do that I am doing the following: FOR EXAMPLE take ORDERS table: -Renaming the TABLE ORDERS to ORDERS_OLD. -Creating a table ORDERS same as ORDER...
Categories: DBA Blogs

What are possible meanings of error OCCI ORA-12560: TNS:protocol adapter error?

Tom Kyte - Wed, 2016-06-29 00:06
Hi, The Oracle error messages are not always very descriptive. Searching the internet I found couple reasons why we can get ORA-12560 error: 1) Oracle database instance is not started 2) ORACLE_HOME is not set (for version 10) What are other ...
Categories: DBA Blogs

Peformance issue with user_tab_partitions query

Tom Kyte - Wed, 2016-06-29 00:06
Hi, We have the below query which is taking lot of time....while reading the partition name for the given high_value of table table_1. Here P_SEQ is the input value that we are passing through a procedure to this query. SELECT to_char(x.parti...
Categories: DBA Blogs

Inserting a BLOB file into oracle using a pl/sql script

Tom Kyte - Wed, 2016-06-29 00:06
I cannot for the life of me figure out how to successfully insert a file into a blob column in a DB, using a PL/SQL script. Should be able to reuse this script indefinitely, preferably, with several files at the same time, but as long as I can do on...
Categories: DBA Blogs

getting error for NOLOGGING NOCACHE

Tom Kyte - Wed, 2016-06-29 00:06
Hi there, We have recently migrated our database from Oracle 9i to Oracle 11g. Now,One of our procedure is having query to create external table of oracle loader type. It also include NOLOGGING NOCACHE. This procedure was working properly in Oracl...
Categories: DBA Blogs

Can Oracle 8i client connect to Oracle 12c server?

Tom Kyte - Wed, 2016-06-29 00:06
Can Oracle 8i client connect to Oracle 12c server? If yes, please anybody can help me that what are the changes does in Oracle 8i client's file and Oracle 12c Server's file...
Categories: DBA Blogs

Generating Dates between two date ranges

Tom Kyte - Wed, 2016-06-29 00:06
<code>Hi Tom , I need to write a query which will give me a list of dates between two date ranges (start date and end date) . For Example if my start date is 12/01/2003 and end Date is 12/31/2003. i need to generate my output as Date ---...
Categories: DBA Blogs

Refresh Materialized view when there is records in the source table

Tom Kyte - Wed, 2016-06-29 00:06
Hi , Need help to refresh one materialize view only when there is records in the table. say example . create materialize view MABCD as select field1,field2,field3 from abcdef ; my requirement is if abcdef have records greater than zer...
Categories: DBA Blogs

Online redefination of table

Learn DB Concepts with me... - Tue, 2016-06-28 09:55

Online table redefinition of a table
 Lets create a table with some dependent objects for this setup.

DROP PROCEDURE get_description;
DROP VIEW redef_tab_v;
DROP SEQUENCE redef_tab_seq;
DROP TABLE redef_tab PURGE;

CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);

CREATE VIEW redef_tab_v AS
SELECT * FROM redef_tab;

CREATE SEQUENCE redef_tab_seq;

CREATE OR REPLACE PROCEDURE get_description (
  p_id          IN  redef_tab.id%TYPE,
  p_description OUT redef_tab.description%TYPE) AS
BEGIN
  SELECT description
  INTO   p_description
  FROM   redef_tab
  WHERE  id = p_id;
END;
/

CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := redef_tab_seq.NEXTVAL;
END;
/


Lets confirm that objects  are created 


COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB_BIR        TRIGGER             VALID
REDEF_TAB_PK         INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

6 rows selected.


Basic Online Table Redefinition

Now we perform an online table redefinition.

-- Check table can be redefined

EXEC DBMS_REDEFINITION.can_redef_table('ATOORPU', 'REDEF_TAB'); 
--- SCHEMA = ATOORPU, TABLE_NAME=REDEF_TAB


CREATE TABLE redef_tab2 AS
SELECT *
FROM   redef_tab WHERE 1=2;

-- Start Redefinition
EXEC DBMS_REDEFINITION.start_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');


-- Optionally synchronize new table with interim data before index creation
EXEC DBMS_REDEFINITION.sync_interim_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

-- Add new PK.
ALTER TABLE redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

-- Remove original table which now has the name of the new table
DROP TABLE redef_tab2;



Finally, we verify the status of the schema objects.


COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID


Note : The GET_DESCRIPTION procedure and REDEF_TAB_V view are still valid, but the REDEF_TAB_BIR trigger is gone. The trigger was still associated with the original table, renamed to REDEF_TAB2, so when the original table was dropped, the trigger was dropped with it.
Categories: DBA Blogs

Different between FGA and virtual private database?

Tom Kyte - Tue, 2016-06-28 05:46
I am very confused with some terms such as difference between Fine Grained Auditing and virtual private database?
Categories: DBA Blogs

UTL EMAIL (HTML)

Tom Kyte - Tue, 2016-06-28 05:46
Hi Tom, I have issue with my UTL Email, when i am sendint the email the text matter comming in same line: please check comments in the code where the issue: create or replace PROCEDURE ...
Categories: DBA Blogs

Split a int value in multiple rows based on percentage and in multiple of another number

Tom Kyte - Tue, 2016-06-28 05:46
Hi Tom, I have situation where i have to divide a integer in multiple rows depends on the %value given and the rounding specified. The value in ASSIGNMENT table needs to be divided in multiple of ROUNDING depends on the CONSIGNMENT records. In...
Categories: DBA Blogs

Optimal number of partitions

Tom Kyte - Tue, 2016-06-28 05:46
Tom, I have this table with around one million records. The table has the potential to grow to 2 million, 3 million or even more... I am planning on partitioning this table using hash partitioning on the most queried column. A few questions: ...
Categories: DBA Blogs

Adaptive Cursor Sharing

Tom Kyte - Tue, 2016-06-28 05:46
Hi Tom, I am aware, you have already clarified about the same subject many times. I have a table, which stores customer transaction information. Data load is happening continuously into the table. Since it is a transaction table, given a day,...
Categories: DBA Blogs

Oracle 10g upgrade

Tom Kyte - Tue, 2016-06-28 05:46
<code>Tom You mentioned that the AskTom site was recently upgraded to 10g. I take it therefore that you consider it to be of sufficient quality to run production code, even though the officially released version is only a couple of months old ? ...
Categories: DBA Blogs

Links for 2016-06-27 [del.icio.us]

Categories: DBA Blogs

datapump export using DBMS_DATAPUMP

Tom Kyte - Mon, 2016-06-27 11:06
Good afternoon, it is a beginning programmer. I had a problem with DBMS_DATAPUMP.data_filter. Data_filter not work. create PROCEDURE exp_tables_w_qfilter ( p_schema_name IN VARCHAR2, -- 'LTP' p_table_name IN VARCHAR2, -- 'AUDITTABLE_TEST' p_t...
Categories: DBA Blogs

Transpose Rows into Columns

Tom Kyte - Mon, 2016-06-27 11:06
I have a table like: Client Process Date Status A B 21-june Y C D 22-june N A B 22-june N...
Categories: DBA Blogs

2 highest salary department wise ..alternate method

Tom Kyte - Mon, 2016-06-27 11:06
<b>select * from employees e1 where :parameter=(select count(distinct e2.salary) from employees e2 where e1.salary<=e2.salary);</b> --<i>hi can anyone explain me this query .i know this can be solve using rank,dense_rank but confused while ...
Categories: DBA Blogs

Services -- 4 : Using the SERVICE_NAMES parameter (non-RAC, PDB)

Hemant K Chitale - Mon, 2016-06-27 09:55
In my previous blog post, I have demonstrated using DBMS_SERVICE to create and start services.

But there is another way.  Using the SERVICE_NAMES parameter.  Going by most google references, SERVICE_NAME seems to be more popular than DBMS_SERVICE.

Can it be used in a Pluggable Database ?  Let' try.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 22:39:59 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 973082144 bytes
Database Buffers 654311424 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:43:36

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=14129))
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$


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

CON_ID
------------------------------
3
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> alter system set service_names='NEW_SVC_1,NEW_SVC_2';
alter system set service_names='NEW_SVC_1,NEW_SVC_2'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> !oerr ora 65040
65040, 00000, "operation not allowed from within a pluggable database"
// *Cause: An operation was attempted that can only be performed in the root
// container.
// *Action: Switch to the root container to perform the operation.
//

SQL>


So, apparently, ORA-65040 means that we can't define SERVICE_NAMES for a PDB.

SQL> connect sys/oracle as sysdba
Connected.
SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set service_names='NEW_SVC_1,NEW_SVC_2';

System altered.

SQL>

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:53:56

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=14129))
Service "NEW_SVC_1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_SVC_2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ tnsping NEW_SVC_1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:54:57

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_SVC_1)))
OK (30 msec)
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@new_svc_1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 22:55:04 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: system/oracle@new_svc_1
Last Successful login time: Mon Jun 27 2016 22:44:13 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>


The new Services are actually running in the ROOT, not in the PDB .  Unlike my earlier blog post where I used the new services to connect to the PDB.

Therefore, for a PDB environment, I'd use DBMS_SERVICE as I've demonstrated here and here.
.
.
.



Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs