To get the space taken up by all the objects in the schema, the following query can be used select ((sum(bytes)/1024)/1024)/1024 Space_in_GB from dba_segments where owner=’SCHEMA_NAME’
How to avoid ORA-01555 “snapshot too
old" error?
From the developer’s
perspective:
Restructure your PL/SQL code to
avoid fetching across commits that cause the ORA-01555 error.
One possible reason can be if we
leave the cursor open for fetching while
we are processing and committing data changes for a long time.
Installing sample schemas(HR,OE,PM etc…) in Oracle 10g R2
1. Get the companion disk for Oracle 10g from oracle.com
2. Unzip and extract the contents on the server
3. Run the Oracle Universal Installer to install the components, choose "Oracle database products"
A no. of errors are encountered while importing dump files in Oracle database.
Some of them are due to
1. Tablespace not found.
2. User/Role not found.
3. Incorrect database charset
If correct parameters are not provided to the person doing the import, it a more of HIT/TRIAL methdology.
Try using this query to identify the approach to attack invalid objects in the schema select referenced_name, count(referenced_name) cnt from user_dependencies where referenced_name in (select distinct object_name from user_objects where status = ‘INVALID’) group by referenced_name order by cnt desc This will list down the objects in descending order on which maximum no. of invalid objects … Read more
Not able to connect to the DB.
Error Messages encountered:
TNS-03505:
Failed to resolve name
ORA-12154:
TNS:could not resolve the connect identifier specified
The possible solutions can be
This TIP can be used to add a sequence in a column for a table The syntax for creating a sequence is CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; The steps are –create table with a column create table tickets3(s_no integer); — create a sequence , Option 1CREATE … Read more
How to use the copy command in Oracle SQL Syntax: COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, …)]USING query where database has the following syntax: username[/password]@connect_identifier Copies data from a query to a table in a local or remote database. COPY supports the following datatypes: … Read more