No question at this time
DBA Top 10
1 M. Cadot 11200
2 A. Kavsek 6000
2 B. Vroman 6000
4 P. Wisse 3400
5 J. Schnackenberg 3200
6 J. Péran 2000
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
8 D. Dave 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48559
Total active users1398
Act. users last 24h3
Act. users last hour0
Registered user hits last week55
Registered user hits last month810
Go up

Do you have a trick for me ;-)
Next thread: sqlplus hangs after entering username/pwd
Prev thread: dba_audit_session view in pluggable database

Message Score Author Date
Hi everyone, I wish that everyone is ok and ...... Joël Péran Sep 18, 2020, 08:26
Hello Joël, thanks for your wishes. I hope you ...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Sep 18, 2020, 09:21
I once had to think about a way to do this, too. ...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Jan Schnackenberg Sep 18, 2020, 14:00
Hi Joël, thank you for a good wishes, likewise!...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek Sep 18, 2020, 14:13

Follow up by mail Click here


Subject: Do you have a trick for me ;-)
Author: Joël Péran, France
Date: Sep 18, 2020, 08:26, 67 days ago
Os info: Windows 2012 R2 EE
Oracle info: Oracle 12.1.0.2 or 19.9
Message: Hi everyone,

I wish that everyone is ok and that your families are safe too !
Some of our databases were initially created with a 4K db_block_size instead of 8K. We have OLTP applications so this is not a big problem, though we would like to change this. I know that the only way, theorically :), is to create a new 8k db_block_size database and to import the original datas in it. But, I was wondering, with the multitenant option and so on, would it be possible to create a 8k container data and move the 4K database to a pluggable one ? What would YOU do with this case ?
I am actually in a studying phase of a 19c migration project (but without multitenant, this would be another project), so I was wondering ...
Thanks for your replies,

Best Regards,
Joel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Do you have a trick for me ;-)
Author: Bruno Vroman, Belgium
Date: Sep 18, 2020, 09:21, 67 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hello Joël,

thanks for your wishes. I hope you are OK too (in your "red zone" if I'm right... ;-)
If you plug a DB using 4K blocks on a container using 8K blocks, what is the gain? All your user data is still in 4K blocks... But this reminds the idea that a DB can use multiple block sizes: each tablespace can have its block size.
So here is my idea if you cannot do a full "export from 4K and import in a new 8K": create new tablespaces (one by one) using blocks of 8K and move data from old to new (move tables, rebuild indexes, maybe "dbms_redefinition").
Then you will end up with all the "user data" in 8K blocks.

(Still the easiest is probably, if you can afford the downtime, to create a brand new database (with tablespaces precreated) and to use datapump to move data from old to new...)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Do you have a trick for me ;-)
Author: Jan Schnackenberg, Germany
Date: Sep 18, 2020, 14:00, 67 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: I once had to think about a way to do this, too.

Basically, as long as you do NOT have partitioned tables, then you can do this:

* create a new 8k DB
* create a buffer cache for 4k Blocks
* Move the tablespaces there with Transportable Tablespaces
* Then do Brunos thing with moving the tables one-by-one to new 8k block-size tablespaces

At the end, drop the empty tablespaces and remove the 4k buffer cache.

There were some restrictions (mainly, if I remember correctly, the aforementioned partitioning thing, because one table cannot have multiple blocksizes) but basically this is the way to do it.

Should work without dbms_redefinition.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Do you have a trick for me ;-)
Author: Ales Kavsek, Slovenia
Date: Sep 18, 2020, 14:13, 67 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Joël,

thank you for a good wishes, likewise!

If you're wondering if you can plug in 4K DB in 8KB CDB then the answer is yes, of course you'll need to specify 4K cache as Mike described in his blog post...

https://mikedietrichde.com/2018/05/08/different-block-sizes-when-you-plugin/

...but if you really want to hear our opinion, then personally I would definitely do everything I can do to avoid situation of having PDB with 4K block size.
From Oracle >=12c you can use DBMS_REDEFINITION.REDEF_TABLE to move table (indexes as well) to another tablespace on-line if your concern is uptime, but the process itself can get tedious as hell (-> error prone) if you have a decent amount of tables.
Imho, datapump is perhaps the most dba-friendly approach if you can "treat yourself" with some downtime and decent lager at hand ;-).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here