DBMS_SCHEDULER.CREATE_JOB (job_name => 'TRACE_OFF_JOB',JOB_TYPE => 'PLSQL_BLOCK',JOB_ACTION => 'DECLARE rowcnt number; BEGIN SELECT COUNT(*) INTO ROWCNT FROM MY_LOG; IF (ROWCNT >1000000) THEN UPDATE FOO_PROPERTIES SET VALUE = ''OFF'' WHERE KEY = ''DB_TRACE_LEVEL'';END IF; COMMIT; END;',start_date => SYSTIMESTAMP,REPEAT_INTERVAL => 'freq=hourly; BYMINUTE=0,20,40; bysecond=0',end_date => NULL,ENABLED => TRUE);
-- Built XML from elementsSELECTXMLAGG(XMLTYPE('<employee>' || ename || '</employee>')ORDER BY ename)FROM emp;# Result (single row):# <employee>ADAMS</employee><employee>ALLEN</employee>...
SELECT XMLELEMENT('Emp', XMLATTRIBUTES('Sales Representative' AS "Title"), XMLAGG(XMLELEMENT('Name', full_name))) FROM ( SELECT CONCAT ("FirstName", ' ', "LastName") AS full_name FROM ... WHERE ... ORDER BY 1) AS subq; _______________________________________________________________________________ <Emp Title="Sales Representative"> <Name>Anne Dodsworth</Name> <Name>Janet Leverling</Name> <Name>Margaret Peacock</Name> <Name>Michael Suyama</Name> <Name>Nancy Davolio</Name> <Name>Robert King</Name> </Emp>
SELECTRTRIM(XMLAGG(XMLELEMENT(E,CONCAT(CONCAT(F.Calltype, T.Useridentifiernumber),Fr.Functioncode)|| ',')) .Extract ('//text()'),',') # remove the last ,FROM ...
MERGE INTO "ORACLE"."POST_DETAILS"
USING
(SELECT 1 "one" FROM dual)
ON
("ORACLE"."POST_DETAILS"."ID" = 1)
WHEN NOT matched THEN
INSERT (
"ID",
"CREATED_BY",
"CREATED_ON"
)
VALUES (
1,
'Alice',
TIMESTAMP '2017-11-06 16:12:18.407'
)Bunun şöyle yapıldığını da gördüm. Eğer değer yoksa INSERT işletilir.MERGE INTO [target_table] USING (SELECT 1 FROM DUAL)ON [target_table.id = 5]WHEN MATCHED THEN UPDATE ...WHEN NOT MATCHED THEN INSERT ...
MERGE INTO ADV.GSRSYS_PROPERTIES D USING DUAL ON (D.key ='GEORED_STATUS') WHEN NOT MATCHED THEN INSERT (D.key,D.value) VALUES ('GEORED_STATUS',null);
<<begin_end_block>>
DECLARE
...
BEGIN
...
<<loopblk>>
FOR itr8 IN 1 .. 5
LOOP
...
END loop loopblk;
END begin_end_block;
/create or replace type integer_varray as varray (4000) of int;
create table plan_capacities
(
id int generated by default as identity not null
constraint plan_capacities_pkey primary key,
line_id int references lines (id) on delete cascade,
model_id int references models (id) on delete cascade,
plan_id int references plans (id) on delete cascade,
capacity integer_varray
);ÖrnekROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned,...
SELECT ROWNUM, customers.*FROM customersWHERE customer_id > 4500;ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE------ ----------- --------- ---------- ---------------------1 5000 Smith Jane www.digminecraft.com2 6000 Ferguson Samantha www.bigactivities.com3 7000 Reynolds Allen www.checkyourmath.com4 8000 Anderson Paige5 9000 Johnson Derek www.techonthenet.comSELECT ROWNUM, customers.*FROM customersWHERE customer_id > 4500ORDER BY last_name;ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE------ ----------- --------- ---------- ---------------------4 8000 Anderson Paige2 6000 Ferguson Samantha www.bigactivities.com5 9000 Johnson Derek www.techonthenet.com3 7000 Reynolds Allen www.checkyourmath.com1 5000 Smith Jane www.digminecraft.com
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
------------------------------------------------------------------
28-MAR-00 12.38.55.538741 PM -08:00SELECT * FROM MyTable WHERE ts > SYSTIMESTAMP -(1/24) ORDER BY ts DESC;
export ORACLE_BASE=/u02/app/oracle/product/12.2.0 export ORACLE_HOME=$ORACLE_BASE/dbhome_1 export ORACLE_SID=dbz export PATH=$PATH:$ORACLE_HOME/bin
$ sqlplus / as sysdba
# set errorcodes SCRIPT_ERROR=1 NO_ERROR=19968 ALREADY_INSTALLED=20000 ALREADY_EXISTS=20001 COMPILATION_ERRORS=20100
SQLADM="$ORACLE_HOME/bin/sqlplus -S $DBUSER/$DBPASSWORD@\"${DBURL}\" as sysdba"SCRIPTS=`/bin/ls -1`SQL_HEADER="SQL.HEADER"SQL_FOOTER="SQL.FOOTER"for f in $SCRIPTSdocat $SQL_HEADER $f $SQL_FOOTER > $SCRIPT_TO_RUN$SQLADM @$SCRIPT_TO_RUN $f<<EOFEOFERRORCODE=$(( $? + $NO_ERROR ))if [ $ERRORCODE -eq $ALREADY_INSTALLED ]; thenecho "[INFO] Skipped script $f - already installed"echo "[INFO] ------------------------------"continueelif [ $ERRORCODE -eq $ALREADY_EXISTS ]; thenecho "[INFO] Skipped script $f - already exists"echo "[INFO] ------------------------------"continueelif [ $ERRORCODE -eq $COMPILATION_ERRORS ]; thenecho "[ERROR] PL/SQL compilation errors, failed to install $f"echo "[ERROR] ##############################"exit $ERRORCODEelif [ $ERRORCODE -ne $NO_ERROR ]; thenecho "[ERROR] SQL errors, failed to install $f!"echo "[ERROR] ##############################"exit $ERRORCODEelseecho "[INFO] Successfully installed $f"echo "[INFO] ------------------------------"fidone
DROP USER myuser CASCADE;
CREATE USER adv IDENTIFIED BY "2Ab}7C";
GRANT ALL PRIVILEGES TO myuser;
GRANT EXECUTE ON utl_file TO myuser;
COMMIT;
QUIT
Açıklaması şöyle. Yani burada SYS'ye ait tablolara da erişim veriliyor.GRANT SELECT ON DBA_SCHEDULER_JOBS TO MYUSER;GRANT SELECT ON v_$database TO MYUSER;
v$database is actually a public synonym for the v_$database view owned by sys (checked in Oracle 12).
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MYSCHEMA.JavaToolsASpackage util.oracle;import java.io.*;import java.net.InetAddress;import java.util.List;public class JavaTools {public static double pingOSCommand(String Command){double time = -1;try {Process process = Runtime.getRuntime().exec(Command);process.waitFor();BufferedReader stdInput = new BufferedReader( new InputStreamReader(process.getInputStream()));BufferedReader stdError = new BufferedReader( new InputStreamReader(process.getErrorStream()));while ((String s = stdInput.readLine()) != null) {if (s.contains("time=")) {if (s.contains("ms")){time = Double.parseDouble(s.substring(s.indexOf("time=")+5, s.indexOf("ms")-1));}}}while ((s = stdError.readLine()) != null) {System.out.println(s);}return time;} catch (Exception e){System.out.println(e.getMessage());return -1;}}};//******************************************************************************/GRANT EXECUTE ON JAVA SOURCE MYSCHEMA.RailwayJavaTools TO MYUSER;COMMIT;Execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute');execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '*' );execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '*' );COMMIT;
CREATE OR REPLACE PACKAGE MYSCHEMA.MyTools AS function f_pingOSCommand(command varchar2) return number; END MyTools; / CREATE OR REPLACE PACKAGE body MYSCHEMA.MyTools AS function f_pingOSCommand(command varchar2) return number as language java NAME 'util.oracle.JavaTools.pingOSCommand(java.lang.String) return double'; end MYTools; /
SELECT MYSCHEMA.MyTools.f_pingOSCommand(?) FROM DUAL
DBMS_APPLY_ADM.START_APPLY(apply_name => 'SYNC_APPLY_1'); ... DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'SYNC_APPLY_1'); DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'SYNC_APPLY_1');
1. You cannot use Oracle Streams to replicate a table which has duplicate rows. It will not work.
2. If you don’t have unique constraint on your table in some form, you will have duplicate rows.
--on source CONNECT strmadmin/strmadminpw; CREATE DATABASE LINK REP01 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'REP01'; select count(*) from dba_tables@REP01; -- on target CONNECT strmadmin/strmadminpw; CREATE DATABASE LINK DB05 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DB05'; select count(*) from dba_tables@DB05
DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table =>'STRADMIN.SQT_IMMEDIATE1_S',queue_name => 'STRADMIN.SQ_IMMEDIATE_S');
DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => ..., streams_type => 'apply', streams_name => ..., queue_name => ... );
Synchronous Mode
Synchronous Capture uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real-time on the source database. The change tables are populated when DML operations on the source table are committed. Hence it is titled “Synchronous” Change Data Capture.
New change data arrives automatically as DML operations on the source tables are committed. Publishers can define new change sets in the predefined SYNC_SOURCE change source or use the predefined change set, SYNC_SET. The SYNC_SET change set cannot be altered or dropped.
While the “invasive” nature of the approach of the synchronous mode of Change Data Capture adds overhead to the source database at capture time, this mode can reduce costs (as compared to attempting to extract change data using table differencing or change-value section) by simplifying the extraction of change data.
DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name=>'DEV110.APP_QUEUE', streams_type=>'capture', streams_name=>'CAPTURE_110DEV', queue_name=>'REP_CAPTURE_QUEUE', include_dml=>TRUE, include_ddl=>TRUE, include_tagged_lcr=> FALSE, source_database=>'DB05', inclusion_rule=>FALSE, and_condition=>NULL);
srvctl status listener
srvctl status database -db MYDB
Oracle provides four types of drivers for their database, but I'll only enumerate the two you asked about.The OCI driver is a type 2 JDBC driver and uses native code to connect to the database. Thus, it is only an option on platforms that have native Oracle drivers available and it is not a "pure" Java implementation.Oracle's JDBC Thin driver is a type 4 JDBC Driver that uses Java sockets to connect directly to Oracle. It implements Oracle's SQL*Net TCP/IP protocol directly. Because it is 100% Java, it is platform independent ...
<dependency> <groupId>com.oracle.database.jdbc</groupId> <artifactId>ojdbc11</artifactId> <version>23.2.0.0</version> </dependency>
Java 11<dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc10</artifactId><version>${oracle.version}</version></dependency>Java 8<dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc8</artifactId><version>${oracle.version}</version></dependency>Java 6<dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc6</artifactId><version>${oracle.version}</version></dependency>
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc8</artifactId> <version>19.7.0.0</version> </dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8-production</artifactId>
<version>21.7.0.0</version>
<type>pom</type>
</dependency><dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.2</version>
</dependency>Prior to 12c (i.e., 12.1.0.1.0), UCP could work with any version of Oracle JDBC driver. With the new pool, UCP 12.1.0.2, it is dependent on Oracle JDBC driver 12.1.0.2.
Örnekimport oracle.ucp.jdbc.PoolDataSource;import java.sql.Connection;import java.sql.SQLException;PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();ds.setUser("...");ds.setPassword("...");ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");ds.setURL("jdbc:oracle:thin:@exampledb?TNS_ADMIN=/msdataworkshop/creds");Connecton con = ds.getConnection();
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setURL(...);
pds.setUser(...);
pds.setPassword(...);
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
//Setting pool properties
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(20);
Connection conn = pds.getConnection();int usedConnectionCount = pds.getBorrowedConnectionsCount();
int availableConnectionCotun = pds.getAvailableConnectionsCount();