Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

While there are several tools provided by OEM to monitor database performance, some are most useful than others. I believe the reports are the value added (AWR, ADDM, and ASH), these measure performance metrics and statistic over an interval of time. Recently my management directed me to provide step-by-step procedures, to our customer’s SysAdmins to deploy OEM to look at the screens, graphs, pie charts during an 12-36 hour customer event to monitor the database.  I think this was ridiculous and ill-advised. It is my opinion the important factor of database performance would be to measure performance statistics over a specific interval of time.  Please talk me down, as I think watching graph lines with web and flow is almost meaningless , or give me some ammunition to justify my stance.  I can somewhat understand our OS people interested in CPU, Memory, I/O; but beyond that …….
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

How would I write an insert statement to only insert a record in a department table only if a specific order number doesen't exisit?
For example, I have department 12345 and it has order numbers 20, 30, 40, 70, and 90.
I need to insert a record for order number 80 with the necessary values?
0
I have  attached a notepad with all the info.  Thank you for all your help.
Code.txt
0
In Visual Studio I want to build an application to run as a 32 bit application running against an Oracle database.  Now, After I build and run the application against Oracle, I am getting the below error message. I verified that I have a 32 bit oracle driver installed by running odbcad32 and on the "Drivers" tab verifying that SQLORA32.dll is listed.

ERROR: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit client components installed.

I need some expert help on why I am getting this error message when running against Oracle
0
Hello,
I want to make with an existing database and tables an ER-Diagram like mySQL Workbench.
The tables (over 200) are displayed overlapped in mySQLWorkbench. So, I am looking for an alternative.
I am using MariaDB/HeidiSQL.
Which software is free and has these functions?

* Import ERD as png and pdf?
*  Reverse Engineering (make from an existing database a ERD)
* Triggers should also be displayed in the ERD
0
Hello,
the follow SQL statement works perfectly. However I now need to add some new logic which I am struggling to do.

SELECT
	"Wrk Ctr",
	"Op Seq",
	"Order #",
	"Item #",
	"Qty started",
	"Cur Qty Open",
	"W/O Create Date",
	"Cur Requested Date",
	"Planned Start Date",
	"Actual Start Date (40)",
	"Actual Start Time (40)",
	"WO Seq",
	"Schedule Variance",
	"Schedule adhered to Y/N",
	"Cat Code",
	"Current Status",
	"Planner #",
	"Master Planning family",
	"Family Name",
	"Site",
	"Bus Unit"
from (
	SELECT
		wlmcu as "Wrk Ctr",
		wlopsq as "Op Seq",
		wadoco as "Order #",
		walitm as "Item #",
		syuorg/100 as "Qty started",
		wauorg/100 as "Cur Qty Open",
		to_char(to_date((watrdj+ 1900000),'yyyyddd')) as "W/O Create Date",
		to_char(to_date((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
		to_char(to_date((t2.syupmj+ 1900000),'yyyyddd')) as "Actual Start Date (40)",
		to_char ( to_date ( LPAD(SYTDAY,6,0), 'HH24MISS'), 'HH:MI:SS AM')  as "Actual Start Time (40)",
		to_char(to_date((t2.systrt+ 1900000),'yyyyddd')) as "Planned Start Date",
		t2.syupmj-systrt as "Schedule Variance",
		CASE WHEN systrt=syupmj or syupmj=0 and TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000<= wastrt THEN 'Y' ELSE 'N' END  as "Schedule adhered to Y/N",
		wawr10 as "Cat Code",
		waresc as "WO Seq", TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000 ,
		wasrst as "Current Status",
		ibanpl ||'- '|| abalph as "Planner #",
		ibprp4 || '-'|| drdl01 as "Master Planning family",
		

Open in new window

0
My application is experiencing very frequent database locks, getting ORA-02049 distributed timeout locks.  Of course our J2EE developers point to the DBAs, and my DBA team is pointing to bad code produced by inexperienced JAVA coders.  Most of the Java software developers have limited knowledge of how a RAC distributed database works. And as you can expect Management is furious.

How can we nail down any locks in the database?  How can we query gv$lock and determine, what query/tables are complicit in the application.  We need to quickly identify either the offending code, or badly formed queries, or combination of both.  I have found these specific queries, but need to know how to take this 2-steps forward and get the query that is part of the lock.  We are running under Solaris, with a 3-node Oracle 12.1 RAC database.  Things are really bad, please help.

select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';

Show sessions waiting for a TX lock:
select * from v$lock where type='TX' and request>0;

Show sessions holding a TX lock:
select * from v$lock where type='TX' and lmode>0;
0
hi,

how to ONLY import oracle trigger, SP, view, function, sequence to Oracle DB with existing table and data untouch ?
0
Hi.
Below query will take 300 hrs for 50 million rows here is explain plan how can we optimize it

Plan
SELECT STATEMENT  ALL_ROWSCost: 8,365,085  Bytes: 14,653,830,562  Cardinality: 111,861,302  							
	2 TABLE ACCESS BY INDEX ROWID BATCHED TABLE ICRS_WH.D_BUSINESS_LINE Cost: 2  Bytes: 17  Cardinality: 1  						
		1 INDEX RANGE SCAN INDEX ICRS_WH.BSNSLINE_IN Cost: 1  Cardinality: 1  					
	15 WINDOW SORT  Cost: 8,365,085  Bytes: 14,653,830,562  Cardinality: 111,861,302  						
		14 WINDOW SORT  Cost: 8,365,085  Bytes: 14,653,830,562  Cardinality: 111,861,302  					
			13 WINDOW SORT  Cost: 8,365,085  Bytes: 14,653,830,562  Cardinality: 111,861,302  				
				12 HASH JOIN RIGHT OUTER  Cost: 156,779  Bytes: 14,653,830,562  Cardinality: 111,861,302  			
					6 VIEW VIEW ICRS_WH.index$_join$_003 Cost: 13  Bytes: 26,819  Cardinality: 2,063  		
						5 HASH JOIN  	
							3 INDEX FAST FULL SCAN INDEX (UNIQUE) ICRS_WH.OFFC_UK Cost: 8  Bytes: 26,819  Cardinality: 2,063  
							4 INDEX FAST FULL SCAN INDEX ICRS_WH.OFFICE_IN2 Cost: 9  Bytes: 26,819  Cardinality: 2,063  
					11 HASH JOIN RIGHT OUTER  Cost: 156,400  Bytes: 13,199,633,636  Cardinality: 111,861,302  		
						7 TABLE ACCESS FULL TABLE ICRS_WH.D_BUSINESS_LINE Cost: 2  Bytes: 105  Cardinality: 15  	
						10 HASH JOIN RIGHT OUTER  Cost: 156,033  Bytes: 12,416,604,522  Cardinality: 111,861,302  	
							8 TABLE ACCESS FULL TABLE ICRS_WH.D_INTERVIEW_SERVICE_ZONE Cost: 2  Bytes: 77  Cardinality: 11  
							9 TABLE 

Open in new window

0
Can I do something like this, without using case statement, and be able to change these values to
those listed in the replace columns, like whenever 1,2,7,8 appears, then SE, if NE,SE,NW,SW appears then All and so on and so forth else col1 end;


SELECT COL1 from dual
Order by decode(1,2,7,8,3,4,5,6,9,10,15,16,11,12,13,14, 'ALL') ,
decode(1,2,7,8, "SE"),
decode(3,4,5,6, "SW" ,
decode(9,10,15,16, "NE") ,
decode(11,12,13,14, "NW") ,
decode(NE,NW,SW,SE, "ALL")

FROM DUAL;

Replace(location, "1,2,7,8", "SE")
Replace(location, "3,4,5,6", "SW")
Replace(location, "(9,10,15,16", "SE")
Replace(location, "11,12,13,14", "SE")
Replace(location, "NE,NW,SW,SE", "ALL")



col1
-----
NE,NW,SE,SW
SE,NE,NW,SW
SE,NE,NW,SW
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
NE,NW,SE,SW
SE,NE,NW,SW
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
SE,NE,NW,SW
1,10,11,12,13,14,15,16,2,3,4,5,6,7,8,9
10,11,12,13,14,15,16,9,SE,SW
10,11,4,7,15,16,9,SE,SW
1,6,7,8,9
10,11,12,13,14,15,16,9,SE,SW
1,10,11,12,13
10,11,12,13,14,SE
1,2,3,4,5,6,7,8,9
NE,NW,SE,SW
SE,SW
NW
SW,NW
0
Rowby Goren Makes an Impact on Screen and Online
LVL 13
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

In Oracle fusion, using HCM is it possible to mass assign a role/roles to existing user accounts? I know you can update elements in the Person Record, but was looking at the roles. which is previous versions of oracle might be referred to as 'responsibilities'.

I can do this manually, locate user record and then add the role, but it is time consuming to recall each user and assign the role manually. Would like to find a way I can do this en mass for non auto provisioned roles.

I've scoured google for examples/literature but there is not much there for a novice like me. Any information be gratefully received.
0
Here is a query

select STU_WBL_X.WBL_EXPCODECCR
from S_SC_STU_WBL_X;

The results from this particular query could be
Could be C or I
But I don't want the letters C or I in the results.
I want Cooperative for C and Internship for I.

What is the correct syntax to get the results spelled out?
Thanks
D
0
hi,

As I am testing Oracle to MariaDB converison and when I migrate to MariaDB some table may fail to create when there are foreign key prevent me from creating tables .

from your point of view, how can I check quickly which MySQL constraint I have to remove before dropping that table and recreate again .

it is a very time consuming task if I have to check tables by tables .. ( 3xxx tables!)

or I should look at Oracle constraint instead to give hints  ?
0
hi,

as I am tried to migrate from Oracle to MariaDB/MySQL and we are try to tools Ispirer, once the tools convert the following object for us how can we verify the converted objects can works without any problem:

1) sequence,

by running Select NEXTVAL(<sequence name>); ?

what should be the output?

2) Function.

3) trigger

4) package,

5) synonyms.

any idea?
0
hi,

is there any way for me to find out in oracle:

1. Existing Oracle BLOB store what kind of data (XML, …, document)

2. Existing Oracle PL/SQL uses hints  (index hints, parallel hints…….)

3. Existing Oracle DB got FULL-TEXT search
0
Hello Everyone

We recently upgraded our Oracle database from 11g to 12.2.

Actually we are using Oracle developer Forms 6i and after the upgrade we are not able to issue any rollback from the form screen to the database and we receive below error:

FRM-40655 SQL Error Forced Rollback.

Please advise.

Thank you.
0
Hi everyone -

We recently upgraded db from 11g to 12.2.
We have a dilemma with one form that was created with Forms 6i. it is a standalone Utility Form created around 10 yrs ago
We had no problems working with it on 11g but now (12.2) when we exit the Form we get - FRM-40655 SQL Error Forced Rollback Clear Form and Re-Enter Transaction-
Does anyone have any idea why this is happening on 12.2. and how to solve?

Thank you.
0
how can I do a self join on the query below, I ha attached the result set for both queries buy I want it to be one.   please expert advise?        

 select
            t1.unit_code,
            t1.dls_range,
            t1.dls_section,
            t1.lowdesc_case as lowdesc 
        from
            ( 
                select
                    unit_code,
                    dls_range,
                    dls_section,
                    case
                        lowdesc 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20, 22, 23, 24, 25, 28, 29, 30, 32, 33, 34, 36, NE, NW, SE, SW' 
                        then
                            'ALL' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, NW, SE, SW' 
                        then
                            'ALL' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 15' 
                        then
                            'SW,SE,9.10,11,12,13,15' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE, SW' 
                        then
                            'ALL' 
                        when
                            '01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE, SW' 
                 

Open in new window

0
Hi all,

I have created the following function and it does not retrieve the snapids from  the remote db using the dblink -

Type, Objects and function created -

create or replace TYPE Obj_snapids AS OBJECT
(   snap_id NUMBER,
    end_interval_time date,    
    instance_number NUMBER
);
/
create or replace TYPE tab_snap_ids AS TABLE OF Obj_snapids;
/
create or replace FUNCTION rr_snapids(p_link_name IN VARCHAR2)
    RETURN tab_snap_ids
IS
    c_sql_template   CONSTANT VARCHAR2(1000) := '
          SELECT Obj_snapids(
                         snap_id,
                         end_interval_time,
                         instance_number
                 ) obj
            FROM dba_hist_snapshot@~insert_db_link_here~';
    v_results                 tab_snap_ids;
    v_dblink                  VARCHAR2(2000);
BEGIN
   apex_debug.enter(
        'rr_snapids'
        'p_link_name',
         p_link_name);
     v_dblink := p_link_name; --- added replace function to the hostname
    if is_valid_db_link(v_dblink)  
    THEN
        EXECUTE IMMEDIATE REPLACE(c_sql_template, '~insert_db_link_here~', p_link_name)
            BULK COLLECT INTO v_results;
    ELSE
        DBMS_OUTPUT.put_line('Invalid Link Name used: ' || p_link_name);
        v_results := tab_snap_ids();  
    END IF;
    apex_debug.info('rr_snapids exiting with ' || to_char(v_results.count,'fm99999') || ' rows returned');
    RETURN v_results;
END;
/

Please let me know what I …
0
JavaScript Best Practices
LVL 13
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Dear Oracle Gurus,

I am trying to write a oracle query to display all the parent and child tables in a schema based on Primary key , foreign key constraints.
In some cases a particular parent table (which has multiple child tables) will be a child table to another table itself. My query output should look like:

Parent      Child      Level
            
T1                T2      
T1                T3      
T1                T4      
T1                T5      
T6                T7      
T6                T8      
T10                T9      
T10                T11      
T12                T1      
T12                T10      
T12           T13      

I am trying to solve a situation where for me to delete data in Table 12, I need to first delete T9 , T11 and then T10. So looking for additional columns in the query result that will tell me the order in which data needs to be deleted.


Regards
Lavanya
0
I am trying to check if a certain I am trying to check  on a windows server if a certain file exists in oracle on a unix server that I have rights too. I have done this and works

SET SERVEROUTPUT ON SIZE 1000000;
set echo off
set head off
set feedback off
set verify off
set pagesize 0
set pause off
set termout off
set term off
set timing off
#SET LINESIZE 1200
SET TRIMSPOOL ON
spool D:\Admin\Data\chk.txt
DECLARE 
 PROCEDURE checkFileExistOrNot(v_dir IN VARCHAR2, v_file in VARCHAR2) IS
  lb_file_exist boolean;
  ln_size number;
  ln_block_size number;
  BEGIN
    sys.utl_file.fgetattr(v_dir,v_file,lb_file_exist,ln_size,ln_block_size);
    if lb_file_exist then
        dbms_output.put_line('true');
        else
        dbms_output.put_line('false');
      end if;
  END;

 BEGIN
   checkFileExistOrNot('HRUTL','accuhrsBW.csv');
 END;
/
spool off;
EXIT;

Open in new window



Now I trying the same thing but only know part of the filename, I’m trying to pass it in as a variable – this does not work

SET SERVEROUTPUT ON SIZE 1000000;
set echo off
set head off
set feedback off
set verify off
set pagesize 0
set pause off
set termout off
set term off
set timing off
SET LINESIZE 1200
SET TRIMSPOOL ON
--spool D:\Admin\BSwift\BSwift_Export\Data\chk.txt
declare  PROCEDURE checkFileExistOrNot(v_dir IN VARCHAR2, v_file in VARCHAR2) IS
  lb_file_exist boolean;
  ln_size number;
  ln_block_size number;
  BEGIN
   sys.utl_file.fgetattr(v_dir,v_file,lb_file_exist,ln_size,ln_block_size);
    if lb_file_exist then
        dbms_output.put_line('true');
        else
        dbms_output.put_line('false');
      end if;
  END;
  
 declare vfile varchar2(20);
 BEGIN
  vfile:='*ADP*'; 
   checkFileExistOrNot('HRUTL',vfile);
 END;
/

Open in new window

0
I have Oracle 11g, trying to find a way to check the connection to DBSEC when running a script, tried to locate a way to do this.  Any help or guidance would be helpful.
0
Hi APEX Masters,

I have a dashboard with about 50 complicated queries displayed in charts and interactive reports (APEX 18) , getting data from about 3-4 tables and one of the table has about a million records. All the tables have indexes.
It is taking about 65sec to display the page / load, is there any option to increase the performance  of the page , can anyone give a fast solution asap.

Appreciate for your time.

Thanks,
R
0
hi,

how can I list out all oracle objects permission of a schema(s) ?
0
Hello,

Have one Oracle Instance 8i, the client is taking backup through export utility as scheduled task to create DMP files, DMP files size suddenly changed from 20 GB to 1.6 GB , the export is done without issues or interruptions, checked historical big tables and data sounds intact ? what do you think ?

can I compare between 2 files ?

Thanks
0

Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.