-----------------------------------ts_extent_map.sql--------------------------------------
-- -----------------------------------------------------------------------------------
-- File Name : http://www.oracle-base.com/dba/monitoring/ts_extent_map.sql
-- Author : DR Timothy S Hall
-- Description : Displays gaps (empty space) in a tablespace or specific datafile.
-- Requirements : Access to the DBA views.
-- Call Syntax : @ts_extent_map (tablespace-name) [all | file_id]
-- Last Modified: 25/01/2003
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF
DECLARE
l_tablespace_name VARCHAR2(30) := UPPER('&1');
l_file_id VARCHAR2(30) := UPPER('&2');
CURSOR c_extents IS
SELECT owner,
segment_name,
file_id,
block_id AS start_block,
block_id + blocks - 1 AS end_block
FROM dba_extents
WHERE tablespace_name = l_tablespace_name
AND file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
ORDER BY file_id, block_id;
l_block_size NUMBER := 0;
l_last_file_id NUMBER := 0;
l_last_block_id NUMBER := 0;
l_gaps_only BOOLEAN := TRUE;
l_total_blocks NUMBER := 0;
BEGIN
SELECT block_size
INTO l_block_size
FROM dba_tablespaces
WHERE tablespace_name = l_tablespace_name;
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);
FOR cur_rec IN c_extents LOOP
IF cur_rec.file_id != l_last_file_id THEN
l_last_file_id := cur_rec.file_id;
l_last_block_id := cur_rec.start_block - 1;
END IF;
IF cur_rec.start_block > l_last_block_id + 1 THEN
DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||
' FileID=' || cur_rec.file_id ||
' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||
' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
);
l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
END IF;
l_last_block_id := cur_rec.end_block;
IF NOT l_gaps_only THEN
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
END;
/
---------------------------- maxshrink.sql ----------------------------------
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(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
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(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
----------------------------------AUDITING SCHEMA VISE------------------------------------
declare
v_object_schema varchar2(50) :='VIENNAPRODUCTIVE';
v_table_name varchar2(255);
v_policy_name varchar2(255);
begin
for t in (select table_name, 'POL_'||table_name as policy_name from dba_tables where owner = v_object_schema)
loop
v_table_name := t.table_name;
v_policy_name := t.policy_name;
dbms_fga.add_policy (
object_schema => v_object_schema,
object_name => v_table_name,
policy_name => v_policy_name,
statement_types => 'SELECT, INSERT, DELETE, UPDATE'
);
end loop;
end;
SQL> select object_schema, object_name, policy_name, policy_text from dba_audit_policies;
SQL> select count(1) from fga_log$;
SQL> select db_user, to_char(timestamp, 'DD-MM-rr, hh:MI:SS'), sql_text from dba_fga_audit_trail;
declare
v_object_schema varchar2(5) :='MAHIR';
v_table_name varchar2(255);
v_policy_name varchar2(255);
begin
for t in (select table_name, 'POL_'||table_name as policy_name from dba_tables where owner = v_object_schema)
loop
v_table_name := t.table_name;
v_policy_name := t.policy_name;
dbms_fga.drop_policy (
object_schema => v_object_schema,
object_name => v_table_name,
policy_name => v_policy_name,
);
end loop;
end;
---------------------------Explain plan---------------------------
explain plan for
select * from emp;
select * from table(dbms_xplan.display)
-------------------------rebuilding indexes after compression---------------
BEGIN
FOR idx IN (SELECT owner||'.'||index_name indexname FROM all_indexes WHERE owner = 'VIENNACRM')
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '||idx.indexname||' REBUILD';
END LOOP;
END ;
--------------------------spool file to o/p------------------------------
spool 'C:\spool.txt'
select * from v$instance;
select * from v$database;
show parameter spfile
--alter system checkpoint;
--alter system switch logfile;
select * from v$version;
select * from sys.registry$history order by action_time;
select comp_id, comp_name , version, status FROM dba_registry;
select distinct status from dba_data_files;
select distinct status from v$datafile;
select distinct status from dba_temp_files;
select distinct status from v$tempfile;
select * from v$log;
select * from v$logfile;
select * from v$controlfile;
select distinct status from v$backup;
select * from v$recover_file;
select * from v$datafile where name like '%MISSING%';
select * from dba_data_files where file_name like '%MISSING%';
select machine, username, osuser, count(*) from v$session group by machine, username, osuser order by machine, username, osuser;
select owner, object_type, count(*) from dba_objects where status = 'INVALID' group by owner, object_type;
select owner, object_type, object_name from dba_objects where status = 'INVALID' order by owner, object_type, object_name;
select distinct status from dba_indexes;
select owner, table_name, index_name from dba_indexes where status='UNUSABLE';
--alter system checkpoint;
--alter system switch logfile;
select * from V$DATABASE_BLOCK_CORRUPTION;
spool off;
---------------------------create pfile---------------------------------
create pfile= 'C:\pfile.ora' from spfile;
---------------------------creating DB link---------------------------------
CREATE DATABASE LINK "lga1"
CONNECT TO system
IDENTIFIED BY system
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521)))(CONNECT_DATA = (SID = orcl)))'
----------------------converting timestamp to scn & viceversa--------------------
select scn_to_timestamp(884871) as timestamp from dual;
select timestamp_to_scn(to_timestamp('20/1/2012 14:24:54','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
--------------------------------to view sql-----------------------------------
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
select sesion.sid,
sql_text
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sqltext.piece
------------------------------changing timezone---------------------------
SELECT TO_char(sysdate,'DD-MM-RRRR HH24:MI:SS'),
FROM_TZ(CAST(to_date(TO_char(sysdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS')
AS TIMESTAMP), '+5:30') AT TIME ZONE '+4:00'
FROM dual
--------------------------------any dictionary table--------------------
select * from dictionary where table_name LIKE '%CONTROL%' ; - with dba user
------------------------------session & processes-------------------------
SELECT MACHINE, COUNT(*) FROM V$SESSION where status = 'ACTIVE' GROUP BY MACHINE;
select count(*) from v$processes;
-----------------------increase session & parameters-------------------
show parameter processes
show parameter sessions
show parameter transactions
A basic formula for determining these parameter values is as follows:
if processes=x
sessions=x*1.1+5
transactions=sessions*1.1
alter system set processes=500, 1000 scope=spfile;
alter system set sessions=555, 1105 scope=spfile;
alter system set transactions=610, 1216 scope=spfile;
--------------------to kill session userwise------------------------------
select inst_id,sid,serial# from gv$session where username='SCOTT';
alter system kill session '130,620,@1';
---------------------------valid the invalid objects-----------------------
EXEC DBMS_STATS.gather_database_stats;
also run the utlrp.sql script and then take the fresh export
----------------------------DBF size---------------------------------------
select file_id,sum(bytes)/1024/1024 from dba_data_files group by file_id;
------------------------------TEMP FILE SIZE----------------------------
select sum(bytes)/1024/1024 "Meg" from dba_temp_files;
------------------------------LOG SIZE---------------------------------
select sum(bytes)/1024/1024 "Meg" from v$log;
--------------------------TOTAL DB SIZE-----------------------------------
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
------------------------user & their tbsp------------------------------
select owner, tablespace_name, count(*), sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner, tablespace_name
order by 4 desc;
------------------------ users on TBSP-------------------------------
select username from dba_users where default_tablespace='USERS';
-------------------table or index belongs to which DBF-----------------
Use DBA_SEGMENTS to find the table or index
Use DBA_EXTENTS to see the extents that make up the segment
and inside see FILE_ID to look at the file
it is described in DBA_DATA_FILES
----------------- datafile relates to tablespace------------------------
select tablespace_name,file_name from dba_data_files;
---------------------------details of DB------------------------------
select * from dba_objects where object_name like '%JAVA%';
select * from DBA_REGISTRY;
select * from V$OPTIONFsize
---------------------details of tbsp autoextend or not--------------------
set pagesize 100
column file_name format a32
column tablespace_name format a15
column status format a3 trunc
column t format 999,999.000 heading "Total MB"
column a format a4 heading "Aext"
column p format 990.00 heading "% Free"
SELECT df.file_name,
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
(fs.s/df.bytes*100) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
ORDER BY df.tablespace_name, df.file_id;
column file_name clear
column tablespace_name clear
column status clear
column t clear
column a clear
column p clear
ttitle off
------------- if drive is full & extend TBSP----------------
create folder in d drive say oracle or anything and fire the below command
alter tablespace users add datafile 'D:\oracle\user02.dbf' size 3G;
i have assume that the folder name is oracle
alter tablespace ts_sth
add datafile 'c:\xx\sth_04.dbf' size 4M autoextend off;
------------------ Space of TBSP-----------------------------
set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;
-----------------------Size of schema-------------------------------
SELECT owner,SUM (BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB
FROM dba_segments
GROUP BY owner;
------------------------autoextend on-------------------------------
alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' autoextend on;
----------------------Add datafile to TBSP--------------------------
alter tablespace system add datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' size 500M autoextend on;
-----------------------------------indexes---------------------------
SELECT I.INDEX_NAME,
I.INDEX_TYPE,
I.TABLE_NAME,
IC.COLUMN_NAME,
DBMS_METADATA.GET_DDL('INDEX',I.INDEX_NAME)
FROM DBA_INDEXES I,
DBA_IND_COLUMNS IC
WHERE I.OWNER ='HIMLIVE'
AND I.INDEX_NAME = IC.INDEX_NAME
AND I.TABLE_NAME = IC.TABLE_NAME
AND I.TABLE_NAME='M_PRODUCT';
----------------------constraint------------------------------------
SELECT p.table_name AS PARENT,
col.column_name,
p.constraint_name,
c.table_name AS CHILD,
cols.column_name,
c.constraint_name,
c.status
FROM user_constraints c JOIN user_constraints p
ON c.r_constraint_name = p.constraint_name
JOIN user_cons_columns col
ON col.owner = p.owner
AND p.constraint_name = col.constraint_name
AND col.table_name = p.table_name
JOIN user_cons_columns cols
ON cols.owner = c.owner
AND c.constraint_name = cols.constraint_name
AND cols.table_name = c.table_name
AND c.r_owner = p.owner
where c.status='DISABLED' ;
ORDER BY c.constraint_name;
---------------delete duplicate rows-----------------
DELETE FROM emp a
WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM emp b
WHERE a.empno = b.empno
GROUP BY empno);
-------------------------All views-------------------------------
SELECT TYPE_TEXT FROM ALL_VIEWS WHERE VIEW_NAME = 'SMPRODUCT_V';
------------------user_constraints---------------------------------------
select
parent.owner || '.' ||
parent.table_name "Parent table",
'is parent of' " ",
child.owner || '.' ||
child.table_name "Child table"
from
user_constraints child join user_constraints parent on
child.r_constraint_name = parent.constraint_name and
child.r_owner = parent.owner
where
child.table_name like 'AD_PINSTANCE_LOG%' escape '\' --';
-----------------------------valid/invalid objects-------------------------
select owner, object_name, object_type, status from dba_objects where status!='VALID' order by 1;
-------------------To add timestamp in export ---------------------------------
exp system/system file=d:\expdp.%date:~4,2%%date:~7,2%%date:~10,4%%time:~0,2%%time:~3,2%%time:~6,2%.dmp
https://forums.oracle.com/forums/thread.jspa?threadID=959681
-----------------------------expdp all users-----------------------------------------
SELECT 'expdp SYSTEM/system DUMPFILE=' || owner || '_%U.dmp LOGFILE=' || owner ||'.log schemas= ' || owner SQL
FROM (
SELECT owner, ROUND(SUM(size_mb)) MBytes FROM
(
SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
--AND TABLESPACE_NAME LIKE 'COSTE%'
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%'
--AND partition_name LIKE 'USAGE_FCT_NEW%'
AND OWNER <> 'SYSTEM'
--AND ROUND(bytes/(1024*1024),2) > 1000)
)
GROUP BY owner
ORDER BY MBytes DESC);
-------------------export query from 11g to 10g-------------------------
expdp system/system_password@orcl
schemas=hr
version= 10.2
dumpfile=hr_schema.dmp
logfile=hr_schemas_exp.log
---------------import query at 10g from 11g--------------------------
impdp system/system@xe
remap_schema=hr:scott
dumpfile=hr_schema.dmp
logfile=hr_schemas_imp.log --------simple query
impdp system/system_password@orcl
remap_schema=hr:scott
dumpfile=hr_schema.dmp
sqlfile=hr_schemas.sql --------sqlfile
logfile=hr_schemas_imp.log
you can use the parameter sqlfile=schema_sql.txt
this parameter is used during import statements in this case you have the sqlfile and in this case there is no actual import
you can check the sqlfile and come to know about the tablespace and users all
impdp system/system_password@orcl
remap_schema=hr:scott
REMAP_TABLESPACE=EXAMPLE:USERS ------tablespace
dumpfile=hr_schema.dmp
sqlfile=hr_schemas.sql
logfile=hr_schemas_imp.log
-------------------------check for the objects------------------------
select OBJECT_TYPE,count(*)from dba_objects where owner='LGA1' group by OBJECT_TYPE;
------------------------creating/changing path of DATA_PUMP_DIR---------------
drop directory DUMP_DIR ;
--Manually create folder dump
create or replace directory DATA_PUMP_DIR as '';
GRANT read, write ON DIRECTORY DUMP_DIR TO system;
------------------------to check path of data_pump_dir---------------
SELECT directory_path
FROM dba_directories
WHERE directory_name = 'DATA_PUMP_DIR';
emca -config dbcontrol db -repos recreate
Your suggestions and queries are always warm welcomed.
CREATE TABLE STATS_USER
