1)What happen when SGA_MAX_SIZE & SGA_TARGET not define under Init parameter file?
2)When extending datafile with Auto extend on command without specifying size what will happnen?
1) We use defaults where possible. I did this on a 6G RAM (virtual) machine
[oracle@vbgeneric ~]$ cat /u01/app/oracle/product/12.2/db_2/dbs/initnever.ora
db_name = never
[oracle@vbgeneric ~]$ export ORACLE_SID=never
[oracle@vbgeneric ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 3 08:38:40 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> startup nomount
ORACLE instance started.
Total System Global Area 243269632 bytes
Fixed Size 8619256 bytes
Variable Size 180357896 bytes
Database Buffers 50331648 bytes
Redo Buffers 3960832 bytes
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 232M
sga_min_size big integer 0
sga_target big integer 0
unified_audit_sga_queue_size integer 1048576
SQL> show parameter memor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
inmemory_adg_enabled boolean TRUE
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL
memory_max_target big integer 0
memory_target big integer 0
optimizer_inmemory_aware boolean TRUE
shared_memory_address integer 0
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 8808038
shared_pool_size big integer 168M
shared_server_sessions integer
shared_servers integer 0
SQL> show parameter db_cach
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 48M
</code>
2) We can do a simple test. I have a table called T that uses *most* of the space in a tablespace, then create a new table T1 and slowly add rows to it.
SQL> create table t ( x varchar2(1000)) tablespace blah;
Table created.
SQL> insert into t select rpad('x',1000,'x') from dual connect by level <= 7800;
7800 rows created.
SQL> commit;
Commit complete.
SQL> select bytes from user_segments where segment_name = 'T';
BYTES
----------
9437184
1 row selected.
SQL> select bytes from v$datafile where name like '%BLAH%';
BYTES
----------
10485760
1 row selected.
SQL>
SQL> create table t1 ( x varchar2(1000)) tablespace blah;
Table created.
SQL>
SQL> set serverout on
SQL> declare
2 x int;
3 begin
4 loop
5 insert into t1 values ( rpad('x',1000,'x') ) ;
6 commit;
7 select bytes into x from v$datafile where name like '%BLAH%';
8 if x > 10485760 then
9 dbms_output.put_line(x);
10 exit;
11 end if;
12 end loop;
13 end;
14 /
10551296
PL/SQL procedure successfully completed.
You can see that we grow only just enough, ie, to accommodate the next extent.