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


11

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


8

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


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

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

You could use a row-level security policy with a statement_type limited to update (or more likely update and delete, and maybe insert too). See the DMBS_RLS.ADD_POLICY docs for details. Dummy scenario: a list of tasks, only task owners can modify their task. create table owners(owner_id int primary key , owner_name varchar2(10)); create ...


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!


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


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

My_Sequence_Name.NextVal - check here and here. Psoug is a great site for Oracle.


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

Easy in Oracle: comment on table mytable is 'This is a table comment'; comment on column mytable.mycolumn is 'This is a column comment'; You can then view the comments using the USER_TAB_COMMENTS and USER_COL_COMMENTS data dictionary views. This answer covers it for SQL Server.


4

For SQL Server, use: The IDENTITY column property (all versions); or CREATE SEQUENCE and NEXT VALUE FOR in a column default constraint (2012 onward). For Oracle, upgrade to 12c and specify <sequence>.NEXTVAL as the default value for the column. Problem solved!


4

Your table's segment is only 2 MB. The rest of the data is stored in the LOB segments which are physically separate from the table segment. SELECT column_name, segment_name FROM dba_lobs WHERE owner = 'MYUSER' AND table_name = 'MYTABLE' should show you that there are two LOB segments associated with the table. You can then query dba_segments to ...


4

It's signalling a 'ORA-214' during instance startup, which is really bad. $ oerr ORA 214 00214, 00000, "control file '%s' version %s inconsistent with file '%s' version %s" // *Cause: An inconsistent set of control files, datafiles/logfiles, and redo // files was used. // *Action: Use a consistant set of control files, datafiles/logfiles, and redo ...



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