Modern Oracle DBA

Connect the instance using User process and server process Once Basic validation completed SQL statement ready to execute Syntax Check Semantics check Shared pool check Syntax Check: The database validates the sql query syntax is correct or not. Example: SQL> select from dba_objects; select from dba_objects        * ERROR at line 1: ORA-00936: missing expression SQL> Semantics check Oracle Verify all column and table names using the dictionary, and confirm that you have permission to see the data. Shared pool Check Once syntax and semantics check done the server processer will initiate the sql query. Server process will check the library cache In the Library cache server process will check Most recently user to the least recently used algorithm for match the sql statement. If library cache matches the sql statements that’s called soft parsing If there is no match, the server process must continue with the creation of the execution plan hard p

Bigfile tablespce: A bigfile tablespace is made up of a single data or temporary file that can be up to 128 TB in size. Maximum Datafile size for BIG File normal table space DB Block Size Maximum Data file Size 2k 4194303 *2k Block Size=8 TB 4k 4194303 *4k Block Size=16 TB 8k 4194303 *8k Block Size=32 TB 16k 4194303 *16k Block Size=64 TB 32k 4194303 *22k Block Size=128 TB Creating bigfile tablespace  SQL> create bigfile tablespace sanjeev datafile '/u01/app/oracle/oradata/TESTDB/datafile/sanjeev01.dbf' size 10g; Tablespace created. Altering BigFile tabalespace  SQL> alter tablespace sanjeev resize 15g; Drop BigFile tablespace SQL> DROP TABLESPACE SANJEEV INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. SQL> 

 Create Pfile/Spfile from memory new fecture from 11gR1 SYNTAX:- SQL> create pfile from memory; Reference: http://sanjeevvellaisamydba.blogspot.com/2019/01/oracle-pfile-spfile-overview.html Why does create PFILE|SPFILE from MEMORY output differ from that of SPFILE|PFILE? (Doc ID 784133.1)