Tag Info

Hot answers tagged

4

Why do you use this view at all for this query? The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint ...


3

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not. Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on ...


3

You need to use: DBMS_DATAPUMP.METADATA_FILTER(l_job_handle, 'EXCLUDE_PATH_EXPR', 'IN (''STATISTICS'')');


3

It's possible for a SELECT statement to modify the database, for example: select mysequence.nextval increments a sequence select myfunction executes a PL/SQL function which could have any sort of side effects The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as ORA-14551: cannot perform a ...


3

When you use Kerberos authentication (for example Active Directory) with JDBC, you have to prepare your code for it, and not just simply change the connection string. The Database JDBC Developer's Guide and Reference covers this topic: http://docs.oracle.com/cd/E11882_01/java.112/e16548/clntsec.htm#JJDBC28344 Essential parts from the example of the above ...


3

Yes and No. With improper configuration, too much RAM can cause performance degradation. For example on Linux platform, if HugePages is not configured, with large SGA and many database sessions, the pagetable will hold a significant amount of memory. Once at one our clients, the database server had 512 GB memory installed. They had about 4000-5000 database ...


3

There are several ways to do this. Either remove the join and convert the subqueries to correlated: SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA ) AS PET3, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = ...


3

May I suggest doing it in a couple of passes: SQL> select * from records; SUBJ_NO -------------------------------------------------------------------------------- CBS405/505 Fourth Week(Wtr Qtr) BR 404-504-604 Fifth Week(Fall Qtr) AP490-QB-09 Weekly Sessions(Spr Qtr) APR501 Fourth Week(Wtr Qtr) MP 300 Fourth Week(Wtr Qtr) ACX/501 Fifth Week(Sum Qtr) ACX ...


2

You mentioned AWR, so you are licensed to use ASH also. You can find the session(s) executing the specific SQL in DBA_HIST_ACTIVE_SESS_HISTORY view based on the sql_id. The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID columns contain information about the PL/SQL call stack. This however may not be complete if you have several levels of PL/SQL ...


2

A semantically appropriate way of expressing this would be: select ... from a where not exists ( select null from b where b.x = a.x) NOT EXISTS uses an anti-semi-join which allows the optimiser to be very responsive to the table and join cardinalities.


2

This statement doesn't have FROM keyword "SELECT "REGO", "MAKE" TO_CHAR (SYSDATE, 'DD/MM/YY')NOW;". select REGO, MAKE,TO_CHAR(SYSDATE, 'DD/MM/YY') NOW from CAR ;


2

I usually use this statement. with fk_list as ( select parent_table.table_name parent, parent_cons.constraint_name as pk_constraint, child_table.table_name child, child_cons.constraint_name as fk_constraint from user_tables parent_table join user_constraints parent_cons on parent_table.table_name = parent_cons.table_name ...


2

You can check the value of the CLUSTER_DATABASE parameter: SQL> select name, value from v$parameter where name = 'cluster_database'; NAME VALUE --------------------- --------------------- cluster_database TRUE According to the Oracle Database Reference: CLUSTER_DATABASE is an Oracle RAC parameter that specifies whether or ...


2

DBMS_CRYPTO is a package which is owned by SYS schema. You can ask the DBA to give the execute privilege on that package to the schema which you are using. Here DBA has to login as SYS. Even SYSTEM or any other schema for that mater, which has SYSDBA role won't do.


2

You can identify most recently created users/schemas from dba_users view select * from dba_users order by created desc; When you know what schemas to look out for, you can list the most recently created tables with select * from dba_objects where owner in ('SCHEMA1','SCHEMA2') and object_type = 'TABLE' order by created desc; For a single table, you ...


2

You have a missing comma after the alias "Total" in your first query. Extract will take single column as input, while you are giving two columns. You could do it like: SQL> SELECT COUNT(deptno) AS "total", 2 extract(YEAR FROM hiredate) AS "year" 3 FROM emp 4 GROUP BY extract(YEAR FROM hiredate); total year ---------- ...


1

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace ' ' || c.charges - c.payment || ' ' with ' ' || (c.charges - c.payment) || ' ' but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, ...


1

According to https://community.oracle.com/thread/959980 and https://community.oracle.com/thread/838380 the only way to do this is to use the SQLFILE parameter to impdp to create a script. Then replace CREATE PACKAGE with CREATE OR REPLACE PACKAGE and then execute the script.


1

SQL*Plus is not part of the basic Instant Client, it is an additional download at the Instant Client download page. Once you have downloaded and installed it, you can use it by setting the ORACLE_HOME and PATH envrionment variables, for example: export ORACLE_HOME=/oracle/base/product/11.2.0/client_1 export PATH=$ORACLE_HOME/bin:$PATH Or on a Windows OS: ...


1

If you look at the standard SQL way of constructing a recursive query, you would see something like this: with t (num) as ( select rownum-1 num from dual union all select num+1 from t where num+1 < 10 ) select * from t; SQLFiddle: http://sqlfiddle.com/#!4/9eecb/4371/0 As you can see, it starts with selecting one row from the DUAL table, then ...


1

We finally deleted the files manually and OEM is fine. We ran this query to get the delete scripts: SELECT 'alter diskgroup ' || gname || ' drop file ''+' || gname || Sys_connect_by_path(aname, '/') || ''';' a FROM (SELECT b.name gname, a.parent_index pindex, a.name ...


1

SQL> r 1 with t as ( 2 select 1.0 as x from dual 3 union all select -2.0 as x from dual 4 union all select 3.0 as x from dual 5 union all select -4.0 as x from dual 6 union all select 5.0 as x from dual 7 union all select -6.7 as x from dual 8 union all select 8.9 as x from dual) 9 select sum(case ...


1

What the SIDs on all the nodes? Is this really the same DATABASE. For example you can have 3 node cluster having a single ASM on all the nodes, but database A is clustered on node1/node2. But database B runs only on the node 3. The database B then is still started and stopped by the Clusterware, but is not clustered. You can also check output from: ps -ef ...


1

You'll have to write your own, it's not a native feature of oracle, it's something that has been implemented in Oracle tools. Grab the plan from v$sql_plan for your SQL_ID and then grab the delta of session stats before and after you run your query. It's a little more involved in that, but that's the basic mechanics.


1

However oracle keeps making my numbers 22 bytes long This assumption is wrong. Values stored in a NUMBER column take up only as much space as needed. Quote from the manual A NUMBER value requires from 1 to 22 bytes (emphasis mine) This can be validated using the VSIZE() function: create table foo (id number(22), some_value number(22,8)); ...


1

See here ixora. Number has variable length, like string. each digit occupies one nibble (4bits, half byte) NUMBER implementation is platform independent you can validate size of number value in a culumn by calling dump() function PS: Oracle also supports native intergers like PLS_INTEGER, but these can only be used in PL/SQL context only.


1

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit. This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value? ...


1

You can improve efficiency of your query by (for example) replacing: ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(*) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL AND pv.quarantine_status = 'QUARANTINED' GROUP BY pv.item_id ) ct1 ,( SELECT /*+ NO_MERGE */ ...


1

In my opinion, portability between RDBMSs is a hopeless goal. In one job, where we were considering moving from Sybase to Oracle, I went through the code and estimated that 80% of the SQL-related lines would need to be changed. Some 'insurmountable' differences (Oracle vs MySQL): ROWNUM vs LIMIT CLOB vs TEXT INDEXes (OK, there may be a tiny subset that ...


1

There a lot of differences between the two DBMS. Oracle supports window fundtions, the MERGE statement and several other features that MySQL doesn't. They have different datetime functions (but you are lucky here, - interval '1' day will work in both). Different UPDATE syntax - apart from very simple, one table update statements. MySQL supports a JOIN ...



Only top voted, non community-wiki answers of a minimum length are eligible