Oracle 11g Database Detailed

Common exceptions:

ORA-14025: Cannot specify partition for a solid view or a solid view log

Ora-14026:partition and cluster clauses repel each other

ORA-14027: Only one partition clause can be specified

ORA-14028: Missing at or values keywords

Ora-14029:global partition index must be prefixed

There are non-existent partition columns in the ora-14030:createtable statement

ORA-14031: The type of the partition column cannot be a long or longraw

ORA-14032: partition number partition limit is too high

ORA-14033:CTCHVL: Unexpected type of strdef

ORA-14034:CTCHVL: unexpected operand type

ORA-14035:CTCHVL: Unexpected string data type

ORA-14036: Column has a large partition limit value

ORA-14037: partition boundary is too high for partition ""

Ora-14038:global partition index must be prefixed

ORA-14039: A partition column must form a subset of the keyword columns of a unique index

ORA-14040: Improper number of arguments passed to Table$or$index$part$num

ORA-14041: May not specify partition bounds for result partition

ORA-14042: May not specify partition bounds for partitions to be moved, modified, or rebuilt

ORA-14043: Only one partition can be added

ORA-14044: Only one partition can be moved

ORA-14045: Only one partition can be modified

ORA-14046: partition can be divided into two new partitions exactly

ora-14047:altertable| Indexrename cannot be combined with other partitions

ORA-14048: Partition maintenance operations cannot be combined with other operations

ORA-14049: Invalid altertablemodifypartition option

ORA-14050: Invalid alterindexmodifypartition option

Ora-14051:altermaterializedview option is not valid

ORA-14052: Partition extension table name syntax is not allowed in this context

ORA-14053: illegal attempt to modify (in statement)

ORA-14054: Invalid altertabletruncatepartition option

The keyword rebuild in ora-14055:alterindexrebuild must follow the index name

ORA-14056: partition number: The sum of pctused and pctfree can not exceed 100

ORA-14057: Partition "": The sum of pctused and pctfree can not exceed 100

ORA-14058: Partition number: Initrans value must be less than Maxtrans value

ORA-14059: Partition "": Initrans value must be less than Maxtrans value

ORA-14060: You cannot change the data type or length of a table partition column

ORA-14061: You cannot change the data type or length of an index partition column

ORA-14062: One or more table partitions reside in a read-only table space

ORA-14063: A useless index exists in the unique/primary constraint keyword

ORA-14064: An index of a useless partition exists in the unique/primary constraint keyword

ORA-14065: You cannot specify the allocatestorage of a partitioned table

ORA-14066: The option to organize an unpartitioned table by index is illegal

ORA-14067: Repeated tablespace_number instructions

ORA-14068: Tablespace and Tablespace_number cannot be specified at the same time

ORA-14069: Invalid Tablespace_number value

ORA-14070: You can specify only the partition index or the option that contains the rebuild

ORA-14071: Invalid index option for enforcing constraint conditions

ORA-14072: Can not truncate fixed table

ORA-14073: You cannot truncate a bootstrapper table or cluster

ORA-14074: The partition bounds must be adjusted above the last partition boundary

ORA-14075: Partition maintenance operations can only be performed on partitioned indexes

Ora-14076:drop/splitpartition cannot be applied to local index partitions

ORA-14078: You can not delete the highest partition of the global index

ORA-14079: Invalid partition option marked as useless index

ORA-14080: Unable to split partition by specified upper limit

ORA-14081: The new partition name must be different from the old partition name

ORA-14082: The new partition name must be different from any other partition name of the object

ORA-14083: Unable to delete unique partition for partitioned table

ORA-14084: You can specify only the tablespacedefault of the local index

ORA-14085: Partition table cannot have a Long data type column

ORA-14086: The zone index cannot be rebuilt as a whole

The login verification of the general Rights user is the third way, that is, the database authentication, because the user name and password are stored in the database. However, SYS users (users with SYSDBA and Sysoper permissions) are not database-validated. When Oracle data is not started, the SYS user can connect to the database and start the operation, so it is not possible to verify the database. SYS users are using the first and second authentication methods.

1 Sqlplus/nolog,conn/as SYSDBA the ability to log in is the way Oracle is authenticated at logon. You can try this: Sqlplus/nolog,conn abcd/sss as SYSDBA, you find that you can also log in successfully. 2 Oracle at the time of login, there are three kinds of authentication methods: Operating system identity authentication, password file authentication, database authentication. And Conn/as SYSDBA is the operating system certification. Why do you say that? Your current computer is logged on when the user, that is, the user into the operating system, for example, Gooooal, it is in your computer's ORA_DBA group. You can right-click on my Computer, find admin, select Local Users and groups, find a group called ORA_DBA, double-click it, see Gooooal in the member list. That is, the Conn/as sysdba,oracle will be operating system authentication, found that you are currently logged on the user belongs to the ORA_DBA group, so you can log on successfully. You can remove the Gooooal users in the ORA_DBA group, and then Conn/as Sysdba, and find that they can't get in. 3 Password file verification can consult the relevant information, Baidu Oracle authentication method, you can find relevant information. 4 in the Linux or UNIX environment, the installation of Oracle is to set up a lot of things, such as user and user group settings, and so on, unlike in Windows, installation of a fool. 5 With regard to security, if you install Oracle locally, you are locally an Oracle server, so you have higher permissions. Which user do you use to launch the Oracle instance? Which user comes to the Mount database? Which user will open the database? In order to perform SQL operations such as select after? When Oracle instances are not yet started, the users built into the Oracle database must not be able to use them. That's why the operating system is authenticated. Use this user to launch Oracle instances, reprint databases, and so on. Then you can do the Oracle table additions and deletions.

3.2 sql*plus3.2.1 Windows operating system 3.2.1.1 connection database 3.2.1.2 disconnecting database 3.2.2 Linux operating system 3.2.2.1 connection database

To connect to a remote database:

1) Enter the shell command: Sqlplus username/password @ Connection descriptor. For example: Sqlplus test/[email protected], means that the connection descriptor is connected to the database using the test user name and the test password.

2) The operating system will automatically run the $oracle_home/bin/sqlplus executable according to the $PATH environment variable. If the $PATH environment variable or the $ORACLE_HOME environment variable is not correct, the Sqlplus command cannot be executed.

3) Sqlplus will automatically find the connection descriptor configured in the $oracle_home/network/admin/tnsnames.ora file.

4) Sqlplus will automatically connect to the remote database with the specified user name, password, and connection descriptor, and the SQL command interface SQL will appear when the connection succeeds.

Note: If the connection fails, check whether the remote database has been started and whether the remote listener service has started. The connection Descriptor profile Tnsnames.ora whether there are read permissions, whether the connection descriptor is configured correctly, whether the user name and password are correct, whether the password expires, whether the user has connect permissions, and so on.

To connect to the native database:

1) at the command line, enter: Sqlplus/as SYSDBA

2) The operating system will automatically run the $oracle_home/bin/sqlplus executable according to the $PATH environment variable.

3) The SQL interface will appear when the connection is successful.

Tip: The native database can be connected in a closed state with this method, but if you connect to the native database using the method of connecting to the remote database, the database must already be started.

3.2.2.2 Disconnecting a database

Enter SQL command: Exit or quit, which means break and exit sqlplus.

Enter SQL command: Disconnect, which indicates that only the sqlplus is disconnected, but not exited.

3.3 OEM3.3.1 Connection Database

Use the browser to open the OEM URL address, enter the database user name and password, click Login to complete the connection.

To view the OEM's URL address: view readme.txt under $oracle_home\install

3.3.2 Disconnecting a database

You can disconnect the database by clicking the Logout button in the upper-right corner of the page. You can log on again after disconnecting, or close the browser.

3.4 OCILIB3.4.1 Connecting and disconnecting databases

#include stdio.h #include "Ocilib.h" int main (int argc, char * * argv) { if (oci_initialize (null, NULL, oci_env_default) = = 0)//If the initialization of the Ocilib library fails, an error is { return-1; } Oci_connection * OCICONN; Ociconn = Oci_connectioncreate ("192.168.227.128", "Test", "Test", oci_session_default);//Connect to Database if (Ociconn = = 0)//If the connection to the database failed, the error { return-1; } Oci_connectionfree (ociconn);//Disconnect the database return 0; }

3.5 Tnsnames.ora Connection Descriptor configuration File Description 3.5.1 TNS Introduction

The TNS full name is transparence network substrate, which is a transparent net underlying, also known as a connection descriptor.

The Oracle database client knows how to connect to the database server through a connection descriptor.

3.5.2 Configuration file path

This profile is Tnsnames.ora under admin under Network under the Oracle Database installation path

3.5.3 File Format description

Represents a space, one can not be less, nor more

Represents the name of the connection descriptor, not case-sensitive

Represents the IP address of the database server

Represents the port number of the database server and is generally not changed

Indicates a direct connection to the database server, generally do not change

Represents the database service name, using SQL show parameter service_name; command view

Note: When you configure multiple connection descriptors, a blank line is spaced between each one.

3.6 Sqlnet.ora Connection Method Profile Description 3.6.1 Introduction

This configuration file is primarily about how the metabase client connects to the database server side. For example, configure connection time-outs, receive data timeouts, send data timeouts, server lookups, and so on.

3.6.2 Configuration file path

This profile is Sqlnet.ora under admin under Network under the Oracle Database installation path

3.6.3 file Format description

Sqlnet. Recv_timeout = 1//Configure receive data timeout, unit seconds

Sqlnet. Send_timeout = 1//Configure Send data time-out, unit seconds

Sqlnet. Inbound_connect_timeout = 1//Configure connection timeout, in seconds

Sqlnet_allowed_logon_versions = (11,10,9,8)//Allow connection of database server version number

3.7 Listener.ora Monitoring Service profile Description 3.7.1 Introduction

This configuration file is mainly configured to configure the database server monitoring service.

3.7.2 Configuration file path

This profile is Listener.ora under admin under Network under the Oracle Database installation path

3.7.3 file Format description

No.

3.8 Setting up ODBC access to Oracle database under Windows

If the Oracle database is installed on a server with IP 192.168.0.20, the instance name is "Mytestinstance" and the user Mytestuser and password password1 are added for use as an example. Multiple instances can be configured on the server, and each instance is a logically separate database.

A client is a computer that requires access to the Oracle database on the server, which can be the same machine as the servers, or any other computer on which the network can access the server.

The following discusses how to configure Oracle 9i ODBC on a Windows client computer to access a remote Oracle 9i database.

The principle of access to Oracle via ODBC:

Client program----ODBC----Oracle OCI------Oracle Server

First, install an Oracle OCI (Oracle call Interface) on the client, which is a component that really directly delivers data to and from a remote Oracle server. In order to access the Oracle database in a unified manner using ODBC, you must also install an Oracle ODBC driver. It receives database access requests from the client and translates ODBC-type requests into requests for Oracle OCI. The OCI implements the manipulation of the Oracle database and returns the query results or other results to the ODBC driver, Odbcdriver finally returning the results to the client program. In other words, the client accesses the database through ODBC, while ODBC implements its functionality by invoking the OCI API.

Based on the server settings just described, the configuration Steps for client Oracle ODBC are given below.

Step 1: Install the Oracle Client Runtime Library, which includes Oracle OCI.

Step 2: Set up OCI access information

OCI access to Oracle server needs to know

1, server IP address or host name, (host name)

2, the server's listening port, (port number)

3, the name of the database instance you want to access. (Service ID/service name)

The data source name (service naming) is the name of the set of information that consists of these three messages.

This information can be set through the Oracle Net Manager. The nouns used in its settings interface are listed in the corresponding parentheses respectively.

This example is set as follows:

Service naming: ABCD (random string)

Host Name: 192.168.0.20

Port number: 1521 (must be consistent with server side, here is the default value)

Service ID/Service name: mytestinstance

After Oracle ODBC driver is only required to give the OCI service a name (in this case, ABCD), OCI knows how to access the real database.

Step 3: Set up an ODBC DSN, which is an ODBC data source

Go to Control Panel/Administrative Tools/Data source (ODBC)/System DSN

Click Add, select MicroSoft ODBC for Oracle, and then pop up a dialog box to fill in the following.

1, data source name: MYORACLESRC can be casually up, the client program will use it.

2, Description: A description of the text, you can read it.

3, User name: Mytestuser the user name recognized by the database that you end up needing access to.

4, Server: ABCD (that is, service naming in OCI settings. This is the most misleading place in the Chinese version. I began to mistakenly think of the Oracle server to fill in the hostname)

Step 4: How client programs access the database through ODBC

The client needs three information when accessing the database:

1, data Source: MYORACLESRC, which is the data source name set in the ODBC DSN

2, user name: Mytestuser

3, Password: password1

This allows access to the database mytestinstance on the 192.168.0.20.

To verify that the ODBC settings were successful, use the DB Query Analyzer trial version of step 4来 for a simple test.

4 View the database service name, instance name, port number 4.1 sql*plus

If you are unable to connect to the database, it is recommended that you connect using a native database connection.

View Service Name:

Under SQL Input: select global_name from Global_name;

Enter in sql: Show parameter service_name;

To view the instance name:

Under SQL Input: Select instance_name from V$instance;

To view the port number:

5 Manage Database Connections 5.1 View database connections

The V$session table stores all the database connection information and uses the Select command to view the data for the tables. which

Username field: The database user name used by this connection.

Sid Field: The session ID of this connection.

Machine field: The remote host name for this connection.

Port field: The remote port number for this connection.

Note: This view requires DBA authority to view this table.

6 Database Users

Each user of the Oracle database corresponds to a user profile that controls some of the user's behavior, permissions, and resource usage.

The general user is the default user profile, or you can modify the corresponding user profile file name.

Oracle database also has a user profile whether the switch is active, if TRUE indicates that the user profile is turned on, if False indicates that the user profile is closed, but some configuration in the user profile is not controlled by this switch, always on (see 4.1.4.5).

6.1 Creating a database user

Note: The new user must have the Connect or DBA system permissions assigned before connecting remotely.

6.1.1 Sql*plus

Under SQL Input: Create user username identified by password;

6.2 Deleting a database user

Note: Users who are currently connected cannot be deleted.

6.2.1 Sql*plus

Under SQL Input: Drop user username cascade;

6.3 Modifying the password of the database user 6.3.1 Sql*plus

Under SQL input: Alter USER username identified by "New password";

6.4 Database user Rights 6.4.1 user rights Introduction

There are two kinds of permissions, one is System permission and one is Object permission.

system permissions are defined by the database system for users to use the database permissions, permissions acting on the user.

Only users with DBA authority can grant or revoke system permissions to users.

The System_privilege_map table has all the permissions built into the Oracle database system.

There are several system permissions:

Create cluster:

CREATE PROCEDURE:

Create sequence:

CREATE TABLE:

CREATE VIEW:

CREATE TRIGGER:

Create type:

Create session: Can be created sessions, that is, allow the user to log in, without this permission can not.

Alter session:

create synonym:

Create Database link: The private dblink can be created, and the private Dblink only created by the user is available.

Create Public Database Link: Can create global dblink, global Dblink All users are available.

The following is a collection of permissions:

DBA: All privileges, which are the highest system permissions, can create a database structure without this permission.

Resource: Allows users to create a collection of permissions for various objects, including: Create cluster, create procedure, create sequence, create TABLE, create TRIGGER, Create type.

Connect: A set of permissions that allow users to log on remotely, including: Alter session, create cluster, CREATE database link, create sequence, create session, Create synonym, create view.

Oracle Classification of system permissions

Cluster permissions:

Create CLUSTER: Creates, changes, and deletes a cluster in its own scenario.

Create any CLUSTER: Creates a cluster in any scenario.

Alter any CLUSTER: Change the cluster in any scenario.

Drop any CLUSTER: Remove the cluster in any scenario.

Database permissions:

ALTER DATABASE: Run the ALTER DB statement to change the configuration of the databases.

Alter system: Run the ALTER SYSTEMS statement and change the initialization parameters

AUDIT system: Run the AUDIT system and Noaudit system statements to audit SQL.

AUDIT any: Runs the AUDIT and Noaudit statements to audit the objects of any scenario.

Index permissions:

Create any index: indexes are created in any scenario.

Note: Without the CREATE INDEX permission, the CREATE TABLE permission contains the CREATE INDEX permission

Alter ANY index: changes the index in any scenario.

Drop any index: delete the indexes in any scenario.

Procedure permissions:

Create PROCEDURE: Creates, changes, or deletes procedures, functions, and packages in your own scenario.

Create any PROCEDURE: Creates procedures, functions, and packages in any scenario.

Alter ANY PROCEDURE: changes procedures, functions, and packages in any scenario.

Drop any PROCEDURE: Removes a procedure, function, or package from any scenario.

Execute any PROCEDURE: executes or references the procedure in any scenario.

Profile permissions:

Create profile: Creating profiles.

Alter profile: Change profiles.

Drop profile: Deletes the profiles.

Role Permissions:

Create role: Creating roles.

Alter ANY role: change any roles.

Drop any role: removes any roles.

Grant any role: grants any roles to other roles or users.

Note: There is no corresponding revoke any role permission.

Fallback segment Permissions:

Create ROLLBACK SEGMENT: Creates a fallback segment.

Note: There is no permission to revoke a segment.

Alter ROLLBACK SEGMENT: Change the fallback segment.

Drop ROLLBACK SEGMENT: Deletes the fallback segment.

Sequence permissions:

Create Seqlence: Creates, changes, deletes, and selects a sequence in its own scenario.

Create any SEQUENCE: Creates a sequence in any scenario.

Alter any SEQUENCE: changes the sequence in any scenario.

Drop any SEQUENCE: Deletes the sequence in any scenario.

Select any SEQUENCE: Choose from any sequence in any scenario.

Session Permissions:

Create session: Creates sessions and logs into (connect to) the database.

Alter session: Run the ALTER sessions statement to change the properties of the conversation.

Alter RESOURCE cost: Change the way in which compute resources are consumed in the profile.

RESTRICTED session: Connect to data while the database is in restricted sessions mode.

Synonym permissions:

Create synonym: Creates and removes synonyms in your own scenario.

Create any synonym: Creates a private synonym in any scenario.

Create public synonym: Creates a common synonym.

Drop any synonym: Removes synonyms in any scenario.

Drop public synonym: Removes a common synonym.

Table permissions:

CREATE TABLE: Creates, changes, and deletes tables in your own scenario.

Create any table: Creates a table in any scenario.

Alter any-table: Change the table in any scenario.

Drop any table: Deletes the table in any scenario.

COMMENT any table: add comments to any tables, views, or columns in any scenario.

Select any table: Selects records from any table in any scenario.

Insert any table: Inserts a new record into any table in any scenario.

UPDATE any table: changes the records in any table in any scenario.

Delete any table: Deletes records from any table in any scenario.

Lock any table: Locks any tables in any scenario.

FLASHBACK Any table: allows you to use the As of clause to perform a flashback query of an SQL statement for a table or view in any scenario.

Table Space Permissions:

Create Tablespace: Creates a table space.

Alter tablespace: Change table space.

Drop tablespace: Deletes a table space, including tables, indexes, and tablespace clusters.

MANAGE tablespace: Manage tablespaces so that tablespaces are in online, OFFLINE (offline), begin backup (start back up), end backup (back up) status.

UNLIMITED tablespace: Use table spaces without quota restrictions.

Note: Unlimited tablespace can only be granted to an account and cannot be granted a role.

User rights:

Create User: Creates users.

Alter User: Change users.

Become user: Becomes another user when execution is fully loaded.

Drop User: Delete users.

View permissions:

Create VIEW: Creates, changes, and deletes views in your own scenario.

Create any view: Creates a view in any scenario.

Drop any view: Deletes the view in any scenario.

COMMENT any table: add comments to any tables, views, or columns in any scenario.

FLASHBACK Any table: allows you to use the As of clause to perform a flashback query of an SQL statement for a table or view in any scenario.

Trigger Permissions:

Create TRIGGER: Creates, changes, and deletes triggers in your own scenario.

Create any TRIGGER: Creates a trigger in any scenario.

Alter any TRIGGER: Change the trigger in any scenario.

Drop any TRIGGER: delete the trigger in any scenario.

Administer database TRIGGER: Allows creation of on database triggers. Before you can create an on database trigger, you must first have create trigger or create any trigger permissions.

Private permissions:

Sysoper

(System Operator privileges)

STARTUP

SHUTDOWN

ALTER DATABASE Mount/open

ALTER DATABASE BACKUP Controlfile

ALTER DATABASE begin/end BACKUP

ALTER DATABASE ARCHIVELOG

RECOVER DATABASE

RESTRICTED SESSION

CREATE Spfile/pfile

SYSDBA

(System administrator privileges)

Sysoper all permissions, with the WITH ADMIN option clause

CREATE DATABASE

RECOVER DATABASE UNTIL

Additional permissions:

ANALYZE any: Executes the ANALYZE statement on any table, cluster, or index in any scenario.

Grant any object PRIVILEGE: Grants object permissions on any object on any scheme.

Note: There is no corresponding revoke any OBJECT PRIVILEGE.

Grant any PRIVILEGE: Grants any system permissions.

Note: There is no corresponding revoke any PRIVILEGE.

Select any DICTIONARY: Allows you to select from a table of data dictionaries owned by the SYS user.

object permissions are permissions that users access to tables or views of other users, and Permissions act on the table or view under the user.

Object permissions are available in the following ways:

Select: You can query the table data.

Update: The table data can be updated.

Insert: You can insert table data.

Delete: You can delete table data.

alter: The table can be modified.

Index: You can create indexes.

All: All permissions.

Execute: The stored procedure can be executed.

permission passing means that the user who is given permission can also give this permission to other users, adding the WITH ADMIN option option.

6.4.2 granting system Permissions 6.4.2.1 Sql*plus

Under SQL Input: Grant system permissions to user name;

Allow Delivery permissions: Enter under SQL: Grant system permissions to user name with admin option;

Note: Multiple permissions are separated by commas.

6.4.3 Retract system permissions

If you use the WITH ADMIN option to give a user transitive system permissions, and then no longer have permission to pass, the same permissions will not be retracted for all users who have been given the same permissions by the user.

There is no cascade of system permissions, that is, a grants B permission, b grants C permissions, if a reclaims the permissions of B, the permissions of C are not affected, and system permissions can be reclaimed across users, that is, a can directly reclaim the rights of C users.

6.4.3.1 Sql*plus

Under SQL Input: Revoke system permissions from user name;

Note: Multiple permissions are separated by commas.

6.4.4 Viewing system permissions 6.4.4.1 Sql*plus

To see what system permissions the current user has:

Under SQL Input: SELECT * from Session_privs;

See where all users have permissions:

SQL select * from Dba_role_privs;

SQL select * from Dba_sys_privs;

SQL select * from Role_sys_privs;

6.4.5 granting object Permissions 6.4.5.1 Sql*plus

Under SQL Input: Grant object permissions on table or view name to user name;

Note: Multiple permissions are separated by commas.

6.4.6 Revoke object permissions

If you use the WITH ADMIN option to give a user transitive object permissions, you can no longer use permission delivery when you retract, and the same permissions are also being cascaded back to all users who have been given the same permissions by that user.

Object permissions are cascading, that is, a grants B permission, b grants C permissions, and if a reclaims the permissions of B, the permissions of C are retracted, and object permissions can be reclaimed across users, that is, a can reclaim the rights of C users directly.

6.4.6.1 Sql*plus

Under SQL Input: Revoke object permissions on table or view name to user name;

Note: Multiple permissions are separated by commas.

6.4.7 Viewing object permissions 6.4.7.1 Sql*plus

Tables that the user can query:

Under SQL Input: Select owner, table_name from All_tables;

User-Created tables:

Under SQL Input: Select table_name from User_tables;

Access-Accessible tables (permissions granted to objects):

Under SQL input: Select grantor, TABLE_SCHEMA, table_name, privilege from All_tab_privs;

Grant permission to the table (object permissions):

Under SQL Input: Select grantee, owner, TABLE_NAME, privilege from User_tab_privs;

6.5 Locking Database users

Under SQL input: Alter user username account lock;

Note: This command can only be performed by a user with DBA authority.

Each database user can be locked, locked after the user will not be able to login, unlocked and can continue to log on. When locked, this user is prompted to ORA-28000 the error.

If all DBA authority users are locked out, execute the unlock command after logging in to the native database with Sqlplus.

6.5.1 Viewing database user lockout time

Under SQL Input: Select username,lock_date from dba_users where username= ' username uppercase ';

Note: You can view the Listener.log log for this time period of the Oracle server to see what causes the lock.

6.6 Unlocking Database Users

Under SQL input: Alter user username account unlock;

Note: This command can only be performed by a user with DBA authority.

6.7 User Profile 6.7.1 action User profile effective switch

View: Enter in sql: Show parameter resource;

Name is the value of the corresponding value column of the Resource_limit row, and True indicates on, false to close.

Open: Under SQL input: Alter system set resource_limit=true;

OFF: Input under SQL: Alter system set RESOURCE_LIMIT=FALSE;

Note: After modifying the user profile effective switch, you do not need to restart the database to take effect in real time.

6.7.2 Creating user Profiles

Under SQL Input: Create profile User Configuration file name limit;

6.7.3 View the user profile name for the database user

Under SQL Input: Select Username,profile from dba_users where username= ' username uppercase ';

The profile column corresponds to the user configuration name for this user.

To view the user profile name for all users:

Under SQL Input: select username,profile from Dba_users;

6.7.4 Modifying the user profile name of the database user

Under SQL, enter: Alter User user name profile file name;

6.7.5 Viewing user profile resources

Under SQL Input: SELECT * from dba_profiles where profile= ' user profile file name uppercase ';

For example: SELECT * from dba_profiles where profile= ' default ' means to view the default user profile.

Description of the resources in the configuration file:

Oracle 11g Database Detailed