Useful Oracle DB Hints

Importing data

imp userid=baldo_test/baldotest1@localbaldo file=c:\oracle\temp\localbaldo.dmp log=c:\oracle\temp\imp.log

 

Export data

exp  userid=baldo_test/baldotest1@localbaldo file=user1.dmp

Dropping a user
[code]DROP USER USERNAME CASCADE;[/code]

Creating a user
[code]
CREATE USER USERNAME
IDENTIFIED BY "PASSWORD"
ACCOUNT UNLOCK;
[/code]

Truncate Table PL/SQL
http://stackoverflow.com/a/11030009

[code]
BEGIN
FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = ‘R’)
LOOP
EXECUTE IMMEDIATE (‘alter table ‘ || c.table_name || ‘ disable constraint ‘ || c.constraint_name);
END LOOP;
FOR c IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE (‘truncate table ‘ || c.table_name);
END LOOP;
FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = ‘R’)
LOOP
EXECUTE IMMEDIATE (‘alter table ‘ || c.table_name || ‘ enable constraint ‘ || c.constraint_name);
END LOOP;
END;
[/code]

 

Searching a String in all tables

[code]
DECLARE

match_count integer;

v_search_string varchar2(4000) := ‘VALUEGOESHERE’;

BEGIN

FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns

WHERE data_type in (‘CHAR’, ‘VARCHAR2’, ‘NCHAR’, ‘NVARCHAR2’,

‘CLOB’, ‘NCLOB’) )

LOOP

BEGIN

EXECUTE IMMEDIATE

‘SELECT COUNT(*) FROM ‘||t.owner || ‘.’ || t.table_name||

‘ WHERE ‘||t.column_name||’ = :1′

INTO match_count

USING v_search_string;

IF match_count > 0 THEN

dbms_output.put_line( t.owner || ‘.’ || t.table_name ||’ ‘||t.column_name||’ ‘||match_count );

END IF;

EXCEPTION

WHEN others THEN

dbms_output.put_line( ‘Error encountered trying to read ‘ ||

t.column_name || ‘ from ‘ ||

t.owner || ‘.’ || t.table_name );

END;

END LOOP;

END;

/
[/code]

Leave a Reply

Your email address will not be published. Required fields are marked *