ORA-00942: table or view does not exist
Table or view does not exists for V$Session when used PLSQL .
grant privilege to user
GRANT SELECT ON V_$SESSION TO User_name;
TO FIND PRIMARY KEY CONSTRAINT AND ITS FOREIGN KEY RELATION WITH COLUMN NAMES
===========================================================================
SELECT a.table_name,
a.constraint_name pk,
c.column_name pk_column,
b.table_name,
b.constraint_name fk,
d.column_name fk_column
FROM user_constraints a
JOIN user_constraints b
ON a.constraint_name = b.R_CONSTRAINT_NAME
JOIN user_cons_columns c
ON a.constraint_name = c.constraint_name
JOIN user_cons_columns d
ON b.constraint_name = d.constraint_name
order by a.table_name;
TABLESPACE SPACE UTILIZATION STATUS REPORT
=====================================
SET pagesize 300
SET lines 132 pages 66 feedback off
COLUMN tablespace_name format a15 heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible format a6 heading 'Can|Auto|Extend'
COLUMN files_in_tablespace format 999 heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space format 99,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct format 999.99 heading 'Total|Current|Used|PCT'
COLUMN total_free_pct format 999.99 heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace format 99,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct format 999.99 heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct format 999.99 heading 'Total|Max|Free|PCT'
TTITLE left _date center Tablespace Space Utilization Status Report skip 2
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES) total_tbs_free_bytes,
MAX (BYTES) max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT a.tablespace_name,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
) total_used_pct,
((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
) total_free_pct,
AUTOEXTEND.total_growth_tbs max_size_of_tablespace,
( ( ( AUTOEXTEND.total_growth_tbs
- (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
)
)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_used_pct,
( ( (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_free_pct
FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+);
Relocating Datafile in a Tablespace
===========================
Procedure to relocate one or more datafile in a tablespace.
Connect to the database with admin privilege. And have a current backup of database.
1. Check datafile location
SQL>select tablespace_name,file_name from dba_data_files where tablespace_name ='USERS';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS /u01/app/oracle/oradata/dockdb/USERS01.dbf
2.Change status of tablespace to offline.
SQL>alter tablespace USERS offline normal;
3.Move file using OS command.
[oracle@host161 ~]$ mv /u01/app/oracle/oradata/dockdb/USERS01.dbf /u02/dockdb/datafiles/
[oracle@host161 ~]$ ll /u02/dockdb/datafiles/USERS01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 18 05:05 /u02/dockdb/datafiles/USERS01.dbf
4.Change location of datafile in a Tablespace.
SQL>alter tablespace USERS rename datafile '/u01/app/oracle/oradata/dockdb/USERS01.dbf' to '/u02/dockdb/datafiles/USERS01.dbf';
5.Chenge the status of tablespace back to Online.
SQL>alter tablespace USERS online;
6.Review the changed location.
SQL>select tablespace_name,file_name from dba_data_files where tablespace_name ='USERS';
To Kill session
===========
select 'alter system kill session '''||sid||','||serial#||''' immediate;'||' -- User is '||username||' from '||osuser
from v$session where username is not null and username<>'SYS' order by osuser;
To find the Objects and its count grouped by its status.
========================================
SELECT status,object_type ,count(object_type) count
FROM user_objects
GROUP BY object_type,status
UNION ALL
SELECT status,decode(constraint_type, 'R', 'FOREIGN KEY','P', 'PRIMARY KEY','U' , 'UNIQUE KEY','C','CHECK AND NOT NULL') object_type,count(constraint_type)
FROM user_constraints
GROUP BY constraint_type,status
UNION ALL
SELECT status,'DISABLED TRIGGERS' as object_type,count(status)
FROM user_triggers
WHERE status='DISABLED'
GROUP BY status
order by status,object_type;
For importing [impdp] without Constraint error
==================================
Use following options in IMPDP for avoiding constraint error when appending table data.:
Table_exists_action=append DATA_OPTIONS=skip_constraint_errors
For Shrinking free space from Datafiles
=============================
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
For getting Login time of all users in the database.
=====================================
set lines 300
col os_username for a20
col terminal for a20
col userhost for a30
select a.username,os_username,a.timestamp,a.logoff_time,a.returncode,terminal,userhost
from dba_audit_session a
where (a.username,a.timestamp) in
(select b.username,max(b.timestamp)
from dba_audit_session b
group by b.username)
and a.timestamp<(sysdate-0) ;
Compile , Unlock and Gather Stats of a Schema.
====================================
exec dbms_utility.compile_schema('schema_name');
exec DBMS_STATS.UNLOCK_SCHEMA_STATS ('schema_name');
exec DBMS_STATS.GATHER_SCHEMA_STATS ('schema_name');
To check current SCN.
=================
SELECT dbms_flashback.get_system_change_number as current_scn FROM DUAL;
SELECT current_scn FROM V$DATABASE;
When I execute this query , I got a value like this :
SQL> SELECT current_scn
FROM V$DATABASE;
CURRENT_SCN
-----------
1.2807E+13
But Now I got exact value of SCN
SQL> SELECT to_char(current_scn,99999999999999) from V$DATABASE;
TO_CHAR(CURRENT
---------------
12807210994059
To check Opened Cursor :
SELECT COUNT (a.VALUE) Opened_Cursors, s.username
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current'
GROUP BY s.username;
SELECT COUNT (a.VALUE) Opened_Cursors
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current'
Table or view does not exists for V$Session when used PLSQL .
grant privilege to user
GRANT SELECT ON V_$SESSION TO User_name;
TO FIND PRIMARY KEY CONSTRAINT AND ITS FOREIGN KEY RELATION WITH COLUMN NAMES
===========================================================================
SELECT a.table_name,
a.constraint_name pk,
c.column_name pk_column,
b.table_name,
b.constraint_name fk,
d.column_name fk_column
FROM user_constraints a
JOIN user_constraints b
ON a.constraint_name = b.R_CONSTRAINT_NAME
JOIN user_cons_columns c
ON a.constraint_name = c.constraint_name
JOIN user_cons_columns d
ON b.constraint_name = d.constraint_name
order by a.table_name;
TABLESPACE SPACE UTILIZATION STATUS REPORT
=====================================
SET pagesize 300
SET lines 132 pages 66 feedback off
COLUMN tablespace_name format a15 heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible format a6 heading 'Can|Auto|Extend'
COLUMN files_in_tablespace format 999 heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space format 99,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct format 999.99 heading 'Total|Current|Used|PCT'
COLUMN total_free_pct format 999.99 heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace format 99,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct format 999.99 heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct format 999.99 heading 'Total|Max|Free|PCT'
TTITLE left _date center Tablespace Space Utilization Status Report skip 2
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES) total_tbs_free_bytes,
MAX (BYTES) max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT a.tablespace_name,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
) total_used_pct,
((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
) total_free_pct,
AUTOEXTEND.total_growth_tbs max_size_of_tablespace,
( ( ( AUTOEXTEND.total_growth_tbs
- (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
)
)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_used_pct,
( ( (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
/ AUTOEXTEND.total_growth_tbs
)
* 100
) total_auto_free_pct
FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+);
Relocating Datafile in a Tablespace
===========================
Procedure to relocate one or more datafile in a tablespace.
Connect to the database with admin privilege. And have a current backup of database.
1. Check datafile location
SQL>select tablespace_name,file_name from dba_data_files where tablespace_name ='USERS';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS /u01/app/oracle/oradata/dockdb/USERS01.dbf
2.Change status of tablespace to offline.
SQL>alter tablespace USERS offline normal;
3.Move file using OS command.
[oracle@host161 ~]$ mv /u01/app/oracle/oradata/dockdb/USERS01.dbf /u02/dockdb/datafiles/
[oracle@host161 ~]$ ll /u02/dockdb/datafiles/USERS01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 18 05:05 /u02/dockdb/datafiles/USERS01.dbf
4.Change location of datafile in a Tablespace.
SQL>alter tablespace USERS rename datafile '/u01/app/oracle/oradata/dockdb/USERS01.dbf' to '/u02/dockdb/datafiles/USERS01.dbf';
5.Chenge the status of tablespace back to Online.
SQL>alter tablespace USERS online;
6.Review the changed location.
SQL>select tablespace_name,file_name from dba_data_files where tablespace_name ='USERS';
To Kill session
===========
select 'alter system kill session '''||sid||','||serial#||''' immediate;'||' -- User is '||username||' from '||osuser
from v$session where username is not null and username<>'SYS' order by osuser;
To find the Objects and its count grouped by its status.
========================================
SELECT status,object_type ,count(object_type) count
FROM user_objects
GROUP BY object_type,status
UNION ALL
SELECT status,decode(constraint_type, 'R', 'FOREIGN KEY','P', 'PRIMARY KEY','U' , 'UNIQUE KEY','C','CHECK AND NOT NULL') object_type,count(constraint_type)
FROM user_constraints
GROUP BY constraint_type,status
UNION ALL
SELECT status,'DISABLED TRIGGERS' as object_type,count(status)
FROM user_triggers
WHERE status='DISABLED'
GROUP BY status
order by status,object_type;
For importing [impdp] without Constraint error
==================================
Use following options in IMPDP for avoiding constraint error when appending table data.:
Table_exists_action=append DATA_OPTIONS=skip_constraint_errors
For Shrinking free space from Datafiles
=============================
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
=====================================
set lines 300
col os_username for a20
col terminal for a20
col userhost for a30
select a.username,os_username,a.timestamp,a.logoff_time,a.returncode,terminal,userhost
from dba_audit_session a
where (a.username,a.timestamp) in
(select b.username,max(b.timestamp)
from dba_audit_session b
group by b.username)
and a.timestamp<(sysdate-0) ;
Compile , Unlock and Gather Stats of a Schema.
====================================
exec dbms_utility.compile_schema('schema_name');
exec DBMS_STATS.UNLOCK_SCHEMA_STATS ('schema_name');
exec DBMS_STATS.GATHER_SCHEMA_STATS ('schema_name');
To check current SCN.
=================
SELECT dbms_flashback.get_system_change_number as current_scn FROM DUAL;
SELECT current_scn FROM V$DATABASE;
When I execute this query , I got a value like this :
SQL> SELECT current_scn
FROM V$DATABASE;
CURRENT_SCN
-----------
1.2807E+13
But Now I got exact value of SCN
SQL> SELECT to_char(current_scn,99999999999999) from V$DATABASE;
TO_CHAR(CURRENT
---------------
12807210994059
To check Opened Cursor :
SELECT COUNT (a.VALUE) Opened_Cursors, s.username
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current'
GROUP BY s.username;
SELECT COUNT (a.VALUE) Opened_Cursors
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current'
ORDER BY 1;
