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


9

If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table. Rather than trying to tune your current query, I would bet that the ...


7

figured this out, here is a solution in case someone needs to do something similar... using double quotes "" around characters will display them as they are... select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual will display: "Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen


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

Thanks to @ThisSuitIsBlackNot for pointing me to the tracing functionaility in DBI, I was able to track this down to an error with not specifying port. I knew that I should be pointing at 1521 when setting up other connections in other programs, but DBI didn't require port to function, it was just 60 seconds of waiting for it to give results. DBI assumes ...


6

If Oracle only scans the index it cannot decide if there is a row with NULL in this column. So it makes a full table scan. A possible workaround: create a bitmap index on column4. This index contains the NULL columns, too. More details can be found in this post Why isn't oracle using an index for distinct query ?


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

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

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

Anybody can download and install oracle software without any installation codes being necessary. So in effect an Oracle licence is simply an entry in their Licence Management Services (LMS) database. Depending upon what you buy you will get a CSI number. In working with Oracle for more than 15 years this is all I have ever seen at customer sites: a piece of ...


5

The only real downside is that there are a number of configuration options that you can put in a TNS alias that you can't use when you use the easy connect syntax. Easy connect is designed to simplify the syntax for simple connections. In order to do that, though, it loses the ability to create more complex TNS aliases that do things like load balancing/ ...


5

Several things might be going on here and might be difficult to pin down but here's a few things. After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state. Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may ...


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

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

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

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

In reality, on the modern devices the impact on performance is likely to be minimal. The old "tablespace for index, tablespace for data" mantra comes from a time when storage tended to be made up of sets of single devices. I.E. if you said that you wanted to put a tablespace on a certain disk, it went onto a particular physical disk. Having both indexes ...



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