DBA Blogs

Creation of a dynamic database trigger on a table

Tom Kyte - Fri, 2016-08-19 20:26
Hi Tom, I had asked a similar question earlier this month. For some reason, it is not letting me reply or update the same thread. Hence opening a new one. I wanted to provide additional details on my question and request your help. The requ...
Categories: DBA Blogs

Analytical question

Tom Kyte - Fri, 2016-08-19 20:26
Hi Tom, I have a PATIENT table with (fName, lName .... ETHNICITY, GENDER.) Another table XXX with some medical data. <b>I want to get the % of HispanicFemales% of total record in xxx table and the HispanicFemales% of Hispanic Ethnic in xxx tabl...
Categories: DBA Blogs

How can i measure thread usage in a 11g standard edition database

Tom Kyte - Fri, 2016-08-19 20:26
We have an 11g standard edition database. We are being faced with an upgrade to 12c standard edition 2 with its inherent thread limit. Within my database I can see sockets, cores and cpu usage but I need some advice on how this relates to the thread ...
Categories: DBA Blogs

Hybrid Histogram

Tom Kyte - Fri, 2016-08-19 20:26
Team, Was reading about Hybrid histogram from documentation <u>http://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm#TGSQL372</u> <code>demo@ORA12C> create table t as select * from all_objects; Table created. demo@ORA12C> column ow...
Categories: DBA Blogs

XML Parse error due to escape characters

Tom Kyte - Fri, 2016-08-19 20:26
Hi Tom, We have a process wherein we try to consume the XML and store it in a table as CLOB . These XMLs which we are getting are having the escape characters like '&' or '<' in the text part of the XML. When we try to parse the XML using XMLt...
Categories: DBA Blogs

insert only value of rows which has data from tabular form into database

Tom Kyte - Fri, 2016-08-19 20:26
Hello can you please help me how to create a PL/SQL process to insert only rows which has values from tabular form into database,not those rows which does not have value, below is the insert PL/SQL process that i created, but it insert empty rows al...
Categories: DBA Blogs

Bulk collect workaround for memory bug

Bobby Durrett's DBA Blog - Fri, 2016-08-19 16:42

A coworker passed a test script on to me that was failing with the following memory error:

ORA-04030: out of process memory when trying to allocate 4088 bytes (PLS CGA hp,pdzgM64_New_Link)

The error occurred when initializing a PL/SQL table variable with 7500 objects. Here is my sanitized version of the code:

CREATE OR REPLACE TYPE ARRAY_ELEMENT
AS
  OBJECT
  (
    n1 NUMBER,
    n2 NUMBER,
    n3 NUMBER,
    n4 NUMBER );
/

CREATE OR REPLACE TYPE MY_ARRAY
IS
  TABLE OF ARRAY_ELEMENT;
/

DECLARE
  MY_LIST MY_ARRAY;
BEGIN
  MY_LIST := MY_ARRAY(
    ARRAY_ELEMENT(1234,5678,1314,245234),
    ARRAY_ELEMENT(1234,5678,1314,245234),
    ARRAY_ELEMENT(1234,5678,1314,245234),
...
    ARRAY_ELEMENT(1234,5678,1314,245234),
    ARRAY_ELEMENT(1234,5678,1314,245234)
  );

The real code had different meaningful constants for each entry in the table. Here is the error:

8004      ARRAY_ELEMENT(1234,5678,1314,245234)
8005    );
8006  
8007  END;
8008  /
DECLARE
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 4088 bytes 
(PLS CGA hp,pdzgM64_New_Link)


Elapsed: 00:02:51.31

I wrapped the error code manually so it would fit on the page.

The solution looks like this:

create table MY_OBJECTS
  (
    o ARRAY_ELEMENT );

DECLARE
  MY_LIST MY_ARRAY;
BEGIN
 MY_LIST := MY_ARRAY( );
  
 insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
 insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
 insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
...
 insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
 insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
 insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));

 commit;
  
 SELECT o
   BULK COLLECT INTO MY_LIST
   FROM MY_OBJECTS; 

END;
/

Here is what the successful run looks like:

8004    insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
8005    insert into MY_OBJECTS values(ARRAY_ELEMENT(1234,5678,1314,245234));
8006  
8007    commit;
8008  
8009    SELECT o
8010      BULK COLLECT INTO MY_LIST
8011      FROM MY_OBJECTS;
8012  
8013  END;
8014  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:21.36
SQL> 

There is an Oracle document about this bug:

ORA-4030 (PLSQL Opt Pool,pdziM01_Create: New Set), ORA-4030 (PLS CGA hp,pdzgM64_New_Link) (Doc ID 1551115.1)

It doesn’t have using bulk collect as a work around. My situation could be only useful in very specific cases but I thought it was worth sharing it.

Here are my scripts and their logs: zip

This is on HP-UX Itanium Oracle 11.2.0.3.

Bobby

Categories: DBA Blogs

Digging into ADD SCHEMATRANDATA … what is #GoldenGate doing?

DBASolved - Fri, 2016-08-19 10:30

In my post on the differences between ADD TRANDATA and ADD SCHEMATRANDATA, I highlighted the differences between the two ways of adding supplemental logging to tables. One of the things I pointed out was that ADD SCHEMATRANDATA doesn’t add any supplemental log groups (SLG). Without SLGs then how does ADD SCHEMATRANDATA work? That was the question I was left with. So I started digging around to find the answer and I think you may be interested in this as well.

Tracing

In order to figure out what is actually going on within the database when ADD SCHEMATRANDATA is run, I had to trace the GoldenGate session. In order to do this, I first had to login to the database from GGSCI using “dblogin useridalias ggate”. After logging in, I had to identify the session and then setup tracing. This was done with the following scripts:

—SQL to identify the GGate session
select 'exec dbms_monitor.session_trace_enable(session_id=>'||sid||', serial_num=>'||serial#||', binds=>true, waits=true)' 
from v$session where username = 'GGATE';

—Start tracing of the session
exec dbms_monitor.session_trace_enable(session_id=>156, serial_num=>15799, binds=>true, waits=>true);
—Disable tracing after done
exec dbms_monitor.session_trace_disable(session_id=>156, serial_num=>15799);

Now with tracing enabled for the session, I’m able to trace the GoldenGate session from within GGSCI. At this time, I’m able to run DELETE SCHEMATRANDATA and ADD SCHEMATRANDATA against the schema I want to add the logging to.

All the last step of tracing, I need to disable the tracing (script above) of the GoldenGate user and identify the trace file. By default the trace file should be in $ORACLE_BASE/diag/rdbms/<db>/<db>/trace. In this directory, you need to identify the trace files for the session that was traced.

oracle >ls -ltr src12c_ora_23267.*
-rw-r-----. 1 oracle oracle   2788 Aug 15 16:09 src12c_ora_23267.trm
-rw-r-----. 1 oracle oracle 300531 Aug 15 16:09 src12c_ora_23267.trc

After you know the trace file, you can perform a TKPROF on the trace file.

oracle > tkprof src12c_ora_23267.trc src12c_ora_23267.tkprofs

This is generate a tkprofs file that will show specifics for the sessions, such as the SQL that is ran. This is what I’m more interested in, what SQL is ran with ADD SCHEMATRANDATA is called.

Quick Review of TKProf file

I’m not going to go into all the details that are in a tkprof generated file, but I took a look through the file trying to figure out what is going on when running the ADD SCHEMATRANDATA. As I was searching the file, I found a reference to LOGMNR$ALWAYS_SUPLOG_COLUMNS.

If you have kept up with the versions of Oracle GoldenGate, you will know that this is a reference to LogMiner and that Oracle is integrating the Oracle GoldenGate processes with it. This is also a hint to where to look, a.k.a Oracle Streams. As I continued to look through the tkprof file, I found a few references to a streams package – DBMS_CAPTURE_ADM; along with sql statements making calls to views like STREAMS$_PREPARE_DDL.

BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('soe','ALLKEYS_OFF');
  END;
BEGIN sys.dbms_capture_adm.ABORT_SCHEMA_INSTANTIATION('soe'); END;

At this point, it is safe to say, that Oracle is merging Oracle GoldenGate into Oracle Streams.

Review of Packages

Note: These are Oracle packages that are encrypted in the database. You can use UnwrapIt to view if needed. Output here is only for teaching purposes.

Now that I’m armed with what package the ADD SCHEMATRANDATA is calling, I can drill into what exactly is happening.

In taking a look at the DBMS_CAPTURE_ADM package, I wanted to look at the PREPARE_SCHEMA_INSTANTIATION procedure. I can quickly see that the procedure takes the schema_name and that defaults will be used for supplemental logging and container.

PROCEDURE PREPARE_SCHEMA_INSTANTIATION( 
 SCHEMA_NAME               IN VARCHAR2, 
 SUPPLEMENTAL_LOGGING      IN VARCHAR2 DEFAULT 'KEYS', 
 CONTAINER                 IN VARCHAR2 DEFAULT 'CURRENT') 
 IS 
 BEGIN 
 DBMS_CAPTURE_ADM_IVK.PREPARE_SCHEMA_INST_IVK( 
 SCHEMA_NAME => SCHEMA_NAME,  
 SUPPLEMENTAL_LOGGING => SUPPLEMENTAL_LOGGING, 
 CONTAINER => CONTAINER); 
 END;

After the schema is passed to the procedure, this procedure passes all three of the parameters to another package and procedure for execution. This package is DBMS_CAPTURE_ADM_IVK.PREPARE_SCHEMA_INST_IVK.

PROCEDURE PREPARE_SCHEMA_INST_IVK(
SCHEMA_NAME               IN VARCHAR2,
SUPPLEMENTAL_LOGGING      IN VARCHAR2 DEFAULT 'KEYS',
CONTAINER                 IN VARCHAR2 DEFAULT 'CURRENT')
IS
SUPP_LOG_LEVEL      BINARY_INTEGER;    
SYNCHRONIZATION     VARCHAR2(4) := 'LOCK';
CANON_CONTAINER     DBMS_ID;
BEGIN
  DBMS_LOGREP_UTIL.WRITE_TRACE('prepare_schema_inst_ivk()+ container: ' ||
  CONTAINER, DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
  COMMIT;

  SUPP_LOG_LEVEL := DBMS_CAPTURE_ADM_IVK.SUPP_LOG_LEVEL_CHECK(SUPPLEMENTAL_LOGGING, 
  IF DBMS_XSTREAM_GG_ADM.SYNCHRONIZATION IS NOT NULL 
  THEN
         SYNCHRONIZATION := UPPER(DBMS_XSTREAM_GG_ADM.SYNCHRONIZATION);
  END IF;

  IF (SYNCHRONIZATION != 'NONE' AND SYNCHRONIZATION != 'LOCK' AND
           SYNCHRONIZATION != 'WAIT') 
  THEN
           DBMS_LOGREP_UTIL.RAISE_SYSTEM_ERROR(
           DBMS_STREAMS_ADM.INVALID_PARAMETER_NUM,
           DBMS_LOGREP_UTIL.GET_PROC_USE_CONTEXT, 'SYNCHRONIZATION'); 
  END IF;

  DBMS_UTILITY.CANONICALIZE(CONTAINER, CANON_CONTAINER, 30);   
  IF CANON_CONTAINER = 'CURRENT' 
  THEN
     DBMS_LOGREP_UTIL.WRITE_TRACE('prepare schema objects for current' ||
            ' container: ', DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
     DBMS_CAPTURE_ADM_INTERNAL.PREPARE_SCHEMA_INSTANTIATION(
        SCHEMA_NAME, SYS_CONTEXT('USERENV','_USER'), SUPP_LOG_LEVEL,
        SYNCHRONIZATION, CANON_CONTAINER);
  ELSE
     DBMS_CAPTURE_ADM_INTERNAL.PREPARE_SCHEMA_INST_CDB(
            SCHEMA_NAME, CANON_CONTAINER,
            SYS_CONTEXT('USERENV','_USER'), SUPP_LOG_LEVEL,
           SYNCHRONIZATION);
  END IF;

  COMMIT;
  END;

In this procedure, you will notice that the input is all three of the parameters that are passed from the PREPARE_SCHEMA_INSTANTIATION procedure. Then this procedure precedes to check the supplemental logging and attempts to sync the tables with the DBMS_XSTREAM_GG_ADM package. After the tables have been synced, then the procedure calls another package and procedure to prepare (DBMS_CAPTURE_ADM_INTERNAL.PREPARE_SCHEMA_INSTANTIATION) the tables with the required supplemental logging level.

When looking at this procedure, it takes in five different parameters; two of which are defaults. After the procedure sets up tracing, it checks to see if the calling procedure is specifying GoldenGate. Once everything is confirmed and synchronization is complete, then the procedure grabs the CURRENT_SCN, waits for any inflight transactions and prepares the schema tables before exiting the package.

PROCEDURE PREPARE_SCHEMA_INSTANTIATION(
SCHEMA_NAME               IN VARCHAR2,
CANON_INVOKER             IN VARCHAR2,
SUPP_LOG_LEVEL            IN BINARY_INTEGER,
SYNCHRONIZATION           IN VARCHAR2 DEFAULT 'LOCK',
CONTAINER                 IN VARCHAR2 DEFAULT 'CURRENT')
IS
CANON_SCHEMA  DBMS_ID;
IDX           NUMBER :=0;
NAME_ARRAY    DBMS_UTILITY.NAME_ARRAY;
OWNER_ARRAY   DBMS_UTILITY.NAME_ARRAY;
CURRENT_SCN   NUMBER;
WFIT          BOOLEAN := FALSE;
RUN_TKLRWT1   BOOLEAN := FALSE;
SUPPLOG       BINARY_INTEGER := SUPP_LOG_LEVEL;
TEST_EVENT_LVL   BINARY_INTEGER := 0;
BEGIN
 DBMS_LOGREP_UTIL.WRITE_TRACE(
 'dbms_capture_adm_internal.prepare_schema_instantiation()+', DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
 DBMS_LOGREP_UTIL.WRITE_TRACE('schema_name='||SCHEMA_NAME,   DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
  DBMS_LOGREP_UTIL.WRITE_TRACE('supp_log_level='||SUPP_LOG_LEVEL, DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH); 
 DBMS_UTILITY.CANONICALIZE(SCHEMA_NAME, CANON_SCHEMA, 30);  

 IF ((SUPP_LOG_LEVEL = DBMS_STREAMS_DECL.SUPPLOG_ALLKEYS_ON) OR (SUPP_LOG_LEVEL = DBMS_STREAMS_DECL.SUPPLOG_ALLKEYS_OFF)) 
 THEN
    STORE_PREPARE_INFO(CANON_SCHEMA, SUPP_LOG_LEVEL);
    RETURN;
 END IF;
    DBMS_LOGREP_UTIL.WRITE_TRACE('dbms_capture_adm_internal.prepare_schema_instantiation()+ before read_ev', DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
    DBMS_SYSTEM.READ_EV(DBMS_LOGREP_UTIL.EXT_TRACE_EV, TEST_EVENT_LVL);

    IF (DBMS_XSTREAM_GG_ADM.IS_GOLDENGATE AND TEST_EVENT_LVL = 0) 
    THEN
     SUPPLOG := 0;
    END IF;
   DBMS_LOGREP_UTIL.WRITE_TRACE('dbms_capture_adm_internal.prepare_schema_instantiation()+ before prepare_ddl',DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
    PREPARE_DDL(CANON_SCHEMA, SUPPLOG);
    DBMS_LOGREP_UTIL.WRITE_TRACE('dbms_capture_adm_internal.prepare_schema_instantiation()+ before prepare_schema_tables',DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
	RUN_TKLRWT1 := RUN_WFIT_TEST;

    IF (UPPER(SYNCHRONIZATION) = 'NONE') 
    THEN
		PREPARE_SCHEMA_TABLES(CANON_SCHEMA, FALSE, CONTAINER);
    ELSIF (UPPER(SYNCHRONIZATION) = 'LOCK') 
    THEN
		PREPARE_SCHEMA_TABLES(CANON_SCHEMA, TRUE, CONTAINER);
    ELSIF (UPPER(SYNCHRONIZATION) = 'WAIT') 
    THEN
		WFIT := TRUE;
 		CURRENT_SCN  := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
        DBMS_CAPTURE_ADM_INTERNAL.WAIT_FOR_INFLIGHT_TXN(CURRENT_SCN, 'Prepare_schema_instantiation');
        PREPARE_SCHEMA_TABLES(CANON_SCHEMA, FALSE, CONTAINER);
    END IF;
        DONE_WFIT_TEST(WFIT, RUN_TKLRWT1);
    END;

Up to this point, the last three packages have been preparing the tables. Seems like a lot of preparing to get tables set for supplemental logging. Well the last call in the previous package called the procedure to prepare the tables. When I look at this procedure, I see that there is a call to a PREPARE_TABLE_INST procedure (getting a bit lazy on copying the procedure – don’t wan to put to much since this is Oracle’s code).

FOR I IN 1..IDX LOOP
BEGIN
   DBMS_LOGREP_UTIL.WRITE_TRACE('prepare_schema_tables():' || CANON_OWNER_ARRAY(I) || '.' || CANON_NAME_ARRAY(I), DBMS_LOGREP_UTIL.TRACE_FLAG_PUBLISH);
   PREPARE_TABLE_INST(CANON_OWNER_ARRAY(I), CANON_NAME_ARRAY(I),DBMS_STREAMS_DECL.SUPPLOG_NONE, LOCKING, CONTAINER);
EXCEPTION WHEN OTHERS THEN
   DBMS_LOGREP_UTIL.DUMP_TRACE('error:dbms_capture_adm_internal.prepare_table_inst('||'owner='||CANON_OWNER_ARRAY(I)||' name='||CANON_NAME_ARRAY(I));
END;

Now, when I go an look at the PREPARE_TABLE_INST procedure, I see that the procedure is calling a C package called “knlcpreptabinst” to set the supplemental logging on the tables associated with the schema.

PROCEDURE PREPARE_TABLE_INST(
CANON_OWNER                 IN VARCHAR2,
CANON_TABLE_NAME            IN VARCHAR2,
SUPPLEMENTAL_LOGGING_LEVEL  IN BINARY_INTEGER,
LOCKING                     IN BOOLEAN,
CONTAINER                   IN VARCHAR2) 
IS
EXTERNAL
 NAME "knlcpreptabinst"
 LIBRARY DBMS_LOGREP_LIB
 WITH CONTEXT
 PARAMETERS(CONTEXT,    
      CANON_OWNER       OCISTRING, CANON_OWNER       INDICATOR SB2,
      CANON_TABLE_NAME  OCISTRING, CANON_TABLE_NAME  INDICATOR SB2,
      SUPPLEMENTAL_LOGGING_LEVEL UB4,
      LOCKING           UB2,       LOCKING           INDICATOR SB2,
      CONTAINER         OCISTRING, CONTAINER         INDICATOR SB2)
LANGUAGE C;

Without drilling down into the C package, I cannot see exactly how the supplemental logging is added or where it is exactly stored in the database. I can only assume (we know what that means … lol), that it is in the data dictionary some where.

Looking for tables or views that may shed some light on this as well, I’ve found LOGMNR$SCHEMA_ALLKEY_SUPLOG that will show you the schema, if all keys are in supplemental log mode and if no validated pks are allowed. The following query is what I used to extract information about the SOE schema:

select * from LOGMNR$SCHEMA_ALLKEY_SUPLOG
where allkey_suplog = 'YES';

 

Output is as follows:

SCHEMA_NAME     ALL ALL
--------------- --- ---
SOE             YES NO

In drilling down further, after have a friend of mine pointed out a function to me (follow him on twitter -> @resetlogs). You can get down to the table level on supplemental logging when using ADD SCHEMATRANDATA. There is a log miner function that has to be called when using SQL to pull the correct information. This function is similar named to the table I referenced above … LOGMNR$ALWAYS_SUPLOG_COLUMNS.

This function takes two parameters. The first is the schema that holds the objects and the second is the table name. So in the following example, I can see that the ORDERS table of the SOE schema has supplemental logging added.

select * from table(logmnr$always_suplog_columns('SOE','ORDERS'));

OWNER           TABLE_NAME                     COLUMN_NAME                        INTCOL     SEGCOL    USERCOL
--------------- ------------------------------ ------------------------------ ---------- ---------- ----------
SOE             ORDERS                         ORDER_ID                                1          1          1
SOE             ORDERS                         ORDER_TOTAL                             6          6          6
SOE             ORDERS                         COST_OF_DELIVERY                       11         11         11
SOE             ORDERS                         DELIVERY_ADDRESS_ID                    13         13         13
SOE             ORDERS                         ORDER_DATE                              2          2          2
SOE             ORDERS                         CUSTOMER_CLASS                         14         14         14
SOE             ORDERS                         CUSTOMER_ID                             4          4          4
SOE             ORDERS                         ORDER_STATUS                            5          5          5
SOE             ORDERS                         PROMOTION_ID                            8          8          8
SOE             ORDERS                         ORDER_MODE                              3          3          3
SOE             ORDERS                         SALES_REP_ID                            7          7          7
SOE             ORDERS                         WAREHOUSE_ID                            9          9          9
SOE             ORDERS                         DELIVERY_TYPE                          10         10         10
SOE             ORDERS                         WAIT_TILL_ALL_AVAILABLE                12         12         12
SOE             ORDERS                         CARD_ID                                15         15         15
SOE             ORDERS                         INVOICE_ADDRESS_ID                     16         16         16

 

I know this has been a long post, but hopefully, I’ve been able to somewhat show how the ADD SCHEMATRANDATA command within GGSCI works and where you can see if supplemental logging is turned on for the selected schema. As I was trying to dig to the root of this issue, I found it interesting that so many packages are involved with setting the supplemental logging on a schema/tables; while identifying if it is enabled is not as easy as ADD TRANDATA. Where when you use ADD TRANDATA the tables are easily identified and can quickly see that the supplemental log groups have been added.

As Oracle GoldenGate for Oracle moves to a more integrated approach to replication, I think more items will be tied to the log miner and streams architecture.

Enjoy!!

@dbasolved
http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

Simplifying your mobile app UI design with Oracle MAF 2.3.2

As you may already know Oracle MAF 2.3.2 has been released and brought a bunch of new features including important support for IPv6 only networks on iOS (requirement from Apple that any apps...

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

How to automatically execute sql queries in SQl developer

Tom Kyte - Fri, 2016-08-19 02:06
Hi thatjeffsmith, I'm from Vietnam. I have a trouble need your help I want to run this queries in SQL developer automatically, frequency will be daily: spool D:\test.csv select /*csv*/ * from dwh.DEPARTMENT_TCB where rownum <= 10 / spool o...
Categories: DBA Blogs

Query index information is very slow

Tom Kyte - Fri, 2016-08-19 02:06
Hello, I do a <code> SELECT AIC.INDEX_NAME AS IndexName, CASE ALC.CONSTRAINT_TYPE WHEN 'P' THEN 'T' ELSE 'F' END AS IsPrimary, CASE ALC.CONSTRAINT_TYPE W...
Categories: DBA Blogs

Partitioning

Tom Kyte - Fri, 2016-08-19 02:06
I have a table that is partitioned on a date column. When I insert rows into the table and commit all is well. When I select from the table => select * from tableA partition(xyz); I get the number of rows I expect. Also if i select from the table...
Categories: DBA Blogs

New Time Model Clarifications

Tom Kyte - Fri, 2016-08-19 02:06
Good Saturday Morning, Gentlemen. I am getting ready to give a talk to a local Oracle User's group. One item that I plan to discuss with the club is the Time Model. I have a few questions that will help me understand it better. Where will t...
Categories: DBA Blogs

expdp/impdp with Full Database but need only METADATA

Tom Kyte - Fri, 2016-08-19 02:06
i want to do expdp and impdp of full database but condition is METADATA_ONLY. SOURCE DATABASE IS 11.1.0 and TARGET DATABASE IS 11.2.0. Please tell a. while exporting and importing which user should be used to export. b. FULL=Y, CONTENT=MET...
Categories: DBA Blogs

IF THEN ENDIF in SQL

Tom Kyte - Fri, 2016-08-19 02:06
In SQL-Server I could do a IF <Condition> BEGIN <SQLStatement> END example: IF Exists (select 1 from document where id=5) begin select * from sometable END in an SQL-Statement - notice: SQL-Statement! In Oracle as far as I know I could...
Categories: DBA Blogs

explicit Foreign Key constraints in DW

Tom Kyte - Fri, 2016-08-19 02:06
Hi Tom, In all the 3 Data Warehouse projects that I had worked on we never created any Foreign key constraints in the fact tables explicitly. When I asked for the reason the standard answer that I get is "It will impact the performance and N...
Categories: DBA Blogs

Links for 2016-08-18 [del.icio.us]

Categories: DBA Blogs

The rlwrap utility for DBA.

Pythian Group - Thu, 2016-08-18 08:36

I spend most of my time as a DBA in linux terminal and sqlplus. Everybody who works with oracle sqlplus knows about its power, but also about its limitations. For many years I have used the rlwrap utility developed by Hans Lub. It gives me command history, and the ability to edit my SQL Plus commands, and use auto completion if I set it up. In this post I will share some tips about installation and basic usage.

First we need to install the utility, and there are several options for that. We will check a few of them below. Please keep in mind that all of my examples are tested on Oracle Linux 6.

For the first one we need git, yum and automake packages. We will use the latest and greatest source code from the project site on GitHub: https://github.com/hanslub42/rlwrap and your standard Yum repository. Assuming you have connection to GitHub and your Yum repo.
Let’s run it step-by-step:

[root@sandbox ~]# yum install readline-devel
....
[root@sandbox ~]# yum install automake
....
[root@sandbox ~]# yum install git
....
[root@ovmcloud01 ~]# git clone https://github.com/hanslub42/rlwrap.git
Initialized empty Git repository in /root/rlwrap/.git/
remote: Counting objects: 1250, done.
remote: Total 1250 (delta 0), reused 0 (delta 0), pack-reused 1250
Receiving objects: 100% (1250/1250), 565.53 KiB, done.
Resolving deltas: 100% (867/867), done.
[root@ovmcloud01 ~]# cd rlwrap
[root@ovmcloud01 rlwrap]# autoreconf --install
configure.ac:32: installing `tools/config.guess'
configure.ac:32: installing `tools/config.sub'
configure.ac:34: installing `tools/install-sh'
configure.ac:34: installing `tools/missing'
src/Makefile.am: installing `tools/depcomp'
[root@ovmcloud01 rlwrap]# automake  --add-missing
[root@ovmcloud01 rlwrap]# ./configure
....
[root@ovmcloud01 rlwrap]# make install
....

That’s it. You have it installed in your system.

The second way is to compile it from source you have downloaded from http://utopia.knoware.nl/~hlub/uck/rlwrap/ . It may be useful if you don’t have connection to Yum and GitHub.
Keep in mind you will need GNU readline and ncurses libraries and headers installed in your system. So, we download the binaries, unpack it, compile and install.

[root@sandbox]$wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.42.tar.gz
....
[root@sandbox]$tar xfz rlwrap-0.42.tar.gz
[root@sandbox]$cd rlwrap-0.42
[root@sandbox]$./configure
[root@sandbox]$make install

The third way is to copy previously compiled rlwrap execution file and use it on a new system adding it to */bin directory in standard path.
It works if you have several similar, binary compatible systems and don’t want to spend time compiling the same binaries on each one.

[root@sandbox]$cd rlwrap-0.42
[root@sandbox]$ls -l src/rlwrap
-rwxr-xr-x. 1 root root 225023 Aug 16 12:49 src/rlwrap
[root@sandbox]$cp src/rlwrap /usr/local/bin/
[root@sandbox]$rlwrap --help
Usage: rlwrap [options] command ...

Options:
  -a[password prompt]        --always-readline[=password prompt]
  -A                         --ansi-colour-aware
.....

Of course you may consider to make your own rpm or use EPEL (Extra Packages for Enterprise Linux) yum repository and install it from there. Just keep in mind the version you get from EPEL may be slightly outdated.

[root@sandbox]$yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
[root@sandbox]$yum install rlwrap

Having the rlwrap installed you may find use for it.
Here some basic examples how you can use the utility:
Create an alias in your bashrc for sqlplus :

vi ~/.bashrc

and add

alias sqlp='rlwrap sqlplus'

The same you can do for rman :

alias rman='rlwrap rman'

For Oracle GoldenGate command line utility

alias ggsci='rlwrap ./ggsci' 

In rlwrap you can use ! and TAB to call list of commands or use prefix and CTRL+R to search for certain command in command history. Also you can create your own dictionary and use it for auto completion.
Let’s try to build some dictionary for auto-completion
I created a file “lsql.lst” with the following contents:

[oracle@sandbox ~]$ vi lsql.lst
~
select
from
where
and
update
insert
delete
tablespace
v$database

....
[oracle@sandbox ~]$alias sqlp="rlwrap -f lsql.lst sqlplus / as sysdba"
[oracle@sandbox ~]$ sqlp

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 17 15:36:04 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
.............................................
cdb> desc v$t 

— here we are pressing TAB and getting list of suggestions:

cdb> desc v$t 
table       tablespace
cdb> desc v$tablespace
 Name														   Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TS#															    NUMBER

We can make it even better. Let’s upload name for all dba_views
In our sqlplus session we run :

cdb> spool lsql.lst append
cdb> select name from V$FIXED_TABLE;
......
cdb>spool off

logoff and logon again to reload the file and try :

cdb> select * from V$PA -- TAB
PARALLEL_DEGREE_LIMIT_MTH  PARAMETER                  PARAMETER2                 PARAMETER_VALID_VALUES     PATCHES
cdb> select * from V$B -- TAB
BACKUP                       BACKUP_CONTROLFILE_SUMMARY   BACKUP_DATAFILE_SUMMARY      BACKUP_SET                   BACKUP_SYNC_IO               BSP                          BUFFERED_PUBLISHERS
BACKUP_ARCHIVELOG_DETAILS    BACKUP_COPY_DETAILS          BACKUP_DEVICE                BACKUP_SET_DETAILS           BGPROCESS                    BTS_STAT                     BUFFERED_QUEUES
BACKUP_ARCHIVELOG_SUMMARY    BACKUP_COPY_SUMMARY          BACKUP_NONLOGGED             BACKUP_SET_SUMMARY           BH                           BT_SCAN_CACHE                BUFFERED_SUBSCRIBERS
BACKUP_ASYNC_IO              BACKUP_CORRUPTION            BACKUP_PIECE                 BACKUP_SPFILE                BLOCKING_QUIESCE             BT_SCAN_OBJ_TEMPS            BUFFER_POOL
BACKUP_COMPRESSION_PROGRESS  BACKUP_DATAFILE              BACKUP_PIECE_DETAILS         BACKUP_SPFILE_DETAILS        BLOCK_CHANGE_TRACKING        BUFFER                       BUFFER_POOL_STATISTICS
BACKUP_CONTROLFILE_DETAILS   BACKUP_DATAFILE_DETAILS      BACKUP_REDOLOG               BACKUP_SPFILE_SUMMARY        BMAPNONDURSUB                BUFFER2
cdb> select * from V$B

You can see we have all “V$” views and it can be extremely handy when you don’t really have any time to search for a view name and only vaguely remember that you have a view to look up for certain information.

The rlwrap may not be most sophisticated program but it can make your life much easier. There may be a more advanced tool for sqlplus like SQLcl that provides a lot more options. But—the beauty of rlwrap is in its “lightness” and ability to work not only with sqlplus, but with practically any command line tool.

Categories: DBA Blogs

TRIGGER not dropping user in Oracle 11g?

Tom Kyte - Thu, 2016-08-18 07:46
Hi , i have database 11g , and i have user X and want to drop this user just once database startup . so i used this trigger : ---------------------------------------------------------------------------------------------------------- CREATE OR ...
Categories: DBA Blogs

diff between AS and IS in Subprogram syntax

Tom Kyte - Thu, 2016-08-18 07:46
IN The Syntax of Create procedure and function we have two keywords IS,AS what is the difference betwwen them how to use them.Belo I given A link https://livesql.oracle.com/apex/livesql/s/dqk6ejx3lpdaslymswdqfk347 By using is and as i can creat...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs