Server Administration :: Allocating Extent To A Clob Column Of A Table

Dec 30, 2010

I have a table with two clob columns and need to manually allocate space to the table and to its lob segment. Is the following command correct?

--to allocate extent to the table
alter table emp allocate extent;
--the table has columns named col1 and col2 which are clob
--to allocate extents to the columns
alter table emp modify lob (col1) (allocate extent (size 10m))
/
alter table emp modify lob (col2) (allocate extent (size 10m))
/

View 3 Replies


ADVERTISEMENT

Server Administration :: Calculation Of Initial Extent Size Of Table

Apr 21, 2010

I need to create table A. which will going have more than 8L records. Daily this table A will truncate and reinsert all 8L records. Also number of records(8L) will we increase 50K per month. what should be storage clause parameters . Mainly initial and next extent.

View 3 Replies View Related

Server Administration :: Get Size Of CLOB Column But Not Getting Any Output

Sep 10, 2012

I'm trying to get size of CLOB column but not getting any output.

SQL> desc TABLE_STEP_INST234
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_PENDING_PREREQS NOT NULL NUMBER(10)
OBJID NOT NULL VARCHAR2(31)
OUTFLOW_BITS NUMBER(19)
PARAMS CLOB
PARENT2PROC_INST NOT NULL VARCHAR2(31)
ROOT2PROC_INST NOT NULL VARCHAR2(31)
START_TIME DATE
STATUS NOT NULL NUMBER(2)
[code]...

View 2 Replies View Related

Server Administration :: How To Get Row ID In Every Extent

Jun 9, 2012

How to get the row ids of the first and last row in every extent by dba_extents?

View 13 Replies View Related

Server Administration :: Unable To Extend Extent?

Sep 16, 2012

When ever error occurred as "Unable to extend extent", we do add either datafiles or increase the size (autoextend on).But in a interview, i was asked to handle the error without increasing size/adding new datafiles.

how can i handle this error without increasing size?

View 2 Replies View Related

Server Administration :: Tablespace Is Filled / Unable To Extent

Aug 8, 2013

There was a tablespace of size 100MB.That Tablespace is filled and hence throwing an error like "unable to extent". But verified that there is around 20MB freespace present in the tablespace.what would be the reason behind this error?

View 2 Replies View Related

Server Administration :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace?

Aug 11, 2011

I have executed the below statement to move a bulk of table from source table space-SHOP3_DATA to destination tablespace -LINUX_CATA. Before executing the statement the source table space was having 85% full message . While executing the statement this is giving the error for the source tablespace means the the tablespace is getting consumed in the source TS. Currently no-one is using the tablespace SHOP3_DATA' . If I add datafile to the SHOP3_DATA the problem may be solved but why the space is getting consumed in the source. Now the tablespace SHOP3_DATA is 95% full

SQL> alter table LINUX_CATA.TST_FOLDERS move tablespace LINUX_CATA;
Table altered.
SQL> alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA;
alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA

[code]....

View 10 Replies View Related

SQL & PL/SQL :: How To Update CLOB Column In A Particular Table

Feb 21, 2011

How to update a CLOB column in a table, suppose the string is greater than 4000 size.

View 7 Replies View Related

How Oracle Determine Initial Extent And Max Extent Size

Aug 10, 2012

Suppose tablespace allocation_type is system then how oracle determines the initial extent and max extent size?

View 4 Replies View Related

Server Administration :: How To Drop A Column In Huge Table

Aug 8, 2011

I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.

Is there any quickly way to drop a column in a huge table?

View 5 Replies View Related

Server Administration :: Column Limit Same For All Types Of Table

Aug 10, 2010

Question 1: What is the no of column limits in a single table? Is it same for all type of tables.
Question 2: How to estimate the size for a table in advance with "undo generation amount, index size, redo log size"?
Question 3: What is the real time scenario for creating a table on another schema?
Question 4:I read the below line in a document, but I'm not able to understand it

"we can't move types and extent tables to a different schema when the original data still exists in the database"?

View 6 Replies View Related

Convert Existing Table With Varchar Column To A Clob?

May 8, 2013

how to convert existing table with varchar column to a clob

View 1 Replies View Related

Server Utilities :: Sqlldr Loading Into A Clob Column

Oct 26, 2011

I am doing a simple test and need to populate a small table with some data.

My table looks like this:

SQL> desc clob_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(20)
C1 CLOB

I have a pipe delimited data file ID range 1-50000 and random characters with a length of 100-4000 bytes for the clob field.

My control file looks like this but I am guessing it is wrong:

LOAD DATA
INFILE 'clob_test.dat'
INTO TABLE CLOB_TEST
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ID INTEGER EXTERNAL NULLIF (ID=BLANKS)
, C1
)

Let me know what I need to do to my .ctl file in order to load this data?

View 4 Replies View Related

Server Administration :: Reorganization Includes A Table With A LONG Column

Jan 18, 2012

I am trying to reoarganise a tablespace with Enterprise Manager from manual to automatic, but script generation gives the following warnng :

Quote:Reorganization includes a table with a LONG column. To support reorganization of tables with LONG columns that are greater than 32Kbytes the external procedure MGMT$REORG_MOVELONGCOMMAND must be configured properly. It has been determined this external procedure is not currently configured as expected. Configure SQL*Net appropriately to allow it to call the external procedure service process.

I made changes suggested by [URL] ..... in the section Using Reorganize Objects with LONG Columns but the warning persists. I noticed however that $ORACLE_HOME/lib/libnmuc.so is an empty file (0 bytes).

Oracle Database 10g 10.1.0.2.0
Solaris (SunOS 5.9 Generic_117171-07 (64-bit))
Oracle home /data/lun1/oracle/product/10.1.0/db_1
{ORACLE_HOME}/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /usr/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
[code]....

View 1 Replies View Related

Server Utilities :: Loading CLOB Data Into Table

Aug 21, 2012

How to load the CLOB data into table..in the attached file 18 column has clob data it's appear like new line..Using external table how to load. i tried it's not working..

View 12 Replies View Related

Temp Tablespace Not De-allocating

Oct 15, 2010

We all know that Temp segments in Temporary tablespace don't deallocate even after the process completes, the extents are only marked for reuse.

After an instance restart, it should de-allocate, or what is the default behavior?

Doesn't SMON de-allocate temporary segments here during instance startup?

If it doesn't de-allocate space then it will run out of space, the next time a huge sort operation takes place.

So my next question is how to estimate the correct space of the temp tablespace in the physical design phase.?

CODECREATE TEMPORARY TABLESPACE temp02
   TEMPFILE ' /u01/app/oracle/oradata/ora11gR2/temp02.DBF' SIZE 100M
   AUTOEXTEND ON NEXT 2M MAXSIZE 500M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CODESQL> SELECT * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE    FREE_SPACE
------------------------------ ---------------             ---------------                   ----------
TEMP                      30408704          30408704               29360128 – Default for system
TEMP02                  104857600           1048576                     103809024

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME          FILE_ID   BYTES_USED                  BYTES_FREE
------------------------------ ----------   ----------                             ----------
TEMP                            1             30408704                       0
TEMP02                    2            1048576              103809024

    Created a user ‘test’ and assigned TEMP02 as its temporary tablespace.

After running sort (a table containing 20 Million records from User test (session 1)

Sys> SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------         ---------------             ---------------        ----------
TEMP02                     104857600          35651584     69206016
TEMP                               30408704          30408704     29360128

After Running sort from User test (session 2) but same table

Sys> SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP02                     104857600          70254592     34603008
TEMP                      30408704          30408704     29360128

    Free sort space is reduced and more extents allocated for the sort segment

Sys>
select username, tablespace,contents, segtype, extents, blocks from v$sort_usage;

USERNAME               TABLESPACE               CONTENTS  SEGTYPE      EXTENTS      BLOCKS
------------------------------ ------------------------------- --------- ---------            ---------- ----------
TEST                   TEMP02                  TEMPORARY SORT             33    4224

SQL> select tablespace_name, current_users, total_extents, used_extents, free_extents, added_extents, max_used_size
  2  from v$sort_segment;

TABLESPACE_NAME         CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS MAX_USED_SIZE
------------------------------- ------------- ------------- ------------ ------------ ------------- -------------
TEMP                        0         28           0       28          0        1
TEMP02                    1         66          33       33            66            66

After the sort completes;

Sys> SQL> SELECT * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                                           TABLESPACE_SIZE         ALLOCATED_SPACE        FREE_SPACE
------------------------------                                                ---------------                    ---------------                 ----------
TEMP02                     104857600          70254592    103809024
TEMP                               30408704          30408704     29360128

Free space shows the Temp02 extents are free for re-use by another process Extents not de-allocated

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME             FILE_ID BYTES_USED    BYTES_FREE
------------------------------        ---------- ---------- ----------
TEMP                                   1   30408704          0
TEMP02                           2   70254592       34603008

    Actual space (bytes) free in Temp02

    Does it mean that 34603008 is actually free and 103809024 is marked free

select username, tablespace,contents, segtype, extents, blocks from v$sort_usage;

    No records
    Agreed here

SQL> select tablespace_name, current_users, total_extents, used_extents, free_extents, added_extents, max_used_size from v$sort_segment;

TABLESPACE_NAME         CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS MAX_USED_SIZE
------------------------------- ------------- ------------- ------------ ------------ ------------- -------------
TEMP                        0         28           0       28          0        1
TEMP02                        0         66           0       66    66           66

After a startup of instance;

SQL> SELECT * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                      30408704          30408704     29360128
TEMP02                  104857600          70254592     34603008

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME           FILE_ID BYTES_USED BYTES_FREE
------------------------------        ---------- ---------- ----------
TEMP                           1   30408704          0
TEMP02                       2   70254592   34603008

Why is the sort segments not de-allocated after instance startup??

View 9 Replies View Related

Partitioned Table - What Extent Size To Be Set

Sep 28, 2012

I have a partitioned table (one partition per month). Every month there are added about 1GB data. What extent size should I set? 1GB will be ok?

What if data will be greater than 1GB, adding new 1GB extent takes probably a lot of time and clients may see delays while they're inserting in this time? (it's OLTP system)

When new extent is allocated? Exact in time of lacking space in existing extent or before? Partitions are dropped after one year so free space isn't a problem.

View 6 Replies View Related

Estimate Next Extent Size For Very Large Table?

May 13, 2011

How to estimate next extent size for very large table? What should I take into account? Is there any formula for that?

View 4 Replies View Related

Performance Tuning :: Initial Extent For Table?

Mar 19, 2012

1.2 million chained rows, 1.7 million blocks, etc. Initial extent for this table is 64k and next 1 mb. I would try to calculate this out better for efficiency and performance. This will not be efficient as it stands. calculate the size.

View 14 Replies View Related

How To Make System Table Space Extent Management Dictionary

Dec 21, 2010

I want to create system table space's extent management dictionary with the syntax:

CODEcreate database
logfile
group 1 ('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2 ('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3 ('/u01/app/oradata/anand/redo3a.log') size 100M
datafile '/u01/app/oradata/anand/system.dbf' size 400M extent management dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf' size 300M
default temporary tablespace temp tempfile '/u01/app/oradata/anand/temp.dbf' size 50M

but it is giving error
ERROR at line 6:
ORA-25141: invalid EXTENT MANAGEMENT clause

how can I make system tablespace's extent management dictionary?

View 3 Replies View Related

Server Utilities :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace USERS

Apr 4, 2013

while importing data i got this error in my log file.and i cannot import my data successfuly

in my log file error i found like this

ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:

i can import my data using imp utility using this syntax

C:UsersAdministrator>imp tiger/****@tcs file=E:DUMP s.
dmp log=E:DUMP s.log fromuser=tiger121 touser=tiger statistics=none

this my user tiger default tablespace its uses and its a auto extend on and locally managed tablespace,and i have enough space on my drive also.

View 21 Replies View Related

XML DB :: Clob Column To XML

Apr 26, 2013

I have table like-

CREATE TABLE C2X_TEST(NAME VARCHAR2(100),XML_CLOB CLOB,XML_XML XMLType);

here in XML_CLOB column xml files are stored.

Now i want to update XML_XML column with corresponding XML_CLOB value .

View 8 Replies View Related

SQL & PL/SQL :: Insertion In Clob Column?

Jul 3, 2012

i have another problem with clob column , when i try to insert data in it through the stored procedure then it shows an

error- 'ORA-01460: unimplemented or unreasonable conversion requested clob' and this error especially arise when data to e inserted in clob column , have more than 4000 characters.

sql>exec pi_test(1,'sysdate','text of clob column'); ORA-01460: unimplemented or unreasonable conversion requested clob

where is the error.

View 8 Replies View Related

SQL & PL/SQL :: Read CLOB Column?

Dec 19, 2012

I have two tables File_Master and File_Detail
File_Master Primary key FILE_ID
File_Detail Primary key FILE_ID,LINE_ID

I have a CLOB column FILE_CONTENT in table File_Master. For every FILE_ID record in File_Master, several hundreds of lines are stored in the CLOB column.

I want to read this CLOB column 'File_Content' and and break every line (1000 Characters)
piecewise to populate columns of File_Detail.

Since there will be thousands of lines to process, what would be the best approach in writing PL/SQL code for better performance?

View 1 Replies View Related

SQL & PL/SQL :: CLOB Column Append

Feb 13, 2013

I have a requirement when I need to append and show the contents of a CLOB (rich text) column into the Open office report.

The use case is as below.
User(s) can enter the details into a CLOB column.
This data entered by each user need to be concatenated onto another CLOB column which will hold all the history of changes. Mainly i wanted to perform a concatenation of these two columns.

|
| User Entered | Stored |
| Data Stored in Clob Column 1| Data Stored in Clob Column 2 |
| v_clob_1 | v_clob_target |
-------------------------------------------------------------------------------
|"Text 1" and a image |"Text 1" and a image |
-------------------------------------------------------------------------------

Any method to achieve this case. I had tried the following method to achieve this. But no success.

v_merge clob;
v_clob_target clob;
v_clob_1 clob;

1)
DBMS_LOB.APPEND(v_merge, v_clob_target);
DBMS_LOB.APPEND(v_merge, v_clob_1);

2)
DBMS_LOB.APPEND(v_clob_target, v_clob_1);

3)
v_merge:= v_clob_target || ''|| v_clob_1;
v_clob_target := v_merge;

View 6 Replies View Related

SQL & PL/SQL :: Update Clob Column Using Execute Immediate?

Aug 30, 2010

I am encountering an error message while updating a xmltype column using dynamic sql statement. I am using dynamic sql here as the table is not placed in the same schema from where the plsql procedure is invoked. The schema name is passed to the procedure as an argument. I am using below pseudo code for this purpose.

Create procedure myproc(p_schemaname varchar2, p_id number)
is
p_clob clob;
p_str varchar2(2000);
begin

[code]...

This is throwing an error 'missing expression' at the line of 'exeute immediate'.

But it works if I run a static sql update by hard coding the schema name in the statement.

View 3 Replies View Related

SQL & PL/SQL :: Insert Data In Clob Column?

Jun 13, 2012

i have a problem when i try to insert a large character string of nearly 1 lac characters (code of html) in a clob column of my test table, then i get an error "ORA-01704: string literal too long" , i didnot understand that why clob column is not storing this data.

View 1 Replies View Related

PL/SQL :: Finding Length Of CLOB Column

Jul 11, 2012

I had a varchar2 variable which was storing some data and I could use the LENGTH function to get the length of the data. However, If I change it to CLOB. What is the best way to get the length?

v_clob_size:= (DBMS_LOB.getlength(v_clob)) / 1024 / 1024;
DBMS_OUTPUT.put_line('CLOB Size ' || v_clob_size);

another one is
LENGTHB(TO_CHAR(SUBSTR(<clob-column>,1,4000))) But this seems restricted to first 4000 characters only.

View 3 Replies View Related

SQL & PL/SQL :: How To Execute The Statement Exist In CLOB Column

Jun 30, 2010

I have oracle table with a column of datatype CLOB. In the clob column having the manipulation statements (update, insert) as records.

How to execute the statements exists in a clob column as records?

my table structure and one record is

CREATE TABLE ACTN_CFG_T
(
OBJ_ID NUMBER(32) NOT NULL,
ACTN_ID NUMBER(38) NOT NULL,
ACTN_SEQ NUMBER(2) NOT NULL,
RU_DEF CLOB NOT NULL

[code]....

I want to execute the update statement when i call the obj_id. I used the follwing procedure but error.

1 DECLARE
2 V_ABC CLOB;
3 BEGIN
4 SELECT RU_DEF INTO V_ABC FROM ACTN_CFG_T WHERE OBJ_ID = 1625;
5 EXECUTE IMMEDIATE V_ABC;
6 END;

error is...

ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored

View 10 Replies View Related

JDeveloper, Java & XML :: Querying XML In CLOB Column?

Mar 17, 2011

I have a CLOB column called XML_DATA that has (not-surprisingly) xml data in it that's housed inside a table called HMS_XML_TRANSFER. It has been giving me a headache because I'm unable at this point to use the xml field as a condition to get its TRANS_SEQUENCE number. The where clause doesn't work.

SELECT TRANS_SEQUENCE, XML_DATA
FROM HMS_XML_TRANSFER
WHERE EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]');

The only test that I have been able to get working is the one below.

SELECT TRANS_SEQUENCE, EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057/ACTIVITY_CODE')
FROM HMS_XML_TRANSFER
WHERE TRANS_SEQUENCE = '8191602';

It will give me the ACTIVITY_CODE element so I know I can pull data from the XML but I can't do the reverse in the first example which is what I need because I don't know the TRANS_SEQUENCE number, I just know the ACTIVITY_CODE.

I have over 202 error messages logged in Teradata SQL from my many and varied attempts to get this to work using every example I could find online.

As an example this has not worked either...

WHERE EXISTNODE(XML_DATA, '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]') = 1;

So the question... How do I properly form my SQL statement so I can use the XML column's ACTIVITY_CODE element to get the TRANS_SEQUENCE column field? Oh and I'd like to see both columns in the result.

Below is the version of Oracle I'm using, the description of the Table HMS_XML_TRANSFER, and a sample of the XML that comes from XML_DATA. I can't seems to get tabs working.

===============
ORACLE VERSION
===============
SQL*Plus: Release 9.0.1.3.0 - Production on Thu Mar 17 08:18:15 2011

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

=========================
TABLE HMS_XML_TRANSFER
=========================
Name Null? Type
--------------- --------- --------------
TRANS_TYPE NOT NULL VARCHAR2(10)
DATE_IN NOT NULL DATE
DATE_PROCESSED DATE
STATUS VARCHAR2(8)

[code]....

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved