David D., March 26, 2019 - 9:52 am UTC
Thank you very much Connor for your help but you used a column with the datatype sys.xmltype, not PUBLIC.ANYDATA like I did...
Is there a difference between both types?
March 27, 2019 - 5:33 pm UTC
LMAO! What an idiot I am. I misread the question. Let me check around internally and get back to you.
Sorry about that.
==================
Addenda: This time with the right datatype!
The full DDL comes out as:
CREATE TABLE "MCDONAC"."T"
( "ID" NUMBER(*,0),
"DOCUMENT" "SYS"."ANYDATA"
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
OPAQUE TYPE ("DOCUMENT") STORE AS SECUREFILE LOB (
ENABLE STORAGE IN ROW CHUNK 8192
CACHE NOCOMPRESS KEEP_DUPLICATES )
which suggests there is no way to nominate the tablespace for the ANYDATA internal blob.
David D., March 28, 2019 - 9:22 am UTC
Hello Connor,
Thank you for your answer but, well, my question was : how can I move to a new tablespace a table with a ANYDATA/LOB column?
You created your table empty, can you insert datas, commit and try to move the DOCUMENT column to an other tablespace?
Last thing, is there a difference between PUBLIC.ANYDATA (my table) and SYS.ANYDATA (your table)?
Have a nice day,
David D.
March 28, 2019 - 11:42 am UTC
Moving the table will also move the ANYDATA column:
create table t (
c1 int,
c2 anydata
) tablespace users;
insert into t values ( 1, anydata.convertVarchar2( 'test' ) );
commit;
select tablespace_name
from user_lobs
where table_name = 'T'
and column_name = 'C2';
TABLESPACE_NAME
USERS
alter table t move tablespace small_tblsp;
select tablespace_name
from user_lobs
where table_name = 'T'
and column_name = 'C2';
TABLESPACE_NAME
SMALL_TBLSP
I don't think there's a way to move it independently.
The PUBLIC version is a synonym to the SYS object:
select table_owner, table_name
from dba_synonyms
where owner = 'PUBLIC'
and synonym_name = 'ANYDATA';
TABLE_OWNER TABLE_NAME
SYS ANYDATA
David D., March 28, 2019 - 1:41 pm UTC
Ok, finaly I see the light.
You say "I don't think there's a way to move it independently" and I trust you : now, I understand that I cannot move only LOB datas from my column "VALEUR" to a tablespace while the others datas of the SAME column "VALEUR", numbers, dates..., are in another tablespace. There is ONE column, so Oracle cannot split this column in two tablespaces, it is possible only for a table; lob columns in a tablespace and other columns in another tablespace.
One thing, I don't understand why when you insert a varchar2 data in your table, there is a row in user_lobs. You used anydata.convertVarchar2, it is not a lob.
And thank you again, all of you, for your responses.
David D.
March 29, 2019 - 5:05 pm UTC
You can move normal LOB columns (CLOB/BLOB) independently of the table. Just not ANYDATA.
Internally ANYDATA uses LOB storage. You just can't access it like you can with "proper" LOBs.
To David
Rajeshwaran, Jeyabal, March 29, 2019 - 3:19 pm UTC
....
One thing, I don't understand why when you insert a varchar2 data in your table, there is a row in user_lobs. You used anydata.convertVarchar2, it is not a lob.
....the rows in the user_lobs are even if the table is empty. it not related to the number of rows in the underlying table.
even an empty table make an entry into user_lobs.
demo@PDB1> create table t(x int,y anydata);
Table created.
demo@PDB1> select segment_name,tablespace_name
2 from user_lobs
3 where table_name ='T'
4 and column_name ='Y';
SEGMENT_NAME TABLESPACE_NAME
------------------------------ -------------------------
SYS_LOB0000073664C00002$$ TS_DATA
demo@PDB1> select * from t;
no rows selected
demo@PDB1>
"Anydata" datatype is created implicitly as LOB's and we are not constrained to insert any particular values into it.
However SYS.ANYDATA type definition has many static methods to sanitize those input values before inserting into the column defined using ANYDATA datatype. Hence we maintain the data integrity.
demo@PDB1> insert into t(x,y) values(1,sys.anydata.convertnumber(55) );
1 row created.
demo@PDB1> insert into t(x,y) values(2,sys.anydata.convertdate(sysdate) );
1 row created.
demo@PDB1> insert into t(x,y) values(3,sys.anydata.convertvarchar('HelloWorld'));
1 row created.
demo@PDB1> insert into t(x,y) values(4,sys.anydata.convertdate( to_date('30-feb-2019','dd-mon-yyyy') ) );
insert into t(x,y) values(4,sys.anydata.convertdate( to_date('30-feb-2019','dd-mon-yyyy') ) )
*
ERROR at line 1:
ORA-01839: date not valid for month specified
David D., March 30, 2019 - 3:59 pm UTC
Thank you everyone for yours responses.
I discover that, when you create an anydata column, there is a space allocated for a clob, even if the table is empty...
Have a nice day.
David D.