Hot answers tagged

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; /


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


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


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

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


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

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

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

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


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


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; /



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