instead of utlu111i.sql use utlu112i.sql,
utlu111s.sql use utlu112s.sql
create or replace package emps_pkg as
type detail is ref cursor;
function getempdetails (options in INTEGER, departname in VARCHAR2, jobtitle in VARCHAR2) return detail;
end;
/
create or replace package body emps_pkg as
function getempdetails(options in INTEGER,departname in VARCHAR2, jobtitle in VARCHAR2) return detail is
employees detail;
main_query VARCHAR2(2000) := 'select /*+ result_cache */ e.* from employees e,departments d,jobs j
where e.department_id = d.department_id and j.job_id = e.job_id ';
begin
if options = 1 then
main_query := main_query ||'and d.department_name=:1';
open employees for main_query using departname;
return employees;
else
main_query := main_query ||'and j.job_title=:1';
open employees for main_query using jobtitle;
return employees;
end IF ;
end;
end;
/
select emps_pkg.getempdetails(2,'Purchasing','Purchasing Manager') from dual;
select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
Plan hash value: 980169617
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | RESULT CACHE | a68hth9dfygb5ddg0hr8hnty9b | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Plan hash value: 980169617
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';
create table insertdml as select * from all_objects where 1 = 2;
alter table insertdml add constraint insert_pk primary key (object_id);
full_blocks 0
full_bytes 0
total_blocks 8
total_bytes 65536
unused_blocks 5
unused_bytes 40960
last_used_extent_file_id 6
last_used_extent_block_id 34416
declare
type insertrow_type is table of all_objects%rowtype index by pls_integer;
insertrow insertrow_type;
begin
select * bulk collect into insertrow from all_objects;
forall row in insertrow.first .. insertrow.last
insert into insertdml values insertrow(row);
end;
/
full_blocks 1002Note the last used extent block id and delete the rows from the table and check the space usage
full_bytes 8208384
total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096
No more full blocks. Again insert the rows and check the space usage
delete from insertdml;
commit;
full_blocks 0
full_bytes 0
total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096
full_blocks 999Total number of blocks hasn't changed and last used extent block is still the same which means no new extents were added to the segment and old extents are reused.
full_bytes 8183808
total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096
drop table insertdml purge;
create table insertdml compress for all operations as select * from all_objects where 1 = 2;
alter table insertdml add constraint insert_pk primary key (object_id);
full_blocks 0
full_bytes 0
total_blocks 8
total_bytes 65536
unused_blocks 5
unused_bytes 40960
last_used_extent_file_id 6
last_used_extent_block_id 34416
declareand check the space usage
type insertrow_type is table of all_objects%rowtype index by pls_integer;
insertrow insertrow_type;
begin
select * bulk collect into insertrow from all_objects;
forall row in insertrow.first .. insertrow.last
insert into insertdml values insertrow(row);
end;
/
full_blocks 401
full_bytes 3284992
total_blocks 640
total_bytes 5242880
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35584
delete from insertdml;
commit;
full_blocks 0
full_bytes 0
total_blocks 640
total_bytes 5242880
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35584
full_blocks 645
full_bytes 5283840
total_blocks 768
total_bytes 6291456
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35712
select segment_name,bytes from user_segments where segment_name='INSERTDML';
--after initial insert
full_blocks 326
full_bytes 2670592
total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296
--delete the rows
full_blocks 0
full_bytes 0
total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296
--insert again
full_blocks 327
full_bytes 2678784
total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296
select segment_name,sum(bytes)
from user_extents where segment_type='TABLE'
group by segment_name order by 2 desc;
SEGMENT_NAME SUM(BYTES)
------------------------------ ----------
AIRLINE 196608
alter table airline move compress for all operations;
select segment_name,sum(bytes)
from user_extents where segment_type='TABLE'
group by segment_name order by 2 desc;
SEGMENT_NAME SUM(BYTES)
------------------------------ ----------
AIRLINE 196608
select table_name,compression,compress_for from user_tables;
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
AIRLINE ENABLED FOR ALL OPERATIONS
... STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP)
full_blocks 3
full_bytes 24576
total_blocks 24
total_bytes 196608
unused_blocks 18
unused_bytes 147456
last_used_extent_file_id 6
last_used_extent_block_id 1544
full_blocks 2
full_bytes 16384
total_blocks 24
total_bytes 196608
unused_blocks 19
unused_bytes 155648
last_used_extent_file_id 6
last_used_extent_block_id 128
DECLARE
unformatted_blocks NUMBER;
unformatted_bytes NUMBER;
fs1_blocks NUMBER;
fs1_bytes NUMBER;
fs2_blocks NUMBER;
fs2_bytes NUMBER;
fs3_blocks NUMBER;
fs3_bytes NUMBER;
fs4_blocks NUMBER;
fs4_bytes NUMBER;
full_blocks NUMBER;
full_bytes NUMBER;
total_blocks NUMBER;
total_bytes NUMBER;
unused_blocks NUMBER;
unused_bytes NUMBER;
last_used_extent_file_id NUMBER;
last_used_extent_block_id NUMBER;
last_used_block NUMBER;
-- cursor t is select table_name from user_tables order by 1;
-- tname user_tables.table_name%type;
BEGIN
--open t;
--loop
--fetch t into tname;
--exit when t%notfound;
DBMS_SPACE.SPACE_USAGE('TX',
'AIRLINE',
'TABLE',
unformatted_blocks,
unformatted_bytes,
fs1_blocks,
fs1_bytes,
fs2_blocks,
fs2_bytes,
fs3_blocks,
fs3_bytes,
fs4_blocks,
fs4_bytes,
full_blocks,
full_bytes,
NULL);
-- dbms_output.put_line('table name '|| tname);
-- dbms_output.put_line('fs1_blocks ' || fs1_blocks );
-- dbms_output.put_line('fs1_bytes ' || fs1_bytes );
-- dbms_output.put_line('fs2_blocks '||fs2_blocks );
-- dbms_output.put_line('fs2_bytes '||fs2_bytes );
-- dbms_output.put_line('fs3_blocks '||fs3_blocks );
-- dbms_output.put_line('fs3_bytes '|| fs3_bytes );
-- dbms_output.put_line('fs4_blocks '|| fs4_blocks );
-- dbms_output.put_line('fs4_bytes '|| fs4_bytes );
dbms_output.put_line('full_blocks '|| full_blocks);
dbms_output.put_line('full_bytes '|| full_bytes );
dbms_output.put_line('');
--end loop;
--close t;
dbms_space.unused_space('TX',
'AIRLINE',
'TABLE',
total_blocks ,
total_bytes ,
unused_blocks ,
unused_bytes ,
last_used_extent_file_id ,
last_used_extent_block_id ,
last_used_block ,
NULL);
dbms_output.put_line('total_blocks '|| total_blocks);
dbms_output.put_line('total_bytes '|| total_bytes );
dbms_output.put_line('unused_blocks '|| unused_blocks);
dbms_output.put_line('unused_bytes '|| unused_bytes );
dbms_output.put_line('last_used_extent_file_id '|| last_used_extent_file_id);
dbms_output.put_line('last_used_extent_block_id '|| last_used_extent_block_id );
dbms_output.put_line('');
end;
/
After running utltzuv11.sql, you need do a
select * from sys.sys_tzuv2_temptab;
If a "select * from sys.sys_tzuv2_temptab;" gives no rows then there is no action to take you can simply apply the RDBMS DSTv11 patch without action on stored TSTZ data.
If a "select * from sys.sys_tzuv2_temptab;" give(s) affected TSTZ data then you need to back up the TSTZ data before applying the RDBMS DSTv11 patch.
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
@javax.xml.ws.soap.MTOM
@WebService(serviceName =
MIME type Java type
image/gif java.awt.Image
image/jpeg java.awt.Image
text/plain java.lang.String
text/xml javax.xml.transform.Source
application/xml javax.xml.transform.Source
*/* javax.activation.DataHandler
<xs:sequence>
<xs:element name="return" type="xs:base64Binary" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
<xs:sequence>
<xs:element name="return" type="xs:base64Binary" minOccurs="0" maxOccurs="unbounded"
xmime:expectedContentTypes="image/jpeg" xmlns:xmime="http://www.w3.org/2005/05/xmlmime"/>
</xs:sequence>
@WebService(serviceName = ...., wsdlLocation="WEB-INF/wsdl/edited.wsdl")
@MTOM
@WebService
public class NewWebService {
@WebMethod
public void setImage(@XmlMimeType("image/gif")Image img){
@WebMethod
public @XmlMimeType("image/gif")Image getImage( String name){
@WebMethod
public @XmlMimeType("*/*")Image getFiles
backup as copy incremental level 0 tag 'roll_update' database;
backup incremental level 1 for recover of copy with tag 'roll_update' database;
run{
recover copy of database with tag 'roll_update';
backup incremental level 1 for recover of copy with tag 'roll_update' database;
}
run{
recover copy of database with tag 'roll_update';
backup incremental level 1 for recover of copy with tag 'roll_update' database;
}
PROJ SUBJ
----- -----
1 1
1 2
1 3
2 3
2 1
3 1
3 2
3 3
4 1
5 2
6 3
select distinct proj from x where proj not in
(select distinct proj from (select * from
(select distinct proj from x) t1,
(select distinct subj from x) t2) t3
where (proj,subj) not in (select proj,subj from x));
with t1 as (select DISTINCT proj from x),
t2 as (select distinct subj from x)
select distinct proj from x where proj not in
( select proj from t1,t2 where (proj,subj) not in (select * from x));
select * from (select distinct proj from x) projects
where not exists(
select * from (select distinct subj from x) subject
where not exists
(select * from x
where x.proj = projects.proj
and x.subj = subject.subj
));
1. Install the new Oracle Database 11g release. This step is not required for an inplace patchset upgrade. 2. Set ORACLE_HOME to your old Oracle home. This step is not required for an inplace patchset upgrade. 3. Set ORACLE_SID to the SID of the database being upgraded. 4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the Oracle home from which the database is being upgraded. 5. Go to the Oracle home of the new Oracle Database 11g release. 6. Execute one of the following: if Single DB emdwgrd -save -sid old_SID -path save_directory for RAC setenv EM_REMCP /usr/bin/scp emdwgrd -save -cluster -sid old_SID -path save_directory If the release 10g Oracle home is on a shared device, add -shared to the previous command line.
[oracle@server110 std]$ emdwgrd -save -sid std1 -path /home/oracle/emdu Enter sys password for database std1? Mon Sep 14 19:14:24 2009 - Verify EM DB Control files ... pass Mon Sep 14 19:14:24 2009 - Validating DB Connection to std1 ... pass ENV var EM_REMCP not defined, check if rcp or scp is configured. RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh shared = 0 Mon Sep 14 19:14:28 2009 - Creating directory ... created Mon Sep 14 19:14:29 2009 - Stopping DB Control ... stopped Mon Sep 14 19:14:34 2009 - Saving DB Control files ... saved Mon Sep 14 19:14:50 2009 - Recompiling invalid objects ... recompiled Mon Sep 14 19:14:57 2009 - Exporting sysman schema for std1 ... exported Mon Sep 14 19:15:56 2009 - DB Control was saved successfully. Mon Sep 14 19:15:56 2009 - Starting DB Control ... started Mon Sep 14 19:17:33 2009 - Dump directory was dropped successfully.
SQL> SPOOL upgrade_info.log SQL> @utlu112i.sql SQL> SPOOL OFF
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’user_name’);
Database is using a timezone file older than version 11.then check the timezone withselect * from v$timezone_file;
if it is not 11 then to fix the problem use the DBMS_DST PL/SQL package and follow the instructions in "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" in Oracle Database Globalization Support Guide.
if the timezone warning isDatabase is using a timezone file greater than version 11.then before upgrading the database, patch the 11gR2 $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the same version as the one used in the source release database.
alter system set compatible='11.2.0.0.0' scope=spfile ;
shutdown the database and start in upgrade mode
startup upgrade; exec DBMS_DST.BEGIN_UPGRADE(11);
After PL/SQL executes start the database in normal mode and truncate timezone trigger tables (refer 1585343.1 for automating time-zone upgrade)
truncate table sys.dst$error_table; truncate table sys.dst$trigger_table; VAR numfail number BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail, parallel => TRUE, log_errors => TRUE, log_errors_table => 'SYS.DST$ERROR_TABLE', log_triggers_table => 'SYS.DST$TRIGGER_TABLE', error_on_overlap_time => TRUE, error_on_nonexisting_time => TRUE); DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); END; /
after finishes
BEGIN DBMS_DST.END_UPGRADE(:numfail); END; /
#load the php main library
Loadfile "C:/php-5.2.10-Win32/php5ts.dll"
#load the sapi so that apache can use php
LoadModule php5_module "C:/php-5.2.10-Win32/php5apache2_2.dll"
#set the php.ini location
PHPIniDir "C:/php-5.2.10-Win32"
#Hook the php file extensions
AddHandler application/x-httpd-php .php
AddHandler application/x-httpd-php-source .phps
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL("jdbc:oracle:thin:@IP:1521:ent10");
dataSource.setUser("asanga");
dataSource.setPassword("asa");
Properties prop = new Properties();
prop.setProperty("MinLimit", "4");
prop.setProperty("MaxLimit", "10");
prop.setProperty("InitialLimit", "5");
dataSource.setConnectionCacheProperties(prop);
dataSource.setConnectionCachingEnabled(true);
create global temporary table
temp(a varchar2(10), b number)
on commit preserve rows;
create global temporary table
temp(a varchar2(10), b number)
on commit delete rows;
create or replace function proc return number as
rowscount number;
begin
for i in 1 .. 100
loop
insert into temp values ('aaa '||i, i);
end loop;
select count(*) into rowscount from temp;
return rowscount;
end;
/
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;
public class RowSetTest {
public static void main(String[] args) {
new RowSetTest().test();
}
void test() {
try {
java.util.Properties prop = new java.util.Properties();
prop.setProperty("MinLimit", "4"); // the cache size is 5 at least
prop.setProperty("MaxLimit", "10");
prop.setProperty("InitialLimit", "5"); // create
OracleDataSource dataSource;
dataSource = new OracleDataSource();
dataSource.setURL("jdbc:oracle:thin:@192.168.0.110:1521:ent10");
dataSource.setUser("asanga");
dataSource.setPassword("asa");
dataSource.setConnectionCacheProperties(prop);
dataSource.setConnectionCachingEnabled(true);
Connection conn = dataSource.getConnection();
String fares = " BEGIN ? := proc() ;END; ";
CallableStatement fares_call = conn.prepareCall(fares);
fares_call.registerOutParameter(1, OracleTypes.INTEGER);
fares_call.execute();
System.out.println("count from plsql " + fares_call.getInt(1));
PreparedStatement pr;
System.out.println("befroe close ++++++++++++++++++++++++");
pr = conn.prepareStatement("select count(*) from temp");
ResultSet rs = pr.executeQuery();
if (rs.next()) {
System.out.println("count " + rs.getString(1));
}
rs.close();
pr.close();
conn.close();
System.out.println("after close +++++++++++++++++++++");
conn = dataSource.getConnection();
pr = conn.prepareStatement("select count(*) from temp");
rs = pr.executeQuery();
if (rs.next()) {
System.out.println("count " + rs.getString(1));
}
rs.close();
pr.close();
conn.close();
System.out.println("after close +++++++++++++++++++++");
conn = dataSource.getConnection();
conn.setAutoCommit(false);
pr = conn.prepareStatement("select count(*) from temp");
rs = pr.executeQuery();
if (rs.next()) {
System.out.println("count " + rs.getString(1));
}
rs.close();
pr.close();
conn.close();
System.out.println("after close +++++++++++++++++++++");
conn = dataSource.getConnection();
conn.setAutoCommit(false);
pr = conn.prepareStatement("select count(*) from temp");
rs = pr.executeQuery();
if (rs.next()) {
System.out.println("count " + rs.getString(1));
}
rs.close();
pr.close();
conn.close();
} catch (Exception ex) {
Logger.getLogger(RowSetTest.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
select p.table_name as parent_table,
pc.column_name as parent_table_column,
C.Table_Name As Child_Table,
Cc.Column_Name As Child_Table_Column ,
C.R_Constraint_Name As Parent_Constraint_Name,
c.constraint_name as child_constraint_name
from user_constraints p, user_constraints c,
user_cons_columns pc, user_cons_columns cc
where p.constraint_name = c.r_constraint_name
and p.constraint_name = pc.constraint_name
and pc.position = cc.position
and c.constraint_name = cc.constraint_name
and c.table_name = 'table name';
CREATE MATERIALIZED VIEW LOG ON FM_ADDON_SECTOR WITH ROWID
(addon_id,departure,destination,SECTOR_NO,RETURN_COMPULSORY,STOPOVER_ONLY,
IN_BOUND_FOR_IN_DATE,CALC_METHOD)INCLUDING NEW VALUES;
select ...
,rt.rowid rtid
,oas.rowid oasid
,oadepz.rowid oadepzid
,rdepz.rowid rdepzid
,dadesz.rowid dadeszid
,rdesz.rowid rdeszid
,das.rowid dasid
,daa.rowid daaid
,oaa.rowid oaaid
,oa.rowid oaid
,da.rowid daid
....
CREATE MATERIALIZED VIEW "ROUTES"
nologging
BUILD IMMEDIATE
USING INDEX
REFRESH FAST on commit
AS SELECT ...
protege.jarand every jar in the
Protege_3.4\plugins\edu.stanford.smi.protegex.owldirectory
public class OWLDemo {
public static void main(String[] args) {
try {
String uri = "file:///C:/MSC/project/old/travel.owl";
// String uri = "http://localhost:8080/owl/travel.owl";
OWLModel owlModel = ProtegeOWL.createJenaOWLModelFromURI(uri);
OWLNamedClass classs= owlModel.getOWLNamedClass("Trade_Association");
Collection c = classs.getNamedSubclasses();
Iterator itr = c.iterator();
while(itr.hasNext()){
OWLNamedClass cc = (OWLNamedClass) itr.next();
System.out.println(cc.getNestedBrowserText());
System.out.println(cc.getName());
}
SWRLFactory factory = new SWRLFactory(owlModel);
// factory.replaceImps(owlModel); // to run the same query again uncomment
SWRLImp imp = factory.createImp("Query-2", "Use_Inconjunction_With(?x, ?y) -> sqwrl:select(?x, ?y)");
SWRLRuleEngineBridge bridge = BridgeFactory.createBridge("SWRLJessBridge", owlModel);
bridge.infer();
SQWRLResult result = bridge.getSQWRLResult("Query-2");
while (result.hasNext()) {
System.out.println("X: " + result.getObjectValue("?y").getIndividualName());
System.out.println("Y: " + result.getObjectValue("?x").getIndividualName());
result.next();
}
} catch (Exception ex) {
}
}
}
emctl start dbconsolefails and emdctl.trc has the following
Thread-4135626432 WARN http: snmehl_connect: connect failed to
(hostname:3938): Connection refused (error = 111)
export TZ=time_zone
ORACLE_HOME/bin/emctl resetTZ agent
emctl start dbconsole
will occur. This could be confirmed with
ERROR at line 1:
ORA-20233: Invalid agent name hostname:3938
ORA-06512: at "SYSMAN.MGMT_TARGET", line 3737
ORA-06512: at line 1
select target_name, target_type from mgmt_targets;
$ export EMDROOT=$ORACLE_HOME
$ export EMSTATE=$ORACLE_HOME
emctl resetTZ agent
emdctl validateTZ agent
emctl start dbconsole
grant execute on utl_smtp to public;
grant execute on utl_tcp to public;
grant execute on utl_file to public;
grant execute on dbms_sql to public;
grant execute on DBMS_OBFUSCATION_TOOLKIT to public;
$ORACLE_HOME/hostname_sid
$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid
emca -config dbcontrol db -repos create
BUG 3386542 - OLAPI trggers that are installed with seed database, (OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER), does not handle absence of Oracle OLAP.
Disable OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER to avoid error from being generated.
ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE;
ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER DISABLE;
select name,value from v$mystat,v$statname where v$mystat.statistic#=v$statname.statistic# and v$statname.statistic# in (11,12,50,51,52,53,54,62,134,179);
select name,value from v$mystat,v$statname where v$mystat.statistic#=v$statname.statistic# and v$statname.statistic# in (11,12,53,54,55,56,57,58,59,60,61,62,64,65,66,68,72,146);
select name,value from v$mystat,v$statname where v$mystat.statistic#=v$statname.statistic# and v$statname.statistic# in (13,14,63,64,65,66,67,68,69,70,71,72,75,76,77,79,83,169);
select name,value from v$mystat,v$statname where v$mystat.statistic#=v$statname.statistic# and v$statname.statistic# in (15,16,66,67,68,69,70,71,72,73,74,75,78,79,80,82,86,175);
select name,value from v$mystat,v$statname where v$mystat.statistic#=v$statname.statistic# and v$statname.statistic# in (16,17,68,69,70,71,72,73,74,75,76,78,81,82,83,85,89,178);
select name,value
from v$mystat,v$statname
where v$mystat.statistic#=v$statname.statistic#
and v$statname.name in ('CPU used when call started',
'CPU used by this session',
'db block gets',
'db block gets from cache',
'db block gets from cache (fastpath)',
'db block gets direct',
'consistent gets',
'consistent gets from cache',
'consistent gets from cache (fastpath)',
'consistent gets - examination',
'consistent gets direct',
'physical reads',
'physical reads direct',
'physical read IO requests',
'physical read bytes',
'consistent changes',
'physical writes',
'redo size');
select
dbms_metadata.get_ddl('TABLE','C_FEATURE','QA')
From dual;
object type, object name, owner schema name
select addr,gets,misses,sleeps
from v$latch_children
where name='cache buffers chains'
and misses > 100 order by 3 desc;
ADDR GETS MISSES SLEEPS
---------------- ---------- ---------- ----------
000000015BF383D8 38773656 8016 6
000000015B6E0968 11215621 4215 0
000000015B5A27B8 26410038 3834 0
000000015B58B5D0 14755159 2289 6
select file#,dbablk,state,class from x$bh where hladdr='000000015BF383D8';
FILE# DBABLK STATE CLASS
---------- ---------- ---------- ----------
1 78757 1 1
11 614483 1 4
9 249625 1 1
11 714956 1 1
3 33047 1 1
select owner,segment_name
from dba_extents
where file_id=&p1
and &p2 between
block_id and block_id + blocks -1;
Enter value for p1: 11
Enter value for p2: 614483
OWNER SEGMENT_NAME
--------- ------------------
TRNSTG STATIC_CACHE_QUEUE
column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = &address and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;
LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738
LOCK_ MODE_HELD MODE_REQUE LOCK_ID1
----- ---------- ---------- ----------
DML Row-X (SX) Share 227738
