I do below alter command
alter tablespace RCA_MB_IDX add datafile '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbfX'
I mark X for that space ...
The command is commit .
But there is problem when do backup .
I do below alter command alter tablespace RCA_MB_IDX add datafile '/gxsdb/database oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5 RCA/RCA_REF_MB_IDX_01.dbfX' I mark X for that space ...
The command is commit .
But there is problem when do backup ...as the file cannot be find since there is extra space after ".dbf" How can I fall back ?
I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?
FUNCTION add_country_region_column (
p_s_country_code_iso_2 IN varchar2_table_type,
p_type IN d_country.c1_type%TYPE,
p_name IN d_country.c1_name%TYPE,
p_desc IN d_country.c1_desc%TYPE,
[Code]....
I have one issue while loading the value through sql*loader the last column data is SG1 and when its loaded , it is length of this columns is showing 4 char. Unable to understand, how to find this extra space. Though used TRIM but does not work.
View 8 Replies View RelatedI want to alter a very large table.
ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)
My table is very large and I am getting an error saying I am out of undo space.
The dba says the undo space is as big as the table.
alter index test_idx1 shrink space;
I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?
how I can create an index dynamically? The DBA wants me to put this code below in the beginning of a PL/SQL package. At first, I tried by just putting the below syntax in the code, but I get an 'Alter' is not a valid identifier.
alter index ZZAP_selected_invoices_n1 rebuild online;
And then once the job completes...
alter index ZZAP_selected_invoices_n1 unusable;
can you use alter table command to disable/enable constraints in a form if you can how, if you cant why
View 4 Replies View Relatedi need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA
BEGIN
update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old;
END;
/
Meta table contains Table name and column name..i attached the table data in atext file
How to reduce the space usage in RMAN commands.
View 3 Replies View RelatedWhat is the difference between alter session and alter system?
View 2 Replies View RelatedI want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
Just now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I use Oracle 10.
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
view the below select statement..why it's adding extra zero's...
select to_timestamp('2001-05-22 12:00:18.600','YYYY-MM-DD HH:MI:SS.ff3AM') from dual
output: 5/22/2001 12:00:18.600000000 PM ---why it's adding extra zeors's
my output should be as " 5/22/2001 12:00:18.600 PM"
I need to copy records from a working table to a history table. I have the following sql statement
insert into test.history
(equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status)
select (equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status
from test.info_AP
Table test.history has one more field in it called test_year. I need to fill this field when I do the insert. Can't use an after update trigger as the field is currently set to not allow nulls.
Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.
SQL> SELECT count(*)
2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR
3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER;
COUNT(*)
----------
2243
[code]...
Why cant oracle give an extra parameter for summary function like MAX and MIN...For example
select max(salary,2)
from empWill give the second max salary.
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
i am trying to read the file, for which i m using the following:
l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');
utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);
it gets these three characters at the start of the line
l_hdr_evnt = 
So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
View 3 Replies View RelatedI am modifying a stored proc that i wrote a couple of years ago. The change is that i want certain inputs to accept strings of characters (comma delimited) so they can be parsed and processed in the proc. The problem is, when i pass very little data (about 50 characters) It fails saying the character sting buffer is to small.
I have setup the input as a type of a column that is a clob and i have also tried using just vahrchar2 (with no size set).
I would like to create a procedure in oracle to remove extra spaces coming as blank spaces(squared ones)in strings.
View 32 Replies View RelatedI've a query that sum up data from one table. But I've to convert them into rows.
Summary query result.
COL1 COL2 COL3 COL4
1000 2000 3000 4000
Test Table creation script.
CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000
A2 2000
A3 3000
A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
All the reports are skipping one extra page after printing of last page irrespective of number of pages in the report. if same report file is generated and given to printer using dos or linex dosen't skips extra page at end. It happens only if i print the report through ORARRP. The report file extension is '.rrpt'.
View 2 Replies View RelatedI have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.
Attached File(s)
1.jpeg ( 1.8MB )
Number of downloads: 1
2.jpeg ( 1.6MB )
Number of downloads: 1
3.jpeg ( 1.52MB )
Number of downloads: 0
is there any way to alter only max value in sequence without specifying the max value.
i know we can alter it like :
-- Alter sequence
alter sequence TEST_SEQ
maxvalue 99999999;
can we alter it without providing the max value and let oracle choose default value for the same, same as we can do it when creating a new sequence.
I have a table Employee which gets updated and Inserted very frequently. Wanted to increase the PCTFREE value for this table
select table_name,pct_free,chain_cnt from user_tables
where table_name='EMPLOYEE'
EMPLOYEE NULL 0
is the output. I altered the pctfree like below
ALTER TABLE EMPLOYEE PCTFREE 30
and I can see Table altered but the value is not reflecting in above command again. Where else I should alter the value and cross check that value is changed.
I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.
CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.
CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.
CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?