DBlink s

select owner,db_link,username,host from dba_db_links where owner='DdBMON';

select owner,db_link, host from all_db_links;


select instance_name from v$instance@OMD_CACHE;

select max(lengthb(app_lic_nbr)) from v_omdirect_cutadr@omd_cache;

set heading off;

set echo off;

Set pages 999;

set long 90000;

SELECT DBMS_METADATA.GET_DDL('DB_LINK','OMD_CACHE','PUBLIC') from dba_db_links db;

select DB_LINK,owner from dba_db_links where db_link='OMD_CACHE';

$ sqlplus "/as sysdba"
DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20
COL DB_LINK FORMAT A30
select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;

set head off
set pages 0
set long 9999999

select dbms_metadata.get_ddl('DB_LINK','PMF_LINK','STAT_PRODGOV') from dual;


select DB_LINK,owner from dba_db_links where db_link='PM_LINK';


select dbms_metadata.get_ddl ('DB_LINK', db_link, owner) from dba_db_links where owner like 'DBdMON';

or


SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) Text from dba_db_links db WHERE owner like 'DBMON';


CREATE DATABASE LINK POEMDB_BACKUP_REPORT connect to DBdMON identified by "Mokk" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OMIP.omi.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = POEddMDB)))';

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select * from global_name;
SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# and U.NAME in ( 'PRINCEADMIN');



create database link PRINCEADMIN.OARTEST.OMI.COM
connect to APPSQ identified by "OMAPPSQTEST#13" using '(DESCRIPTION=(ADDRESS=(PR
OTOCOL=tcp)(HOST=omidrl031.omi.com)(PORT=1524)) (CONNECT_DATA= (SERVER = DEDICAT
ED) (SERVICE_NAME = omtest)))'
;

Drop database link
==============

select * from dba_db_links where OWNER='SCOTT';
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
SCOTT LINK1 SCOTT testdb 04-NOV-11




SQL>drop database link scott.LINK1;
drop database link scott.LINK1
*
ERROR at line 1:
ORA-02024: database link not found


ORA-02021: DDL operations are not allowed on a remote database





create a procedure

SQL> CREATE PROCEDURE scott.drop_db_link AS

BEGIN

EXECUTE IMMEDIATE 'drop database link LINK1';

END drop_db_link; 2 3 4

5 /

Procedure created

SQL> exec scott.drop_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

no rows selected

Troubleshooting Dblinks issues.

1. What is the local and remote database version involved in database link? local database version 11.2.0.3.0 remote database version 18.0.0.0.0 2.For below query uploaded HTML format. set markup html on spool dist_queries.html show user select name from v$DATABASE; SELECT * FROM DBA_DB_LINKS; SELECT * FROM GLOBAL_NAME; select * from DBA_2PC_PENDING; select * from DBA_2PC_NEIGHBORS; select * from sys.pending_trans$; select * from SYS.PENDING_SESSIONS$; select * from SYS.PENDING_SUB_SESSIONS$; select * from V$GLOBAL_TRANSACTION; SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE'; select * from v$active_services; spool off set markup html off 3. current setting for SQLNET.EXPIRE_TIME on both local and remote database. local database sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /oraclebase/app/oracle Remote database # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10 NAMES.DEFAULT_DOMAIN = HQ.DAL.OMI.COM SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Now check the DBA_2PC_PENDING view for the LOCAL_TRAN_ID.

SET LINESIZE 500;
COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A50
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
   FROM DBA_2PC_PENDING
/

How to Drop SCHEMA Objects in Oracle

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

DROP SCHEMA OBJECTS
-------------------
set head off
set pagesize 0
set linesize 300

spool target_schemaname_drop_obj.sql

select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB','LOB') order by  object_type,object_name
/

spool off;
set head on

@ target_schemaname_drop_obj.sql
exit

DROP OTHER OBJECTS (If required)
------------------
set head off
set pagesize 0
set linesize 300

spool target_schemaname_drop_other_obj.sql

select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/

spool off;
set head on

@target_schemaname_drop_other_obj.sql
exit

ls -lrth target_schemaname_drop*.sql

vi target_schemaname_drop_obj.sql  /  vi target_schemaname_drop_other_obj.sql

:wq

sqlplus "/as sysdba"

@target_schemaname_drop_obj.sql

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

exit

Objects

Script to find out total number of oracle schema objects and its size

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
 select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;


Owner                               Objects      size MB
------------------------------ ------------ ------------
OMDSS                                43,855    2,105,198
OMEDW                                19,627    1,159,418
OMSTG                                 7,815      776,717
OMDSS_PRF                            27,567      575,393
OMEDW_PRF                             6,507      480,971
OMSTG_PRF                             4,104      351,780
OBIEDW                                5,209      314,094
SYS                                  14,317      187,567
OBIEDW_PRF                            4,515      177,136
PMFODS                                  912       44,965
PMFODS_PRF                              882       30,982

SELECT owner,
segment_name,
table_name,
--partition_name,
--segment_type,
sum (bytes / 1024/1024) "size_in_mb"
FROM dba_segments
WHERE segment_type IN ('INDEX', 'INDEX PARTITION')
and owner='OMDSS_PRF'
group by  owner,segment_name,table_name

How To Change/Restore User Password in 11G

Password 

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password

SQL> create user amit identified by amit;

User created.

SQL> grant create session to amit;

Grant succeeded.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT                           9DEC0D889E8E9A6B

SQL> alter user amit identified by abc;

User altered.

SQL> conn amit/abc
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user amit identified by values '9DEC0D889E8E9A6B';

User altered.

SQL> conn amit/amit
Connected.

In 11g if you query password field, it will return NULL.

SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT

Let's first see Case-sensitive password feature in 11g and then steps to change/restore passwords

SQL> create user amit identified by AMIT;

User created.

SQL> grant connect,resource to amit;

Grant succeeded.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

This behavior is controlled by "sec_case_sensitive_logon" initialization paramter. If the value is true then it will enforce case sensitive passwords

SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon';

NAME                                     VALUE
---------------------------------------- --------------------
sec_case_sensitive_logon                 TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

Now to reset the password in 11g, we need to query spare4 column in user$ table

SQL> select spare4 from user$ where name='AMIT';

SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL> alter user amit identified by abc12;

User altered.

SQL> conn amit/abc12
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';

User altered.

SQL> conn amit/abc12
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)
for example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5;

USERNAME                       PASSWORD
------------------------------ --------
SYS                            10G 11G
SYSTEM                         10G 11G
OUTLN                          10G 11G
DIP                            10G 11G

In this case it means both old and new-style hash values are available for the users, the new hash value is stored in the USER$.SPARE4 column, as long as this remains NULL it means the password has not been changed since the migration and the user will have the old case insensitive password.

SQL> create user test identified by test;

User created.

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in ('AMIT','TEST');

USERNAME                       PASSWORD
------------------------------ --------
AMIT                           11G
TEST                           10G 11G

As I had reset password using only spare4 string, password will be case -sensitive irrespective of setting for sec_case_sensitive_logon parameter value. i.e why we see value of "11G"  for user Amit.

Update

When resetting the password, we need to also query password column from user$ column if we wish to use case-insensitive feature in future. i.e In my above example I used only spare4 column value to reset the password. Now if I set sec_case_sensitive_logon=false , I will not be able to connect.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

In case we wish to use both, we need to set identified by values ‘S:spare4;password’. As I didnot use password field while resetting, I find that password field in user$ is empty. To correct it, I had to change the password again.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
                               S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL>  alter system set sec_case_sensitive_logon=true;

System altered.

SQL> alter user amit identified by AMIT;

User altered.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

So to reset the password, following needs to be used.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> alter user amit identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

User altered.

Thanks to Laurent for pointing this. You can see his article for more information.You can use below code to get the password script

?

1 2 3

select 'alter user '||name||' identified by values '''||password||''';' from user$ where spare4 is null and password is not null union select 'alter user '||name||' identified by values '''||spare4||';'||password||''';' from user$ where spare4 is not null and password is not null;