Tag Info

Hot answers tagged

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


3

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


2

To answer some of your questions, firstly read this: http://www.oracle.com/technetwork/indexes/downloads/index.html and this http://www.oracle.com/technetwork/licenses/standard-license-152015.html. These are your licencing restrictions. All the manuals are available here: http://docs.oracle.com/cd/E11882_01/index.htm. Read the installation guide, ...


2

You can try using: ps -ef | grep -v grep | grep ora_pr Or if data guard is in use: ps -ef | grep -v grep | grep ora_rsm


2

You can do this by using a save point. create or replace PROCEDURE SPTest AS BEGIN -- We create a savepoint here. SAVEPOINT sp_sptest; insert into emptest(empid,empname,deptno) (1,'ravi',10); insert into test1(id,name,sal) (1,'raju',4444); update emptest set empname='hari' where empid=1; -- If any exception occurs EXCEPTION WHEN ...


2

Table person is situated in schema assigned to your apex workspace. You can go to APEX IDE to the section SQL Workshop -> Object Browser, schema name will be displayed in the top right part of the page. Or you can execute query: select owner from all_tables where table_name = 'PERSON'; You will get names of all users that have table with such name. After ...


2

You should be fine; the rowid would be a show-stopper if you were using it Temporarily, there would be "holes" in the data files, but Oracle will fill them in without any intervention. Ensure you have automatic tablespace management enabled. You can invoke that command but no need to; let Oracle take care of it The person that stated that probably meant ...


2

Not sure about the efficient part, but the following does the trick. You'll want (emp_id, work_dt) indexed at least, having the time_type in there should not hurt either. Assuming this table: create table times(emp_id number, work_dt date, time_type number); You can run this: (SQL Fiddle) -- get the last work date with last_dt as ( select emp_id, ...


2

Here is one solution: with t as ( select grp, emp_id, time_type, min(work_dt) as start_dt , max(work_dt) as end_dt from ( select row_number() over (partition by emp_id order by work_dt) - row_number() over (partition by emp_id, time_type order by ...


1

You should be able to do this using the either glogin.sql or login.sql. The former is run whenever anyone logs into SQL*Plus and the latter is specific to a user. Im not currently in a position to install it myself to try out but this link to the Oracle docs should help. Configuring SQL*Plus


1

You could try the Schema Comparison Tool from dbsolo. I don't know if you can compare different databases with one another. You have to keep in mind that Oracle and PostgreSQL have quite different table definition clauses which make it difficult to compare them accurately.


1

as far as i know there is no such tool around. however, you got a couple of choices: a.) use oracle_fdw and just join the stuff or b.) export data to text files and use a simple UNIX diff. it works like a charm usually.


1

No its not possible to Partition a non partitioned index using rebuild in Oracle. For partitioning an index you will need to Drop the index and then create a partitioned index. Hope it Helps Vishad


1

Please try the below query to see if it's helps: select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;


1

Depending on your privilege level, you'd want to use DBA_TABLES, ALL_TABLES, or USER_TABLES. There is a StackOverflow thread that walks through this in some detail. By default, Oracle identifiers are going to be stored in upper case in the data dictionary and queries are case-sensitive. Most likely, if you have a table whose name contains the string ...


1

To add single quotes in a string literal, just double it, or use the chr function. E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote. To get your fixed end time, just concatenate it. select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from ...


1

You may need to resecure the agent. Here is a link that should help. http://jhdba.wordpress.com/2008/12/10/clearing-an-enterprise-manager-agent-that-fails-to-upload/


1

Well. it seems it was Oracle SQL Developer fault because when I deleted it and downloaded the newer edition it worked.


1

If you are mostly loading the data, and seldom querying it, then no need to split the table. Any "home-grown" attempt at splitting the table is bound to cause grief; use Oracle's (extra-cost) partitioning option if your queries are usually date-ranged; but be careful; partitioning does not necessarily improve performance. It can if the queries include ...


1

If you have advanced partitioning and can partition by date range and sub partition by charge, then you can reduce the amount of logical IO required to get to your data, provided that you are doing date range based searches that mostly use charge to filter. If you don't have advanced partitioning, you can still partition it your self, but will have a higher ...


1

Try doing this: GRANT SELECT ON gv_$session TO system; Because you are accessing a sys table by system in a stored procedure you need a direct grant on the underlying table.


1

You are, as per your tag, not using Oracle 12c. The syntax id NUMBER DEFAULT t1_seq.NEXTVAL, is only valid from 12c onwards, so you get an error. On Oracle 11 and prior, you want to use an insert trigger to apply the sequence's nextval to the primary key: create or replace trigger binaries_ins before insert on binaries for each row when (new.id is ...


1

Oracle might be binary, but it will still initiate system commands which in term could involve shells. On UNIX systems, the ulimit command controls the limits on system resource, such as process data size, process virtual memory, and process file size. It is also mostly the users processes affect parent processes. So the parent processes manage the users. ...


1

A query like SELECT * FROM tbl1 AS OF test_restore_point; is not possible. Restorepoints can be used with the command FLASHBACK DATABASE. FLASHBACK DATABASE works similar like a restore. First the whole database will be brought to a state before the time of the restore point. But this is not accomplished by copying a whole backup over each data file ...


1

Generally when someone wants to create a user to run an application, but not own the tables, they create a group that has select on the necessary tables and views and execute on the necessary stored procedures. They can then either create synonyms for the objects that they need or create public synonyms for the objects that everyone needs. You might want to ...


1

Ok, so I had some time to waste, and this is just for the "fun" or "interesting" factor. It's nowhere near that I would use in a real scenario, I have played with it in my lab environment on x86-64 Linux platform, with a few 10g, 11g and 12c databases. At least you can do this even if the database is shut down. When you do a controlfile dump with: alter ...


1

You say Then we gather statistics on the table (and implicitly on the index): but statistics on indexes aren't gathered unless you do this: dbms_stats.gather_table_stats(null,'PART',cascade=>true); What appears to be happening is index statistics are not being gathered (or not gathered correctly) on 10.2.0.5; if specifying cascade=>true ...


1

In the SQL Language Reference you will find under Prerequesites (emphasized by me): The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather ...


1

Instead of thousands of updates, you could insert the data into temporary source table and then run merge against the target table. merge into target_table t using source_table s on (t.id = s.id) when matched then update set t.column1 = s.column1, t.column2 = s.column2, ...;



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