While i am inserting data into a specific table that table assigns empty spaces.
For example:
CREATE TABLE DEALER_MST
("DEALER_CODE" CHAR(10) NOT NULL ENABLE,
"REGION_CODE" CHAR(3),
"SUB_REGION_CODE" CHAR(3),
"DEALER_NAME" VARCHAR2(80),
[Code]....
But it is giving result as DS with 8 spaces. It assigns spaces in db automatically while fetching.
During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again
The Grid infrastructure was deinstall one time and still the same issue.
I 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?
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work):
select
(case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product
from tbl_product
where productid= '123';
If one record exists it should produce: 'My Widget' (or whatever)
If zero records exist it should produce: 'N/A'
While I was exporting ( schema level ) ,I am getting following errors :
vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
'Parfile' contents :
owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log
What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace
I used a dynamic item_list. This list should contain only the result of the query but I found an empty element in the list. So, how to eliminate this element empty list?
see the picture:
[URL]........
there this is my insert loop but the problem is there is row added more than the normal count ..
DECLARE
var_record_count NUMBER;
var_p_request NUMBER;
[Code]....
There is one table with data in ORCL1 database.I have created the table using create table statement in ORCL2 database.Now i want to insert only the data into table.
I know one method ... drop the table i created using drop table statement and then create the table with data using export/import.
Is there any other way we can load data into empty table?
I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.
I need a query that should return the column which is empty but its next and previous column is not empty.
How can i check if the result of a defined cursor is empty?
View 6 Replies View RelatedMy requirement is that under table name like A I have to update remaining empty columns as A and under B remaining null columns as B and so on.
View 14 Replies View RelatedMy db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.
View 2 Replies View Relatedwhat empty blocks are, and how to remove them.What I'd like to do is not have empty blocks in the first place on loading a table. I load a lot of "static" tables and would like to not have any wasted space at the end, with minimal shinanigans.
I've set pctfree 0
I"ve set initial to close to the end table size
I've set next to 1M
I've set pctincrease 0
blocksize is 8k
Yet I still need to at least do an alter table deallocate unused
i have project for hotel , i can't solve query to search empty room between date . i have table for room , when room record under reservation between date . then i want to found empty room between date, which is query to make do.
Table Room
Room_No
Type_Room
From_Date
To_Date
Status
Now i need to found empty room between date search room empty between
11/03/2009 - 13/03/2009
select Room_No from Room where From_Date Not between '11/03/2009' and '13/03/2009' i found hard to query.
I have text file with data in UNIX. I want to clear only records(make empty the file) using UTL_FILE..
I know we can remove the text file from UNIX and create file again with same name...
Is there any other way??
I am select list of ids;
cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;
now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.
①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID
---------
57082
②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK BLOCKS
------------- --------
683 8
③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER
----------------
684
④SQL> SHUTDOWN IMMEDIATE;
⑤SQL> STARTUP;
⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found
⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1
⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK# CLASS#
------- ----------
686 1
684 1
687 1
685 1
688 1
683 4
⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3
QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?
QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).
When I try to run the form by clicking Program/Run form, it opens a webpage(explorer) and displays ?ORACLE FORMS? label, but does not show the form only blank screen appear. When I try to run the form by clicking Program/Run form, it opens a webpage(Firefox), but does not show the form only blank screen appear.
I have noticed that in Start OC4J Instance it has some thing like as below:
SYSDATE TIME FormsServlet init():
configFileName: E:DevSuiteHome_2/forms/server/formsweb.cfg
testMode: false
pop-ups is not blocked in my browser I have allow active content to run in files on your computer.directory name (where the form is stored) also doesn't contain any spaces.
Also I have change the
baseHTMLjinitiator=basejini.htm
to this
baseHTMLjinitiator=basejpi.htm.
I have done all the things mention in this forum to overcome this error.But, still the same problem is there.Also please take a note that I am using JRE not JINITIATOR. Beacuse when I am using the JINITIATOR it give me the problem of browser crash in both IE and firefox.Also I am using Windows XP and oracle form 10g.
I was wondering can i check if a text item in another block is empty when a user try to create a record using a button in another block.
/*creating a program that will check to see if the customer id in the customer block is null it will then display the the stop alert and tell the user that he/she cant create a new order without having a selected a customer*/
--this is the block_item im checking to see if it null
IF (:customer.c_id) = NULL THEN
DISPLAY_ORD_STOP_ALERT;
GO_ITEM('CUSTOMER.C_ID');
[code]....
I need to send email notifications with attachments. Below is my testing codes.
declare
text varchar2(32000);
crlf VARCHAR2(2) := chr(13)||chr(10);
begin
text := 'Test only'||crlf||'test 2'||crlf||'test 3';
UTL_MAIL.SEND_ATTACH_VARCHAR2(
[code]......
I notice that, there is an empty line at the top of the content of this file. I don't want the empty line to be included in my file.
db 10.2.0.4
AIX 5.2
I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.
SQL> drop tablespace ret_index including contents;
drop tablespace ret_index including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter tablespace ret_index drop datafile '/path/index3.dbf';
alter tablespace ret_index drop datafile '/path/ret_index3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
try to resize
SQL> alter database datafile 4 resize 100m;
alter database datafile 4 resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Tablespace size is 280gb
how to get rid of the first EMPTY line generated by SQL*Plus when spool'ing to a file.
I tried using set pagesize 0 and while that get rid of the empty blank line, it also disabled the heading ...
Currently using sed as a work around. But unfortunately, can't use sed on Windows?
set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off
[code].......
As mentioned, using pagesize 0 remove the blank line but I also lost the heading.
I have successfully cleaned up our test environment but am having problems with the development environment.
We have a master table, a single materialized view log on that table, and 85 materialized views based on them.
Following is the output from my 'refresh_all_mviews' and query on 'deferror':08:36:04 SQL> SET serverout ON
DECLARE
v_failures NUMBER(12) := 0;
begin
dbms_mview.refresh_all_mviews(v_failures,'F','', refresh_after_errors => true, atomic_refresh => false);
dbms_output.put_line('Failures='||v_failures);
END;
/
08:36:33 SQL> 08:36:33 2 08:36:33 3 08:36:33 4 08:36:33 5 08:36:33 6 08:36:33 7 08
:36:33 8
Failures=5
PL/SQL procedure successfully completed.
08:36:47 SQL> 08:36:47 SQL> select * from deferror;
no rows selected
08:36:56 SQL>
why no rows are being selected? Do I have to 'set' something 'on'?
I am getting an empty line after every 988 records in the output. There are no pagebreaks defined in the layout.
This is a "|" delimited report.
Is there a way to get around the "cannot insert NULL" error when inserting a row with an empty string.
For example:
create table t (
col1 varchar2( 8 ),
col2 varchar2( 8 ),
...,
PRIMARY KEY (col1, col2)
);
insert into t (col1, col2, ...) values ('Hi', '', ...);
I want to know how can i create an empty database in oracle 10g and what is the meaning of empty database. basically i want to perform migration from one database to another database by using exp and imp.
How to create empty database and migrate the database one platform to another platform by using the exp and imp. I have oracle 10g (10.2.0.1.0) on both xp and linux.
Inherited a report created in 6i (works fine) and learned it doesn't correctly display in 10g! (Example attached) Cell frames do not print when there's no info to display, leaving blank gaps in the report. Is this a bug or something in Reports 10g that's causing the problem?
View 7 Replies View RelatedOracle 11G...After creating a new datafile, it was accidentally deleted at OS level (AIX).Because it was new there are no backups of it.Now tablespace thinks there are 3 datafiles in it, but one of them is missing.
Is it possible to recreate an empty data file on-the-fly?It has let me set the datafile to offline, but wouldnt let me do anything else.
Now my backups are failing as it recognises :"the status of database file is RECOVER".