Hot answers tagged

9

The decisive factor is whether there is a unique constraint/index on b (key_column). If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column). If on the opposite, there is a unique constraint, the query is equivalent to ...


8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


7

As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes: SELECT T1.*, (SELECT T3.SMN_DATEC-1 FROM (SELECT T2.DET_NUMBERA , T2.SMN_DATEC , row_number() over (ORDER BY ...


7

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


6

You could use Flashback to return the table data back to its original state. Is Flashback enabled? Database needs to be in archive mode: select log_mode from v$database; Should return "ARCHIVEMODE" Check if flashback is enabled: select flashback_on from v$database; Should return "YES" If "NO", you can enable flashback by runing: alter database ...


6

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.) But this part of the code: FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i ...


6

First, join TABLE_2 and TABLE_3 using an inner join and additionally filtering on Value_A = 'a': SELECT t2.ID_1, t3.ID_A, t3.Value_A FROM TABLE_2 t2 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A WHERE t3.Value_A = 'a' This will give you the following result set: ID_1 ID_A Value_A ---- ---- ------- 1 b a 4 d a Now use the ...


6

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? No. When you create a primary key or unique constraint in SQL Server, a unique index to support that constraint is created automatically, with the same keys. Which seems to imply that there is a way of specifying what index should be used for a Primary Key. No. The ...


6

Deleting data isn't expected to change the size of the table segment. It will create free space in blocks that are part of the table segment and it will probably create at least a few empty blocks. That free space will be made available for subsequent insert operations on the table (assuming you're doing conventional-path inserts) as well as update ...


6

Internal Oracle users (SYS, SYSTEM etc) should never be modified in any way, except for password changes. As far as roles & grants are concerned, SYS already has unrestricted access to the entire RDBMS due to the nature of the user.


6

Often the RULE-hint helps when querying dictionary views. select /*+ RULE */ constraint_name,table_name from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='SUPPLIER'); But ...


6

An ORDER BY clause will always add a sort operation if there is no usable index to retrieve the data in order. For larger sets of data the data being sorted may be written to disk adding additional processing. For smaller result sets where indexes are being used, there may be no requirement for a sort step. If sorting is required, the database will have ...


5

This is a feature that's controllable by a parameter. From the documentation: Multi-byte Character Sets Ratio Suppression This feature optionally suppresses the ratio expansion from SQL Server database to Oracle database involving multi-byte character set. By default, Oracle gateways assume the worst ratio to prevent data being truncated or ...


5

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period: select cid, sum(amount) from transfer where trans_date >= date '2014-11-01' and trans_date < date '2015-06-01' group by cid having sum(amount) >= 1000; But this is not what you want. You want a "rolling running ...


5

You can fully achieve this with MERGE: MERGE INTO big_table B USING (SELECT key_col, col1, col2 FROM small_table) S ON (B.key_col = S.key_col) WHEN MATCHED THEN UPDATE SET B.col1=S.col1, B.col2=S.col2 WHEN NOT MATCHED THEN INSERT (B.col1, B.col2) VALUES (S.col2, S.col2) /


5

define my_schema_name = 'SCHEMA_1'; CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER( p_userid IN DBUSER.USER_ID%TYPE, p_username IN DBUSER.USERNAME%TYPE) IS BEGIN UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid; COMMIT; END;


5

It is really easy to log in as another user, just use the proxy user feature that is available since 8i. Unfortunately it does not work with SYS, but works with any other user. Basically you can grant the privilege to users to connect as another user. SQL> alter user system identified by Oracle123; User altered. SQL> alter user hr identified by ...


5

This is a Gap & Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions Here is how the query works: YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format. then ROW_NUMBER() ...


5

This is simply an aggregation problem. select student_id, count(case when type in ('Expulsion','Probation') and quarter=3-1 then 1 end) as previous, count(case when type in ('Expulsion','Probation') and quarter=3 then 1 end) as "CURRENT" from student_actions group by student_id; See http://sqlfiddle.com/#!4/f4263/10/0


5

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( ...


5

There are a number of things that can cause the actual plan to differ from the estimated plan (and if you want to get really deep in the weeds, there are lots of things that can cause different methods of producing the actual plan to produce different results but I'll ignore that). The simplest (and most common) revolve around bind variables. If I do an ...


5

It gives an error: ORA-01790: expression must have same datatype as corresponding expression From the documentation for UNION: The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). Tested at SQLFiddle


4

As always: it depends. However in most cases the default values are adequate. The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50? INITRANS ...


4

I think an answer for future browsers on *nix could be: cat /etc/oratab


4

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this: Sample Data: CREATE TABLE #Example ( Eid integer NULL ); INSERT #Example (Eid) VALUES (1), (2), (3), (4), (5), (NULL), (NULL), (NULL), (NULL), (NULL); My idea revolves around the idea that ...


4

DBA_TABLESPACE_USAGE_METRICS shows units in database blocks, not bytes. 4 MB is not enough for a SYSTEM tablespace. Most likely what you saw was 4M blocks, which is 32 GB with a 8K blocksize tablespace, and that is the maximum size of a datafile in a 8K smallfile tablespace. That is why your attempts had no effect. You can add a new datafile to the ...


4

Possible solutions: Use a Secure External Password Store (Oracle Wallet): http://docs.oracle.com/database/121/DBSEG/authentication.htm#CHDHGAIJ You can use the stored credentials without specifying a username or password. Connection string: /@orcl. REMOTE_OS_AUTHENT: http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams208.htm Not ...


4

Using dbms_job, you're never going to have two instances of a job running at the same time. If I do something like this with an interval that is 1 minute in the future and a procedure that takes 3 minutes to run declare l_jobno pls_integer; begin dbms_job.submit( l_jobno, 'begin proc_that_takes_3_minutes(); end;', ...


4

Please find the below 4 queries and run these queries from system database. //For seeing Full user details SELECT profile FROM dba_users WHERE username = 'SYSTEM'; //This query is used to change the password life time to unlimited ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; //This query is used to chagne the default password. alter user ...


4

Assuming that your database character set is AL32UTF8 (if there is a difference between the two, this is almost certainly your character set) and that all the data is characters that exist in the US7ASCII character set ("english" might constitute more than that depending on the definitions we're using) there won't be any noticable performance differences ...



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