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

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