In this schema I only have “regular” tables and indexes. not in system tablespace, as I want to limit its size strictly.ĪLTER USER system DEFAULT TABLESPACE users ĭbms_output.put_line('Changing: "' || i.owner || '"."' || i.table_name || '"') ĮXECUTE IMMEDIATE 'ALTER TABLE "' || i.owner || '"."' || i.table_name || '" PCTFREE 0 ROW STORE COMPRESS ADVANCED' ĭbms_output.put_line('Changing: "' || i.owner || '"."' || i.index_name || '"') ĮXECUTE IMMEDIATE 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" REBUILD PCTFREE 0 COMPRESS ADVANCED' sometimes I use user system for data pump, let us create its master tables UNDO TABLESPACE undotbs DATAFILE '/u01/oradata/XE18/undotbs01XE18.dbf' SIZE 1G I like undo tablespaces with fixed size datafiles without autoextend TEMP and UNDO do not count, so make them as huge as you need themĭEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/XE18/temp01XE18.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED this tablespace is for our real user dataĭEFAULT TABLESPACE users DATAFILE '/u01/oradata/XE18/users01XE18.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX DATAFILE '/u01/oradata/XE18/sysaux01XE18.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M MAXSIZE 400M later on you should monitor the growth of the tablespaces well and find out how you can stop it from further growingĭATAFILE '/u01/oradata/XE18/system01XE18.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M MAXSIZE 450M 450M for SYSTEM and 400M for SYSAUX should be enough as a start I am too stingy, so restrict autoextend for SYSTEM and SYSAUX probably we could also experiment with other character sets here, but I like to have databases in unicode yes we can and should multiplex our control and redo log files is quite minimal.Ĭreate an oratab entry in /etc/oratab: XE18:/opt/oracle/product/18c/dbhomeXE:Y, then create a password file and shared parameter file: The other parameters are Trivadis best practice (TBP). Typically I set audit_trail=db,extended, but here I want to save space, so I set it to none. Log_archive_dest_1='location="USE_DB_RECOVERY_FILE_DEST"','valid_for=(ONLINE_LOGFILES,ALL_ROLES)' It stopped when creating index for primary key of orders table as it could not be created any more.Īudit_file_dest="/u00/app/oracle/admin/XE18/adump"Ĭontrol_files='/u01/oradata/XE18/ctl1XE18.dbf','/u02/oradata/XE18/ctl2XE18.dbf'ĭb_recovery_file_dest='/u02/fast_recovery_area' Actually all the data was loaded but the indexes where missing. At my lab it took a while until the error ORA-12954 was raised. ![]() Start data generator, then file –> open –> soe.xml to choose the popular schema with the customers, orders and order_items, thenĬlick tools –> sizing wizard and try to insert 15,1 GB of data which is much more than allowed, click finish.Ĭlick run –> start data generation to database, provide db credentials and possibly increase jdbc batch size, then click next. This is how I configure test data generator to load data until full: GRANT CREATE TABLE, CREATE SESSION, ALTER SESSION TO testdata Īnd now let us see how much data we can load until I get the famous error: ORA-12954: The request exceeds the maximum allowed database size of 12 GB. ![]() ĭominic is also the author of the great swingbench tool, but here I make use of his test data generator.ĬREATE USER testdata IDENTIFIED BY testdata ĪLTER USER testdata QUOTA UNLIMITED ON users I use Dominic Giles’ data generator tool for this, which can be downloaded from. We waste space for probably unneeded components.Īnyway, now I go on with it and load data. So having OLAP, Multimedia and so on in this CDB multiple times seems not to be that lightweight any more. This PDB is excluded from the most CDB_-views by default and so missing in the output above. Actually it is also there a third time – in PDB$SEED. ![]() The registry components are visible here twice as it is loaded into CDB$ROOT and PDB XEPDB1.
0 Comments
Leave a Reply. |