Tag Info

Hot answers tagged

2

It's possible to add a_type to b, and have check constraint on b.a_type. Breaking normalization a bit allows enforcing storing details in proper table . In Oracle you can have 2 constrains supported by the same index, for instance PK on a.id, and UNIQUE on (a.id,a.type) , then some tables that require proper type may have FK to (a.id,a.type), and other ...


1

Thanks for your help. I changed my query like this and it's working finde. 😊 SELECT DATES.DAT, RET.RETURN_AMOUNT, ORD.ORDER_AMOUNT FROM ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT FROM all_objects WHERE ROWNUM <= ...


1

You could join to the 2 tables separately (and GROUP BY) and then join them together: WITH dates (dat) AS ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat FROM all_objects WHERE ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy') - TO_DATE('02.05.2014','dd.mm.yyyy') + 1 ) , ret ...


1

Strictly speaking, you cannot overflow the redo logs, because they are endlessly reused in a circular manner. When in the ARCHIVELOG mode, however, a log file cannot be reused until it's archived, so there can be a situation where you would have to wait for archiving if you fill redo logs faster than they are archived. In the NOARCHIVELOG mode there is no ...


1

If you want even more automation, then download SQLcl, then you can do fun things like ctas current_table new_table and it generates necessary code for you. e.g., SQL> ctas my_table new_table CREATE TABLE OWNER."NEW_TABLE" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", ...


1

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; ...


1

Following sample may be useful to build query which you want to write. USE tempdb GO CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY ) GO INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4') GO CREATE VIEW v_V1 AS SELECT 'id1' id, 2 value UNION SELECT 'id1' id, 1 UNION SELECT 'id1' id, 3 UNION SELECT 'id2' id, 1 GO CREATE VIEW v_V2 AS SELECT 'id3' ...


1

It turns out the user had been granted other privileges by a different account as well as the one we use, so we were unable to see those privileges in ALL_TAB_PRIVS. We had to look in DBA_TAB_PRIVS, and sure enough, there were the other permissions. Thanks to all for chiming in!


1

Please see the following to understand the issue So even though you're connected with user SYSTEM to the pdb, when you issue connect without specifying the pdb, it'll try to connect to the container and the user is not defined there.



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