Displaying CLOB Column with 4000+ characters

I guess you could display the chunks as seperate rows ?

SELECT ROWNUM as chunk_no,ID, SUBSTR (t1.clob_col, (ROWNUM-1)*4000, 4000) AS chunk
FROM t1
CONNECT BY (ROWNUM-1)*4000 <= LENGTH(t1.clob_col)

or if there is a constraint on the maximum size a clob could be in you system you could hard code the number of text columns returned

SELECT SUBSTR (t1.clob_col, 1, 4000) AS pt1,
       CASE WHEN LENGTH (t1.clob_col) > 4000  THEN SUBSTR (t1.clob_col, 4001, 4000) END AS pt2,
       CASE WHEN LENGTH (t1.clob_col) > 8000  THEN SUBSTR (t1.clob_col, 8001, 4000) END AS pt3,
       CASE WHEN LENGTH (t1.clob_col) > 12000 THEN SUBSTR (t1.clob_col, 1201, 4000) END AS pt4
FROM t1

http://stackoverflow.com/questions/7357999/displaying-clob-column-with-4000-characters

How to retrieve the current value of an oracle sequence without increment it?

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = ''
   AND sequence_name = '';

You can get a variety of sequence metadata from user_sequences, all_sequences anddba_sequences.

These views work across sessions.

EDIT:

If the sequence is in your default schema then:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name = '';

If you want all the metadata then:

SELECT *
  FROM user_sequences
 WHERE sequence_name = '';

Hope it helps...

EDIT2:

A long winded way of doing it more reliably if your cache size is not 1 would be:

SELECT increment_by I
  FROM user_sequences
 WHERE sequence_name = 'SEQ';

      I
-------
      1

SELECT seq.nextval S
  FROM dual;

      S
-------
   1234

-- Set the sequence to decrement by 
-- the same as its original increment
ALTER SEQUENCE seq 
INCREMENT BY -1;

Sequence altered.

SELECT seq.nextval S
  FROM dual;

      S
-------
   1233

-- Reset the sequence to its original increment
ALTER SEQUENCE seq 
INCREMENT BY 1;

Sequence altered.

Just beware that if others are using the sequence during this time - they (or you) may get

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

Also, you might want to set the cache to NOCACHE prior to the resetting and then back to its original value afterwards to make sure you've not cached a lot of values.

http://stackoverflow.com/questions/10210273/how-to-retrieve-the-current-value-of-an-oracle-sequence-without-increment-it

Checking oracle sid and database name

I presume select user from dual; should give you the current user

and select sys_context('userenv','instance_name') from dual; the name of the instance

I believe you can get SID as SELECT sys_context('USERENV', 'SID') FROM DUAL; (can't to check this now)

http://stackoverflow.com/questions/6288122/checking-oracle-sid-and-database-name

Default passwords of Oracle 11g?

It is possible to connect to the database without specifying a password. Once you've done that you can then reset the passwords. I'm assuming that you've installed the database on your machine; if not you'll first need to connect to the machine the database is running on.

1. Ensure your user account is a member of the dba group. How you do this depends on what OS you are running.
2. Enter sqlplus / as sysdba in a Command Prompt/shell/Terminal window as appropriate. This should log you in to the database as SYS.
3. Once you're logged in, you can then enter

   alter user SYS identified by "newpassword";
   

   to reset the SYS password, and similarly for SYSTEM.

See also here.

(Note: I haven't tried any of this on Oracle 11g; I'm assuming they haven't changed things since Oracle 10g.)

http://stackoverflow.com/questions/740119/default-passwords-of-oracle-11g

Oracle - what is tnsnames

When clients want to connect to an Instance, they need to know where
the connectionrequest should be made, and how it should be made. The TNSNAMES.ORA file is a text file,located on each client machine, which gives the client these two crucial pieces of information.

Strictly speaking, clients don’t connect directly to an Instance at all. Instead, theyconnect to a Listener process, which knows where to establish the connection to the Instance on the client’s behalf. Therefore, a large part of the “where” information in TNSNAMES.ORA is related to identifying the machine on which the Listener is running, and the port it is listening on. There is also an element which specifies which particular Instance the client wishes to connect to, since a single Listener process can be listening on behalf of many Instances.

The “how” information in the TNSNAMES.ORA file relates mainly to the networking protocol by which the client wishes to connect to the Instance (should it be TCP/IP,SPX/IPX and so on). There is also a part which informs the Listener whether the client isseeking a dedicated or shared server connection.

http://www.scribd.com/doc/58113083/Oracle-what-is-tnsnames

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Symtompts of the Problem:
---------------------------
Whenever you try to conenct to database by providing SYS user name and password it retuens error.
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Cause of The Problem:
--------------------
This is because of the parameter O7_DICTIONARY_ACCESSIBILITY settings to FALSE. 

Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings.

Workaround Example:
---------------------
1)Try to connect by user sys without sysdba privilege.

SQL> conn sys/a
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Warning: You are no longer connected to ORACLE.

2)Connect as sysdba and change O7_DICTIONARY_ACCESSIBILITY
SQL> conn / as sysdba
Connected.
SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE

SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
System altered.

3)Since O7_DICTIONARY_ACCESSIBILITY is static parameter restart is necessary.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 109051944 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

4)Now connect as sys with only password.

SQL> conn sys/a
Connected.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
5)Though you are SYS user but you have not currently have SYSDBA privilege. So, you can't do SYSDBA privilege tasks.

SQL> shutdown immediate;
ORA-01031: insufficient privileges

SQL> show user
USER is "SYS"


Caution:
-----------
Oracle Strongly recommends not to use O7_DICTIONARY_ACCESSIBILITY to TRUE.

http://arjudba.blogspot.com/2008/05/ora-28009-connection-as-sys-should-be.html

Lost Oracle SYS and SYSTEM password?

If your administration is as good as anybodies, you are bound to loose the not-so-frequently used password for the SYS and SYSTEM users of oracle. Here are a few ways I found to re-set those passwords:

Method 1: SQLPLUS (Tested on AIX Oracle 9.2.0.1.0)

Log into the database server as a user belonging to ‘dba’ [unix ] or ‘ora_dba’ [windows ] group , typically ‘oracle’, or an administrator on your windos machine. You are able to log into Oracle as SYS user, and change the SYSTEM password by doing the following:

$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 5 15:32:09 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> show user

USER is "SYS"

SQL> passw system
Changing password for system
New password:
Retype new password:
Password changed
SQL> quit

Next, we need to change the password of SYS:

$ sqlplus "/ as system"
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 5 15:36:45 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where   ::= [/][@] | /
Enter user-name: system
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> passw sys
Changing password for sys
New password:
Retype new password:
Password changed
SQL> quit

You should now be able to log on the SYS and SYSTEM users, with the passwords you just typed in.

Method 2: Creating pwd file (Tested on Windows Oracle 8.1.7)

1. Stop the Oracle service of the instance you want to change the passwords of.
2. Find the PWD###.ora file for this instance, this is usuallly located atC:\oracle\ora81\database\, where ### is the SID of your database.
3. rename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
4. Create a new pwd file by issuing the command: 
   orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
   where ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts.
5. Start the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.

https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/

How to check the privileges assigned to a role

-- select all the role name and granted role

select *
from role_role_privs
order by 1;


-- select all the roles and their privilages

select *
from role_sys_privs
where ROLE = 'TABQ'
order by 1;

------------------------------------------------------

-- show all object privilages in a role

select *
from role_tab_privs
where role = 'TABQ';

https://forums.oracle.com/thread/2140697?start=0&tstart=0

How to create id with AUTO_INCREMENT on Oracle?

There is no such thing as "auto_increment" or "identity" columns in Oracle. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle