If you want to estimate datapump file size then run following command
estimate datapump export size
datapump export file size
$ expdp "'/ as sysdba'" estimate_only=y full=Y
. estimated "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB
. estimated "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
Total estimation using BLOCKS method: 257.5 MB
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:41:20
Monday, 9 January 2012
Thursday, 5 January 2012
Oracle Memory Allocation Per User / Process
-- Check Memory Allocation Per User/Process
Memory Allocation Per Process
Memory Allocation Per User
session wise memory
session memory
set linesize 150
SET PAGESIZE 9999
COLUMN sid_serial format a12 HEADING 'sid_serial#'
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN SERIAL# FORMAT 99999 HEADING 'SERIAL#'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY left
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a12 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
set linesize 150
SELECT
-- s.sid
s.sid||','||SERIAL# sid_serial
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,12) session_machine
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
sid_serial# Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX
------------ ------------ --------- ------------------ ------------ -------------- -------------- -------------- --------------
21,2 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
23,1 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
19,5 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
22,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
11,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
10,1 oracle oracle@apt-amd-02 apt-amd-02 7 32 0 0
15,1 oracle oracle@apt-amd-02 apt-amd-02 3 4 1 2
17,10 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 2 0 1
13,1 oracle oracle@apt-amd-02 apt-amd-02 2 4 1 3
1,9 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 24 1 3
56,622 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 2 0 0
62,47 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 14 1 6
32,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
34,3 oracle oracle@apt-amd-02 apt-amd-02 1 5 1 2
40,15 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
60,443 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
30,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 1 1
27,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
16,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
14,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 1
12,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
9,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
8,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
7,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
6,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
5,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
4,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
3,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
2,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
29 rows selected.
SET LINESIZE 300 PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a35 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT /*+ PARALLEL(8) */
s.sid sid
, s.sql_id
, PREV_SQL_ID
,SQL_EXEC_ID
, lpad(s.username,20) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,20) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value in Oracle
Oracle add Days
Oracle add Hour
Oracle add Minute
Oracle add second
-- # Add a day
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1,'dd-mm-yyyy hh24:mi:ss') One_day from dual ;
Todays-date ONE_DAY
------------------- -------------------
05-01-2012 12:55:52 06-01-2012 12:55:52
-- # Add an hour
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/24,'dd-mm-yyyy hh24:mi:ss') One_hour from dual ;
Todays-date ONE_HOUR
------------------- -------------------
05-01-2012 12:56:06 05-01-2012 13:56:06
-- # Add an Minute
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60),'dd-mm-yyyy hh24:mi:ss') One_minute from dual ;
Todays-date ONE_MINUTE
------------------- -------------------
05-01-2012 12:56:18 05-01-2012 12:57:18
-- # Add Second
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss') One_second from dual ;
Todays-date ONE_SECOND
------------------- -------------------
05-01-2012 12:56:27 05-01-2012 12:56:28
if we want to insert into the table then
to_date(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss')
or
sysdate +1/(24*60*60)
or
SQL> select to_char(sysdate,'DD-MM-YYYY HH:MI:SS'), to_char(sysdate+interval '15' minute,'DD-MM-YYYY HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD TO_CHAR(SYSDATE+INT
------------------- -------------------
05-01-2012 04:36:39 05-01-2012 04:51:39
Tuesday, 3 January 2012
Oracle minact-scn master-status in trace file
minact-scn master-status
error message in Trace file " minact-scn master-status "
In On Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
In my case file location
/****us/oracle/Diag/diag/rdbms/aptdb/aptdb/trace
-rw-r-----. 1 oracle oinstall 125969 Jan 3 09:40 aptdb_mmon_2433.trc <<<<< trace file in
-bash-4.1$ tail -100 aptdb_mmon_2433.trc
*** 2012-01-03 09:30:01.182
minact-scn master-status: grec-scn:0x0000.0029e860 gmin-scn:0x0000.0029e860 gcalc-scn:0x0000.0029e860
minact-scn master-status: grec-scn:0x0000.0029e912 gmin-scn:0x0000.0029e912 gcalc-scn:0x0000.0029e912
*** 2012-01-03 09:40:07.680
minact-scn master-status: grec-scn:0x0000.0029e9b3 gmin-scn:0x0000.0029e9b3 gcalc-scn:0x0000.0029e9b3
*** 2012-01-03 09:45:08.186
minact-scn master-status: grec-scn:0x0000.0029ea76 gmin-scn:0x0000.0029ea76 gcalc-scn:0x0000.0029ea76
metalink note ID...
Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1]
Cause
Bug 11891463
The issue is similar with BUG 11891463 - MINACT-SCN MASTER-STATUS MESSAGES WRITTEN TO MMON TRACE FILE
solution ...
-----------
SQL> alter system set "_enable_minscn_cr"=false scope=spfile; ----- restart the database ..
System altered.
or
"_smu_debug_mode=134217728"
After setting above parameter the message will not be generated but This will disable min active SCN optimization feature.
"min active scn" introduced in 11g which is supposed to enhance delayed block cleanout operations.
Note : - Disabling min active scn optimization feature will not have any impact on database.
error message in Trace file " minact-scn master-status "
In On Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
In my case file location
/****us/oracle/Diag/diag/rdbms/aptdb/aptdb/trace
-rw-r-----. 1 oracle oinstall 125969 Jan 3 09:40 aptdb_mmon_2433.trc <<<<< trace file in
-bash-4.1$ tail -100 aptdb_mmon_2433.trc
*** 2012-01-03 09:30:01.182
minact-scn master-status: grec-scn:0x0000.0029e860 gmin-scn:0x0000.0029e860 gcalc-scn:0x0000.0029e860
minact-scn master-status: grec-scn:0x0000.0029e912 gmin-scn:0x0000.0029e912 gcalc-scn:0x0000.0029e912
*** 2012-01-03 09:40:07.680
minact-scn master-status: grec-scn:0x0000.0029e9b3 gmin-scn:0x0000.0029e9b3 gcalc-scn:0x0000.0029e9b3
*** 2012-01-03 09:45:08.186
minact-scn master-status: grec-scn:0x0000.0029ea76 gmin-scn:0x0000.0029ea76 gcalc-scn:0x0000.0029ea76
metalink note ID...
Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1]
Cause
Bug 11891463
The issue is similar with BUG 11891463 - MINACT-SCN MASTER-STATUS MESSAGES WRITTEN TO MMON TRACE FILE
solution ...
-----------
SQL> alter system set "_enable_minscn_cr"=false scope=spfile; ----- restart the database ..
System altered.
or
"_smu_debug_mode=134217728"
After setting above parameter the message will not be generated but This will disable min active SCN optimization feature.
"min active scn" introduced in 11g which is supposed to enhance delayed block cleanout operations.
Note : - Disabling min active scn optimization feature will not have any impact on database.
Monday, 2 January 2012
Oracle Subtract dates times
select trunc(mydate / 3600) myhour,trunc(mod(mydate, 3600) / 60) myminute,mod(mydate, 60) mysecond
from (select round((to_date('02-01-2012 11:00:34','dd-mm-yyyy hh24:mi:ss') - to_date('02-01-2012 5:12:00','dd-mm-yyyy hh24:mi:ss')) * 86400) mydate from dual)
MYHOUR MYMINUTE MYSECOND
---------- ---------- ----------
5 48 34
Sunday, 1 January 2012
Thursday, 29 December 2011
ORA-00845: MEMORY_TARGET not supported on this system
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 968M
memory_target big integer 968M
shared_memory_address integer 0
alter system set memory_max_target=1200m scope=spfile;
SQL> alter system set memory_max_target=1200m scope=spfile;
System altered.
SQL> alter system set memory_target=1200m scope=spfile;
System altered.
SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
21G 17G 2.9G 86% /
tmpfs 1000M 420K 1000M 1% /dev/shm <<<<------------------ increase this value
/dev/sda1 485M 41M 419M 9% /boot
from root
[root@localhost ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm
vi /etc/fstab
/dev/mapper/VolGroup-lv_root / ext4 defaults 1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot ext4 defaults 1 2
/dev/mapper/VolGroup-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
shmfs /dev/shm tmpfs size=2048m 0 0 <<<<<<<<<---- add following line
then init 6 from reboot <<<<<--- if you can other wise leave
SQL> startup ;
ORACLE instance started.
Total System Global Area 1255473152 bytes
Fixed Size 1344652 bytes
Variable Size 754977652 bytes
Database Buffers 486539264 bytes
Redo Buffers 12611584 bytes
Database mounted.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1200M
memory_target big integer 1200M
shared_memory_address integer 0
Subscribe to:
Posts (Atom)
