I am struggling with the ORA_ROWSCN pseudocolumn. (Oracle version is 11g). I am aware that without ROWDEPENDANCIES set on the tables, the SCN of a row is in reality that of the block in which the row is resides. The problem I am seeing goes something like this:
1) A datapump import creates a large table (400M+ rows) at 11 am in the morning
2) No subsequent changes are made to this table (at least that I know of)
3) Despite no changes being made the ORA_ROWSCN of almost all the rows changes every few minutes. (a few rows remain with SCN's indicating the original time of import, but the rest change)
I detect this behavior by running this query:
SELECT ora_rowscn, SCN_TO_TIMESTAMP(ora_rowscn),COUNT(*)
FROM test_table GROUP BY ora_rowscn
This ran around 5.15 am on 10/27 and resulted in:
72353518 27-OCT-12 05.17.21.000000000 AM 492009391
67515248 26-OCT-2 11.01.45.000000000 AM 370
Note that almost all rows have a very recent SCN. Only a tiny portion of the rows (370) have an SCN consistent with when the import completed. If I rerun the query a bit later, the SCN of the large rowcount will have advanced again.Block size on the database is 8192.
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
how to find the root causes for temporary table space to grow unexpetedly and how to claim that grown space back automatically after the transaction over.
I'm having some trouble with iSQL*Plus.I instaled oracle database and at first it worked fine, but then I installed client on the same maschine and all products that come with client, and somewhere in the meantime iSQL*Plus stopped working.
Then I uninstalled client and products that go with him (Policy Manager, Oracle Wallet...) because I thought that they have "broken" iSQL*Plus, but it still doesn't work. Finally, I re-installed iSQL*Plus, but that didn't work either. Interesting thing is that when I type in command line
" c:/miljana/bin/isqlplusctl start "
it says that iSQLPlus is started, but when I go to browser there is an error:
" Firefox can't establish a connection to the server at miljana.localhost.com:5560. "
Also from Administrative tools/Services if a try to start iSQL*Plus there is an error:
" Could not start the OracleOraDb10g_home1iSQL*Plus service on Local Computer. Error 1067: The process terminated unexpectedly. "
I know that there is a lot of topics for this, but most of solutions where for Linux, and didn't work for me.I'm using Oracle 10g Enterprise Edition on WindowsXP
Backup for database has been working fine until today. Today, about 1/3 of the way through the backup (large database), I got this... RMAN-12005: error during channel cleanup RMAN-10038: database session for channel sbt_4 terminated unexpectedly ...and the backup died.
Never seen before and the research I've done online has been less than useful. This database is an SAP/oracle db with brtools 7.20 (22) and is using the new parameter in its init.sap file, called "_rman_chan_failo = yes", to address disagreement between brtools and oracle about channel drops being ok if all datafiles backed up.
Nothing in the alert log at the time of this error.
I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).
what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
I created a table of Number(20,4) column. I inserted an amount value 999999999999999.5555 but this value is rounded off to 1000000000000000.0000 automatically in Oracle. How to avoid this? I tried for less number of digits and I am getting the exact value. Is there any way to get the exact value without changing the datatype?
i have a table test on 4 different-different database, at the starting structure is same in all the database. and now i want to change the datatype of the primary key column named "testid" , then i add a temp column in test table (and it is added at the last in table).
i have copied the data of testid in temp column and renamed it as testid and i dropped the testid column , then the problem is that primary key column will comes at last and i want to make it like previous position so that there will not be any difference in all 4 database as in structure.
is it possible to changing column order as our desire without dropping the table?and i made all the script to changing the datatype of primary key column.
change my row value as column head. Here are my scripts:
Create table temp (code varchar2(3), head varchar2(4), value number(5));
insert into temp values('101','1101',500); insert into temp values('101','1102',700); insert into temp values('101','1103',600); insert into temp values('102','1101',1000); insert into temp values('102','1102',800); insert into temp values('102','1103',900); insert into temp values('103','1101',600); insert into temp values('103','1102',400); insert into temp values('103','1103',500);
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
In the database, NLS_DATE_FORMAT = DD-MON-RR.I have a date field called LOAD_DATE in one of the tables. I want to store the value as '25-OCT-11 01:16:25 PM' in LOAD_DATE field. Is there a way to do it without changing the NLS parameter (ALTER SESSION...) ?
I have created my schema in SYSTEM schema way back accidentally by forgetting to specify the database tablespace. There is about 60 objects (tables, indexes, functions etc) and 6MB Of data.
What would be the best way migrate the schema to the tablespace I planned to put it into? .
I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?
I have been asked to create a new instance of an application server. As part of this, my management has asked me to replace any old JREs or JDKs.
We run 10g, so I installed the 10203_vista_w2k8_x86_production_client on my new server. I would like to replace the 1.4.2 JDK that is bundled with the Oracle Production Client with 1.6.0.
We are about to undertake a storage migration of our RAC environment from EMC to XIV storage.
The migration method we are using means that we will be presented with identical disks post the migration, however the UNIX paths to the disk devices will be changing. Using our migration method it is not possible to present the old and new devices at the same time.
Therefore once the migration is complete i need to update the device configuration to reflect the new paths. I believe this is done using the following techniques but I am unsure as some of the documentation is ambiguous and I have not done this before
When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?
as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.
RESTRICT_REFERENCE states that the code should not do the following RNPS : Read no package state, WNPS : Write no package state RNDS : Read no database state WNDS : Write no database state
what is there to state as i know what my code is doing.
without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).
trying to get oracle-xe-universal_10.2.0.1-1.0_i386.deb rolling on Debian/6.0.1a which coincidentally is on another server, not on my desktop, so I obviously can't reach the web frontend at 127.0.0.1:8080. And I couldn't trick it with a simple TCP proxy on the server:
nc -l -p 80 127.0.0.1 8080 and then on the client: telnet 192.168.0.252 80 bailed out with an error invalid connection to [192.168.0.252] from (UNKNOWN) [192.168.0.1] 35798
So, is there a way to change the listening IP address, or to allow IPs other than the local host to connect? I've tried some googling around,.
For eg. If I want to change the background colour of Item it can be done in 6i using set_item_property builtin and can be written in mouse enter trigger. In Forms 10g when_mouse_enter trigger not be used.
How to implement the same funtionality or FOrms 10g.
I am using SQL Plus v9.2.0.1.0 and am having trouble with the column widths. By way of example:
Let's say the column is a varchar(2) and the column name is called V9ABC12345. When I see the result of my query I only get the heading name as V9 ie the maximum width of 2 characters. The table has over 100 columns and I know I can specify the column width using the format command but I am using the select * from table_name command
How do I change my formatting to include the column heading by default?
I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.
I have already done the max month query:
SELECT MAX(MONTH_ID) FROM Table_a;
SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy" FROM Table_a WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);
I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.
I have a table users which contains the column password and the datatype for this is raw(64) ,which will be the hashed one coming from the application. When Inserting the application is inserting with the lower case but while storing the table data is storing int the uppercase .Is it the expected behavior of this datatype. I am using Oracle 11g and attaching the ddl and dml pertaining to the column
CREATE TABLE users_new(USER_ID NUMBER(10), PASSWORD RAW(64) ); insert into USERS_NEWvalues(1,'ba660ed7b382f2c57754c4cd0d5289cc60ee455244847268d88b555168b28b23156f5a016b2764966a15674bec4eb0fb2220be736bfc21282be957910b9e80cf');
But after selecting from the database I am getting as below BA660ED7B382F2C57754C4CD0D5289CC60EE455244847268D88B555168B28B23156F5A016B2764966A15674BEC4EB0FB2220BE736BFC21282BE957910B9E80CF
I want to send mail whenever the schema password is changed by the developers (clients) . I created this trigger to send mail. I am able to know which schema's password is changed but i want know how to get the client details who is changing the password.
Create Trigger Pwd_chg_trigger before alter on database declare begin if (ora_dict_obj_type = 'USER') then DBMS_OUTPUT.PUT_LINE(' ORA_DICT_OBJ_OWNER = ' || ORA_DICT_OBJ_NAME); -- to know which schema -- using the above value and additional formatting i am sending the mail using my SMTP end if; end; /
