Tag Info

New answers tagged

0

Note the extra brackets. SQL> variable d varchar2(100); SQL> exec :d := '09-DEC-2014'; PL/SQL procedure successfully completed. SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual; ''''||(TO_DATE(:D,'D -------------------- '10-DEC-14' Changing date format: SQL> alter session set nls_date_format='DD-MON-YYYY'; Session ...


0

As @a_horse_with_no_name stated in a comment, there is a function USER that returns the user of a session, so select USER from DUAL is a way to find out the user of a session.


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


0

Since the client is at the same server as the database you could try the IPC protocol instead of TCP as a workaround. It is not only independent of the TCP traffic conditions but also MUCH quicker than TCP. Your connection string would be something like jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY = ...


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


0

If disk space is not at a premium, you could be able to create a "work" copy of the table, say my_table_new, using CTAS (Create Table As Select) with criteria that would omit the records to be dropped. You can do the create statement in parallel, and with the append hint to make it fast, and then build all your indexes. Then, once it it finished, (and ...


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


0

And sometimes following Oracle documentation solves quite some hassle. Make sure Basic Instant Client is deployed before running the ODBC install script. Unzip the ODBC Instant Client package, then execute the odbc_update_ini.sh script with Driver Manager installed directory as a command line argument. (For complete syntax, please run "odbc_update_ini.sh" ...


0

You may need to reset the "dbsnmp" user's password from the database. Then update the password of dbsnmp user in OEM: Targets --> Select database and press "Configure".


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


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


0

I just deployed Instant Client in a directory and after updating LD_LIBRARY_PATH (including the lib directory of Instant Client) and ORACLE_HOME I was facing the same issue. I now have updated PATH (including the bin directory for Instant Client) and this solved the problem.


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/


0

The chances are that it's more efficient to use a FTS rather than an index scan. If you're doing: SELECT a,b,c FROM table1 with no WHERE clause, then the RDBMS is going to have to read the whole table anyhow. Depending on the size of the table, it's almost certainly quicker to read the whole table rather than look up 1 row at a time. The RDBMS can read ...


1

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


0

On Unix/Linux system you can use the user level configuration files to override system level entries. System-Level User-Level Configuration File Configuration File ------------------ ------------------- sqlnet.ora $HOME/.sqlnet.ora tnsnames.ora $HOME/.tnsnames.ora The system-level configuration Files can be found in the ...


0

It's a typo. I changed the REFERENCES Emplyee to REFERENCES Employee sorry, beginner mistake.


0

Your code above does not create the trigger, it just puts it to the SQL buffer, because this is how SQL*Plus works. One side effect of this is regular SQL statements not executing, or not getting the prompt back. Use a slash / after the END TRIGGER01; clause: CREATE TRIGGER TRIGGER01 AFTER INSERT ON WAREHOUSE REFERENCING NEW AS NEWWAREHOUSE FOR EACH ROW ...


0

As an example of the possibilities: because it's pl/sql in back, one can define an object to represent a row, define a pl/sql table of those objects, create type T_MY_TABLE as table of t_my_object; and end with OPEN p_recordset FOR select * from table( v_my_table ); So rather than constructing mongo, often dense and/or cryptic direct queries on a ...


0

Solved! the issue was LOCAL_LISTENER was not set to orcl so SQL> alter system register; SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both; did the trick !


0

Looks like Tom Kyte has the answer: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:22541885338185 Gandolf, you were technically correct according to what Tom responds with, but for whatever reason (bug? user error?) I couldn't get that to work. So I tried the workaround: 15:09:47 system@DEV> drop procedure system.kill_dev_session; ...


0

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.


0

I think you should have a service called 'orcl' when you do a lsnrctl status But at the moment you don't. Or inversely try to change your tnsname.ora (SERVICE_NAME = orcl) to 'CLRExtProc'.


0

An issue I found on a similar problem was that the ‘first’ grant on the object had to be done ‘with grant option’. Eg for the issue above, this would therefore mean: GRANT SELECT ON <underlying table owner>.<table_name> TO CST0 WITH GRANT OPTION; Then running GRANT SELECT ON <schema>.<view_name> TO CST0; should work. Note this ...


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

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


0

If the user that you added to use the Linked Server is not for the login you are currently using (and it is the only one you've set up), when you click OK, the connection test will fail because the current user will have no valid mapping to use the Linked Server. To fix this simply create a valid mapping for your login as well. That way when you click OK ...


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


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


0

WITH ADMIN OPTION Specify WITH ADMIN OPTION to enable the grantee to: Grant the role to another user or role, unless the role is a GLOBAL role Revoke the role from another user or role Alter the role to change the authorization needed to access it Drop the role https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm Like Gandolf989 ...


0

Oracle here warn you from dropping datafile because it contain amount of data, to make sure the datafile is completely empty please execute the following command col tablespace_name format A30 heading tablespace col file_name format A40 col tot_KB format 99,999,999 col tot_freeKB format 9,999,999 col Kb_max format 9,999,999 select f.tablespace_name ...


0

For me this line seems most telling: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS" You should check to see if the user that you exported had a job scheduled using a program owned by someone else. If so you are trying to schedule a job for a program that does not exist. You can create the program, then do the import as long as the ...


0

I generally use PL/SQL collections for this. Essentially you create a table type, then create a variable with that table type, then do a select for update to get all of the data that you want to update, then do a forall update to update the data. Since you are updating the a table the record type should be that table. Such as: TYPE sku_tab_type IS TABLE OF ...


0

By default you can only see a table if you own it, have been granted explicit select privileges, have select any table, or have dba or sysdba roles. or finally if you grant the select privileges to the public role. As I explained in another post by you, you can create a view with all of the privileges that a regular user would need, then grant that role to ...


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


0

It's the moment of the last redolog archival, before some point in time during the RMAN duplicate command. You can test this easily, if you either manually provoke redolog switches (and thus subsequent archivals), or even better: write some script to provoke very frequent and unnecessary redolog switches. In which you perform some insert/update/delete, to ...


0

Rename (archive) the table. If wanted, remove all records of that table by means of a DELETE command. Then you control when you really lose the data, by means of doing the commit on that DELETE command.


0

You can actually start that service, while you are waiting on it to start in another session. Even if both starts occur at the same time, Windows should be able to handle that correctly, and end with the service being up. Or, you sleep. What time ? Put something, and test. Ain't that hard, no ?


0

It COULD be simple, but then it's down to luck, more than to anything else. The way of working is very simple : get all processes listed of the main database, ignore the ones from the standby. Then, do the same with the standby database : collect all process info. What is important here, is that you capture this while all is normal. Then you compare both ...


0

1) If you want the headings in one line, you have to use a UNION with a select from dual. Example: select '"'||'col1'||'","'||'"col2"'||'","'||'"col3"' from dual union select '"'||col1||'","'||col2||'","'||col3||'"' from table; 2) This one is tricky. It helps to know what ascii character your line breaks actually are. Depending on how they were ...


0

I would also say it depends on the volume and what you want to do. Does your graph need to use real time data ? and does it use all data from these 6 databases ? In some situation a classic ETL (extract,transform,load) to a new database supporting heavy queries is the answer If you need quasi-real time data: Replicating heterogeneous databases is not easy, ...


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


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


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


0

There's lots of things that can cause this. But config files do not often change by themseleves, also they should not change on a server reboot. What I'd like to know is : how did you know to which IP to reset to ? I mean, the TNS points to "MYDBSERVER". How come YOU are changing the IP on that server ? Isn't that a DNS server that does that, or a router ? ...


0

I would move the old records (closed incidents) to another table, not to another partition in the same table. Why ? Because if there is a LOGICAL reason, in my mind it makes more sense to put them as much seperate as possible, on a physical level. Consider this : you can store incidents, but your data schema can also store "open incidents" and "closed ...


0

Definately, exporting that many rows to a tool like Excel, sounds like the worst solution to what sounds like a normal action. This being : updating data. In the opening post, I don't see any reason why the data has to leave the Oracle database. Certainly not to a tool like Excel.


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


0

I suppose you could write the information directly to the redo log at each modification, but that would mean a lot of very small 'write' operations on disk. If you cache this instead you can at least wait the next 'COMMIT' and do less 'writes'. In my experience this buffer does not need to be very large because of this, on a typical system COMMITs will ...



Top 50 recent answers are included