Alter column size of an Oracle Table

Use below statement to increase or decrease column size of a table.

ALTER TABLE table_name
MODIFY column_name VARCHAR2(100)

Renaming an Oracle Table

Use below query to rename an Oracle table..

ALTER TABLE table_name RENAME TO new_table_name;

But the below query will gives an error.

alter table MY.TEST rename to MY.TEST_BK;

To avoid it use new table name without metioning the Schema name as below.

alter table MY.TEST rename to TEST_BK;

ORA-00257:archiver error. Connect internal only, until freed.

When log in to the database this error will show up sometimes.
This is because the db_recovery_file_dest disk space is full.

Free up some space by deleting old archives or expand the size of the  db_recovery_file_dest by the below command.

ALTER SYSTEM SET db_recovery_file_dest_size=100G;

Find out the relevent table name of a given constraint

Below query can be used to get the table name for a given constraint.This will help you to find out dependencies very easily.

SELECT TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = '[owner/schema name]'
AND CONSTRAINT_NAME = '[constraint_name]'

To view locked objects in a Oracle DB

Below query can be used to view locked objects in a Oracle database..

SELECT C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE,B.SID,B.SERIAL#,B.STATUS,B.OSUSER,B.MACHINE
FROM V$LOCKED_OBJECT A ,
V$SESSION B,
DBA_OBJECTS C
WHERE B.SID = A.SESSION_ID AND
A.OBJECT_ID = C.OBJECT_ID

If you want to kill a session after finding out the locked session below is the statement to execute..

ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]' ;

How to view current active sessions in a Oracle DB

Below query can be used to view currently connected sessions in a Oracle DB.It is useful in situations where your DB is slow to response and you need to find out what sessions are exactly causing it.

SELECT USERNAME,SID,SERIAL#,TERMINAL,LOGON_TIME,STATUS,
(SELECT DISTINCT SQL_TEXT
FROM SYS.V_$SQL_SHARED_MEMORY
WHERE HASH_VALUE=SQL_HASH_VALUE)
FROM V$SESSION
WHERE STATUS='ACTIVE' AND
USERNAME IS NOT NULL

Named User Plus (Number of Users) in a Oracle License

Named User Plus is defined as an individual authorized by you to use the programs which are installed on a single server or multiple servers, regardless of whether the individual is actively using the programs at any given time. A non human operated device will be counted as a named user plus in addition to all individuals authorized to use the programs, if such devices can access the programs.

If multiplexing hardware or software (e.g., a TP monitor or a web server product) is used, this number must be measured at the multiplexing front end. Automated batching of data from computer to computer is permitted. You are responsible for ensuring that the named user plus per processor minimums are maintained for the programs contained in the user minimum table in the licensing rules section; the minimums table provides for the minimum number of named users plus required and all actual users must be licensed.

Actually the above definition is somewhat very confusing and DBAs have confusion of what is exactly mean by it.

 

Simply saying "Number of Users" mentioned in a Oracle License is actually the number of users who interact with the database.It may be end users who are using a system which has Oracle DB as the back end.It doesn't matter whether that users is current connected from a session.It counts all the users who have access to the DB through the system.