Scribd
Upload
34 views18 pages

TP Tabs

The document details a series of SQL commands executed in an Oracle Database environment, including user logins, connection attempts, and tablespace management. It highlights errors encountered during connection attempts and tablespace creation, as well as successful operations such as creating and dropping tablespaces. The document also shows the structure of the database and the files associated with various tablespaces.

Uploaded by

berphi mayela
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
34 views18 pages

TP Tabs

The document details a series of SQL commands executed in an Oracle Database environment, including user logins, connection attempts, and tablespace management. It highlights errors encountered during connection attempts and tablespace creation, as well as successful operations such as creating and dropping tablespaces. The document also shows the structure of the database and the files associated with various tablespaces.

Uploaded by

berphi mayela
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

login as: root

[email protected]'s password:

Last login: Sat Mar 15 23:54:36 2025 from 192.168.1.65

[root@database ~]# su - oracle

Dernière connexion : samedi 15 mars 2025 à 22:55:47 WAT de 192.168.1.65 sur pts/1

[oracle@database ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 17 14:42:50 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

SQL> conn sys/password@cdb as sysdba

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

SQL> conn sys/password@BASEDSE as sysdba

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

SQL> exit

[oracle@database ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 17 14:44:42 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.


Connecte a :

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

SYSTEM

/data/BASEDATA/system01.dbf

SYSAUX

/data/BASEDATA/sysaux01.dbf

UNDOTBS1

/data/BASEDATA/undotbs01.dbf

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

USERS

/data/BASEDATA/users01.dbf
FINANCE_DATA

/data/BASEDATA/finance_data01.dbf

SQL> set linesize 180

SQL> column file_name format a60

SQL> column tablespace_name format a40

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

SYSTEM /data/BASEDATA/system01.dbf

SYSAUX /data/BASEDATA/sysaux01.dbf

UNDOTBS1 /data/BASEDATA/undotbs01.dbf

USERS /data/BASEDATA/users01.dbf

FINANCE_DATA /data/BASEDATA/finance_data01.dbf

SQL> alter session set container=PDB1;

Session modifiee.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

UNDOTBS1 /data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX /data/BASEDATA/PDB1/sysaux01.dbf

USERS /data/BASEDATA/PDB1/users01.dbf

SYSTEM /data/BASEDATA/PDB1/system01.dbf
SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------

/data/BASEDATA/PDB1/system01.dbf

/data/BASEDATA/PDB1/sysaux01.dbf

/data/BASEDATA/PDB1/undotbs01.dbf

/data/BASEDATA/PDB1/users01.dbf

SQL> exit

Deconnecte de Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@database ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 17 14:56:08 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

SQL> alter session set container=PDB1;

SP2-0640: Not connected

SQL> conn PDB1/password as sysdba

Connecte.

SQL> show user

USER est "SYS"

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT
SQL> alter session set container=PDB1;

Session modifiee.

SQL> show con_name;

CON_NAME

------------------------------

PDB1

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

UNDOTBS1

/data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX

/data/BASEDATA/PDB1/sysaux01.dbf

USERS

/data/BASEDATA/PDB1/users01.dbf

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

SYSTEM

/data/BASEDATA/PDB1/system01.dbf
SQL> select name from v$tablespace

2 ;

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

SQL> select name from datafile;

select name from datafile

ERREUR a la ligne 1 :

ORA-00942: Table ou vue inexistante

SQL> select name from datafiles;

select name from datafiles

ERREUR a la ligne 1 :

ORA-00942: Table ou vue inexistante

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------
/data/BASEDATA/PDB1/system01.dbf

/data/BASEDATA/PDB1/sysaux01.dbf

/data/BASEDATA/PDB1/undotbs01.dbf

/data/BASEDATA/PDB1/users01.dbf

SQL> create tablespace tbs1 datafile '/data/BASEDATA/PDB1/' size 50M;

create tablespace tbs1 datafile '/data/BASEDATA/PDB1/' size 50M

ERREUR a la ligne 1 :

ORA-01119: echec de creation du fichier de base de donnees

'/data/BASEDATA/PDB1/'

ORA-27038: le fichier cree existe deja

Additional information: 1

SQL> create tablespace tbs1 datafile '/data/BASEDATA/PDB1/.dbf size 50M;

create tablespace tbs1 datafile '/data/BASEDATA/PDB1/.dbf size 50M

ERREUR a la ligne 1 :

ORA-01756: une chaine entre apostrophes ne se termine pas correctement

SQL> create tablespace tbs1 datafile '/data/BASEDATA/PDB1/.dbf' size 50M;

Tablespace cree.

SQL> select tablespace_name,file_name from dba_data_file;

select tablespace_name,file_name from dba_data_file

ERREUR a la ligne 1 :

ORA-00942: Table ou vue inexistante


SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

UNDOTBS1

/data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX

/data/BASEDATA/PDB1/sysaux01.dbf

USERS

/data/BASEDATA/PDB1/users01.dbf

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

SYSTEM

/data/BASEDATA/PDB1/system01.dbf

TBS1

/data/BASEDATA/PDB1/.dbf

SQL> set linesize 180

SQL> column file_name format a60


SQL> column tablespace_name format a40

SQL> SP2-0734: commande inconnue au debut de "SQL> colum..." - le reste de la ligne est ignore.

SQL> SP2-0734: commande inconnue au debut de "SQL> colum..." - le reste de la ligne est ignore.

SQL> column file_name format a60

SQL> column tablespace_name format a40

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

UNDOTBS1 /data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX /data/BASEDATA/PDB1/sysaux01.dbf

USERS /data/BASEDATA/PDB1/users01.dbf

SYSTEM /data/BASEDATA/PDB1/system01.dbf

TBS1 /data/BASEDATA/PDB1/.dbf

SQL> create tablespace tbs2 datafile '/DATA/BASEDATA/PDB1/.dbf size 50M;

create tablespace tbs2 datafile '/DATA/BASEDATA/PDB1/.dbf size 50M

ERREUR a la ligne 1 :

ORA-01756: une chaine entre apostrophes ne se termine pas correctement

SQL> create tablespace tbs2 datafile '/DATA/BASEDATA/PDB1/'.dbf size 50M;

create tablespace tbs2 datafile '/DATA/BASEDATA/PDB1/'.dbf size 50M

ERREUR a la ligne 1 :

ORA-02180: option non valide pour CREATE TABLESPACE

SQL> create tablespace tbs2 datafile '/DATA/BASEDATA/PDB1/.dbf' size 50M;

create tablespace tbs2 datafile '/DATA/BASEDATA/PDB1/.dbf' size 50M


*

ERREUR a la ligne 1 :

ORA-01119: echec de creation du fichier de base de donnees '/DATA/BASEDATA/PDB1/.dbf'

ORA-27040: erreur lors de la creation du fichier : creation impossible

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

SQL> create tablespace tbs2 datafile '/data/BASEDATA/PDB1/.dbf' size 50M;

create tablespace tbs2 datafile '/data/BASEDATA/PDB1/.dbf' size 50M

ERREUR a la ligne 1 :

ORA-01537: impossible d'ajouter le fichier '/data/BASEDATA/PDB1/.dbf' - il fait deja partie de la base


de donnees

SQL> CREATE TABLESPACE tbs2

DATAFILE '/DATA/BASEDATA/PDB1/tbs2.dbf'

SIZE 50M

AUTOEXTEND ON NEXT 10M MAXSIZE 1G

EXTENT MANAGEMENT LOCAL;

2 3 4 5 CREATE TABLESPACE tbs2

ERREUR a la ligne 1 :

ORA-01119: echec de creation du fichier de base de donnees '/DATA/BASEDATA/PDB1/tbs2.dbf'

ORA-27040: erreur lors de la creation du fichier : creation impossible

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

SQL> CREATE TABLESPACE tbs2


DATAFILE '/data/BASEDATA/PDB1/tbs2.dbf'

SIZE 50M

AUTOEXTEND ON NEXT 10M MAXSIZE 1G

EXTENT MANAGEMENT LOCAL;

2 3 4 5 ;

Tablespace cree.

SQL> create tablespace tbs3 datafile '/data/BASEDATA/PDB1/tbs3.dbf' size 40M;

SP2-0734: commande inconnue au debut de ";create ta..." - le reste de la ligne est ignore.

SQL> SHOW CON_NAME; -- Vérifie si vous êtes dans PDB1 ou dans la CDB$ROOT

ALTER SESSION SET CONTAINER=PDB1; -- Change de base si nécessaire

CON_NAME

------------------------------

PDB1

SP2-0158: option SHOW inconnue ";"

SP2-0158: option SHOW inconnue "--"

SP2-0158: option SHOW inconnue "Vérifie"

SP2-0158: option SHOW inconnue "si"

SP2-0158: option SHOW inconnue "vous"

SP2-0158: option SHOW inconnue "êtes"

SP2-0158: option SHOW inconnue "dans"

SP2-0158: option SHOW inconnue "PDB1"

SP2-0158: option SHOW inconnue "ou"

SP2-0158: option SHOW inconnue "dans"

SP2-0158: option SHOW inconnue "la"

SP2-0158: option SHOW inconnue "CDB$ROOT"

SQL> SQL> 2 ;

ALTER SESSION SET CONTAINER=PDB1; -- Change de base si nécessaire

*
ERREUR a la ligne 1 :

ORA-00922: option erronee ou absente

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session modifiee.

SQL> create tablespace tbs3 datafile '/data/BASEDATA/PDB1/tbs3.dbf' size 40M;

Tablespace cree.

SQL> salect tablespace_name,file_name from dba_data_files;

SP2-0734: commande inconnue au debut de "salect tab..." - le reste de la ligne est ignore.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

UNDOTBS1 /data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX /data/BASEDATA/PDB1/sysaux01.dbf

USERS /data/BASEDATA/PDB1/users01.dbf

SYSTEM /data/BASEDATA/PDB1/system01.dbf

TBS1 /data/BASEDATA/PDB1/.dbf

TBS2 /data/BASEDATA/PDB1/tbs2.dbf

TBS3 /data/BASEDATA/PDB1/tbs3.dbf

7 lignes selectionnees.

SQL> create tablespace tbs4 datafile /data/BASEDATA/PDB1/tbs4.dbf size 100M autoextend on


nexte 20M maxisize 1G;

create tablespace tbs4 datafile /data/BASEDATA/PDB1/tbs4.dbf size 100M autoextend on nexte 20M
maxisize 1G
*

ERREUR a la ligne 1 :

ORA-02236: nom de fichier non valide

SQL> create tablespace tbs4 datafile '/data/BASEDATA/PDB1/tbs4.dbf' size 100M autoextend on


nexte 20M maxisize 1G;

create tablespace tbs4 datafile '/data/BASEDATA/PDB1/tbs4.dbf' size 100M autoextend on nexte


20M maxisize 1G

ERREUR a la ligne 1 :

ORA-02180: option non valide pour CREATE TABLESPACE

SQL> create tablespace tbs4 datafile '/data/BASEDATA/PDB1/tbs4.dbf' size 30M autoextend on next
10m maxsize 100M;

Tablespace cree.

SQL> create table t1 tablespace tbs2 as select * from tab;

Table creee.

SQL> drop tablespace tbs2;

drop tablespace tbs2

ERREUR a la ligne 1 :

ORA-01549: le tablespace n'est pas vide ; utiliser l'option INCLUDING CONTENTS

SQL> drop tablespace tbs2 including content;

drop tablespace tbs2 including content


*

ERREUR a la ligne 1 :

ORA-01911: mot-cle CONTENTS attendu

SQL> drop tablespace tbs2 including contents;

Tablespace supprime.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

UNDOTBS1 /data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX /data/BASEDATA/PDB1/sysaux01.dbf

USERS /data/BASEDATA/PDB1/users01.dbf

SYSTEM /data/BASEDATA/PDB1/system01.dbf

TBS1 /data/BASEDATA/PDB1/.dbf

TBS3 /data/BASEDATA/PDB1/tbs3.dbf

TBS4 /data/BASEDATA/PDB1/tbs4.dbf

7 lignes selectionnees.

SQL> drop tablespace tbs1;

Tablespace supprime.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------
UNDOTBS1 /data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX /data/BASEDATA/PDB1/sysaux01.dbf

USERS /data/BASEDATA/PDB1/users01.dbf

SYSTEM /data/BASEDATA/PDB1/system01.dbf

TBS3 /data/BASEDATA/PDB1/tbs3.dbf

TBS4 /data/BASEDATA/PDB1/tbs4.dbf

6 lignes selectionnees.

SQL> drop tablespace tbs3,tbs4;

drop tablespace tbs3,tbs4

ERREUR a la ligne 1 :

ORA-02173: option non valide pour DROP TABLESPACE

SQL> drop tablespace tbs3;

Tablespace supprime.

SQL> drop tablespace tbs4;

Tablespace supprime.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

UNDOTBS1 /data/BASEDATA/PDB1/undotbs01.dbf

SYSAUX /data/BASEDATA/PDB1/sysaux01.dbf

USERS /data/BASEDATA/PDB1/users01.dbf
SYSTEM /data/BASEDATA/PDB1/system01.dbf

SQL> select property_value from database_properties where


property_name='DEFAULT_PERMENT_TABLESPACE';

aucune ligne selectionnee

SQL> show user

USER est "SYS"

SQL> show con_name

CON_NAME

------------------------------

PDB1

SQL> conn sys/ as sysdba

Entrez le mot de passe :

Connecte.

SQL> select property_value from database_properties where


property_name='DEFAULT_PERMENT_TABLESPACE';

aucune ligne selectionnee

SQL> select property_value from database_properties where


property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------

USERS

SQL> select tablespace_name,file_name from dba_data_files;


TABLESPACE_NAME FILE_NAME

---------------------------------------- ------------------------------------------------------------

SYSTEM /data/BASEDATA/system01.dbf

SYSAUX /data/BASEDATA/sysaux01.dbf

UNDOTBS1 /data/BASEDATA/undotbs01.dbf

USERS /data/BASEDATA/users01.dbf

FINANCE_DATA /data/BASEDATA/finance_data01.dbf

SQL> select file_name from dba_data_files;

FILE_NAME

------------------------------------------------------------

/data/BASEDATA/system01.dbf

/data/BASEDATA/sysaux01.dbf

/data/BASEDATA/undotbs01.dbf

/data/BASEDATA/users01.dbf

/data/BASEDATA/finance_data01.dbf

SQL> desc v$tablespace;

Nom NULL ? Type

----------------------------------------------------------------------------------------------------- -------- -----------------------


---------------------------------------------

TS# NUMBER

NAME VARCHAR2(30)

INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)

BIGFILE VARCHAR2(3)

FLASHBACK_ON VARCHAR2(3)

ENCRYPT_IN_BACKUP VARCHAR2(3)

CON_ID NUMBER

SQL> select name from tablespace;


select name from tablespace

ERREUR a la ligne 1 :

ORA-00942: Table ou vue inexistante

SQL>

You might also like