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

You can grant SELECT ANY TABLE, however, that is seen by some as "excessive" privileges, and should be questioned. On the plus side, Oracle 11 keeps key system views hidden even from that grant .. so it's not nearly as bad as it once was ;) It would be best to build ROLEs and grant the SELECTs to the required tables to the ROLEs ... then grant the ROLE to ...


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

With Grid Infrastructure, you need to modify the configuration with srvctl, for example after upgrading to a new release: srvctl stop database -d db_unique_name export ORACLE_HOME=<..new home...> export PATH=$ORACLE_HOME/bin:$PATH srvctl upgrade database -d db_unique_name -o oracle_home Alternatively you can just re-register the database (add back ...


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

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.


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


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


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


1

According to the output of the command ps -ef|grep pmon shows that the instance name of the standby database is teststan. [oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon But in the description of listener on standby database, SID_NAME is given as 'test'. The Oracle ...


0

UTL_MAIL strongly rely on TCP (Network) and the number of active TCP connections that can be open for a session is 16, per documented Bug 8262564. Bug 8262564 got fixed in Oracle 11g 11.2.0.1 or later and also on Oracle 11g 11.1.0.7 as one-off patches. Bug 8262564 is quoted as reference to state that the number of active TCP connections that can be open ...


2

Thats is not what you should execute. sma is the procedure, c4 is simply a cursor in it. You do not execute the cursor local to a procedure, you can not even access it. Execute the below: set serveroutput on begin sma; end; /


0

It somewhat begs the question of why not use rman for your backups instead of user managed backups. rman has been the robust solution since at least 9i, and here we are with 12c being mature.


0

Yes, it can. As mentioned in Making User-Managed Backups of Online Read/Write Tablespaces copies of the datafile blocks modified are written to th redo logs. But checkpoints are no problem.


0

When you use SQLPlus and make a bequeath connection (e.g., not going through the listener and not specifying the SID/Service Name in the connect string or using a TNS alias), SQLPlus/Oracle uses the combination of ORACLE_SID and ORACLE_HOME to uniquely identify the instance that it connects to. Important to note: You may have multiple instances running on ...


-4

You can use MATERIALIZED VIEW to get faster response then the simple view in oracle. here is the syntax for the same. try this in your query. CREATE MATERIALIZED VIEW hr.employees AS SELECT * FROM hr.employees;


8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


3

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table. UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you ...


0

You need to add a grouping ID to your data. You can do that with the row_number analytic function in a sub factored query, then you can use the generated row number as the value to group your column groups by: with t1 as ( select tbl.* , row_number() over (partition by col_id order by col_group) rn from tbl1 ) select * from t1 pivot ...


2

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing: alter session set NLS_LENGTH_SEMANTICS='CHAR'; Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener: >export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 >export ORACLE_SID=<YOURINSTANCESID> ...


1

I can't do hard PIVOTs, it's just one of those things that breaks my brain. You need @bluefeet for that. Anyway, you can do it the oldskool way with case ... when and max() if the number of groups per col_id is finite (I'm assuming the Col_Group names are unknown, and there's a finite number: select Col_Id, ...



Top 50 recent answers are included