Create Oracle User

--Selct all users
SELECT * FROM ALL_USERS;
SELECT * FROM SYS.USER$;
/
--To change the password for a user
ALTER USER username IDENTIFIED BY new_password;
/
--To Dropping a Database User CASCADE  Objects
DROP USER user2delete CASCADE;
/
--create new user
--This CREATE USER statement would create a new user called user_apn in the Oracle database whose password is user_apn
DROP USER user_apn CASCADE;
/
CREATE USER user_apn IDENTIFIED BY user_apn;
/
GRANT CONNECT, RESOURCE TO user_apn;
/

Oracle Password Expired

Run the following command

> sqlplus system/admin@localhost/xe

SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 20 16:36:23 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for system
New password:
Retype new password:

or development you can disable password policy if no other profile was set (i.e. disable password expiration in default one):

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Identify Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

    SELECT owner,
           object_type,
           object_name,
           status
    FROM   dba_objects
    WHERE  status = 'INVALID' and owner='ownername'
    ORDER BY owner, object_type, object_name;

How to connect SQLPlus without tnsnames.ora

Open RUN (window + R) and copy the following command (replacing database properties)

sqlplus user/pwd@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostName)(Port=portNumber))(CONNECT_DATA=(SID=systemId)))"

Duplicate rows from Oracle table

Remove duplicated including rows having "null"values

DELETE
FROM table_name t1
WHERE t1.rowid > ANY
  (SELECT t2.rowid
  FROM table_name t2
  WHERE (t1.col1 = t2.col1
  OR (t1.col1   IS NULL
  AND t2.col1   IS NULL))
  AND (t1.col2   = t2.col2
  OR (t1.col2   IS NULL
  AND t2.col2   IS NULL))
  );

Find and delete duplicate rows

DELETE
FROM table_name t1
WHERE rowid <>
  (SELECT MAX(rowid)
  FROM table_name t2
  WHERE t1.col1 = t2.col1
  AND t1.col2   = t2.col2
  );

Oracle: Function to concatenate column output

 Type:

create or replace
TYPE        "STRINGAGGTYPE"                                          as object
    (
       theString varchar2(4000),
   
       static function
            ODCIAggregateInitialize(sctx IN OUT StringAggType )
            return number,
   
       member function
           ODCIAggregateIterate(self IN OUT StringAggType ,
                                value IN varchar2 )
           return number,
  
      member function
           ODCIAggregateTerminate(self IN StringAggType,
                                  returnValue OUT  varchar2,
                                  flags IN number)
           return number,
  
      member function
           ODCIAggregateMerge(self IN OUT StringAggType,
                              ctx2 IN StringAggType)
           return number
   );

Function:

create or replace
FUNCTION stringAgg(input varchar2 )
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING StringAggType;

Now, you can run a query like this:

select deptno, stringAgg(ename) enames from emp group by deptno;

Result:

DEPTNO  ENAMES
----------  ------------------------------
        10  CLARK,KING,MILLER
        20  SMITH,FORD,ADAMS,SCOTT,JONES
        30  ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Oracle: functions to join and split strings in sql

Join:

create or replace
function join
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end join;
 /

Now, you can run a query like this:

select join(cursor(select ename from emp))from dual;

Split:

create or replace TYPE "SPLIT_TBL" as table of varchar2(32767);
/

create or replace
FUNCTION split(
      p_list VARCHAR2,
      p_del  VARCHAR2 := ',' )
    RETURN split_tbl pipelined
  IS
    l_idx pls_integer;
    l_list VARCHAR2(32767):= p_list;
  BEGIN
    LOOP
      L_IDX   :=INSTR(L_LIST,P_DEL);
      IF l_idx > 0 THEN
        pipe row(SUBSTR(l_list,1,l_idx-1));
        l_list:= SUBSTR(l_list,l_idx  +LENGTH(p_del));
      ELSE
        pipe row(l_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  END split;
 /

Now, you can run a query like this:  

SQL> select * fromtable(split('one,two,three'));


one
two
three

Ref: create functions to join and split strings in sql

Creating Database link

CREATE DATABASE LINK "<linkdemo>" CONNECT TO <user> IDENTIFIED BY <password> '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = <host name> )(PORT = < Port Number>)) (CONNECT_DATA = (SID = <SID>)))'


linkdemo = Name of the link.
host name= where the database is installed
Port Number=TNS listener port of the database
SID=database name
user=Database user
password=Database password

QUERY TO VIEW THE PROCEDURE

USER_SOURCE describes the text source of the stored objects owned by the current user.

SELECT * FROM USER_SOURCE;

NAME: Name of the object
TYPE: Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE: Line number of this line of source
TEXT: Text source of the stored object

More:-
ALL_SOURCE: describes the text source of the stored objects accessible to the current user.
DBA_SOURCE: describes the text source of all stored objects in the database.

View oracle table structure

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='MY_TABLE';

In SQLPLUS, DESCRIBE command can be used.

DESCRIBE 'MY_TABLE';

Search for column names in Oracle

SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='COLUMN_NAME' ;
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='COLUMN_NAME' ;

Try the following also:

• DBA_TAB_COLUMNS
  
• all_constraints (user_constraints, dba_constraints)
• all_indexes
• all_tables
• all_tab_columns

Copy table structure

CREATE TABLE MY_NEW_TABLE AS
SELECT * FROM MY_EXISTING_TABLE WHERE 1=2;

above SQL will create new table MY_NEW_TABLE having structure same as MY_EXISTING_TABLE.

How to Delete All Objects for a User in Oracle

Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.

If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name|| 
DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;

Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects

Ref:
http://forums.oracle.com/forums/message.jspa?messageID=1057359

SQL Oracle Command

CREATE DATABASE link
--------------------
CREATE DATABASE link link_name CONNECT TO user_nameIDENTIFIED BY use_pwd USING 'host:port/service_name';

Test the created Link
----------------------
select * from dual@"link_name";