Daily Archives: November 18, 2013
list DB link
SELECT B.NAME OWNER, A.NAME DB_LINK, A.USERID USERNAME, A.HOST, decode(bitand(a.flag, 1), 1, ‘YES’, ‘NO’) shared, a.authusr FROM SYS.LINK$ A, SYS.USER$ B WHERE A.OWNER# = B.USER# and (B.NAME =’ANAR’ OR B.NAME = ‘PUBLIC’) order by 1,2,3,4;
ORA-39083: Object type OBJECT_GRANT failed to create with error
I imported a schema from Oracle 11.2.0.1 database to Oracle 11.1.0.7 database. I got the following errors Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39083: Object type OBJECT_GRANT failed to create with error: ORA-00990: missing or invalid privilege Failing sql is: GRANT ON “XXXX″.”XXXX” TO “XXXX” Solution: It is bug. The details is referred to the Doc ID […]
Oracle User Account Password Expired
select username , expiry_date , account_status from dba_users where expiry_date < sysdate + 120 and account_status IN ( ‘OPEN’, ‘EXPIRED(ANAR)’) order by account_status, expiry_date, username;
Temporarily change user password
1. Get the password string of user SQL> select name, password from sys.user$ where name=’ANAR’; NAME PASSWORD —————————— —————————— ANAR 90EEB9F64DD6C1BF 2. Change the password of user SQL> alter user ANAR identified by ANAR; 3. Restore the password of the user SQL> alter user ANAR identified by values ’90EEB9F64DD6C1BF’; User altered. SQL> select name, password […]
Load the SQL set from the cursor cache
DECLARE stscur dbms_sqltune.sqlset_cursor; BEGIN OPEN stscur FOR SELECT VALUE(P) FROM TABLE(dbms_sqltune.select_cursor_cache( ‘parsing_schema_name <> ”SYS”’,null, null, null, null, 1, null, ‘ALL’)) P; — populate the sqlset dbms_sqltune.load_sqlset(sqlset_name => ‘STS_for_upgrade’, populate_cursor => stscur); END; /
DBMS_SCHEDULER.create_job
BEGIN DBMS_SCHEDULER.create_job ( job_name => ‘MIS_CLIENT_REPORT’, job_type => ‘PLSQL_BLOCK’, job_action => ‘BEGIN OC_YKB.MIS_CLIENT_REPORT.RUN_REPORT; END;’, start_date => to_date(‘18.11.2013 04:00:00′,’dd/mm/yyyy hh24:mi:ss’), repeat_interval => ‘FREQ=MONTHLY; BYMONTHDAY=1;’, end_date => NULL, enabled => TRUE, comments => ‘Reporting..’); END; /*Enable job*/ BEGIN DBMS_SCHEDULER.ENABLE (‘MIS_CLIENT_REPORT’); END; /*disable job*/ BEGIN DBMS_SCHEDULER.disable (‘MIS_CLIENT_REPORT’); END; /*drop job*/ begin dbms_scheduler.drop_job(job_name => ‘MIS_CLIENT_REPORT’); end;
create a read only user in Oracle 11g
SQL> create user anar identified by anar; User created. SQL> grant create session, select any table, select any dictionary to anar; Grant succeeded. SQL> conn anar/anar Connected. SQL> create table t(id number); create table t(id number) * ERROR at line 1: ORA-01031: insufficient privileges SQL> desc test.t Name Null? Type —————————————– ——– —————————- ID NUMBER […]
anargodjaev.wordpress.com 