FAQ on Queryable Patch Inventory.

This features allow you access to the OPatch information from within the database (Oracle 12c ) .This Package called DBMS_QOPATCH

Some of Attribute to use with this package :

GET_OPATCH_INSTALL_INFO
SET_CURRENT_OPINST
GET_OPATCH_LIST
IS_PATCH_INSTALLED
GET_OPATCH_DATA
GET_OPATCH_BUGS
GET_OPATCH_FILES
GET_OPATCH_COUNT
GET_OPATCH_PREQS
GET_OPATCH_OLAYS
PATCH_CONFLICT_DETECTION
GET_PENDING_ACTIVITY
GET_OPATCH_XSLT
GET_OPATCH_LSINVENTORY
GET_SQLPATCH_STATUS

Since this function used to get Opatch information within database then you have to access to SQL Plus

example:

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

******************************************************************************

Another New Features 12c : generated as identity / Sequence Replacement

in old version of oracle database if you want to create automatic generated number you have to create sequence and use attribute nextval.

But with oracle database 12c this concept is changed new features add when you create table called generated as identity.

Check the Below Demonstration which explain this new features :

SQL> create table test (test_id number generated as identity , test_name varchar2(20));

SQL> desc test ;
Name Null? Type
----------------------------------------- -------- --------------
TEST_ID NOT NULL NUMBER
TEST_NAME VARCHAR2(20)
SQL> insert into test values (1,'sateesh');
insert into test values (1,'sateesh')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
The TEST_ID Column will be inserting automatically no need to use in insert command.

SQL> insert into test (TEST_NAME) values ( 'Raju' );
1 row created.

SQL> select * from test ;
TEST_ID TEST_NAME
---------- --------------------
1 Raju

Check another example :

SQL> create table test2 (TEST_ID NUMBER generated as identity (start with 1 increment by 1 cache 30 order), TEST_NAME varchar2(20));

Table created.
SQL> insert into test2 (test_name) values ('Neelesh');
SQL> insert into test2 (test_name) values ('Mohan');
SQL> insert into test2 (test_name) values ('USER');

SQL> select * from test2 ;

TEST_ID TEST_NAME
---------- --------------------
1 Neelesh
2 Mohan
3 USER

***************************************************************************

Finding Strings in Binary Files

If a user encounters a binary file and does not know what it is used for or where it came from, they may gain some insight into its origins and use by searching for character strings within the file. If the cat command is used to list a binary file, the user will get a screen full of garbage that will more often than not change the display characteristics. Instead, the strings command should be used, as demonstrated in the following example

$ strings echo|grep GLIBC
GLIBC_2.3
GLIBC_2.1.3
GLIBC_2.1
GLIBC_2.0
GLIBC_2.2

This shows how grep can be used to limit the output of a command to only lines that contain certain text.

********************************************************************************

Find archive growth info lat 10 days:

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v$log_history where to_date(first_time) > sysdate -10 GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time) ;

DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
10-AUG-13 1 1 1 0 6 6 1 2 2 2 2 2 2 2 2 1 2 2 2 1 2 1 0 2
11-AUG-13 0 1 1 1 4 5 1 2 2 2 2 2 2 2 2 2 1 2 1 2 1 1 0 2
12-AUG-13 1 1 1 0 5 4 1 3 1 2 2 1 2 3 3 1 2 1 0 1 1 0 1 0
13-AUG-13 1 0 0 1 5 6 1 1 2 1 1 1 2 1 1 1 0 1 1 1 0 1 0 1
14-AUG-13 0 0 0 1 4 4 1 1 2 1 1 1 1 1 1 1 1 0 1 1 0 1 0 1
15-AUG-13 0 0 1 0 5 4 0 1 1 1 1 1 1 1 1 1 1 1 1 2 1 0 1 1
16-AUG-13 0 0 1 1 5 5 1 2 1 2 2 1 1 2 2 1 2 1 2 1 2 1 1 1
17-AUG-13 0 1 1 1 6 5 1 2 2 2 1 2 2 2 2 1 1 2 1 2 1 0 1 1
18-AUG-13 1 1 0 1 4 5 1 2 2 2 1 2 2 2 1 1 2 1 1 2 1 1 0 2
19-AUG-13 1 0 1 1 5 4 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

*********************************************************************************

Find temp tablespace information session wise :

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid,
S.module, S.program,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, SUM (T.blocks) * TBS.block_size / 1024 / 1024/1024 gb_usedT,tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr --and --s.type<>'BACKGROUND'
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY mb_used desc;

SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED GB_USEDT TABLESPACE SORT_OPS
--------------- -------- ---------- ------------------------ -------------------- ---------------- ---------- ---------- ---------- ----------
14454,54205 DBSNMP oracle 3444 emagent_SQL_oracle_d JDBC Thin Client 1 .00097 TEMP 1

********************************************************************************

How to get Hidden parameters description:

select KSPPINM, KSPPDESC from x$ksppi order by 1,2;

KSPPINM KSPPDESC
---------------------------------- ----------------------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY Version 7 Dictionary Accessibility Support
_4030_dump_bitvec bitvec to specify dumps prior to 4030 error
_4031_dump_bitvec bitvec to specify dumps prior to 4031 error
_4031_dump_interval Dump 4031 error once for each n-second interval
_4031_max_dumps Maximum number of 4031 dumps for this process
_4031_sga_dump_interval Dump 4031 SGA heapdump error once for each n-second interval
_4031_sga_max_dumps Maximum number of SGA heapdumps
_NUMA_instance_mapping Set of nodes that this instance should run on
_NUMA_pool_size aggregate size in bytes of NUMA pool
_PX_use_large_pool Use Large Pool as source of PX buffers
__db_cache_size Actual size of DEFAULT buffer pool for standard block size buffers
__dg_broker_service_names service names for broker use
__java_pool_size Actual size in bytes of java pool
__large_pool_size Actual size in bytes of large pool
__oracle_base ORACLE_BASE
__pga_aggregate_target Current target size for the aggregate PGA memory consumed
__sga_target Actual size of SGA
__shared_io_pool_size Actual size of shared IO pool

********************************************************************************

Oracle DBA Information at one place

Search This Blog

FAQ on Queryable Patch Inventory

Finding Strings in Binary Files

Comments

Post a Comment