DBA Blogs

Enabling DB_CACHE_SIZE for DB

Tom Kyte - Sun, 2016-09-04 21:06
Hi, We have DB with enabled AMM (Automatic Memory Management) Version : Oracle Database 11g Enterprise Edition Release - 64bit Parameters: <code>sys@XXX_DB1> show parameter target NAME TYPE V...
Categories: DBA Blogs

oracle utl_smtp issue

Tom Kyte - Sun, 2016-09-04 21:06
Dear Tom: I am testing with utl_smtp, my platform is oracle + Windows Server 2000, my windows server is a member of dc, in our dc, we have exchange server as mail server, I issue the following command within sqlplus, it can work, de...
Categories: DBA Blogs

Using ssl

Tom Kyte - Sun, 2016-09-04 21:06
my connection in tnsnames.ora has tcps as protocol . Does this mean i am using ssl connection and messages are sent encrypted? Also, i have wallet with self signed certificate in client and server for my development environment. Is this requied ...
Categories: DBA Blogs

ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired

VitalSoftTech - Sun, 2016-09-04 19:32
What is the cause of the error ORA-00054 "resource busy and acquire with NOWAIT specified or timeout expired"?
Categories: DBA Blogs

Implicit cursor (SELECT INTO...) behavior

Tom Kyte - Fri, 2016-09-02 14:06
When using SELECT INTO... and have multiple rows returning by query, if the target variable declared strongly (e.g....%TYPE), it keeps the result of a fetch, if declared weakly (e.g....NUMBER), it keeps nothing for the same case. What is going? ...
Categories: DBA Blogs

String Pattern Search...

Tom Kyte - Fri, 2016-09-02 14:06
Hi, I am trying to define a regular expression pattern which satisfies the following criteria for string match: String Length: Maximum 13 characters Allowed Characters: Any digit (0-9) and ":" Condition: ":" can occur only once, digits can re...
Categories: DBA Blogs

Tricky Sequence value requirement thru Trigger

Tom Kyte - Fri, 2016-09-02 14:06
Hi Wonderful Team, I have an oracle sequence say s. I have table t ( id number , n number ); I need to insert the Id column with the sequence s such that the value is the same for the entire transaction thru a "Trigger" So this Id change...
Categories: DBA Blogs

Cannot create the method in customer type.Can anyone explain me the reason .Thanks

Tom Kyte - Fri, 2016-09-02 14:06
create type deposit_ty2 as object( depNo number, depCatagory ref depcatagoey_ty2, amount number, period number ) / create type deposit_ntty2 as table of deposit_ty2 / create type address_tyy as object( homeNo number, street char(14), ...
Categories: DBA Blogs

Materialized Views

Tom Kyte - Fri, 2016-09-02 14:06
Hi, I have a scenario as below. a) Table A with 10 columns of which 3 columns form a composite primary key b) A Materialzed view is created on top of the Table A with primary key enabled and fast refresh c) Materilized view log is also crea...
Categories: DBA Blogs

Avoid TRUNC and using between on sysdate

Tom Kyte - Fri, 2016-09-02 14:06
Hi , I have the below query where am using TRUNC on the column2 filed which is increasing the performance delay. so need to avoid the TRUNC on this check and need to use BETWEEN operator. Here SYSTEMDATE is the currentdateandtime. So ho...
Categories: 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


Subscribe to Oracle FAQ aggregator - DBA Blogs