oracle

Why are Oracle table/column/index names limited to 30 characters?

by

I believe it’s the ANSI standard. EDIT: Actually, I think it’s the SQL-92 standard. A later version of the standard appears to optionally allow for 128 character names, but Oracle doesn’t yet support this (or has partial support for it, insofar as it allows 30 characters. Hmmm.) Search for “F391, Long identifiers” on this page… … Read more

List of foreign keys and the tables they reference in Oracle DB

by

The referenced primary key is described in the columns r_owner and r_constraint_name of the table ALL_CONSTRAINTS. This will give you the info you want: SELECT a.table_name, a.column_name, a.constraint_name, c.owner, — referenced pk c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON … Read more

How to drop all user tables?

by

BEGIN FOR cur_rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN (‘TABLE’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SEQUENCE’, ‘SYNONYM’, ‘PACKAGE BODY’ )) LOOP BEGIN IF cur_rec.object_type=”TABLE” THEN EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ “‘ || cur_rec.object_name || ‘” CASCADE CONSTRAINTS’; ELSE EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ “‘ || … Read more

Number of rows affected by an UPDATE in PL/SQL

by

You use the sql%rowcount variable. You need to call it straight after the statement which you need to find the affected row count for. For example: set serveroutput ON; DECLARE i NUMBER; BEGIN UPDATE employees SET status=”fired” WHERE name LIKE ‘%Bloggs’; i := SQL%rowcount; –note that assignment has to precede COMMIT COMMIT; dbms_output.Put_line(i); END;

How do I turn off Oracle password expiration?

by

To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using: select profile from DBA_USERS where username=”<username>”; Then you can change the limit to never expire using: alter profile <profile_name> limit password_life_time UNLIMITED; If you want to previously check the limit you may use: select … Read more

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)