New answers tagged

1

I don't really understand why you're trying to pivot anything, so maybe I'm missing your point. But try this: select id, name , regexp_replace(metadata, '.*"a":"([^"]+)".*', '\1') m_a , regexp_replace(metadata, '.*"b":"([^"]+)".*', '\1') m_b from foo; Doesn't rely on the order of the JSON attributes to always be the same. It's not completely safe - ...


0

I don't think it's possible to give any definitive answer of the future of the big RDBMs players. If the subject does interest you, you could do worse than have a look at: http://db-engines.com/en/ranking_trend http://www.dbta.com/ Just like TheGameiswar, I really do not think SQL will fade any time soon. But besides this, what might be true in your part ...


0

can i point to views inside package in oracle?


1

the query does not return 43 rows but 1 row that contains the number 43 the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects. from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table ...


0

memory_target is a dynamic parameter which can be set dynamically not necessarily its changes reflect on various components. Its value can be increased up to memory_max_target value. For details: MEMORY_TARGET and MEMORY_MAX_TARGET


1

Try creating the following index: CREATE INDEX s_contact_idx_001 ON s_contact_lupd_lastupdby_name ( last_upd, last_upd_by, last_name ); You should change the name to correspond to your naming convention and make sure that it is unique.


1

According to the statistics provided user I/O wait time is 6172 out of db time 8383. When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


1

Oracle Instant Client is a light-weight software which helps to run OCI, OBDBC, Pro*C, JDBC without installing full Oracle client. In other hand the Oracle Call Interface (OCI) is an application programming interface (API) that allows applications written in C to interact with one or more Oracle Servers. For details: What is Oracle's instant client and how ...


1

OCI is short for "Oracle Call Interface". It's essentially the API that defines how program (a "client") can communicate with the database server. The instant client provides an implementation of the OCI that can be used by other programs, so that they don't have to implement the communication layer. It is e.g. used by SQL*Plus or other client ...


0

According to the question(though i dont know what is written in the book that you are following) it seem that you want to create new user and you dont have privilege to execute that statement. Keep in mind that database users and APEX users different. In order to do that on login page: In Username, enter admin. In Password, enter the Oracle Application ...


0

Seems to be a bug in Oracle 12.1. Solution is simply: SQL> purge recyclebin;


2

You could just use %_99_% instead of %99%. % means 0 ore more characters _ means exactly 1 character So %_99_% means at least 1 character before and after 99, and anything before and after. SQL> select * from t1; C1 -------------------- 1991 1199 9911 SQL> select * from t1 where c1 like '%_99_%'; C1 -------------------- 1991


1

DECLARE v_query varchar2(1024) := ''; BEGIN FOR x IN (SELECT DISTINCT PROFILE FROM dba_profiles) loop v_query := 'alter profile ' || x.PROFILE || ' limit password_life_time unlimited'; execute immediate v_query; end loop; END; /


3

You can simply do it in PL/SQL in a loop: begin for c in (select distinct profile from dba_profiles) loop execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited'; end loop; end; /


0

Oracle's optimiser is clever, it makes decisions about how best to do certain things (e.g. joins) based on what the contents of the tables are. At least, it can do if it knows what the contents of the tables are. The optimiser uses table statistics to achieve this. If it knows that there is a particular type of data, or a particular distribution of ...


0

Perhaps changing your JOIN condition slightly might help if you want the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join : From: grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE; To: (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL); As shown completely here: SELECT ...


0

If you install from an rpm then it's installed as a service. If you start postgresql from a custom script as user postgres then the process will catch the environment variables but if you start postgresql as a service it won't. You should look for how to pass environment variables to systemd (or sysv) services. I used this example for systemd services. ...


3

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug. Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively an interim patch for this fix (patch ...


0

Yes, it is necessary to drop and recreate the linked server. This takes just moments, so the hit is very small. Likewise, there is no reason to believe that SQL Server would try to read the internal contents of a different relational database.


0

How you solved the issue is the way to solve it because we can't modify a Linked Server. And SQL Server doesnt read the tnsnames.ora. Though the solution is drop and recreate we can use SQL Management Studio to edit the Drop and Create script as follows: 1. Right click on the linked server. 2. Choose Script Linked Server as. 3. Choose New Query Editor ...


0

BEGIN DBMS_WORKLOAD_REPOSITORY.create_baseline ( start_snap_id => --ANY Integer Number_Min Value, end_snap_id => --ANY Integer Number_Max value, , baseline_name => 'batch baseline'); END; / So here min and max value are already in DBA_HIST_BASELINE view. You can't chose random value for snapshot. Remember value already in oracle ...


0

$ORACLE_HOME/dbs is used to store the spfiles, init files and password files on Linux and Unix systems. But as you pointed out the dbs directory not used on windows servers. So on a Windows servers it is probably not needed.


0

Community Wiki answer from a self-answer originally edited into the question (and based on a comment by Balazs Papp). I forgot an incarnation: RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ...


3

Expressions in SELECT evaluated after filter[s] in WHERE applied. So if phones for records with RATING <1 can be converted to number, you don't have errors. Simplified example : SELECT CASE WHEN n1=1 THEN 'a' ELSE 'b' END AS test_str FROM ( SELECT '1a' AS n1 , 0 AS n2 FROM dual UNION ALL SELECT '1' AS n1 , 1 AS n2 FROM dual ) x WHERE n2 =0 -- gives an ...


1

Let me show you what Oracle says about your errors. ORA-01157: cannot identify/lock data file string - see DBWR trace file Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However ...


0

We are using Linked server to connect to Oracle through OLE DB. the using openquery command to pull data down to SQL server. This will help


0

The shadow process term is from SAP,In windows the shadow process is implemented in the form of a shadow thread and in Oracle Server process.


2

You can use TRUNC (date) function which returns date with the time portion of the day truncated to the unit specified by the format model. Syntax: TRUNC(date, fmt) or TRUNC(date). Where fmt can be 'DAY', 'YEAR','HH','MI'. For more format model please refer: ROUND and TRUNC Date Functions Example: SELECT 1 FROM sample_table WHERE ...


0

You can try and check if REMOTE hint works for you. SELECT * FROM SQL_TABLE B INNER REMOTE JOIN linkedServerName.dbname.schema.ORACLE_TABLE A ON A.KEY1 = B.KEY1 AND A.KEY2 = B.KEY2 INNER JOIN SQL_TABLE C ON A.KEY3 = C.KEY1 AND A.KEY4 = C.KEY2


2

This line: if job_id<>jobidnew THEN is not part of the UPDATE which precedes it, so the identifier job_id does not refer to the column by that name but to a separate (undeclared) variable. The way to test whether the UPDATE did anything or not is to test the implicit cursor attribute SQL%notfound, as in if SQL%notfound THEN RAISE e; The ...


0

For me too many indexes means that it is more expensive to insert, update, or delete rows. Because, if the indexes are of any use they need to be maintained. Find all of the indexes with columns that you either don't query, or don't query very often and drop them. Find all of the indexes where the columns are the same, but in a different order drop all but ...


1

After so much of thinking about logic and OPERATORS what I simply did was first I created a simple string in my code like below string date = txtdatefilter.Text; in which I will assign the value of date textbox. My main issue was, I was not able to decode my date values. So I checked the condition in my C# code like below:- if (date == "") { ...


0

If you want to run in parallel you have to design for it. Things to consider is using partitions so they can be scanned in parallel. Don't forget that parallelism in general will try to use full scans on segments. Partition pruning can help reducing the size of your scans. If you really are using pq for this sort of small queries, make sure you have enough ...


3

The problem is with the below bind variable substitution: AND ( sv.project_id = DECODE(:' 1368','0', project_id, :'1368') AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:' ', '0', date_in, :' ') AND vehicle_no = DECODE(:' GJ-06-0Z-9202', '0', vehicle_no, :' GJ-06-0Z-9202') )) Your application should pass the value ...


0

Better solution would be to install docker or virtualbox and run the OracleXe image within one of those containers. Once docker is running you can easily launch a new OracleXe container with two commands. Already configured to allow easy access via SQLDeveloper. I tested my own steps by downloading the docker toolbox, installing and running the OracleXe ...


0

Honestly, if your looking for a nimble solution for OracleXe your best option would be to run a pre-built docker container that already has oracle XE configured. You can go about this two different ways. Run VirtualBox with a Ubuntu image on your Mac. Install docker within that ubuntu image. Once docker is installed you can do a simple "docker pull ...


1

OK, then think the other way around. Something like the general outline: Create a Linked Server from Oracle to SQL Server and SQL Server to Oracle. From Oracle select the 100 rows of SQL Server data and store in a temporary table in Oracle. Run the query in Oracle to join/filter/whatever the 1 billion Oracle rows by the 100 SQL Server rows in the ...


1

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause): create or replace function func_get_Open_Days_2 ( ARG_SUBMITTED_BY_PERSON_ID in ...


1

Not in 11g. Not even in 12c, but 12c makes life a bit easier, you can delay the "rebuild" part until a more appropriate time. In 12c there is a new feature, called Asynchronous Global Index Maintenance. You can drop the partition, and that still leaves the global index usable, and marks the no longer needed index entries orphaned. Then later, you can clean ...


5

This is a Gap & Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions Here is how the query works: YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format. then ROW_NUMBER() ...


0

At this moment, if you want a more up to date replacement for sqlplus, I would say, go for SQLcl. It is part of sqldeveloper and has a lot of nice features you always wanted to have but were afraid to ask for. You can download it for free here sql-developer/downloads (check bottom of page for SQLcl download) It has keyword completion column and tablename ...


0

Conflicts are automatically detected when a merge or refresh operation is requested, and they are presented to the user in conflict views. There is one conflict view for each table.Each conflict view has a name in the form table_name_CONF. To keep the rows that you want, first of all let me show you the syntax of ResolveConflicts procedure. ...


1

First of all you need to make those rows unique, otherwise Oracle wouldn't know which value to choose. In SQL Server I would use something like this: WITH NewT AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY fileNo , folder, fileType ORDER BY (SELECT NULL)) AS RowN, fileNo, folder, fileType ...


1

Using the ranking ROW_NUMBER() function will work. First give row numbers to all rows in both tables, then join using these row numbers, then update: with oldt as ( select fileNo , folder, fileType, row_number() over (partition by fileNo, folder order by fileType) as rn from oldtable ), ...


1

This is absolutely normal when you try to perform a complete recovery from a hot backup, because that is how hot backups work, you will never have the latest changes in your backups. Your backup includes archivelogs up to sequence 29. The next changes were in log sequence 30, but log sequence 30 became the current redo log at the time your backup was ...


1

I implemented what you are trying to do this way: the transformation to a user defined object happens in a packaged procedure that is called by triggers. ---all comments and debugging removed to keep it simple---- CREATE OR REPLACE TRIGGER when_close_ini AFTER UPDATE OF end_date ON YOUR_TABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW ...


0

I addressed this with the PL/SQL project manager years ago, and was told that it is not possible. You can't address the :NEW or :OLD columns as a record and convert them to anything. You can create a temporary table that is either identical to the table that you are writing the trigger for or generic, i.e. table_name, column_name, column_type, clob. You ...


0

Unfortunately Oracle does not have anything like for example IF EXISTS in MySQL. You can do the above in a PL/SQL block, catch the exception, then do nothing with it. begin execute immediate 'drop table continent'; exception when others then null; end; /


1

We can compress B*Tree indexes. This removes redundancies from concatenated indexes. We dont get compression for free. Oracle spends more time processing the compressed data while maintaining and searching the index during query execution. Compression may increase CPU time(Extra processing overhead) while reducing I/O time. so be aware of tradeoff. ...


0

Parallel Desktop is a virtualization application provided by Apple/Mac. There is no difference to install Oracle on other virtualization software and Parallel Desktop. You can download and install it, but you need to know the requirements(RAM, Hard Disk, OS:32/64bit, Network etc) to install Oracle Database. The details about it can be found on Oracle's ...



Top 50 recent answers are included