Tuesday, October 28, 2008

ORA-00059: maximum number of DB_FILES exceeded

You do not need to recreate control file. See below:

SQL> sho user
SYS
SQL> select * from v$version;

BANNER
\----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> sho parameter db_files

NAME TYPE VALUE
\------------------------------------ ----------- \------------------------------
db_files integer 200
SQL> alter system set db_files = 256 scope = spfile;

SQL> shutdown immediate
SQL> startup
SQL> sho parameter db_files

NAME TYPE VALUE
\------------------------------------ ----------- \------------------------------
db_files integer 256

Wednesday, October 8, 2008

Oracle Data Dictionary Views

View Name Assoc. Views Description
DBA_2PC_NEIGHBORS

Describes incoming and outgoing connections for pending transactions.
DBA_2PC_PENDING

Describes distributed transactions awaiting recovery.
DBA_ALL_TABLES ALL_ USER_ Displays descriptions of all object tables and relational tables in the database.
DBA_ANALYZE_OBJECTS

Lists objects that have been analyzed. (8i view only?) see DBA_OBJECTS?
DBA_APPLICATION_ROLES

Describes all the roles that have authentication policy functions defined.
ARGUMENTS ALL_ USER_ Lists all arguments of procedures and functions that are accessible to the current user.
DBA_ASSOCIATIONS ALL_ USER_ Describes all user-defined statistics in the database.
DBA_BASE_TABLE_MVIEWS ALL_ USER_ Describes all materialized views using materialized view logs in the database.
DBA_BLOCKERS

Displays a session holding a lock on an object for which another session is waiting. See catblock.sql
DBA_CACHEABLE_NONTABLE_OBJECTS

Not listed in Oracle Documentation
DBA_CACHEABLE_OBJECTS

Not listed in Oracle Documentation
DBA_CACHEABLE_OBJECTS_BASE

Not listed in Oracle Documentation
DBA_CACHEABLE_TABLES

Not listed in Oracle Documentation
DBA_CACHEABLE_TABLES_BASE

Not listed in Oracle Documentation
DBA_CATALOG ALL_ USER_ Lists all indexes, tables, views, clusters, synonyms, and sequences in the database.
DBA_CLU_COLUMNS
USER_ Maps all table columns owned by the current user to related cluster columns.
DBA_CLUSTER_HASH_EXPRESSIONS ALL_ USER_ Lists hash functions for all hash clusters in the database.
DBA_CLUSTERS ALL_ USER_ Describes all clusters in the database.
DBA_COLL_TYPES ALL_ USER_ Describes all named collection types (arrays, nested tables, object tables, etc) in the database.
DBA_COL_COMMENTS ALL_ USER_ Describes comments on columns of all tables and views in the database.
DBA_COL_PRIVS ALL_ USER_ Describes all column object grants in the database.
COL_PRIVS_MADE ALL_ USER_ Lists column object grants for which the current user is object owner or grantor.
COL_PRIVS_RECD ALL_ USER_ Describes column object grants for which the current user or PUBLIC is the grantee.
DBA_CONSTRAINTS ALL_ USER_ Describes all constraint definitions on all tables in the database.
DBA_CONS_COLUMNS ALL_ USER_ Describes all columns in the database that are specified in constraint definitions.
DBA_CONS_OBJ_COLUMNS ALL_
List of types an object column or attribute is constrained to in all tables in the database.
DBA_CONTEXT ALL_
Provides all context namespace information in the database.
DBA_DATA_FILES

Describes database files.
DBA_DB_LINKS ALL_ USER_ Describes all database links in the database.
DBA_DDL_LOCKS

Lists all DDL locks held in the database and all outstanding requests for a DDL lock. See catblock.sql
DBA_DEPENDENCIES ALL_ USER_ Describes all dependencies between procedures, packages, functions, triggers, etc.
DBA_DIRECTORIES ALL_ USER_ Describes all directory objects in the database.
DBA_DML_LOCKS

Lists all DML locks held in the database and all outstanding requests for a DML lock. See catblock.sql
DBA_DMT_FREE_SPACE

Describes the free extents in all dictionary managed tablespaces in the database.
DBA_DMT_USED_EXTENTS

Describes the extents comprising the segments in all dictionary managed tablespaces.
DBA_ERRORS ALL_ USER_ Describes current errors on all stored objects (views, procedures, functions, packages).
DBA_EXTENTS
USER_ Describes the extents comprising the segments in all tablespaces in the database.
DBA_EXTERNAL_LOCATIONS ALL_ USER_ Describes the locations (data sources) of all external tables in the database.
DBA_EXTERNAL_TABLES ALL_ USER_ Describes all external tables in the database.
DBA_FREE_SPACE
USER_ Describes the free extents in all tablespaces in the database.
DBA_FREE_SPACE_COALESCED

Describes statistics on coalesced space in all tablespaces in the database.
DBA_FREE_SPACE_COALESCED_TMP1

Not listed in Oracle Documentation
DBA_FREE_SPACE_COALESCED_TMP2

Not listed in Oracle Documentation
DBA_FREE_SPACE_COALESCED_TMP3

Not listed in Oracle Documentation
DBA_GLOBAL_CONTEXT

Description of all context information accessible globally.
DBA_INDEXES ALL_ USER_ Describes all indexes in the database.
DBA_INDEXTYPES ALL_ USER_ Describes all indexes in the database.
DBA_INDEXTYPE_COMMENTS ALL_ USER_ Lists all comments for user-defined indextypes in the database.
DBA_INDEXTYPE_OPERATORS ALL_ USER_ Lists all the operators supported by indextypes in the database.
DBA_IND_COLUMNS ALL_ USER_ Describes the columns of indexes on all tables in the database.
DBA_IND_EXPRESSIONS ALL_ USER_ Lists expressions of function-based indexes on all tables and clusters in the database.
DBA_INTERNAL_TRIGGERS ALL_ USER_ Describes internal triggers on all tables in the database.
DBA_JOBS ALL_ USER_ Describes all jobs in the database.
DBA_JOBS_RUNNING

Lists all jobs in the database that are currently running.
DBA_JOIN_IND_COLUMNS ALL_ USER_ Describes all join conditions in the database.
DBA_KEEPSIZES

Shows the size PL/SQL objects will occupy the shared pool when "pinned" via DBMS_SHARED_POOL.KEEP procedure.
DBA_KGLLOCK

Lists all the locks and pins held on KGL objects. See catblock.sql
DBA_LIBRARIES ALL_ USER_ Describes all the libraries in the database.
DBA_LMT_FREE_SPACE

Describes the free extents in all locally managed tablespaces in the database.
DBA_LMT_USED_EXTENTS

Describes the extents comprising the segments in all locally managed tablespaces in the database.
DBA_LOCK

All locks held or requested within the database. See catblock.sql
DBA_LOCKS

Synonym for DBA_LOCK.
DBA_LOCK_INTERNAL

Displays a row for each lock or latch that is being held. See catblock.sql
DBA_LOG_GROUP_COLUMNS ALL_ USER_ Describes all columns in the database that are specified in log groups.
DBA_LOG_GROUPS ALL_ USER_ Describes log group definitions on all tables in the database.
DBA_METHOD_PARAMS ALL_ USER_ Description of method parameters of all types in the database.
DBA_METHOD_RESULTS ALL_ USER_ Description of method results of all types in the database.
DBA_MVIEW_AGGREGATES ALL_ USER_ Description of the materialized view aggregates accessible to dba.
DBA_MVIEW_ANALYSIS ALL_ USER_ Description of the materialized views accessible to dba.
DBA_MVIEW_DETAIL_RELATIONS ALL_ USER_ Description of the materialized view detail tables accessible to dba.
DBA_MVIEW_JOINS ALL_ USER_ Describes the join between two columns in the WHERE clause of a materialized view accessible.
DBA_MVIEW_KEYS ALL_ USER_ Describes the columns or expressions in the SELECT list upon which materialized views in the database are based.
DBA_MVIEW_LOG_FILTER_COLS

Lists all columns (excluding primary key columns) being logged in the materialized view logs.
DBA_MVIEW_LOGS ALL_ USER_ Describes all materialized view logs in the database.
DBA_MVIEW_REFRESH_TIMES ALL_ USER_ Describes refresh times of all materialized views in the database.
DBA_MVIEWS ALL_ USER_ Describes all materialized views in the database.
DBA_NESTED_TABLES ALL_ USER_ Description of nested tables contained in all tables.
DBA_OBJ_COLATTRS ALL_ USER_ Description of object columns and attributes contained in all tables in the database.
DBA_OBJECT_SIZE
USER_ Lists the sizes, in bytes, of various PL/SQL objects.
DBA_OBJECT_TABLES ALL_ USER_ Describes all object tables in the database.
DBA_OBJECTS ALL_ USER_ Describes all objects in the database.
DBA_OPANCILLARY ALL_ USER_ Describes operators whose bindings are ancillary to other (primary) operators.
DBA_OPARGUMENTS ALL_ USER_ Describes arguments for each operator binding accessible to database users.
DBA_OPBINDINGS ALL_ USER_ Describes bindings of all operators in the database.
DBA_OPERATOR_COMMENTS ALL_ USER_ Lists all comments for user-defined operators in the database.
DBA_OPERATORS ALL_ USER_ Lists all comments for user-defined operators in the database.
DBA_ORPHAN_KEY_TABLE

NOT IN DB Reports key values from indexes where the underlying base table has block corruptions.
DBA_OUTLINE_HINTS ALL_ USER_ Describes the set of hints that make up the all the outlines in the database.
DBA_OUTLINES ALL_ USER_ Describes all outlines in the database.
DBA_PARTIAL_DROP_TABS ALL_ USER_ List tables that have partially completed DROP COLUMN operations.
PASSWORD_LIMITS
USER_ Describes the password profile parameters that are assigned to the user.
DBA_PENDING_CONV_TABLES ALL_ USER_ Describes all pending conversion tables in the database.
DBA_PENDING_TRANSACTIONS

Describes unresolved transactions (either due to failure or if the coordinator has not sent a COMMIT/ROLLBACK).
DBA_POLICIES ALL_ USER_ Describes all security policies in the database.
DBA_POLICY_CONTEXTS ALL_ USER_ Describes all driving contexts in the database.
DBA_POLICY_GROUPS ALL_ USER_ Describes all policy groups in the database.
DBA_PROCEDURES ALL_ USER_ Lists all functions and procedures along with their associated properties.
DBA_PROFILES

Displays all profiles and their limits.
DBA_PROXIES
USER_ Displays Information about all proxy connections in the system.
DBA_REFS ALL_ USER_ describes the REF columns and REF attributes in object type columns of all the objects in the database.
DBA_REGISTERED_MVIEWS ALL_ USER_ Describes all registered materialized views (registered at a master site or a master materialized view site) in the database.
DBA_REGISTERED_MVIEW_GROUPS

Lists all the materialized view repgroups at this site.
DBA_REGISTRY
USER_ Displays information about the components loaded into the database that are owned by the current user.
REGISTRY_BANNERS ALL_
Displays the valid components loaded into the database.
DBA_REGISTRY_HIERARCHY

Displays information about the components loaded into the database, grouped by owner and organized in the component hierarchy.
RESOURCE_LIMITS
USER_ Displays the resource limits for the current user.
DBA_RESUMABLE
USER_ Lists all resumable statements executed in the system.
DBA_ROLE_PRIVS
USER_ Describes the roles granted to all users and roles in the database.
DBA_ROLES

Lists all roles that exist in the database.
DBA_ROLLBACK_SEGS

Describes rollback segments.
DBA_SECONDARY_OBJECTS ALL_ USER_ All secondary objects for domain indexes.
DBA_SEGMENTS
USER_ Describes the storage allocated for all segments in the database.
DBA_SEQUENCES ALL_ USER_ Describes all sequences in the database.
DBA_SOURCE ALL_ USER_ Describes the text source of all stored objects in the database.
DBA_STORED_SETTINGS ALL_ USER_ Lists information about the persistent parameter settings for stored PL/SQL units for which the current user has execute privileges.
SUMDELTA ALL_
Lists direct path load entries accessible to the current user.
DBA_SYNONYMS ALL_ USER_ Describes all synonyms in the database.
DBA_SYS_PRIVS
USER_ Describes system privileges granted to users and roles.
DBA_TAB_COL_STATISTICS ALL_ USER_ Contains column statistics and histogram information extracted from DBA_TAB_COLUMNS.
DBA_TAB_COLUMNS ALL_ USER_ Describes columns of all tables, views, and clusters in the database.
DBA_TAB_COLS ALL_ USER_ Same as DBA_TAB_COLUMNS but has 4 more columns.
DBA_TAB_COMMENTS ALL_ USER_ Describes comments on all tables and views in the database.
DBA_TAB_HISTOGRAMS ALL_ USER_ Describes histograms on columns of all tables in the database.
DBA_TAB_MODIFICATIONS ALL_ USER_ Information regarding modifications to tables.
DBA_TAB_PRIVS ALL_ USER_ Describes all object grants in the database. Returns one row for each object privilege held by a user.
TAB_PRIVS_MADE ALL_ USER_ Describes the object grants for which the current user is the object owner or grantor.
TAB_PRIVS_RECD ALL_ USER_ Describes object grants for which the current user is the grantee or where an enabled role or PUBLIC is the grantee
DBA_TABLES ALL_ USER_ Describes all relational tables in the database.
DBA_TABLESPACES
USER_ Describes all tablespaces in the database.
DBA_TEMP_FILES

Describes all temporary files (tempfiles) in the database.
DBA_TEMPLATE_REFGROUPS

Not listed in Oracle Documentation
DBA_TEMPLATE_TARGETS

Not listed in Oracle Documentation
DBA_TRIGGER_COLS ALL_ USER_ Describes column usage in all triggers.
DBA_TRIGGERS ALL_ USER_ Describes all triggers in the database.
DBA_TS_QUOTAS
USER_ Describes tablespace quotas for all users.
DBA_TYPE_ATTRS ALL_ USER_ Description of attributes of all types in the database.
DBA_TYPE_METHODS ALL_ USER_ Description of methods of all types in the database.
DBA_TYPE_VERSIONS ALL_ USER_ Describes the versions of all object types in the database.
DBA_TYPES ALL_ USER_ Describes all object types in the database.
DBA_UNDO_EXTENTS

Describes the extents comprising the segments in all undo tablespaces in the database.
DBA_UNUSED_COL_TABS ALL_ USER_ All tables with unused columns in the database.
DBA_UPDATABLE_COLUMNS ALL_ USER_ Describes all columns in a join view that can be updated by the database administrator.
DBA_USERS ALL_ USER_ Describes all users of the database.
DBA_USTATS ALL_ USER_ All statistics collected on either tables or indexes.
DBA_VARRAYS ALL_ USER_ Describes all the varrays in the database.
DBA_VIEWS ALL_ USER_ Describes all views in the database.
DBA_WAITERS

Shows all sessions waiting for locks and the session that holds the lock. See catblock.sql

Thursday, October 2, 2008

Installing DBI and Using ORACLE with Perl in Linux

You will learn in this tutorial how to install the DBI (the Perl interface to databases) in RedHat Linux 7.0 and above. There is another version for Windows here. You can obtain a more detailed introduction to DBI by reading its FAQ. You can see below the basic DBI model. The scripts are written in Perl using standard Perl variables, commands and syntax. The DBI has methods and handles which are database software independent. You program to access, change or query a database using the standard SQL language, combined with the DBI methods and handles. You install as many DBD::type modules as you need to support the different database software you may have, but you Perl script will be the same.

You will also see in this tutorial the basics of using DBI to perform queries, and display the results. Once you learn the basics you can use Perl to program the other SQL commands we saw briefly in our class on MySQL, and you should have learned in more details in the DB course. Please note that we have on reserve at the Library the book Programming the Perl DBI, the authoritative reference to DBI.

Hướng dẫn cài DBI và DBD

1. Test máy đã cài DBI chưa
perl -e 'use DBI; print $DBI::VERSION,"\n";'

2. Test máy đã cài DBD chưa
perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'



Install DBINếu cài báo lỗi
cc1: invalid option `tune=pentium4'

Tìm trong file Makefile bỏ dòng này
OPTIMIZE = -O2 -g -pipe -m32 -march=i386 -mtune=pentium4



Install DBD:Oracle

Simply download, gunzip, and untar the DBD module into a temporary directory and make it. Below is a quick guide to installing DBD:


1. Cài xong Test lại DBI

perl -e 'use DBI; print $DBI::VERSION,"\n";'

1. Cài xong Test lại DBD
perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'