Displaying rows into columns


I have table having below records.

empno ename deptno

101 a 10
102 b 20
103 c 10
104 d 20
105 e 30

Normal Output

deptno count(*)
-----------------
10 2
20 2
30 1

I want to display like this(rows into columns)
--------------------------------------------------------

Required Output
-------------
10 20 30

2 2 1
Posted On: Monday 31st of December 2012 01:49:43 AM Total Views:  398
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Statspack Report Lost

Hi I am new to unix-Solaris. From my client laptop, I telnet to the database and ran STATSPACK report whick was successful. I thought you can get to a copy of the report generated in your current directory or at leas in the udump Where possibly can this report be How can I find a file in UNIX $find Please advise
VIEWS ON THIS POST

131

Posted on:

Wednesday 7th November 2012
View Replies!

VERITAS Bare Metal Restore

veritas has this software, VERITAS Bare Metal Restore. I didnt come across that Data proctector or Tivoli has equivalent functionalit. Anyone has come across this Please advise.
VIEWS ON THIS POST

347

Posted on:

Wednesday 7th November 2012
View Replies!

SQL trace changes explain plan

I've got a select query that is taking 20 secs when it should take under 1 sec. I've done some tracing etc and have noticed that when I put a trace on the query it chooses a different (and correct) plan than when there's no tracing. Can anyone help me understand why! 9204 Oracle on Solaris: Code: SQL> variable b1 varchar2(20) SQL> exec :b1 := 'ABC'; PL/SQL procedure successfully completed. SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:20.93 SQL> alter session set events '10046 trace name context forever, level 12'; Session altered. Elapsed: 00:00:00.00 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:00.57 SQL> alter session set events '10046 trace name context off'; Session altered. Elapsed: 00:00:00.01 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:20.76 SQL> alter session set sql_trace=true; Session altered. Elapsed: 00:00:00.00 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:00.54 SQL> alter session set sql_trace=false; Session altered. Elapsed: 00:00:00.01 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:20.83
VIEWS ON THIS POST

243

Posted on:

Wednesday 7th November 2012
View Replies!

What are the Issues in Migration from oracle 8i to oracle 10g

Can you let me know what are the issues that i face when i migrate from oracle 8i to oracle 10g. I will be very thankful if you give me a list of issues that you faced in your past experience while migrating from 8i to 10g
VIEWS ON THIS POST

128

Posted on:

Wednesday 7th November 2012
View Replies!

Rman restore

, i have a qustion concerning the restore of the hotbackup that has been taken by rman . my enviroment consists of : 1- live sun server containing live dtabase .rmanuser=rmanlive 2- test sun server containing an image of the live database rmanuser=rmantest. 3- sun server "veritas netbackup " used as a backupserver for the live database . both the live and the test servers has users on the rrman catalog . my question is : can i resotore the hot bakup that is taken on the live machine to the test machine and how \t I was thinking of this senaio : on the test machine caonnect to the target database "test database " and then connecct to the recovery catalog using the rmanlive user and then do a restore .... i havent treid anything yet .... i really appreciate your prompt response
VIEWS ON THIS POST

217

Posted on:

Wednesday 7th November 2012
View Replies!

reducing cost of table query

I have a table query which does SELECT on table A twice. The query is as follows: SELECT * from A where (ID, NAME) in ( SELECT ID, max(NAME) from A group by ID) I have a composite index, INN, on (ID, NAME) as well as a single index, NN, on NAME. However, when i ran explain plan on this query, it gives the following: SELECT STATEMENT Optimizer Mode=ALL_ROWS (rows=2, cost=13457) TABLE ACCESS BY INDEX ROWID A (rows=2, bytes=584, cost=2) NESTED LOOPS (rows=2, bytes=656, cost=13457) VIEW (rows=1 M, bytes=43 M, cost=1186) HASH GROUP BY (rows=1, bytes=43 M, cost=1186) INDEX FAST FULL SCAN INN_IDX (rows=1 M, bytes=43 M, cost=971) INDEX RANGE SCAN INN_IDX (rows=1, cost=1) Is there any way i can improve the query by utilizing my present indexes It seems that the line 'SELECT * from...' has the highest cost and doesn't use any index in its scan. Correct me if i'm wrong. Also, is there a specific cost range which we should have to ensure optimal performance
VIEWS ON THIS POST

195

Posted on:

Wednesday 7th November 2012
View Replies!

Transportable Tablespace

Does anybody know if I am able to export a transporable tablespace from a read only tablespace
VIEWS ON THIS POST

278

Posted on:

Wednesday 7th November 2012
View Replies!

shutdown take forever.

i shutdown immediate it gives me message database closed database dismounted and stop there. i checked alert file it says that shutdown:waiting for detached processes to terminate. what's wrong
VIEWS ON THIS POST

195

Posted on:

Wednesday 7th November 2012
View Replies!

Shutdown hangs Cancel "DROP COLUMN"

We were trying to drop column on a very big table, after that we cancelled that operation, and given shutdown immediate command. Now shutdown is taking too much time, going on from last 9 hours..... We want to start the database, what can be the best possible solution. Shutdown abort and startup will help
VIEWS ON THIS POST

150

Posted on:

Wednesday 7th November 2012
View Replies!

SAN or NAS for Oracle?

What is preferred for Oracle databases
VIEWS ON THIS POST

52

Posted on:

Wednesday 7th November 2012
View Replies!

Running 8i on XP Prof. Is it possible?

Has anyone tried running 8i Enterprise on XP professional
VIEWS ON THIS POST

121

Posted on:

Wednesday 7th November 2012
View Replies!

Rollback segments - Wraps

Quick and probably easy question for the resident experts in the house! I have just reconfigured my rollback segments, but I was wondering, are wraps good or bad and is there a way of eliminating them Rich
VIEWS ON THIS POST

79

Posted on:

Wednesday 7th November 2012
View Replies!

Replication from Oracle 9i to Oracle 7.3.3 is possible or not

I have a problem with replication from Oracle 9i to Oracle 7.3.4 database. Can you help me.
VIEWS ON THIS POST

243

Posted on:

Wednesday 7th November 2012
View Replies!

Question on Partitioning by day

Hi Friends I want to setup partitioned table based on day. And then as a daily maintainance , want to drop the partition older then 12 days and partition for next day. I can't get this code working select to_char(trunc(sysdate+1),'DD_MON_YYYY') into partdatestr1 from dual; sqlst := 'ALTER TABLE ' || table_name || ' add partition ' || 'PRIM_' || partdatestr1 || ' values less than ' || 'to_date(trunc((sysdate+1))' ; dbms_output.put_line(sqlst); single_execute_dynamic(sqlst); I want to put all this in procedure. Any input , how can automate add+delete partition process. Thank You Gc
VIEWS ON THIS POST

91

Posted on:

Wednesday 7th November 2012
View Replies!

Refresh the Dev database

What is the practical way or the best practice to refresh the Test and Development databases from a very large production database How do you do it in your real environment
VIEWS ON THIS POST

223

Posted on:

Wednesday 7th November 2012
View Replies!

Partitioning advantages

Hi , I am looking into the pros and cons of the partitioning in oracle 9i.I have some but it does not encourages me to go for the partitioning.Please explain me the advantages of partitioning from performance point of view or give me the link of web page where I can read the advantages of partitioning which can drastically improve the performance of my database.I am working on OLTP database where some tables have million of rows.
VIEWS ON THIS POST

80

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle9iAS install problem!

Hi all, Working on Oracle 9iAS on win2k OS. Query; 1.I am having problem at instaling Oracle 9i Application server release 1.0.2 on win2k OS. What is meant by the following: 1.wireless Edition repository: Hostname: Port: SID: While installing oracle9iAS is wireless Application compulsory or optional. 2.Oracle Portal. 3.I faced a peculiar problem after installation of Oracle9iAS that HTTP listener could not be started in the services dialog box.What could be the problem.What could have gone wrong while installing.Did I missed out any option.... Even after deinstalling and once again installing,services of Oracle9iAS could not be removed from services dialog box.as well as Oracle Home. Is there any installation guide,documentation,manual of installing Oracle9iAS on win2k. Has anyone out there installed this before. Guidance on the above would be appreciated. Thank You for your time.
VIEWS ON THIS POST

128

Posted on:

Wednesday 7th November 2012
View Replies!

Sizing of SGA

I would appreciate if anybody can suggest in allocating the SGA for a database(8i/9i).For example...I would like to create a dabtabse with aroudn 80-100 tables, around 50 trnsaction tables...,aroudn 50 concuttetn sessions,..3000-4000 trnasactiosn per day...I have around 2 GB or ram... I need a suggetion(average) on splitting db_block_bufeers or db_chache_size,shared_pool_size,large_pool_size(going to use RMAN for Backup),Java_pool_size etc.
VIEWS ON THIS POST

44

Posted on:

Wednesday 7th November 2012
View Replies!

SGA

hai, How do you calculate the SGA for your application. and the size of the database
VIEWS ON THIS POST

61

Posted on:

Wednesday 7th November 2012
View Replies!

PMON report error,next,all rollback segments become full

PMON always report error like below *** SESSION ID:(1.1) 2001.09.28.20.41.35.850 *** 2001.09.28.20.41.35.850 kssxdl: error deleting SO: c00000000ff612b0, type: 15, owner: c00000000ffa4fa8, flag: I/-/-/0x00: ORA-24756: transaction does not exist and later (after a few days) my db's all rollback segments become full and I have to shutdown abort can anyone help me
VIEWS ON THIS POST

309

Posted on:

Wednesday 7th November 2012
View Replies!

Calling Stored Procedures from c#

dere , I'm tyring to call stored procedures from asp.net C#. Initially i created a storedprocedure in mysql databasewhich returns awhole table when executed.Bt i'm a bit confused on how to callthe stored procedure from c#. thnks in advance.....
VIEWS ON THIS POST

292

Posted on:

Wednesday 7th November 2012
View Replies!

reading from excel file with warning 'number stored as text'

in my application i have to read from an excel files and check the value of every cell i'm using an OleDb connection to open it and reading but i have this issue if some of the cells in the excel sheet is having this warning when i try to read it returns DBNull \t could any one give me a clue ....knowing that i can not force the user for not having this warning ...i have to solve this within my code ...any advices ....and plz if anything is not clear ask about
VIEWS ON THIS POST

236

Posted on:

Wednesday 7th November 2012
View Replies!

Not seeing Oracle Data Provider for .NET in Change Data Source window

We've been chasing this down for a couple of days now, and for some reason haven't found an answer as to why. Problem: We've installed Oracle Developer Tools for Visual Studio .NET with Oracle 10g Release 2 ODAC 10.2.0.2.21... but we are not seeing the Oracle Data Provider for .NET in the Change Data Source window of VS2005 Team Edition. I've seen many posts and helpful sites that show this as an option after installing said product. We've installed and reinstalled, and have checked the GAC, machine.config and registry settings, but it is not showing up. It is in the GAC as expected, it shows as an entry in the DbProviderFactories section of the machine.config, and the registry appears to be in order under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.102.2.20. Can anyone tell me what we may need to do other than install that would make it show up in the Change Data Source window of VS2005 Many
VIEWS ON THIS POST

217

Posted on:

Wednesday 7th November 2012
View Replies!

DB2 AS400

Does ASP.Net 2.0 have a way to connect to an AS400 DB2 database We currently push alot of our information into SQL for use on our website but we are now wondering if we can go directly to the AS400 to get data...
VIEWS ON THIS POST

273

Posted on:

Wednesday 7th November 2012
View Replies!

correct this error

How do I correct this error Inwhich image Uploaded with ImageShack.us
VIEWS ON THIS POST

215

Posted on:

Wednesday 7th November 2012
View Replies!

Export SQL Data to Microsoft Excel (using Visual Studio 2008, c#)

I have list of tables in my database. how to retrieve list of tables including data into databound control and how to export the tables data to excelsheet individually.. a)Gets the list of tables within the database b)Gets data c)Exports the data to excel worksheets (there are 2 methods defined (1) range method (2) cell by cell d.) Saves the excel sheet plz provide the code for above requirements as soon as possible
VIEWS ON THIS POST

122

Posted on:

Wednesday 7th November 2012
View Replies!

variable

dear all; how to create variale with max size in pls/sql function
VIEWS ON THIS POST

146

Posted on:

Wednesday 7th November 2012
View Replies!

Help needed with use of ...IN(list) in SQL

I'm using the standard Oracle database interface software that comes with Visual Studio 2008. In one tableadapter query I want to select rows based on a variable-length list of values, so I am using the "...WHERE value IN(:List)" construct in the SQL. But the query never returns any rows, so Iclearlyhave something wrong. In the parameters for the query, I have described the :List parameter as an ANSI String - when the query builder added the parameter it put it in astype 'Decimal', but that didn't work either.The table column being comparedis type Decimal. If I remove just that one condition from the query I getall the rowsI expect, and the values in the field being compared to the list definitely do occur in the list. There is never any error or complaint from the tableadapter - it just doesn't find the rows. I can't see any other data type that seems relevant to use for the parameter. So what am I missing here\t What'sthe trick to getting "WHEREcolumn IN (:list)" to work\t Can it be used on Decimal columns
VIEWS ON THIS POST

209

Posted on:

Wednesday 7th November 2012
View Replies!

Maximum limit Exceeded Error

Hi all, \t\t I am facing a problem in my Web Application. when try to insert a bulk data in the database, i am getting Error like ORA-00020: maximum number of processes (%s) exceeded . i am using Orcale 9i as database. what could be the problem. how can i sort it out.
VIEWS ON THIS POST

197

Posted on:

Wednesday 7th November 2012
View Replies!

Insufficient memory - webrequest

I got the following error while accessing backend (dataccess) Hope any one can help me out or just a hint how to avoid what steps to be taken etc. Arul Insufficient memory to continue the execution of the program. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.OutOfMemoryException: Insufficient memory to continue the execution of the program. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [OutOfMemoryException: Insufficient memory to continue the execution of the program.] System.Runtime.InteropServices.Marshal.ocCoTaskMem(Int32 cb) +181 System.Data.OracleClient.NativeBuffer.PtrOffset(Int32 offset) +114 System.Data.OracleClient.NativeBuffer.get_Ptr() +28 System.Data.OracleClient.NativeBuffer_ParameterBuffer..ctor(Int32 initialsize) +99 System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList& refCursorParameterOrdinals) +1922 System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) +32 System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciHandle& rowidDescriptor) +171 System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +56 DataAccess.TicketRequestDataAccess.getProcessingRemarksDetails(Int32 intRequestNo) +279 tripsnetdev.TicketRequestForm.FramePnrDetails(Int32 intTicketReqNo, String strStatus, String strFormattedStatusDate, String strFormattedStatusTime, String strPnr) +276 tripsnetdev.TicketRequestForm.Page_Load(Object sender, EventArgs e) +11575 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +739
VIEWS ON THIS POST

159

Posted on:

Wednesday 7th November 2012
View Replies!

oracle and web matrix

I'm new to all this. How can I connect an asp.net page developed with web matrix to my oracle9i database Thank you.
VIEWS ON THIS POST

186

Posted on:

Wednesday 7th November 2012
View Replies!

ORA-00907: missing right parenthesis

I am running following queries from asp.net using oracle database. Here I want to display "Empty" if any record have month field empty . I tried following query, but it is giving error ORA-00907: missing right parenthesis SELECT ra_no,cust_code,year,IIf(len(month)=0, 'Empty',month) FROM trans_view Month field type is NUMBER. Let me know What I am missing
VIEWS ON THIS POST

442

Posted on:

Wednesday 7th November 2012
View Replies!

so Urgent

Hi all iam just a new programmer iam used to use sql server data bases but i have to now using mysql or Access databases instead . i really tried but in Access i couldnt connect to the data base at all , in my sql i have installed the program but i couldnt find any interfaces to work throught it as in sql server for example ., it may be a bad source could any one in this great community guide me to use one of them but please take care iam a new programmer 2 monthes only iam using Visual Studio .Net 2002 Net Framework 1\t\t\t c#
VIEWS ON THIS POST

133

Posted on:

Wednesday 7th November 2012
View Replies!

Terminology problem

is there an equvialent to ORACLE Materialized Views in SQL Server 2005
VIEWS ON THIS POST

130

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle Raptor

For anyone developing with Oracle (in my case Oracle 10g) I would recommend checking out Oracle Raptor. It has a host of tools that make development easier.
VIEWS ON THIS POST

69

Posted on:

Wednesday 7th November 2012
View Replies!

slow response on table drop

aix/oracle 8.1.7.4 --- slow response when doing table drops approximately 9 seconds to drop a 16000 row table. Tried various tweeks including nologging for tablespaces.
VIEWS ON THIS POST

165

Posted on:

Thursday 15th November 2012
View Replies!

Rollback segments waits

Hi Friends.. I issued the following sql : select gets,waits,waits/gets*100 wait_ratio,'%' from v$rollstat The results were : GETS WAITS WAIT_RATIO ' ---------- ---------- ---------- - 898 0 0 % 21800 0 0 % 20550 0 0 % 19363 0 0 % 18700 0 0 % 20153 0 0 % 18809 0 0 % 20080 0 0 % 19423 0 0 % 18899 0 0 % 21022 0 0 % GETS WAITS WAIT_RATIO ' ---------- ---------- ---------- - 20640 1 .004844961 % 19875 0 0 % 20053 0 0 % 19873 0 0 % 19588 0 0 % 20170 0 0 % 21167 1 .004724335 % 19917 0 0 % 20122 0 0 % 19503 0 0 % 19346 0 0 % GETS WAITS WAIT_RATIO ' ---------- ---------- ---------- - 19476 0 0 % 20348 0 0 % 21198 0 0 % 2 rollback segments have had a wait. does this mean that I should consider adding another rollback segment or 2
VIEWS ON THIS POST

78

Posted on:

Thursday 15th November 2012
View Replies!

RMAN behaves different with FORMAT cmnd

Target:Oracle 816/NT 4 - RMAN DB:Oracle 816 /NT I am trying to implement RMAN at my site. When trying different options I found different unusual scenario: If I don't specify format command, It generates one backup set with all the datafiles in one piece at default location. I thought I want specific location for my backup, so, If I specify format command with fixed filename, it errors out. Specifying format with variables (_%U), it generates two backup set. Does it mean that format command force it to generate more backup set or does it apply the restriction of filesize and force it to generate more than one files. No other options like, FILESPERSET or SET LIMIT used. For your reference, here is the actual output: ******************* First Case ******************** RMAN> run{ 2> allocate channel d1 type disk; 3> backup 4> (database include current controlfile); 5> } RMAN> list backup; RMAN-03022: compiling command: list List of Backup Sets Key Recid Stamp LV Set Stamp Set Count Completion Time ------- ---------- ---------- -- ---------- ---------- ---------------------- 2688 50 450784014 0 450783988 57 10-JAN-02 List of Backup Pieces Key Pc# Cp# Status Completion Time Piece Name ------- --- --- ----------- ---------------------- ------------------------ 2689 1 1 AVAILABLE 10-JAN-02 D:\ORACLE\ORACLE816\DATABASE\1PDDSQNK_1_1 List of Datafiles Included File Name LV Type Ckp SCN Ckp Time ---- ------------------------------------- -- ---- ---------- ------------- 1 D:\ORACLE\ORADATA\ORCL\SYSTEM\SYSTEM01.ORA 0 Full 3228966 10-JAN-02 2 D:\ORACLE\ORADATA\ORCL\USERDATA\USER01.ORA 0 Full 3228966 10-JAN-02 3 D:\ORACLE\ORADATA\ORCL\TEMPORARY\TEMPORARY01.ORA 0 Full 3228966 10-JAN-02 4 D:\ORACLE\ORADATA\ORCL\RBS\RBS01.ORA 0 Full 3228966 10-JAN-02 RMAN> ******************* Second Case ******************** RMAN> run{ 2> allocate channel d1 type disk; 3> backup 4> format 'd:\oracle\oraclebackup\rman\DATA_011002.bak' 5> (database include current controlfile); 6> } RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03007: retryable error occurred during execution of command: backup RMAN-07004: unhandled exception during command execution on channel d1 RMAN-10035: exception raised in RPC: ORA-19504: failed to create file "d:\oracle\oraclebackup\rman\D ATA_011002.bak" ORA-27038: skgfrcre: file exists OSD-04010: option specified, file already exists RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE ******************* Third Case ******************** RMAN> run{ 2> allocate channel d1 type disk; 3> backup 4> format 'd:\oracle\oraclebackup\rman\DATA_%U.bak' 5> (database include current controlfile); 6> } RMAN> list backup; RMAN-03022: compiling command: list List of Backup Sets Key Recid Stamp LV Set Stamp Set Count Completion Time ------- ---------- ---------- -- ---------- ---------- ---------------------- 2708 52 450784468 0 450784456 60 10-JAN-02 List of Backup Pieces Key Pc# Cp# Status Completion Time Piece Name ------- --- --- ----------- ---------------------- ------------------------ 2710 1 1 AVAILABLE 10-JAN-02 D:\ORACLE\ORACLEBACKUP\RMAN\DATA_1SDDSR68_1_1 .BAK List of Datafiles Included File Name LV Type Ckp SCN Ckp Time ---- ------------------------------------- -- ---- ---------- ------------- 1 D:\ORACLE\ORADATA\ORCL\SYSTEM\SYSTEM01.ORA 0 Full 3229065 10-JAN-02 4 D:\ORACLE\ORADATA\ORCL\RBS\RBS01.ORA 0 Full 3229065 10-JAN-02 List of Backup Sets Key Recid Stamp LV Set Stamp Set Count Completion Time ------- ---------- ---------- -- ---------- ---------- ---------------------- 2709 53 450784488 0 450784472 61 10-JAN-02 List of Backup Pieces Key Pc# Cp# Status Completion Time Piece Name ------- --- --- ----------- ---------------------- ------------------------ 2711 1 1 AVAILABLE 10-JAN-02 D:\ORACLE\ORACLEBACKUP\RMAN\DATA_1TDDSR6O_1_1 .BAK List of Datafiles Included File Name LV Type Ckp SCN Ckp Time ---- ------------------------------------- -- ---- ---------- ------------- 2 D:\ORACLE\ORADATA\ORCL\USERDATA\USER01.ORA 0 Full 3229066 10-JAN-02 3 D:\ORACLE\ORADATA\ORCL\TEMPORARY\TEMPORARY01.ORA 0 Full 3229066 10-JAN-02 [Edited by Sharma on 01-10-2002 at 11:23 AM]
VIEWS ON THIS POST

153

Posted on:

Friday 16th November 2012
View Replies!

Rebuilding Index Tablespace Strategy

My index tablespace has been growing for 2 years. I do check it and rebuild individual indexes as and when, generally reducing their size by around 2-5%. The datafiles have autoextend OFF. So when the Tablespace reaches between 97-99% full, I add another datafile. I now have 7 x 100Mb Index datafiles. They are all DICTIONARY Managed - this is a legacy of the original pre-Ora8i database design. We now run Ora 8.1.5.0 on NT4. I want to completely rebuild these indexes - all of them. I was planning to create a REBUILD_INDEX tablespace with 1 datafile - of say 250Mb - with AUTOEXTEND ON. Then I was going to rebuild ALL the indexes into it. Then I was going to drop and recreate the original tablespace. Then I was going to REBUILD all the indexes back into this tablespace. I do like the existing individual 100Mb datafiles we use as its a good graphic visualisation of the growing tablespace. Is it best to build them all back into just 1 datafile if so, what happens if the 1 big datafile becomes corrupt Wouldn't it be better to use several smaller datafiles (For recovery purposes) If individual datafiles are better - how do I build the indexes back into 5 or 6 100Mb datafiles like before. Is there a better way to accomplish what I'm trying to achieve Advice - comments - criticism all welcome.
VIEWS ON THIS POST

87

Posted on:

Friday 16th November 2012
View Replies!

Question re. Install Oracle8i EE on Windows 2000 Professional

I installed Oracle8i Enterprise Edition on Windows 2000 Professional platform. I would like to run server and client on the same PC. Can anyone tell me how to configure it
VIEWS ON THIS POST

43

Posted on:

Friday 16th November 2012
View Replies!

question on size of segment

hi! just a question on the segment size, please find the part on "initial 1m next 2m pctincrease 50 minextents 5" on this site.. http://asktom.oracle.com/pls/ask/fp...:7149039425561 he said 1 + 2 + 3 + 4.5 + 6.75 meg of space rounded UP where did 3 came from i mean 1 is the initial then 2 next extent what is 3 4.5 is 3 + 1.5 from the PCTINCREASE
VIEWS ON THIS POST

164

Posted on:

Friday 16th November 2012
View Replies!

PLS-00905 with remote stored procedure

Oracle 7.3.3 - 7.3.4 I was working on the problem when user called remote stored procedure (user - Oracle 7.3.3, procedure -- Oracle 7.3.4) using db_link. The code is like the next: DECLARE v_emply NUMBER(5) := 11111; v_var1 number(2) := 111 v_sqlcode number(8); v_sqlerrm varchar2(256); BEGIN CHECK_MSG@db_LINK.WORLD(v_emply,v_var1,v_sqlcode,v_sqlerrm); END; The return error code was:PLS-00905: object is invalid; DB_LINK connected to user VIEWUSER. This user did not have direct EXECUTE privilege on the called procedure. EXECUTE privilege was given thru the role. I gave direct EXECUTE privilege. It did not help. So I gave direct execute privilege to all procedures that were called inside of CHECK_MSG. It also did not help -- the same error code. So I recompiled all of those stored procs. The same result. Then I gave direct privilege on all tables that are accessed thru the stored procedure and after that procedure started to work. I can understand everything except of the last step. Does anybody can explain to me why I should perform that last step
VIEWS ON THIS POST

442

Posted on:

Friday 16th November 2012
View Replies!

Please Help... Datafile Deleted.

I have deleted a datafile that was the part of the active Tablespace, Now the database does not start but can be mounted and show the Datafile in v$dadafiles. Can any one please let me know how can I start the Database without that file or how to delete from Database and add other one. This is Oracle 8i on Win NT.
VIEWS ON THIS POST

61

Posted on:

Friday 16th November 2012
View Replies!

Performance & Tuning Book

, Can anyone pls suggest me an advance Oracle performance & tuning book. I've been working as DBA since couple of years and will like to know about different DB tuning techniques, as per the performance bottleneck. I would really appreciate if someone can suggest me a book with enough illustration on performance & tuning art and possiblly if it has examples of live production enviorment.
VIEWS ON THIS POST

54

Posted on:

Friday 16th November 2012
View Replies!

keyword not supported error

when i connect my asp.net application with the mysql 5.1 then an error occur:---in connection string:-ArgumentException was Unhandled by user code keyword not supported .parameter name :dsn.............plz can anybody tell me the solution of it:
VIEWS ON THIS POST

160

Posted on:

Tuesday 20th November 2012
View Replies!

Import Data from an excel file which resides on a Server

Hi I want to import an excel file in .net. Excel file resides on server. when I try to imort the file using below code , i am getting error. Protected Function ExcelConnection() As OleDbCommand Try ' Connect to the Excel Spreadsheet 'Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ ' "Data Source=C:\Users\tcs0028\Desktop\Upload.xls;" & _ ' "Extended Properties=Excel 8.0;" Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=https://ppgac.blob.core.windows.net/dcpimages/Upload.xls;" & _ "Extended Properties=Excel 8.0;" ' create your excel connection object using the connection string Dim objXConn As New OleDbConnection (xConnStr) objXConn.Open() Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]" , objXConn) Return objCommand Catch ex As Exception Throw ex End Try End Function If i import excel file from local drive its working fine. But when i try to import excel file from server(URL of file) then i am getting error. I am getting error(Oledbexception Failure creating file.). Please sugget how to import excel file that in resides on server(file is not on my local drive, i will access file through URL).
VIEWS ON THIS POST

237

Posted on:

Tuesday 20th November 2012
View Replies!

Interbase / Firebird Connection String

Hi , I primarily work with SQL server. I have been trying to work on a current project with Interbase / Firebird DB but am having problems. I have googled and searched forums but still have been unable to get a connection to the db. I am using Version 1.5 Could you please provide me an example of connection to the database and running "SELECT TOP 1 [mytest] FROM FIREBIRDDB" Label1.Text = mytest value I program in VB but can convert. Thankyou for any assistance.
VIEWS ON THIS POST

281

Posted on:

Tuesday 20th November 2012
View Replies!

MySQL update problem

Hi i'm trying to update my MySQL Database and i've tried 3 methods and it did work. Here are the codes.. 1st Method - Using the object datasets - connected via ODBC AkuDataTableAdapters.announcementTableAdapter \t\t\t\t\t announceAdapter = new AkuDataTableAdapters.announcementTableAdapter(); \t\t\t announceAdapter.UpdateAnnouncement(DateTime.Now, Server.HtmlEncode(FreeTextBox1.Text), announceID); 2nd Method - Using ODBC connecter String akudetaConnectionString \t\t\t\t\t\t\t = "Dsn=akudetaDB;database=akudeta;description=Located at web host;option=0;port=0;server=server; uid=pass"; \t\t\t\t\t using (OdbcConnection con = new OdbcConnection(akudetaConnectionString)) \t\t\t\t\t using (OdbcCommand cmd = new OdbcCommand("UPDATE announcement SET Message = WHERE AnnounceNo =", con)) \t\t\t\t\t { \t\t\t\t\t\t\t cmd.Parameters.Add("@Message", OdbcType.VarChar, 2000).Value = Server.HtmlEncode(FreeTextBox1.Text); \t\t\t\t\t\t\t cmd.Parameters.Add("@AnnounceNo", OdbcType.Int).Value = announceID; \t\t\t\t\t\t\t con.Open(); \t\t\t\t\t\t\t cmd.ExecuteNonQuery(); \t\t\t\t\t } 3rd method using .Net Connector \t\t\t\t\t String ConnString = " Database=akudeta;Data Source=akudeta.com;"; \t\t\t\t\t ConnString += " User Id=akudetadb; Password=pass"; \t\t\t\t\t using (MySqlConnection con2 = new MySqlConnection(ConnString)) \t\t\t\t\t using (MySqlCommand cmd2 = new MySqlCommand("UPDATE announcement SET Message =Message WHERE AnnounceNo \t \t\t =AnnounceNo", con2)) \t\t\t\t\t { \t\t\t\t\t\t\t cmd2.Parameters.Add("Message", MySqlDbType.VarChar, 2000).Value = Server.HtmlEncode(FreeTextBox1.Text); \t\t\t\t\t\t\t cmd2.Parameters.Add("AnnounceNo", MySqlDbType.Int32).Value = announceID; \t\t\t\t\t\t\t con2.Open(); \t\t\t\t\t\t\t cmd2.ExecuteNonQuery(); \t\t\t\t\t } Apparently there are no errors when i put these codes in a try-catch method but my database is not updated.
VIEWS ON THIS POST

190

Posted on:

Wednesday 21st November 2012
View Replies!

DATEFORMAT Q

I have a table containing a DATETIME field. How can I in my SELECT use DATEFORMAT to retrieve only the parts of this DATETIME field that I need, i.e. just month and day with a "/" between, or just hour and minutes with a ":" inbetween /Raymond
VIEWS ON THIS POST

127

Posted on:

Wednesday 21st November 2012
View Replies!

ORA-01427: single-row subquery returns more than one row

I am returning the ORA-01427 after running the query below. Can someone help me understand why I am returning the error and how to address it. Thank you. select b.value , b.name, p.value ...... (case when p.value 'G2' then null else (select c.oldvalue from ad_changelog c where c.record_id = b.c_bpartner_id and c.ad_table_id = 291 and c.ad_column_id = 4216 ) end) as oldtradeName from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us where a.c_bpartner_id = b.c_bpartner_id and a.zz_receipt_id = r.zz_receipt_id and us.ad_user_id = r.createdby and p.m_product_id = a.m_product_id
VIEWS ON THIS POST

137

Posted on:

Friday 28th December 2012
View Replies!