Tag Info

Hot answers tagged

6

You could use Flashback to return the table data back to its original state. Is Flashback enabled? Database needs to be in archive mode: select log_mode from v$database; Should return "ARCHIVEMODE" Check if flashback is enabled: select flashback_on from v$database; Should return "YES" If "NO", you can enable flashback by runing: alter database ...


6

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


6

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.) But this part of the code: FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i ...


4

This will delete anything three years older than January 1st of this year. DELETE table_name WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);


4

Possible solutions: Use a Secure External Password Store (Oracle Wallet): http://docs.oracle.com/database/121/DBSEG/authentication.htm#CHDHGAIJ You can use the stored credentials without specifying a username or password. Connection string: /@orcl. REMOTE_OS_AUTHENT: http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams208.htm Not ...


4

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this: Sample Data: CREATE TABLE #Example ( Eid integer NULL ); INSERT #Example (Eid) VALUES (1), (2), (3), (4), (5), (NULL), (NULL), (NULL), (NULL), (NULL); My idea revolves around the idea that ...


4

DBA_TABLESPACE_USAGE_METRICS shows units in database blocks, not bytes. 4 MB is not enough for a SYSTEM tablespace. Most likely what you saw was 4M blocks, which is 32 GB with a 8K blocksize tablespace, and that is the maximum size of a datafile in a 8K smallfile tablespace. That is why your attempts had no effect. You can add a new datafile to the ...


4

As always: it depends. However in most cases the default values are adequate. The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50? INITRANS ...


4

It gives an error: ORA-01790: expression must have same datatype as corresponding expression From the documentation for UNION: The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). Tested at SQLFiddle


3

This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer. In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets ...


3

Using dbms_job, you're never going to have two instances of a job running at the same time. If I do something like this with an interval that is 1 minute in the future and a procedure that takes 3 minutes to run declare l_jobno pls_integer; begin dbms_job.submit( l_jobno, 'begin proc_that_takes_3_minutes(); end;', ...


3

There is a built-in procedure for that. set serveroutput on declare feature_boolean number; aux_count number; feature_info clob; begin dbms_feature_data_guard(feature_boolean, aux_count, feature_info); dbms_output.put_line(feature_boolean); dbms_output.put_line(feature_info); end; / Sample output when Data Guard is not used: 0 Data Guard ...


3

You'd need to use regexp_like not like if you want to use regular expressions. It sounds like you want with x as ( select 'A1B C2D' postal_code from dual union all select 'XXX 777' from dual union all select 'a1B 2C3' from dual ) select * from x where regexp_like( postal_code, '^[A-Z][0-9][A-Z] [0-9][A-Z][0-9]$', 'i' );


3

The constraints are kept for the renamed table. Drop them before you import the tables with the correct data. alter table t1 drop constraint c; There is also another way to do it if you do not want to lose the wrong data. You can create a new table based on the 2 tables: create table t1 as select * from t; Then you must disable the constraints and ...


3

Why don`t you dip into the manual SQL Language Reference, Appendix D, Oracle Regular Expression Support: [] Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for ...


3

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A8434 Can LDAP be used with Instant Client? Yes, LDAP can be used with Instant Client, as of 10.1.0.4.


3

It sounds like you want WHERE date_entered BETWEEN sysdate - interval '2' hour AND sysdate Assuming date_entered never contains future dates WHERE date_entered >= sysdate - interval '2' hour Depending on exactly what you need (and why you're running the query), you may want to truncate the date to the hour or minute so that ...


3

I can see why Oracle RAC can handle split brain and PXC cannot. What separates them in their architecture and data storage. In what ways ??? Oracle RAC All RAC Instances deal with only one set of database files Each RAC Instance has its own Log Buffer and LGWR (Log Writer) process Writes to any Oracle RAC instance, even on nodes that go down temporarily, ...


3

You could write your own user-defined function to generate sequence numbers. Alternatively, you could create a separate table with a single AUTO_INCREMENT column to generate new IDs: in your stored procedure you would insert a row into that table, use LAST_INSERT_ID() to fetch the generated value, then delete the inserted row.


3

GRANT is a DDL statement. You can not run DDL statements directly in PL/SQL, but you can use execute immediate: CREATE OR REPLACE PROCEDURE "setUserPermissions" (u VARCHAR2, l INTEGER, tbl VARCHAR2) AS BEGIN DELETE FROM "userLvl" WHERE "user" = u; INSERT INTO "userLvl" VALUES(u,l); IF l>0 AND l<6 THEN execute immediate 'GRANT ...


2

A list of predefined roles in Oracle 12c can be found in the Security Guide. Also in Oracle 12c the view dba_roles has a column ORACLE_MAINTAINED. A 'Y' in this column tells you that this is a role created by the Oracle installation scripts. For Oracle versions before 12c this column does not exist. So for Oracle version lower than 12c you have to check the ...


2

You wrote 12c db has the exact data of 11g (expdp/impdp) But are you sure? 1) Did you make a consistent export of your tables? This means all table data is from the same point in time. For this it is sufficient that you stop all other write activity on the database during export. Or you set the flashback_scn parameter when using expdp or the ...


2

Oracle up to 11g used a thread based model on Windows and a process based model on Linux. Quote from the manual: A process is a mechanism in an operating system that can run a series of steps. The mechanism depends on the operating system. For example, on Linux an Oracle background process is a Linux process. On Windows, an Oracle background process is ...


2

Use partition by on the name column, and count the rows based on the order of id-s. Where the row number of the actual row is greater than 1, concatenate id and name, else just use the name. with t as ( select 1 as id, 'john' as name from dual union all select 2, 'david' from dual union all select 3, 'zed' from dual union all select 4, 'john' from dual ...


2

Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table. create table tmpIds(id int not null) insert into tmpIds (59156) insert into tmpIds (57045) etc... SELECT COUNT(*) FROM "VERSION" INNER JOIN tmpIds ON "VERSION"."VERSION_ID" = tmpIds.id drop table tmpIds


2

You can use extract() to get the actual year and compare those: DELETE FROM table_name WHERE extract(year from change_date) <= extract(year from current_date) - 5; If today is 2015 this will delete anything that is in 2010 or older. Replace the - 5 with the "X" you want to use. Note that extract(year from change_date) will not make use of an index ...


2

Some notes first: Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2. Adding account_type to Payments (and modifying accordingly the foreign key to ...


2

You could calculate the percentage of the sum of a you've reached up till the current row, then segment that based on your thresholds. with pct as ( select a , (sum(a) over (order by a desc))/sum(a) over() pct from foo ) select a , pct , sum(a) over (partition by case when pct <= .6 then 'A' when pct <= .9 then 'B' else ...


2

I think an answer for future browsers on *nix could be: cat /etc/oratab


2

It's not a bug. This behaviour is expected. If you need to increase the connections, just change the processes limit: SQL> show parameter processes processes integer 150 SQL> alter system set processes = <integer> scope=[...]; SQL> shutdown immediate; SQL> startup; -- Either using pfile or spfile, you'll ...



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