It relieves the server from having to invoke the function for every key value as it performs a search on the indxed expression.
For example:
CREATE INDEX table1_ix ON TABLE1(my_function(column1, column2));
Now any query that contains the expression my_function(column1, column2) in the WHERE clause may be able to take advantage of this index (otherwise, it would force a full table scan)
There are Restricted ROWIDs and Extended ROWIDs.
Structure of a Restricted ROWID
ROWID ENAME
------------------ ----------
00000DD5.0000.0001 KRISHNAN
As shown, a restricted rowid's VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:
Structure of an Extended ROWID
ROWID LAST_NAME
------------------ ----------
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG
An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:
Optional clauses for the DROP TABLE statement:
Some considerations:
If you do not use the PURGE option, the table definition, associated indexes, and triggers are placed in a recycle bin. That means that the space taken up by the table and its indexes still counts against user's allowed quota for the tablespaces involved. Use PURGE RECYCLEBIN command to empty the recycle bin or FLASHBACK TABLE command to recover schema objects if PURGE clause was not used in DROP TABLE statement
So the order is (case: DML statement, followed by COMMIT):
Non-deferred constraints are enforced at the end of every DML statement and a violation causes the statement to roll back.
Deferred constraints are enforced when a transaction is committed and a violation causes the transaction to roll back.
A constraint that is defined as deferrable can be specified as one of the following:
In adittion to creating VERIFY_FUNCTION, the utlpwdmg script also changes the DEFAULT profile with the following ALTER PROFILE command:
ALTER PROFILE default LIMIT
PASSWORD_LIFE_TIME 60
PASSOWRD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFYH_FUNCTION verify_function;
Control resource consumption
Profiles enable the administrator to control the following system resources:
Profiles also allow a composite limit. Composite limits are based on a weighted combination of CPU/Session, Reads/Session, Connect Time, and Private SGA. Composite limits are discussed in more detail in the Oracle Database Security Guide.
Manage account status and password expiration
Oracle password management is implemented with user profiles. You should set it by clicking on the Password tab in the desired Profile from Enterprise Manager. Profiles can provide many standard security features including the following:
Account locking:
Password aging and expiration:
Password history (mutually exclusive parameters):
Password complexity verification:
There are some predefined roles such as:
Notes:
Functional Roles
Other roles that authorize you to administer special functions are created when that functionality is installed. For example:
The syntax for the NVL function is:
NVL( string1, replace_with )
string1 is the string to test for a null value.
replace_with is the value returned if string1 is null.
But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.
Simply put if the grouping column contains more than one null value, the null values are put into a single group.
For example, the grouped query:SELECT A, B,SUM(amount_purchased) AS 'C'will display a results table similar to
FROM customers
GROUP BY A, B
ORDER BY A, B
| A | B | C |
|---|---|---|
| NULL | NULL | 61438.0000 |
| NULL | 101 | 196156.0000 |
| AZ | NULL | 75815.0000 |
| AZ | 103 | 36958.0000 |
| CA | 101 | 78252.0000 |
| LA | NULL | 181632.0000 |
| A | B | amount_purchased |
|---|---|---|
| NULL | NULL | 45612.00000 |
| NULL | NULL | 15826.00000 |
| NULL | 101 | 45852.0000 |
| NULL | 101 | 74815.0000 |
| NULL | 101 | 75489.0000 |
| AZ | NULL | 75815.0000 |
| AZ | 103 | 36958.0000 |
| CA | 101 | 78252.0000 |
| LA | NULL | 96385.0000 |
| LA | NULL | 85247.0000 |
5-28. ASM: Concepts
You can also start up a database instance in restricted mode so that it is available to users with administrative privileges only. to start an instance in restricted mode, select the "Restrict access to database" option on the Advanced Startup Options page.
Starting an instance includes the following tasks:
$ cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus
$ $JAVA_HOME/bin/java -Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home/config/jazn.security.props -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell
JAZN> add user "iSQL*Plus DBA" username password
JAZN> grantrole webDba "iSQL*Plus DBA" username
Each of these componenets must be explicitly started before you can use the services of the component and must be shut down cleanly when shutting down the server hosting the Oracle database.
Start up order is important. these components must be started in this order:
Dropping a database involves removing its data files, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all the following conditions must apply:
An example of this statement is:
DROP DATABASE;
The DROP DATABASE statement has no effect on archieved log files nor does it have any effect on copies or backups of the database. It is best to use Recovery Manager (RMAN) to delete such files. If the database is on raw disks, then the actual raw disk special files are not deleted.
For details, check Oracle 10g: Administration Workshop I, page 2-19
