to reset your current sequence to value 1 follow the following steps:
we will assume the sequence name is SQ_CURR
1. Search for the current sequence value via the command:
SQL> select SQ_CURR.currval from dual;
2. Then change the sequence by adding the negative value of your previous result but subtract and addition one so for example if the value was 133389 you will increment it by -133388
SQL> ALTER SEQUENCE SQ_CURR INCREMENT by -133388;
3. Then run the command that will reset the sequence:
SQL> SQ_CURR.NEXTVAL SELECT FROM DUAL;
4. To restore the increment of the sequence:
SQL> ALTER SEQUENCE SQ_CURR INCREMENT by 1;
hope this helps ….cheers!
the “resource” is a pre-defined oracle database system role that consists of the following permissions:
starting from Oracle 12cR1 this role doesn’t have “UNLIMITED TABLESPACE” system privilege, which is considered a security enhancement so the database user account won’t have any unlimited access to all tablespaces within the database. The DBA will be required to manually grant the specific tablespace quota to the user.
to revoke a tablespace quota from an oracle database user, you can use the command as follows:
ALTER USER DB_USER QUOTA 0 ON TABLESPACE_NAME;
DB_USER is the oracle database user.
TABLESPACE_NAME is the tablespace name that you want to revoke quota from.
Hope This Helps ! 🙂
In oracle database 12c release, you can use the package DBMS_QOPATCH to find information about patches currently applied in your database.
this package can only be executed by “sys” user account.
examples of the package sub-functions used:
patchnum IN VARCHAR2);
*.* returns information about Patch ID, patch creation time.
*.* information about th patch and oracle inventory location.
*.* returns full patch inventory in XML format
Also, you can use the query:
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
in Oracle Database 12c, the following are the list of system administration accounts:
and the following is the list of Operating System Accounts (OS Accounts) and its corresponding Oracle 12c System Admin Accounts:
to view all accounts in your database and their status, use the following query:
ORDER BY username;
you can also use this query, from root container in 12c:
FROM cdb_users u
WHERE u.con_id = c.con_id
ORDER BY c.name, u.username;
The New Oracle Database release named “12c” is based on Multitenant Architecture. Moreover, you have the option either to upgrade with this architecture with CDB (Container Database) and PDB (Pluggable Databases) OR you can you use the traditional Oracle Database Architecture.
In this article I will talk about the overview of the architecture.
The 12c CDB database will consists of the following major elements:
CDB$ROOT, which has the data dictionary views for the root and all pluggable databases.
PDB$SEED is a template database that a DBA can use to create other databases.
PDB is a pluggable database, which you can have zero,one, or more within a CDB. The maximum number of PDB in one CDB is “252 pluggable databases”. Each pluggable database has “CON_ID” which is a unique container identification.
High level architecture overview:
CDB level Architecture Image:
The Following Points will describe different 12c CDB architecture information:
- Container Database(CDB$ROOT) will host all metadata for all PDB within CDB.
- Each PDB will have the following tablespaces: SYSTEM, SYSAUX. TEMP tablespace can be “optionally” created under a PDB, otherwise PDB will use the TEMP tablespace for the root container.
- In 12c Character Set is set at CDB level, and will be applied to all associated PDB.
- Using SYS to access root CDB, the DBA can stop/start CDB instance. This is not applicable if connecting to PDB.
- All pluggable databases use a common UNDO tablespace under root CDB.
- There is a centralized (common) CDB alert log, so all error/informative messages for each PDB are being written to one common alert log and set of trace files.
Final Remark: what I like about Multitenant Architecture from “Security” perspective is that applications will be deployed on database-level instead of a schema-level which gives you more “data isolation”, freedom of using public synonyms, provisioning, and portability.
In 11g if you use an excessive permission such as “SELECT ANY TABLE” the user can access the data from any schema within the database, which means other applications data. So when you deploy your application on database-level this is not applicable since all objects within the database is primarily owned by your application.
Oracle initialization file existence is a “mandatory” pre-requisite before starting up the database, otherwise you won’t be able to startup the database.
The initialization file is a configuration file used for example to adjust memory parameters and control file locations.
There are 2 types of initialization files:
- Server parameter file (spfile)
- Init.ora text file
The first type (spfile) can be used if you require to change the contents of the file using sql statement (ALTER SYSTEM) command. Also, if you use spfile you can remotely start the database from your client machine.
The second type (init.ora) text file can be edited using OS text editor, and you can add hash comments while performing modifications. I personally prefer using this type of initialization file.
The location of initialization of file is under $ORACLE_HOME/dbs directory
The init file would like the following:
spfile<SID>.ora OR spfile.ora OR int<SID>.ora
Where <SID> is the oracle database instance system id
In 11g and 12c set the memory_target and memory_max_target initialization parameters, this setting will enable automatic memory management.