SQL oracle difference concepts architecture In this post, I am just referring similar to the concept to understand better, but it is not exactly same as what I have referred. Oracle SQL Comments System Master SYSAUX Resource Model MSDB temporary TempDB More than one & User can have their own There is no TempDB and it doesn’t need to re-create DESCRIBE sp_help dba_views Identity Sequence Auto generate number DBA_SCHEDULER_JOBS Sys.sysjobs DBMS_SCHEDULER / OEM-Oracle Scheduler SQL Agent SMON Recovery PMON User kill/rollback show parameter sp_configure Pfile & Spfile SQL server property you can change system and session level Data file Data file ARCHIVELOG mode Full mode NOARCHIVELOG mode Simple mode Default…
-
-
Oracle Tablespace-05
Tablespace: A database is divided into one or more logical storage units called tablespaces. All tablespaces, including the SYSTEM tablespace, can be locally managed. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7116328455352 http://www.orafaq.com/node/3. Types: 1.System tablespace 2. Non System tablespace System SysAUX Undo Temporary — A different block size object cannot move between the tablespace. — All the data file name should be created as unique. Since, it might have some issues in cloning a database. TEMPFILEs are not fully initialized (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database…
-
Oracle Database Physical and logical structures-04
Database Physical and logical structures Block Smallest storage unit called a block. Size can be {2k,4k,8k,16k & 32k} – Default 8k. Standard block and Nonstandard block. Default is standard block. Db_block_size =8192. Once it’s configured we cannot change this. Each block has pctused, pctfree & high water mark. PCTused – For insert – 40% PCTfree – For Update – 10% High Water Mark – Separate the used & free space of block Multiple block size (9i onwards) You can configure the size of the block for the database. Db_nk_cache_size = nk Db_16k_cache_size =16k Block utilization parameter: ================ Initran 1 Initial transaction Maxtran 256 – maximum transaction Pctused – inserted…
-
Oracle Architecture-03
Oracle 11g Architecture Oracle – PGA+ Instance + DB files Instance – SGA + BG Instance —SGA and BG SGA — Shared Pool, Database buffer cache, Redo log buffer cache, large pool, Java pool & Streams pool. Shared Pool — Most recently executed statements and definitions. Data Dictionary cache & Library cache Data Dictionary — most recently used definitions, including table, index & privilege. Library cache — most recently used SQL & PL/SQL statements Shared SQL area & Shared PL/SQL area Database buffer pool — It stores a copy of data block. Default, keep & Recycle Redo log buffer pool — it stores all the changes made to the…
-
Oracle DBA Unix commands-02
DBA Unix commands | symbol is for combine and filter the results like we use in Power Shell pipe out. R is for recursive & grep command is used to search text ps -ef |grep pmon – Get the running user process. ps -ef |grep smon – Get the running user process. Disk related commands: # df -h {-m,-k} -- Disk free # du -h /opt -- Disk usage for Particular mount point disk # pwd -present working directory # cd /opt - change directory # ls - list the files and folders -l -long list like a windows properties -lt - long list with time -ltr - long list…