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

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

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

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


5

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


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

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


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


4

There is only one REMAP_TABLESPACE parameter in de command. It would be seperated with ",". REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT,OLD_SCHEMA_IDX:NEW_SCHEMA_IDX Oracle doc impdp 11.2 http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929


4

I suspect that you did not execute this command with sqlplus. Not every sql program can handle the desc command. You can either use sqlplus or get the table definition with the following SQL command: SELECT dbms_metadata.get_ddl (object_type, object_name, owner) FROM all_objects WHERE owner = '<owner>' AND object_name LIKE ...


4

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO. Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL. But it's usually not too difficult to track down the worst SQL statement. Since you've already got AWR, look at the SQL Statistics section, ordered by ...


4

DBA_DEPENDENCIES view has all the answers to such questions. select * from DBA_DEPENDENCIES where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';


4

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types. Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? ...


4

No that is not possible, only session accessing the GTT can see it. IF you wish, you can use a physical table and model it as if it s a GTT. It isn't difficult, but isn't very efficient either.


4

The duplicate database ... from active database is designed to work without any existing backup. I would expect Oracle to clean up the archivelogs which were used during the clone process. So I would say this is an unexpected behavior. Try to log a Service Request at Oracle. I cannot recall if I had to clean up archivelogs after a clone processes. Well, I ...


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



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