1)How can i know that in a table when i modified a row or deleted a row and which row i inserted when i want to know the particular time
can it is possiable if possiable then tell me how.
2)Is there any difference between 9i merge and 10g merge command ?
Provide me a query to find the last modified timestamp of all the tables present in a schema?
View 5 Replies View RelatedI have multiple project databases where their tables are similar. I wanted to know just a single date from each project databases is modified/updated. If I run a query like select * from component order by eng_proj_id, chg_date desc then it will return many records sorted by their eng_proj_id and chg_date in their descending but I only need just a single record with last update from each project databases.
View 13 Replies View RelatedThe requirement is to find the last date when either ATTRIB1 or ATTRIB2 were modified for each ID. There are many other columns in the table, but I'm not interested in other columns.
Test Case
SET LINES 100
SET PAGES 100
DROP TABLE test_log
/
CREATE TABLE test_log
[code]....
Expected Output
ID LAST_CHANGE_DATE
---------- --------------------
11 02-SEP-2012 10:58:32
35 05-AUG-2012 10:58:32
I have written the below query, to get the required output.
SELECT MAX(i_date) last_change_date
FROM
(
SELECT seq,id, attrib1, attrib2, i_date,
row_number() OVER (PARTITION BY attrib1 ORDER BY i_date) rn1,
row_number() OVER (PARTITION BY attrib2 ORDER BY i_date) rn2
[code]....
i have a sequence for one of my table that this sequence's current value was 3000 yesterday but today when i checked current value of it, i surprised because the value changed to 50, can i check who changed my sequence? is exists any data dictionary that shows logs of modified database objects.
View 3 Replies View Related how to check last modified function or procedure.
for example: INVO_75.PCK.PrintInvoices_fct
how to find who modified a package code at last? I can only find the latest modified date with USER_OBJECTS.
View 3 Replies View RelatedI have created a web source with the default settings and pointed it at a test site. The pages being craweled have both last modified response headers and meta tag last-modified being set in the rendered html but the crawler doesn't seem to be indexing the values.
It is indexing and returning last modified on the liked files (i.e. *.doc, *.pdf) but not the actual web page itself.
Am I missing a step? How do I get the last modified meta tag of html to be indexed?
pga_aggregate_target has been incorrectly set to 21gb in one of our databases.
SQL> sho parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 21686M
When I try to change it to 1g or 800m I get the following error.
SQL> alter system set pga_aggregate_target=1g scope=memory;
alter system set pga_aggregate_target=1g scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00068: invalid value 104850 for parameter smmmax_size, must be between 0 and 0
Whereas when i try to set the scope to spfile it works
SQL> alter system set pga_aggregate_target=1g scope=spfile;
System altered.
Version is 11.2.0.3 on RHEL 5
how I can set the pga_aggregate_target right, I am not sure whether bouncing will work or not since i m doubtful that I may get the same error related to smmmax_size while bouncing with the new pga_aggregate_target.
I want to writa a query to fetch the INCOME TAX RATE from the given table, my calculated gross salary amount is 594000
FromAMT ToAMT Rate
------- ------ ----
300001 350000 0.75
350001 400000 1.50
400001 450000 2.5
450001 550000 3.5
5500001 650000 4.50
and so on...
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
View 27 Replies View RelatedI have a small question is it possible to find the details of a user who modified the structure of a table, including what command he ran to change the structure of the table?
View 2 Replies View RelatedWe have Forms application created in Oracle Forms Builder version 9.0.4.0.19. We have done modification in some forms and few forms created using Oracle Forms Builder 10g version 10.1.2.0.2.
Now we need to convert those forms which are around 5 or 6 in number to Oracle Forms 9.
I use oracle 9i. After doing execution ,process, how can I know which table in the oracle have been update/modify?
View 18 Replies View RelatedI'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".
On searching more about this error, it seems like the function index must be dropped before altering the column.The table I'm dealing with is huge.
Question 1:In case of dropping and recreating the index, should the following steps be done:
- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index
Question 2:Is there anything else that should be done when the index is dropped and re-created?
Question 3:What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?
Question 4:Would it work if I disable the index, alter the column and reenable the index?Do I have to rebuild the index and gather Stats upon reenabling it?
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?
View 1 Replies View RelatedWhen I pass the input as 'micky', then i should get the count of records as 4. I tried to use REGEXP_LIKE, but could not get the result.
Test Case:
DROP TABLE test1;
CREATE TABLE test1( pattern_series VARCHAR2(30));
INSERT INTO test1 VALUES ('qa_micky1');
INSERT INTO test1 VALUES ('qa_micky2');
[code].....
-- I should get the output as 4 not 8
How can I find out that exact count of '~'?
SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;
I am using Release 10.2.0.1.0 version of oracle. I am getting a special character, but i suspect it as any other language character.
Its appearing in my 'TOAD editor/Sql prompt editor' as 'A?'. but when i am selecting it from the table using below query, its giving 'No rows Returned'.
select id from tab1 where id like 'A%';
How can i be able to see the exact character or which editor will enable me to see the character?
I think sql developer GUI might be able to show the same but i dont have sql developer with me.
I got an error FRM-40202: Field must be entered. I did some modifications to Standard form by copying it. after that when I selected the first Email check box in the list and then selected the Email button which I created newly for that form.
View 1 Replies View RelatedI want to compare two users on different databases, actaully there are two users one in user a on database a and another user b on database b they have same tables, and everytime when a table or object is created on user a (database a) i will take the table name ,procedure or any other object from user_objects based on ddl_created date and then i need to recreate the same on user b on database b, is there a way to find out tables which are not only created but also i need to check whether if there is any column added or any change in procedure or any other objects.Is there a way to generate the scripts based on list of objects selected from user_objects.
all i want is.
a)Find out the list of objects added along with creation scripts
b)find out the list of objects modified along with creation scripts.
How to validate a sum of some records in a table to be exact value?
I want to guarantee that sum(val_column)=100 at database level. Check constraints out of question. Trigger before update statement disable any update to that column when incrementing one row and decrement another... (unless with for all...)
I have two tables as per attachement - TABLE_A has Vehicle details while TABLE_B has Address details:
I am trying to build a query in order to link the Vehicle Details with their Address Details.
I would require an SQL that links records in TABLE_A with records in TABLE_B when group_number are the same; however links with group_number '999' (for the same customer_number) if there are no exact matches.
SELECT A.*,B.*
FROM TABKE_A A, TABLE_B B
WHERE ((A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = B.GROUP_NUMBER)
OR (A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = '999'))
The only problem with such query is that record in TABLE_A with group_number '456' will return 2 times. One with address having group_number '456' and one with group_number '999'.
There was one constraint define on the column of table that the value of column should be in range of 100 to 200. Some one has deleted the constraint. Is it possible to get the exact name of constraint from any system table.
View 7 Replies View RelatedI must admit my "google skills" have failed me and it is possible that I might be missing something obvious here but allow me to explain.
If I have a table that is populated/accessed by a typical OLTP application such that over time the data in the table may become "fragmented".
I know that is a very controversial term I used but that is not the question here. A simple way to describe the state of the table might be that the table has always been populated with lots of small INSERTs, modified with lots of small UPDATEs and data has been deleted in small DELETEs.
This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).
Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes
1) its data
2) its constraints/indexes etc.
3) its storage parameters
4) data stored in EXACTLY same manner as in original table.
To avoid complications, the table in question is just a normal heap table without any partitioning involved. While
CREATE TABLE AS SELECT (or CREATE TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.
I have a problem some user modified the objects and now objects became invalid .How to trace the ip address and operating system username and service ip who modified the objects?
View 3 Replies View RelatedI need to modify the column type that already has data in it, i need it change varchar to number then The datas entered have $ and , (e.g $3,200). I need the $ and , removed with column type set to numbers.
When i currently do this i get the following error:
The TABLE operation was not successful for the following reason:
ORA-01439: column to be modified must be empty to change datatype
I've tried unloading the data and loading the data using the spreadsheet, cut and past and csv file but both give me more errors, so i just want that one column modified.
I need to check the exact amount of space used (in bytes or MB) by a table which is having a BLOB column.I tried the following query but it is not giving the proper usage.
select segment_name , sum(bytes)from dba_extentswhere segment_type='TABLE'and segment_name in ('TEST_CLOB','TEST_BLOB','TEST_CLOB_ADV','TEST_BLOB_ADV') group by segment_name; I even tried the following stored procedure create or replace procedure sp_get_table_size (p_table_name varchar2)as l_segment_name varchar2(30); l_segment_size_blocks number; l_segment_size_bytes number; l_used_blocks number; l_used_bytes number; l_expired_blocks number; l_expired_bytes number; l_unexpired_blocks number; l_unexpired_bytes number; begin select
[code].......
But it is giving the error
Error starting at line 298 in command:exec sp_get_table_size ('TEST_CLOB_ADV')Error report:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE packageORA-06512: at "SYS.DBMS_SPACE", line 210ORA-06512: at "SYS.SP_GET_TABLE_SIZE", line 20ORA-06512: at line 103213. 00000 - "Invalid Lob Segment Name for DBMS_SPACE package"*Cause: The Lob Segment specified in the DBMS_SPACE operation does not exist.
*Action: Fix the Segment Specification Although the LOB section is specified in create table syntax.
I have following shell script :-
In our testing DB local Server , we are using following script ... We are connecting Via putty ...
CODEexport ORACLE_SID=testdb
sqlplus /nolog <<eof
conn sys/sys as sysdba
[Code].....
QUOTE 1. How can i set pagesize to get good format Our problem is output format is not good .. we set set pagesize from 0 to 120 .. no improvement .. " Again Re-Installed VMware tools also ... no improvbement ....
2. We are planning to set alert message if "Archive destination crossed 60 % Script automatically will generate alert message via mobile or mail.. JUst we will configure crontab ....
I am using view AB_EV_VIEW created with UNION ALL of 10 tables (ex. AB_EV_1, AB_EV_2.... )
I want to know the data comming in select * from AB_EV_VIEW is from which table ?
Suppose AB_EV_1 is having column ID, NAME
sql> select * from AB_EV_VIEW
100, A1
200, A2
300, A3
400, A4
500, A5