How to find out if the temporary tablespace is autoextensible or not

Situation

The user runs into multiple ORA-01555 errors stating that the TEMP tablespace is unable to extend the temp segment by a particular size.

If the database template was used in the creation of the database, this ought to be a very rare occurrence.  The DBT establish the temp tablespace with autoextend on and the ability to grow to a max of "unlimited", which is to say 32G for a single file.

Solution

To determine if the autoextend option has been set for a temp tablespace, log into SQL Plus as SYS / as SYSDBA, and run the following script:

column file_name format a30
SELECT file_name, tablespace_name, autoextensible
  FROM dba_temp_files;

To turn autoextend on, modify the following script to include your temp datafile and path, and run from SQL Plus as SYS / as SYSDBA:

SQL> alter database tempfile '<Your path>\<Your_TEMP01>.DBF' autoextend on;

Here's an example:

SQL> alter database tempfile 'C:\APP\ADMINISTRATOR\ORADATA\PROTECT\TEMP01.DBF' autoextend on;

How to set current value of a sequence without dropping/recreating

Oracle does not let you change directly the value of a sequence. If you need to change its value, you should re-create the sequence.
On the other hand, we can use a trick to change the value of a sequence without dropping/recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

SQL> CREATE SEQUENCE test_seq

            INCREMENT BY 1

            START WITH 1000

            MINVALUE 1

            MAXVALUE 99999999;

SQL> SELECT last_number

       FROM user_sequences

      WHERE sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

1000

SQL> ALTER SEQUENCE test_seq INCREMENT BY -500;

SQL> SELECT test_seq.NEXTVAL FROM dual;

SQL> ALTER SEQUENCE test_seq INCREMENT BY 1;

SQL> SELECT last_number

       FROM   user_sequences

      WHERE  sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

500

By this way, we can set the new value of the given sequence.

Just for fun and for easy use of that logic in the future, let’s create a procedure for it.

CREATE OR REPLACE PROCEDURE Set_Sequence_value

              ( pi_owner         IN VARCHAR2,

                pi_seq_name      IN VARCHAR2,

                pi_new_seq_value IN NUMBER)

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    l_last_number            NUMBER;

    l_incr_by                NUMBER;

    CURSOR c_get_seq_param IS

    SELECT increment_by

      FROM all_sequences

     WHERE sequence_owner = UPPER (pi_owner)

       AND sequence_name = UPPER (pi_seq_name);

BEGIN

    OPEN c_get_seq_param;

    FETCH c_get_seq_param INTO l_incr_by;

    CLOSE c_get_seq_param;

    IF l_incr_by IS NOT NULL

    THEN

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.CURRVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY '||(pi_new_seq_value - l_last_number);

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.NEXTVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY ' || l_incr_by;

    END IF;

END Set_Sequence_value;

Note: In order to not affect the transaction control, the procedure is created under AUTONOMOUS_TRANSACTION.

Have a fun and thank you for your time to read this article.

How to Fix "ORA-12505, TNS: listener does not currently know of SID given in connect descriptor"

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

To properly resolve this error and connect to the appropriate Oracle database, we’ll need to expound a bit on how Oracle behaves and, therefore, what is causing this issue in the first place.

SIDs vs SERVICE_NAMES
It is important to understand the (slight) difference between what Oracle defines as a SID compared to a SERVICE_NAME, as we’ll use this information to create a proper connection string later on.

In Oracle, the system identifier (or SID) is a local identifier of up to eight characters in length that is used to identify a particular database and differentiate it from other databases on the system.

Often the SID is the prefix word or DB_UNIQUE_NAME that precedes the DB_DOMAIN.

SERVICE_NAMES, on the other hand, represent the names by which database instances can be connected to. A SERVICE_NAME will typically follow the format of the SID followed by the database domain, like so: DB_UNIQUE_NAME.DB_DOMAIN

The TNS Listener
When a client is attempting to connect to an Oracle database, rather than connecting to the database directly, there is a broker service that intervenes and handles the connection request for the client.

This broker application is known as the listener and it performs the task of listening for incoming client requests. When a request is received, the listener processes and forwards that request onto the appropriate Oracle database server using a service handler, which just acts as the connection between the listener and the database server.

TNS Names and Configuration
When connecting to an Oracle database, typically your database server will have tnsnames.ora, which is a configuration file that informs the server about NET_SERVICE_NAMES which are valid database connections. By default, this file is located at ORACLE_HOME/network/admin.

For example, a NET_SERVICE_NAME descriptor in tnsnames.ora may be formatted like this:

myDatabaseNetService =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)(QUEUESIZE = 100))
    (CONNECT_DATA =
      (SERVICE_NAME = bookstore.company.com)
    )
  )

This would define a NET_SERVICE_NAME using the SERVICE_NAME we discussed earlier and connecting to localhost through port 1521.

Connection String
With a bit more knowledge about how Oracle actually connects to databases, we can now look at how connection strings are formatted.


Connect via TNS NAME
When connecting through a NET_SERVICE_NAME as specified in your tnsnames.ora config file, you must use the username, password, and then append the NET_SERVICE_NAME with the @ symbol, like so:

username/password@NET_SERVICE_NAME

Connect via NET_SERVICE_NAME
Thus, for our previous NET_SERVICE_NAME descriptor above, the actual NET_SERVICE_NAME we defined was myTestDatabaseNetService, so our connection string might look something like this:

john/Pass123@myTestDatabaseNetService

Connect via SERVICE_NAME
When connecting through a SERVICE_NAME, you’ll also need to add the host and port, along with the / symbol preceding the SERVICE_NAME itself:

username/password@host:port/SERVICE_NAME

Connect via SID
Finally, if connecting without a configured NET_SERVICE_NAME or even SERVICE_NAME, you can do so directly through the SID by using the : symbol instead of the / symbol as with the SERVICE_NAME connection string:

username/password@host:port:SID

Change a user's password in Oracle

Question: How do I change the password for a user in Oracle?

Answer: To change a user's password in Oracle, you need to execute the alter user command.

Syntax:
The syntax for changing a password in Oracle is:
ALTER USER user_name IDENTIFIED BY new_password;

Parameters or Arguments:
user_name - The user whose password you wish to change.
new_password - The new password to assign.

Example
Let's look at an example of how to change a password for a user in Oracle/PLSQL.

ALTER USER smith IDENTIFIED BY Pass05_NM;

This example would change the password for the user named smith and set the new password to Pass05_NM.

How to force a trace file to be created when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had a message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on an event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:

Oracle docs note this about ORA-01652:
Error: RA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


To turn trace on you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK LEVEL 3’;

It will write to the alert.log

Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set an event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
ALTER system SET event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL
ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on an event for.

Thank you for reading and support.

Recompile the whole DB using Oracle's scripts utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. 

They are typically run after major database changes such as upgrade or downgrade or patches installation. 

They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper for the UTL_RECOMP package. UTL_RECOMP provides a more general recompilation interface, including options to recompile objects in a single schema. Please see the documentation for package UTL_RECOMP for more details. 

The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". 

The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

     0 - The level of parallelism is derived based on the CPU_COUNT parameter.

     1 - The recompilation is run serially, one object at a time.

     N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Thank you for the reading and support.

ORA-22950 cannot ORDER objects without MAP or ORDER method

The following is the cause of this error:

The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared.
But an object type, such as a some_type, which can have multiple attributes of various data types, has no predefined axis of comparison.
To be able to compare and order variables of an object type, you must specify a basis for comparing them.
Two special kinds of member methods can be defined for doing this: map methods and order methods.
The action you can take to resolve this issue: Define a MAP or ORDER method for the object type.

For more information, you can see https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref211

ORA-02030 error when try to grant permissions to a v$ tables

Face with an error ORA-02030 when you try to give permission on a v$ view

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Why it couldn’t give select privileges to a v$ view. If you tried to give permission to other v$views you will face same the error.

The problem is caused because of trying to give select privileges on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must grant permissions like below:

SQL> grant select on v_$session to hr;
Grant succeeded.

Switching to a Different Schema

The following statement sets the schema of the current session to the schema name specified in the statement.

ALTER SESSION SET CURRENT_SCHEMA = <schema name>;

In subsequent SQL statements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains its original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.

You can use this command above to avoid the use of public synonyms.  By setting the CURRENT_SCHEMA attribute to the schema owner name it is not necessary to create public synonyms for production table names. Without the current_schema syntax, you would have to assign a public synonym for every table in your production database.

If you want to check the current schema of the session you can use the following statement:

SELECT sys_context( 'userenv', 'current_schema' ) from dual; 

Let's look in the following example:

CONNECT user1

ALTER SESSION SET CURRENT_SCHEMA = schema_2;

SELECT * FROM empoyers;

Because user1 is not schema-qualified, the table name is resolved under schema schema_2. But if user1 does not have select privilege on table schema_2.empoyers, then user1 cannot execute the SELECT statement even switching the current session schema.

Using UNIX environment variables in SQL Plus

Passing variables to a SQL script under UNIX enviroment.

Method 1

$ Variable1="Someting value"
sqlplus db_user/db_password@db_sid <<EOF
set serveroutput on
BEGIN
  dbms_output.put_line('${Variable1}');
END;
/
exit;
EOF



Method 2
Create a test file called test_script.sql

$ cat test_script.sql
set serveroutput on
DECLARE
  var1 varchar2(100):='&&1';
  var2 varchar2(100):='&&2';
BEGIN
  dbms_output.put_line(var1);
  dbms_output.put_line(var2);
END;
/
exit;

For the test purpose let’s create a UNIX script file which executes out sql script.
$ cat test_unix_script.sh
var1="Some text"
var2="Some other text”
sqlplus -s db_user/db_password@db_sid @test_script.sql $var1 $var2

Output from execution of  the script
$ test_str.sh
old   3:   l_var1 varchar2(100):='&&1';
new   3:   l_var1 varchar2(100):='Some text';
old   4:   l_var2 varchar2(100):='&&2';
new   4:   l_var2 varchar2(100):='Some other text';

Some text 
Some other text

Setting, Resetting/Unsetting Oracle DB Parameters

There are more than 250 documented and more than 900 undocumented parameters for Oracle.
You can query the documented parameters with the query below

select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated,
from v$parameter

You can query the list of undocumented parameters with the script  list of hidden parameters in oracle db

Also you can query v$parameter2 as the same way you query the v$parameter view to view the session specific parameter values.

The values from the columns isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated are important.

isdefault = TRUE/FALSE –indicates if the value is the default value

isses_modifiable=TRUE/FALSE –indicates if the value can be modified by sessions

issys_modifiable = IMMEDIATE/DEFERRED/FALSE –indicates if the value can be modified by alter system command. Values represents when the changes takes effect. For immediate, no matter what you use (spfile or init file ) parameter change takes effect immediately.For deferred, parameter can be changed but change takes affect in subsequent sessions not for currently established sessions. For false it means parameter cannot be changed if you dont have a spfile.

isinstance_modifiable= true/false –this option is spesicif for RAC which has multiple instances for one database

If you want to view value of the parameter briefly you can also use show parameter command which searches the entered string with wildcard on both ends

SQL> show parameter pool

NAME TYPE VALUE
————————————
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 7759462
shared_pool_size big integer 0
streams_pool_size big integer 0

As you see the output above all parameters which include “pool” string in it are listed briefly.

Setting Parameter for Instance

You can set the parameter values with the command below

Alter system set parameter=value <comment=’text’> <deferred>
<scope=memory|spfile|both> <sid=’sid|*’>

elements of the command;

scope =BOTH/SPFILE/MEMORY indicates the scope of the parameter setting. both is the default behaviour.

sid = SID/* if you are on RAC environment you can give SID or * for this element which indicate that the setting will effect only the given SID or all nodes in RAC configuration.

comment= You can comment the change for future lookups. This comment can be viewed from UPDATE_COMMENT column of v$parameter view.

deferred = Indicates that the parameter change takes place for subsequent sessions only (not currently established sessions, including the one making the change). This element must be used for the parameters whose ISSYS_MODIFIABLE column in v$parameter view is deferred.

When you want to change the parameter only for the running instance and not for the subsequent instance you can change it only in memory. ;

SQL> alter system set cpu_count=4 scope=MEMORY;
System altered.

When you want to change the parameter for subsequent instances only
SQL> alter system set cpu_count=4 scope=SPFILE;
System altered.

When you want to change the parameter for running and subsequent instances (if issys_modifiable is not false);

SQL> alter system set cpu_count=4 scope=BOTH;
System altered.

!!!!!NB: When using an init.ora parameter file, the default and only valid value is SCOPE=MEMORY. So you must manually edit your init.ora file to change the parameter for subsequent instances.

Setting Parameters for sessions

You can change the values of some parameters whose ISSES_MODIFIABLE column of v$parameter view is TRUE. Scope of the parameter will be the end of the session.

SQL> alter session set timed_statistics=false;
Session altered.

SQL> show parameter timed_statistics

NAME TYPE VALUE
———————————— ———– ——-
timed_statistics boolean FALSE

Resetting/Unsetting parameters

If you want to use the default value of the parameter you have changed. You must reset it.

alter system reset parameter <scope=memory|spfile|both> sid=’sid|*’

Elements are same as setting parameter. The only difference is “you must” give si value (for single instance use ‘*’).

SQL> alter system reset control_file_record_keep_time scope=spfile sid=’*’;
System altered.

!!!!!NB: Do not try to change the value of a parameter with ” or null to reset it to default

Managing Initialization Parameters Using a Server Parameter File (SPFILE)

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file(PFILE). For better manageability, you can choose to maintain initialization parameters in a binary server parameter file (SPFILE) that is persistent across database startup and shutdown.

Grants to execute AWR (Automatic Workload Repository) Report

Problem:  How to give access rights to certain user to run AWR reports.

How to give access to run awr reports from normal users.
Generally, we run AWR from some administrator accounts (usually from sys or system users).
In order to generate the AWR reports from normal user we need to grant the follow privileges to him.

Let's follow the following plan:
  1. Create user.
  2. Grant the advisor privilege.
  3. Grant execute permission on dbms_workload_repository package.
  4. Grant select any dictionary

Here here is the complete summary of the activity
===================================================================
SQL> create user awradmin identified by pass123;

User created.

SQL> grant connect,resource to awradmin;

Grant succeeded.

SQL> grant advisor to awradmin;

Grant succeeded.

SQL> grant execute on dbms_workload_repository to awradmin;

Grant succeeded.

SQL> grant select any dictionary to awradmin;

Grant succeeded.

SQL> conn awradmin/awradmin123
Connected.
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>


===================================================================
In some cases it may be not possible to grant all permissions so in that case you can use follow the plan described below.

GRANT SELECT ON SYS.V_$DATABASE TO awradmin;
GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;

SQL> GRANT SELECT ON SYS.V_$DATABASE TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;

Grant succeeded.

SQL>

=====================================================================
There is a fast way to do that :) You need to grant role CONNECT, SELECT_CATALOG_ROLE and OEM_MONITOR to user and that is all.

GRANT CONNECT TO awradmin;
GRANT SELECT_CATALOG_ROLE TO awradmin;
GRANT OEM_MONITOR TO awradmin;

The role "SELECT_CATALOG_ROLE" contains the privileges:
   SELECT ON DBA_HIST_SNAPSHOT
   SELECT ON DBA_HIST_DATABASE_INSTANCE
   SELCT ON V_$INSTANCE
   SELECT ON V_$DATABASE

The role "OEM_MONITOR" contains the privilege EXECUTE ON DBMS_WORKLOAD_REPOSITORY.


SQL> GRANT CONNECT TO awradmin;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO awradmin;

Grant succeeded.

SQL> GRANT OEM_MONITOR TO awradmin;

Grant succeeded.

SQL>


Thank you, Enjoy ... :)

Invisible Indexes in Oracle Database

DESCRIPTION
Oracle 11g introduces a new feature for indexes, invisible indexes. That can be useful in several different situations. An invisible index is an index that is maintained by the database but ignored by the optimizer unless explicitly specified. The invisible index is an alternative to dropping or making an index unusable. This feature is also functional when certain modules of an application require a specific index without affecting the rest of the application.

SYNTAX
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;


EXAMPLES
The following script creates and populates a table, then creates an invisible index on it.

CREATE TABLE test_tab (id  NUMBER);
Table created.

BEGIN
  FOR i IN 1 .. 10000 LOOP
    INSERT INTO test_tab VALUES (i);
  END LOOP;
  COMMIT;
END;
/
PL/SQL procedure successfully completed.

CREATE INDEX test_idx ON test_tab(id) INVISIBLE;
Index created.

EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade=> TRUE);
PL/SQL procedure successfully completed.

A query using the indexed column in the WHERE clause ignores the index and does a full table scan.

SET AUTOTRACE ON
SELECT * FROM test_tab WHERE id = 9999;

----------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |      1 |        |      1 |00:00:00.01 |      24 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |      1 |      1 |      1 |00:00:00.01 |      24 |
----------------------------------------------------------------------------------------

Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.

SELECT id FROM test_tab WHERE id = 9999;

---------------------------------------------------------------------------------------
| Id  | Operation        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
Session altered.

ALTER INDEX test_idx VISIBLE;
Index altered.

---------------------------------------------------------------------------------------
| Id  | Operation        | Name     | Starts | E-Rows | A-Rsows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.

SELECT index_name, visibility FROM user_indexes WHERE index_name='TEST_IDX';

INDEX_NAME VISIBILITY
---------- --------------
TEST_IDX   VISIBLE

Resizing initial extent of the table

DESCRIPTION

There is no way to change the initial extent size of the table directly. There is a way to do this without dropping and recreating the table. It just needs to move the table with storage clause. It’s possible to move the table in other tablespace and back it again to the current one but this is not necessary. It’s just an option.

SYNTAX

ALTER TABLE [schema_name].[table_name] MOVE TABLESPACE [tablespace_name] STORAGE (INITIAL [number1] NEXT [number2] PCTINCREASE 0);

PARAMETERS or ARGUMENTS

schema_name - Name of the schema.

table_name - Name of the table which you want to change initial extent

number1 - Initial extent size in bytes. 

number2 - Size of the next extent which will be allocated to the object

NOTE

[number1] and [number2] can be represented with size_clause.

The size_clause lets you specify the amount of disk memory space. It can be a number of bytes, kilobytes (K), megabytes (M), gigabytes (G), terabytes (T). If you don’t specify any abbreviation the integer is considered as bytes.

Example: 1024; 64K; 10M ; 2G; 1T;

EXAMPLES

Tablespace name: def_tbs

Schema name      : def_schema

Table name         : test_table_1

Example 1:

ALTER TABLE def_schema.test_table_1

  MOVE TABLESPACE def_tbs

  STORAGE (INITIAL 64K NEXT 1M PCTINCREASE 0);

Example 2 :    Make the same like Example 1

ALTER TABLE def_schema.test_table_1

  MOVE TABLESPACE def_tbs

  STORAGE (INITIAL 65536 NEXT 1048576 PCTINCREASE 0);

Oracle DB: REPLACE Function

DESCRIPTION

The Oracle REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX

REPLACE( string1, string_to_replace, replacement_string )

PARAMETERS or ARGUMENTS

string1 - The string to replace a sequence of characters with another set of characters.

string_to_replace - The string that will be searched for in string1.

replacement_string – This parameter is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.

EXAMPLES

REPLACE('123123test', '12');    
     Result: '33test'

REPLACE('123work123', '123');   
     Result: 'work'

REPLACE('222oracle', '2', '3'); 
     Result: '333oracle'

REPLACE('0000102300', '0');     
     Result: '123'

REPLACE('0000888', '0', ' ');   
     Result: '    888'

Oracle DB: NVL Function

DESCRIPTION

NVL replaces NULL value with other value in the results of a query.

SYNTAX

NVL(expr1, expr2)

PARAMETERS or ARGUMENTS

If expr1 is NULL, then NVL returns expr2. If expr1 is not NULL, then NVL returns expr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.

The implicit conversion is implemented as follows:

-          If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

-          If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

EXAMPLES

SQL> SELECT NVL(supplier_city, 'n/a')

       FROM suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a NULLvalue. Otherwise, it would return the supplier_city value.

Execute a SQL script files in sqlplus

Question: How do I execute a SQL script file in SQLPlus?

Answer: To execute a script file in SQLPlus, type @ and then the file name.

SQL> @{yourfile}

For example, if your file was called yourscript.sql, you'd type the following command at the SQL prompt:

SQL> @yourscript.sql

The above command assumes that the file is in the current directory, i.e. the current directory is usually the directory that you were located in before you launched SQLPlus.

If you need to execute a script file that is not in the current directory, you would type:

SQL> @{path}{yourfile}

For example:

SQL> @/oracle/scripts/yourscript.sql

This command would run a script file called yourscript.sql that was located in the /oracle/scripts directory.