Tag Info

New answers tagged

0

Short version: install the 12c software, create an empty database, and transfer the data with Data Pump. This way you can skip 11.2.0.1+ versions. Long version: OTN Developer License Terms In this, there is the following: No Technical Support Unless Oracle support for the Programs, if any, is expressly included in a separate, current support ...


1

IPs with different created_by: SELECT ip , Count(DISTINCT created_by) As number_of_created_by FROM your_table GROUP BY ip HAVING Count(DISTINCT created_by) > 1 Alternative method: SELECT ip , Min(created_by) As min_created_by , Max(created_by) As max_created_by FROM your_table GROUP BY ip HAVING Min(created_by) < ...


1

As this answer on Stack Overflow indicates there is no built in functionality to do what you want but there are alternatives: export your tables to csv files and then import into MySql using LOAD DATA INFILE. This solution does not scale well for large databases or schemas with clobs and blobs that are images or documents. SQL Developer can help you ...


-2

The following should work as per your requirement. However, if there is a tie on the time too, it would pick a random row. You may want to choose a combination that would give you a unique row. select myTable.* from myTable INNER JOIN (select top 1 Value, Time from myTable where subscriber = 'aaa' order by Value desc, Time ) TT ON myTable.Value = ...


1

Oracle 11g R2 Database Release 2 only support Windows, Linux, and Solaris. So if you want to using Oracle 11g R2 on a Mac OS you will need to using something like Virtual Box or VMware Fusion. You can read more about installing Oracle 11g R2 on these operating system here.


3

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

select a.value from your_table a where a.value = (select max(value) from your_table) and a.time = (select min(b.time) from your_table b where b.value = a.value) That being said a description of the table would help. Is Time a date time value or a string?


0

After researching and reading different threads on this website I was able to resolve my own issue... The syntax below will allow you to place comments either before or after your query, so that the comments display on the output.txt file BEGIN DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE '); END; / Here are some commands you can use while 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.


1

No. A date in Oracle will always have a year, month, day, hour, minute, and second. You can choose to display only certain components of the date in your front end and to give a consistent default to the other components of the date. It's pretty common, for example, to let the time component of the date default to midnight. You could force the year to be ...


0

One ad hoc improvement: poor: for root in all_fitting_roots LOOP FITTING := FITTING + 1; END LOOP; better: SELECT COUNT(DISTINCT ROOT_ID) into FITTING FROM ( SELECT UNIQUE_ID, ROOT_ID, FIRST_NAME, LAST_NAME, POSTAL_CODE, FSA FROM (SELECT p.*, CONNECT_BY_ROOT UNIQUE_ID AS ROOT_ID FROM UNIQUE_PHYSICIAN p START WITH PARENT_PHYS_ID IS ...


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.


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


1

The magic key word is NLS (National Language Support). It will define the dateformat, calender format etc. You can query your current NLS-settings with this command: SELECT * FROM nls_database_parameters; If you want to change it in a session or an instance you can query the current settings using this views: SELECT * FROM nls_session_parameters; -- or ...


-1

I'm afraid there is no way how to do that, archivelogs are a must. Best approach is to use approach recomended by the vendor. Datafiles might be inconsistent anytime, Oracle writes changes into blocks even before the transaction is commited. The only place where you can find an information needed to have data consistent is in (archived) redo-logs. Oracle ...


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

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


1

You can edit the dump file and change the included NLS_LENGTH_SEMANTICS from BYTE to CHAR. Near the beginning of the file you find a line ending like this : ^@02:00:^@^@^D^@BYTE^F^@UNUSED^A^@2^K^INTERPRETED^K^@DISABLE:ALL^@^@ Change BYTE to CHAR using your favorite text editor and save the file. When you will imp that new dump file, all columns will be ...


0

ok, I solved this by: select * from ( select id,id_operator,denumire,loc_den,str, row_number() over (partition by id_operator,loc_den,str order by id_operator) as no from rap where is_punct_lucru=1 order by id_operator ) where no>1


-3

log into sqlplus as username:SYSTEM give previous password sql>password old_password:<oldpassword> new_password:<new password> retype new password:<new password> password is set


1

XE changes the following: tnsnames.ora Path variables takes the default port 1521 for itself and this cannot be changed The best way to install XE and other products is to install XE first, then any other Oracle version afterwards. I think you can fix this by examining the path variables by entering PATH at the command line ensuring that each ...


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


0

Timezone of SYSTIMESTAMP is taken from operating system. However, on operating system this can be user-specific. Do you get different results when you do this on DB Server? $ su oracle $ setenv TZ America/Winnipeg $ sqlplus / as sysdba SQL> select systimestamp from dual; ... SQL> exit $ setenv TZ Europe/Zurich $ sqlplus / as sysdba SQL> select ...


1

If the problem is only cannot start sqlplus, there are two alternatives: 1) If one has the GUI (GNOME or KDE), do the search: "sql command line", and It will appear Run SQL Command Line. 2) Check again the environment variables. Edit the files /root/.bash_profile and /root/.bashrc (for example: # nano /root/.bash_profile) and put the line (at the end): ...


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


1

You are trying to output avg_sal.salary which is not defined. Use alias in the cursor body : .... cursor c1 is select avg(e.salary) AS salary ... Side notes. It seems that parameter passed (ddept_id ) is not used anywhere inside the procedure except printing. Also, it's been long time since JOIN syntax was introduced and it's much more readable ...


0

I think you're going about this the wrong way. First off, I don't think you need a procedure/function to solve your problem. Second, I definitely don't see how a cursor should come in to it. Try the following window query using the OVER() clause: SELECT avg(e.salary) OVER(PARTITION BY d.dname) FROM emp e, works w, dept d WHERE e.eid=w.eid and d.did = ...


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


0

I believe you could just use: select * from ANIMAL.MONKEY where TAIL_COUNT is null; to identify problematic rows, or add clause NOVALIDATE at the end of your alter statement.


0

First thing first: you need to know the username and password to connect to database for step 2 1. check the oracle process runs: On Un*x: ps -ef|grep pmon On Windows: tasklist|findstr /i oracle if the command in any case return output i.e. if the pmon/oracle process is running in your environment the database is running. Go to ORACLE_HOME/bin and ...


1

ALTER USER should be enough, see below. SQL> select banner from v$version where rownum = 1; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production SQL> create user u1 identified by u1; User created. SQL> create user u2 identified by u2 ...


1

you have not shown the roles TEST_USER is granted. Likely they have been granted the DBA role which allows ALTER USER. see the documentation for details. This query will show the roles a user has SELECT * FROM DBA_ROLE_PRIVS where grantee = 'TEST_USER' of course this requires privileges too, unless you have the CATALOG role, if you select from this ...


0

I finally got the answer for this error, this was because of the machine name being big. While setting up a new machine we should ensure that the machine name is not bigger then the bios name. which is 15 chars. This will definitely work.


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.


0

It is normal, because your NLS settings differ on the server and on the client. The NLS conversion always happens on the client side no matter what your server settings are. Compare the output of these: select * from nls_database_parameters; select * from nls_session_parameters;


0

It seems there was some issue with TOAD. I opened TOAD after they granted me DBA Role, yet it was like it did not take effect. I could query the USER_ROLE_PRIVS table and see I had the role, I could GRANT myself the SELECT_CATALOG_ROLE, but when I called DBMS_METADATA.GET_DDL it did not work. Since I had open TOAD after the access had been granted, I ...


0

1) online redefinition uses the methods provided by advanced replication. You can try to create a solution with these methods. You have to create a materialized view that contains only the data you need. Then you have a short period of time where you rename the table and its indexes, grant the permissions on the new objects etc. 2) The simpler way is to ...


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.


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


0

I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish: "I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan. To accomplish the objective in SQLplus, use "Substitution ...


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

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


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


0

If I understand your question correctly, if the trigger on YourTable inserts a row into YourTable as well, then that insertion will fire another instance of the same trigger. See previous answers at: http://stackoverflow.com/questions/16391081/how-to-fix-oracle-trigger-causing-recursion-issue Recursion has issues. Yes, I have written recursive code in the ...


0

Yes, if this was to work, it would generate an infinite loop. But more than likely, it will throw a mutating table exception first. What is the problem that you are trying to solve? A trigger that updates other rows in a table when one row changes is unlikely to be a reasonable solution. If you have cross-row dependencies, that almost always indicates ...


0

I believe this will do the job but the databases I have access to at the moment do not have text indexes. I will confirm later today. SELECT * FROM ALL_INDEXES WHERE index_type = 'CONTEXT'


0

There are 4 types of Text indexes in Oracle, owned by the CTXSYS user (installed optionally), so you can simply query [DBA|ALL|USER]_INDEXES and filter rows by the ITYP_OWNER and ITYP_NAME columns: select * from dba_indexes where ityp_owner = 'CTXSYS' and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH') ; Index types can be found in the ...


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


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;



Top 50 recent answers are included