I would like to have a query which should fetch previous day records from column which is having timestamp data type.
select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;
But this gives output not for previous day, but all records which are 24 hrs less than current day. How to get records for previous day based on column having timestamp data type.
I installed timesten server and client on different machines in LAN, but with the same user and group - ttadmin:ttgroup When I tried to connect to server with ttisqlcs -connStr "DSN=sampleCS"The output gave me error messages as below connect "DSN=sampleCS";S1000: Failed to retrieve IP address of the system. System error: -2The command failed.Done. The related part in sys.ttconnect.ini is set as below
[192.168.0.206]Description=TimesTen ServerNetwork_Address=192.168.0.206TCP_PORT=53397
And the
sys.odbc.ini [ODBC Data Sources]sampleCS=TimesTen 11.2.2 Client Driver [sampleCS]TTC_SERVER=192.168.0.206TTC_SERVER_DSN=nredb_ds
What does it mean by "retrieve IP" ?Does it try to resolve IP by the hostname even when I already gave it the ip address?By the way I can ping 192.168.0.206 and telnet at port 53397 with no problem.
Can i know the internal process of initialization of DB into memory in timesten , when a new connection is establishing? Will timesten create tables and indexes in RAM when first connection is established if the RAM policy is default?
want to know the internal functional flow of timesten when any command is fired against it.
try to evaluate it as IMDB cache.I am facing this error repeatedly. I have increased perm size from 6.25 GB to 10 GB.After inserting about 460.000 rows I get the error again. Is it possible that 460.000 rows need 3.75 GB space?
In Oracle database these rows occupy about 200 MB space.
As per my understanding , Timesten or IMDB Cache can be connected through DSN by any external client. Want to know whether Radware can be integrated with Timesten or IMDB Cache.
View 1 Replies View RelatedI connection to TimesTen as follow:
String url = "jdbc:timesten:direct:dsn=my_ttdb";
String username = "ADMINISTRATOR";
String password = "";
Connection connection = DriverManager.getConnection(TestTT.url, TestTT.username, TestTT.password);
I wonder if this JDBC Connection is a TCP/IP connection on the backend? If this is not a TCP/IP connection, how can I make a connection with TimesTen via TCP/IP protocol?
dsn:my_ttdb configuration likes:
[my_ttdb]
Driver=/home/tttest/TimesTen/tt1122/lib/libtten.so
DataStore=/home/tttest/ttdata/database/my_ttdb
LogDir=/home/tttest/ttdata/logs
PermSize=40
TempSize=32
DatabaseCharacterSet=ZHS16GBK
OracleNetServiceName=orcl
I'm using TimesTen Release 11.2.1.9.8 (64 bit Linux/x86_64) 1. is there any limit in size for a single table. How much a table size can be increased? 2. Is there any limit in number of records in a table?
View 2 Replies View Related,Im having following replication scheme: M1,M2 and M3 are multimaster replicated datastores.These three datastores will replicate its data to a node which is going to act as a Propagator.The Propagator will then replicate data to a set of subscribers. Question:-------------
Can we configure the Propagator to be redundant.ie, can we configure an additional propagator which will act as redundant and replicate to the same set of subscribers?
We cache oracle 11g2 data in timesten 11.2.2.5.0 ( IMDB ) We can't find any trigonometric functions like cos(x), sin(x) or so in timesten. Does that means timesten doesn't support trigonometric functions?
View 2 Replies View RelatedThe document says "Propagators are also useful for distributing replication loads in configurations that involve a master database that must replicate to a large number of subscribers".
Link [URL]........
My question is how do we define this " large number ". Is 5 a large number or 10 a large number ? I've a bidirectional legacy replication scheme wherein a node replicates to 10 other nodes. Should i introduce Propagator between these nodes.
Is it possible to use TimesTen to create cache groups that have different table structures from original Oracle tables based on relational conditions?
View 3 Replies View RelatedWe have been using XLA to capture events on TimesTen for a while now without any issues. We were on TimesTen 7.0.5 and then moved to 11.2.2.3.0 and now 11.2.2.4.0. The XLA processes used Java 6 64 bit and works well with TimesTen versions 11.2.2.4.0 - 64 bit and 11.2.2.3.0 - 64 bit, without any issues.
However, we recently upgraded to Java 7 64 bit both during build time of our XLA processes as well during runtime. The problem we see now is that XLA process upon startup processes events for a while and then after that doesnt receive any events. The process doesn't throw any errors/exceptions. If we restart the XLA process, all the unprocessed events are received and then it behaves normally for a while after which it fails to receive any further events.
Is there any issue with Java 7 64 bit and the TimesTen XLA API? I read the TimesTen 11.2.2.4.0 manual and it says that Java 7 and the ttjdbc7.jar have been certified to work well. Was just wondering if there were any other issues.
In addition, to debug the problem, we ran XLA on a single node and on a two safe setup. The same observations are noted. On Java 6, the process runs just fine capturing all events without any issues, but with Java 7, the issues persist.
In addition, we are only performing updates/inserts on to TimesTen, no delete actions. The OS on which TimesTen's and XLA runs is Red Hat Enterprise Linux Server release 5.7 (Tikanga).
We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2 . It throws error unknown referenced column error. Command> select current_date from dual; 2211:
Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support.
TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function
Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.
we want to truncate a oracle Table in the Oracle DB. After the truncate the fact table will be loaded again. After the new load in the fact table we want to tell the times ten db to refresh the cache table. The cache Table is a user owned read-only cache group with no autorefresh. We want to tell times ten in a PL/SQL Block from Oracle DB that starts the refresh from the cache group in times ten. The refresh should not be a autorefresh because the refresh should only start if the fact table will new loaded after the truncate.
View 1 Replies View RelatedI want increase speed of importing data using ttisql. My script contains about 12k simular MERGEs. Can I prepare this state,ent once and later substitute params from script?
View 1 Replies View RelatedI have already created an active standby pair(without cache group) and managed by Oracle Clusterware and it work fine.
Now ,I create another DSN and I also want to create another active standby pair on the same 2 hosts.
I try to setup and reference this setting:
[URL]...
But I get error message in ttcwmessage :
TT17013 The default hostname cannot be set to host_name because a local host of host_name is currently in use
Is it possible that I can create more than one A/S pair(with different DSN) ? Or any other solution? !
TimesTen version:11.2.2.3.3 (64 bit Linux/x86_64)
For applications and Timesten databases on the same server we can use direct model to gain the base performance. But I want to know that how big heap size of JVM to be set for my java application when enabling direct model?
Does my application need more head memory when direct model than other local communication protocols, such as Unix domain socket or IPC? Supposing my Timesten database takes 12GB memory from OS, does it mean I need specify the same size for JVM heap(-Xmx12G)?
I have one table as shown below,
CREATE TABLE MAT
(matrl varchar2(100),
date_man date,
weight number(10)
);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);
The data will look like this .
matrldate_manWeight
mat112-Dec-10100
mat213-Dec-10200
mat321-Dec-10300
mat426-Dec-10400
mat522-Dec-10500
mat602-Dec-10600
mat723-Dec-10700
mat807-Dec-10800
I need to order by this data in terms of date and need to get the data as shown below. Acc_wt is accumulated weight till the material.
Date Weightacc_wt
mat602-Dec-106000
mat807-Dec-10800600
mat112-Dec-101001400
mat213-Dec-102001500
mat321-Dec-103001700
mat522-Dec-105002000
mat723-Dec-107002500
mat426-Dec-104003200
Can we make use of oracle lag and lead functions here?Or some other methods?
I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
I have some question.
TTversion : TimesTen Release 11.2.2.3.0 (64 bit Linux/x86_64) (tt112230:53376) 2012-05-24T09:20:08Z
We are testing a AWT cache group ( with CacheAwtParallelism=4 ).
Application(1 process) to the DML generates to TimesTen(DSN=TEST).
At this point, Are delivered to the 4 parallel DML?
[TEST]
Driver=/home/TimesTen/tt112230/lib/libtten.so
DataStore=/home/TimesTen/DataStore/TEST/test
PermSize=1024
TempSize=512
PLSQL=1
[code].......
The main condition in SQL is like this.
SELECT TO_DATE (TO_CHAR (doc_date, 'MON-YY'), 'MON-YY') "INV_MTH",
SUM (inv_amt) INV_TOTAL
FROM table_x
WHERE doc_date BETWEEN TRUNC (SYSDATE, 'YYYY')
AND LAST_DAY (
ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)
);
My Output from if run in JAN as of now 16 Jan.
INV_MTHINV_TOTAL
Jan-136260830.42
I want an sql until previous day of that month for example 15 Jan and another sql until day before previous day of that month for example 14 Jan.
create table a(sourcerow number(2), test_level number(2), dpn varchar2(1), qty number(5)); T
he insert scripts are as follows:
insert into a values(1,3,'Y',5); insert into a values(2,2,'Y',4); insert into a values(3,3,'N',3); insert into a values(4,4,'Y',3); insert into a values(5,1,'N',6); insert into a values(6,2,'N',5); insert into a values(7,2,'Y',4); insert into a values(8,3,'N',2); insert into a values(9,4,'Y',2); insert into a values(10,1,'Y',3); . SQL>select * from v$version; Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
My logic should have the output as follows without 5th column: sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has
dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.
It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent.
when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below
go_block('show');
clear_block(NO_VALIDATE);
declare
cursor c1 is select *
from qtr_demand order by 1;
begin
[code]..........
I have a table:
create table FIELDS
(
FIELD_NAME VARCHAR2(30) not null,
PRG_FIELD NUMBER not null,
LENGTH NUMBER
);
with
INSERT INTO FIELDS VALUES('FIELD1', 1, 3);
INSERT INTO FIELDS VALUES('FIELD2', 2, 3);
INSERT INTO FIELDS VALUES('FIELD3', 3, 4);
INSERT INTO FIELDS VALUES('FIELD4', 4, 2);
INSERT INTO FIELDS VALUES('FIELD5', 5, 1);
I need to insert in a table:
create table STUFF
(
FIELD_NAME VARCHAR2(30) not null,
FSTART NUMBER not null,
LENGTH NUMBER
);
And the output I want is:
INSERT INTO STUFF VALUES('FIELD1',0,3);
INSERT INTO STUFF VALUES('FIELD2',3,3);
INSERT INTO STUFF VALUES('FIELD3',6,4);
INSERT INTO STUFF VALUES('FIELD4',10,2);
INSERT INTO STUFF VALUES('FIELD5',12,1);
So each field starts where the previous (ordered by PRG_FIELD asc) ends.
I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.
PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out
I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
View 7 Replies View RelatedI need a query to retrieve a particular string from all the columns and all tables in a specified schema.
For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.
I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).
I've already achieve getting data from the previous month by using this formula:
(to_char(CRE_DTTM,'MON')) = UPPER(to_char(add_months(trunc(sysdate,'MONTH'),-1), 'Mon'))
My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
i am looking for a SQL query by which i can extract data between 25th of previous month till today .
i tried the below code but no luck
SELECT TO_CHAR(SYSDATE, 'YYMM'), TO_CHAR(SYSDATE, 'YYMM') -1, TO_CHAR(SYSDATE, 'YYMM') -2, TO_CHAR(SYSDATE, 'YYMM') -3
FROM DUAL
I have a following requirement in SQL -
Requirement -
I need to list the purchase Ordrers which are not closed.
my sql query should pull if the PO is not closed in 30,90 and 150 days.
It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.