1. SQL Developer Support User's Guide
  2. Working with TimesTen Objects

4 Working with TimesTen Objects

This chapter focuses on working with TimesTen objects in SQL Developer.

Topics include:

Working with users

You can create, edit, or drop database users from a TimesTen database with SQL Developer.

Topics include:

Create a user

You can create a database user for a TimesTen database.

To create a user, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

Note:

You can only create users under a direct connection, and the connected user needs ADMIN system privileges. See Connecting to a TimesTen Database.

  1. Right-click on the Other Users node and select Create User.

    The Create User dialog appears.

  2. In the User Name text field, type a name for the user.
  3. In the New Password text field, type a password for the user.
  4. In the Confirm Password text field, type the same password as before.
  5. In the Profile drop-down list, select a profile for the user. The default is DEFAULT.

    See "Profile for password management"in the Oracle TimesTen In-Memory Database Security Guide for more information on profiles.

    See Working with profiles for more information on how to manage profiles using SQL Developer.

You can now select the system privileges to grant to the user.

Grant system privileges to user

To grant system privileges to the user, locate the System Privileges tab.

  1. Select the System Privileges tab.
  2. In the Privileges table, select the check box for the system privileges to be granted to the user.

    Alternatively, you can click the Grant All button to select all system privileges.

    See "System privileges" in the Oracle TimesTen In-Memory Database Security Guide for more information on systems privileges in TimesTen.

You can now review the SQL statements before they are applied.

Review the SQL of the CREATE USER and GRANT statements

To review the CREATE USER and GRANT statements, locate the SQL tab.

  1. Select the SQL tab.
  2. Once you have review the SQL statements, click Apply.

    TimesTen runs the CREATE USER and GRANT statements, and the Create User dialog automatically selects the Results tab. The Results tab shows the results of running the SQL statements.

    See "CREATE USER" and "GRANT" in the Oracle TimesTen In-Memory Database SQL Reference for more information on the CREATE USER and GRANT statements, respectively.

  3. Click Close.

    The Create User dialog closes.

You have successfully created a user.

Edit a user

You can alter a database user already created. When you alter an existing user, you can perform these actions:

  • Change the password.

  • Change the profile.

  • Lock or unlock the user.

  • Expire the current password.

  • Update the list of granted system privileges.

    Note:

    All actions above, except for updating the list of granted system privileges, need to be performed under a direct or encrypted client/server connection, the connected user needs ADMIN system privileges. See "Transport Layer Security for TimesTen Client/Server" in the Oracle TimesTen In-Memory Database Security Guide for more information on encrypted client/server connections.

To alter a user, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.
  1. Expand the Other Users node.

    The node expands into the list of available users.

    Note:

    The connected user is not listed. You can only reset the password through the current connection, see Reset password of connected user.

    To edit the connected user use a TimesTen connection with a different user.

  2. Right-click on the user name and select Edit user.

    The Edit User dialog appears. The Edit User dialog is the same as the Create User dialog, except that not all options can be edited. You can edit the options listed above based on your connection type.

    See Create a user for more information on how to use the Create User dialog.

Once you are done editing the options in the User and System Privileges tabs, you can review the SQL statements before they are applied.

Review the SQL of the ALTER USER and GRANT/REVOKE statements

To review the ALTER USER and GRANT statements, locate the SQL tab.

  1. Select the SQL tab.
  2. Once you have review the SQL statements, click Apply.

    TimesTen runs the ALTER USER and GRANT/REVOKE statements, and the Edit User dialog automatically selects the Results tab. The Results tab shows the results of running the SQL statements.

    See "ALTER USER", "GRANT", and "REVOKE" in the Oracle TimesTen In-Memory Database SQL Reference for more information on the ALTER USER, GRANT, and REVOKE statements, respectively.

  3. Click Close.

    The Edit User dialog closes.

You have successfully edited a user.

Drop a user

You can drop a database user from a TimesTen database from SQL Developer.

To drop a user, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

Note:

The connected user needs ADMIN system privileges to drop a user.

  1. Expand the Other Users node.

    The node expands into the list of available users.

  2. Right-click on the user name and select Drop user.

    The Drop User dialog appears.

  3. Click Apply.

    TimesTen runs DROP USER statement, and the Confirmation dialog appears.

    See "DROP USER" in the Oracle TimesTen In-Memory Database SQL Reference for more information on the DROP USER statement.

    Note:

    If you attempt to drop a user that is connected to the database, TimesTen returns an error.

  4. Click OK.

    The Confirmation dialog closes.

You have successfully dropped a user.

Reset password of connected user

You can reset the password of the database user used for the TimesTen connection.

To reset the password of the connected user, ensure that you are on the main SQL Developer page.

Note:

  • You cannot reset the password of the instance administrator or external users. See "Overview of TimesTen users" in the Oracle TimesTen In-Memory Database Security Guide,

  • You can only reset the password of the connected user under a direct or encrypted client/server connection. See "Transport Layer Security for TimesTen Client/Server" in the Oracle TimesTen In-Memory Database Security Guide for more information on encrypted client/server connections.

  1. Right-click on the TimesTen connection name and select Reset Password.

    The Enter New Password dialog appears.

  2. In the Current Password text field, type the current password for the user.
  3. In the New Password text field, type a new password for the user.
  4. In the Confirm Password text field, type the same password as before.
  5. Click OK.

    TimesTen resets the password and the Reset Password dialog appears.

  6. Click OK.

    The Reset Password dialog closes.

You have successfully reset the password of the connected user.

Working with profiles

You can create, edit, or drop password management profiles from a TimesTen database with SQL Developer.

Topics include:

Create a profile

You can create a password management profile for database users in a TimesTen database.

To create a profile, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

Note:

The connected user needs ADMIN system privileges to create a profile.

  1. Right-click the Profiles node and select New Profile.

    The New Profile dialog appears.

  2. In the Profile name text field, type the name for the profile.
  3. Type a valid value for:
    1. The Failed login attempts text field.
    2. The Password life time (days) text field.
    3. The Password reuse time (days) text field.
    4. The Password reuse max text field.
    5. The Password lock time (days) text field.
    6. The Password grace time (days) text field.

    Note:

    These text fields support DEFAULT, UNLIMITED, or an integer greater than 1 as input. The DEFAULT values are defined in the DEFAULT profile. See Password management in the Oracle TimesTen In-Memory Database Security Guide.

  4. Click Apply.

    TimesTen runs the CREATE PROFILE statement, and the New Profile dialog automatically selects the Results tab. The Results tab shows the results of running the SQL statement.

    See "CREATE PROFILE" in the Oracle TimesTen In-Memory Database SQL Reference for more information on the CREATE PROFILE statement.

  5. Click Close.

    The New Profile dialog closes.

You have successfully created a profile.

Edit a profile

You can edit a password management profile for database users in a TimesTen database.

To edit a profile, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

Note:

The connected user needs ADMIN system privileges to edit a profile.

  1. Expand the Profiles node.

    The node expands into the list of available profiles.

  2. Right-click on the profile name and select Edit Profile.

    The Edit Profile dialog appears. The Edit Profile dialog is the same as the New Profile dialog. You can edit all options except for the Profile name. See Create a profile for more information on how to use the New Profile dialog.

  3. Once you are done editing the options in the Profile tab, click Apply.

    TimesTen runs the ALTER PROFILE statement, and the Edit Profile dialog automatically selects the Results tab. The Results tab shows the results of running the SQL statement.

    See "ALTER PROFILE" in the Oracle TimesTen In-Memory Database SQL Reference for more information on the ALTER PROFILE statement.

  4. Click Close.

    The Edit Profile dialog closes.

You have successfully edited a profile.

Drop a profile

You can drop a password management profile for database users from a TimesTen database.

To drop a profile, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

Note:

The connected user needs ADMIN system privileges to edit a profile.

  1. Expand the Profiles node.

    The node expands into the list of available profiles.

  2. Right-click on the profile name and select Drop Profile.

    The Drop Profile dialog appears.

  3. Click Apply.

    TimesTen runs the DROP PROFILE statement, and the Drop Profile dialog automatically selects the Results tab. The Results tab shows the results of running the SQL statement.

    See "DROP PROFILE" in the Oracle TimesTen In-Memory Database SQL Reference for more information on the DROP PROFILE statement.

  4. Click Close.

    The Drop Profile dialog closes.

You have successfully dropped a profile.

Working with tables

You can work with tables for the TimesTen databases. This section includes these topics:

Create a table

You can create a table for the TimesTen database or TimesTen Scaleout database.

Ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Right-click the Tables option and select New Table.

    The Create Table dialog displays. Locate the Schema drop-down list.

  2. From the Schema drop-down list, select the owner of the table.
  3. In the Name text field, type a name for the table.

    You can now define the columns for your table. Locate the Columns region.

Define the columns of your table

To define the columns of your table, locate the Columns table. For each of the columns that you want to define:

  1. In the PK column, click in the column if you want to define this column as a primary key. You need to have a primary key if you want to create an index on your table.
  2. In the Name column, type a column name. For more information on supported column names, see "Basic names" in the Oracle TimesTen In-Memory Database SQL Reference.
  3. In the Data Type column, select a data type for your column. For more information on data types, see "Data Types" in the Oracle TimesTen In-Memory Database SQL Reference.
  4. In the Size column, select a size for your column based on your the data type that you selected.
  5. If you want this column to be NOT NULL, select the Not Null check box.
  6. If you want this column to have a default value, type a value in the Default column.
  7. If you want to define this column as INLINE, select the Inline check box. By default, variable-length columns whose declared column length is > 128 bytes are stored out of line. Variable-length columns whose declared column length is <= 128 bytes are stored inline.

You have successfully defined a column for your table.

To add another column, click the + button that is located in the top right corner of the Columns table. Then, repeat steps 1-7 to define each column.

To remove a column, select the column that you want to remove and click the - sign that is located in the top right corner of the Columns table.

To copy a column, select the column that you want to copy and click the Copy Column button that is located in the top right corner of the Columns table.

Once you have defined all of the columns for your table:

Create an index for your table

To define an index for your table, you need to have defined a primary key for your table. To define an index on your table:

  1. Select an index type from the Index Type options. For more information on index types, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.
  2. If you selected a hash index, type a RowPages parameter in the Page size(Rows) text field.
  3. Compute this value by dividing the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for the value of RowPages (256000/256=1000).

You have successfully defined an index on your table. You are now ready to define constraints for your table.

Set constraints for your table

Select the Constraints tab to work with constraints for your table.

If you have defined a primary key or compound primary key in the columns tab, you see this primary key constraint in the Constraints tab.

You can set these types of constraints:

  • Primary key constraint: If you have not defined a primary key for your table in the Columns tab, click the + button located in the top right corner of the Constraints region and then select New Primary Key Constraint.

    If you have already defined a primary key, the New Primary Key Constraint option is not available. However, you are able to edit the definition of your primary key in the constraints region.

    In the Constraints table you can:

    • Edit the name of your primary key by editing the name column of the Primary Key row.

    • Select columns to be part of your primary key definition by double clicking the column name in the Available Columns list.

    • Remove columns from your primary key definition by double clicking the column name in the Selected Columns list.

    • Remove a primary key constraint by selecting the primary key constraint that you want to remove, and then clicking the x sign that is located in the top right corner of the Constraints region.

  • Foreign key constraint: To define a foreign key for your table:

    1. Click the + button located in the top right corner of the Constraints region and then select New Foreign Key Constraint.

    2. In the Constraints table, specify the name of your foreign key. You can edit the name of a foreign key by editing the name column of the Foreign Key row in the Constraints table.

      Locate the Referenced Constraint region.

    3. From the Schema drop-down list, select the schema name of the table that you want to reference. Alternatively, you can type the schema name instead of selecting it from the drop-down list.

    4. From the Table drop-down list, select the table name that you want to reference. Alternatively, you can type the table name instead of selecting it from the drop-down list.

    5. From the Constraint drop-down list, select a primary key constraint name that you want to reference. Alternatively, you can type the primary key constraint name instead of selecting it from the drop-down list.

    6. From the On Delete drop-down list, select an on delete option.

    You have successfully defined a foreign key constraint.

    • To add another foreign key constraint, the + button located in the top right corner of the Constraints region and then select New Foreign Key Constraint. Then, repeat steps 1-6 to define each foreign key constraint.

    • To remove a foreign key constraint, select the foreign key constraint that you want to remove and click the x sign that is located in the top right corner of the Constraints region.

Set the distribution method for your table

This tab is only available when working with a TimesTen Scaleout table.

To define the distribution method for your table:

  1. Select the Distribution tab.
  2. From the Distribution method drop-down list, select a distribution method for your table:

    • Distribute by Hash: Distribution scheme that distributes data based on the hash of the primary key or a set of user-specified columns. For more information, see "Hash" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

      When you decide how to distribute your table with the hash distribution scheme, consider the following:

      • If the table has a primary key and you do not specify a distribution, column, TimesTen Scaleout distributes the data based on the hash of the primary key.

      • If the table does not have a primary key or you do not specify a distribution column, TimesTen Scaleout distributes the data based on the hash of a hidden column that TimesTen Scaleout adds for this purpose.

      • If you specify a distribution column, TimesTen Scaleout distributes the data based on the hash of the distribution column. You can specify multiple distribution columns.

      To specify distribution columns for your table:

      • To select columns to be part of your distribution column, double click the column name in the Available Columns list.

      • To remove columns from your distribution column, double click the column name in the Selected Columns list.

    • Distribute by Reference: Distribution scheme that distributes the data of a child table based on the location of the corresponding parent row of a foreign key constraint. Your table needs at least one foreign key to be able to use this distribution scheme. For more information, see "Reference" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

      Ensure that you have defined a foreign key to use the distribute by reference distribution scheme. To distribute your table with this distribution scheme:

      From the Foreign Key drop-down list, select the foreign key to distribute the data of your table.

    • Distribute by Duplicate: Distribution scheme that distributes full identical copies of the table's data to all the elements of a database. For more information, see "Duplicate" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

      When you select the distribute by duplicate distribution scheme, you do not need to specify any additional fields in the Distribution tab of the Create Table dialog.

    You are now ready to review the DDL of your CREATE TABLE statement. See Review the DDL of the CREATE TABLE statement for more information.

Review the DDL of the CREATE TABLE statement

You can review the DDL of your CREATE TABLE statement.

  1. Select the DDL tab.

    Note:

    To save the DDL statement in a .sql file, click the Save button.

  2. Once you have reviewed the DDL statement, click OK.

The Create Table dialog closes and TimesTen Scaleout creates your table.

You have successfully created a table.

Edit a table

You can alter a table that is already created. When you alter an existing table, you cannot perform these actions:

  • Change the schema name.

  • Change the table name.

  • Change the index.

  • Change the distribution scheme of a table that contains data. This only applies to TimesTen Scaleout tables.

To edit a table, ensure that you are on the main SQL Developer page and that your connection is expanded:

Expand the Tables option, right-click the table name, and then select Edit.

The Edit Table dialog displays. The Edit Table dialog is the same as the Create Table dialog, except that not all options can be edited. You can edit all of the options except for those that are mentioned above. See Create a table for more information on how to use the Create Table dialog.

Review the distribution of data for a TimesTen Scaleout table

You can view distribution statistics for your table. This feature is only available for TimesTen Scaleout tables. Distribution statistics enable you to see how your data is distributed between the data instances of your grid.

To view the distribution statics for a table, ensure that you are on the main SQL Developer page and that your connection is expanded:

  1. Expand the Tables option and double-click the table name.

    A table view displays. Locate the Distribution tab.

  2. Click the Distribution tab from the table view.

    The Distribution tab displays.

Figure 4-1 Distribution tab

Description of Figure 4-1 follows
Description of "Figure 4-1 Distribution tab"

The Distribution tab contains these regions:

Note:

The contents of each region can vary depending on the distribution scheme of the table.

  • Row distribution chart

    This chart can show either a column or pie chart of the row count of your table on each of your elements. Use the drop-down list in this region to switch between the column and pie chart for your table.

    The pie chart enables you to view a pie chart for the row count of each element of your data space groups. Use the drop-down list in this region to switch between data space groups. The pie chart is not available when your table uses a duplicate distribution scheme.

    If one of your elements is down or unavailable, the corresponding column chart for that element becomes unavailable. Also, the pie chart for the data space group of the element becomes unavailable.

  • Distribution scheme table

    This table shows you information about the distribution scheme that your table uses.

    • Distribute by hash distribution scheme - This table shows the columns that TimesTen Scaleout uses to create a hash in order to distribute the rows of your table.

    • Distribute by reference distribution scheme - This table shows the foreign key relationship that TimesTen Scaleout uses to distribute the rows of your table.

    • Duplicate distribution scheme - This table doesn't show any information because this distribution scheme has the same information on every element.

  • Row distribution table

    This table shows the row count of your table on each of your elements. You can also see information about the data space group, replica set, element ID, instance name, and distribution percentage for each of the elements. If you use the duplicate distribution scheme, the distribution percentage column is not available.

    If one of your elements is down or unavailable, the row count and distribution percentage columns for that element are marked as unavailable.

    If the table is distributed by hash or reference, the distribution tab will display a summary row including totals for row count and distribution. This summary is only accessible when the table is sorted by data space group and will be hidden if sorted on a different column. If needed, this information is always available in the "Summary" tab next to "Row Distribution".

    If the table is distributed by hash or reference, this table displays summary rows with total row count and distribution percentage for each data space group. However, this is only available if the row distribution table is sorted by data space groups. If you use a different sort order, you can click the Summary tab to see these summary rows.

  • Summary table

    This table shows the row count and distribution percentage for each data space group.

Create a primary key constraint

You can create a primary key constraint on a table that does not have a primary key defined. Follow these steps:

  1. Click the + to the left of the Tables node to view the list of tables. Then right-click the name of the table to create a primary key constraint.
  2. Select Constraint, then select Add Primary Key.

    The Add primary key dialog appears.

  3. In the Key index type field choose either Use range to create a primary key using a range index or Use hash to create a primary key using a hash index.

    If you choose Use hash, you see the text with number of followed by a drop-down list of either row or pages. Select either row or pages and in the box after the colon (:), enter the number of rows or pages. The default selection is row.

    For more information on hash index sizing, see "CREATE INDEX" in the Oracle TimesTen In-Memory Database SQL Reference.

  4. In the Constraint Name field, enter the constraint name. The name cannot exceed 30 characters in length.
  5. In the Column field(s), you see a drop-down list of column names. Choose the column name(s) to use for the primary key.
  6. Click Apply.

    The Confirm dialog box appears indicating that the primary key constraint has been added.

  7. Click OK.

    The primary key constraint has been added successfully.

To view the SQL statement used to create the primary key constraint, choose the SQL tab of the Add primary key dialog box.

For information about creating a primary key constraint, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference or "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Change the attributes of primary key

You can change the attributes of a primary key constraint after you have defined a primary key on a table. Follow these steps:

  1. Click the + to the left of the Tables node to view the list of tables. Then right-click the name of the table on which to change the primary key attributes.
  2. Select Constraint, then select Change Primary Key Parameters.

    The Change parameters of a primary key dialog appears.

  3. In the Key index type field choose either Use range to change the primary key constraint from using a hash index to using a range index or Use hash to change the primary key constraint from using a range index to using a hash index.

    If you choose Use hash, you see the text with number of followed by a drop-down list of either row or pages. Select either row or pages and in the box after the colon (:), enter the number of rows or pages. The default selection is row.

    For more information on hash index sizing, see "CREATE INDEX" in Oracle TimesTen In-Memory Database SQL Reference.

  4. Click Apply.

    The Confirm dialog appears indicating that the index(es) for the primary key constraint has been changed.

  5. Click OK.

    The index(es) for the primary key constraint has been changed successfully.

To view the SQL statement used to change the attributes of the primary key constraint, choose the SQL tab of the Change parameters of a primary key dialog.

For information about changing the attributes of a primary key constraint, see "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Create an index on a table

You can create a range index or a hash index on a regular table or on a cache table.

To create an index, click the + to the left of the Tables node to view the list of tables. Then right-click the name of the table to create an index on and select Index, then select Create Index.

You can also right-click the Indexes node and select New Index.

In the Properties tab of the Create Index dialog, locate the Table drop-down menu. From this menu, select the table for the index.

For Index Type:

  • To create a range index, choose Range.

  • To create a hash index, choose Hash. Then, select a Pages value:

    • Page size(Rows): Specify the number of pages to use. To determine the value, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 (256000/256=1000).

    • Current: The current number of rows in the table is used to calculate the page count value. Do not use this option if there are no rows in your table. This is because when rows are added to the table, the hash index performs poorly.

For Uniqueness:

  • To have a single occurrence of index key column values in your table, choose Unique.

  • If not, select Non-unique.

For information about the different types of indexes supported in TimesTen, see "CREATE INDEX" in the Oracle TimesTen In-Memory Database SQL Reference.

In the Index section, specify the columns for the index. You can select the columns from the Column Name and then click > to add these columns to your index. For each indexed column, from the Order select list, you can specify whether the column is to be sorted in ascending or descending order. The default sort order is ascending.

To create a composite index:

  1. Select additional columns and then click >. Select a column and then the < to remove columns from the index definition.
  2. In the DDL tab of the Create Index dialog, you can view the CREATE INDEX statement used to create the index.
  3. Click OK to create the index.

Create a foreign key constraint

You can create a foreign key constraint on a table. Ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Click the + to the left of the Tables node to expand the list of tables.
  2. Right-click the name of the table and select Constraint, then select Add Foreign Key.

    The Add Foreign Key dialog displays. The Owner and Name fields are auto-filled and you cannot edit these fields. Prepare to enter the Constraint Name.

  3. To enable the ON DELETE CASCADE referential action, select the Cascade delete foreign key checkbox. If specified, when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign key values are also deleted.
  4. In the Constraint Name field, enter the name of the foreign key.
  5. Select the column on which to apply the foreign key constraint from the Column Name drop-down list.
  6. Select the table name that the foreign key references from the Referenced Table Name drop-down list.
  7. Select the column that the foreign key references from the Referenced Column drop-down list.

    To view the SQL statement that will be used to create the foreign key constraint, choose the SQL tab of the Add Foreign Key dialog box.

  8. Click Apply.

    The Confirmation dialog box displays indicating that the foreign key constraint has been added.

  9. Click OK.

    The foreign key constraint has been added successfully.

For information about creating a foreign key constraint, see "CREATE TABLE" and "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Specify and display LOB data types

If you are using TimesTen release 11.2.2 or higher, then you can specify and display LOB data types in your table definitions. If you are using TimesTen Scaleout, ensure that this feature is supported in your version of TimesTen Scaleout. See "Comparison between TimesTen Scaleout and TimesTen Classic" in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

If you want to create a column with a LOB data type, then in the Create Table dialog, expand the drop-down list for the Data Type column header. You see CLOB, NCLOB, and BLOB as valid data types.

To view the data type for your columns, select the table and then choose the Columns tab. You see the LOB data type for your column.

If you want to add a column and specify a LOB data type, use the Add Column option. Right-click on the table, select Column, then select Add. The Add Column dialog appears. In the Add Column dialog, expand Data Type. You see CLOB, NCLOB, and BLOB among the possible choices.

For PL/SQL objects, you can specify LOB data types as IN, OUT, and IN OUT parameters. In the PL/SQL edit dialogs, for parameter types, choose CLOB, NCLOB, or BLOB.

Specify the INLINE attribute for columns

You can specify the INLINE attribute for columns of type VARCHAR2, NVARCHAR2, and VARBINARY.

In the Create Table dialog, locate the column header named Inline. Click in the check box to define the column with the INLINE attribute.

You can also specify the INLINE attribute when adding a column to a table. Right-click on the table, select Column, then select Add. The Add Column dialog appears. For columns of type VARCHAR2, NVARCHAR2, and VARBINARY, click in the Inline check box to add the column with the INLINE attribute.

Alter the aging properties of a table

You can add an aging policy to or drop an aging policy from a regular table or a cache table. You can also change the aging state for a table's existing aging policy, or change the lifetime and cycle for a table's existing time-based aging policy. An aging policy is only supported on TimesTen tables.

The following sections describes these aging policy operations:

Adding an aging policy to a table

To add an aging policy to a regular table or a cache table, under the Tables node, right-click the name of the table to add an aging policy to and select Aging, then select Add Usage-based to add an LRU aging policy. To add a time-based aging policy select Aging, then select Add Time-based. An aging policy can be added to a cache table only if it is the root table of a cache group.

If you are unable to find the table that you are looking for, see Locating a TimesTen database object.

To add an LRU aging policy to the table, in the Prompts tab of the Add Usage-based dialog box, specify an aging state by choosing either On or Off in the Usage-based aging field. The default aging state is on. Click Apply to add the LRU aging policy to the table.

The TimesTen database memory usage thresholds determine when data starts and stops being deleted from the table. The default memory usage threshold that determines when data starts being deleted from the table is 90%. The default memory usage threshold that determines when data stops being deleted from the table is 80%. The default LRU aging cycle is 1 minute. For information about how to change these thresholds and the LRU aging cycle, see Specifying an aging policy.

An LRU aging policy can be added to any regular table, and only to cache tables in an AWT, SWT or user managed cache group that does not have automatic refresh defined.

To add a time-based aging policy to the table, in the Prompts tab of the Add time-based dialog box, select the column name from Column to store the timestamp value indicating when each row was added or most recently updated.

In the Life-time field, indicate the length of time in which data that has not been updated is to be kept in the table by specifying a numeric value followed by a unit of minutes, hours or days in the drop-down menu.

In the Cycle field, indicate the frequency at which data is to be aged out of the table by specifying a numeric value followed by a unit of minutes, hours or days. The default time-based aging cycle is 5 minutes.

Specify an aging state by choosing either On or Off in the Time-based aging field. The default aging state is on. Then click Apply to add the time-based aging policy to the table.

A time-based aging policy can only be added to a table that contains a non-nullable DATE or TIMESTAMP column.

Dropping an aging policy from a table

To drop an existing aging policy from a regular table or a cache table, under the Tables node, right-click the name of the table to drop an aging policy from and select Aging, then select Drop.

If you are unable to find the table that you are looking for, see Locating a TimesTen database object.

Click Apply to drop the aging policy from the table.

Changing the aging state of a table's aging policy

To change the aging state of a regular table's or cache table's existing aging policy, under the Tables node, right-click the name of the table to change the aging state of and select Aging, then select Change State On/Off.

If you are unable to find the table that you are looking for, see Locating a TimesTen database object.

In the Prompts tab of the Change state on/off dialog, change the aging state by selecting either On or Off in the Change aging state field. Click Apply to change the aging state of the table.

Changing the memory usage thresholds and LRU aging cycle

To change the memory usage thresholds and the LRU aging cycle, right-click the node of the connection name for the TimesTen database and choose Change Usage-based Aging Attributes.

In the Prompts tab of the Changing the usage-based thresholds dialog box, specify the threshold that determines when data starts being deleted from the tables in the High usage threshold field. Specify the threshold that determines when data stops being deleted from the tables in the Low usage threshold field. Specify the LRU aging cycle in the Update Frequency field. Then click Apply to change the memory usage thresholds and the LRU aging cycle.

The new settings apply to all tables that have an LRU aging policy defined.

Changing the lifetime and cycle of a table's time-based aging policy

To change the lifetime and cycle of a regular table's or cache table's existing time-based aging policy, under the Tables node, right-click the name of the table to change the lifetime and cycle of and select Aging, then select Change Lifetime and Cycle.

If you are unable to find the table that you are looking for, see Locating a TimesTen database object for information.

In the Life-time field within the Prompts tab of the Change Lifetime and Cycle dialog box, change the length of time in which data that has not been updated is to be kept in the table by specifying a numeric value followed by a unit of minutes, hours or days.

In the Cycle field, change the frequency at which data is to be aged out of the table by specifying a numeric value followed by a unit of minutes, hours or days.

Then click Apply to change the lifetime and cycle for the table.

Load data into tables

You can load data into your table using Import Data. Before you can load your data, you must export the data into a file. To export the data, right-click on the name of the table and choose Export. After successfully exporting your data, you can import the data into a TimesTen table. The TimesTen export feature does not support exporting data from a TimesTen table for the purpose of importing that data into an Oracle database.

  1. Choose + to the left of the Tables node to view the list of tables.
  2. Right-click on the table name.
  3. Select Table, then select Import Data.

    An Open dialog appears. Locate the data file to import. This file is the file you previously exported.

  4. Choose Open.

    The data is loaded into the table.

Compute table size information

If you are using TimesTen Release 11.2.2 or higher, you can compute table size information.

To view table size information, you must first compute the table size:

  1. Choose + to the left of the Tables node to view the list of tables.

  2. Right-click the name of the table to compute table size information.

  3. Select Table, then select Compute Size.

    The Compute Size dialog appears. The owner and name of the table are displayed.

  4. Click in the Also count out-of-line sizes check box if you want to compute out-of-line sizes.

  5. Choose Apply.

    A Confirmation dialog appears.

  6. Choose OK.

    The table size statistics for the table are computed.

To view the SQL for computing the table size, choose the SQL tab in the Compute Size dialog. You see that a TimesTen built-in procedure called ttComputeTabSizes is executed. After this built-in is executed, you can review the table size information for your table. Note that this table size information is on a per table basis.

To compute table sizes for all tables in your database including materialized views, system tables, and tables that are part of cache groups, use the SQL Worksheet and execute the command: Call ttComputeTabSizes (NULL,0); or to include out-of-line data: Call ttComputeTabSizes (NULL,1);.

After you compute the table size for one or more tables, you can view the table size information:

  1. Choose + to the left of the Tables node to view the list of tables.
  2. Choose the table you want to review table size information.
  3. Choose the Sizes tab located among the tabs that display the attributes of the table.

    Table size information is displayed. Specifically, values for INLINE BYTES, OUT OF LINE BYTES, and METADATA BYTES are shown.

Figure 4-2 Displaying table size information

Description of Figure 4-2 follows
Description of "Figure 4-2 Displaying table size information"

You can choose the Actions menu to generate or regenerate the table size. If you select the Actions menu, then Table, then select Compute Size. The Compute Size dialog appears allowing you to compute the table size information.

Choose Refresh to refresh the displayed table size data.

You can view table size information for all tables that you have computed table sizes. To view such information, select TimesTen Reports, then Table, then select Table Sizes. For more information on TimesTen reports, see Generating TimesTen Reports.

For more information on the ttComputeTabSizes built-in procedure, see "ttComputeTabSizes" in Oracle TimesTen In-Memory Database Reference.

View the characteristics of a table

After you have created a regular table, a global temporary table or a cache table, you can view the characteristics of the table itself such as its columns, indexes and aging policy.

Click the + to the left of the Tables node to view the list of tables. Click the name of the table to view.

If you are unable to find the table that you are looking for, see Locating a TimesTen database object.

Viewing the columns

The Columns tab, located within the table's tab, shows information about the columns of a table. The name of the column, data type, nullability property and default value is displayed.

To view the list of columns in a table, locate the Tables node. Click the + to the left of the node that contains the name of the table.

Figure 4-3 Viewing the columns

Description of Figure 4-3 follows
Description of "Figure 4-3 Viewing the columns"
Viewing the indexes

The Indexes tab, located within the table tab, shows information about the index for a table. The name of the index, type, uniqueness property, and the number and name of the columns are displayed.

Viewing the aging attributes

The Aging attributes tab, located within the table tab, shows information about the aging attributes for a table. The aging policy type, aging cycle, and aging state are displayed.

For tables that have an LRU aging policy defined, the TimesTen database memory usage thresholds are displayed. For information about how to change these thresholds and the LRU aging cycle, see Specifying an aging policy.

For tables that have a time-based aging policy defined, the name of the non-nullable DATE or TIMESTAMP column used to store the timestamp value (indicating when each row was added or most recently updated) and the length of time non-updated data is not deleted from the table is displayed.

Figure 4-4 Viewing the aging attributes for a table

Description of Figure 4-4 follows
Description of "Figure 4-4 Viewing the aging attributes for a table"
Viewing the table definition

The SQL tab, located within the table tab, shows the CREATE TABLE statement that was used to create the table for a regular table or a global temporary table.

Since a cache table is created when its accompanying cache group is created, you can view a cache table's definition by viewing its cache group definition. See Viewing the cache group definition.

Figure 4-5 Viewing the SQL for the table definition

Description of Figure 4-5 follows
Description of "Figure 4-5 Viewing the SQL for the table definition"

Drop a constraint

You can drop a constraint from a TimesTen table. Ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Click the + to the left of the Tables node to expand the list of tables.
  2. Right-click the name of the table and select Constraint, then select Drop.

    The Drop dialog displays. The Owner and Name fields are auto-filled and you cannot edit these fields. Prepare to select the Constraint.

  3. Select the constraint that you want to drop from the Constraint drop-down list.

    To view the SQL statement that will be used to drop the constraint, choose the SQL tab of the Drop dialog box.

  4. Click Apply.

    The Confirmation dialog box displays indicating that the constraint has been dropped.

  5. Click OK.

    The constraint has been successfully dropped.

Working with PL/SQL

In SQL Developer you can work with PL/SQL. This section includes these topics:

Create a PL/SQL package

You can create a PL/SQL package for the TimesTen database.

To create a PL/SQL package, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Right-click the Packages option and select New Package.

    The Create Package dialog displays. Locate the schema drop-down list.

  2. Expand the drop-down list labeled Schema and select the schema for which you want to create the PL/SQL package.
  3. In the Name text field, type a name for the PL/SQL package.
  4. Optionally, check the Add New Source In Lowercase checkbox to create the new source with lowercase text. If you select this option, the text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the package name, because PL/SQL is not case sensitive in its execution.
  5. Click OK.

    The Create Package dialog closes and the PL/SQL package worksheet displays. Locate the PL/SQL package worksheet.

  6. Define the PL/SQL package in the PL/SQL package worksheet.

    When you finish defining your PL/SQL package, you can compile and save the PL/SQL package. Locate the File menu in the top-left corner of the SQL Developer window.

  7. From the File menu, select Save.

    SQL Developer compiles and saves your PL/SQL package. Review the Messages - Log area below the PL/SQL package worksheet to see the compilation results. If you see any compilation errors, troubleshoot and fix the compilation errors. Then, recompile and save your PL/SQL package.

You have successfully compiled and saved a PL/SQL package. You are now ready to specify a PL/SQL package body.

Define the body of a PL/SQL package

Before you define the body of a PL/SQL package, ensure that you have defined a PL/SQL package.

To define the body of a PL/SQL package, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Click + to the left of the Packages node to view a list of PL/SQL packages.
  2. Right-click the name of your PL/SQL package and select Create Body.

    The PL/SQL package body worksheet displays. Locate the PL/SQL package body worksheet.

  3. Define the body of you PL/SQL package in the PL/SQL package body worksheet.

    When you finish defining the body of your PL/SQL package, you can compile and save the body of your PL/SQL package. Locate the File menu in the top-left corner of the SQL Developer window.

  4. From the File menu, select Save.

    SQL Developer compiles and saves the body of your PL/SQL package. Review the Messages - Log area below the PL/SQL package body worksheet to see the compilation results. If you see any compilation errors, troubleshoot and fix the compilation errors. Then, recompile and save your PL/SQL package.

You have successfully compiled and saved the body of a PL/SQL package. You are now ready to run your PL/SQL package.

Run a PL/SQL package

To run a PL/SQL package, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Click + to the left of the Packages node to view a list of PL/SQL packages.
  2. Right-click the name of your PL/SQL package and select Run.

    The Run PL/SQL dialog displays. The Run PL/SQL dialog contains three panes:

    • Target: This pane shows all the functions and procedures of your PL/SQL package.

    • Parameters: This pane shows a list of parameters for the selected function or procedure.

    • PL/SQL Block: This pane shows the PL/SQL code that SQL Developer generates to run the selected function or procedure.

    In the Target pane, locate the function or procedure that you want to run.

  3. In the Target pane, select the function or procedure that you want to run.

    The Parameters and PL/SQL Block panes update with information that is relevant to the function or procedure that you selected.

  4. In the Parameters pane, enter values for all the parameters that are marked as IN mode.
  5. In the PL/SQL block, review the PL/SQL code and make any necessary changes to run your PL/SQL function or procedure.
  6. Click OK.

    TimesTen runs your PL/SQL procedure or function. Locate the Output Variables tab at the bottom of the main SQL Developer page.

  7. Select the Output Variables tab.

    The Output Variables tab displays. Review the Output Variables to see the variable results from the PL/SQL procedure or function.

You successfully ran a PL/SQL package.

Compile PL/SQL packages

Before you compile a PL/SQL package, ensure that you have created a PL/SQL package and defined the body of that PL/SQL package. You should compile a PL/SQL package after you edit any part of your PL/SQL package or package body.

You can either compile a specific PL/SQL package or compile all of your saved PL/SQL packages.

Compile a specific PL/SQL package

To compile a specific PL/SQL package, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Click + to the left of the Packages node to view a list of PL/SQL packages.
  2. Right-click the name of your PL/SQL package and select Compile.

    SQL Developer compiles and saves the body of your PL/SQL package. Review the Messages - Log area to see the compilation results. If you see any compilation errors, troubleshoot and fix the compilation errors. Then, recompile and save your PL/SQL package.

You have successfully compiled a PL/SQL package.

Compile all of your PL/SQL packages

To compile all of your saved PL/SQL packages, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Right-click the Packages option and select Compile All.

    The Compile All dialog displays. Locate the Apply button.

  2. Click Apply.

    TimesTen compiles all of your saved PL/SQL packages. A confirmation dialog displays with the compilation results and any errors that TimesTen encountered.

  3. Click OK.

    Review the Messages - Log area to see the compilation results. If you see any compilation errors, troubleshoot and fix the compilation errors. Then, recompile and save your PL/SQL packages.

You have successfully compiled all of your saved PL/SQL packages.

Compile invalid PL/SQL packages

To compile all of your invalid PL/SQL packages, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Right-click the Packages option and select Compile Invalid.

    The Compile Invalid dialog displays. Locate the Apply button.

  2. Click Apply.

    TimesTen compiles all of your invalid PL/SQL packages. A confirmation dialog displays with the compilation results and any errors that TimesTen encountered.

  3. Click OK.

    Review the Messages - Log area to see the compilation results. If you see any compilation errors, troubleshoot and fix the compilation errors. Then, recompile and save your PL/SQL packages.

You have successfully compiled all of your invalid PL/SQL packages.

Drop a PL/SQL package

To drop a PL/SQL package, ensure that you are on the main SQL Developer page and that your TimesTen connection is expanded.

  1. Click + to the left of the Packages node to view a list of PL/SQL packages.
  2. Right-click the name of your PL/SQL package and select Drop Package.

    The Drop Package displays. Review that the Owner and Name fields match the information of the PL/SQL package that you want to drop. Locate the Apply button.

  3. Click Apply.

    TimesTen drops your PL/SQL package and SQL Developer displays a confirmation dialog.

  4. Click OK.

    The confirmation dialog closes.

You have successfully dropped a PL/SQL package.

Use a REF CURSOR as an OUT parameter

You can test a PL/SQL function, procedure, or package by defining a REF CURSOR as an OUT parameter in your PL/SQL function, procedure, or package. After you define a REF CURSOR, compile and run your PL/SQL function, procedure, or package. The Run dialog appears and when you choose OK, the details of the run are displayed and the output from the execution of the function, procedure, or package is displayed in the Output Variables tab.

The following example creates a package called get_emp_pkg. The get_emp_pkg package defines a REF CURSOR as an OUT parameter and defines a procedure that uses the REF CURSOR as an OUT parameter.

Figure 4-6 get_emp_pkg definition

Description of Figure 4-6 follows
Description of "Figure 4-6 get_emp_pkg definition"

Next create a package body that defines the procedure get_emp. The procedure get_emp opens the REF CURSOR variable and performs a query on the employees table.

Figure 4-7 get_emp_pkg package body definition

Description of Figure 4-7 follows
Description of "Figure 4-7 get_emp_pkg package body definition"

Compile the package and package body. Right-click on the get_emp_pkg and choose Run. The Run PL/SQL dialog displays allowing you to run the test wrapper. Choose OK to run the test wrapper.

Figure 4-8 Viewing the Run PL/SQL dialog

Description of Figure 4-8 follows
Description of "Figure 4-8 Viewing the Run PL/SQL dialog"

At the bottom of SQL Developer, you see the following tabs:

  • Messages: Displays the status of all of your compilations and if any errors occurred.

  • Logging Page: Displays all errors that have occurred.

  • IdeConnections: Displays the status of your run including any errors encountered.

  • Output Variables: Shows the output from the execution of the function, procedure, or package.

Working with sequences

You can work with sequences for TimesTen and TimesTen Scaleout databases. For more information on sequences, see "CREATE SEQUENCE" in the Oracle TimesTen In-Memory Database SQL Reference.

This section includes these topics:

Create a sequence

You can create a sequence for the TimesTen database or TimesTen Scaleout database.

To create a sequence, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Right-click the Sequences option and select New Sequence.

    The Create Sequence dialog displays. Locate the Schema drop-down list.

  2. From the Schema drop-down list, select the owner of the sequence.
  3. In the Name text field, type a name for the sequence.
  4. In the Start With text field, specify the first sequence number to be generated.

    Use this field to start an ascending sequence at a value that is greater than the minimum value or to start a descending sequence at a value less than the maximum.

  5. In the Increment text field, specify the incremental value between consecutive numbers.

    This value can be either a positive or negative integer. It cannot be 0. If the value is positive, it is an ascending sequence. If the value is negative, it is descending. The default value is 1.

  6. In the Min Value text field, specify the minimum value for an ascending sequence, or the final value for a descending sequence.

    This value must be less than or equal to the value that you specified in the Start With field. The default minimum value is 1.

  7. In the Max Value text field, specify the largest possible value for an ascending sequence, or the starting value for a descending sequence.

    This value must be greater than or equal to the value that you specified in the Start With field. The default maximum value is (263) -1, which is the maximum value of the BIGINT data type.

  8. If you want to cache the numbers from this sequence, select the Cache option from the Cache drop-down list. If not, select the No Cache option.
  9. If you selected the Cache option from the Cache drop-down list, in the Cache Value text field, specify the range of numbers that are cached.

    When a restart occurs, unused cached numbers are lost. If you specify a cache value of 1, then each use of the sequence results in an update to the database. Larger cache values result in fewer changes to the database and less overhead. The default value is 20.

  10. From the Cycle drop-down list, select one of these options:

    • Cycle: The sequence number generator continues to generate numbers after it reaches the maximum or minimum value.

    • No Cycle: The sequence number generator stops generating numbers when the maximum or minimum value is reached.

    By default, sequences do not cycle. Once the number reaches the maximum value in the ascending sequence, the sequence wraps around and generates numbers from its minimum value. For a descending sequence, when the minimum value is reached, the sequence number wraps around, beginning from the maximum value.

  11. In the Batch value text field, define the range of unique sequence values that are stored at each element of the grid. The default value is 10 million. This option is only available for TimesTen Scaleout databases.

    Locate the OK button.

    Note:

    If you want to review the DDL statement that TimesTen or TimesTen Scaleout uses to create your sequence, click the DDL tab.

  12. Click OK.

    The Create Sequence dialog closes and TimesTen or TimesTen Scaleout creates your sequence.

You have successfully created a sequence.

Alter a sequence

You can alter the batch value of an existing sequence of a TimesTen Scaleout database.

To alter a sequence, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Expand the Sequences option, right-click the name of the sequence, then select Edit.

    The Edit Sequence dialog displays. Locate the Batch Value text field.

  2. In the Batch Value text field, type the new batch value for your sequence.

    Locate the OK button.

  3. Click OK.

    The Edit Sequence dialog closes.

You have successfully edited the batch value of an existing sequence.

Drop a sequence

You can drop a sequence of the TimesTen database or TimesTen Scaleout database.

To drop a sequence, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Expand the Sequences option, right-click the name of the sequence, then select Drop.

    The Drop dialog displays. Locate the Apply button.

  2. Click Apply.

    The Drop dialog closes and a Confirmation dialog closes. Locate the OK button.

  3. Click OK.

    The Confirmation dialog closes.

You have successfully dropped a sequence from your database.

Working with views

You can work with views for the TimesTen and TimesTen Scaleout databases. For more information on views, see "Understanding views" in the Oracle TimesTen In-Memory Database Operations Guide.

This section includes these topics:

Create a view

You can create a view for the TimesTen database or TimesTen Scaleout database.

To create a view, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Right-click the Views option and select New View.

    The New View dialog displays. Locate the View Name text field.

  2. In the View Name text field, type a name for the view.
  3. In the SQL text field, type the SQL query for the view.
  4. Click Apply.

    The New View dialog closes and a Confirmation dialog displays.

  5. Click OK.

    The Confirmation dialog closes and TimesTen creates your view.

You have successfully created a view.

Drop a view

You can drop a view of the TimesTen database or TimesTen Scaleout database.

To drop a view, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Expand the Views option, right-click the name of the view, then select Drop.

    The Drop dialog displays. Locate the Apply button.

  2. Click Apply.

    The Drop dialog closes and a Confirmation dialog closes. Locate the OK button.

  3. Click OK.

    The Confirmation dialog closes.

You have successfully dropped a view from your database.

Working with materialized views

You can work with materialized views for the TimesTen and TimesTen Scaleout databases. For more information on views, see "Understanding materialized views" in the Oracle TimesTen In-Memory Database Operations Guide.

This section includes these topics:

Create a materialized view

You can create a materialized view for the TimesTen database or TimesTen Scaleout database.

To create a materialized view, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Right-click the Materialized Views option and select New Materialized View.

    The New Materialized View dialog displays. Locate the Materialized View Name text field.

  2. In the Materialized View Name text field, type a name for the view.
  3. In the SQL text field, type the SQL query for the view.
  4. Click Apply.

    The New Materialized View dialog closes and a confirmation dialog displays.

  5. Click OK.

    The Confirmation dialog closes and TimesTen creates your view.

You have successfully created a materialized view.

Drop a materialized view

You can drop a materialized view of the TimesTen database or TimesTen Scaleout database.

To drop a view, ensure that you are on the main SQL Developer page and that your TimesTen or TimesTen Scaleout connection is expanded.

  1. Expand the Materialized Views option, right-click the name of the materialized view, then select Drop.

    The Drop dialog displays. Locate the Apply button.

  2. Click Apply.

    The Drop dialog closes and a Confirmation dialog closes. Locate the OK button.

  3. Click OK.

    The Confirmation dialog closes.

You have successfully dropped a materialized view from your database.

Locating a TimesTen database object

If you are unable to find a database object, right-click the node corresponding to the object type and select Refresh to refresh the list of objects of that type.

For example, to find a cache group, right-click the Cache Groups node and select Refresh to refresh the list of cache groups.

You can also check if the object appears under the Other Users node. Click the + to the left of the Other Users node to expand the node. Then click the + to the left of any of the users' nodes to expand that user's node.

Click the + to the left of the node corresponding to the object type to view the list of objects of that type owned by the user.

For example, to find a table, click the + to the left of the user's Tables node to view the list of tables owned by that user.

Granting and revoking object privileges

You can grant privileges to and revoke privileges from the following database objects:

  • Regular and cache tables

  • Regular and materialized views

  • Sequences

  • PL/SQL packages, procedures and functions

To grant privileges on an object, right-click the name of the object and select Privileges, then select Grant.

In the Users drop-down menu of the Grant dialog box, select the user to grant object privileges to the object. In the Privileges section, select the All check box or click >> to grant all available object privileges on the object to the selected user. Otherwise, select the individual privileges from the Available Privileges list for the privileges to grant to the selected user and then click > to move those privileges into the Selected Privileges list. To select multiple privileges, press and hold the CTRL key, and click the desired privileges. Click Apply to grant the selected object privileges on the object to the selected user.

For a particular object type, only the available object privileges are shown. For example, DELETE, INDEX, INSERT, REFERENCES, SELECT and UPDATE privileges can be granted on a table. However, only INDEX, REFERENCES and SELECT privileges can be granted on a materialized view.

Only users with the ADMIN system privilege or the owner of an object can grant object privileges on the object.

For more information about the set of privileges available to each type of object, see "Object privileges" in the Oracle TimesTen In-Memory Database SQL Reference.

To revoke privileges from an object, right-click the name of the object and select Privileges, then select Revoke.

In the Users drop-down menu of the Revoke dialog box, select the user to revoke object privileges from the object. In the Privileges section, select the All check box or click >> to revoke all granted object privileges on the object from the selected user. Otherwise, select the individual privileges from the Available Privileges list for the privileges to revoke from the selected user and then click > to move those privileges into the Selected Privileges list. To select multiple privileges, press and hold the CTRL key, and click the desired privileges. Click Apply to revoke the selected object privileges on the object from the selected user.

The Users drop-down menu shows only users that have object privileges on the object. For a particular user, only the object privileges that they have been granted are shown.

Only users with the ADMIN system privilege or the owner of an object can revoke object privileges from the object.

Viewing the privileges granted on a database object

You can view the object privileges granted to all users on a particular object such as a regular table or a cache table, a regular view or a materialized view, a sequence, or a PL/SQL package, procedure or function.

For example, to view the privileges granted on a regular table, click the + to the left of the user's Tables node to view the list of tables owned by that user and then click the name of the desired table.

The Grants tab, located within the table tab, shows the users who have privileges on the table, what privileges they have been granted, and the user who granted the privileges.

ttIsql commands in SQL worksheet

The following ttIsql commands are supported in the SQL Worksheet:

  • autocommit

  • desc

  • dssize

  • version

For more information, see "ttIsql" in the Oracle TimesTen In-Memory Database Reference.