Oracle Tips

To connect to Oracle DB as sysdba admin user
sqlplus / as sysdba

1 2	To connect to Oracle DB as sysdba admin user sqlplus / as sysdba

-- formatting sqlplus outputs
SET LINESIZE 32000;
SPOOL output.txt;
SET WRAP OFF;
SET PAGESIZE 0;
SET AUTOCOMMIT OFF;
SET NEWPAGE 0;
SET SPACE 0;
SET PAGESIZE 0;
SET LINESIZE 10000;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
WHENEVER SQLERROR EXIT 33;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

-- formatting sqlplus outputs

SET LINESIZE 32000;

SPOOL output.txt;

SET WRAP OFF;

SET PAGESIZE 0;

SET AUTOCOMMIT OFF;

SET NEWPAGE 0;

SET SPACE 0;

SET PAGESIZE 0;

SET LINESIZE 10000;

SET ECHO OFF;

SET FEEDBACK OFF;

SET HEADING OFF;

WHENEVER SQLERROR EXIT 33;

-- describe table definitions
DESC TABS;

1 2	-- describe table definitions DESC TABS;

-- For a list of available users (Schemas) on AEQ DB server.
select distinct username from dba_users order by username;

1 2	-- For a list of available users (Schemas) on AEQ DB server. select distinct username from dba_users order by username;

desc dba_directories;

1	desc dba_directories;

-- For a list of all DATAPUMP directories:
select DIRECTORY_NAME||' | ' ||DIRECTORY_PATH from dba_directories;

-- get the directory path for DATAPUMP
select DIRECTORY_NAME||'|'||DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATAPUMP';

-- To create a directory 
create directory DB_IMP_DIR as '/home/database/DB_IMPORT';

1

2

3

4

5

6

7

8

-- For a list of all DATAPUMP directories:

select DIRECTORY_NAME||' | ' ||DIRECTORY_PATH from dba_directories;

-- get the directory path for DATAPUMP

select DIRECTORY_NAME||'|'||DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATAPUMP';

-- To create a directory

create directory DB_IMP_DIR as '/home/database/DB_IMPORT';

-- To find a list of all available table spaces
clear breaks
SET linesize 180
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a75
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name,
	   dd.file_name file_name,
	   dd.bytes/1024 TABLESPACE_KB,
	   SUM(fs.bytes)/1024 KBYTES_FREE,
	   MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

-- To find a list of all available table spaces

clear breaks

SET linesize 180

SET pagesize 60

break ON tablespace_name skip 1

col tablespace_name format a15

col file_name format a75

col tablespace_kb heading 'TABLESPACE|TOTAL KB'

col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name,

dd.file_name file_name,

dd.bytes/1024 TABLESPACE_KB,

SUM(fs.bytes)/1024 KBYTES_FREE,

MAX(fs.bytes)/1024 NEXT_FREE

FROM sys.dba_free_space fs, sys.dba_data_files dd

WHERE dd.tablespace_name = fs.tablespace_name

AND dd.file_id = fs.file_id

GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024

ORDER BY dd.tablespace_name, dd.file_name;

-- Alternate #1 (Preferred Method)
clear breaks
SET linesize 180
SET pagesize 60
col tablespace_name format a15
col file_name format a75
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
 
SELECT dd.tablespace_name tablespace_name,
	   dd.file_name file_name,
	   dd.bytes/1024 TABLESPACE_KB,
	   SUM(fs.bytes)/1024 KBYTES_FREE,
	   MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

-- Alternate #1 (Preferred Method)

clear breaks

SET linesize 180

SET pagesize 60

col tablespace_name format a15

col file_name format a75

col tablespace_kb heading 'TABLESPACE|TOTAL KB'

col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name,

dd.file_name file_name,

dd.bytes/1024 TABLESPACE_KB,

SUM(fs.bytes)/1024 KBYTES_FREE,

MAX(fs.bytes)/1024 NEXT_FREE

FROM sys.dba_free_space fs, sys.dba_data_files dd

WHERE dd.tablespace_name = fs.tablespace_name

AND dd.file_id = fs.file_id

GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024

ORDER BY dd.tablespace_name, dd.file_name;

-- Alternate #2
clear breaks
SET linesize 150
SET pagesize 60
col tablespace_name format a20;
col file_name format a75;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;

1

2

3

4

5

6

7

-- Alternate #2

clear breaks

SET linesize 150

SET pagesize 60

col tablespace_name format a20;

col file_name format a75;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;

-- To get a list of Resource Names & Utilization values.
SET LINESIZE 400;
SELECT * from v$resource_limit;

-- Alternate 
 select 
  resource_name, 
  current_utilization, 
  max_utilization, 
  limit_value
from 
  v$resource_limit
where 
  resource_name in ( 'sessions', 'processes');

1

2

3

4

5

6

7

8

9

10

11

12

13

14

-- To get a list of Resource Names & Utilization values.

SET LINESIZE 400;

SELECT * from v$resource_limit;

-- Alternate

select

resource_name,

current_utilization,

max_utilization,

limit_value

from

v$resource_limit

where

resource_name in ( 'sessions', 'processes');