Tag Info

Hot answers tagged

17

You might prefer something like this: select * from foobar where (subject,term) in ( ('STAT','111') ,('STAT','222') ,('ENGLISH','555') ,('COMM','444') ,('COMM','333') ,('STAT','222') ,('STAT','666') ...


10

In terms of a pure code cleansing, the following looks cleaner: SELECT * FROM foobar WHERE (SUBJECT = 'STAT' and TERM IN ('111','222','666') ) OR (SUBJECT = 'COMM' and TERM IN ('333','444') ) OR (SUBJECT = 'ENGLISH' and TERM = '555' ) Depending on the application and how often the logic will be reused, it may also be worth setting up a ...


7

You created your column with double quotes, so now Oracle requires you to always refer to this column using identifiers and respecting the case. If you remove the double quotes, Oracle always transforms the name to upper case, so without them it is case insensitive. This will work if you created the column without identifiers: where rulE_id where RULE_ID ...


7

It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the "_optimizer_skip_scan_guess" parameter. The default value for this parameter is ...


7

DROP and TRUNCATE are Data Definition Language commands and thus cannot be rolled back. However, in Oracle you can use the following technologies to recover your table: Flashback Drop Flashback Database Tablespace Point in Time Recovery Table-Level Recovery From Backups (new in 12c) Flashback Drop If the Recycle Bin is enabled in your database, you ...


6

From the 12c docs: The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you ...


6

Easily solved: export ORACLE_HOME=/usr/lib/oracle/12.1/client64 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH Stick those lines in your ~/.bash_profile if you want them to persist for a given user, or in /etc/bashrc to persist for all users.


6

I don't know of any built-in way to accomplish this but you could use a derived table or a Common Table Expression (CTE or as Oracle likes to call them: subquery_factoring_clause), if your aim is to pass the parameters only once in the USING: -- derived table OPEN myCursor FOR SELECT value1 AS Value1, myPackage.function1 (value1, my.id) AS ...


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

Well it does now - Oracle 12c introduced IDENTITY columns, see: Identity Columns in Oracle Database 12c Release 1 (12.1) e.g. CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );


6

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different ...


6

I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of: You admitted that you are working with large record counts in your tables. What ...


5

I usually use a number(1) type combined with a check constraint: some_flag number(1) not null check (some_flag in (1,0)) To make things crystal clear I also add a comment to that table: comment on column some_table.some_flag is '0 is false, 1 is true'; so that the explanation on what "true" means can be seen when looking at the definition of the ...


5

It's still in beta. It won't be released till the 12.1.0.2 patchset. As such, unless you're in the beta program, not much information is available yet. I think the NDA has been lifted, as I saw Tom Kyte speak about it at Collaborate 14 in Las Vegas a couple of weeks ago, but, you can't actually use it yet. Hope that helps.


5

snapshot is an old and deprecated term for a materialized view. They're exactly the same thing, snapshot is just an older term. A view is a stored query. It consumes (effectively) no space, just the space required to store the text of the query in the data dictionary. When you query the view, the stored query is executed. The results, therefore, will ...


5

If all else fails read the docco. Try here for starters (v. good diagram). In Oracle, a schema is a database. Also see here - particularly this "One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects [i.e. ...


5

In short - yes, there are standard operating procedures for doing all of this with Oracle. You should start by looking into RMAN (Recovery MANager). I have put together a high level overview of RMAN as well as an introduction to Oracle backups for SQL Server DBAs. I suggest watching both of those and then heading over to the Oracle Database Backup and ...


5

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies). This means however, you ...


5

Yes, that's fine: All software downloads are free, and most come with a Developer License that allows you to use full versions of the products at no charge while developing and prototyping your applications, or for strictly self-educational purposes. The full Developer License can also be found on OTN, including: Oracle grants You a nonexclusive, ...


5

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To provide the data in a database table you can use sql loader or [external tables]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create ...


5

Oracle (as well as any other RDMS) doesn't scan "rows". It operates with blocks (other vendors may use different terminology , e.g. page) , and it doesn't know which blocks have or don't have rows. Also, sequential read is much faster than random , and it's way cheaper to read more than needed into memory... A good graphical explanation of HWM : ...


5

No you cannot. To restore a table from the recycle bin use flashback table <table_name> to before drop; <table_name> is the original table name and not the object_name from the recycle bin. Do not rename the table to restore it from the recycle bin!


4

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it. What if your CTAS action runs out of tablespace? You would never see the error. If you have an unrealiable Internet connection, then the best solution is probably Simple/basic solution Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. ...


4

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID. It also seems to assume that Person indexes PersonID and Person independently. If this is the case, the first query comes back as {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID} {INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice and Zack ...


4

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt. The issue being described is that if you issue these three semantically identical statements: SELECT thing FROM mytable; select thing from mytable; Select Thing From MyTable; the database treats them as three entirely different entities, ...


4

You have essentially no guarantees about the sequence of ROWIDs. From the ROWID Pseudocolumn docs: If you delete a row, then Oracle may reassign its rowid to a new row inserted later. So the delete scenario has a potential for not being sequential. The ROWID encodes a relative file number and block number. There is no guarantee that these will ...


4

The Oracle Database In-Memory Option is not released yet and there is no official documentation yet. Yesterday I attended a presentation from an Oracle product manager about this new option. We were not allowed to record the session but there was no NDA or any request to keep the information secret. Below is everything I can remember from the presentation, ...


4

Balazs's answer is excellently clear. Here is some info on right deep, left deep and bushy with a lot of graphics to communicate the points: http://kylehailey.com/right-deep-left-deep-and-bushy-joins-in-sql/


4

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not tow wrap lines. If your viewer is lessthen -S the option you have to use according to http://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less . On Unix/Linux you can use head -1 output.txt to get the first line ...


4

You want to make an outer join. In Oracle 8i one has to use the (+) notation. SELECT H.STUDENT_CODE, H.FIRSTNAME, H.LASTNAME, H.PROGRAM, H.TEACHER_CODE, H.COUNTRY_CODE, AC.COUNTRY_NAME, P.PROGRAM_NAME, T.TEACHER_NAME FROM HTUD H, PROGRAM P, TEACHER T, AP_COUNTRY AC WHERE H.FIRSTNAME LIKE '%ark%' AND H.PROGRAM = ...



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