I need to extract DDL's without storage parameters. if i use the export and import using indexfile or if i try to extract using the DBMS_METADATA.GET_DDL package , in both ways my output is with the storage parameters
eg:
CREATE TABLE "SEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
[Code].....
My organisation is currently discussing different storage options for the database storage. Our production database is nearly 2TB and we do not want to continue with the existing NetApp storage (we use a 2 node RAC running 11.2.02 with nfs filesystem from NetApp filer).
We were looking at different options and came across Nimble Storage, they are very fast growing company aiming mid-range storage customers. The initial talks and demonstration looked very promising in terms of IO performance (they claim 40,000 - 60,000 IOPs for their CS400 series Nimble Storage array) and other options they are providing but we understand that majority of their customers are using it for VDI and other infrastructures.
They have demonstrated us using if for Oracle database with ASM storage over iSCSI LUNs. We are yet to do the POCs and benchmarking.
Has anyone come across Nimble Storage for running Oracle databases?
I have a requirement where the data between [] or ][ has to be extracted from a string.
Here is my situation :
INPUT:
[abc] [def]-[ghi][jlk]
OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl
Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?
View 5 Replies View RelatedHow to extract DDL of DBMS_JOB in sqlplus ?
View 30 Replies View RelatedI have to extract a csv file running a sql file.
SQL>@d: estEndItem_Vio_Item_Material_Violations.sql;
This works on the sql prompt. I have to do the same using schedular now for which I want to do the same embedded in a procedure.
create or replace procedure test_csv
as
begin
@d: estEndItem_Vio_Item_Material_Violations.sql;
end;
How can I run the sql file in a procedure.
I'm stuck on 1 scenario
I have the following table:
Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion
INSERT INTO age_rateSELECT 45, 50, 60, 90
FROM dual UNION ALLSELECT 45, 50, 60, 88
FROM dual UNION ALLSELECT 40, 50, 60, 90 FROM dual UNION ALLSELECT 5, 50, 60, 88
FROM dual ;
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4 age_5_20 age_21_34 age_35_44 45 50 60 9045 50 60 8840 50 60 905 50 60 88 Required outputRate Min_age Max_age
----The below rate is for age band 0_445 0 445 0 440 0 45 0 4
--The below rate is for age band 5_2050 5 2050 5 2050 5 2050 5 20
--The below rate is for age band 21_3460 21 3460 21 3460 21 3460 21 34
--The below rate is for age band 35_4490 35 4488 35 4490 35 4488 35 44
Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not
I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.
The test script is below:
create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....
The expected output would be:
Fields - ID, old_level, old_level_start_date, new_level, new_level_start date
e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'
Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but this is not right
i want to extract numbers without DOT also.
we are moving datacenter from one place to another where existing datacenter has got ASM(RAC env) with HP storage which needs to be moved into target datacenter for EMC storage.basically How ASM can be disassociate from source HP storage then associate target EMC storage with very minimal down time.
what are all the ways and all step by steps required for the same.
How password value is stored in 9i/10g/11g internally and how to retrieve those password values ?
View 1 Replies View RelatedI am an Oracle dba new to xml db,we have binary storage with 4schemas ,Right now indexing on 2gb table takes like 20GB on Path table and secondary xml index.
I want to change the table storage without disturbing the application development just the xml type column into Object relational and merged all the 4xml schemas into master schema.
Right now i want to insert the data from old tables which had different URL . How I can improve performance without having 20Gb on secondary index objects.
I have a table:
create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.
I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)
how to extract hour part from a date?
suppose my date is like
29-mar-2004 09:20:34
i wanna get only hour from the above date-
I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.
Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.
Original database was on Unix, I'm operating on Windows platform.
I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file. I guess pl/sql and unix needs to be used.
View 1 Replies View RelatedAm trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,
View 1 Replies View RelatedIs there a way, i can extract index DDL from my database?
View 1 Replies View RelatedIm trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .
In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.
I would like to extract the user rights from the ERP that I use with SQL/Oracle.
There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.
I would like to know all the actions that all users can do or not.
The result must be something like:
Action 1
User 1 Yes
User 2 No
Action 2
User 1 No
User 2 Yes
....
I need to extract the First portion of date entered in remarks column , i have prepared a test as below.
CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....
--Required output is
LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012
I have following column data
NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)
Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL
the output should be
abc_mode
bch_mode
cdh_mode
Data that enter by user in database having " in the sentences
example:
column1
----------
"My Smurf Village
column2
--------
Farmvile
column3
--------
"Testing
My select statement is
Select column1,column2,column3 from table. Output in toad perfect but When I export into excel using pipe | as a separator, the output become
column1 in excel
----------------
My Smurf Village|Farmvile
column2 in excel
---------------
Testing
How I can code my program so that when extract to excel " is recognized as sentences. output are correct as in database?
1) Extaract time from date field
2) Extract minute form that time ..
How to extract data from the below string.
END OF FILE. ROW COUNT: 23,9
Output should be divided into 3 parts
END OF FILE. ROW COUNT:
23
9
I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:
SELECT regexp_replace (regexp_replace ( REGEXp_REPLACE (' !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 | " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','') FROM dual
{code}
Is there a more efficient way to get this done ?
I need to extract the values and store it in seperate column. write the function to extract the data from column.I am having column DOC_NO from which i have to extract dob, gender, nationality Doc_no:
9005070288080 DOB : 900507- yyddmm Gender : 0288- if less than 5000 then female else male Nationality: 080- 0 means India 1 means foreigner.
extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.
WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =
[code]...
I have a field that is 3 fields combined in 1 with ## as the seperator. I need to extract the 3 different parts into 3 seperate fields. Below is an example of the field.
Medical Issue##gets ssi on 3rd of every month##is his only transportation
What I need to end up with is:
Field1: Medical Issue
Field2: gets ssi on 3rd of every month
Field3: is his only trasportation
In one of the book (Personal Oracle Real Application Clusters: Create Oracle 10g Grid Computing) I have seen the detailed explanation to configure Oracle 10g RAC on external disk- MAXTOR A01A200.
Is it possible to configure Oracle 11g Release 2 RAC with two node using MAXTOR disk?
or Is there any other external disk available which I can use it as a shared storage?
