I'm about to build a machine whos prinary focus will be running a 'hobby' app that uses oracle express as its db. some info on the db..main tabl has 30 fields and 500K records,all other tables add up to about 20% of that size.
I run huge queries that return most of the db in about 200K rows with 50 fields.these queies contain many 'partion fields' using 'rank', 'percect rank' etc. My old pc really struggles with thhis stuff and im going to invest about 600 euro (exculing monitor etc) in a new mahcine.
general terms what i should get, im thinking about OS, hard drive (1 for data, 1 for indexs?), mutli core processer?, type and size of ram. Any othr consideration i should have.
I have NO experience with APEX but - have been browsing the documentation in considering it's use as a web application/dashboard reporting utility.
However - I'm wondering if it allows for the ability to connect to DB2 as well as Oracle...
i.e. - building a form that allows a user to 'pull' data for analysis from a DB2 instance and - generate reporting results that would then be stored in an Oracle instance.
I'm using Apex4.1 on a hosted platform. I'm trying to build a business application and the client wants a Dashboard.
Here is the best way I can explain it:
The dashboard displays a series of rows in the table. When you click on the "edit" button, it runs a query and displays it data on a report. How would I build this?
Let me explain it a different way:
The home page shows a table report with Column 1, Column 2, Column 3.
I want to make it so where you can click any ROW and then it goes to a different page that shows all of the data So the Home Page you can just see 3 Columns, but the next page will have all the columns.
I want my application to provide an end-user with ability to upload the files from their machine to SFTP server directly. So far, I have managed to write the function which uses java source and allows to upload files from the database. It works just fine.
The function looks as follows:
FUNCTION SFTP_CMD ( V_USER IN VARCHAR2, V_PASS IN VARCHAR2, V_HOST IN VARCHAR2,
[code]..
where V_FILE_SRC represents location of the file to be uploaded. The thing is that the FILE BROWSE apex item does not allow to specify the file location, it identifies it somehow automatically, when you press the button 'Choose file'. The question is how can I catch file's path, so that I can pass it to V_FILE_SRC parameter in my function? Should I write my own plugin? or hack FILE BROWSE item? =)
(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?
(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?
(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ? (b) I am planning to implement this in two steps :
first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE
I am trying to build a data warehouse for Consumer Price Index and so I have downloaded data from the Bureau of Statistics.It is in excel format and since I am working with Oracle Warehouse Builder I have converted it to .csv file so that I can use it as a data source.
Question1: Is it practical to use single .csv file as a source of data for a data warehouse?
Question2: I have 3 dimensions tables and a fact table.The dimensions are one for the Region(as the date is organized in region,states etc),two is the consumer goods and services (as the data is organized in groups of goods and services, services/goods types) and finally time(year and month),
Now how am I going to do the mapping here?Is it possible to do a one to one mapping here as all data required by the dimensions is located in the .csv file.
I have been trying to figure out how to write a query that shows each building code building name and number of rooms from a database with four tables : emp, build, room, roombook
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt` do echo 'BEGIN WORK; ' > rc1.sql echo ' ' >> rc1.sql echo 'insert into Master' >> rc1.sql echo '(prod, rate, rate_exp) ' >> rc1.sql
I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.
Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:
Left outer join. Works but its incredibly slow:
SELECT distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM", count(distinct ule.id) as "User Logins", count(distinct ule.user_id) as "Users logged on", count(distinct p2.id) as "Existing Users", count(distinct p1.id) as "New Users", count(distinct r1.id) as "Resources created"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select distinct ule.month as "Month", count(distinct p1.user_id) as "Users created", count (ule.id) as "Logins", count (distinct ule.user_id) as "Users logged in", count(rv.id) as "Resource Views", count(distinct rv.resource_id) as "Resources Viewed"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select distinct ule.month as "Month", count(distinct p1.user_id) as "Users created", count (ule.id) as "Logins", count (distinct ule.user_id) as "Users logged in", count(rv.id) as "Resource Views", count(distinct rv.resource_id) as "Resources Viewed"
[code]....
another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.
select distinct ule.loginday as "Month", count(distinct ule.id) as "Logins", count(distinct ule.user_id) as "Users logged in", count(distinct p1.user_id) as "Users created", count(distinct p2.user_id) as "Existing users1"
[code]....
performance is OK with 2 tables but the example above takes forever to execute.
Tried an approach with union but this creates new rows for each table
SELECT DISTINCT p1.MONTH AS "Month", COUNT(DISTINCT p1.user_id) AS "Users created", NULL AS "Logins", NULL AS "Users Logged in", NULL AS "Resource views", NULL AS "Resources viewed" FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,
i install oracle 10g on linux on perform the following step but my database is not auto start with the machine
Automatic startup and shutdown oracle on linux Oracle database server provides two scripts to configure automatic database startup and shutdown process.
The scripts are, $ORACLE_HOME/bin/dbstart $ORACLE_HOME/bin/dbshut
Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.
-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.
In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.
If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and startup or shutdown the database.
Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:Y Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.
If in my database there is two database named arju and arjudup then my oratab file will contain the entry like, arju:/var/opt/oracle/product/10.2.0/db_1:Y arjudup:/var/opt/oracle/product/10.2.0/db_1:Y where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.
Step 02: Create a script to call dbstart and dbshut. In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.
a) Login as root. b) Change directories to /etc/init.d c) Create a file called dbora and chmod it to 750.
# touch dbora # chmod 750 dbora d)Edit the dbora file and make the contents of it like below.
i was searching for the example of subject mentioned and visited the following website
[URL]...
here is a tutorial to create such report, but i am confused as here in Report's Data Model they created 3 Ref Cursor Queries and did not Close the opened cursor, is there no need of this or any mistake or if it necessary, where have to Close the cursor?First, they have created following Packages
PACKAGE concl_cv IS type conclass_rec is RECORD (EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25),
[code]...
then, created Functions which created in Ref Cursor Query in Data Model function q_portdescRefCurDS return port_cv.portdesc_refcur is temp_portdesc port_cv.portdesc_refcur; begin open temp_portdesc for select department_id, department_name from departments;
[code]...
nothing here any Close cursor statement.i am trying to explore this area of the development.
I am a software developer for a small company with a parts inventory. We develop a Java program that is an add on to our inventory management software.
The inventory management software recently got upgraded from a Firebird platform to an Oracle platform, so to continue developing our software I need to upgrade our development environment. This environment is a separate environment (more specifically, my laptop) so I am trying to get a copy of the database onto my machine.
The software vendor pointed me to a DMP file that he said I could use to import the data onto my machine. However, using impdp has been a headache. I ran it with the parameters supplied from the software vendor and I received a flood of errors (already exists errors, data too big errors, parent key not found errors). After waiting several hours for impdp to do its thing, the result is that the user I am trying to import to is still empty.
Is DMP the best way to accomplish my goal? If you were trying to get a database from machine a to machine b in Oracle, how would you do it? In Firebird, it was as easy as copying the database file and pointing the DBMS to it.
I am not looking for live sync, I plan to update the database with the live data about once every month or so.
I have installed oracle 10g on Red Hat Linux and I am able to connect as well.The listener and DB is up and running and I am able to coonect as well from the server.
But I have installed the oracle client (TOAD) on some windows machine and from there I am not able to connect . I am directly connecting by giving IP and other details and using TNS as well but I am not able to connect from client machine. I am getting ORA 12170 - TNS timeout error.
I have Oracle installed on a Windows 2008 server. When the database was created, it could not register with the listener (I confirmed that the listener service was running but when I started creating the database it stopped).I completed the db creation and started the listener again and tried "alter system register" but that didn't work either.Then I manually edited listener.ora and added an entry to the SID_LIST_LISTENER and I could successfully connect using sqlplus dba1@orcl/password from the same server.
Now, I tried to use the instant client to connect to the database from another Windows 2008 server. I copied the TNSNames.ora from the db server to this new server. When I run sqlplus dba1@orcl/password I get the ORA 12154 error.
Why would this happen from a remote machine which is on the same network? Ping from one server to the other works (both ways).
I have installed Developer 10 suite and 6i on same machine, developer 6i run time works properly but when i open URL with "?config=icc" it says form generated in previous version of forms builder, i have already compile called form (configure in default.env and formsweb.cfg) in forms builder 10g.
I would like to down load and install 11g XE beta but I do not know if it will install and run on my 64 bid Windows machine. I had trouble with the 10 G XE trying to down load it on 64 bits.
If I cannot use it on 64 bits could I make it work if I down load Unbuntu Linux? System info as follows:
OS Name Microsoft Windows 7 Professional Version 6.1.7601 Service Pack 1 Build 7601 Other OS Description Not Available OS Manufacturer Microsoft Corporation System Name XXXXXXXXX (censored) [code]........
On cliente/server, I used to get the user IP address using SYS_CONTEXT and IP_ADDRESS, and the terminal name with SYS_CONTEXT and HOST.But in web environment, these functions returned Server IP and name.I have to build a trigger to obtain the information of user IP address and terminal name to save on audit table.
I have some client RMAN backups from an AIX machine, a set of .bkp files including the control file, that I have been asked to restore on a Windows 2003 server. My first question is if such a restore is possible?Also, the backups are from Oracle 10.2.0.3, and I need to restore them to 11g, will this cause any complications?
how to carry out such a restore procedure from scratch.
am trying to backup my oracle database using RMAN to a remote machine. both systems are using redhat linux. i have successfully configured NFS on my remote machine and mounted it from my oracle database machine.
i then configure rman channel to disk on mounted linux directory..when i take full backup of database i receive this error
ORA-19504: failed to create file "/mnt/ora_df766174940_s104_s1" ORA-27054: NFS file system where the file is created or resides is not mounted w ith correct options Additional information: 3
How do i mount this with correct options the error msg says OR what do i do to backup to the said directory?
I work as a Sys. Admin. for several RHEL 3.8 servers, most of them are clusters of 2 machines. All these servers are running Oracle 9.2.0.7 database. They are running fine on a separate filesystem. So everytime the system has to be formatted for some particular reason, there is no need to re-install the database.
I am trying to make some tests by running the same filesystem containing the oracle database in a fresh Red Hat Enterprise Linux 4.8 X86_64 Install. This task has become impossible, and I'm not quite sure why.
I installed all the compat- packages required for a fresh oracle 9 install in a RHEL4 machine, but at the time of stating the STARTUP sentence, it gives me the next error:
CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2. CMCLI ERROR: OpenCommPort: connect failed with error 2.
After this error (repeated) it says something like: Cannot start an already running database.... But if i stat a shutdown sentence, it says that the instance has not been initialized....
I don't know whether i have to re-install all the oracle software in order to make a clean install in the new kernel version or not, i tried to apply a patch, and the oracle installer recognized the installation i had.
I think it might be because the original system is configured to work as a cluster, and i'm running it on only a virtual machine.
From a script how I can read a local file (where I have the sql client)?
I have an Oracle database on a server, and I connect from another machine with SQL Developer.I want to read a text file on my local machine ( where I run my script) to use the data in the script.I never want to read or write on the server.
Reading on internet i read about UTF_FILE package But what is not clear to me is, this is on the server or client ?Also some people says UTF_FILE can work both on client side and server side.Also I read about package text_io, but I think it is only for forms.
i am using oel 5.5 with oracle 10g release 2 installed in it . I am unable to run two different instances on same machine how can do that? I changed the ORACLE_SID then i startup one instance is starting but when i change the sid again and tried to startup it shows me that oracle is already started shut it down first.
Is there any way to startup two different instances?
