Common Oracle system queries

Common Oracle system queries

1. query all objects in the system
 
Select owner, OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS
 
FROM DBA_OBJECTS where owner = UPPER ('Scott ')
 
2. view all tables in the system
 
Select owner, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES
 
3. View tables of all users
 
Select owner, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES
 
4. view the current user table
 
SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
 
5. view User table Indexes
 
Select t. *, I. INDEX_TYPE FROM USER_IND_COLUMNS T, USER_INDEXES I WHERE
 
T. INDEX_NAME = I. INDEX_NAME and t. TABLE_NAME = I. TABLE_NAME
 
And t. TABLE_NAME = 'table to be query'
 
6. view the primary key
 
Select cu. * FROM USER_CONS_COLUMNSCU, USER_CONSTRAINTS AU
 
Where cu. CONSTRAINT_NAME = AU. CONSTRAINT_NAME
 
And au. CONSTRAINT_TYPE = UPPER ('P') and au. TABLE_NAME = 'table to be query'
 
7. View uniqueness constraints
 
SELECT COLUMN_NAME FROM USER_CONS_COLUMNSCU, USER_CONSTRAINTS AU
 
Where cu. CONSTRAINT_NAME = AU. CONSTRAINT_NAME and au. CONSTRAINT_TYPE = UPPER ('U ')
 
And au. TABLE_NAME = 'table to be query'
 
8. View Foreign keys
 
SELECT * FROM USER_CONSTRAINTS c where c. CONSTRAINT_TYPE = 'r' and c. TABLE_NAME = 'table to be query'
 
SELECT * FROM USER_CONS_COLUMNSCL where cl. CONSTRAINT_NAME = 'foreign key name'
 
SELECT * FROM USER_CONS_COLUMNSCL where cl. CONSTRAINT_NAME = 'foreign key reference table keyname'
 
9. view the column attributes of a table
 
Select t. *, C. comments from USER_TAB_COLUMNS T, USER_COL_COMMENTS C
 
Where t. TABLE_NAME = C. TABLE_NAME and t. COLUMN_NAME = C. COLUMN_NAME and t. TABLE_NAME = 'table to query'
 
10. View All tablespaces.
 
SELECT TABLESPACE_NAME FROM DBA_DATA_FILES group by TABLESPACE_NAME
 
 
 
11. view the maximum number of Oracle connections
 
SQL> SHOW PARAMETER PROCESSES

NAME TYPE VALUE
 
-----------------------------------------------------------------------------
 
Aq_tm_processes integer 0
 
Db_writer_processes integer 1
 
Gcs_server_processes integer 0
 
Global_txn_processes integer 1
 
Job_queue_processes integer 1000
 
Log_archive_max_processes integer 4
 
Processes integer 150
 
12. Modify the maximum number of connections
 

SQL> ALTER SYSTEM SET PROCESSES = VALUE SCOPE = SPFILE
-Restart the database.
SQL> SHUTDOWN ABORT
SQL> START FORCE
 
13. view the current number of connections
 
SQL> SELECT * FROM V $ SESSION WHERE USERNAME IS NOT NULL;
 
14. view the number of connections of different users
 
SQL> SELECT USERNAME, COUNT (USERNAME) FROM V $ SESSION WHERE USERNAME IS NOT NULL GROUPBY USERNAME;
 
# View the number of connections of a specified user
 
15. View active connections
 
SQL> SELECT COUNT (*) FROM V $ SESSION WHERE STATUS = 'active ';
 
# View concurrent connections
 
16. view the number of connections of a specified program
 
SQL> SELECT COUNT (*) FROM V $ SESSION WHERE PROGRAM = 'jdbc THIN client ';
 
# View the number of jdbc connections to oracle
 
17. view the database installation instance (dba permission)
 
SQL> SELECT * FROM V $ INSTANCE;
 
18. view the running Instance name
 
SQL> SHOW PARAMETER INSTANCE_NAME;
 
19. view the Database Name
 
SQL> SHOW PARAMETER DB_NAME;
 
20. view the database Domain Name
 
SQL> SHOW PARAMETER DB_DOMAIN;
 
21. view the Database Service name
 
SQL> SHOW PARAMETER SERVICE_NAMES;
 
22. view the global database name
 
SQL> SHOW PARAMETER GLOBAL;
 
23. view the tablespace usage
 
 
 
Select dbf. TABLESPACE_NAME,
 
DBF. TOTALSPACE "Total (M )",
 
DBF. totalblocks as "Total number of blocks ",
 
DFS. FREESPACE "total remaining amount (M )",
 
DFS. FREEBLOCKS "remaining blocks ",
 
(DFS. FREESPACE/DBF. TOTALSPACE) * 100 AS "idle percentage"
 
FROM (select t. TABLESPACE_NAME,
 
SUM (T. BYTES)/1024/1024 TOTALSPACE,
 
SUM (T. BLOCKS) TOTALBLOCKS
 
FROM DBA_DATA_FILES T
 
Group by t. TABLESPACE_NAME) DBF,
 
(Select tt. TABLESPACE_NAME,
 
SUM (TT. BYTES)/1024/1024 FREESPACE,
 
SUM (TT. BLOCKS) FREEBLOCKS
 
FROM DBA_FREE_SPACE TT
 
Group by tt. TABLESPACE_NAME) DFS
 
Where trim (DBF. TABLESPACE_NAME) = TRIM (DFS. TABLESPACE_NAME)
 
 
 
 
 
Select t. NAME "tablespace name ",
 
FREE_SPACE,
 
(TOTAL_SPACE-FREE_SPACE) USED_SPACE,
 
TOTAL_SPACE
 
FROM (SELECT TABLESPACE_NAME, SUM (BYTES/1024/1024) FREE_SPACE
 
From sys. DBA_FREE_SPACE
 
Group by TABLESPACE_NAME) FREE,
 
(Select B. NAME, SUM (BYTES/1024/1024) TOTAL_SPACE
 
From sys. V _ $ datafile a, SYS. V _ $ TABLESPACE B
 
Where a. TS # = B. TS #
 
Group by B. NAME) T
 
Where free. TABLESPACE_NAME = T. NAME

 

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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