Hot answers tagged

3

You can calculate a RANK/ROW_NUMBER based on this logic: ROW_NUMBER() OVER (PARTITION BY IMMS_ID ORDER BY CASE WHEN status='DI' THEN 1 WHEN status='OI' THEN 2 WHEN status='FI' THEN 3 WHEN status='OV' THEN 4 WHEN status='FV' THEN 5 END)


2

Excellent points by @joishi-bodio. Here's a simple diagram explaining the different entities, attributes, and their relationships in your model. The first step in modeling any database is to identify the real-world entities; the attributes that define each entity; and then their relationships and cross references. Here are different observations and ...


2

I think the article Raj quoted (https://www.pythian.com/blog/analyze-index-validate-structure-dark-side/) describes this pretty well. "clustering factor" was also my first guess while reading the description of your problem. I also prefer to use RMAN to check for corruption. RMAN> backup check logical validate database; Afterwards you can query ...


2

perhaps you looked at flashback queries, what you need are restore points. Create a guaranteed restore point before starting test Perform test, measure, save results startup mount the db, flashback database to restore point drop and re-create guaranteed restore point. lather. rinse. repeat It really doesn't get easier than that. I do it multiple times a ...


2

Sure you can, you can append the SAMPLE clause to your selects: For example: select * from table sample(20); sample_clause The sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table. BLOCK BLOCK instructs the database to attempt to perform random block ...


2

Do I understand it correctly that you have a production database with 1mil++ rows in some tables and you want to reduce the amount of rows in your test/development database? This is pretty tricky and there is no feature which does this "in a few clicks". First of all you should analyze the relations between the tables. If you have 100 tables with a small ...


1

I figured it out, had to group by trucn(invdate,'mm') and count the separate columns SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month", COUNT(DISTINCT(inv.order_no)) "Orders", COUNT(inv.order_no) "Order Lines", COUNT(DISTINCT(inv.invoice_no)) "Invoices", COUNT(inv.invoice_no) "Invoice Lines", ...


1

You can create pfile to set asm_diskstring. $ vi asmpfile.ora instance_type=asm asm_diskstring=path_to_asmdisks Then can start asm instance using this pfile and later can create spfile from pfile.


1

First of all do not use database links for larger transactions. This can cause too many problems with blocking sessions. Another hint is to keep transactions in a good size (not to small, not to big). If your table is not partitioned then write a piece of code to remove just 1000 rows, commit and delete the next 1000 rows. You said something about alter ...


1

Table1: A (DATA1, DATA2, DATA3) Table2: B (DATA1, DATA2, DATA3) Assumption: First fieldName of both table is primary key MERGE INTO A TA USING (SELECT * FROM B) TB ON (TA.DATA1 = TB.DATA1) WHEN NOT MATCHED THEN INSERT (TA.DATA1, TA.DATA2, TA.DATA3) VALUES (TB.DATA1, TB.DATA2, TB.DATA3);


1

In such case you don't have to use MERGE. Assuming data1 column can be used to check whether the record exists, the following should do the job : INSERT INTO A (data1, data2, dataX) SELECT B.data1, B.data2, B.data3 FROM B b WHERE NOT EXISTS ( SELECT NULL FROM A a WHERE a.data1=b.data1 );


1

Did I understand this correctly that your customer has MSSQL Server? Source Database MSSQL or other You have two options: Script your own replication Use Oracle Golden Gate to stream the data to your Oracle Database. Golden Gate supports "zero downtime" migration. Source Database Oracle Additionally to the options mentioned in "Source Database MSSQL ...


1

Redo logs can be mirrored, your query does not take into account that. Controlfiles are also part of the database. select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) ...


1

You can try: shutdown immediate startup mount alter database datafile '/u02/oradata/hfd1/index/index17.dbf' offline drop; alter database open


1

bloodgroup can be moved to it's own table.. You probably don't need bloodgroup at all in the blood_bag table, considering that it's always going to match the donor's blood type. Likewise, you shouldn't need the donor_id in the transaction table since it's always going to match the blood_bag's donor. I'm not really sure why you have a receiver table at all ...



Only top voted, non community-wiki answers of a minimum length are eligible