Tag Info

Hot answers tagged

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

As an academic exercise, I've implemented a solution in Postgres. Now, I know this was a question about Oracle, but it was also framed as a SQL question! :) If someone is more familiar with Oracle's recursive query syntax, maybe they can find the necessary changes to make this run in Oracle. Recursive Querying (in Postgres) In this solution, I make use ...


4

The following works but there's probably some more clever version. Explanation of the query's logic: We first find how many "resets" have been done up to and including the current row by counting the not nulls of the reset_val column, so we can separate the rows into subgroups. We also use another window function LAST_VALUE() with IGNORE NULLS, so we can ...


4

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


3

I think you are over complicating this. Grants and roles govern access by users not what table space and data files where the data is located in. Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question. Separate your business domains by user/schema. Give each user/schema their own ...


3

This could be for at least 2 possible reasons: Shared server architecture: SQL trace files belong to server processes, but when you use shared server architecture, your session does not have a single dedicated server process, your session can exist in several different server processes over time. Parallel processing: when your statements run in parallel, ...


3

You will find listener.ora in here: %ORACLE_HOME%/network/admin The usual problem is that %ORACLE_HOME% isn't set as a system variable and not exposed to a command shell. My quickest way to locate ORACLE_HOME from the command shell is: C:\>where sqlplus The full path to the executable will show. Go there. Go one up, and add /network/admin ...


3

You need an alias for both derived tables. And the outer pair around the from clause is useless. SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same FROM ( --<< only one opening parenthesis SELECT unique_id, confidence_is_same, first_name, last_name, postal_code FROM daniel.unique_physician WHERE ...


3

you don't need to join all 4 tables, this will show the missing rows select id, fk_id_chest, x, y from SOL_PUBLIC.COORD@RACDB minus select id, fk_id_chest, x, y from SIM_SOL.COORD


3

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error. http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225 If you specify schema.index, then Oracle attempts to enforce the constraint using ...


3

Yes, by using database links. For example, in db2: create database link db1_link connect to myuser identified by mypassword using 'db1'; create view myview as select * from mytable@db1link; select * from myview; Where using 'db1' refers to a TNS entry, but it could be replaced with the actual TNS descriptor.


2

To get the desired result from your sample table, select tgroup, name, id from ( select tgroup, name, id, row_number() over (partition by tgroup order by id) rn from (<<your subselect>>) ) where rn = 1 I've renamed group to tgroup as group is a reserved word in SQL. SQLFiddle As for your real query, please first make it ...


2

You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website: WITH a as ( SELECT t3_field1, t3_field2, ...


2

My best guess is that what you are actually trying to query is: SELECT S.sname FROM Suppliers S WHERE S.sid IN (SELECT C.sid FROM Catalog C, Catalog C1 WHERE C1.pid <> C.pid AND C1.cost = C.cost); I changed the subselect to return C.sid, and removed the parts table altogether, since in your original body text you stated no conditions on the table. ...


2

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases. I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;) Schema and ...


2

Indexing Expressions I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself. For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated ...


2

My proposal would be this one: CREATE TABLE UNIQUE_PHYSICIAN ( PARENT_PHYS_ID NUMBER, PHYS_ID NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY); ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID); CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG ...


2

Provide a name for the constraint: SQL> create table monkey(tail_count number); Table created. SQL> insert into monkey values (null); 1 row created. SQL> alter table monkey modify tail_count not null; alter table monkey modify tail_count not null * ERROR at line 1: ORA-02296: cannot enable (ANIMAL.) - null values found SQL> alter table ...


2

This version of Oracle XE is based on 11g2 edition, which is not compatible with CentOS 7. In fact, it isn't even 100% compatible with CentOS 6, however it will run there without problems, after you install a number of RPM packages not installed/required by Oracle installer (especially libaio both 32/64-bit). These are formal OS requirements for XE 11g2: ...


2

You can do it with Native dynamic SQL ( http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011 ) using the EXECUTE IMMEDIATE statement. Here's an example: DECLARE v_sql varchar2(128); v_username varchar(30); cursor c1 is select username from dba_users where username like ('PATT%'); BEGIN for REC in c1 loop v_sql := ...


2

Not possible, redo logs get transferred unprocessed. If you want to transfer changes related to just some specific objects or schemas, Streams (deprecated, but free) or GoldenGate (requires extra license) does that. Another option for reducing network traffic is to configure redo transport compression (requires the Advanced Compression option), but this ...


2

define/& is a simple text substitution mechanism that does not know anything about sql. And sql and the Oracle Database server do not know anything about the text processing mechanism. So the text that results from the 'define/&'substitutuin must be valid sql statement. Otherwise you get an error. CREATE TABLE P747||_MU as Select percent from ...


2

Choose Install database software only, then later you can upgrade your databases one by one with DBUA (Database Upgrade Assistant) from the new Oracle Home, or by performing a manual upgrade.


2

select subscriber, date_column, time_column, value from ( select subscriber, date_column, time_column, value, rank() over (partition by subscriber order by value desc, time_column) as r from mytab ) where r = 1 and subscriber = 'ccc';


1

It may probably no longer be a problem, but in the code it seems you forgot that the assignment operator is :=, not =, so typeEngine would be null and the query returns no rows.


1

What OS are you using? Check what the environment ORACLE_SID is set to. Change it to the existing one and you won't be needing to specify the connection string anymore.


1

If I understood you don't need to do it online. In this case, how about a CREATE TABLE AS SELECT using a select which is focusing on the subset of data? Then you just do a quick rename, recreate the indexes and done.


1

Just connect to ASM and check database client. set pages 999 lines 120 col SOFTWARE_VERSION for A15 col INSTANCE_NAME for A20 col DB_NAME for A20 trunc select INSTANCE_NAME, DB_NAME, STATUS, SOFTWARE_VERSION as "version" from V$ASM_CLIENT;


1

The syntax restrictions mentioned by @a_horse_with_no_name are merely a reflection of how the query is logically processed by the SQL database engine: The FROM clause is processed and all required tables are identified, after possibly resolving view definitions. The WHERE clause is processed, applying predicates to the table rows. The GROUP BY clause is ...



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