Tag Info

New answers tagged

2

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 ...


0

Courtesy Materialized views are disk based and are updated periodically based upon the query definition. Views are virtual only and run the query definition each time they are accessed. Materialized Views advantage over View is, it's depends on the data. If data is too large view take much time to execute because it does not store data, during the ...


0

If I understand right you need select records that equals two values. You can look this query like exsample: SELECT firm_property.firm_id, firm.title FROM firm_property INNER JOIN firm ON firm_property.firm_id = firm.id WHERE (firm_property.property_id IN ('PKR3344', 'WWF1155')) GROUP BY firm_property.firm_id, firm.title HAVING (COUNT(*) = 2) If you ...


0

You can create a pipelined function. Some examples here.


0

I am generally opposed to triggers. They have their place but surprising behaviour, when you do one simple thing and the change ripples out across the world, will give you headaches later. Instead of having storage_size and memory_usage on the users table, how about creating a view that displays the data you are looking for? Then you don't need triggers ...


0

Here's what worked, although I don't know if it's a good answer or not. I had someone who could log in as an admin on my pc do the Oracle client install. In other words, if you're not a full admin on your machine, it may fail, and not tell you why. I had admin rights, I could edit the registry and path, but there were still some missing rights. Logging ...


0

ypercube is correct in pointing that the output which you claim would be different than the actual output. As the subquery is going to display the total value unless add another filter condition on fk_id_anexa. SELECT a2a.ID, a2a.fk_id_anexa, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2, (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS tab2 where TIP = 'PET' and ...


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 = ...


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

skip=N is what you're looking for. Skips the first N rows, so in your case you just want to skip the first. OPTIONS(skip=1) LOAD DATA ... rest of normal control file


0

You must have a server (say X) with V1 (pointing at server Y) and V2 (pointing at server Z). Can't you use create GTTs (Global Temporary Tables) with V1 and V2 (this will happen on X) and then perform your join operation on the GTT tables on X using the data from GTT_V1 and GTT_V2? There's a good link here about using GTTs. Check out the guidelines section.


0

Delete all your Oracle Clients from machine and make a fresh installation. In case you need both 32 bit and 64 bit follow this instruction: Stack Overflow - Install Oracle x86 and x64. If you have problems to uninstall Oracle, follow this instruction: How can I completely uninstall oracle 11g?


0

Take a look at https://apex.oracle.com/en/ - it's really a development environment, but may work for you. It's really slick, but may not suit your needs. It is free and comes with Oracle itself - but you'll need a sysadmin/dba to set you up on the db server - but I presume that you already have the privileges necessary to do your work. An alternative might ...


0

Figured it out, I just have to put the sequence's nextVal in quotes in the control file: append into [tablename] ( [column name] "[sequence name].nextVal" ... )


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 ...


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, ...


0

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct ...


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.


0

Oracle 12c has a Performance Hub which includes Monitored SQL in historical mode. Although I haven't tested it, and based on some open bugs I doubt it works 100% of the time. For 11g, or when the Performance Hub is not available or not working, I have created the open source program Historical SQL Monitoring (hist_sql_mon): Here's an example of how to ...


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: ...


0

You didn't mention how the data comes in, but I would hazard a guess that it is through flat files. If this is the case, I worked at an order processing company that used Oracle for the back-end of the WMS we used. We regularly received 10 of millions of records a day. We used SQL loader scripts to load the data. The caveat to that is that the data needs to ...


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 ...


0

I had a similar problem, the fix was a linked table refresh. Access was not aware that the precision had changed in my Oracle table and kept giving me the 'precision too small' error.


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 ...


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.


0

One way to accomplish this, using Java, would be to prepare the query statement, then use the PreparedStatement.getMetaData() method to obtain the ResultSetMetaData object that has the list of columns that would be returned, as well as their data types. Other database interfaces, such as ODBC and OCI, have similar functionality.


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 ...


3

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


0

Use sql*loader to load the data : http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm#sthref540 (or external tables). 1) setup a .ctl file with your file format 2) setup / point to a receiving table in your database. 3) invoke sql*loader (sqlld)


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.


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 ...


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 ...


0

To original poster... in the first option you miss to close a parenthesis Incorrect: (You string) sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))' Corrrect: sqlplus ...


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 ...


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 ;


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 ...


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 ...


0

Yes, following the backup output: RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 1010.57M DISK 00:00:48 02-APR-15 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: ...


1

You can find all tables locks in an Oracle database by running the following query select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;


1

From here, it appears that you can use it for the very purposes you describe at no cost. The relevant passage is here License Rights and Restrictions Oracle grants You a nonexclusive, nontransferable, limited license to internally use the Programs, subject to the restrictions stated in this Agreement, only for the purpose of developing, testing, ...


1

Given the lack of sample tables I have to make this up.... select * from parent_table where parent_table.foreign_key_id in (select parent_table.foreign_key_id from parent_table minus select child_table.id from child_table) where the parent_table.foreign_key_id might match to child_table.id


1

Yes you do. If you install the Oracle software you need to pay for it. However numerous exceptions exist including you can use Oracle Express 11g for free your customer may have licencing provisions that apply to you Either way, do not take anything from my answer or anyone else's answer as valid. Oracle licencing is complex and a final word can only ...


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 ...


0

Try connecting to database with -prelim option, I have used it to change database parameters when normal logon is not possible > sqlplus -prelim / as sysdba SQL> startup mount; SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 20G scope=both; SQL> shutdown immediate; SQL> startup;


-1

I found that it is easy to do remote desktop connection and open the Command Prompt then connect by: sqlplus sys/YourPassword as sysdba



Top 50 recent answers are included