DBA Blogs

Delete 50 percent data from a table with billions of records.

Tom Kyte - Fri, 2016-09-02 14:06
Hi team, I have a nightmare recently, it comes along with a poor table design of our customer's database: A table named T_PRODUCT_TEST_DATA which has more than 3.6 billion records. What's worse, neither is this table a partitioned table nor has a DA...
Categories: DBA Blogs

Pluggable Database not open automatically

Tom Kyte - Fri, 2016-09-02 14:06
Some Days before I have Install Oracle Database New version with no error or warnings. Created 2 pluggable databases in the DB container. <code>SELECT name, open_mode from v$pdbs; NAME OPEN_MODE ------------...
Categories: DBA Blogs

Oracle Service Secrets: quiesce tactically

Pythian Group - Fri, 2016-09-02 10:18

In the last post of this series about Oracle net services, I talked about how services can help you identify performance issues faster and easier by tagging connections with service names. Today I am introducing you to the idea of temporarily disabling connections during maintenance with the help of services.

During deployments, testing or reorganizations it might be necessary to prevent clients from connecting to the database while still allowing access for DBAs to do their work. Some methods to do this include temporarily locking application user accounts or putting the database in quiesce mode. But with services, you now also have a more tactical approach to this issue.

My example assumes a single instance with two services DEMO_BATCH and DEMO_OLTP. And let’s assume that we need to temporarily disable batch services, maybe just to reduce system load due to those activities or maybe because we are reorganizing the objects used by the batch processes.

To disable a service in a single instance we can either remove it from the SERVICE_NAMES instance parameter or use the DBMS_SERVICE package.




PL/SQL procedure successfully completed.

New sessions using the service name will receive an ORA-12514 error when trying to connect:

brbook:~ brost$ ./sqlcl/bin/sql brost/******@

SQLcl: Release RC on Thu Aug 18 13:12:27 2016

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

  USER          = brost
  URL           = jdbc:oracle:thin:@
  Error Message = Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Existing sessions are allowed to continue

Note that stopping will only affect new connections. Existing sessions that used the DEMO_BATCH service are allowed to continue until they disconnect or you kill them. This gives you the flexibility of a grace period where you just wait for existing sessions to finish their work and disconnect by themselves.

no rows selected


-------------------- ------------------------------
Grid Infrastructure has option to force disconnects

If you are using grid infrastructure and manage services through srvctl this behaviour is basically the same but you get an extra “force” switch to also disconnect existing sessions while stopping a service.

[oracle@ractrial1 ~]$ srvctl stop service -db orcl42 -service racdemo_batch [-force]

[oracle@ractrial1 ~]$ srvctl stop service -h

Stops the service.

Usage: srvctl stop service -db <db_unique_name> [-service  "<service_name_list>"] [-serverpool <pool_name>] [-node <node_name> | -instance <inst_name>] [-pq] [-global_override] [-force [-noreplay]] [-eval] [-verbose]
    -db <db_unique_name>           Unique name for the database
    -service "<serv,...>"          Comma separated service names
    -serverpool <pool_name>        Server pool name
    -node <node_name>              Node name
    -instance <inst_name>          Instance name
    -pq                            To perform the action on parallel query service
    -global_override               Override value to operate on a global service.Ignored for a non-global service
    -force                         Disconnect all sessions during stop or relocate service operations
    -noreplay                      Disable session replay during disconnection
    -eval                          Evaluates the effects of event without making any changes to the system
    -verbose                       Verbose output
    -help                          Print usage

Creating extra services on a database allows you to stop and start them for maintenance which can be used as a convenient way to lock out only certain parts of an application while leaving user accounts unlocked to connect via different services.

Categories: DBA Blogs

Links for 2016-09-01 [del.icio.us]

Categories: DBA Blogs

Oracle 12c: Indexing JSON in the Database Part III (Paperback Writer)

Richard Foote - Fri, 2016-09-02 00:13
In Part I and Part II, we looked at how to index specific attributes within a JSON document store within an Oracle 12c database. But what if we’re not sure which specific attributes might benefit from an index or indeed, as JSON is by it’s nature a schema-less way to store data, what if we’re not entirely sure […]
Categories: DBA Blogs

New graph: Average Active Sessions per minute

Bobby Durrett's DBA Blog - Thu, 2016-09-01 17:25

I am working on a production issue. I do not think that we have a database issue but I am graphing some performance metrics to make sure. I made a new graph in my PythonDBAGraphs program.


It shows the average number of active sessions for a given minute. It prompts you for start and stop date and time. It works best with a relatively small interval or the graph gets too busy. Red is sessions active on CPU and blue is all active sessions. This graph is a production database today. Activity peaked around mid day.

It is kind of like the OEM performance screen but at least having it in Python lets me tinker with the graph to meet my needs. Check out the README on the GitHub link above if you want to run this in your environment.


Categories: DBA Blogs

Send data to oracle procedure using ref cursor

Tom Kyte - Thu, 2016-09-01 01:26
Hi Tom, In our application (.Net & Oracle) we have an address table with 20+ fields, and I was wondering if it's a good idea to pass address to SP using REF CURSOR insted of multiple params (SP below will be called from other SPs as well as from C...
Categories: DBA Blogs

Java Codes for inserting list of directories in database

Tom Kyte - Thu, 2016-09-01 01:26
Hi Tom, This is a java programming language and i'm going to insert the directory path and filename in DB but my problem is the directory path without filename doesnt insert in DB it supposed to be the column FILE_NAMES is already null but it does...
Categories: DBA Blogs

Hierarchical query

Tom Kyte - Thu, 2016-09-01 01:26
Hi: I have hierarchical query where i would like to know all parents, grand parents, their parents etc. i.e. as connect by value is changing i would like to know. I know in 9i we have sys_connect_by_path but i need that functionality in 8.1.7. Is...
Categories: DBA Blogs

How to split the big file contains personal data

Tom Kyte - Thu, 2016-09-01 01:26
Hi Tom, Good evening. Can yo please guide me how we can do below requirement. Input will take huge data file it's having personal any data it's not like fixed sized or delimited. It's like any format. this data have to split 1. Based on t...
Categories: DBA Blogs

Analyzing 27 TB of database for upgrade from ( to

Tom Kyte - Thu, 2016-09-01 01:26
Hi, We are planning to upgrade 27 terabyte of database from to we are not sure things to look before upgrading as this is very huge database and it is running from many years live for users. Please suggest things and scripts t...
Categories: DBA Blogs

Oracle Open World 2016 – What GoldenGate sessions are there?

DBASolved - Wed, 2016-08-31 23:00

Well, it is that time of year again; the streets of San Francisco will be crowded with members of the Oracle community! As everyone in the Oracle community descends onto the bay area, there will be excitement about the things that will be announced this year. Sure a lot of it is going to be about “cloud” and what direction Oracle is taking with their “cloud” strategy. Besides, “cloud” there will be a lot of good things to take in as well. As I look through the online session catalog for 2016, I’m interested in the topics related to Oracle GoldenGate.

This year there appears to be a good number of Oracle GoldenGate sessions at Oracle Open World, to be specific there are 21 scheduled during the event. I have listed a few of the ones I think will be interesting and will make an attempt to attend; always check the session catalog because what I think is good you may not.

  • CON6632 – Oracle GoldenGate for Big Data
  • CON7318 – Getting Started with Oracle GoldenGate
  • CON6551 – New Oracle GoldenGate 12.3 Services Architecture (beta stuff)
  • CON6633 – Accelerate Cloud Onboarding with Oracle GoldenGate Cloud Service
  • CON6634 – Faster Design, Development and Deployment with Oracle GoldenGate Studio (should be updated from last year session)
  • CON6558 – Best Practice for High Availability and Performance Tuning for Oracle GoldenGate
  • UGF616 – Oracle GoldenGate and Apache Kafka: A Deep Dive into Real-Time Data Streaming
  • THT7817 – Real-Time and Batch Data Ingestion into Data Lake with Oracle GoldenGate Cloud Service
  • UGF5120 – Oracle GoldenGate and Baseball: Five Fundamentals Before Jumping to the Cloud

As we are within two weeks of Oracle Open World, I hope everyone is ready to go and looking forward to seeing others from the community.



Filed under: General
Categories: DBA Blogs

Year as input in a date column

Tom Kyte - Wed, 2016-08-31 07:26
I have a user who needs the possibility to enter just a year-number (ex. 2016) in a view containing a column that is defined as a date). The user see this value in the view as just a year (by using substr). I have been trying to make a trigger whi...
Categories: DBA Blogs

Procedures in one procedure

Tom Kyte - Wed, 2016-08-31 07:26
Hi, I've created a procedure containing 4 others procedures (INSERT INTO Table...) CREATE OR REPLACE PROCEDURE ALLPROC AS BEGIN PROC1; PROC2; PROC3; PROC4; END ALLPROC; Can you tell me if those 4 procedures are running in parallell or on...
Categories: DBA Blogs

When I trying to insert data to customer table it gives " inconsistent datatypes: expected UDT got CHAR" error,But i have given the datatype of " depNo" as char(9) already.. Can anyone explain me how to fix this error? Thanks.

Tom Kyte - Wed, 2016-08-31 07:26
--------------------creating types-------------- create type currency_vaty as varray(5) of char(3) / create type depcatogory_ty as object( depName char(5), intRate number(2,2), minDeposit number(8), currencies currency_vaty ) / ...
Categories: DBA Blogs

Datatype Number results in Numeric overflow although value is small enough

Tom Kyte - Wed, 2016-08-31 07:26
Try following Test Cases: DECLARE x NUMBER := 1; BEGIN x := 1024 * 1024 * 1024 * 5; END; / -> ORA-01426: numeric overflow DECLARE x NUMBER := 1; BEGIN x := x * 1024; x := x * 1024; x := x * 1024; x := x * 5; END; /...
Categories: DBA Blogs

Blocks allocation to tables and segments

Tom Kyte - Wed, 2016-08-31 07:26
1) select s.segment_name,sum(s.blocks),sum(s.BYTES),sum(s.extents) from user_segments s where s.segment_name='TBL_1' group by s.segment_name; SEGMENT_NAME SUM(S.BLOCKS) SUM(S.BYTES) SUM(S.EXTENTS) TBL_1 5504 45088768 8 2) sel...
Categories: DBA Blogs

How to display zero as count if there is no record in data base in combination with Date column?

Tom Kyte - Wed, 2016-08-31 07:26
Hello, After executing the Query Month Starts from APR to SEP only data is available in database and displaying properly. If there is no data i would like to display Month and Count as 0 with the same result. Ex: Here January(01) month has no re...
Categories: DBA Blogs

High number of buffers to get one block

Tom Kyte - Wed, 2016-08-31 07:26
I have a query who's DBMS_XPLAN output looks like this: <code>------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | O...
Categories: DBA Blogs

Cannot update simple Number(19,2) column

Tom Kyte - Wed, 2016-08-31 07:26
PS - I'm using Toad 4 to do this..... I have a database that has a table called parts_master with many columns, including.... pn varchar2(40) list_price Number(19,2) Mfg_auto_key Number list_price_date(date) I have a column: select pn, l...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs