Indexes: http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895 http://www.oracle.com/us/education/my-richardfoote-seminar-079059.html A table dependent object. (Root — Branch –Leaf) An index is an optional structure, associated with a table or table cluster. Index properties: Usability Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment. When we move the table without index it will be unusable.…
-
-
Oracle User Profile Profile (password & session) Role (object & system) User-09
Profile {Profile (password & session), Role (object & system), User} Profile — is for resource limit, we can enforce password & session parameters to the user Role — is for system & object level permission for user Profile It’s a collection of user security resource limit. We can apply password and session parameters. You can enforce a limit on resource utilization using resource limit parameters. Also, you can maintain database security by using password management feature. User accounts can have only a single profile. By default a DEFAULT profile will be assigned for the user. Types: 1. Password management 2. Session management Password management parameters: Default for all…
-
Oracle Startup and shutdown options-08
Startup and shutdown options Startup Options Startup nomount – Read initialization parameter Startup mount – Read Control file Startup / Open Startup force Startup restrict Startup upgrade/migrate Startup nomount — Read initialization parameter Instance will start and parameter file read. For database creation needs to start from here. Control file corruption can recover from here. Any parameter changes from pfile to spfile has to done from here, if the database uses a spfile. Startup mount — Read Control file The followings are possibility Read control file content Data file rename, redo log file rename. Archive log to no archive mode vice vera. Flash back database ON/OFF Startup…
-
Oracle Physical Database files-07
Physical Database files: Password file It’s a physical file. “ORAPWD” is a command utility to create the file. To connect the oracle database as DBA, either by password file or OS group which has SYSDBA access. It assists the DBA with granting SYSDBA and SYSOPER privileges to other users. The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used. Sql> show parameter password SQL> select * from v$pwfile_users; The only time the password file is accessed is if someone logs on ‘as sysdba’. orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret Parameter files It’s…
-
SQL server Oracle difference concepts architecture-06
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…