Re: Default PCTFREE

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Thu, 14 Sep 2023 15:19:56 +0100
Message-ID: <CALe4HpmQ3-_tj4gUU-gcrVtsXy2ViRfZ56AviiygoF_p5x3c3g_at_mail.gmail.com>

Mladen,

You can do that with a combination of a lockdown profile and a couple of undocumented parameters.
Here is a short demo:
https://gist.github.com/mvelikikh/2f33f3e257bdfbe359e72659a7b5bff2

set echo on
conn / as sysdba
select banner_full from v$version;
drop lockdown profile prf;
create lockdown profile prf;
alter session set container=pdb;

*alter system set pdb_lockdown=prf -                 "_default_pct_free"=42
-                 "_pdb_lockdown_ddl_clauses"=1;*

drop user tc cascade;
grant alter session, create session, create table to tc identified by tc; alter user tc quota 100m on users;
conn tc/tc_at_server/pdb
create table compress_(x int) compress;
create table default_(x int);
create table explicit(x int) pctfree 50; alter session set "_default_pct_free"=43; create table session_(x int);
col table_name for a12
select table_name, pct_free from tabs order by 2;

*SQL> select table_name, pct_free from tabs order by 2;*

*TABLE_NAME PCT_FREE*
*------------ ----------*
*COMPRESS_ 42*
*DEFAULT_ 42*
*EXPLICIT 42*
*SESSION_ 43 *

On Thu, 14 Sept 2023 at 00:39, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> I am looking for a way of changing default PCTFREE for a tablespace or, if
> that is not possible, for the entire DB. My employer is a bank, very heavy
> on the OLTP side, and developers are creating objects without specifying
> PCTFREE. Normally, that can be cured by a baseball bat, but I am looking
> for more elegant and less violent solutions. I am unable to find anything
> to change the default PCTFREE for a tablespace or for a database. Any
> suggestion will be appreciated.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 14 2023 - 16:19:56 CEST