below query.
1) How to add a new column to the existing table's particular position, instead of atlast.
2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies View Relatedi have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
i want to create a new partition for version 2
existing table is as below
create table test
(
name varchar2(100),
version NUMBER(12)
)
[Code]....
I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?
View 2 Replies View RelatedI had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04. I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04.
I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option"
how to convert existing table with varchar column to a clob
View 1 Replies View Relatedi have one table emp in this table i have the column eno,ename,hiredate and i have data also in this table.
my eno colum data type is number so now i have to change this colum data type from number to varchar2.
if yes how can when i am trying to change this colum data type i got this error
Failed to commit: ORA-01439: column to be modified must be empty to change datatype.
I have 11.1.0.7 instance running and got more 300G of storage available for the database. I would like to know what are the steps to add these disks/storage to the existing DATA asm disk group?
View 5 Replies View RelatedI am facing some challenge while running update query on newly added column in existing table.
Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)
[Code]...
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24
Problem faced & My analysis
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Below are the queries completed successfully
Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
[Code]...
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[Code]....
We have a Production Oracle 10g R2 RAC on HP-UX v2 IA64 servers.We have Two Disk Groups one for Archive (ARC_DISK - 100 GB) and other for Database(DATA_DISK - 1 TB]. We wanted to add more space to the DATA_DISK disk group.Unix admin configured 200 GB from SAN and changed the ownership of the Disk to oracle and permissions to 775 on 1st Node.I opended DBCA from 1st Node and was able to see the disk in 'Show Candidate'.
I added this disk to the DATA_DISK disk group and clicked OK but got ORA- error with some message like some operations could not be performed. I exited DBCA.We realized that we had forgotten to change the ownerhip and permission from the 2nd Node.Unix admin changed the ownership of the Disk to oracle and permissions to 775 on the 2nd Node.
I opened DBCA again from 1st Node and selected the DATA_DISK disk group but could not find the Disk in 'Show Candiate' open. I clicked on 'Show All' and this disk was shown with Header_Status - MEMBER but not allocated to DATA_DISKGROUP. When I clicked the 'Show Member' option, this disk is not shown for DATA_DISK disk group. I exited DBCA at this point.As this is a critcal Production database I didnt proceed any further and exited DBCA.
Now I need to add this Disk to the DATA_DISK disk group but not sure which option to select. I got one reply from another forum to run DBCA select the DATA_DISK Disk Group and then click 'Show All' and select this Disk (which already has MEMBER as Header Status) and select Force Option and click OK to continue.
current environment
Oracle VM 2 Node RAC cluster
Oracle 11G 11.2.0.3.0
Oracle 11g rel 2 GI
on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2
we shutdown vmorarac1 to clone it to vmorarac5
on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211
mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5
152.144.199.171 vmorarac1.pbi.global.pvt vmorarac1
192.168.2.30 vmorarac1-priv.pbi.global.pvt vmorarac1-priv
152.144.199.184 vmorarac1-vip.pbi.global.pvt vmorarac1-vip
[Code]....
Query is is that all ok to reboot the new node to reflect changes as below and change the hostname to vmorarac2
152.144.199.210 vmorarac2.pbi.global.pvt vmorarac2
192.168.2.32 vmorarac2-priv.pbi.global.pvt vmorarac2-priv
152.144.199.211 vmorarac2-vip.pbi.global.pvt vmorarac2-vip
hostname change in /etc/sysconfig/network
On new node vmorarac5, there is alreay software for RAC DB and GUI cloned from vmorarac1
addnode.sh is used to add existing node, but as part of pre-requisite configuratoin is there any config step missing
how do I add a column to an existing table which when rows are added to the table this column will have the same default value?
alter table thetable add (new_column_name varchar2(100) default bbubu);
I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
Here is what I have made so far:
DECLARE
COLEXISTS integer;
vCmdStr varchar2(4000);
[Code]....
I can't figure out where the invalid character is.
I had created a table which have 100s' of entries in it.
create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
[Code]..
1. In this table, how to update the Nth row, how can I do it
2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.
i have 2 data block (maintenance & maintenanceparts) in one form and i will like to add the values of the attribute "LineCost" in the maintenanceparts datablock and put the sum into 'Totalcost' attribute in the maintenance datablock.
I tried use the code
"begin
select sum(maintenanceparts.LineCost)into :maintenance.totalcost
from MaintenanceParts;
end;"
But it ended up adding all the records that was save in that attribute and putting it in totalcost. I am new to oracle
we received a design advise to add columns to track the update and delete done on each row in our tables:
- DELETED_DATE_TIME
- DELETED_BY
- UPDATE_DATE_TIME
- UPDATED_BY
In our architecture, the application can only access functions/procedures to access/modify data. Each function logs the action, the executed sql statement, oracle error, user terminal, and the user into a unified log table by using v$ tables to create a general log function that is called after execution or error.
The only advantage is that it will be easier to know the delete and last update information faster versus space and design modification.
we are providing a salary column. from that salary column we need to calculate bonus with the condition as bonus=(salary+10%). how to execute this.
View 5 Replies View RelatedOracle 11g r2, APEX 4.1.1.00.23.
I have some classic reports.
I go to Report Attributes, then I click Add Column Link in the "Tasks" right menu, it adds me a column link, I just add some text for the link and a page to go to. Then I run the report and I get :
report error: ORA-01403: no data foundTested with several classic reports on multiple pages.
Debug mode shows me :
0.43816 0.00240 ...Execute Statement: select distinct [...] order by 3,11 ,4
0.44056 0.00162 print column headings
0.44218 0.04816 rows loop: 25 row(s)
0.49037 0.00141 report error: ORA-01403: aucune donnée trouvée
0.49175 0.00078 Computation point: After Box BodyWhen I run the query in my favorite tool, I get expected results.
Did I missed something ?
On a "Form on a table with report" page created with the wizard, I want to add a new report column from the underlying table (this is the multi-row page). If I try to add the column name to the query, Apex tells me:
"You have requested to change the Interactive Report query. If you added columns to the query, they will not be displayed when the report is run. You will need to use the actions menu and either select the columns or click Reset. If you removed any columns from the query, it will disable existing filters, highlight rules, and other report settings referencing those columns. Please confirm your request."
I sure don't want to start over and re-build the page, so where the heck is this "actions menu"?
I'm running Apex 4.1.
We have a fact table t1 in the warehouse which has above 6 million records.There is to be an update like this where t2 has aid+bid as composite primary key. column aid repeats in t1.There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.
update t1
set t1.aid =
(select t2.aid from t2
where t1.bid = t2.bid
)
I've tried cursor loop with 3 commits in the middle based on if condition that evaluates on every iteration.
sort out the error of this procedure to search an employee by passing employee fname or middle or last to get his name .
CREATE OR REPLACE PROCEDURE "WEB_MASTER"."SEARCH_EMPLOYEE"
(empname IN VARCHAR2) is
cur number;
v_emp_name VARCHAR2 (100);
r number;
begin
[code]....
I installed web logic server and form & report on my linux system and gave configuration option later . so now web logic admin server is running on different port and wls_reports server is running to different port in cluster. How its running on cluster and when i run
localhost:9002/reports/rwservlet/getserverinfo?server="report_server_name" i am getting rep-51002 error.
I am using Oracle 11g database. The situation is I need to run a batch process a fixed intervals and copy data into my table from a remote database using a DB Link. I have 3 tables,1. Original data Table, 2. Exported data Table, 3. Status table. All the data from the remote database will be loaded into the exported data table. As and when each copying is finished the status table will be updated with 'Y' if export is success and with 'N' if export is unsuccessful. When the exporting is over, then status table is checked, if all the status' is Y then the data from Exported data table will be copied into the Original data table after truncating all the rows of Original data table. If the status is N then the Original data table will be intact.
How to check whether the exporting was success for all the exported data ? what to do if the connection through the DB Link was lost during the exporting?
I created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?
View 3 Replies View RelatedIve got a look up table and i want it to display text instead of just the ID number, how would i do that? so for example if
1 = Hello
2 = Bye
it would display "Hello" instead of "1"
I want to have sum of Time Stamp Values in a Table. i.e. Group by Query
example : Select Name, Sum(Timestamp column) from Emp group by name;
OR
I have Time values in character column of a table. i.e. Table TT column t1 char(5) values '03:02'
Above mentioned values in more than one line, i want to get the sum of all the values and it should be a time value.
Ive got loads of new entries that need to be entered into a table if they're not in there already.
Is there a quick way of doing this rather than having to add the entries manually using some kind of tool rather than using manual sql statements(would take a very long time since theres 800 new entries).?
I have requirement on table type. without using bulk or %rowtype is there any possible to create table type with two or more columns.
I got with single column,but I am unable to create with multiple columns.
DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
T T1;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100 LOOP
CNT:=CNT+1;
[code]....